officefileapi-115942-excel-export-library-tables-how-to-create-a-table.md
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.
After you start the table export, you can optionally specify various table settings.
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);
Dim table As IXlTable
' Specify an array containing column headings for a table.
Dim columnNames() As String = { "Product", "Category", "Amount" }
' Create the first row in the worksheet from which the table starts.
Using row As IXlRow = 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.
Dim accounting As XlNumberFormat = "_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)"
table.Columns(2).DataFormatting = accounting
table.Columns(2).TotalRowFormatting = accounting
End Using
' Generate table rows and populate them with data.
Using row As IXlRow = sheet.CreateRow()
row.BulkCells(New Object() { "Camembert Pierrot", "Dairy Products", 17000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
row.BulkCells(New Object() { "Gnocchi di nonna Alice", "Grains/Cereals", 15500 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
row.BulkCells(New Object() { "Mascarpone Fabioli", "Dairy Products", 15000 }, Nothing)
End Using
Using row As IXlRow = sheet.CreateRow()
row.BulkCells(New Object() { "Ravioli Angelo", "Grains/Cereals", 12500 }, Nothing)
End Using
' Create the total row and finish the table.
Using row As IXlRow = sheet.CreateRow()
row.EndTable(table, True)
End Using
The following image shows the result of the above-mentioned code’s execution (the workbook is opened in Microsoft® Excel®).