diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
index 4edb936..a74d98f 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs
@@ -1,5 +1,7 @@
using System;
using System.Collections;
+using System.Collections.Generic;
+using System.Data;
namespace ClosedXML.Excel
{
@@ -167,7 +169,7 @@
/// The new table will receive a generic name: Table#
///
/// The table data.
- IXLTable InsertTable(IEnumerable data);
+ IXLTable InsertTable(IEnumerable data);
///
/// Inserts the IEnumerable data elements as a table and returns it.
@@ -178,14 +180,14 @@
/// if set to true it will create an Excel table.
/// if set to false the table will be created in memory.
///
- IXLTable InsertTable(IEnumerable data, Boolean createTable);
+ IXLTable InsertTable(IEnumerable data, Boolean createTable);
///
/// Creates an Excel table from the given IEnumerable data elements.
///
/// The table data.
/// Name of the table.
- IXLTable InsertTable(IEnumerable data, String tableName);
+ IXLTable InsertTable(IEnumerable data, String tableName);
///
/// Inserts the IEnumerable data elements as a table and returns it.
@@ -196,7 +198,45 @@
/// if set to true it will create an Excel table.
/// if set to false the table will be created in memory.
///
- IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable);
+ IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable);
+
+
+ ///
+ /// Inserts the DataTable data elements as a table and returns it.
+ /// The new table will receive a generic name: Table#
+ ///
+ /// The table data.
+ IXLTable InsertTable(DataTable data);
+
+ ///
+ /// Inserts the DataTable data elements as a table and returns it.
+ /// The new table will receive a generic name: Table#
+ ///
+ /// The table data.
+ ///
+ /// if set to true it will create an Excel table.
+ /// if set to false the table will be created in memory.
+ ///
+ IXLTable InsertTable(DataTable data, Boolean createTable);
+
+ ///
+ /// Creates an Excel table from the given DataTable data elements.
+ ///
+ /// The table data.
+ /// Name of the table.
+ IXLTable InsertTable(DataTable data, String tableName);
+
+ ///
+ /// Inserts the DataTable data elements as a table and returns it.
+ ///
+ /// The table data.
+ /// Name of the table.
+ ///
+ /// if set to true it will create an Excel table.
+ /// if set to false the table will be created in memory.
+ ///
+ IXLTable InsertTable(DataTable data, String tableName, Boolean createTable);
+
XLHyperlink Hyperlink { get; set; }
IXLWorksheet Worksheet { get; }
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
index d3b9440..725463d 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs
@@ -507,22 +507,22 @@
return true;
}
- public IXLTable InsertTable(IEnumerable data)
+ public IXLTable InsertTable(IEnumerable data)
{
return InsertTable(data, null, true);
}
- public IXLTable InsertTable(IEnumerable data, bool createTable)
+ public IXLTable InsertTable(IEnumerable data, bool createTable)
{
return InsertTable(data, null, createTable);
}
- public IXLTable InsertTable(IEnumerable data, string tableName)
+ public IXLTable InsertTable(IEnumerable data, string tableName)
{
return InsertTable(data, tableName, true);
}
- public IXLTable InsertTable(IEnumerable data, string tableName, bool createTable)
+ public IXLTable InsertTable(IEnumerable data, string tableName, bool createTable)
{
if (data != null && data.GetType() != typeof(String))
{
@@ -532,142 +532,153 @@
int maxCo = 0;
bool isDataTable = false;
bool isDataReader = false;
- foreach (object m in data)
+ if (!data.Any())
{
- int co = Address.ColumnNumber;
-
- if (m.GetType().IsPrimitive || m is string || m is DateTime || m is Decimal)
- {
- if (!hasTitles)
- {
- string fieldName = GetFieldName(m.GetType().GetCustomAttributes(true));
- if (StringExtensions.IsNullOrWhiteSpace(fieldName))
- fieldName = m.GetType().Name;
-
- SetValue(fieldName, fRo, co);
- hasTitles = true;
- co = Address.ColumnNumber;
- }
-
- SetValue(m, ro, co);
- co++;
- }
- else if (m.GetType().IsArray)
- {
- foreach (object item in (Array)m)
- {
- SetValue(item, ro, co);
- co++;
- }
- }
- else if (isDataTable || m is DataRow)
- {
- if (!isDataTable)
- isDataTable = true;
-
- if (!hasTitles)
- {
- foreach (string fieldName in from DataColumn column in ((DataRow)m).Table.Columns
- select StringExtensions.IsNullOrWhiteSpace(column.Caption)
- ? column.ColumnName
- : column.Caption)
- {
- SetValue(fieldName, fRo, co);
- co++;
- }
-
- co = Address.ColumnNumber;
- hasTitles = true;
- }
-
- foreach (object item in ((DataRow)m).ItemArray)
- {
- SetValue(item, ro, co);
- co++;
- }
- }
- else if (isDataReader || m is IDataRecord)
- {
- if (!isDataReader)
- isDataReader = true;
-
- var record = m as IDataRecord;
-
- Int32 fieldCount = record.FieldCount;
- if (!hasTitles)
- {
- for (int i = 0; i < fieldCount; i++)
- {
- SetValue(record.GetName(i), fRo, co);
- co++;
- }
-
- co = Address.ColumnNumber;
- hasTitles = true;
- }
-
- for (int i = 0; i < fieldCount; i++)
- {
- SetValue(record[i], ro, co);
- co++;
- }
- }
+ var t = data.GetItemType();
+ if (t.IsPrimitive || t == typeof(string) || t == typeof(DateTime) || t == typeof(Decimal))
+ maxCo = Address.ColumnNumber + 1;
else
+ maxCo = Address.ColumnNumber + t.GetFields().Length + t.GetProperties().Length;
+ }
+ else
+ {
+ foreach (object m in data)
{
- var fieldInfo = m.GetType().GetFields();
- var propertyInfo = m.GetType().GetProperties();
- if (!hasTitles)
- {
- foreach (FieldInfo info in fieldInfo)
- {
- if ((info as IEnumerable) == null)
- {
- string fieldName = GetFieldName(info.GetCustomAttributes(true));
- if (StringExtensions.IsNullOrWhiteSpace(fieldName))
- fieldName = info.Name;
+ int co = Address.ColumnNumber;
+ if (m.GetType().IsPrimitive || m is string || m is DateTime || m is Decimal)
+ {
+ if (!hasTitles)
+ {
+ string fieldName = GetFieldName(m.GetType().GetCustomAttributes(true));
+ if (StringExtensions.IsNullOrWhiteSpace(fieldName))
+ fieldName = m.GetType().Name;
+
+ SetValue(fieldName, fRo, co);
+ hasTitles = true;
+ co = Address.ColumnNumber;
+ }
+
+ SetValue(m, ro, co);
+ co++;
+ }
+ else if (m.GetType().IsArray)
+ {
+ foreach (object item in (Array) m)
+ {
+ SetValue(item, ro, co);
+ co++;
+ }
+ }
+ else if (isDataTable || m is DataRow)
+ {
+ if (!isDataTable)
+ isDataTable = true;
+
+ if (!hasTitles)
+ {
+ foreach (string fieldName in from DataColumn column in ((DataRow) m).Table.Columns
+ select StringExtensions.IsNullOrWhiteSpace(column.Caption)
+ ? column.ColumnName
+ : column.Caption)
+ {
SetValue(fieldName, fRo, co);
+ co++;
}
+ co = Address.ColumnNumber;
+ hasTitles = true;
+ }
+
+ foreach (object item in ((DataRow) m).ItemArray)
+ {
+ SetValue(item, ro, co);
+ co++;
+ }
+ }
+ else if (isDataReader || m is IDataRecord)
+ {
+ if (!isDataReader)
+ isDataReader = true;
+
+ var record = m as IDataRecord;
+
+ Int32 fieldCount = record.FieldCount;
+ if (!hasTitles)
+ {
+ for (int i = 0; i < fieldCount; i++)
+ {
+ SetValue(record.GetName(i), fRo, co);
+ co++;
+ }
+
+ co = Address.ColumnNumber;
+ hasTitles = true;
+ }
+
+ for (int i = 0; i < fieldCount; i++)
+ {
+ SetValue(record[i], ro, co);
+ co++;
+ }
+ }
+ else
+ {
+ var fieldInfo = m.GetType().GetFields();
+ var propertyInfo = m.GetType().GetProperties();
+ if (!hasTitles)
+ {
+ foreach (FieldInfo info in fieldInfo)
+ {
+ if ((info as IEnumerable) == null)
+ {
+ string fieldName = GetFieldName(info.GetCustomAttributes(true));
+ if (StringExtensions.IsNullOrWhiteSpace(fieldName))
+ fieldName = info.Name;
+
+ SetValue(fieldName, fRo, co);
+ }
+
+ co++;
+ }
+
+ foreach (PropertyInfo info in propertyInfo)
+ {
+ if ((info as IEnumerable) == null)
+ {
+ string fieldName = GetFieldName(info.GetCustomAttributes(true));
+ if (StringExtensions.IsNullOrWhiteSpace(fieldName))
+ fieldName = info.Name;
+
+ SetValue(fieldName, fRo, co);
+ }
+
+ co++;
+ }
+
+ co = Address.ColumnNumber;
+ hasTitles = true;
+ }
+
+ foreach (FieldInfo info in fieldInfo)
+ {
+ SetValue(info.GetValue(m), ro, co);
co++;
}
foreach (PropertyInfo info in propertyInfo)
{
if ((info as IEnumerable) == null)
- {
- string fieldName = GetFieldName(info.GetCustomAttributes(true));
- if (StringExtensions.IsNullOrWhiteSpace(fieldName))
- fieldName = info.Name;
-
- SetValue(fieldName, fRo, co);
- }
-
+ SetValue(info.GetValue(m, null), ro, co);
co++;
}
-
- co = Address.ColumnNumber;
- hasTitles = true;
}
- foreach (FieldInfo info in fieldInfo)
- {
- SetValue(info.GetValue(m), ro, co);
- co++;
- }
+ if (co > maxCo)
+ maxCo = co;
- foreach (PropertyInfo info in propertyInfo)
- {
- if ((info as IEnumerable) == null)
- SetValue(info.GetValue(m, null), ro, co);
- co++;
- }
+ ro++;
}
-
- if (co > maxCo)
- maxCo = co;
-
- ro++;
}
ClearMerged();
@@ -685,6 +696,45 @@
return null;
}
+
+
+ public IXLTable InsertTable(DataTable data)
+ {
+ return InsertTable(data, null, true);
+ }
+
+ public IXLTable InsertTable(DataTable data, bool createTable)
+ {
+ return InsertTable(data, null, createTable);
+ }
+
+ public IXLTable InsertTable(DataTable data, string tableName)
+ {
+ return InsertTable(data, tableName, true);
+ }
+
+ public IXLTable InsertTable(DataTable data, string tableName, bool createTable)
+ {
+ if (data == null) return null;
+
+ if (data.Rows.Count > 0) return InsertTable(data.AsEnumerable(), tableName, createTable);
+
+ int ro = Address.RowNumber + 1;
+ int maxCo = Address.ColumnNumber + data.Columns.Count;
+
+ ClearMerged();
+ var range = _worksheet.Range(
+ Address.RowNumber,
+ Address.ColumnNumber,
+ ro - 1,
+ maxCo - 1);
+
+ if (createTable) return tableName == null ? range.CreateTable() : range.CreateTable(tableName);
+
+ return tableName == null ? range.AsTable() : range.AsTable(tableName);
+
+ }
+
public IXLRange InsertData(IEnumerable data)
{
if (data != null && data.GetType() != typeof(String))
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs
index f6f5c79..3521773 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs
@@ -377,6 +377,9 @@
InitializeAutoFilter();
HeadersRow().DataType = XLCellValues.Text;
+
+ if (RowCount() == 1)
+ InsertRowsBelow(1);
}
public void InitializeAutoFilter()
diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs
index ebd3ab7..7a08d77 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs
@@ -140,7 +140,7 @@
public IXLWorksheet Add(DataTable dataTable)
{
var ws = Add(dataTable.TableName);
- ws.Cell(1, 1).InsertTable(dataTable.AsEnumerable());
+ ws.Cell(1, 1).InsertTable(dataTable);
ws.Columns().AdjustToContents(1, 75);
return ws;
}
diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs
index 24707c4..324f28b 100644
--- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs
+++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs
@@ -1,4 +1,5 @@
using System;
+using System.Collections;
using System.Collections.Generic;
using System.Drawing;
using System.Globalization;
@@ -16,11 +17,7 @@
public static class Extensions
{
// Adds the .ForEach method to all IEnumerables
- public static void ForEach(this IEnumerable source, Action action)
- {
- foreach (T item in source)
- action(item);
- }
+
private static readonly char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'};
@@ -259,5 +256,19 @@
// }
//}
}
+
+ public static class EnumerableExtensions
+ {
+ public static void ForEach(this IEnumerable source, Action action)
+ {
+ foreach (T item in source)
+ action(item);
+ }
+
+ public static Type GetItemType(this IEnumerable source)
+ {
+ return typeof(T);
+ }
+ }
}
diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Tables/TablesTests.cs
index f7e2df0..e044eb8 100644
--- a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Tables/TablesTests.cs
+++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Tables/TablesTests.cs
@@ -1,4 +1,7 @@
-using ClosedXML.Excel;
+using System.Collections.Generic;
+using System.Data;
+using System.IO;
+using ClosedXML.Excel;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Linq;
@@ -183,5 +186,89 @@
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx");
}
+
+ [TestMethod]
+ public void CreatingATableFromHeadersPushCellsBelow()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().SetValue("Title")
+ .CellBelow().SetValue("X");
+ ws.Range("A1").CreateTable();
+
+ Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty);
+ Assert.AreEqual(ws.Cell("A3").GetString(), "X");
+ }
+
+
+ [TestMethod]
+ public void CanSaveTableCreatedFromSingleRow()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().SetValue("Title");
+ ws.Range("A1").CreateTable();
+
+ using (var ms = new MemoryStream())
+ wb.SaveAs(ms);
+
+ }
+
+ [TestMethod]
+ public void CanSaveTableCreatedFromEmptyDataTable()
+ {
+ var dt = new DataTable("sheet1");
+ dt.Columns.Add("col1", typeof(string));
+ dt.Columns.Add("col2", typeof(double));
+
+ var wb = new XLWorkbook();
+ wb.AddWorksheet(dt);
+
+ using (var ms = new MemoryStream())
+ wb.SaveAs(ms);
+
+ }
+
+ [TestMethod]
+ public void TableCreatedFromEmptyDataTable()
+ {
+ var dt = new DataTable("sheet1");
+ dt.Columns.Add("col1", typeof(string));
+ dt.Columns.Add("col2", typeof(double));
+
+ var wb = new XLWorkbook();
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().InsertTable(dt);
+ Assert.AreEqual(2, ws.Tables.First().ColumnCount());
+ }
+
+ [TestMethod]
+ public void TableCreatedFromEmptyListOfInt()
+ {
+ var l = new List();
+
+ var wb = new XLWorkbook();
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().InsertTable(l);
+ Assert.AreEqual(1, ws.Tables.First().ColumnCount());
+
+ }
+
+ public class TestObject
+ {
+ public String Column1 { get; set; }
+ public String Column2 { get; set; }
+ }
+ [TestMethod]
+ public void TableCreatedFromEmptyListOfObject()
+ {
+ var l = new List();
+
+ var wb = new XLWorkbook();
+ var ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().InsertTable(l);
+ Assert.AreEqual(2, ws.Tables.First().ColumnCount());
+
+ }
}
}