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

<%@ 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, 7906.25F, 40, 63250, 69, 109106.25F, 31, 49018.75F}, {"Fred", 32, 45640, 80, 114100, 45, 64181.25, 87, 124083.75F}, {"John", 60, 107355, 65, 116301.25F, 90, 161032.5F, 50, 89462.5F}, {"Jane", 40, 54770, 1, 1369.25F, 22, 30123.5F, 65, 89001.25F}, {"Total", 137, 215671.25F, 186, 295020.5F, 226, 364443.5F, 233, 351566.25F}}

 

            Dim row As Integer

            Dim column As Integer

 

            'Bind values to cells

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

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

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

                  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/EmployeeDataFormat" & Session.SessionID & ".xml")

            wb.SaveAs(fileName)

 

            'Prepare response for streaming

            Response.Clear()

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

            Response.AddHeader("Content-Disposition", "attachment; filename=EmployeeDataFormat.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>