How to enhance Performance set Data from SQL to Excel sheet by C# .net core?

Question

I recieve 900000 row from database and I entire it in Excel sheet by XLWorkbook Liberary but it take More 20 minute it is very very slow and in sometime give my TimeOut Error , So what the solution or another tech to enhance performance

Answer 1

You might consider fetching data in chunks, each chunk goes to a new worksheet so not to hit max rows.

SELECT col1, col2, ...
 FROM ...
 ORDER BY -- this is a MUST there must be ORDER BY statement
-- the paging comes here
OFFSET     10 ROWS       -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows

I favor SpreadSheetLight NuGet package to do the import.

public static void ExportToExcel(DataTable table, string fileName, bool includeHeader, string sheetName)
{
    using var document = new SLDocument();

    // import to first row, first column
    document.ImportDataTable(1, SLConvert.ToColumnIndex("A"), table, includeHeader);

    // give sheet a useful name
    document.RenameWorksheet(SLDocument.DefaultFirstSheetName, sheetName);

    document.SaveAs(fileName);
}

Another option is using EF Core, Skip/Take then using ExcelMapper NuGet package to export to Excel.