Creating Excel Tables with ExpertXls Library for .NET |
An Excel table is a range with a header row, a built-in table style, filter buttons and an optional totals row. Its name can be used in formulas with structured references, for example =SUM(Sales[Total]). The Tables property of a worksheet returns an ExcelTablesCollection; its Add(String, ExcelRange) method creates an ExcelTable from a range whose first row gives the names of the columns.
Excel tables are available only in the Excel 2007 or later (.xlsx) format. In an Excel 97-2003 workbook use an AutoFilter and cell styles instead.
A new table has the Medium 2 style, banded rows and filter buttons, as in Excel. Showing the totals row adds a row below the data (the cells below the table, in its columns, move one row down); the totals calculation of a column (ExcelTableTotalsCalculation) is a SUBTOTAL formula calculated by Excel.
ExcelTable table = worksheet.Tables.Add("Sales", worksheet["A6:G18"]); table.Style = ExcelTableStyle.Medium9; table.ShowFirstColumn = true; table.ShowTotalsRow = true; table.Columns["Product"].TotalsRowLabel = "Total"; table.Columns["Q1"].TotalsCalculation = ExcelTableTotalsCalculation.Sum; table.Columns["Total"].TotalsCalculation = ExcelTableTotalsCalculation.Average; // a formula with a structured reference to the table worksheet["B22"].Formula = "=MAX(Sales[Total])";
The Range property returns the whole table and DataRange the data rows only. The tables of an existing workbook are read with the indexers of the collection, by position or by name.