The Requirement
Over the last couple of days I have been working on creating a way of getting data from a MySQL Database and getting this into an Excel spreadsheet for a project for a client. This is something I had done before so I thought it would be no big task. In fact, phpMyAdmin (apparently, though I haven't tried it) has a way of exporting data into a CSV file which is readable by excel.
The Problem
The problem is, a CSV file is a simple dump of data into a file, which contains no formatting, and so can make the spreadsheet look pretty ugly as well as hard to read. This is obviously not what a client, or anyone wanting to analyse the data from a spreadsheet wants.
The Solution
So after hours and hours and hours of searching the internet and trying all different methods (none of which seemed to work correctly - for me) for one reason or another, I decided to reverse engineer an Excel file; create a basic spreadsheet, format it as required, and save it as an Excel XML file. Then all I would have to do is open it up in a text editor, see how Excel was formatting the file, and recreate this with some php code and adding in the functionality to get the data from the required database then save that into an xml file.
If you want to do a lot of formatting to your spreadsheet, then I recommend you reverse engineer your own Excel file sheet, and recreate it with php yourself. If there is a quicker way of doing this, let me know, but this is the only way I could personally get it working.
Tools
Something I found very useful for this was a basic online tool called XML Pretty Print (http://xmlprettyprint.com/) that converts ugly xml with all the tags pushed up together in a very hard to read way into a much more organised, easy to look at layout. This made it a lot easier to separate the different lines of XML code when it came to creating the php file.
I also used Compare My Files (http://www.comparemyfiles.com/) to compare the XML file I exported from Excel with the XML file created from the code when it wasn't working, to see what was wrong with the php created XML file.
Example
Here is what I ended up with. It took quite a bit of playing around with to get it working fully. The slightest mistake and Excel would show a blank spreadsheet. (To be honest, I was using Open Office, so Excel may have opened it, I don't currently have Excel installed to test it on).
Feel free to play around with this and see how it works for you.
//Connection to Database - Fill in with the details for your database
$host = '';
$user = '';
$pass = '';
$database = '';
$table = '';
//Code to connect to the database
$linkID = mysql_connect($host, $user, $pass) or die("Could not connect to host.");
mysql_select_db($database, $linkID) or die("Could not find database.");
//Query the database - select all fields
$query = "SELECT * FROM `$table`";
$resultID = mysql_query($query, $linkID) or die("Data not found.");
//The XML code. $xml will store the entire xml structure and will be
//saved to an xml file at the end.
$xml = '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '3';
$xml .= '#c0c0c0';
$xml .= '';
$xml .= '';
$xml .= '4';
$xml .= '#ff0000';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '9000';
$xml .= '13860';
$xml .= '240';
$xml .= '75';
$xml .= '';
//STYLES - This is the style information used for the rows,
//columns and individual cells
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
//END STYLES
//WORKSHEET
$xml .= '';
//TABLE
$xml .= '
';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
$xml .= '';
//HEADER ROW
$xml .= '';
//CELLS
$xml .= ''; |
$xml .= 'Order ID';
$xml .= '';
$xml .= ''; |
$xml .= 'Invoice Number';
$xml .= '';
$xml .= ''; |
$xml .= 'Customer ID';
$xml .= '';
$xml .= ''; |
$xml .= 'Store Number';
$xml .= '';
$xml .= ''; |
$xml .= 'Total';
$xml .= '';
//End CELLS
$xml .= '';
//End HEADER ROWS
//Code to get rows from database - you will have to replace the row names with the rows in your database.
for($x = 0 ; $x < mysql_num_rows($resultID) ; $x++) //Loop through all rows and do the following for each
{
$row = mysql_fetch_assoc($resultID);
//ROWS
$xml .= '';
//CELLS
$xml .= ''; |
$xml .= ''. $row['order_id'] .'';
$xml .= '';
$xml .= ''; |
$xml .= ''. $row['invoice_no'] .'';
$xml .= '';
$xml .= ''; |
$xml .= '' . $row['customer_id'] .'';
$xml .= '';
$xml .= ''; |
$xml .= ''. $row['store_name'] .'';
$xml .= '';
$xml .= ''; |
$xml .= ''. $row['total'] .'';
$xml .= '';
//End CELLS
$xml .= '';
//End ROWS
}
$xml .= '
';
//End TABLE
$xml .= '';
$xml .= '';
//End WORKSHEET
$xml .= '';
//End WORKBOOK
//Write the entire xml structure and data to an xml file "xmlOutput.xml"
$fp = fopen("xmlOutput.xml", "w");
fwrite($fp, $xml);
fclose($fp);
echo "XML Data written to xmlOutput.xml";
?>