Create Excel Files on the Fly

I hate throwing away code. Recently, we had a requirement to create an Excel spreadsheet with formulas and specific data for a particluar user. Of course we could always use Reporting Services to get the data in Excel format but then there would be no formulas. So I ran across this article by Peter Bromberg titled Create Dynamic ASP.NET Excel Workbooks In C#. It’s a short article but it gives a good code listing at the end. I created a prototype in VB.Net & ASP.Net and it was working pretty well, a bit slow, but it worked. Then it got yanked. Since I hate throwing away code a modified version ends up here. Enjoy.


  Imports Microsoft.Office.Interop


  Public Class test

    Inherits System.Web.UI.Page


    Private Sub RemoveFiles(ByVal strPath As String)


      Dim di As System.IO.DirectoryInfo = New System.IO.DirectoryInfo(strPath)

      Dim fiArr() As System.IO.FileInfo = di.GetFiles()

      For Each fri As System.IO.FileInfo In fiArr


        If fri.Extension.ToString() = “.xls” Or fri.Extension.ToString() = “.csv” Then


          Dim min As TimeSpan = New TimeSpan(0, 0, 60, 0, 0)

          If fri.CreationTime < DateTime.Now.Subtract(min) Then


          End If


        End If



    End Sub


    Private Sub CreateExcelWorkbook()


      Dim strCurrentDir As String = Server.MapPath(“.”) & “\”

      RemoveFiles(strCurrentDir) ‘ utility method to clean up old files

      Dim oXL As Excel.Application

      Dim oWB As Excel._Workbook

      Dim oSheet As Excel._Worksheet

      Dim oRng As Excel.Range



        GC.Collect() ‘ clean up any other excel guys hangin’ around…

        oXL = New Excel.Application

        oXL.Visible = False


        ‘ Get a new workbook.

        oWB = CType(oXL.Workbooks.Add, Excel.Workbook)

        oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)


        ‘ Write Header in Excel

        oSheet.Cells(1, 1) = “Col1”

        oSheet.Cells(1, 2) = “Col2”

        oSheet.Cells(1, 3) = “Col3”

        oSheet.Cells(1, 4) = “Col4”

        oSheet.Cells(1, 5) = “Total Value”


        ‘ Format Cells.

        oSheet.Range(“A1”, “Z1”).Font.Bold = True

        oSheet.Range(“B1”, “D3”).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        oSheet.Range(“E1”, “E3”).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

        oSheet.Range(“E1”, “E3”).NumberFormat = “$0.00”

        oSheet.Range(“E1”, “E1”).Formula = “= A1 * C1”

        oSheet.Range(“E2”, “E2”).Formula = “= A2 * C2”

        oSheet.Range(“E3”, “E3”).Formula = “= A3 * C3”


        ‘ AutoFit columns A:Z.

        oRng = oSheet.Range(“A1”, “Z1”)



        oXL.Visible = False

        oXL.UserControl = False

        Dim strFile As String = “report” & System.DateTime.Now.Ticks.ToString() & “.xls”

        oWB.SaveAs(strCurrentDir & strFile)


      Catch e As Exception



        ‘ Need all following code to clean up and extingush all references!!!

        oWB.Close(Nothing, Nothing, Nothing)









        oSheet = Nothing

        oWB = Nothing

        oXL = Nothing

        GC.Collect() ‘ force final cleanup!

      End Try

    End Sub

  End Class