DEV Community

Leon Davis
Leon Davis

Posted on

Efficiently Create and Manage Tables in Excel Using C#

#csharp #createexceltable

Excel is more than just a spreadsheet tool; it offers powerful table functionalities that help users manage, analyze, and present data efficiently. Excel tables are not merely simple data ranges-they come with features like sorting, filtering, and formatting, all of which significantly enhance data processing efficiency. However, as the volume of data increases, manually managing these tables becomes increasingly cumbersome. By leveraging programming, we can automate these tasks, using C# to create, manage, and optimize table features in Excel, making these complex tasks more streamlined and efficient.

This article will guide you through the process of efficiently creating and managing tables in Excel using C#.

1. Prerequisites

To implement the operations mentioned above in C#, we can use the Spire.XLS library. Spire.XLS is a powerful library that enables users to easily generate Excel tables, populate data, apply styles, and handle various formatting and functional settings.

Installing the Spire.XLS Library

First, ensure that the Spire.XLS library is installed in your C# project. You can do this by using the NuGet package manager:

Install-Package Spire.XLS
Enter fullscreen mode Exit fullscreen mode

Once installed, you can reference Spire.XLS in your code to begin working with Excel files.

2. Creating Excel Tables and Applying Styles Using Csharp

The following steps describe how to create an Excel table using C# and the Spire.XLS library:

  • Create a Workbook and Worksheet: Start by creating a new Workbook object using new Workbook(), and access the default worksheet via Worksheets[0].
  • Fill Data: Use the Range object's Text property to populate data, such as product IDs, names, prices, and stock quantities.
  • Create a Table: Use worksheet.ListObjects.Add to convert a specified data range into an Excel table.
  • Apply Table Styles: Use the BuiltInTableStyle property to apply a predefined style to the table.
  • Save the File: Use the SaveToFile method to save the generated Excel file to a specified location.

Here's how to implement these steps in C#:

using Spire.XLS;

class Program
{
    static void Main()
    {
        // Create a new Excel workbook
        Workbook workbook = new Workbook();

        // Get the default worksheet
        Worksheet worksheet = workbook.Worksheets[0];

        // Set data range and fill data
        worksheet.Range["A1"].Text = "Product ID";
        worksheet.Range["B1"].Text = "Product Name";
        worksheet.Range["C1"].Text = "Price";
        worksheet.Range["D1"].Text = "Stock";

        worksheet.Range["A2"].Text = "P001";
        worksheet.Range["B2"].Text = "Product A";
        worksheet.Range["C2"].NumberValue = 25.5;
        worksheet.Range["D2"].NumberValue = 100;

        worksheet.Range["A3"].Text = "P002";
        worksheet.Range["B3"].Text = "Product B";
        worksheet.Range["C3"].NumberValue = 15.8;
        worksheet.Range["D3"].NumberValue = 150;

        // Define data range and convert to table
        ListObject table = worksheet.ListObjects.Add(ListObjectSourceType.Range, worksheet.Range["A1:D3"], true);

        // Apply built-in table style
        table.BuiltInTableStyle = TableBuiltInStyles.TableStyleMedium2;

        // Save the Excel file
        workbook.SaveToFile("Product_Info_Table.xlsx", ExcelVersion.Version2016);
        Console.WriteLine("Excel file has been saved!");
    }
}
Enter fullscreen mode Exit fullscreen mode

3. Modifying the Name and Data Range of an Excel Table Using Csharp

You can use the DisplayName property of the ListObject to modify the table name and the Location property to adjust the data range of an existing table. For example, to modify the data range of the table, you can use the following code:

// Get the existing table
ListObject table = worksheet.ListObjects[0];

// Modify the table's data range
Range newRange = worksheet.Range["C1:F5"];
table.Location = newRange;

// Modify the table name
table.DisplayName = "Product_Sales";

// Save the file
workbook.SaveToFile("Modify_Table_Name_and_Range.xlsx", ExcelVersion.Version2016);
Enter fullscreen mode Exit fullscreen mode

4. Adding a Total Row to an Excel Table Using Csharp

You can quickly add a total row to an Excel table and set the calculation method (such as Sum, Average, etc.) by enabling the Total Row option. Here's how to add a total row to your table:

// Enable total row
table.DisplayTotalRow = true;

// Set the label for the total row
table.Columns[0].TotalsRowLabel = "Total";

// Set the calculation method for the total row
table.Columns[2].TotalsCalculation = ExcelTotalsCalculation.Sum;  // Sum of the price column
table.Columns[3].TotalsCalculation = ExcelTotalsCalculation.Sum;  // Sum of the stock column

// Save the file
workbook.SaveToFile("Add_Total_Row.xlsx", ExcelVersion.Version2016);
Enter fullscreen mode Exit fullscreen mode

5. Conclusion

By using Spire.XLS and C#, you can easily create and manipulate Excel tables, allowing for operations such as setting styles, modifying data ranges, adding total rows, and more. These functionalities are especially useful for data processing and report generation, significantly improving work efficiency. If you frequently work with Excel, using C# with Spire.XLS can help automate these tasks, reducing repetitive work. We hope this article has helped you better understand how to create and manage Excel tables in C#.

Top comments (0)