Using Tables in Workbooks for .NET

Learn more about Xeed Workbooks for .NET

 

In the previous article, we covered how to access and modify Cells, Columns and Rows.

This time we will look into adding and modifying Tables in a Worksheet.

 

What is a Table?

A Table represents a range of cells in a Worksheet. To be considered a Table, these cells should be independent from the other cells in the Worksheet and grouped to form a rectangular arrangement of rows and columns. Built-in styles can be used to unify these grouped cells visually.

The Table's header row can display filtering options for each column. A total row can also be added, each cell of this row will display a formula calculation based on the data in that column (Sum, Average, Count, etc.).

 

Filling the data for the Table

As said previously, a Table is a range of cells in a Worksheet, so the first step is to fill the cells to know the range the Table will occupy.

To fill the cell values, we use what we have learned in the previous article. The first row will contain our headers, while the rest will contain the data.

// Set the Table's content
	using( var document = Workbook.Load( "testDoc.xlsx" ));
	{
		var worksheet = document.Worksheets[ 0 ];
		
		// Column 1 (B3:B7)
		worksheet.Rows[ 2 ].Cells[ 1 ].Value = "Employee";
		worksheet.Rows[ 3 ].Cells[ 1 ].Value = "Nancy Davolio";
		worksheet.Rows[ 4 ].Cells[ 1 ].Value = "Margaret Peacock";
		worksheet.Rows[ 5 ].Cells[ 1 ].Value = "Steven Buchanan";
		worksheet.Rows[ 6 ].Cells[ 1 ].Value = "Laura Callahan";

		// Column 2 (C3:C7)
		worksheet.Rows[ 2 ].Cells[ 2 ].Value = "Years of Experience";
		worksheet.Rows[ 3 ].Cells[ 2 ].Value = "12";
		worksheet.Rows[ 4 ].Cells[ 2 ].Value = "2";
		worksheet.Rows[ 5 ].Cells[ 2 ].Value = "9";
		worksheet.Rows[ 6 ].Cells[ 2 ].Value = "7";

		// Column 3 (D3:D7)
		worksheet.Rows[ 2 ].Cells[ 3 ].Value = "Salary";
		worksheet.Rows[ 3 ].Cells[ 3 ].Value = "75000";
		worksheet.Rows[ 4 ].Cells[ 3 ].Value = "41000";
		worksheet.Rows[ 5 ].Cells[ 3 ].Value = "64000";
		worksheet.Rows[ 6 ].Cells[ 3 ].Value = "55000";
		
		document.Save();
	}

 

Adding a Table

Tables are stored in a TableCollection object, available through the Tables property on a Worksheet. To add a Table, we use the Add method found on TableCollection. The method expects the name of the table (optional), a range of cells (either by cell addresses or defined by indexes), the table style and if the header row is shown.

The Add method supports 4 overloads:

• (String, String, TableStyle, Boolean): the range of cells is defined by cell addresses.

• (String, String, String, TableStyle, Boolean): the table name is specified, and the range of cells is defined by cell addresses.

• (Int32, Int32, Int32, Int32, TableStyle, Boolean): the range of cells is defined by indexes.

• (String, Int32, Int32, Int32, Int32, TableStyle, Boolean): the table name is specified, and the range of cells is defined by indexes.

// Add a Table from cell B3 to cell F7
	using( var document = Workbook.Load( "testDoc.xlsx" ));
	{
		var worksheet = document.Worksheets[ 0 ];

		// Overload 1: Using addresses, the table name is not specified
		worksheet.Tables.Add( "B3", "F7", TableStyle.TableStyleMedium20, true);

		// Overload 2: Using addresses, the table name is specified
		worksheet.Tables.Add( "Results", "B3", "F7", TableStyle.TableStyleMedium20, true);

		// Overload 3: Using indexes, the table name is not specified
		worksheet.Tables.Add( 2, 1, 6, 5, TableStyle.TableStyleMedium20, true);

		// Overload 4: Using indexes, the table name is specified
		worksheet.Tables.Add( "Results", 2, 1, 6, 5, TableStyle.TableStyleMedium20, true);

		document.Save();
	}

 

Note:

• If the range of cells is defined by indexes, the sequence is startRowId, startColumnId, endRowId, endColumnId, and the indexes each start at 0. For example, the cell at the address "A1" would be at the indexes (0, 0).

 

Accessing and Modifying a Table

We can access an existing table, such as one that was just added, directly from the TableCollection on the Worksheet. We can specify either the Table's index in the collection, or its name.

// Fetching a specific Table
	var firstTable = worksheet.Tables[ 0 ];
	var myTable = worksheet.Tables[ "Results" ];

 

With the Table now in hand, we can customize some of its properties.
The list of available properties on the Table class is available in the documentation here.

// Modifying a Table's properties
	var myTable = worksheet.Tables[ 0 ];
	myTable.AutoFilter.ShowFilterButton = false;
	myTable.ShowFirstColumnFormatting = true;
	myTable.ShowBandedRows = true;

 

We can also make modifications to the individual columns of a Table, which can be accessed through the Table's Columns property.
The list of available properties on the TableColumn class is available in the documentation here.

// Modifying a Columns's properties
	var myTable = worksheet.Tables[ 0 ];
	myTable.Columns[ "Fruits" ].TotalRowLabel = "TOTAL:";
	myTable.Columns[ "Quantity" ].TotalRowFunction = TotalRowFunction.Sum;

 

For more information, please refer to the documentation.