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())