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

            fri.Delete()

          End If

 

        End If

      Next

 

    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

      Try

 

        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”)

        oRng.EntireColumn.AutoFit()

 

        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

        System.Diagnostics.Debug.WriteLine(e)

      Finally

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

        oWB.Close(Nothing, Nothing, Nothing)

        oXL.Workbooks.Close()

        oXL.Quit()

 

        System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng)

        System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL)

        System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)

        System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB)

 

        oSheet = Nothing

        oWB = Nothing

        oXL = Nothing

        GC.Collect() ‘ force final cleanup!

      End Try

    End Sub

  End Class