diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs
index d685041..9e82e5a 100644
--- a/ClosedXML/Excel/Cells/IXLCell.cs
+++ b/ClosedXML/Excel/Cells/IXLCell.cs
@@ -173,6 +173,14 @@
IXLRange InsertData(IEnumerable data);
///
+ /// Inserts the IEnumerable data elements and returns the range it occupies.
+ ///
+ /// The IEnumerable data.
+ /// if set to true the data will be transposed before inserting.
+ ///
+ IXLRange InsertData(IEnumerable data, Boolean tranpose);
+
+ ///
/// Inserts the IEnumerable data elements as a table and returns it.
/// The new table will receive a generic name: Table#
///
@@ -260,7 +268,7 @@
IXLCells InsertCellsBefore(int numberOfColumns);
///
- /// Creates a named range out of this cell.
+ /// Creates a named range out of this cell.
/// If the named range exists, it will add this range to that named range.
/// The default scope for the named range is Workbook.
///
@@ -268,7 +276,7 @@
IXLCell AddToNamed(String rangeName);
///
- /// Creates a named range out of this cell.
+ /// Creates a named range out of this cell.
/// If the named range exists, it will add this range to that named range.
/// Name of the range.
/// The scope for the named range.
@@ -276,7 +284,7 @@
IXLCell AddToNamed(String rangeName, XLScope scope);
///
- /// Creates a named range out of this cell.
+ /// Creates a named range out of this cell.
/// If the named range exists, it will add this range to that named range.
/// Name of the range.
/// The scope for the named range.
diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs
index 39a4928..8aaea02 100644
--- a/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/Excel/Cells/XLCell.cs
@@ -470,7 +470,7 @@
public IXLTable InsertTable(IEnumerable data, string tableName, bool createTable)
{
- if (data != null && data.GetType() != typeof(String))
+ if (data != null && !(data is String))
{
var ro = Address.RowNumber + 1;
var fRo = Address.RowNumber;
@@ -713,10 +713,18 @@
public IXLRange InsertData(IEnumerable data)
{
- if (data != null && data.GetType() != typeof(String))
+ return InsertData(data, false);
+ }
+
+ public IXLRange InsertData(IEnumerable data, Boolean transpose)
+ {
+ if (data != null && !(data is String))
{
- var ro = Address.RowNumber;
- var maxCo = 0;
+ var rowNumber = Address.RowNumber;
+ var columnNumber = Address.ColumnNumber;
+
+ var maxColumnNumber = 0;
+ var maxRowNumber = 0;
var isDataTable = false;
var isDataReader = false;
@@ -745,20 +753,31 @@
members = memberCache[itemType];
accessor = accessorCache[itemType];
- var co = Address.ColumnNumber;
+ if (transpose)
+ rowNumber = Address.RowNumber;
+ else
+ columnNumber = Address.ColumnNumber;
+
if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber())
{
- SetValue(m, ro, co);
- co++;
+ SetValue(m, rowNumber, columnNumber);
+
+ if (transpose)
+ rowNumber++;
+ else
+ columnNumber++;
}
else if (itemType.IsArray)
{
- // dynamic arr = m;
foreach (var item in (Array)m)
{
- SetValue(item, ro, co);
- co++;
+ SetValue(item, rowNumber, columnNumber);
+
+ if (transpose)
+ rowNumber++;
+ else
+ columnNumber++;
}
}
else if (isDataTable || m is DataRow)
@@ -768,8 +787,12 @@
foreach (var item in (m as DataRow).ItemArray)
{
- SetValue(item, ro, co);
- co++;
+ SetValue(item, rowNumber, columnNumber);
+
+ if (transpose)
+ rowNumber++;
+ else
+ columnNumber++;
}
}
else if (isDataReader || m is IDataRecord)
@@ -782,31 +805,45 @@
var fieldCount = record.FieldCount;
for (var i = 0; i < fieldCount; i++)
{
- SetValue(record[i], ro, co);
- co++;
+ SetValue(record[i], rowNumber, columnNumber);
+
+ if (transpose)
+ rowNumber++;
+ else
+ columnNumber++;
}
}
else
{
foreach (var mi in members)
{
- SetValue(accessor[m, mi.Name], ro, co);
- co++;
+ SetValue(accessor[m, mi.Name], rowNumber, columnNumber);
+
+ if (transpose)
+ rowNumber++;
+ else
+ columnNumber++;
}
}
- if (co > maxCo)
- maxCo = co;
+ if (transpose)
+ columnNumber++;
+ else
+ rowNumber++;
- ro++;
+ if (columnNumber > maxColumnNumber)
+ maxColumnNumber = columnNumber;
+
+ if (rowNumber > maxRowNumber)
+ maxRowNumber = rowNumber;
}
ClearMerged();
return _worksheet.Range(
Address.RowNumber,
Address.ColumnNumber,
- ro - 1,
- maxCo - 1);
+ maxRowNumber - 1,
+ maxColumnNumber - 1);
}
return null;
@@ -1620,7 +1657,7 @@
private bool SetEnumerable(object collectionObject)
{
// IXLRichText implements IEnumerable, but we don't want to handle this here.
- if ((collectionObject as IXLRichText) != null) return false;
+ if (collectionObject is IXLRichText) return false;
var asEnumerable = collectionObject as IEnumerable;
return InsertData(asEnumerable) != null;
@@ -1639,13 +1676,10 @@
{
if (value == null)
_worksheet.Cell(ro, co).SetValue(String.Empty);
+ else if (value is IConvertible)
+ _worksheet.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T)));
else
- {
- if (value is IConvertible)
- _worksheet.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T)));
- else
- _worksheet.Cell(ro, co).SetValue(value);
- }
+ _worksheet.Cell(ro, co).SetValue(value);
}
private void SetValue(object value)
diff --git a/ClosedXML_Examples/Misc/InsertingData.cs b/ClosedXML_Examples/Misc/InsertingData.cs
index d0ade19..d1035d5 100644
--- a/ClosedXML_Examples/Misc/InsertingData.cs
+++ b/ClosedXML_Examples/Misc/InsertingData.cs
@@ -13,59 +13,65 @@
// Public
public void Create(String filePath)
{
- var wb = new XLWorkbook();
- var ws = wb.Worksheets.Add("Inserting Data");
+ using (var wb = new XLWorkbook())
+ {
+ var ws = wb.Worksheets.Add("Inserting Data");
- // From a list of strings
- var listOfStrings = new List();
- listOfStrings.Add("House");
- listOfStrings.Add("001");
- ws.Cell(1, 1).Value = "From Strings";
- ws.Cell(1, 1).AsRange().AddToNamed("Titles");
- ws.Cell(2, 1).InsertData(listOfStrings);
+ // From a list of strings
+ var listOfStrings = new List();
+ listOfStrings.Add("House");
+ listOfStrings.Add("001");
+ ws.Cell(1, 1).Value = "From Strings";
+ ws.Cell(1, 1).AsRange().AddToNamed("Titles");
+ ws.Cell(2, 1).InsertData(listOfStrings);
- // From a list of arrays
- var listOfArr = new List();
- listOfArr.Add(new Int32[] { 1, 2, 3 });
- listOfArr.Add(new Int32[] { 1 });
- listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 });
- ws.Cell(1, 3).Value = "From Arrays";
- ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles");
- ws.Cell(2, 3).InsertData(listOfArr);
+ // From a list of arrays
+ var listOfArr = new List();
+ listOfArr.Add(new Int32[] { 1, 2, 3 });
+ listOfArr.Add(new Int32[] { 1 });
+ listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 });
+ ws.Cell(1, 3).Value = "From Arrays";
+ ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles");
+ ws.Cell(2, 3).InsertData(listOfArr);
- // From a DataTable
- var dataTable = GetTable();
- ws.Cell(6, 1).Value = "From DataTable";
- ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles");
- ws.Cell(7, 1).InsertData(dataTable.AsEnumerable());
+ // From a DataTable
+ var dataTable = GetTable();
+ ws.Cell(6, 1).Value = "From DataTable";
+ ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles");
+ ws.Cell(7, 1).InsertData(dataTable.AsEnumerable());
- // From a query
- var list = new List();
- list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." });
- list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." });
- list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." });
- list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." });
+ // From a query
+ var list = new List();
+ list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." });
+ list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." });
+ list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." });
+ list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." });
- var people = from p in list
- where p.Age >= 21
- select new { p.Name, p.House, p.Age };
+ var people = from p in list
+ where p.Age >= 21
+ select new { p.Name, p.House, p.Age };
- ws.Cell(6, 6).Value = "From Query";
- ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles");
- ws.Cell(7, 6).InsertData(people.AsEnumerable());
+ ws.Cell(6, 6).Value = "From Query";
+ ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles");
+ ws.Cell(7, 6).InsertData(people.AsEnumerable());
- // Prepare the style for the titles
- var titlesStyle = wb.Style;
- titlesStyle.Font.Bold = true;
- titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
- titlesStyle.Fill.BackgroundColor = XLColor.Cyan;
-
- // Format all titles in one shot
- wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;
+ ws.Cell("F13").Value = "Transposed";
+ ws.Range(13, 6, 13, 8).Merge().AddToNamed("Titles");
+ ws.Cell("F14").InsertData(people.AsEnumerable(), true);
- ws.Columns().AdjustToContents();
+ // Prepare the style for the titles
+ var titlesStyle = wb.Style;
+ titlesStyle.Font.Bold = true;
+ titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
+ titlesStyle.Fill.BackgroundColor = XLColor.Cyan;
- wb.SaveAs(filePath);
+ // Format all titles in one shot
+ wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle;
+
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(filePath);
+ }
}
class Person
diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs
index 31e66f9..9620511 100644
--- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs
+++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs
@@ -83,6 +83,22 @@
}
[Test]
+ public void InsertData2()
+ {
+ IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
+ IXLRange range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" }, false);
+ Assert.AreEqual("Sheet1!B2:B4", range.ToString());
+ }
+
+ [Test]
+ public void InsertData3()
+ {
+ IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
+ IXLRange range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" }, true);
+ Assert.AreEqual("Sheet1!B2:D2", range.ToString());
+ }
+
+ [Test]
public void IsEmpty1()
{
IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1");
diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
index 3c94664..326340b 100644
--- a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
+++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs
@@ -14,7 +14,6 @@
using (var wb = new XLWorkbook())
{
var ws = wb.AddWorksheet("Sheet1");
- ws.Column(1).Style.NumberFormat.Format = "yy-MM-dd";
var table = new DataTable();
table.Columns.Add("Date", typeof(DateTime));
@@ -24,9 +23,13 @@
table.Rows.Add(new DateTime(2017, 1, 1).AddMonths(i));
}
+ ws.Column(1).Style.NumberFormat.Format = "yy-MM-dd";
ws.Cell("A1").InsertData(table.AsEnumerable());
-
Assert.AreEqual("yy-MM-dd", ws.Cell("A5").Style.DateFormat.Format);
+
+ ws.Row(1).Style.NumberFormat.Format = "yy-MM-dd";
+ ws.Cell("A1").InsertData(table.AsEnumerable(), true);
+ Assert.AreEqual("yy-MM-dd", ws.Cell("E1").Style.DateFormat.Format);
}
}
}
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx
index 9e9d20f..c8ae77a 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx
Binary files differ