Generating xls file from corresponding xml

Question

I have an Excel (xls) template with example data. I need to prepare an Excel file with dynamically- generated data.

First, I made an XML file from the corresponding xls. The XML file looks like this :

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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>Op</Author>
  <LastAuthor>ufoq</LastAuthor>
  <Created>2011-11-18T06:54:25Z</Created>
  <LastSaved>2011-12-05T11:43:26Z</LastSaved>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9750</WindowHeight>
  <WindowWidth>23310</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>405</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font x:CharSet="238" x:Family="Swiss" ss:Size="8" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
......

Now I placed this content in symfony view and filled it with my generated data. I need to output in XML file format but I don't know how to to this. The new file must have the same structure as the template file so I cannot make a new file from scratch.

Answer 1

Looks like you have documents in the openxml format which you can process with the PHPExcel library.

---

If you just want to replace some simple values in your template you could also use DOMDocument and DOMXPath to process the xml document, e.g.

<?php
$doc = new DOMDocument;
$doc->loadxml( getData() );
$xpath = new DOMXPath($doc);
$xpath->registerNamespace('o', "urn:schemas-microsoft-com:office:office");
$xpath->registerNamespace('x', "urn:schemas-microsoft-com:office:excel");
$xpath->registerNamespace('ss', "urn:schemas-microsoft-com:office:spreadsheet");
$xpath->registerNamespace('html', "http://www.w3.org/TR/REC-html40");

foreach( $xpath->query('/ss:Workbook/o:DocumentProperties/o:LastAuthor') as $n ) {
    //echo '.';
    $text = $doc->createTextnode('SO Test');
    $n->replaceChild($text, $n->firstChild);
}
echo $doc->savexml();

function getData() {
    return <<< eox
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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>Op</Author>
  <LastAuthor>ufoq</LastAuthor>
  <Created>2011-11-18T06:54:25Z</Created>
  <LastSaved>2011-12-05T11:43:26Z</LastSaved>
  <Version>11.9999</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9750</WindowHeight>
  <WindowWidth>23310</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>405</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font x:CharSet="238" x:Family="Swiss" ss:Size="8" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   [...]
  </Style> 
 </Styles>
</Workbook>
eox;
}

---

update: If you want the client to recognize the file type you have to set the content (mime) type to the approriate value, see Office 2007 File Format MIME Types for HTTP Content Streaming. E.g.

$spreadsheet = new DOMDocument;
$spreadsheet->loadxml(getData());
someProcessing($spreadsheet);
if ( headers_sent() ) {
    die('error: headers already sent');
}
header('Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
echo $spreadsheet->save('php://output');