How to convert XML to Excel?

Topics: Developer Forum, User Forum
May 8, 2014 at 7:55 PM
Edited May 8, 2014 at 9:02 PM
PHP version: 5.4.12
PHPExcel version: 1.8.0
OS version: Windows 7 Professional 64-bit

Hello,

I have a simple HTML form, that asks for customer name and date range.
When the form is submitted a new web browser tab is opened and displays XML data based on values provided in the form.
<!-- the form -->
<table>
<form method="GET" action="generateReport.php" target="_blank">
    <tr>
        <td><label for="customer">Customer:</label></td>
        <td colspan="3">
            <select id="customer" name="customer" type="text" required />
            <!-- list of loyal customers -->
            </select>
        </td>
    </tr>
    <tr>
        <td colspan="4"><label>Date range:</label></td>
    </tr>
    <tr>
                    <!--    
                            inputs "from" and "to" are turned into
                            calendar widgets thanks to jQuery's Calendar Widget
                    -->
                    <td>From</td>
                    <td><input type="text" id="from" name="from" required /></td>
                    <td>To</td>
                    <td><input type="text" id="to" name="to" required /></td>
    </tr>
    <tr>
        <td colspan="4"><input type="submit" name="submit" /></td>
    </tr>
</form>
</table>
This XML data will contain a lot of data, but I'm interested in extracting and saving only some data as Excel from that XML.
Below is a pseudo-structure of the XML:
<results>
    <row id="1">
        <case_number>7454</case_number>
        <priority_level>3</priority_level>
        <customer_name>John Smith</customer_name>
        <summary>Computer will not turn on</summary>
        <description>When power button is pressed - nothing happens</description>
        <case_status>resolved</case_status>
        <resolution>Power cable was unplugged</resolution>
        <total_hours>0.50</total_hours>
        <date_created>01/01/2014 09:00:05 AM</date_created>
        <date_resolved>01/01/2014 09:30:05 AM</date_resolved>
    </row>
    <row id="2">
    </row>
    <row></row>
</results>
I grab all that XML data with simplexml_load_file() function and put it into $sxml variable. Like so:
// $url contains URL to XML that is generated
// based on provided values in the form above
$sxml = simplexml_load_file($url);
How to convert the XML data in $sxml to Excel, and add SUM() function to the very last cell under all the hours column that would add up all the total_hours, and add formatting to the table with font styles and cell colors?

Here's an example of the finished table I would like to achieve from the XML example provided above:

Image
  • As you can see I only need case_number, total_hours, summary, resolution data from XML and the rest are unimportant in this report. Customer name and date range are gathered from the passed form values
P.S. I tried to follow XMLReader.php example provided with PHPExcel download, but I got the Excel file that was produced by the script was empty. No errors or anything.