Export DataTable to Excel with EPPlus

Export DataTable to Excel with EPPlus

To export a DataTable to an Excel file using EPPlus in C#, you can follow these steps:

  1. Install the EPPlus NuGet package by right-clicking on your project in Visual Studio and selecting "Manage NuGet Packages". Search for "EPPlus" and install the package.

  2. Add the following using statements to the top of your C# file:

    using OfficeOpenXml; using OfficeOpenXml.Style; using System.IO;

  3. Create a new ExcelPackage object, which represents the Excel file you want to create:

    ExcelPackage excel = new ExcelPackage();

  4. Create a new worksheet in the Excel file by calling the Workbook.Worksheets.Add method of the ExcelPackage object:

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

  5. Define the column headers in the first row of the worksheet. You can use a loop to iterate over the columns of the DataTable and set the cell values:

    int row = 1; int col = 1; foreach (DataColumn column in dataTable.Columns) { worksheet.Cells[row, col].Value = column.ColumnName; col++; }

  6. Populate the remaining rows of the worksheet with data from the DataTable. You can use a nested loop to iterate over the rows and columns of the DataTable and set the cell values:

    row = 2; foreach (DataRow dataRow in dataTable.Rows) { col = 1; foreach (DataColumn column in dataTable.Columns) { worksheet.Cells[row, col].Value = dataRow[column.ColumnName].ToString(); col++; } row++; }

  7. Save the Excel file to disk by calling the ExcelPackage.SaveAs method and passing in a file stream:

    FileInfo excelFile = new FileInfo(@"C:\path\to\your\file.xlsx"); excel.SaveAs(excelFile);

Here is the complete code:

using OfficeOpenXml; using OfficeOpenXml.Style; using System.Data; using System.IO; public static void ExportDataTableToExcel(DataTable dataTable) { ExcelPackage excel = new ExcelPackage(); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add("Sheet1"); int row = 1; int col = 1; foreach (DataColumn column in dataTable.Columns) { worksheet.Cells[row, col].Value = column.ColumnName; col++; } row = 2; foreach (DataRow dataRow in dataTable.Rows) { col = 1; foreach (DataColumn column in dataTable.Columns) { worksheet.Cells[row, col].Value = dataRow[column.ColumnName].ToString(); col++; } row++; } FileInfo excelFile = new FileInfo(@"C:\path\to\your\file.xlsx"); excel.SaveAs(excelFile); }

Note that you may need to add additional code to handle formatting or styling of the Excel file, depending on your requirements.

Examples

  1. "C# export DataTable to Excel using EPPlus"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Demonstrates the basic usage of EPPlus to export a DataTable to an Excel file.

  2. "EPPlus DataTable to Excel with custom sheet name"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("CustomSheetName"); worksheet.Cells.LoadFromDataTable(dataTable, true); package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Illustrates how to export a DataTable to an Excel file with a custom sheet name using EPPlus.

  3. "C# EPPlus export DataTable to Excel with formatting"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Apply formatting, styles, etc. worksheet.Cells["A:Z"].AutoFitColumns(); package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Shows how to apply formatting and styles to the Excel sheet when exporting a DataTable using EPPlus.

  4. "C# EPPlus DataTable to Excel with header styling"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Apply header styling worksheet.Cells["1:1"].Style.Font.Bold = true; package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Demonstrates how to apply styling to the header row when exporting a DataTable to Excel using EPPlus.

  5. "EPPlus export DataTable to Excel with column width customization"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Set custom column width worksheet.Column(1).Width = 20; package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Illustrates how to customize the column width when exporting a DataTable to Excel using EPPlus.

  6. "C# EPPlus DataTable to Excel with conditional formatting"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Apply conditional formatting worksheet.Cells["C:C"].Style.Numberformat.Format = "#,##0.00"; package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Demonstrates applying conditional formatting, such as number formatting, when exporting a DataTable to Excel with EPPlus.

  7. "EPPlus export DataTable to Excel with cell coloring"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Apply cell coloring worksheet.Cells["A:Z"].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells["A:Z"].Style.Fill.BackgroundColor.SetColor(Color.LightBlue); package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Illustrates how to color cells when exporting a DataTable to Excel using EPPlus.

  8. "C# EPPlus export DataTable to Excel with hyperlinks"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Add hyperlinks worksheet.Cells["A2"].Hyperlink = new ExcelHyperLink(new Uri("https://example.com"), "Visit Example"); package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Shows how to add hyperlinks to cells when exporting a DataTable to Excel using EPPlus.

  9. "C# EPPlus DataTable to Excel with merged cells"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Merge cells worksheet.Cells["A1:B1"].Merge = true; package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Demonstrates how to merge cells when exporting a DataTable to Excel using EPPlus.

  10. "C# EPPlus export DataTable to Excel with date formatting"

    // Assuming dataTable is your DataTable using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add("Sheet1"); worksheet.Cells.LoadFromDataTable(dataTable, true); // Apply date formatting worksheet.Cells["C:C"].Style.Numberformat.Format = "yyyy-MM-dd"; package.SaveAs(new FileInfo("output.xlsx")); }

    Description: Illustrates how to format date columns when exporting a DataTable to Excel with EPPlus.

More Tags

cefsharp ios8.1 android-bottomnav javascript-intellisense angular-reactive-forms os.system thread-local angular-providers ddms batch-insert

More C# Questions

More Biochemistry Calculators

More Chemical reactions Calculators

More Entertainment Anecdotes Calculators

More Genetics Calculators