Exporting MySQL to Excel XML with formatting through PHP

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 .= 'False'; 

   $xml .= 'False';

$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";

?>

My Website

I've been doing some more work on my website and have now programmed it so different sections appear in the correct pages by looking up in the database the relevant data.

http://www.colinjensen.co.uk/~gibboco/index.php?pg=home

Website update

Over the summer I started learning some php and use of mySQL databases in websites.  After finishing the current version of the Wizards game (which is not really finished, but I'm having a break) I came back to doing a little bit of web stuff.  Here is the page at the moment of writing this page http://www.colinjensen.co.uk/~gibboco/1/index.php  (its on my friends website, not got around to getting my own domain name yet).  Theres nothing much there, and I know the design is pretty much none existant.  The only thing the site does is gets the information from the database and displays different posts in the blog depending on which category is selected. Nothing amazing and needs work but it's a start.

PHP (4) - Sorting By Date

PHP & MYSQL TUTORIAL/PROGRESS BLOG


Comments/Guest Book

PART 4 "Sorting By Date"

The next thing I wanted to do was to be able to sort the information backwards, so the latest items of data appear at the top. Although I realized this would be possible to do by simply doing a descending sorting of the ID, I thought it would be more useful to sort descending by date as this is another piece of information that would be useful to provide on a comments page/guest book, etc.

Adding Date field into database table

This is done by going back into the phpMyAdmin front end and clicking the structure tab. Underneath the table is a bit that says Add (number) field(s) At End of Table/At Beginning of Table/After (field name). Since it is more organized to keep the ID as the first field, choose After, and the field name you want it to be after (ID in this case). Name the field 'Date' and select type as 'Date' too. Save this then go back to the php file.

Writing the date into the database

All you need to do in the php is add 'Date' into the list of fields you are adding to and 'CURDATE()' as its associated value. The line should look like this:

$query = "INSERT INTO example1 (Date, Name, Subject, Comment, URL) VALUES (CURDATE(), '$Name', '$Subject', '$Comment', '$URL')";

Save the file and open it from within Firefox. Press submit and then if no errors appear, go back into phpMyAdmin and click the Browse tab, where you should see the date stored in the most recent Id's date field (click ID to make the ID list descending so the newest is at the top).




Information/help from:

page resource
electric toolbox
Tiz Taz

©Chris Guiblin 2008
URL: www.guiblin.com/chris/
EMAIL: chris@guiblin.com

PHP, Programming etc

I have tried several half attempts at installing php and mysql onto my server and gave up, I havn't got anywhere web design or php wise. I keep setting myself projects to get this done, and to create a website, but then I keep giving up.Anyway my friend from about five years back (Jensen) who used to make php websites for my comic management pointed me in the direction of an application called XAMPP which does the whole php, mysql thingy really easily. For now I just have it on my desktop computer to learn some php. Once I have some stuff to put up I will probably install it on the server.

Anyway, I think it is working:Not actually tried it out yet, but I thought I would post something since it has been a while. I have been away for three weeks visiting family (instead of the originally planned one or two weeks). Although I got a lot of designs and stuff done I didn't have access to a computer and so didn't get anything done in any projects I was hoping to achieve. Anyway, my newly revised aims for the rest of the summer are to:1/ learn php and get some basic websites made using the language2/ Design a simple, but polished room in Maya3/ Create an interactive comic/gameI have also added a plug in for Firefox called ScribeFire which allows you to post directly to your blog from your web browser. I haven't tried posting yet, but if this blog posts successfully then I would definitely recommend it.

Anyway my friend from about five years back (Jensen) who used to make php websites for my comic management pointed me in the direction of an application called XAMPP which does the whole php, mysql thingy really easily. For now I just have it on my desktop computer to learn some php. Once I have some stuff to put up I will probably install it on the server.

Anyway, I think it is working:



Not actually tried it out yet, but I thought I would post something since it has been a while. I have been away for three weeks visiting family (instead of the originally planned one or two weeks). Although I got a lot of designs and stuff done I didn't have access to a computer and so didn't get anything done in any projects I was hoping to achieve. Anyway, my newly revised aims for the rest of the summer are to:

1/ learn php and get some basic websites made using the language
2/ Design a simple, but polished room in Maya
3/ Create an interactive comic/game

I have also added a plug in for Firefox called ScribeFire which allows you to post directly to your blog from your web browser. I haven't tried posting yet, but if this blog posts successfully then I would definitely recommend it.

(update: it works great)