diff --git a/ClosedXML/Excel/IXLWorksheets.cs b/ClosedXML/Excel/IXLWorksheets.cs index f811507..180c304 100644 --- a/ClosedXML/Excel/IXLWorksheets.cs +++ b/ClosedXML/Excel/IXLWorksheets.cs @@ -4,21 +4,28 @@ namespace ClosedXML.Excel { - public interface IXLWorksheets: IEnumerable + public interface IXLWorksheets : IEnumerable { int Count { get; } - bool TryGetWorksheet(string sheetName,out IXLWorksheet worksheet); + + bool TryGetWorksheet(string sheetName, out IXLWorksheet worksheet); IXLWorksheet Worksheet(String sheetName); - IXLWorksheet Worksheet(Int32 position); - IXLWorksheet Add(String sheetName); - IXLWorksheet Add(String sheetName, Int32 position); - IXLWorksheet Add(DataTable dataTable); - IXLWorksheet Add(DataTable dataTable, String sheetName); - void Add(DataSet dataSet); - void Delete(String sheetName); - void Delete(Int32 position); - + IXLWorksheet Worksheet(Int32 position); + + IXLWorksheet Add(String sheetName); + + IXLWorksheet Add(String sheetName, Int32 position); + + IXLWorksheet Add(DataTable dataTable); + + IXLWorksheet Add(DataTable dataTable, String sheetName); + + void Add(DataSet dataSet); + + void Delete(String sheetName); + + void Delete(Int32 position); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index d42ddf1..b5677e9 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -219,13 +219,39 @@ get { return _name; } set { - if (Worksheet.Tables.Any(t => t.Name == value)) - { - throw new ArgumentException(String.Format("This worksheet already contains a table named '{0}'", - value)); - } + if (_name == value) return; + + // Validation rules for table names + var oldname = _name ?? string.Empty; + + if (XLHelper.IsNullOrWhiteSpace(value)) + throw new ArgumentException($"The table name '{value}' is invalid"); + + // Table names are case insensitive + if (!oldname.Equals(value, StringComparison.OrdinalIgnoreCase) + && Worksheet.Tables.Any(t => t.Name.Equals(value, StringComparison.OrdinalIgnoreCase))) + throw new ArgumentException($"This worksheet already contains a table named '{value}'"); + + if (value[0] != '_' && !char.IsLetter(value[0])) + throw new ArgumentException($"The table name '{value}' does not begin with a letter or an underscore"); + + if (value.Length > 255) + throw new ArgumentException("The table name is more than 255 characters"); + + if (new[] { 'C', 'R' }.Any(c => value.ToUpper().Equals(c.ToString()))) + throw new ArgumentException($"The table name '{value}' is invalid"); _name = value; + + // Some totals row formula depend on the table name. Update them. + if (_fieldNames?.Any() ?? false) + this.Fields.ForEach(f => (f as XLTableField).UpdateTableFieldTotalsRowFormula()); + + if (!XLHelper.IsNullOrWhiteSpace(oldname)) + { + Worksheet.Tables.Add(this); + Worksheet.Tables.Remove(oldname); + } } } @@ -337,6 +363,8 @@ var existingHeaders = this.FieldNames.Keys; var newHeaders = new HashSet(); + + // Force evaluation of f.Column field var tempArray = this.Fields.Select(f => f.Column).ToArray(); var firstRow = range.Row(1); @@ -391,7 +419,7 @@ { foreach (var f in this._fieldNames.Values.Cast()) { - f.UpdateUnderlyingCellFormula(); + f.UpdateTableFieldTotalsRowFormula(); var c = this.TotalsRow().Cell(f.Index + 1); if (!XLHelper.IsNullOrWhiteSpace(f.TotalsRowLabel)) { diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 03b47e9..0a975dd 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -1,4 +1,5 @@ using System; +using System.Collections.Generic; using System.Diagnostics; using System.Linq; @@ -89,7 +90,7 @@ set { totalsRowFunction = value; - UpdateUnderlyingCellFormula(); + UpdateTableFieldTotalsRowFormula(); } } @@ -170,30 +171,40 @@ .GroupBy(f => f) .Select(g => new { Key = g.Key, Count = g.Count() }); - var ie = distinctStyles.First().Key.Equals(distinctStyles.Last().Key); - return distinctStyles.Count() == 1; } - internal void UpdateUnderlyingCellFormula() + private static IEnumerable QuotedTableFieldCharacters = new[] { "'", "#" }; + + internal void UpdateTableFieldTotalsRowFormula() { if (TotalsRowFunction != XLTotalsRowFunction.None && TotalsRowFunction != XLTotalsRowFunction.Custom) { var cell = table.TotalsRow().Cell(Index + 1); - String formula = String.Empty; + var formulaCode = String.Empty; switch (TotalsRowFunction) { - case XLTotalsRowFunction.Sum: formula = "109"; break; - case XLTotalsRowFunction.Minimum: formula = "105"; break; - case XLTotalsRowFunction.Maximum: formula = "104"; break; - case XLTotalsRowFunction.Average: formula = "101"; break; - case XLTotalsRowFunction.Count: formula = "103"; break; - case XLTotalsRowFunction.CountNumbers: formula = "102"; break; - case XLTotalsRowFunction.StandardDeviation: formula = "107"; break; - case XLTotalsRowFunction.Variance: formula = "110"; break; + case XLTotalsRowFunction.Sum: formulaCode = "109"; break; + case XLTotalsRowFunction.Minimum: formulaCode = "105"; break; + case XLTotalsRowFunction.Maximum: formulaCode = "104"; break; + case XLTotalsRowFunction.Average: formulaCode = "101"; break; + case XLTotalsRowFunction.Count: formulaCode = "103"; break; + case XLTotalsRowFunction.CountNumbers: formulaCode = "102"; break; + case XLTotalsRowFunction.StandardDeviation: formulaCode = "107"; break; + case XLTotalsRowFunction.Variance: formulaCode = "110"; break; } - cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; + var modifiedName = Name; + QuotedTableFieldCharacters.ForEach(c => modifiedName = modifiedName.Replace(c, "'" + c)); + + if (modifiedName.StartsWith(" ") || modifiedName.EndsWith(" ")) + { + modifiedName = "[" + modifiedName + "]"; + } + + var prependTableName = modifiedName.Contains(" "); + + cell.FormulaA1 = $"SUBTOTAL({formulaCode},{(prependTableName ? table.Name : string.Empty)}[{modifiedName}])"; var lastCell = table.LastRow().Cell(Index + 1); if (lastCell.DataType != XLDataType.Text) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 0ba0cdc..47d6668 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -3422,8 +3422,6 @@ { foreach (var cf in ws.ConditionalFormats) { - //var ie = context.DifferentialFormats.Keys.First().Equals(cf.Style); - if (!cf.Style.Equals(DefaultStyle) && !context.DifferentialFormats.ContainsKey(cf.Style)) AddConditionalDifferentialFormat(workbookStylesPart.Stylesheet.DifferentialFormats, cf, context); } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 9d2ba32..eaf1280 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -197,7 +197,7 @@ set { if (value > Workbook.WorksheetsInternal.Count + Workbook.UnsupportedSheets.Count + 1) - throw new IndexOutOfRangeException("Index must be equal or less than the number of worksheets + 1."); + throw new ArgumentOutOfRangeException(nameof(value), "Index must be equal or less than the number of worksheets + 1."); if (value < _position) { diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 5df66aa..e5868fa 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -44,11 +44,11 @@ using (var wb = new XLWorkbook()) { - wb.AddWorksheet(dt); + wb.AddWorksheet(dt); - using (var ms = new MemoryStream()) - wb.SaveAs(ms, true); - } + using (var ms = new MemoryStream()) + wb.SaveAs(ms, true); + } } [Test] @@ -70,13 +70,13 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Title"); - ws.Range("A1").CreateTable(); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Title"); + ws.Range("A1").CreateTable(); - using (var ms = new MemoryStream()) - wb.SaveAs(ms, true); - } + using (var ms = new MemoryStream()) + wb.SaveAs(ms, true); + } } [Test] @@ -84,14 +84,14 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Title") - .CellBelow().SetValue("X"); - ws.Range("A1").CreateTable(); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Title") + .CellBelow().SetValue("X"); + ws.Range("A1").CreateTable(); - Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); - Assert.AreEqual(ws.Cell("A3").GetString(), "X"); - } + Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); + Assert.AreEqual(ws.Cell("A3").GetString(), "X"); + } } [Test] @@ -99,16 +99,16 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Categories") - .CellBelow().SetValue("A") - .CellBelow().SetValue("B") - .CellBelow().SetValue("C"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); - IXLTable table = ws.RangeUsed().CreateTable(); - table.InsertColumnsAfter(1); - Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString()); - } + IXLTable table = ws.RangeUsed().CreateTable(); + table.InsertColumnsAfter(1); + Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString()); + } } [Test] @@ -116,54 +116,54 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - string columnName = "Line1" + Environment.NewLine + "Line2"; - ws.FirstCell().SetValue(columnName) - .CellBelow().SetValue("A"); - ws.RangeUsed().CreateTable(); - using (var ms = new MemoryStream()) - { - wb.SaveAs(ms, true); - var wb2 = new XLWorkbook(ms); - IXLWorksheet ws2 = wb2.Worksheet(1); - IXLTable table2 = ws2.Table(0); - string fieldName = table2.Field(0).Name; - Assert.AreEqual("Line1\nLine2", fieldName); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + string columnName = "Line1" + Environment.NewLine + "Line2"; + ws.FirstCell().SetValue(columnName) + .CellBelow().SetValue("A"); + ws.RangeUsed().CreateTable(); + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + var wb2 = new XLWorkbook(ms); + IXLWorksheet ws2 = wb2.Worksheet(1); + IXLTable table2 = ws2.Table(0); + string fieldName = table2.Field(0).Name; + Assert.AreEqual("Line1\nLine2", fieldName); + } } } - } [Test] public void SavingLoadingTableWithNewLineInHeader2() { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.Worksheets.Add("Test"); + IXLWorksheet ws = wb.Worksheets.Add("Test"); - var dt = new DataTable(); - string columnName = "Line1" + Environment.NewLine + "Line2"; - dt.Columns.Add(columnName); + var dt = new DataTable(); + string columnName = "Line1" + Environment.NewLine + "Line2"; + dt.Columns.Add(columnName); - DataRow dr = dt.NewRow(); - dr[columnName] = "some text"; - dt.Rows.Add(dr); + DataRow dr = dt.NewRow(); + dr[columnName] = "some text"; + dt.Rows.Add(dr); ws.Cell(1, 1).InsertTable(dt); - IXLTable table1 = ws.Table(0); - string fieldName1 = table1.Field(0).Name; - Assert.AreEqual(columnName, fieldName1); + IXLTable table1 = ws.Table(0); + string fieldName1 = table1.Field(0).Name; + Assert.AreEqual(columnName, fieldName1); - using (var ms = new MemoryStream()) - { - wb.SaveAs(ms, true); - var wb2 = new XLWorkbook(ms); - IXLWorksheet ws2 = wb2.Worksheet(1); - IXLTable table2 = ws2.Table(0); - string fieldName2 = table2.Field(0).Name; - Assert.AreEqual("Line1\nLine2", fieldName2); + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + var wb2 = new XLWorkbook(ms); + IXLWorksheet ws2 = wb2.Worksheet(1); + IXLTable table2 = ws2.Table(0); + string fieldName2 = table2.Field(0).Name; + Assert.AreEqual("Line1\nLine2", fieldName2); + } } } - } [Test] public void TableCreatedFromEmptyDataTable() @@ -174,10 +174,10 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(dt); - Assert.AreEqual(2, ws.Tables.First().ColumnCount()); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(dt); + Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + } } [Test] @@ -187,10 +187,10 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(1, ws.Tables.First().ColumnCount()); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(1, ws.Tables.First().ColumnCount()); + } } [Test] @@ -200,10 +200,10 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(2, ws.Tables.First().ColumnCount()); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + } } [Test] @@ -217,14 +217,14 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(4, ws.Tables.First().ColumnCount()); - Assert.AreEqual("FirstColumn", ws.FirstCell().Value); - Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); - Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); - Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(4, ws.Tables.First().ColumnCount()); + Assert.AreEqual("FirstColumn", ws.FirstCell().Value); + Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); + Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); + Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); + } } [Test] @@ -232,24 +232,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 3; - row = table.DataRange.InsertRowsAbove(1).First(); - row.Field("Value").Value = 2; - row = table.DataRange.InsertRowsAbove(1).First(); - row.Field("Value").Value = 1; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 3; + row = table.DataRange.InsertRowsAbove(1).First(); + row.Field("Value").Value = 2; + row = table.DataRange.InsertRowsAbove(1).First(); + row.Field("Value").Value = 1; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -257,24 +257,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 3; - row = row.InsertRowsAbove(1).First(); - row.Field("Value").Value = 2; - row = row.InsertRowsAbove(1).First(); - row.Field("Value").Value = 1; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 3; + row = row.InsertRowsAbove(1).First(); + row.Field("Value").Value = 2; + row = row.InsertRowsAbove(1).First(); + row.Field("Value").Value = 1; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -282,24 +282,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 1; - row = table.DataRange.InsertRowsBelow(1).First(); - row.Field("Value").Value = 2; - row = table.DataRange.InsertRowsBelow(1).First(); - row.Field("Value").Value = 3; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 1; + row = table.DataRange.InsertRowsBelow(1).First(); + row.Field("Value").Value = 2; + row = table.DataRange.InsertRowsBelow(1).First(); + row.Field("Value").Value = 3; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -307,24 +307,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 1; - row = row.InsertRowsBelow(1).First(); - row.Field("Value").Value = 2; - row = row.InsertRowsBelow(1).First(); - row.Field("Value").Value = 3; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 1; + row = row.InsertRowsBelow(1).First(); + row.Field("Value").Value = 2; + row = row.InsertRowsBelow(1).First(); + row.Field("Value").Value = 3; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -332,46 +332,46 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Categories") - .CellBelow().SetValue("A") - .CellBelow().SetValue("B") - .CellBelow().SetValue("C"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); - IXLTable table = ws.RangeUsed().CreateTable(); + IXLTable table = ws.RangeUsed().CreateTable(); - Assert.AreEqual("Categories", table.Fields.First().Name); + Assert.AreEqual("Categories", table.Fields.First().Name); - table.SetShowHeaderRow(false); + table.SetShowHeaderRow(false); - Assert.AreEqual("Categories", table.Fields.First().Name); + Assert.AreEqual("Categories", table.Fields.First().Name); - Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); - Assert.AreEqual(null, table.HeadersRow()); - Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); - Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); - Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); - Assert.AreEqual("C", table.DataRange.LastCell().GetString()); + Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); + Assert.AreEqual(null, table.HeadersRow()); + Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); + Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); + Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); + Assert.AreEqual("C", table.DataRange.LastCell().GetString()); - table.SetShowHeaderRow(); - IXLRangeRow headerRow = table.HeadersRow(); - Assert.AreNotEqual(null, headerRow); - Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); + table.SetShowHeaderRow(); + IXLRangeRow headerRow = table.HeadersRow(); + Assert.AreNotEqual(null, headerRow); + Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); - table.SetShowHeaderRow(false); + table.SetShowHeaderRow(false); - ws.FirstCell().SetValue("x"); + ws.FirstCell().SetValue("x"); - table.SetShowHeaderRow(); + table.SetShowHeaderRow(); - Assert.AreEqual("x", ws.FirstCell().GetString()); - Assert.AreEqual("Categories", ws.Cell("A2").GetString()); - Assert.AreNotEqual(null, headerRow); - Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); - Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); - Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); - Assert.AreEqual("C", table.DataRange.LastCell().GetString()); - } + Assert.AreEqual("x", ws.FirstCell().GetString()); + Assert.AreEqual("Categories", ws.Cell("A2").GetString()); + Assert.AreNotEqual(null, headerRow); + Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); + Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); + Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); + Assert.AreEqual("C", table.DataRange.LastCell().GetString()); + } } [Test] @@ -379,40 +379,40 @@ { using (var wb = new XLWorkbook()) { - var ws = wb.AddWorksheet("Sheet"); - ws.Cell("A1").SetValue("FName") - .CellBelow().SetValue("John"); + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").SetValue("FName") + .CellBelow().SetValue("John"); - ws.Cell("B1").SetValue("LName") - .CellBelow().SetValue("Doe"); + ws.Cell("B1").SetValue("LName") + .CellBelow().SetValue("Doe"); - var tbl = ws.RangeUsed().CreateTable(); - var nameBefore = tbl.Field(tbl.Fields.Last().Index).Name; - tbl.Field(tbl.Fields.Last().Index).Name = "LastName"; - var nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; + var tbl = ws.RangeUsed().CreateTable(); + var nameBefore = tbl.Field(tbl.Fields.Last().Index).Name; + tbl.Field(tbl.Fields.Last().Index).Name = "LastName"; + var nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; - var cellValue = ws.Cell("B1").GetString(); + var cellValue = ws.Cell("B1").GetString(); - Assert.AreEqual("LName", nameBefore); - Assert.AreEqual("LastName", nameAfter); - Assert.AreEqual("LastName", cellValue); + Assert.AreEqual("LName", nameBefore); + Assert.AreEqual("LastName", nameAfter); + Assert.AreEqual("LastName", cellValue); - tbl.ShowHeaderRow = false; - tbl.Field(tbl.Fields.Last().Index).Name = "LastNameChanged"; - nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; - Assert.AreEqual("LastNameChanged", nameAfter); + tbl.ShowHeaderRow = false; + tbl.Field(tbl.Fields.Last().Index).Name = "LastNameChanged"; + nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; + Assert.AreEqual("LastNameChanged", nameAfter); - tbl.SetShowHeaderRow(true); - nameAfter = tbl.Cell("B1").Value.ToString(); - Assert.AreEqual("LastNameChanged", nameAfter); + tbl.SetShowHeaderRow(true); + nameAfter = tbl.Cell("B1").Value.ToString(); + Assert.AreEqual("LastNameChanged", nameAfter); var field = tbl.Field("LastNameChanged"); Assert.AreEqual("LastNameChanged", field.Name); tbl.Cell(1, 1).Value = "FirstName"; Assert.AreEqual("FirstName", tbl.Field(0).Name); + } } - } [Test] public void CanDeleteTableColumn() @@ -437,7 +437,7 @@ Assert.AreEqual("UnOrderedColumn", table.Fields.Last().Name); Assert.AreEqual(2, table.Fields.Last().Index); -} + } } [Test] @@ -510,8 +510,8 @@ IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().InsertTable(dt, true); Assert.Throws(() => ws.FirstCell().CellRight().InsertTable(dt, true)); - } -} + } + } [Test] public void OverwritingTableTotalsRow() @@ -555,6 +555,41 @@ } [Test] + public void TableRenameTests() + { + 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 table1 = ws.FirstCell().InsertTable(l); + var table2 = ws.Cell("A10").InsertTable(l); + + Assert.AreEqual("Table1", table1.Name); + Assert.AreEqual("Table2", table2.Name); + + table1.Name = "table1"; + Assert.AreEqual("table1", table1.Name); + + Assert.Throws(() => table1.Name = ""); + Assert.Throws(() => table1.Name = "R"); + Assert.Throws(() => table1.Name = "C"); + Assert.Throws(() => table1.Name = "r"); + Assert.Throws(() => table1.Name = "c"); + + Assert.Throws(() => table1.Name = "123"); + Assert.Throws(() => table1.Name = new String('A', 256)); + + Assert.Throws(() => table1.Name = "Table2"); + Assert.Throws(() => table1.Name = "TABLE2"); + } + } + + [Test] public void CanResizeTable() { using (var wb = new XLWorkbook()) @@ -692,6 +727,42 @@ } } + [Test] + public void TotalsFunctionsOfHeadersWithWeirdCharacters() + { + 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()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l, false); + + // Give the headings weird names (i.e. spaces, hashes, single quotes + ws.Cell("A1").Value = "ABCD "; + ws.Cell("B1").Value = " #BCD"; + ws.Cell("C1").Value = " as'df "; + ws.Cell("D1").Value = "Normal"; + + var table = ws.RangeUsed().CreateTable(); + Assert.IsNotNull(table); + + table.ShowTotalsRow = true; + table.Field(0).TotalsRowFunction = XLTotalsRowFunction.Count; + table.Field(1).TotalsRowFunction = XLTotalsRowFunction.Count; + table.Field(2).TotalsRowFunction = XLTotalsRowFunction.Sum; + table.Field(3).TotalsRowFunction = XLTotalsRowFunction.Sum; + + Assert.AreEqual("SUBTOTAL(103,Table1[[ABCD ]])", table.Field(0).TotalsRowFormulaA1); + Assert.AreEqual("SUBTOTAL(103,Table1[[ '#BCD]])", table.Field(1).TotalsRowFormulaA1); + Assert.AreEqual("SUBTOTAL(109,Table1[[ as''df ]])", table.Field(2).TotalsRowFormulaA1); + Assert.AreEqual("SUBTOTAL(109,[Normal])", table.Field(3).TotalsRowFormulaA1); + } + } + //TODO: Delete table (not underlying range) } }