exporting excel with epplus in c#

Learn how to efficiently export data from a DataTable to an Excel file using the EPPlus library in C#. This blog post provides a step-by-step guide, complete with code examples and use cases, showcasing the simplicity and power of EPPlus for Excel manipulation in your .NET applications.

Installation (NuGet Packages):

To get started, you need to install the EPPlus library. You can do this using the NuGet Package Manager Console with the following command:

Install-Package EPPlus

Code Example:

using System;

using System.Data;

using System.IO;

using OfficeOpenXml;

class Program

{

    static void Main(string[] args)

    {

        // Create a sample DataTable with dummy data

        DataTable dataTable = CreateSampleDataTable();

        // Export DataTable to Excel

        ExportDataTableToExcel(dataTable, @"C:\sampledata.xlsx");

        Console.WriteLine("Excel file created successfully.");

    }

    static DataTable CreateSampleDataTable()

    {

        DataTable dataTable = new DataTable("Employees");

        dataTable.Columns.Add("ID", typeof(int));

        dataTable.Columns.Add("Name", typeof(string));

        dataTable.Columns.Add("Email", typeof(string));

        // Add some dummy data to the DataTable

        dataTable.Rows.Add(1, "John", "john@example.com");

        dataTable.Rows.Add(2, "Alice", "alice@example.com");

        dataTable.Rows.Add(3, "Bob", "bob@example.com");

        dataTable.Rows.Add(4, "Carol", "carol@example.com");

        return dataTable;

    }

    static void ExportDataTableToExcel(DataTable dataTable, string filePath)

    {

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

        using (ExcelPackage excelPackage = new ExcelPackage())

        {

            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");

            // Load data from DataTable to Excel worksheet

            worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);

            // Save the Excel package

            FileInfo excelFile = new FileInfo(filePath);

            excelPackage.SaveAs(excelFile);

        }

    }

}

Use Cases of EPPlus:

1. Importing Data: EPPlus supports not only exporting but also importing data from Excel files to DataTables or other data structures, making it a versatile tool for data manipulation.

2. Styling and Formatting: EPPlus allows you to customize the appearance of your Excel sheets. You can apply various styles, formats, and colors to cells, rows, and columns.

3. Formula Support: EPPlus enables the creation and manipulation of Excel formulas within your worksheets. This is beneficial when dealing with complex calculations or dynamic data.

4. Chart Generation: You can use EPPlus to create charts and graphs dynamically based on your data. This feature is valuable for visualizing trends and patterns in your datasets.

5. Data Validation: EPPlus supports data validation, helping you ensure that the data entered into your Excel sheets meets specific criteria, enhancing data integrity.

The EPPlus library simplifies Excel file manipulation in C#, making it easy to export data from DataTables, customize styles, and perform advanced tasks such as chart generation and formula application. In this blog post, we'll explore the step-by-step process of exporting DataTables to Excel using EPPlus, highlighting its versatility and additional features for efficient data handling. Whether you're a beginner or an experienced developer, integrating EPPlus into your .NET applications can significantly enhance your Excel-related functionalities.