Best way to Export large DataTable into Excel in C#?-CodePudding

I am working on a project where the client wants to export upto 300,000 (three lakhs) record at once in Excel (.xlsx). I have to Export a large dataTable (containing 300,000 rows) in the one excel sheet of excel file. I have tried OpenXML, ClosedXML, Excel Interop etc. None of these worked for me. By using these it take upto 10min of time and high CPU and Memory consumption of local machine while exporting file.

Any suggestions?

CodePudding user response：

When i had almost the same problem as you (450,000 rows), i've used NPOI. Worked perfectly.

CodePudding user response：

Take a look at SpreadSheetLight which I have not tried with a large data set.

Benchmarks

Base example

public static (bool success, Exception exception) ExportDataTable(DataTable table)
{
    try
    {

        using (var document = new SLDocument())
        {
            document.ImportDataTable(1, SLConvert.ToColumnIndex("A"), table, true);
            document.SaveAs(FileName);

            return (true, null);
        }
    }
    catch (Exception exception)
    {
        return (false, exception);
    }
}