Create Multi Excel Table for Each Worksheet by C#

Are you tired of manually creating multiple tables in Excel for each worksheet? Do you want to automate this process using C#? Look no further! In this article, we will guide you through a step-by-step process of creating multiple Excel tables for each worksheet using C#. By the end of this tutorial, you will be able to programmatically create Excel tables with ease.

What You Will Need

To follow along with this tutorial, you will need the following:

• Visual Studio (any version)
• C# programming language
• EPPlus library (a .NET library for Excel file manipulation)
• Excel file with multiple worksheets

Step 1: Install EPPlus Library

EPPlus is a popular .NET library used for Excel file manipulation. To install EPPlus, follow these steps:

1. Open your Visual Studio project.
2. Right-click on your project in the Solution Explorer and select “Manage NuGet Packages”.
3. In the NuGet Package Manager, search for “EPPlus”.
4. Select the EPPlus package and click “Install”.

Step 2: Create a New C# Project

Create a new C# project in Visual Studio. You can choose any project type, but for this tutorial, we will use a Console App.

Step 3: Import EPPlus and Excel Interop

In your C# project, add the following using statements:

using OfficeOpenXml;
using OfficeOpenXml.Excel;

Step 4: Load the Excel File

Loading the Excel file is the first step in creating multiple tables for each worksheet. Use the following code:

// Load the Excel file
using (ExcelPackage package = new ExcelPackage(new FileInfo("example.xlsx")))
{
    // Get the Excel workbook
    ExcelWorkbook workbook = package.Workbook;
}

Step 5: Loop Through Each Worksheet

To create multiple tables for each worksheet, we need to loop through each worksheet. Use the following code:

// Loop through each worksheet
foreach (ExcelWorksheet worksheet in workbook.Worksheets)
{
    // Get the worksheet name
    string worksheetName = worksheet.Name;
    
    // Create a new table for each worksheet
    CreateTable(worksheet);
}

Step 6: Create a Table for Each Worksheet

In the `CreateTable` method, we will create a new table for each worksheet. Use the following code:

private void CreateTable(ExcelWorksheet worksheet)
{
    // Create a new table
    ExcelTable table = worksheet.Tables.Add(worksheet.Dimension.Address, "Table" + worksheet.Index);
    
    // Set the table style
    table.TableStyle = workbook.TableStyles["TableStyleMedium9"];
    
    // Add columns to the table
    for (int i = 1; i <= worksheet.Dimension.End.Column; i++)
    {
        table.Columns.Add();
    }
}

Step 7: Save the Excel File

Finally, save the Excel file with the new tables:

// Save the Excel file
package.Save();

Full Code Example

Here is the full code example:

using OfficeOpenXml;
using OfficeOpenXml.Excel;

class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file
        using (ExcelPackage package = new ExcelPackage(new FileInfo("example.xlsx")))
        {
            // Get the Excel workbook
            ExcelWorkbook workbook = package.Workbook;
            
            // Loop through each worksheet
            foreach (ExcelWorksheet worksheet in workbook.Worksheets)
            {
                // Get the worksheet name
                string worksheetName = worksheet.Name;
                
                // Create a new table for each worksheet
                CreateTable(worksheet, workbook);
            }
            
            // Save the Excel file
            package.Save();
        }
    }

    private static void CreateTable(ExcelWorksheet worksheet, ExcelWorkbook workbook)
    {
        // Create a new table
        ExcelTable table = worksheet.Tables.Add(worksheet.Dimension.Address, "Table" + worksheet.Index);
        
        // Set the table style
        table.TableStyle = workbook.TableStyles["TableStyleMedium9"];
        
        // Add columns to the table
        for (int i = 1; i <= worksheet.Dimension.End.Column; i++)
        {
            table.Columns.Add();
        }
    }
}

Conclusion

In this article, we showed you how to create multiple Excel tables for each worksheet using C#. By following these steps, you can automate the process of creating tables in Excel, saving you time and effort. Remember to install EPPlus library and import it in your C# project. Loop through each worksheet, create a new table, and save the Excel file. With this tutorial, you are now equipped to create Excel tables programmatically.

Best Practices

When working with Excel files and EPPlus, keep the following best practices in mind:

• Always use the `using` statement to ensure the Excel file is properly closed.
• Use the `FileInfo` class to load the Excel file.
• Loop through each worksheet using a `foreach` loop.
• Use the `CreateTable` method to create a new table for each worksheet.
• Set the table style using the `TableStyle` property.

Common Issues

If you encounter any issues while following this tutorial, check the following:

• Make sure you have installed EPPlus library correctly.
• Check the Excel file path and name.
• Ensure the worksheet is not protected or locked.
• Verify that the table name is unique.

Worksheet Name	Table Name
Sheet1	Table1
Sheet2	Table2
Sheet3	Table3

By following this tutorial, you should now be able to create multiple Excel tables for each worksheet using C#. Remember to follow best practices and troubleshoot common issues. Happy coding!

Frequently Asked Question

Get the answers to your most burning questions about creating multi-excel tables for each worksheet using C#!