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