Sample VB .NET Code: Excel reporting - batch processing sample

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

            Dim startTime, endTime As DateTime

            Dim runTime As TimeSpan

 

            'Get start time

            startTime = DateTime.Now

 

            'Create an instance of the application

            Dim app As New Application()

 

            'Open quote template workbook

            app.Workbooks.Open(Server.MapPath("Spreadsheets/AutoInsurance/AutoInsuranceQuote.xml"))

            'Open workbook that contains customer list for quotes

            app.Workbooks.Open(Server.MapPath("Spreadsheets/AutoInsurance/AutoInsuranceQuoteData.xls"))

 

            'Load workbooks

            Dim wbTemplate As Workbook = app.Workbooks("AutoInsuranceQuote.xml")

            Dim wbData As Workbook = app.Workbooks("AutoInsuranceQuoteData.xls")

 

            'Load worksheets

            Dim wsTemplate As Worksheet = wbTemplate.Worksheets("Sheet1")

            Dim wsData As Worksheet = wbData.Worksheets("Data")

 

            'Get count of customers

            Dim rowCount As Integer = wsData.Cells.Height

            Dim rowIndex As Integer

 

            Dim value As Object = ""

 

            'Prepare and save a quote workbook for each customer

            For rowIndex = 1 To rowCount - 1

                  'Set quote values from customer information

                  wbTemplate.Names("Name").Range.Value = wsData.Cells(rowIndex, 0).Value

                  wbTemplate.Names("Adress").Range.Value = wsData.Cells(rowIndex, 1).Value

                  wbTemplate.Names("CityStateZIP").Range.Value = wsData.Cells(rowIndex, 2).Value

                  wbTemplate.Names("yearlypremium").Range.Value = wsData.Cells(rowIndex, 3).Value

                  wbTemplate.Names("monthlypremium").Range.Value = wsData.Cells(rowIndex, 4).Value

                  wsTemplate.Range("B21").Value = wsData.Cells(rowIndex, 5).Value

                  wsTemplate.Range("B22").Value = wsData.Cells(rowIndex, 6).Value

                  wsTemplate.Range("B23").Value = wsData.Cells(rowIndex, 7).Value

                  wsTemplate.Range("B24").Value = wsData.Cells(rowIndex, 8).Value

                  wsTemplate.Range("B25").Value = wsData.Cells(rowIndex, 9).Value

                  wsTemplate.Range("B26").Value = wsData.Cells(rowIndex, 10).Value

                  value = wsData.Cells(rowIndex, 11).Value

                  If IsNothing(value) Then

                        wsTemplate.Range("D21").Value = ""

                  Else

                        wsTemplate.Range("D21").Value = value

                  End If

                  value = wsData.Cells(rowIndex, 12).Value

                  If IsNothing(value) Then

                        wsTemplate.Range("D22").Value = ""

                  Else

                        wsTemplate.Range("D22").Value = value

                  End If

                  value = wsData.Cells(rowIndex, 13).Value

                  If IsNothing(value) Then

                        wsTemplate.Range("D23").Value = ""

                  Else

                        wsTemplate.Range("D23").Value = value

                  End If

                  wsTemplate.Range("B31").Value = wsData.Cells(rowIndex, 14).Value

                  wsTemplate.Range("B32").Value = wsData.Cells(rowIndex, 15).Value

                  wsTemplate.Range("B33").Value = wsData.Cells(rowIndex, 16).Value

                  wsTemplate.Range("B34").Value = wsData.Cells(rowIndex, 17).Value

                  wsTemplate.Range("E31").Value = wsData.Cells(rowIndex, 18).Value

                  wsTemplate.Range("E32").Value = wsData.Cells(rowIndex, 19).Value

                  wsTemplate.Range("E33").Value = wsData.Cells(rowIndex, 20).Value

 

                  'Save quote workbook

                  wbTemplate.SaveAs(Server.MapPath("Spreadsheets/AutoInsurance/Quotes/Quote" + rowIndex.ToString() + ".xml"))

            Next

 

            'Close workbooks

            wbData.Close()

            wbTemplate.Close()

 

            'Calculate and display runtime

            endTime = DateTime.Now

            runTime = endTime.Subtract(startTime)

            Response.Write("Runtime : " + runTime.Seconds.ToString() & "." & runTime.Milliseconds.ToString().PadLeft(3, "0") & " seconds")

      End Sub

</script>