diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index e5672c9..4976654 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -221,11 +221,23 @@ public IXLCell SetValue(T value) { + return SetValue(value, true); + } + + internal IXLCell SetValue(T value, bool setTableHeader) + { if (value == null) return this.Clear(XLClearOptions.Contents); FormulaA1 = String.Empty; _richText = null; + + if (setTableHeader) + { + if (SetTableHeader(value)) return this; + CheckOverWriteTableFooter(); + } + var style = GetStyleForRead(); if (value is String || value is char) { @@ -507,12 +519,12 @@ if (String.IsNullOrWhiteSpace(fieldName)) fieldName = itemType.Name; - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); hasTitles = true; co = Address.ColumnNumber; } - SetValue(o, ro, co); + _worksheet.SetValue(o, ro, co); co++; if (co > maxCo) @@ -569,7 +581,7 @@ { foreach (var item in (m as Array)) { - SetValue(item, ro, co); + _worksheet.SetValue(item, ro, co); co++; } } @@ -586,7 +598,7 @@ ? column.ColumnName : column.Caption) { - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); co++; } @@ -596,7 +608,7 @@ foreach (var item in row.ItemArray) { - SetValue(item, ro, co); + _worksheet.SetValue(item, ro, co); co++; } } @@ -612,7 +624,7 @@ { for (var i = 0; i < fieldCount; i++) { - SetValue(record.GetName(i), fRo, co); + _worksheet.SetValue(record.GetName(i), fRo, co); co++; } @@ -622,7 +634,7 @@ for (var i = 0; i < fieldCount; i++) { - SetValue(record[i], ro, co); + _worksheet.SetValue(record[i], ro, co); co++; } } @@ -638,7 +650,7 @@ if (String.IsNullOrWhiteSpace(fieldName)) fieldName = mi.Name; - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); } co++; @@ -650,7 +662,7 @@ foreach (var mi in members) { - SetValue(accessor[m, mi.Name], ro, co); + _worksheet.SetValue(accessor[m, mi.Name], ro, co); co++; } } @@ -706,7 +718,7 @@ foreach (DataColumn col in data.Columns) { - SetValue(col.ColumnName, ro, co); + _worksheet.SetValue(col.ColumnName, ro, co); co++; } @@ -772,7 +784,7 @@ if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) { - SetValue(m, rowNumber, columnNumber); + _worksheet.SetValue(m, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -783,7 +795,7 @@ { foreach (var item in (Array)m) { - SetValue(item, rowNumber, columnNumber); + _worksheet.SetValue(item, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -798,7 +810,7 @@ foreach (var item in (m as DataRow).ItemArray) { - SetValue(item, rowNumber, columnNumber); + _worksheet.SetValue(item, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -816,7 +828,7 @@ var fieldCount = record.FieldCount; for (var i = 0; i < fieldCount; i++) { - SetValue(record[i], rowNumber, columnNumber); + _worksheet.SetValue(record[i], rowNumber, columnNumber); if (transpose) rowNumber++; @@ -828,7 +840,7 @@ { foreach (var mi in members) { - SetValue(accessor[m, mi.Name], rowNumber, columnNumber); + _worksheet.SetValue(accessor[m, mi.Name], rowNumber, columnNumber); if (transpose) rowNumber++; @@ -1515,6 +1527,16 @@ return false; } + private void CheckOverWriteTableFooter() + { + foreach (var table in Worksheet.Tables.Where(t => t.ShowTotalsRow)) + { + var cells = table.TotalsRow().Cells(c => c.Address.Equals(this.Address)); + if (cells.Any()) + throw new InvalidOperationException(String.Format("Inserted data will overwrite totals row cell {0}.", this.Address)); + } + } + private bool SetRangeColumns(object value) { var columns = value as XLRangeColumns; @@ -1700,15 +1722,6 @@ mergeToDelete.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); } - private void SetValue(T value, int ro, int co) where T : class - { - if (value == null) - _worksheet.Cell(ro, co).SetValue(String.Empty); - else if (value is IConvertible) - _worksheet.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); - else - _worksheet.Cell(ro, co).SetValue(value); - } private void SetValue(object value) { @@ -1787,6 +1800,9 @@ } } if (val.Length > 32767) throw new ArgumentException("Cells can only hold 32,767 characters."); + + if (SetTableHeader(val)) return; + _cellValue = val; } diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs index 386c669..f08c8d2 100644 --- a/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/Excel/Tables/IXLTable.cs @@ -41,5 +41,7 @@ IXLTable SetShowHeaderRow(); IXLTable SetShowHeaderRow(Boolean value); IXLTableRange DataRange { get; } + + void Resize(IXLRange range); } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 6048e33..1520843 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -253,6 +253,34 @@ } } + public void Resize(IXLRange range) + { + if (!this.ShowHeaderRow) + throw new NotImplementedException("Resizing of tables with no headers not supported yet."); + + if (this.ShowTotalsRow) + throw new NotImplementedException("Resizing of tables with a totals row not supported yet."); + + this.RangeAddress = range.RangeAddress as XLRangeAddress; + + var firstRow = range.Row(1); + if (!firstRow.FirstCell().Address.Equals(this.HeadersRow().FirstCell().Address) + || !firstRow.LastCell().Address.Equals(this.HeadersRow().LastCell().Address)) + { + _uniqueNames.Clear(); + var co = this.Fields.Count() - 1; + foreach (var c in firstRow.Cells()) + { + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + c.Value = GetUniqueName("Column" + co.ToInvariantString()); + _uniqueNames.Add(c.GetString()); + co++; + } + } + + _fieldNames = null; + } + public IXLTable SetEmphasizeFirstColumn() { EmphasizeFirstColumn = true; diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 528d7d2..ca7f8d8 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -35,7 +35,7 @@ set { if (table.ShowHeaderRow) - table.HeadersRow().Cell(Index + 1).SetValue(value); + (table.HeadersRow().Cell(Index + 1) as XLCell).SetValue(value, false); table.RenameField(name, value); name = value; @@ -101,7 +101,7 @@ set { totalsRowFunction = XLTotalsRowFunction.None; - table.TotalsRow().Cell(Index + 1).SetValue(value); + (table.TotalsRow().Cell(Index + 1) as XLCell).SetValue(value, false); totalsRowLabel = value; } } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index c40ad1b..6b5348a 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1595,5 +1595,16 @@ { return true; } + + internal void SetValue(T value, int ro, int co) where T : class + { + if (value == null) + this.Cell(ro, co).SetValue(String.Empty); + else if (value is IConvertible) + this.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); + else + this.Cell(ro, co).SetValue(value); + } + } } diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 9f4871b..13237ec 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -113,6 +113,7 @@ + diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 1e639dd..2afcf33 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -67,6 +67,7 @@ new RowCells().Create(Path.Combine(path, "RowCells.xlsx")); new FreezePanes().Create(Path.Combine(path, "FreezePanes.xlsx")); new UsingTables().Create(Path.Combine(path, "UsingTables.xlsx")); + new ResizingTables().Create(Path.Combine(path, "ResizingTables.xlsx")); new AddingRowToTables().Create(Path.Combine(path, "AddingRowToTables.xlsx")); new RightToLeft().Create(Path.Combine(path, "RightToLeft.xlsx")); new ShowCase().Create(Path.Combine(path, "ShowCase.xlsx")); diff --git a/ClosedXML_Examples/Ranges/ResizingTables.cs b/ClosedXML_Examples/Ranges/ResizingTables.cs new file mode 100644 index 0000000..38b7db0 --- /dev/null +++ b/ClosedXML_Examples/Ranges/ResizingTables.cs @@ -0,0 +1,41 @@ +using ClosedXML.Excel; +using System.IO; +using System.Linq; + +namespace ClosedXML_Examples.Ranges +{ + public class ResizingTables : IXLExample + { + public void Create(string filePath) + { + string tempFile = ExampleHelper.GetTempFilePath(filePath); + try + { + new UsingTables().Create(tempFile); + using (var wb = new XLWorkbook(tempFile)) + { + var ws1 = wb.Worksheets.First(); + + var ws2 = ws1.CopyTo("Contacts 2"); + ws2.Cell("A2").Value = "Index"; + ws2.Cell("A3").Value = Enumerable.Range(1, 3).ToArray(); + var table2 = ws2.Tables.First().SetShowTotalsRow(false); + table2.Resize(ws2.Range(ws2.Cell("A2"), table2.DataRange.LastCell())); + + var ws3 = ws1.CopyTo("Contacts 3"); + var table3 = ws3.Tables.First().SetShowTotalsRow(false); + table3.Resize(ws3.Range(table3.AsRange().FirstCell(), table3.DataRange.LastCell().CellLeft())); + + wb.SaveAs(filePath); + } + } + finally + { + if (File.Exists(tempFile)) + { + File.Delete(tempFile); + } + } + } + } +} diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 8059cad..0531fa7 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -291,6 +291,7 @@ + diff --git a/ClosedXML_Tests/Examples/RangesTests.cs b/ClosedXML_Tests/Examples/RangesTests.cs index fe3e1e2..5d78654 100644 --- a/ClosedXML_Tests/Examples/RangesTests.cs +++ b/ClosedXML_Tests/Examples/RangesTests.cs @@ -105,6 +105,12 @@ } [Test] + public void ResizingTables() + { + TestHelper.RunTestExample(@"Ranges\ResizingTables.xlsx"); + } + + [Test] public void AddingRowToTables() { TestHelper.RunTestExample(@"Ranges\AddingRowToTables.xlsx"); @@ -116,4 +122,4 @@ TestHelper.RunTestExample(@"Ranges\WalkingRanges.xlsx"); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ResizingTables.xlsx new file mode 100644 index 0000000..9259bba --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ResizingTables.xlsx Binary files differ