Tables | .NET Excel Library

Overview

The Syncfusion .NET Excel Library (XlsIO) offers comprehensive support for creating, accessing, and formatting tables in Excel worksheets. Users can organize and analyze related data using tables and apply built-in and custom styles. This library allows you to insert or remove rows and columns, add total rows, and create tables from external connections.

How to create a table in Excel using C#

Install the Syncfusion.XlsIO.Net.Core NuGet package in your .NET application.
Initialize the ExcelEngine and IApplication objects.
Initialize the IWorkbook and load the Excel document.
Create an IWorksheet instance to access the sheet in Excel Library.
Create a table by using IListObject for a specific range.
Save the workbook object to a FileStream object.

Here is an example of how to create a table in Excel Library using C#:

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using (FileStream inputStream = new FileStream("sample.xlsx",FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
        IWorksheet worksheet = workbook.Worksheets[0];

        //Create table with the data in given range.
        IListObject table = worksheet.ListObjects.Create("Table1", worksheet["A1:C8"]);

        //Saving the workbook as stream.
        using (FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite))
        {
            workbook.SaveAs(outputStream);
        }
    }
}

Table styles

Tables can be formatted with built-in styles or custom table styles. There are 60 built-in styles in Microsoft Excel, which are categorized into dark, medium, and light with different colors. Excel Library supports all these built-in styles. Excel Library also allows users to customize the table styles with different borders, fonts, and background colors.

Here is an example of applying a built-in style to an Excel table using C#:

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using (FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
        IWorksheet worksheet = workbook.Worksheets[0];
 
        //Creating a table.
        IListObject table = worksheet.ListObjects.Create("Table1", worksheet["A1:C8"]);
 
        //Formatting the table with a built-in style.
        table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium9;
 
        //Saving the workbook as stream
        using (FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite))
        {
            workbook.SaveAs(outputStream);
        }
    }
}

Here is an example of how to apply a custom table style in Excel using C#:

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];
 
    //Create data for the table.
    worksheet[1, 1].Text = "Products";
    worksheet[1, 2].Text = "Qtr1";
    worksheet[1, 3].Text = "Qtr2";
    worksheet[1, 4].Text = "Qtr3";
    worksheet[1, 5].Text = "Qtr4";

    worksheet[2, 1].Text = "Alfreds Futterkiste";
    worksheet[2, 2].Number = 744.6;
    worksheet[2, 3].Number = 162.56;
   
    worksheet[2, 4].Number = 5079.6;
    worksheet[2, 5].Number = 1249.2;
 
    worksheet[3, 1].Text = "Antonio Moreno";
    worksheet[3, 2].Number = 5079.6;
    worksheet[3, 3].Number = 1249.2;
    worksheet[3, 4].Number = 943.89;
    worksheet[3, 5].Number = 349.6;
 
    worksheet[4, 1].Text = "Around the Horn";
    worksheet[4, 2].Number = 1267.5;
    worksheet[4, 3].Number = 1062.5;
    worksheet[4, 4].Number = 744.6;
    worksheet[4, 5].Number = 162.56;
 
    worksheet[5, 1].Text = "Bon app";
    worksheet[5, 2].Number = 1418;
    worksheet[5, 3].Number = 756;
    worksheet[5, 4].Number = 1267.5;
    worksheet[5, 5].Number = 1062.5;
 
    //Create style for table number format.
    IStyle style = workbook.Styles.Add("CurrencyFormat");
    style.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
    worksheet["B2:E8"].CellStyleName = "CurrencyFormat";
 
    //Create table.
    IListObject table = worksheet.ListObjects.Create("Table1", worksheet["A1:E7"]);
 
    //Apply custom table style.
    ITableStyles tableStyles = workbook.TableStyles;
    ITableStyle tableStyle = tableStyles.Add("Table Style 1");
    ITableStyleElements tableStyleElements = tableStyle.TableStyleElements;
    ITableStyleElement tableStyleElement = tableStyleElements.Add(ExcelTableStyleElementType.SecondColumnStripe);

    tableStyleElement.BackColorRGB = Color.FromArgb(217, 225, 242);
 
    ITableStyleElement tableStyleElement1 = tableStyleElements.Add(ExcelTableStyleElementType.FirstColumn);
    tableStyleElement1.FontColorRGB = Color.FromArgb(128, 128, 128);
 
    ITableStyleElement tableStyleElement2 = tableStyleElements.Add(ExcelTableStyleElementType.HeaderRow);
    tableStyleElement2.FontColor = ExcelKnownColors.White;
    tableStyleElement2.BackColorRGB = Color.FromArgb(0, 112, 192);
 
    ITableStyleElement tableStyleElement3 = tableStyleElements.Add(ExcelTableStyleElementType.TotalRow);
    tableStyleElement3.BackColorRGB = Color.FromArgb(0, 112, 192);
    tableStyleElement3.FontColor = ExcelKnownColors.White;
 
    table.TableStyleName = tableStyle.Name; 
 
    //Saving the workbook as stream.
    using (FileStream outputStream = new FileStream("CustomTableStyle.xlsx", FileMode.Create, FileAccess.Write))
    {
        workbook.SaveAs(outputStream);
    } 
}

Total row

A total row can be added to a table. It enables users to calculate and display totals for specific columns within the table. The total row can use any Excel function.

Here is an example of how to add a total row to an Excel table using C#:

c#
using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    using (FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read))
    {
        IWorkbook workbook = application.Workbooks.Open(inputStream, ExcelOpenType.Automatic);
        IWorksheet worksheet = workbook.Worksheets[0];
        //Creating a table
        IListObject table = worksheet.ListObjects.Create("Table1", worksheet["A1:C8"]);
        //Adding Total Row
        table.ShowTotals = true;
        table.Columns[0].TotalsRowLabel = "Total";
        table.Columns[1].TotalsCalculation = ExcelTotalsCalculation.Sum;
        table.Columns[2].TotalsCalculation = ExcelTotalsCalculation.Sum;
 
        //Saving the workbook as stream.
        using (FileStream outputStream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite))
        {
            workbook.SaveAs(outputStream);
        }
    }
}