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

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.H开发者_Python百科ttpApplication.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?

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.

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.

I know this post is really old but if you are using the ExcelPackage I kept getting the Out of memory exception on my "LoadFromDataTable" call for a datatable which had about 300k rows. I found for me the point was about 100k where the error would ocassionally occur.

I used a split datatable method at about 50k and iterated through the datatable collection and then called the "LoadFromDataTable" method. Seems to be working only problem is the method deletes the previous. If I could figure out how to append I would be alright. Excel exports at its finest I tell you.