Simple php excel XLS générator

 

source http://php.net/manual/en/ref.dba.php

If you need a ‘download data’ button that
automatically fires up a spreadsheet (like Excel), find that fputcsv()
isn’t working as expected, that none of the installed DBA database
engines create a spreadsheet that can be opened, and that XLS generating
components are just too heavy weight, then this might just hit the
spot:


<?php
// simple table to present
$data = array(
array(
'col1','col2'),
array(
1,2),
array(
3,4)
);

// pretend content (which is XML) is XLS native
header(« Pragma: public »);
header(« Expires: 0 »);
header(« Cache-Control: must-revalidate, post-check=0, pre-check=0 »);
header(« Cache-Control: private », false);
header(« Content-Type: application/vnd.ms-excel »);
header(« Content-Disposition: attachment; filename=\ »sheet.xls\ »; » );

// construct skeleton
$dom = new DOMDocument(‘1.0’, ‘utf-8’);
$dom->formatOutput = $dom->preserveSpaces = true; // optional
$n = new DOMProcessingInstruction(‘mso-application’, ‘progid= »Excel.Sheet »‘);
$dom->appendChild($n);

$workbook = $dom->appendChild(new DOMElement(‘Workbook’));
$workbook->setAttribute(‘xmlns’,‘urn:schemas-microsoft-com:office:spreadsheet’);
$workbook->setAttribute(‘xmlns:o’,‘urn:schemas-microsoft-com:office:office’);
$workbook->setAttribute(‘xmlns:x’,‘urn:schemas-microsoft-com:office:excel’);
$workbook->setAttribute(‘xmlns:ss’,‘xmlns:ss= »urn:schemas-microsoft-com:office:spreadsheet’);
$workbook->setAttribute(‘xmlns:html’,http://www.w3.org/TR/REC-html40);

$styles = $workbook->appendChild(new DOMElement(‘Styles’));
$style = $styles->appendChild(new DOMElement(‘Style’));
$style->setAttribute(‘ss:ID’,‘Default’);
$worksheet = $workbook->appendChild(new DOMElement(‘Worksheet’));
$worksheet->setAttribute(‘ss:Name’,‘sheet1’);
$xmltable = $worksheet->appendChild(new DOMElement(‘Table’));

// populate with data
foreach ($data as $datarow) {
$xmlrow = $xmltable->appendChild(new DOMElement(‘Row’));
foreach (
$datarow as $datacell) {
$xmlcell = $xmlrow->appendChild(new DOMElement(‘Cell’));
$xmldata = $xmlcell->appendChild(new DOMElement(‘Data’, $datacell));
$xmldata->setAttribute(‘ss:Type’, is_numeric($datacell) ? ‘Number’ : ‘String’);
}
}

// display and quit
echo $dom->saveXML();
?>