PHP - Office XML: Creating an excel document

October 16, 2010 by Christoff Truter PHP   Microsoft Office  

Last month (2010/9) I wrote a post about how to generate an excel document using OpenXML and C#. In this post we're going to have a look at how to do something similar using PHP.

However instead of using OpenXML, I am going to demonstrate how to achieve this using another method called Office XML (not to be confused with OpenXML), which is one of the easiest methods to generate Office documents.

The basic layout for a spreadsheet using Office XML looks something like this:

<?xml version="1.0" ?> 
<?mso-application progid="Excel.Sheet"?> 
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
	<ss:Worksheet ss:Name="Sheet1">
		<ss:Table>
			<ss:Row>
				<ss:Cell>
					<ss:Data ss:Type="String">ID</ss:Data> 
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Firstname</ss:Data> 
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Lastname</ss:Data> 
				</ss:Cell>
			</ss:Row>
		</ss:Table>
	</ss:Worksheet>
</ss:Workbook>

What we've got here is a simple workbook containing a single worksheet with three cells on it.

There is a number of ways to generate XML using PHP e.g. DOMDocument, SimpleXML, XMLWriter etc, the following snippet demonstrates using SimpleXML (not the best option in PHP for generating xml e.g. flexibility wise - awesome for reading XML though)

$xmlns = "urn:schemas-microsoft-com:office:spreadsheet";

$xml = '<?mso-application progid="Excel.Sheet"?>'.
		'<ss:Workbook xmlns:ss="'.$xmlns.'" />';

$Workbook = new SimpleXMLElement($xml);

$Worksheet = $Workbook->addChild('ss:Worksheet');
$Worksheet->addAttribute('ss:Name', 'Sheet1', $xmlns);
$Table = $Worksheet->addChild('ss:Table');

$Row = $Table->addChild('ss:Row');

$Cell = $Row->addChild('ss:Cell');
$Data = $Cell->addChild('ss:Data', 'ID', $xmlns);
$Data->addAttribute('ss:Type', 'String', $xmlns);

$Cell = $Row->addChild('ss:Cell');
$Data = $Cell->addChild('ss:Data', 'Firstname', $xmlns);
$Data->addAttribute('ss:Type', 'String', $xmlns);

$Cell = $Row->addChild('ss:Cell');
$Data = $Cell->addChild('ss:Data', 'Lastname', $xmlns);
$Data->addAttribute('ss:Type', 'String', $xmlns);

echo $Workbook->asXml();

Now instead of writing a thousand lines of code to simply build a sheet, I wrote classes to simplify the preceding snippet, observe:

$Workbook = new Workbook();

$Worksheet = $Workbook->addWorksheet('Sheet1');
$Table = $Worksheet->addTable();
$Row = $Table->addRow(array('ID', 'Firstname', 'Lastname'));

$Workbook->Output();

This makes it rather simple to output the results of a SQL query to excel for example, but what about styling the sheet? The XML output for styling a row of data will look something like this:

<?xml version="1.0" ?> 
<?mso-application progid="Excel.Sheet"?> 
<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
	<ss:Styles>
		<ss:Style ss:ID="A">
			<ss:Font ss:FontName="Times" ss:Size="16" ss:Color="Red" ss:Bold="1" /> 
		</ss:Style>
	</ss:Styles>
	<ss:Worksheet ss:Name="Sheet1">
		<ss:Table>
			<ss:Row ss:StyleID="A">
				<ss:Cell>
					<ss:Data ss:Type="String">ID</ss:Data> 
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Firstname</ss:Data> 
				</ss:Cell>
				<ss:Cell>
					<ss:Data ss:Type="String">Lastname</ss:Data> 
				</ss:Cell>
			</ss:Row>
		</ss:Table>
	</ss:Worksheet>
</ss:Workbook>

Which will look something like this (using the wrapper classes I wrote):

$Workbook = new Workbook();
$Styles = new Styles($Workbook);

$Worksheet = $Workbook->addWorksheet('Sheet1');
$Table = $Worksheet->addTable();
$Row = $Table->addRow(array('ID', 'Firstname', 'Lastname'));

$Style = $Styles->setStyle('A', $Row);
$Style->SetFont('Times', '16', 'Red', true);

$Workbook->Output();

And thats pretty much it, the classes are by no means a comprehensive implementation of the Office XML standard, but it should give you an idea and get you on your way.

Additional reading
http://blogs.msdn.com/b/brian_jones/archive/2005/06/27/433152.aspx
http://msdn.microsoft.com/en-us/library/aa140062(office.10).aspx
http://www.microsoft.com/downloads/en/details.aspx?FamilyId=FE118952-3547-420A-A412-00A2662442D9&displaylang=en