For a change, I've taken break from bashing internals, and got back to real work. (More on DBDO later this week hopefully)

One of my on-going projects, that has been dragging on longer than I would of liked is a shipping management application. I think it's mentioned in the archives, but for anyone who missed it, it is a mid sized XUL application which deals primarily with the management of a trading companies shipping requirements. I originally outsourced the main development, and have been tidying up and refining the code as we near final deployment (which as usual has taken longer than expected.)

This week I sat down and focused on the last major part of the project, reporting. Almost all the requirements for reporting include the ability to download an excel file of the data. So previously I had been making heavy use of PEAR's Spreadsheet_Excel_Writer. In using it, I had gone through various stages of evolution

• Writing raw Excel_Writer code in PHP, This however becomes very tedious, is not amazingly readable, kind of breaks the seperation of display/computation. And tends to be less flexible over a long period of time.
• Using a gnumeric as a template and using XML_Tree to merge data with it and output via Spreadsheet_Excel_Writer, again this helped in terms of enabling a simpler API for spreadsheet writing, and moving some of the layout/look and feel into the Gnumeric template. But the code for doing this was not quite as elegant as I would have liked.
• Using Javascript to read HTML tables and create a CSV file, that is sent to the server, and back again as text/csv mimetype (forcing the browser to open it in excel/openoffice etc.). Which was nice from an architectural point of view, by lacked any formating.
• And finally this week. Using javascript to generate a Spreadsheet_Excel_Writer specific XML file (by mixing a XML template file and the HTML content of the page), sending it to the server, and then letting PHP use the DOM extension and simple iteration with Spreadsheet_Excel_Writer to generate the page.

This weeks solution while not quite complete has a number of key advantages, some of which appeared after I started using it.

• No display level code goes into the Action->Data manipulation stage (we just store the data ready for the template engine/ template to render)
• It is possible to visualize the data prior to it ending up in the excel file.
• hence debugging the data output and finding issues is a lot quicker
• More code reuse,
• the library for XML to Excel is simple to reuse,
  
• the code for extracting the data from the html and generating XML is simple enough for copy & paste. and maybe possible to create a js library eventually.
• It offers infinate possibilities for formating, and changing layout.
• Less memory intensive, the data retrieval/storage and excel file create are broken up into two seperate processes.
  

The extended entry includes a few more details....

The process.

Starting with the xml excel template:
This gives an example of a very simple template, without the data in there..

<workbook filename="test.xls">
     
    <format name="greyhead" 
        Align="center"
        Bold="1"
        FgColor="silver"
        Size="10"
        />
    <format name="dottedDate" 
        NumFormat='dd"."mm"."yy' />
        
    <format name="percent"
        NumFormat="0.00%" />
        
    <format name="dollarvalue"
        NumFormat='$"#,##0.00_);[Red]("$"#,##0.00)' />
           
 <worksheet name="sheet1" active="true">
 
 <column firstcol="0" lastcol="0" width="25"/>
 <column firstcol="1" lastcol="40" width="15" />
 </worksheet>
</workbook>

The page then uses javascript to load this template (xmlhttprequest etc.) and starts adding <cell elements to represent the data.)

