title: VB .NET server generating Excel file on the fly
body: <p>I have a VB .NET web application running on a server with multiple requests and performance requirements.
  I have a function that retrieves some data from a DB and has to generate an excel report to show to the system users.
  I somehow did it, but my solution has not the performance I'd like it to have: basically what my code does is:</p>
  
  <ul>
  <li>The server accepts the request of report generating</li>
  <li>The server fills an excel file</li>
  <li>The server <strong>saves locally</strong> the xls file</li>
  <li>The server attaches the file to the html response and the user downloads it</li>
  <li>The server deletes the file (when? I need to handle borderline cases too)</li>
  </ul>
  
  <p>The code snippet is like:</p>
  
  <pre><code>Public Sub ExportaDataTableToExcel(ByVal dt As System.Data.DataTable, ByVal Page As System.Web.UI.Page, ByVal ReportName As String)
          Dim oExcel As Excel.Application
          Dim oBook As Excel.Workbook
          Dim oSheet As Excel.Worksheet
          Dim colIndex As Integer
          Dim rowIndex As Integer
  
          oExcel = New Excel.Application
          oBook = oExcel.Workbooks.Add(Type.Missing)
          oSheet = oBook.Worksheets(1)
  
  
          'Export the Columns to excel file
          For Each dc In dt.Columns
              colIndex = colIndex + 1
              oSheet.Cells(1, colIndex) = dc.ColumnName
  
          Next
  
          oBook.SaveAs("C:\file.xls")
          oSheet = Nothing
          oBook.Close()
  
          Page.Response.AddHeader("content-disposition", "attachment;filename=" &amp; ReportName &amp; ".xls")
          Page.Response.Charset = String.Empty
          Page.Response.ContentType = "vnd.application/ms-excel"
  
  
          Page.Response.TransmitFile("C:\file.xls")
  
          Page.Response.Flush()
  
          Page.Response.End()
  
          oExcel.Quit()
      End Sub
  </code></pre>
  
  <p>As you can see the server generates the xls file locally in C:\file.xls, then trasmittes the file in the response page.</p>
  
  <p>What I'd like to do, and I really don't know if it's possible as I did not found any example on this, is generating the xls file on the fly without saving it locally (maybe returning as a Stream of bytes or something like this) and then assembling this as xls file in the response page, without saving the file locally.</p>
  
  <p>I tried using both Page and System.IO.StringWriter with no luck, maybe I'm doing something wrong.
  The following code acts abnormally, asking me to save modifies on the file (on the server side), and downloading an xls file that has parts of the html of the page, generating errors about missing css files and displaying part of the page. So, with this approach I'm almost at zero. Here it is the code:</p>
  
  <pre><code>Public Sub ExportaDataTableToExcel(ByVal dt As System.Data.DataTable, ByVal Page As System.Web.UI.Page, ByVal ReportName As String)
          Dim oExcel As Excel.Application
          Dim oBook As Excel.Workbook
          Dim oSheet As Excel.Worksheet
          Dim colIndex As Integer
          Dim rowIndex As Integer
  
          oExcel = New Excel.Application
          oBook = oExcel.Workbooks.Add(Type.Missing)
          oSheet = oBook.Worksheets(1)
  
  
          'Export the Columns to excel file
          For Each dc In dt.Columns
              colIndex = colIndex + 1
              oSheet.Cells(1, colIndex) = dc.ColumnName
  
          Next
  
          oSheet = Nothing
          oBook.Close()
  
  
          Dim stringWrite As System.IO.StringWriter = New System.IO.StringWriter
          Dim htmlWrite As System.Web.UI.Html32TextWriter = New System.Web.UI.Html32TextWriter(stringWrite)
          Page.Response.Clear()
          Page.Response.AddHeader("content-disposition", "attachment;filename=" &amp; ReportName &amp; ".xls")
          Page.Response.Charset = String.Empty
          Page.Response.ContentType = "vnd.application/ms-excel"
  
  
  
  
          Page.Response.WriteFile(stringWrite.ToString)
          Page.Response.Flush()
  
          Page.Response.End()
  
          oExcel.Quit()
      End Sub
  </code></pre>