Is there any way that an export-to-Excel function can be scalable?

Question

Summary: ASP.Net website with a couple hundred users. Data is exported to Excel files which can be relatively large (~5 MB).

In the pilot phase (just a few users), we are already seeing occasional errors on the server in the exporting method.

Here's the stack trace:

System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. --->
System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown.    at
System.IO.MemoryStream.set_Capacity(Int32 value)    at
System.IO.MemoryStream.EnsureCapacity(Int32 value)    at
System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count)   at
MS.Internal.IO.Packaging.TrackingMemoryStream.Write(Byte[]buffer, Int32 offset, Int32 count)   at
MS.Internal.IO.Packaging.SparseMemoryStream.WriteAndCollapseBlocks(Byte[] buffer, Int32 offset, Int32 count)  at
MS.Internal.IO.Packaging.SparseMemoryStream.Write(Byte[]buffer, Int32 offset, Int32 count)   at
MS.Internal.IO.Packaging.CompressEmulationStream.Write(Byte[]buffer, Int32 offset, Int32 count)   at
MS.Internal.IO.Packaging.CompressStream.Write(Byte[]buffer, Int32 offset, Int32 count)   at
MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Write(Byte[]buffer, Int32 offset, Int32 count)   at
MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Write(Byte[]buffer, Int32 offset, Int32 count)   at
System.IO.StreamWriter.Flush(BooleanflushStream, Boolean flushEncoder)   at 
System.IO.StreamWriter.Write(String value)    at
System.Xml.XmlTextEncoder.Write(String text)    at
System.Xml.XmlTextWriter.WriteString(String text)    at
System.Xml.XmlText.WriteTo(XmlWriter w)    at
System.Xml.XmlAttribute.WriteContentTo(XmlWriter w)    at
System.Xml.XmlAttribute.WriteTo(XmlWriter w)    at
System.Xml.XmlElement.WriteTo(XmlWriter w)    at
System.Xml.XmlElement.WriteContentTo(XmlWriter w)    at
System.Xml.XmlElement.WriteTo(XmlWriter w)    at
System.Xml.XmlElement.WriteContentTo(XmlWriter w)    at
System.Xml.XmlElement.WriteTo(XmlWriter w)    at
System.Xml.XmlElement.WriteContentTo(XmlWriter w)    at
System.Xml.XmlElement.WriteTo(XmlWriter w)    at
System.Xml.XmlDocument.WriteContentTo(XmlWriter xw)    at
System.Xml.XmlDocument.WriteTo(XmlWriter w)    at
System.Xml.XmlDocument.Save(Stream outStream)    at
OfficeOpenXml.ExcelWorksheet.Save() in
C:\temp\XXXXXXXXXX\ExcelPackage\ExcelWorksheet.cs:line 605    at
OfficeOpenXml.ExcelWorkbook.Save() in
C:\temp\XXXXXXXXXX\ExcelPackage\ExcelWorkbook.cs:line 439    at
OfficeOpenXml.ExcelPackage.Save() in
C:\temp\XXXXXXXXXX\ExcelPackage\ExcelPackage.cs:line 348    at
Framework.Exporting.Business.ExcelExport.BuildReport(HttpContext context)    at
WebUserControl.BtnXLS_Click(Object sender, EventArgs e) in
C:\TEMP\XXXXXXXXXX\XXXXXXXXXX\XXXXXXX\UserControls\ExportToExcel.ascx.cs:line 108    at
System.Web.UI.WebControls.Button.OnClick(EventArgs e)    at
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)    at
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)   at
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)    at
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)    
---End of inner exception stack trace ---
at
System.Web.UI.Page.HandleError(Exception e)    at
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)    at
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)    at
System.Web.UI.Page.ProcessRequest()    at
System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context)    at
System.Web.UI.Page.ProcessRequest(HttpContext context)    at
ASP.XXXXXXXXXXX_aspx.ProcessRequest(HttpContext context) in
c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\TemporaryASP.NET
Files\XXXX\cdf32a52\d1a5eabd\App_Web_enxdwlks.1.cs:line 0    at
System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()    at
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)

Even aside from this particular problem, in general exporting to Excel requires the instantiation of huge Excel objects on the server for each request, which I've always assumed to mean disqualifies Excel for "serious" work on a highly-loaded server. Is there any general way to export to Excel in a "light-weight" manner? Would simply streaming the data into a CSV file work for this?

Answer 1

I would offload this to another machine. Create a Windows Service that takes the command, generates the file, zips it up, saves it to disk and then returns the path to the file so that the web app can serve it. This is one of those really memory intensive operations that you don't want bogging down your web server.

Answer 2

If formatting is not an issue, I would say yes to CSV.

Another thing I noticed, you are using ExcelPackage. I ran into some issue using it with large files. I think It was the way it handled xml and exporting large files was really slow. I would recommend using some other library.