Use the Excel Export API to Create a Table
- 6 minutes to read
This example demonstrates how to use the Excel Export API to format a cell range containing related data as a table. By using the table features, you can control and manage information in your document more effectively. For example, you can sort and filter table data, use structured references that refer to different table regions in formulas, create calculated columns, display and calculate table totals, and so on.
To organize worksheet data in a table, follow the steps below.
To start generating a table, call the IXlRow.BeginTable method for the row where you wish for your table to start and pass the following parameters.
- A string array that contains names for the table columns. Column names are displayed in the header row of the table and must be unique within the table. If the specified column names are not unique, a System.ArgumentException will be thrown.
- A Boolean value indicating whether the header row of the table is displayed. If this parameter is true, the corresponding cells containing column names will be automatically generated in the header row. If you pass false, the header row will be hidden, and therefore you need to add table data to the current row that automatically becomes the first row of the table. Otherwise, an empty row will appear at the beginning of your table.
The third parameter of the IXlRow.BeginTable method is optional and allows you to specify formatting settings for the table header row.
After you start the table export, you can optionally specify various table settings.
Format the table
You can quickly format the table by applying a built-in table style (IXlTableStyleInfo.Name) or setting custom formatting for different table regions: the table data range (IXlTable.DataFormatting) or total row (IXlTable.TotalRowFormatting).
Format individual table columns
Besides formatting the entire table, you can also apply custom formatting to individual table columns. To do this, access the column you wish to format by its index in the IXlTable.Columns collection and use one of the following properties of the returned IXlTableColumn object: the IXlTableColumn.DataFormatting property - to apply special formatting to the data area of the current column, or the IXlTableColumn.TotalRowFormatting property - to format the column’s total cell.
Important
Note that you should set the required format characteristics for a table or table columns before you start generating any table data. Otherwise, you may get a table with partial or incorrect formatting. For more information on how to format a table or its regions, refer to the How to: Apply Custom Formatting to a Table example.
Specify the function to calculate table totals
You can specify how to calculate a total for each column in the table. To do this, use the column’s IXlTableColumn.TotalRowFunction property that allows you to select one of the predefined functions to calculate the column total. Use the IXlTableColumn.TotalRowLabel property to specify the text to be displayed in the total row cell of the table column.
- Generate table rows and populate them with data. This can be done by using regular methods of the XL Export library designed for creating worksheet rows and cells (for details, refer to the How to: Create a Row and How to: Create a Worksheet Cell and Set Its Value examples). When you add a new row, the table data range expands one row down and table formatting is automatically applied to each row cell that appears within the table data range. When generating table cells, control the accuracy and consistency of table data so that each column in the table contains related data of the same type.
- After you generate all the required data, finish the table export by calling the IXlRow.EndTable method. This method accepts a Boolean parameter specifying whether the table total row is visible. If your table does not require the total row, populate the last row in the table with data and call the row’s IXlRow.EndTable method with the hasTotalRow parameter set to false.
After a table is generated, it is automatically added to the read-only IXlTableCollection collection, which stores all tables in a worksheet and can be accessed by using the IXlSheet.Tables property.
IXlTable table;
// Specify an array containing column headings for a table.
string[] columnNames = new string[] { "Product", "Category", "Amount" };
// Create the first row in the worksheet from which the table starts.
using (IXlRow row = sheet.CreateRow())
{
// Start generating the table with a header row displayed.
table = row.BeginTable(columnNames, true);
// Specify the total row label.
table.Columns[0].TotalRowLabel = "Total";
// Specify the function to calculate the total.
table.Columns[2].TotalRowFunction = XlTotalRowFunction.Sum;
// Specify the number format for the "Amount" column and its total cell.
XlNumberFormat accounting = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
table.Columns[2].DataFormatting = accounting;
table.Columns[2].TotalRowFormatting = accounting;
}
// Generate table rows and populate them with data.
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Camembert Pierrot", "Dairy Products", 17000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Mascarpone Fabioli", "Dairy Products", 15000 }, null);
using (IXlRow row = sheet.CreateRow())
row.BulkCells(new object[] { "Ravioli Angelo", "Grains/Cereals", 12500 }, null);
// Create the total row and finish the table.
using (IXlRow row = sheet.CreateRow())
row.EndTable(table, true);
The following image shows the result of the above-mentioned code’s execution (the workbook is opened in Microsoft® Excel®).
