This project has moved. For the latest updates, please go here.

Slow wb.Worksheets.Add() with big datatable

Jul 16, 2015 at 3:07 PM
Hi,

I am using ClosedXML wb.Worksheets.Add() to create a worksheet from a datatable the has over 100,000 rows and 59 columns in an ASP.Net web site. The code is running on a Windows Server 2008 R2 64bit machine. The process to create the worksheet is taking 10 - 15 minutes but it is always successful. Understandably the end users don't like the wait.

Is there anything that I can do to speed up the process?

I've included the code below.

Thanks,

Dave
XLWorkbook wb = new XLWorkbook();

            // Add a DataTable as a worksheet
            wb.Worksheets.Add(UserControls_ProductionExcelOutput.MatrixPROCOutputdt, "ProdMatrixOutput");
            wb.Worksheets.First().Tables.First().ShowAutoFilter = false;

            // Prepare the response
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //httpResponse.AddHeader("content-disposition", "attachment;filename=\"" + (String)Session["MatrixOutputFileName"] + "\"");
            Response.AddHeader("content-disposition", "attachment;filename=" + (String)Session["MatrixOutputFileName"]);

            //httpResponse.AddHeader("content-disposition", "attachment;filename=\"Prod_Matrix_Output.xlsx\"");

            // Flush the workbook to the Response.OutputStream
            using (MemoryStream memoryStream = new MemoryStream())
            {
                wb.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                memoryStream.Close();
            }
Response.End();
Editor
Jul 18, 2015 at 9:55 PM
When you say it's taking 15 minutes - is that how long it's taking to create and download the workbook? Or is that the amount of time taken by the line
wb.Worksheets.Add(UserControls_ProductionExcelOutput.MatrixPROCOutputdt, "ProdMatrixOutput");
?

I whipped up an example program with a fake datatable (with 59 columns and 100,000 rows), created a workbook, and streamed it to a memory stream. While it wasn't fast, it didn't take anywhere near 15 minutes. My fake data was just a lot of integers, though - what sort of data is in the table you're exporting? And how large is the file that's generated?

Also, how much memory is on the machine you're using? With that many rows and columns of data, maybe you're hitting the memory limits of the machine. My test program had to allocated a couple of gigs to run.