Sample JAVA Code: Excel reporting - batch processing sample

package com.pagos.sample;

 

import java.io.File;

import java.util.Date;

import javax.servlet.jsp.JspWriter;

import com.pagos.Application;

import com.pagos.Workbook;

import com.pagos.Worksheet;

 

public class AutoInsuranceBatch {

 

      private String sessionId;

     

      private JspWriter out;

     

      private final static String PATH = "C:/Sun/AppServer/domains/domain1/applications/j2ee-modules/PSC42Samples/";

     

      public AutoInsuranceBatch(String sessionId, JspWriter out) {

            this.sessionId = sessionId;

            this.out = out;

      }

     

      public void executeSample() throws Exception

      {

            long start = new Date().getTime();

           

            //Create an instance of the application

            Application app = new Application();

           

            //Open quote template workbook

            app.getWorkbooks().open(PATH+"Spreadsheets/AutoInsurance/AutoInsuranceQuote.xml");

            //Open workbook that contains customer list for quotes

            app.getWorkbooks().open(PATH+"Spreadsheets/AutoInsurance/AutoInsuranceQuoteData.xls");

     

            //Load workbooks

            Workbook wbTemplate = app.getWorkbooks().getItem(PATH+"Spreadsheets/AutoInsurance/AutoInsuranceQuote.xml");

            Workbook wbData = app.getWorkbooks().getItem(PATH+"Spreadsheets/AutoInsurance/AutoInsuranceQuoteData.xls");

           

            //Load worksheets

            Worksheet wsTemplate = wbTemplate.getWorksheets().getItem("Sheet1");

            Worksheet wsData = wbData.getWorksheets().getItem("Data");

           

            //Get count of customers          

            int rowIndex;

           

            Object value = "Example";

           

            File f = new File(PATH+"Spreadsheets/AutoInsurance/Quotes/");

            f.mkdirs();      

           

            //Prepare and save a quote workbook for each customer

            for (rowIndex = 0; rowIndex < 10; rowIndex++)

            {

                  //Set quote values from customer information

                  wbTemplate.getNames().getItem("Name").getRange().setValue(wsData.getCells(rowIndex, 0).getValue());

                  wbTemplate.getNames().getItem("Adress").getRange().setValue(wsData.getCells(rowIndex, 1).getValue());

                  wbTemplate.getNames().getItem("CityStateZIP").getRange().setValue(wsData.getCells(rowIndex, 2).getValue());

                  wbTemplate.getNames().getItem("yearlypremium").getRange().setValue(wsData.getCells(rowIndex, 3).getValue());

                  wbTemplate.getNames().getItem("monthlypremium").getRange().setValue(wsData.getCells(rowIndex, 4).getValue());

                  wsTemplate.getRange("B21").setValue(wsData.getCells(rowIndex, 5).getValue());

                  wsTemplate.getRange("B22").setValue(wsData.getCells(rowIndex, 6).getValue());

                  wsTemplate.getRange("B23").setValue(wsData.getCells(rowIndex, 7).getValue());

                  wsTemplate.getRange("B24").setValue(wsData.getCells(rowIndex, 8).getValue());

                  wsTemplate.getRange("B25").setValue(wsData.getCells(rowIndex, 9).getValue());

                  wsTemplate.getRange("B26").setValue(wsData.getCells(rowIndex, 10).getValue());

                  value = wsData.getCells(rowIndex, 11).getValue();

                  wsTemplate.getRange("D21").setValue(value);

                  value = wsData.getCells(rowIndex, 12).getValue();

                  wsTemplate.getRange("D22").setValue(value);

                  value = wsData.getCells(rowIndex, 13).getValue();

                  wsTemplate.getRange("D23").setValue(value);

                  wsTemplate.getRange("B31").setValue(wsData.getCells(rowIndex, 14).getValue());

                  wsTemplate.getRange("B32").setValue(wsData.getCells(rowIndex, 15).getValue());

                  wsTemplate.getRange("B33").setValue(wsData.getCells(rowIndex, 16).getValue());

                  wsTemplate.getRange("B34").setValue(wsData.getCells(rowIndex, 17).getValue());

                  wsTemplate.getRange("E31").setValue(wsData.getCells(rowIndex, 18).getValue());

                  wsTemplate.getRange("E32").setValue(wsData.getCells(rowIndex, 19).getValue());

                  wsTemplate.getRange("E33").setValue(wsData.getCells(rowIndex, 20).getValue());

 

                  //Save quote workbook             

                  wbTemplate.saveAs(PATH+"Spreadsheets/AutoInsurance/Quotes/Quote-"+sessionId+"-"+rowIndex+".xml");

            }

 

            //Close workbooks

            wbData.close();

            wbTemplate.close();

 

            //Calculate and display runtime

            long end = new Date().getTime();

            long runTime = end - start;

            out.print("Runtime : ");

            out.print(runTime / 1000.0f);

            out.print(" seconds<br>");

           

      }

     

}