PHP Hacks

Hack 49. Create Excel Spreadsheets Dynamically

Use the new XML format supported by Microsoft Office 2004 to generate spreadsheets dynamically.

Word processing documents aren't the only things you might want to generate dynamically [Hack #48]. You also can create spreadsheets dynamically. With the new XML features of Microsoft Office 2004, we can build spreadsheets by simply using XML.

Start by creating a document in Excel, as shown in Figure 5-24.

Use the Save As command to save the spreadsheet in XML format. Then use the XML that's exported by Excel as the basis of your PHP file.

5.17.1. The Code

Save the code in Example 5-40 as spreadsheet.php.

Figure 5-24. The simple Excel spreadsheet that I use as a template

Example 5-40. Using state data to represent a spreadsheet

<?
header( "content-type: text/xml" );
$data = array(
array(state => "Alabama", population => 4530182 ),
array(state => "Alaska", population => 655435 ),
array(state => "Arizona", population => 5743834 ),
array(state => "Arkansas", population => 2752629 ),
array(state => "California", population => 35893799 ),
…
array(state => "Washington", population => 6203788 ),
array(state => "West Virginia", population => 1815354 ),
array(state => "Wisconsin", population => 5509026 ),
array(state => "Wyoming", population => 506529 )
);
echo( "<?xml version=\"1.0\"?>\n" );
echo( "<?mso-application progid=\"Excel.Sheet\"?>\n" );
?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
 <Author>Jack Herrington</Author>
 <LastAuthor>Jack Herrington</LastAuthor>
 <Created>2005-04-30T14:08:07Z</Created>
 <LastSaved>2005-04-30T14:09:14Z</LastSaved>
 <Company>Myself</Company>
 <Version>11.6360</Version>
</DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
 <DownloadComponents/>
 <LocationOfComponents HRef="file:///C:\APPINSTALL\Microsoft\Office_Pro_2003\"/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
 <WindowHeight>15930</WindowHeight>
 <WindowWidth>20025</WindowWidth>
 <WindowTopX>480</WindowTopX>
 <WindowTopY>105</WindowTopY>
 <ProtectStructure>False</ProtectStructure>
 <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
 <Style ss:ID="Default" ss:Name="Normal">
 <Alignment ss:Vertical="Bottom"/>
 <Borders/>
 <Font/>
 <Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="States">
<Table ss:ExpandedColumnCount="2"
ss:ExpandedRowCount="<? print( count( $data ) + 1 ) ?>"
x:FullColumns="1"
x:FullRows="1">
<Row>
<Cell><Data ss:Type="String">State</Data></Cell>
<Cell><Data ss:Type="String">Population</Data></Cell>
</Row>
<? foreach( $data as $row ) { ?>
<Row>
<Cell><Data ss:Type="String"><? print( $row["state"] ) ?></Data></Cell>
<Cell><Data ss:Type="Number"><? print( $row["population"] ) ?></Data></Cell>
</Row>
<? } ?>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>

<ActiveRow>2</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

I used the original XML spreadsheet as the basis for the script and then added the header() function at the beginning to indicate that this is XML data (so that the browser won't mishandle it). The data in the script is the hardcoded data of the state populations of the United States from the 2000 census. I use a foreach loop to create the rows of the table in the spreadsheet.

Getting ss:ExpandedRowCount correct is critical. If these numbers don't match up, Excel will refuse to load the spreadsheet and will give only a cryptic error response. In general, as I have created spreadsheets using this mechanism, I find that Excel is finicky about data integrity. I try to make small adjustments to the XML until I get it where I want it. Making too many changes in one shot is an exercise in frustration, as Excel is likely to refuse the faulty data, with little or no indication as to exactly what went wrong.

5.17.2. Running the Hack

Copy the files to the server and navigate to the PHP script using Internet Explorer. The first thing you'll see is Figure 5-25.

Figure 5-25. The security dialog that pops up when the page is loaded

Internet Explorer brings up this security dialog when it sees non-HTML content. Clicking the Open button launches the Excel control within the Internet Explorer window with the XML spreadsheet data. This is shown in Figure 5-26; just what we wanted!

Figure 5-26. The completed spreadsheet with the dynamic data

5.17.3. See Also

• "Create RTF Documents Dynamically" [Hack #48]

• "Suck Data from Excel Uploads" [Hack #45]

Origami Paper AirPlane