Sample PHP Code: Excel reporting - richly formatted data

<?php

            // Create an instance of the application

            $psc = new COM("PagosSpreadSheet.Application") or die("Pagos Spreadsheet component is not installed");

                 

            // Create a new 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, 7906.25, 40, 63250, 69, 109106.25, 31, 49018.75),

                       

                  array(

                        "Fred", 32, 45640, 80, 114100, 45, 64181.25, 87, 124083.75),

                       

                  array(

                        "John", 60, 107355, 65, 116301.25, 90, 161032.5, 50, 89462.5),

                       

                  array(

                        "Jane", 40, 54770, 1, 1369.25, 22, 30123.5, 65, 89001.25),

                       

                  array(

                        "Total", 137, 215671.25, 186, 295020.5, 226, 364443.5, 233, 351566.25)

            );

 

            $row;

            $column;

            $counter = 0;

   

            // Bind values to cells

            foreach ($data as $i => $val)

            {

                  for ($j = 0; $j < count($val); $j++)

                  {

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

 

            $workbook->SaveAs($filename);

 

            $psc->Workbooks()->Close();

           

    // Open saved workbook file         

    header('Content-disposition: attachment; filename=EmployeeDataFormat.xml');

    header('Content-type: application/vnd.ms-excel');

    readfile($filename);

 

    unlink($filename);

?>