Sample VB .NET Code: Excel reporting - richly formatted data and formulas

<%@ Page Language="VB"%>

<%@ Import Namespace="System.IO" %>

<%@ Import Namespace="PagosSpreadSheet" %>

<script runat="server">

      Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)

            'Create an instance of the application

            Dim app As New Application()

            'Add a workbook

            Dim wb As Workbook = app.Workbooks.Add()

            'Load a worksheet from added workbook

            Dim ws As Worksheet = wb.Worksheets("Sheet1")

 

            'Prepare worksheet data

            Dim data As Object(,) = {{"Employee", "Volume Q1", "Sales Q1", "Volume Q2", "Sales Q2", "Volume Q3", "Sales Q3", "Volume Q4", "Sales Q4"}, {"Helen", 5, "=B2*1581.25", 40, "=D2*1581.25", 69, "=F2*1581.25", 31, "=H2*1581.25"}, {"Fred", 32, "=B3*1426.25", 80, "=D3*1426.25", 45, "=F3*1426.25", 87, "=H3*1426.25"}, {"John", 60, "=B4*1789.25", 65, "=D4*1789.25", 90, "=F4*1789.25", 50, "=H4*1789.25"}, {"Jane", 40, "=B5*1369.25", 1, "=D5*1369.25", 22, "=F5*1369.25", 65, "=H5*1369.25"}, {"Total", "=SUM(B2:B5)", "=SUM(C2:C5)", "=SUM(D2:D5)", "=SUM(E2:E5)", "=SUM(F2:F5)", "=SUM(G2:G5)", "=SUM(H2:H5)", "=SUM(I2:I5)"}}

 

            Dim row As Integer

            Dim column As Integer

 

            'Bind values/formulas to cells

            For row = 0 To data.GetLength(0) - 1

                  For column = 0 To data.GetLength(1) - 1

                        'If input starts with "=" then bind it as formula

                        If data(row, column).ToString().IndexOf("=") <> 0 Then

                             ws.Cells(row, column).Value = data(row, column)

                        Else

                             ws.Cells(row, column).Formula = data(row, column).ToString()

                        End If

                  Next

            Next

 

            'Apply styles

            Dim a1i6 As Range = ws.Range("A1:I6")

            a1i6.Font.Name = "Arial"

            a1i6.Font.Size = 8

            a1i6.HorizontalAlignment = HorizontalAlignment.Center

 

            Dim a1i1 As Range = ws.Range("A1:I1")

            a1i1.Font.Bold = True

            a1i1.Interior.Color = System.Drawing.Color.FromArgb(51, 204, 204)

 

            Dim a6i6 As Range = ws.Range("A6:I6")

            a6i6.Font.Bold = True

            a6i6.Interior.Color = System.Drawing.Color.FromArgb(51, 204, 204)

 

            Dim numberFormat As String = "_(" & Chr(34).ToString() & "$" & Chr(34).ToString() & "* #,##0.00_);_(" & Chr(34).ToString() & "$" & Chr(34).ToString() & "* \(#,##0.00\);_(" & Chr(34).ToString() & "$" & Chr(34).ToString() & "* " & Chr(34).ToString() & "-" & Chr(34).ToString() & "??_);_(@_)"

            Dim c2c6 As Range = ws.Range("C2:C6")

            Dim e2e6 As Range = ws.Range("E2:E6")

            Dim g2g6 As Range = ws.Range("G2:G6")

            Dim i2i6 As Range = ws.Range("I2:I6")

            c2c6.NumberFormat = numberFormat

            e2e6.NumberFormat = numberFormat

            g2g6.NumberFormat = numberFormat

            i2i6.NumberFormat = numberFormat

 

            'Save workbook to a temporary file for streaming

            Dim fileName As String = Server.MapPath("Spreadsheets/Temp/EmployeeDataFormatFormula" & Session.SessionID & ".xml")

            wb.SaveAs(fileName)

 

            'Prepare response for streaming

            Response.Clear()

            Response.ContentType = "application/vnd.ms-excel"

            Response.AddHeader("Content-Disposition", "attachment; filename=EmployeeDataFormatFormula.xml")

           

            'Stream workbook to response

            Response.WriteFile(fileName, True)

 

            'Delete temporary file

            File.Delete(fileName)

 

            'Close workbook

            wb.Close()

 

            'End response

            Response.End()

      End Sub

</script>