<?php
// Create an instance of the
application
$psc = new
COM("PagosSpreadSheet.Application") or die("Pagos Spreadsheet
component is not installed");
// Add a workbook
$workbook =
$psc->Workbooks->Add();
// Load a worksheet from added
workbook
$worksheet =
$workbook->Worksheets->Item(1);
//Prepare worksheet data
$data = array
(
array(
"Employee",
"Volume Q1", "Sales Q1", "Volume Q2",
"Sales Q2",
"Volume Q3", "Sales Q3", "Volume Q4", "Sales
Q4"),
array(
"Helen", 5,
"=B2*1581.25", 40, "=D2*1581.25", 69,
"=F2*1581.25",
31, "=H2*1581.25"),
array(
"Fred", 32,
"=B3*1426.25", 80, "=D3*1426.25", 45,
"=F3*1426.25",
87, "=H3*1426.25"),
array(
"John", 60,
"=B4*1789.25", 65, "=D4*1789.25", 90,
"=F4*1789.25",
50, "=H4*1789.25"),
array(
"Jane", 40,
"=B5*1369.25", 1, "=D5*1369.25", 22,
"=F5*1369.25",
65, "=H5*1369.25"),
array(
"Total",
"=SUM(B2:B5)", "=SUM(C2:C5)", "=SUM(D2:D5)",
"=SUM(E2:E5)",
"=SUM(F2:F5)",
"=SUM(G2:G5)", "=SUM(H2:H5)", "=SUM(I2:I5)"),
);
$row;
$column;
$counter = 0;
// Bind values to cells
foreach ($data as $i => $val)
{
for ($j = 0; $j <
count($val); $j++)
{
if (strstr($val[$j],
"=") != false)
$worksheet->Cells($counter
+ 1, $j + 1)->Formula = $val[$j];
else
$worksheet->Cells($counter
+ 1, $j + 1)->Value = $val[$j];
}
$counter++;
}
// Apply styles
$a1i6 =
$worksheet->Range("A1:I6");
$a1i6->Font->Name =
"Arial";
$a1i6->Font->Size = 8;
$a1i6->HorizontalAlignment =
-4108; // center
$a1i1 =
$worksheet->Range("A1:I1");
$a1i1->Font->Bold = true;
$a1i1->Interior->Color =
0x33CCCC;
$a6i6 =
$worksheet->Range("A6:I6");
$a6i6->Font->Bold = true;
$a6i6->Interior->Color =
0x33CCCC;
$numberFormat =
"_(\"$\"* #,##0.00_);_(\"$\"*
\\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)";
$c2c6 = $worksheet->Range("C2:C6");
$e2e6 =
$worksheet->Range("E2:E6");
$g2g6 =
$worksheet->Range("G2:G6");
$i2i6 =
$worksheet->Range("I2:I6");
$c2c6->NumberFormat =
$numberFormat;
$e2e6->NumberFormat =
$numberFormat;
$g2g6->NumberFormat =
$numberFormat;
$i2i6->NumberFormat =
$numberFormat;
//Save workbook to a temporary file
for streaming
srand((double)microtime() *
1000000);
$filename = realpath("") .
"\\Spreadsheets\\Temp\\EmployeeData" . rand(100, 1000) .
".xml";
// Save spreadsheet file
$workbook->SaveAs($filename);
// Close workbook
$psc->Workbooks()->Close();
// Open saved workbook file
header('Content-disposition: attachment;
filename=EmployeeDataFormatFormula.xml');
header('Content-type:
application/vnd.ms-excel');
readfile($filename);
unlink($filename);
?>