Sample VB .NET Code: Excel reporting - Data only

<%@ 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

 

            'Save workbook to a temporary file for streaming

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

            wb.SaveAs(fileName)

 

            'Prepare response for streaming

            Response.Clear()

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

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