I am trying to build a high(er) performance Excel exporter for large datasets for my ASP.Net application.   It's coming along ok, but I have hit an internal error that I do not understand.

                Dim rnd As New Random
                Dim sTempFileName As String = Server.MapPath("Reports") & "\" & rnd.Next(1, 10000000) & ".xlsx"

                Dim dr As SqlDataReader = SqlHelper.ExecuteReader(GetSQL())
                Dim wkb As New Infragistics.Documents.Excel.Workbook(Infragistics.Documents.Excel.WorkbookFormat.Excel2007)
                Dim wks As Infragistics.Documents.Excel.Worksheet = wkb.Worksheets.Add("Report")

                AddSqlDataReader(dr, wks)

                wkb.Save(sTempFileName)

This throws a cryptic error on wkb.Save:

Server Error in '/publisher_dev' Application.


Can not access a closed Stream. 
Description: 
An unhandled exception occurred during the execution of the current web 
request. Please review the stack trace for more information about the error and 
where it originated in the code. 

Exception Details: 
System.ObjectDisposedException: Can not access a closed 
Stream.

Source Error: 






Line 189:                AddSqlDataReader(dr, wks)
Line 190:
Line 191:                wkb.Save(sTempFileName)
Line 192:
Line 193:





Source File: 
D:\Webfolders\publisher_dev\CustomReports40.aspx.vb    Line: 191 


Stack Trace: 






[ObjectDisposedException: Can not access a closed Stream.]
   System.IO.Compression.DeflateStream.Flush() +6164822
   MS.Internal.IO.Packaging.CompressStream.Flush() +29
   MS.Internal.IO.Zip.ProgressiveCrcCalculatingStream.Flush() +23
   MS.Internal.IO.Zip.ZipIOLocalFileBlock.FlushExposedStreams() +16
   MS.Internal.IO.Zip.ZipIOLocalFileBlock.UpdateReferences(Boolean closingFlag) +95
   MS.Internal.IO.Zip.ZipIOBlockManager.SaveContainer(Boolean closingFlag) +182
   MS.Internal.IO.Zip.ZipIOBlockManager.SaveStream(ZipIOLocalFileBlock blockRequestingFlush, Boolean closingFlag) +80
   MS.Internal.IO.Zip.ZipIOModeEnforcingStream.Dispose(Boolean disposing) +94
   System.IO.Stream.Close() +20
   System.IO.Packaging.PackagePart.Close() +139
   System.IO.Packaging.Package.DoClose(PackagePart p) +194
   System.IO.Packaging.Package.DoOperationOnEachPart(PartOperation operation) +321
   System.IO.Packaging.Package.System.IDisposable.Dispose() +183
   Infragistics.Documents.Excel.PackageWrapper.Dispose() +13
   Infragistics.Documents.Excel.Serialization.Excel2007.Excel2007WorkbookSerializationManager.Dispose(Boolean disposing) +13
   Infragistics.Documents.Excel.Serialization.WorkbookSerializationManager.System.IDisposable.Dispose() +27
   Infragistics.Documents.Excel.Workbook.SaveXLSXFile(Stream stream, IPackageFactory packageFactory) +135
   Infragistics.Documents.Excel.Workbook.SaveHelper(Stream stream, IPackageFactory packageFactory) +178
   Infragistics.Documents.Excel.Workbook.Save(String fileName, IPackageFactory packageFactory) +80
   CustomReports40.btnDownload_Click(Object sender, EventArgs e) in D:\Webfolders\publisher_dev\CustomReports40.aspx.vb:191
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563








Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET 
Version:4.0.30319.237 

Here is the code I use to append the SQLDataReader to the worksheet.  I have similar code for appending a datatable to the worksheet.  Both end up with the same error.

    Shared Sub AddSqlDataReader(ByVal dr As SqlDataReader, ByRef sheetToAddTo As Infragistics.Documents.Excel.Worksheet)
        Dim i As Integer = 0
        Dim iColCount As Integer = dr.FieldCount
        'add the column names from the datareader
        For i = 0 To iColCount - 1
            sheetToAddTo.Rows(0).Cells(i).Value = dr.GetName(i)
        Next

        Dim iRow As Integer = 0
        Dim iCol As Integer = 0
        While dr.Read
            For iCol = 0 To iColCount - 1
                Try
                    sheetToAddTo.Rows(iRow + 1).Cells(iCol).Value = dr(iCol).ToString
                Catch ex As ArgumentException
 
                End Try
            Next
            iRow += 1
        End While
        dr.Close()
    End Sub

The size of the worksheet seems to have something to do with it.  If it is a smaller worksheet, it doesn't return the error.  However, larger worksheets over the Excel 97/2003 row cap throw the error.

When the error occurs, a file actually is written to the target location... however, the file is only 2kb in size, and cannot be opened in Excel..