diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs
index a680486..0f714ef 100644
--- a/ClosedXML/Excel/Tables/IXLTable.cs
+++ b/ClosedXML/Excel/Tables/IXLTable.cs
@@ -1,4 +1,5 @@
using System;
+using System.Collections;
using System.Collections.Generic;
using System.Data;
@@ -32,7 +33,79 @@
IXLRangeRow HeadersRow();
///
- /// Resizes the table to the specified range.
+ /// Appends the IEnumerable data elements and returns the range of the new rows.
+ ///
+ /// The IEnumerable data.
+ ///
+ /// The range of the new rows.
+ ///
+ IXLRange AppendData(IEnumerable data);
+
+ ///
+ /// Appends the IEnumerable data elements and returns the range of the new rows.
+ ///
+ /// The IEnumerable data.
+ /// if set to true the data will be transposed before inserting.
+ ///
+ /// The range of the new rows.
+ ///
+ IXLRange AppendData(IEnumerable data, Boolean transpose);
+
+ ///
+ /// Appends the data of a data table and returns the range of the new rows.
+ ///
+ /// The data table.
+ ///
+ /// The range of the new rows.
+ ///
+ IXLRange AppendData(DataTable dataTable);
+
+ ///
+ /// Appends the IEnumerable data elements and returns the range of the new rows.
+ ///
+ ///
+ /// The table data.
+ ///
+ /// The range of the new rows.
+ ///
+ IXLRange AppendData(IEnumerable data);
+
+ ///
+ /// Replaces the IEnumerable data elements and returns the table's data range.
+ ///
+ /// The IEnumerable data.
+ /// The table's data range.
+ IXLRange ReplaceData(IEnumerable data);
+
+ ///
+ /// Replaces the IEnumerable data elements and returns the table's data range.
+ ///
+ /// The IEnumerable data.
+ /// if set to true the data will be transposed before inserting.
+ ///
+ /// The table's data range.
+ ///
+ IXLRange ReplaceData(IEnumerable data, Boolean transpose);
+
+ ///
+ /// Replaces the data from the records of a data table and returns the table's data range.
+ ///
+ /// The data table.
+ ///
+ /// The table's data range.
+ ///
+ IXLRange ReplaceData(DataTable dataTable);
+
+ ///
+ /// Replaces the IEnumerable data elements as a table and the table's data range.
+ ///
+ ///
+ /// The table data.
+ /// The table's data range.
+ IXLRange ReplaceData(IEnumerable data);
+
+ ///
+ /// Resizes the table to the specified range address.
///
/// The new table range.
///
diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs
index 3d82240..5841bb9 100644
--- a/ClosedXML/Excel/Tables/XLTable.cs
+++ b/ClosedXML/Excel/Tables/XLTable.cs
@@ -1,4 +1,5 @@
using System;
+using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
@@ -12,6 +13,7 @@
internal class XLTable : XLRange, IXLTable
{
#region Private fields
+
private string _name;
internal bool _showTotalsRow;
internal HashSet _uniqueNames;
@@ -145,18 +147,19 @@
{
XLRange range;
+ var firstDataRowNumber = 1;
+ var lastDataRowNumber = RowCount();
+
if (_showHeaderRow)
- {
- range = _showTotalsRow
- ? Range(2, 1, RowCount() - 1, ColumnCount())
- : Range(2, 1, RowCount(), ColumnCount());
- }
- else
- {
- range = _showTotalsRow
- ? Range(1, 1, RowCount() - 1, ColumnCount())
- : Range(1, 1, RowCount(), ColumnCount());
- }
+ firstDataRowNumber++;
+
+ if (_showTotalsRow)
+ lastDataRowNumber--;
+
+ if (firstDataRowNumber > lastDataRowNumber)
+ return null;
+
+ range = Range(firstDataRowNumber, 1, lastDataRowNumber, ColumnCount());
return new XLTableRange(range, this);
}
@@ -822,7 +825,7 @@
public IXLTable CopyTo(IXLWorksheet targetSheet)
{
- return CopyTo((XLWorksheet) targetSheet);
+ return CopyTo((XLWorksheet)targetSheet);
}
internal IXLTable CopyTo(XLWorksheet targetSheet, bool copyData = true)
@@ -860,5 +863,106 @@
}
return newTable;
}
+
+ #region Append and replace data
+
+ public IXLRange AppendData(IEnumerable data)
+ {
+ return AppendData(data, false);
+ }
+
+ public IXLRange AppendData(IEnumerable data, bool transpose)
+ {
+ var castedData = data?.Cast();
+ if (!(castedData?.Any() ?? false) || data is String)
+ return null;
+
+ var numberOfNewRows = castedData.Count();
+
+ var lastRowOfOldRange = this.DataRange.LastRow();
+ lastRowOfOldRange.InsertRowsBelow(numberOfNewRows);
+
+ return lastRowOfOldRange.RowBelow().FirstCell().InsertData(castedData, transpose);
+ }
+
+ public IXLRange AppendData(DataTable dataTable)
+ {
+ return AppendData(dataTable.Rows.Cast());
+ }
+
+ public IXLRange AppendData(IEnumerable data)
+ {
+ if (!(data?.Any() ?? false) || data is String)
+ return null;
+
+ var numberOfNewRows = data.Count();
+
+ if (numberOfNewRows == 0)
+ return null;
+
+ var lastRowOfOldRange = this.DataRange.LastRow();
+ lastRowOfOldRange.InsertRowsBelow(numberOfNewRows);
+
+ return lastRowOfOldRange.RowBelow().FirstCell().InsertData(data);
+ }
+
+ public IXLRange ReplaceData(IEnumerable data)
+ {
+ return ReplaceData(data, false);
+ }
+
+ public IXLRange ReplaceData(IEnumerable data, bool transpose)
+ {
+ var castedData = data?.Cast();
+ if (!(castedData?.Any() ?? false) || data is String)
+ throw new InvalidOperationException("Cannot replace table data with empty enumerable.");
+
+ var firstDataRowNumber = this.DataRange.FirstRow().RowNumber();
+ var lastDataRowNumber = this.DataRange.LastRow().RowNumber();
+
+ // Resize table
+ var sizeDifference = castedData.Count() - this.DataRange.RowCount();
+ if (sizeDifference > 0)
+ this.DataRange.LastRow().InsertRowsBelow(sizeDifference);
+ else if (sizeDifference < 0)
+ this.DataRange.Rows
+ (
+ lastDataRowNumber + sizeDifference + 1 - firstDataRowNumber + 1,
+ lastDataRowNumber - firstDataRowNumber + 1
+ )
+ .Delete();
+
+ return this.DataRange.FirstCell().InsertData(castedData, transpose);
+ }
+
+ public IXLRange ReplaceData(DataTable dataTable)
+ {
+ return ReplaceData(dataTable.Rows.Cast());
+ }
+
+ public IXLRange ReplaceData(IEnumerable data)
+ {
+ if (!(data?.Any() ?? false) || data is String)
+ throw new InvalidOperationException("Cannot replace table data with empty enumerable.");
+
+ var firstDataRowNumber = this.DataRange.FirstRow().RowNumber();
+ var lastDataRowNumber = this.DataRange.LastRow().RowNumber();
+
+ // Resize table
+ var sizeDifference = data.Count() - this.DataRange.RowCount();
+ if (sizeDifference > 0)
+ this.DataRange.LastRow().InsertRowsBelow(sizeDifference);
+ else if (sizeDifference < 0)
+ this.DataRange.Rows
+ (
+ lastDataRowNumber + sizeDifference + 1 - firstDataRowNumber + 1,
+ lastDataRowNumber - firstDataRowNumber + 1
+ )
+ .Delete();
+
+ return this.DataRange.FirstCell().InsertData(data);
+ }
+
+ #endregion Append and replace data
}
}
diff --git a/ClosedXML_Tests/Excel/Tables/AddingAndReplacingTableDataTests.cs b/ClosedXML_Tests/Excel/Tables/AddingAndReplacingTableDataTests.cs
new file mode 100644
index 0000000..5dbd18d
--- /dev/null
+++ b/ClosedXML_Tests/Excel/Tables/AddingAndReplacingTableDataTests.cs
@@ -0,0 +1,452 @@
+using ClosedXML.Attributes;
+using ClosedXML.Excel;
+using NUnit.Framework;
+using System;
+using System.Collections;
+using System.Collections.Generic;
+using System.IO;
+using System.Linq;
+
+namespace ClosedXML_Tests.Excel.Tables
+{
+ [TestFixture]
+ public class AppendingAndReplacingTableDataTests
+ {
+ public class TestObjectWithoutAttributes
+ {
+ public String Column1 { get; set; }
+ public String Column2 { get; set; }
+ }
+
+ public class Person
+ {
+ public int Age { get; set; }
+
+ [XLColumn(Header = "Last name", Order = 2)]
+ public String LastName { get; set; }
+
+ [XLColumn(Header = "First name", Order = 1)]
+ public String FirstName { get; set; }
+
+ [XLColumn(Header = "Full name", Order = 0)]
+ public String FullName { get => string.Concat(FirstName, " ", LastName); }
+
+ [XLColumn(Order = 3)]
+ public DateTime DateOfBirth { get; set; }
+
+ [XLColumn(Header = "Is active", Order = 4)]
+ public bool IsActive;
+ }
+
+ private XLWorkbook PrepareWorkbook()
+ {
+ var wb = new XLWorkbook();
+ var ws = wb.AddWorksheet("Tables");
+
+ var data = new[]
+ {
+ new Person{FirstName = "Francois", LastName = "Botha", Age = 39, DateOfBirth = new DateTime(1980,1,1), IsActive = true},
+ new Person{FirstName = "Leon", LastName = "Oosthuizen", Age = 40, DateOfBirth = new DateTime(1979,1,1), IsActive = false},
+ new Person{FirstName = "Rian", LastName = "Prinsloo", Age = 41, DateOfBirth = new DateTime(1978,1,1), IsActive = false}
+ };
+
+ ws.FirstCell().CellRight().CellBelow().InsertTable(data);
+
+ ws.Columns().AdjustToContents();
+
+ return wb;
+ }
+
+ private Person[] NewData
+ {
+ get
+ {
+ return new[]
+ {
+ new Person{FirstName = "Michelle", LastName = "de Beer", Age = 35, DateOfBirth = new DateTime(1983,1,1), IsActive = false},
+ new Person{FirstName = "Marichen", LastName = "van der Gryp", Age = 30, DateOfBirth = new DateTime(1990,1,1), IsActive = true}
+ };
+ }
+ }
+
+ [Test]
+ public void AddingEmptyEnumerables()
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ IEnumerable personEnumerable = null;
+ Assert.AreEqual(null, table.AppendData(personEnumerable));
+
+ personEnumerable = new Person[] { };
+ Assert.AreEqual(null, table.AppendData(personEnumerable));
+
+ IEnumerable enumerable = null;
+ Assert.AreEqual(null, table.AppendData(enumerable));
+
+ enumerable = new Person[] { };
+ Assert.AreEqual(null, table.AppendData(enumerable));
+ }
+ }
+
+ [Test]
+ public void ReplaceWithEmptyEnumerables()
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ IEnumerable personEnumerable = null;
+ Assert.Throws(() => table.ReplaceData(personEnumerable));
+
+ personEnumerable = new Person[] { };
+ Assert.Throws(() => table.ReplaceData(personEnumerable));
+
+ IEnumerable enumerable = null;
+ Assert.Throws(() => table.ReplaceData(enumerable));
+
+ enumerable = new Person[] { };
+ Assert.Throws(() => table.ReplaceData(enumerable));
+ }
+ }
+
+ [Test]
+ public void CanAppendTypedEnumerable()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ IEnumerable personEnumerable = NewData;
+ var addedRange = table.AppendData(personEnumerable);
+
+ Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(5, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanAppendToTableWithTotalsRow()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+ table.SetShowTotalsRow(true);
+ table.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Average;
+
+ IEnumerable personEnumerable = NewData;
+ var addedRange = table.AppendData(personEnumerable);
+
+ Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(5, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanAppendTypedEnumerableAndPushDownCellsBelowTable()
+ {
+ using (var ms = new MemoryStream())
+ {
+ var value = "Some value that will be overwritten";
+ IXLAddress address;
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ var cell = table.LastRow().FirstCell().CellRight(2).CellBelow(1);
+ address = cell.Address;
+ cell.Value = value;
+
+ IEnumerable personEnumerable = NewData;
+ var addedRange = table.AppendData(personEnumerable);
+
+ Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ var cell = ws.Cell(address);
+ Assert.AreEqual("de Beer", cell.Value);
+ Assert.AreEqual(5, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+
+ Assert.AreEqual(value, cell.CellBelow(NewData.Count()).Value);
+ }
+ }
+ }
+
+ [Test]
+ public void CanAppendUntypedEnumerable()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ var list = new ArrayList();
+ list.AddRange(NewData);
+
+ var addedRange = table.AppendData(list);
+
+ Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString());
+
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(5, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanAppendDataTable()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ IEnumerable personEnumerable = NewData;
+
+ var ws2 = wb.AddWorksheet("temp");
+ var dataTable = ws2.FirstCell().InsertTable(personEnumerable).AsNativeDataTable();
+
+ var addedRange = table.AppendData(dataTable);
+
+ Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(5, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanReplaceWithTypedEnumerable()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ IEnumerable personEnumerable = NewData;
+ var replacedRange = table.ReplaceData(personEnumerable);
+
+ Assert.AreEqual("B3:G4", replacedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(2, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanReplaceWithUntypedEnumerable()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ var list = new ArrayList();
+ list.AddRange(NewData);
+
+ var replacedRange = table.ReplaceData(list);
+
+ Assert.AreEqual("B3:G4", replacedRange.RangeAddress.ToString());
+
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(2, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanReplaceWithDataTable()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+
+ IEnumerable personEnumerable = NewData;
+
+ var ws2 = wb.AddWorksheet("temp");
+ var dataTable = ws2.FirstCell().InsertTable(personEnumerable).AsNativeDataTable();
+
+ var replacedRange = table.ReplaceData(dataTable);
+
+ Assert.AreEqual("B3:G4", replacedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(2, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanReplaceToTableWithTablesRow1()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+ table.SetShowTotalsRow(true);
+ table.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Average;
+
+ // Will cause table to overflow
+ IEnumerable personEnumerable = NewData.Union(NewData).Union(NewData);
+ var replacedRange = table.ReplaceData(personEnumerable);
+
+ Assert.AreEqual("B3:G8", replacedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(6, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+
+ [Test]
+ public void CanReplaceToTableWithTablesRow2()
+ {
+ using (var ms = new MemoryStream())
+ {
+ using (var wb = PrepareWorkbook())
+ {
+ var ws = wb.Worksheets.First();
+
+ var table = ws.Tables.First();
+ table.SetShowTotalsRow(true);
+ table.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Average;
+
+ // Will cause table to shrink
+ IEnumerable personEnumerable = NewData.Take(1);
+ var replacedRange = table.ReplaceData(personEnumerable);
+
+ Assert.AreEqual("B3:G3", replacedRange.RangeAddress.ToString());
+ ws.Columns().AdjustToContents();
+
+ wb.SaveAs(ms);
+ }
+
+ using (var wb = new XLWorkbook(ms))
+ {
+ var table = wb.Worksheets.SelectMany(ws => ws.Tables).First();
+
+ Assert.AreEqual(1, table.DataRange.RowCount());
+ Assert.AreEqual(6, table.DataRange.ColumnCount());
+ }
+ }
+ }
+ }
+}
diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs
index 8cd32a8..d6cb000 100644
--- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs
+++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs
@@ -112,6 +112,25 @@
}
[Test]
+ public void DataRange_returns_null_if_empty()
+ {
+ using (var wb = new XLWorkbook())
+ {
+ IXLWorksheet ws = wb.AddWorksheet("Sheet1");
+ ws.FirstCell().SetValue("Categories")
+ .CellBelow().SetValue("A")
+ .CellBelow().SetValue("B")
+ .CellBelow().SetValue("C");
+
+ IXLTable table = ws.RangeUsed().CreateTable();
+
+ ws.Rows("2:4").Delete();
+
+ Assert.IsNull(table.DataRange);
+ }
+ }
+
+ [Test]
public void SavingLoadingTableWithNewLineInHeader()
{
using (var wb = new XLWorkbook())