<script type="application/x-javascript"> 
 
    function download_as_excel()
    {
        var form = document.getElementById('_post');
        if (!form) {
            alert('not loaded yet');
            return;
        }
        var wbq = new phpRequest(rooturl + 
		'/FlexyShipping/templates/spreadsheets/base_excel.xml');
        
        var wbd = wbq.getXML();
        
        var ws = wbd.getElementsByTagName('worksheet')[0];
        var wb = wbd.getElementsByTagName('workbook')[0];
        
        wb.setAttribute('filename','summary-'+  
		document.getElementById('date').childNodes[0].nodeValue +'.xls');
        ws.setAttribute('name',
		document.getElementById('title').childNodes[0].nodeValue);
        var cell = wbd.createElement('cell');
        cell.setAttribute('row',0);
        cell.setAttribute('col',0);
        var cell_value = wbd.createTextNode(
            'Summary for ' + 
	    document.getElementById('title').childNodes[0].nodeValue + 
            ' as of ' + document.getElementById('date').childNodes[0].nodeValue
             );
        cell.appendChild(cell_value);
        ws.appendChild(cell);
        // now the rows..
         
        // <cell col="A" row="1">Test< / cell>
        // <cell col="B" row="2" type="Number" format="test1">30< / cell>
        var rows = document.getElementById('datagrid').getElementsByTagName('tr');
        //alert(rows.length);
        for(var row=0;row<rows.length;row++) {
            cols = rows[row].getElementsByTagName('td');
            for(var col=0;col < cols.length; col++) {
                 
                if (!cols[col].childNodes.length) {
                    continue;
                }
                // skip blannk cells
                 if (!cols[col].childNodes[0].nodeValue.
                                replace(/^\s*|\s*$/g,"").length) {
                        continue;
                    }
                var cell = wbd.createElement('cell');
                cell.setAttribute('row',row+1);
                cell.setAttribute('col',col);
                //fixme
                if (cols[col].getAttribute('xls:type')) {
                    cell.setAttribute('type',
			cols[col].getAttribute('xls:type'));
                }
                
                if (cols[col].getAttribute('xls:format')) {
                    cell.setAttribute('format',
			cols[col].getAttribute('xls:format'));
                }
                var cell_value = wbd.createTextNode(
			cols[col].childNodes[0].nodeValue);
                if (cols[col].getAttribute('xls:formula')) {
                    var s = cols[col].getAttribute('xls:formula');
                    s = s.replace(/#row#/g,(row + 2 ));
                    //alert(s);return;
                    cell_value.nodeValue = s;
                }
                if (cols[col].getAttribute('xls:percent')) {
                    cell_value.nodeValue = 0.01 * cell_value.nodeValue;
                }
                cell.appendChild(cell_value);
                ws.appendChild(cell);
            }
        }
        var ser = new XMLSerializer();
        
        var str = ser.serializeToString(wbd);
        //alert(str);
        
        document.getElementById('_xml').setAttribute('value',str);
        form.submit();
        
    }
 
  </script>

The final HTML output uses extra attributes (in the xls: namespace)  to store formating and cell styling, which is picked up by the javascript code above.

<table id="datagrid">
 <!-- header row -->
   <tr class="head">
       
      <td xls:format="greyhead">Customer</td>
      <td xls:format="greyhead">Order Date</td>
      <td xls:format="greyhead">delivery</td>
      <td xls:format="greyhead">Last ETD</td>
      <td xls:format="greyhead">Days Late</td>
      <td xls:format="greyhead">Supplier</td>
      <td xls:format="greyhead">Item</td>
      <td xls:format="greyhead">Qty</td>
      <td xls:format="greyhead">Del. Perf.</td>
      <td xls:format="greyhead">Ord. Ammount</td>
      <td xls:format="greyhead">Order Num</td>
        
    </tr>
    <tr flexy:foreach="results,r,row">
    
            <td>{row.customer_name}</td>
            <td xls:type="Date" xls:format="dottedDate">{row.ordered_date}</td>
            <td xls:type="Date" xls:format="dottedDate">{row.delivery_date}</td>
            <td xls:type="Date" xls:format="dottedDate">{row.getMaxShipDate()}</td>
            <td xls:format="numbervalue"  xls:type="Number">{row.shipmentLateDays}</td>
            <td >{row.supplier_name}</td>
            <td>{row.getFirstProductName()}</td>
            <td xls:format="numbervalue"  xls:type="Number">{row.getTotalQty()}</td>
            <td>&nbsp;</td>
            <td xls:format="dollarvalue"  xls:type="Number">{row.getTotalBuyCost()}</td>
            <td>{row.order_number}</td>
     
    </tr>
</table>

The final step of posting it to a url, that runs the library
http://devel.akbkhome.com/svn/index.php/akpear/XML_Spreadsheet_Writer/Writer.php

Since it's DOM usage is pretty simple, hopefully a PHP5 version will not be too much hastle...