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