Create Excel Tables

The following example shows how to use GemBox.Spreadsheet to 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
Excel Table created with GemBox.Spreadsheet
Screenshot of Excel Table created with GemBox.Spreadsheet

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.

Note that in GemBox.Spreadsheet tables can be loaded from and saved to the XLSX file format only.

This next example shows how you can update a Table by removing, modifying, and adding new rows.

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
Excel Table updated with GemBox.Spreadsheet
Screenshot of Excel Table updated with GemBox.Spreadsheet

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