Is there any way that an export-to-Excel function can be scalable?
- by MusiGenesis
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(Boolean
flushStream, 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\OneList\UserControls\TicketReportExporter.
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.Rai
sePostBackEvent(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\Temporary
ASP.NET
Files\XXXX\cdf32a52\d1a5eabd\App_Web_enxdwlks.1.cs:line
0 at
System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpAppli
cation.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?
