Pagos Spreadsheet Component

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>AutoInsuranceBatch</title>

</head>

<body>

<?php

function microtime_float()

{

list($usec, $sec) = explode(" ", microtime());

return ((float)$usec + (float)$sec);

}

$time_start = microtime_float();

$app = new COM("PagosSpreadSheet.Application")

or die("Pagos Spreadsheet component is not installed");

$app->Workbooks->Open(realpath("Spreadsheets/AutoInsurance/AutoInsuranceQuote.xml"));

$app->Workbooks->Open(realpath("Spreadsheets/AutoInsurance/AutoInsuranceQuoteData.xls"));

// Load workbooks

$wbTemplate = $app->Workbooks("AutoInsuranceQuote.xml");

$wbData = $app->Workbooks("AutoInsuranceQuoteData.xls");

// Load worksheets

$wsTemplate = $wbTemplate->Worksheets("Sheet1");

$wsData = $wbData->Worksheets("Data");

// Get count of customers

$rowCount = $wsData->Cells->Height;

$value= "";

// Prepare and save a quote workbook for each customer

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

{

// Set quote values from customer information

$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;

$filename = realpath("Spreadsheets/AutoInsurance/Quotes") . "\\Quote" . $rowIndex . ".xml";

// Save quote workbook

$wbTemplate->SaveAs($filename);

}

// Close workbooks

$wbData->Close();

$wbTemplate->Close();

// Close workbook

$app->Workbooks->Close();

$time_end = microtime_float();

$time = $time_end - $time_start;

echo "Runtime : $time seconds";

</body>

</html>

Sample PHP Code: Excel reporting - batch processing sample