Sample ASP Code: Excel reporting - batch processing sample

<%@ language="JavaScript" %>

<%

      var stime = new Date;

     

      // Create an instance of Application

      var app = Server.CreateObject("PagosSpreadSheet.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

      var wbTemplate = app.Workbooks("AutoInsuranceQuote.xml");

      var wbData = app.Workbooks("AutoInsuranceQuoteData.xls");

     

      // Load worksheets

      var wsTemplate = wbTemplate.Worksheets("Sheet1");

      var wsData = wbData.Worksheets("Data");

 

      // Get count of customers

      var rowCount = wsData.Cells.Height;

      var rowIndex;

 

      var value = "";

 

      // Prepare and save a quote workbook for each customer

      for (rowIndex = 2; rowIndex < rowCount+1; rowIndex++)

      {

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

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

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

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

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

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

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

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

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

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

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

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

            wsTemplate.Range("D21").Value = value != null ? value : "";

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

            wsTemplate.Range("D22").Value = value != null ? value : "";

            value = wsData.Cells(rowIndex, 14).Value;

            wsTemplate.Range("D23").Value = value != null ? value : "";

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

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

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

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

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

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

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

 

            // Save quote workbook

            wbTemplate.SaveAs(Server.MapPath("Spreadsheets/AutoInsurance/Quotes/Quote" + (parseInt(rowIndex)-1) + ".xml"));

      }

     

      // Close workbooks

      wbData.Close();

      wbTemplate.Close();

     

      var diff = new Date((new Date) - stime);

     

      Response.Write("Runtime : " + diff.getSeconds() + "." + diff.getMilliseconds() + " seconds");

%>