Asp.net C# best way to export excel with multiple sheets | Need ideas

I would recommend NPOI for this scenario, especially if you need to support both .xls and .xlsx and are working with .NET Framework 4.5.2.

For simple data-only exports, NPOI is more than sufficient. It also supports basic formatting such as bold headers, background colors, borders, number formats, and column widths.

If you only need .xlsx, EPPlus provides a very convenient API and is particularly good for formatting. However, you should consider its licensing for commercial applications.

For very large datasets, Open XML can provide better memory/performance characteristics because you have more control over how the workbook is generated, but the implementation is considerably more complex.

In practice, I would choose:

• NPOI - best overall/free option, especially when .xls support is required.

• EPPlus - easier API and excellent formatting for .xlsx.

• Open XML - best when dealing with very large datasets and memory/performance is critical.

• CSV - best if you only need raw data and multiple Excel sheets aren't required.

Also, for large exports, the library itself isn't always the bottleneck. Avoid keeping unnecessarily large DataTablein memory, creating styles per cell, and using AutoFitColumns() on huge worksheets.

For a typical application with multiple dynamic DataTables and a few thousand to tens of thousands of rows per table, NPOI would be my choice.

For a .NET Framework 4.5.2 application exporting multiple DataTables into a dynamic multi-sheet Excel file, I would use:

• EPPlus if you need formatting or maintainable code.

• Open XML SDK only if exporting extremely large datasets and performance/memory are the top priority.

• NPOI only when .xls support is a hard requirement.