Create Excel Tables in C# and VB.NET
An Excel table is a named object that allows you to manage and analyze the related data easier and independently from the rest of the worksheet. With it you can organize your data as a series of TableRow
and TableColumn
elements which makes it easier to run row and column calculations, sort, and expand the data.
In GemBox.Spreadsheet tables are represented by Table
elements and can be loaded from and saved to the XLSX file format only.
The following example shows how you can create an Excel Table from a range of cells in C# and VB.NET.

using GemBox.Spreadsheet;
using GemBox.Spreadsheet.Tables;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Tables");
// Add some data.
var data = new object[5, 3]
{
{ "Worker", "Hours", "Price" },
{ "John Doe", 25, 35.0 },
{ "Jane Doe", 27, 35.0 },
{ "Jack White", 18, 32.0 },
{ "George Black", 31, 35.0 }
};
for (int i = 0; i < 5; i++)
for (int j = 0; j < 3; j++)
worksheet.Cells[i, j].Value = data[i, j];
// Set column widths.
worksheet.Columns[0].SetWidth(100, LengthUnit.Pixel);
worksheet.Columns[1].SetWidth(70, LengthUnit.Pixel);
worksheet.Columns[2].SetWidth(70, LengthUnit.Pixel);
worksheet.Columns[3].SetWidth(70, LengthUnit.Pixel);
worksheet.Columns[2].Style.NumberFormat = "\"$\"#,##0.00";
worksheet.Columns[3].Style.NumberFormat = "\"$\"#,##0.00";
// Create table and enable totals row.
var table = worksheet.Tables.Add("Table1", "A1:C5", true);
table.HasTotalsRow = true;
// Add new column.
var column = table.Columns.Add();
column.Name = "Total";
// Populate column.
foreach (var cell in column.DataRange)
cell.Formula = "=Table1[Hours] * Table1[Price]";
// Set totals row function for newly added column and calculate it.
column.TotalsRowFunction = TotalsRowFunction.Sum;
column.Range.Calculate();
// Set table style.
table.BuiltInStyle = BuiltInTableStyleName.TableStyleMedium2;
workbook.Save("Tables.xlsx");
}
}
Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.Tables
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook As New ExcelFile()
Dim worksheet = workbook.Worksheets.Add("Tables")
' Add some data.
Dim data(,) = New Object(4, 2) _
{
{"Worker", "Hours", "Price"},
{"John Doe", 25, 35.0},
{"Jane Doe", 27, 35.0},
{"Jack White", 18, 32.0},
{"George Black", 31, 35.0}
}
For i As Integer = 0 To 4
For j As Integer = 0 To 2
worksheet.Cells.Item(i, j).Value = data(i, j)
Next
Next
' Set column widths and formats.
worksheet.Columns(0).SetWidth(100, LengthUnit.Pixel)
worksheet.Columns(1).SetWidth(70, LengthUnit.Pixel)
worksheet.Columns(2).SetWidth(70, LengthUnit.Pixel)
worksheet.Columns(3).SetWidth(70, LengthUnit.Pixel)
worksheet.Columns(2).Style.NumberFormat = """$""#,##0.00"
worksheet.Columns(3).Style.NumberFormat = """$""#,##0.00"
' Create table And enable totals row.
Dim table = worksheet.Tables.Add("Table1", "A1:C5", True)
table.HasTotalsRow = True
' Add New column.
Dim column = table.Columns.Add()
column.Name = "Total"
' Populate column.
For Each cell In column.DataRange
cell.Formula = "=Table1[Hours] * Table1[Price]"
Next
' Set totals row function for newly added column and calculate it.
column.TotalsRowFunction = TotalsRowFunction.Sum
column.Range.Calculate()
' Set table style.
table.BuiltInStyle = BuiltInTableStyleName.TableStyleMedium2
workbook.Save("Tables.xlsx")
End Sub
End Module
The following example shows how you can update an Excel Table by adding new or modifying existing rows in C# and VB.NET.

using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%#Tables.xlsx%");
var worksheet = workbook.Worksheets["Tables"];
var table = worksheet.Tables["Table1"];
// Remove existing table row.
table.Rows.RemoveAt(0);
// Update existing table row.
var tableRow = table.Rows[0];
tableRow.DataRange[0].Value = "Jane Updated";
tableRow.DataRange[1].Value = 30;
tableRow.DataRange[2].Value = 40.0;
// Sample data for writing into a table.
var data = new[]
{
new object[]{ "Fred Nurk", 22, 35.0 },
new object[]{ "Hans Meier", 16, 20.0 },
new object[]{ "Ivan Horvat", 24, 34.0 }
};
foreach (object[] items in data)
{
// Add new table row by adding cell values directly.
tableRow = table.Rows.Add(items);
tableRow.DataRange[3].Formula = "=Table1[Hours] * Table1[Price]";
}
table.Columns["Total"].Range.Calculate();
workbook.Save("Tables Updated.xlsx");
}
}
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%#Tables.xlsx%")
Dim worksheet = workbook.Worksheets("Tables")
Dim table = worksheet.Tables("Table1")
' Remove existing table row.
table.Rows.RemoveAt(0)
' Update existing table row.
Dim tableRow = table.Rows(0)
tableRow.DataRange(0).Value = "Jane Updated"
tableRow.DataRange(1).Value = 30
tableRow.DataRange(2).Value = 40.0
' Sample data for writing into a table.
Dim data = {
New Object() {"Fred Nurk", 22, 35.0},
New Object() {"Hans Meier", 16, 20.0},
New Object() {"Ivan Horvat", 24, 34.0}
}
For Each items As Object() In data
' Add new table row by adding cell values directly.
tableRow = table.Rows.Add(items)
tableRow.DataRange(3).Formula = "=Table1[Hours] * Table1[Price]"
Next
table.Columns("Total").Range.Calculate()
workbook.Save("Tables Updated.xlsx")
End Sub
End Module
See also
Next steps
Published: December 13, 2018 | Modified: December 19, 2022 | Author: Marko Kozlina