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 rows and columns which makes it easier to run column and row 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.

Excel Table created with GemBox.Spreadsheet
Screenshot of Excel Table created with GemBox.Spreadsheet
using GemBox.Spreadsheet;
using GemBox.Spreadsheet.Tables;

class Program
{
    static void Main()
    {
        // If using 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 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

See also


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