We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback


Trusted by the world’s leading companies

Syncfusion Trusted Companies

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#

Here is an example of how to create a table in Excel Library using 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 for applying a built-in style to an Excel table using 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#.

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#.

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);
        }
    }
}

Use cases

In Syncfusion’s .NET Excel Library, developers can also:




Awards

Greatness—it’s one thing to say you have it, but it means more when others recognize it. Syncfusion is proud to hold the following industry awards.

Scroll up icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon