Sample C# Code: Excel reporting - batch processing sample

<%@ Page Language="C#"%>

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

<%@ Import Namespace="PagosSpreadSheet" %>

<script runat="server">

      protected void Page_Load(object sender, EventArgs e)

      {

            DateTime start, end;

            TimeSpan runTime;

 

            //Get start time

            start = DateTime.Now;

 

            //Create an instance of the application

            Application app = 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

            Workbook wbTemplate = app.Workbooks["AutoInsuranceQuote.xml"];

            Workbook wbData = app.Workbooks["AutoInsuranceQuoteData.xls"];

 

            //Load worksheets

            Worksheet wsTemplate = wbTemplate.Worksheets["Sheet1"];

            Worksheet wsData = wbData.Worksheets["Data"];

 

            //Get count of customers

            int rowCount = wsData.Cells.Height;

            int rowIndex;

 

            object value = "";

 

            //Prepare and save a quote workbook for each customer

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

            {

                  //Set quote values from customer information

                  wbTemplate.Names["Name"].Range.Value = wsData.get_Cells(rowIndex, 0).Value;

                  wbTemplate.Names["Adress"].Range.Value = wsData.get_Cells(rowIndex, 1).Value;

                  wbTemplate.Names["CityStateZIP"].Range.Value = wsData.get_Cells(rowIndex, 2).Value;

                  wbTemplate.Names["yearlypremium"].Range.Value = wsData.get_Cells(rowIndex, 3).Value;

                  wbTemplate.Names["monthlypremium"].Range.Value = wsData.get_Cells(rowIndex, 4).Value;

                  wsTemplate.get_Range("B21").Value = wsData.get_Cells(rowIndex, 5).Value;

                  wsTemplate.get_Range("B22").Value = wsData.get_Cells(rowIndex, 6).Value;

                  wsTemplate.get_Range("B23").Value = wsData.get_Cells(rowIndex, 7).Value;

                  wsTemplate.get_Range("B24").Value = wsData.get_Cells(rowIndex, 8).Value;

                  wsTemplate.get_Range("B25").Value = wsData.get_Cells(rowIndex, 9).Value;

                  wsTemplate.get_Range("B26").Value = wsData.get_Cells(rowIndex, 10).Value;

                  value = wsData.get_Cells(rowIndex, 11).Value;

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

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

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

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

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

                  wsTemplate.get_Range("B31").Value = wsData.get_Cells(rowIndex, 14).Value;

                  wsTemplate.get_Range("B32").Value = wsData.get_Cells(rowIndex, 15).Value;

                  wsTemplate.get_Range("B33").Value = wsData.get_Cells(rowIndex, 16).Value;

                  wsTemplate.get_Range("B34").Value = wsData.get_Cells(rowIndex, 17).Value;

                  wsTemplate.get_Range("E31").Value = wsData.get_Cells(rowIndex, 18).Value;

                  wsTemplate.get_Range("E32").Value = wsData.get_Cells(rowIndex, 19).Value;

                  wsTemplate.get_Range("E33").Value = wsData.get_Cells(rowIndex, 20).Value;

 

                  //Save quote workbook

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

            }

 

            //Close workbooks

            wbData.Close();

            wbTemplate.Close();

 

            //Calculate and display runtime

            end = DateTime.Now;

            runTime = end.Subtract(start);

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

      }

</script>