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()); + + } } }