diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs index 1030c02..9a6fa97 100644 --- a/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/Excel/Tables/IXLTable.cs @@ -1,5 +1,6 @@ using System; using System.Collections.Generic; +using System.Data; namespace ClosedXML.Excel { @@ -116,5 +117,17 @@ IXLTable SetShowTotalsRow(Boolean value); IXLRangeRow TotalsRow(); + + /// + /// Converts the table to an enumerable of dynamic objects + /// + /// + IEnumerable AsDynamicEnumerable(); + + /// + /// Converts the table to a standard .NET System.Data.DataTable + /// + /// + DataTable AsNativeDataTable(); } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/Tables/IXLTableField.cs b/ClosedXML/Excel/Tables/IXLTableField.cs index 91ba4e8..abd8029 100644 --- a/ClosedXML/Excel/Tables/IXLTableField.cs +++ b/ClosedXML/Excel/Tables/IXLTableField.cs @@ -27,5 +27,10 @@ String TotalsRowLabel { get; set; } void Delete(); + + /// + /// Determines whether all cells this table field have a consistent data type. + /// + Boolean IsConsistentDataType(); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index c468d76..e10c5fe 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -1,5 +1,7 @@ using System; using System.Collections.Generic; +using System.Data; +using System.Dynamic; using System.Linq; using System.Text; @@ -753,5 +755,77 @@ columns.Cast().ForEach(column => column.Table = this); return columns; } + + public IEnumerable AsDynamicEnumerable() + { + foreach (var row in this.DataRange.Rows()) + { + dynamic expando = new ExpandoObject(); + foreach (var f in this.Fields) + { + var value = row.Cell(f.Index + 1).Value; + // ExpandoObject supports IDictionary so we can extend it like this + var expandoDict = expando as IDictionary; + if (expandoDict.ContainsKey(f.Name)) + expandoDict[f.Name] = value; + else + expandoDict.Add(f.Name, value); + } + + yield return expando; + } + } + + public DataTable AsNativeDataTable() + { + var table = new DataTable(this.Name); + + foreach (var f in Fields.Cast()) + { + Type type = typeof(object); + if (f.IsConsistentDataType()) + { + var c = f.Column.Cells().Skip(this.ShowHeaderRow ? 1 : 0).First(); + switch (c.DataType) + { + case XLCellValues.Text: + type = typeof(String); + break; + + case XLCellValues.Boolean: + type = typeof(Boolean); + break; + + case XLCellValues.DateTime: + type = typeof(DateTime); + break; + + case XLCellValues.TimeSpan: + type = typeof(TimeSpan); + break; + + case XLCellValues.Number: + type = typeof(Double); + break; + } + } + + table.Columns.Add(f.Name, type); + } + + foreach (var row in this.DataRange.Rows()) + { + var dr = table.NewRow(); + + foreach (var f in this.Fields) + { + dr[f.Name] = row.Cell(f.Index + 1).Value; + } + + table.Rows.Add(dr); + } + + return table; + } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index c737adc..f4bfe87 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -23,7 +23,14 @@ public IXLRangeColumn Column { - get { return _column ?? (_column = table.HeadersRow(false).Cell(this.Index + 1).AsRange().Columns().Single()); } + get + { + if (_column == null) + { + _column = this.table.AsRange().Column(this.Index + 1); + } + return _column; + } } private Int32 index; @@ -136,11 +143,27 @@ if (deleteUnderlyingRangeColumn) { table.AsRange().ColumnQuick(this.Index + 1).Delete(); - // (this.Column as XLRangeColumn).Delete(false); } fields.Where(f => f.Index > this.Index).ForEach(f => f.Index--); table.FieldNames.Remove(this.Name); } + + public bool IsConsistentDataType() + { + var dataTypes = this.Column + .Cells() + .Skip(this.table.ShowHeaderRow ? 1 : 0) + .Select(c => c.DataType); + + if (this.table.ShowTotalsRow) + dataTypes = dataTypes.Take(dataTypes.Count() - 1); + + var distinctDataTypes = dataTypes + .GroupBy(dt => dt) + .Select(g => new { Key = g.Key, Count = g.Count() }); + + return distinctDataTypes.Count() == 1; + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 25eeb50..9d30ab8 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -566,6 +566,73 @@ } [Test] + public void TableAsDynamicEnumerable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l); + + foreach (var d in table.AsDynamicEnumerable()) + { + Assert.DoesNotThrow(() => + { + object value; + value = d.FirstColumn; + value = d.SecondColumn; + value = d.UnOrderedColumn; + value = d.SomeFieldNotProperty; + }); + } + } + } + + [Test] + public void TableAsDotNetDataTable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l).AsNativeDataTable(); + + Assert.AreEqual(4, table.Columns.Count); + Assert.AreEqual("FirstColumn", table.Columns[0].ColumnName); + Assert.AreEqual("SecondColumn", table.Columns[1].ColumnName); + Assert.AreEqual("SomeFieldNotProperty", table.Columns[2].ColumnName); + Assert.AreEqual("UnOrderedColumn", table.Columns[3].ColumnName); + + Assert.AreEqual(typeof(String), table.Columns[0].DataType); + Assert.AreEqual(typeof(String), table.Columns[1].DataType); + Assert.AreEqual(typeof(Double), table.Columns[2].DataType); + Assert.AreEqual(typeof(Double), table.Columns[3].DataType); + + var dr = table.Rows[0]; + Assert.AreEqual("b", dr["FirstColumn"]); + Assert.AreEqual("a", dr["SecondColumn"]); + Assert.AreEqual(4, dr["SomeFieldNotProperty"]); + Assert.AreEqual(999, dr["UnOrderedColumn"]); + + dr = table.Rows[1]; + Assert.AreEqual("d", dr["FirstColumn"]); + Assert.AreEqual("c", dr["SecondColumn"]); + Assert.AreEqual(5, dr["SomeFieldNotProperty"]); + Assert.AreEqual(777, dr["UnOrderedColumn"]); + } + } + + [Test] public void TestTableCellTypes() { using (var wb = new XLWorkbook())