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.

Screenshot of Excel Table created with GemBox.Spreadsheet

C#
VB.NET

View on GitHub

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.

Screenshot of Excel Table updated with GemBox.Spreadsheet

C#
VB.NET

View on GitHub

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

Next steps

GemBox.Spreadsheet is a .NET component that enables you to read, write, edit, convert, and print spreadsheet files from your .NET applications using one simple API.

Download Buy

Published: December 13, 2018 | Modified: December 19, 2022 | Author: Marko Kozlina

Create Excel Tables in C# and VB.NET

See also

Next steps