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

?>

WizED 1.1 (for Wizards)

Instructions for the editor for my flash game Wizards (WizED v1.1) 

I am putting this in my blog as I don't have a website for the game set up yet, though I should do shortly.  As the editor (along with the game) have been quite rushed, and since it has all been a learning experience, programming things I have not attempted before, things will not all run perfectly.  I have made no attempt at error handling as of yet.  If a problem arises, the game will most likely crash.   

I know this game isn’t perfect, but I have worked hard on it and have learnt quite a lot (mainly that I need to learn better ways of doing things) and I hope you enjoy trying it and messing around with the editor.  I know there are problems with it, and I know it can be frustrating, but I would really appreciate any useful criticism and ideas on how to improve the game and the editor.  I will be working on updates now and again so hopefully the game will begin to improve over time. 

The Editor
With some objects, such as the collectable stars, and mover objects, once they are placed, there is no way of removing them without starting the level design from scratch (restarting the editor).  Mover objects can be drawn over with the blank tile, but this will not actually remove it from the level.

Also, I have found it very hard finding any information on saving information from a flash file to the local computer.  For this reason, to save your level you must copy the level information into a text file and save it in the 'Levels' folder of the 'Wizards 2_2' directory (which you will have to have copied or downloaded onto your computer from the CD-Rom or website).  The next version will allow levels to be loaded from webpage url's.

Unfortunately, for now anyway, once you have clicked the 'make' button, you will not be able to go back and edit your level using the level editor.  Also, there is no way as of yet for importing levels to edit using the editor.  This is something I will be adding in the next version.  So before you click 'make', ensure that the level is exactly how you want it.

Block Brushes

The normal tile brushes are very easy to use and work well.  Simply choose the tile (there are three different tiles available in this version of the editor, and four unused ones (red/other colours).  There is also the blank tile (which has a red line through it) which is used for removing tiles you have placed.  Once you have selected your tile, click and drag around on the map to place the tiles down.  You can place as many tiles as you like without slowing down the game any considerable amount.  If you go wrong, use the blank tile.  You can use the red tiles if you wish, they will just show up as they do on the editor when playing the game (in the current version at least).

Collectable (small) Stars 
The small stars are the collectable items in the game.  You must have at least one of these on your map somewhere or the 'make' button wont function.  Ensure that you put the stars in places that are reachable by one of the characters (in Wizards v2.2, characters can jump approximately 4 blocks high.  This may vary in later versions of the game).  The star brush icon is small so look at the output box at the bottom left of the screen to ensure it reads ‘Star Brush’ before clicking on the map.  You can only place one star at a time, and only one in each box.  In v2.2, stars are always placed in the bottom left of the box (again, may vary in later versions).  Be careful placing stars as they cannot be removed in the editor.  Although stars do not have to be reachable for the ‘make’ button to function, the level exit stars will not appear until all of the smaller stars have been collected in the level, meaning the level will not be completable.   

Also be careful with the amount of stars that you place, as they can start to slow the game down considerably.

Exit Stars
A level can only have, and must have two exit stars to be able to use the make button, and for the game to load it.  Ensure the stars are reachable by the characters (at the same time) to make the level completable.  Although you cannot directly remove exit stars, if you have the exit star brush selected and click on the map when two exit stars already exist, then both exit stars will disappear. 

Mover Blocks
Mover blocks are the big pain with the game and the editor.  These need a lot of work until they are fully usable within the game.  To create a moving block, click the red block to the right of the large star at the top of the screen.  The output text will read ‘Mover Block.  Select a tile type’.  Although you must select a tile type, this is completely irrelevant as all blocks appear as block type 3 (the four brown blocks).  Once a tile type has been selected, type in a number in both the text boxes beside the mover brush.  The top number refers to how many blocks above the selected one the mover block will move up to, and the bottom number how for the mover block will move down to.  There is no cap on the number you can enter, though errors will probably get thrown back at you if you type huge numbers (I’ve not tried). 

When moving blocks move at different heights to each other, the character gets confused when landing on one and starts jumping up and down on the lower ones.  This is annoying and unplayable, so I advise having all moving blocks at the same height.

There is nothing stopping a mover block moving down from the top screen to the bottom screen or vice versa.  I don’t think that I have actually implemented the ability for the bottom character to use mover blocks, but this will definitely be added in the next version.  As stated before, mover blocks cannot be deleted using the editor. 

Editing with Text Editor
If you really want to edit the level after you have created it and found something isn’t working right, then it IS possible to open the xml file in a text editor and make changes.  If you are deleting a star or mover item, ensure that you edit the ‘starNum’ and ‘moverNum’ tags near the start of the file relevant to the changes made.

Saving your Level

When you press the make button, assuming no errors come up, the xml data for the level will appear in a text box.  The text is automatically copied to the clipboard so just open up your text editor and paste it in (the file may look a bit odd but don’t worry).  Save it in the Levels folder as an xml file (e.g. myLevel.xml).  Now when you run Wizards2_2.exe (from the same file as the Levels folder sits) just type in ‘Levels\myLevel.xml’ or whatever you have saved it as in the text box and click ‘Load’ to start your level.

[XML] Loading level layouts into tile based Flash Game

Introduction

I am starting the XML stuff for my wizards game from scratch. Yesterday after trying several methods of getting multiple levels to load, I found out about using XML to load in data to a flash file.  I used several websites to help me understand the concept (Emanuel Feronato,8 Bit Rocket and by far the most useful and easy to understand, Kirupa).

 

Anyway, thinking I had learnt completely how to use it, I dived straight in to adding the code into my project.  This sort of worked.  I am still unsure why it DIDN'T work completely.  I managed to load in the level and display it on the screen, but I couldn't work out why it wasn't showing properly (the tiles were all the same, as oposed to showing the correct tile based on the number in the XML file). 

 

So, this is my blog of doing all the XML walkthrough stuff in a blank project, without any of the other code or anything else confusing me.  It's also sort of a tutorial, so follow it if you like.  It may not be the best way of doing the thing I am trying to acheive, but I will try and explain what I am doing as I go along. 

 

 

Wizards 2.0 Game

I think I’ve gone mad.  I can’t for the life of me work out what I have done wrong here.  The number between each <titlecol> tag gives the tile numer which should be applied to the tile when it loads.  There are three tiles, tile0 (blank), tile 1 (the 2x2 blocks you can see in the image with grass on the top) and a third tile, tile 2 (a 2x2 block of the same tile without brown on). The ordering of the <tilecol> tags determines where the tiles in that row go, and the different <tilerow> determines on what row it’s column’s tiles go.

 

 

(above: the level design)

<map>

<tilerow>

 

<tilecol>1</tilecol>
<tilecol>2</tilecol>
<tilecol>1</tilecol>
<tilecol>2</tilecol>
<tilecol>1</tilecol>
<tilecol>2</tilecol>
<tilecol>2</tilecol>
<tilecol>2</tilecol>
<tilecol>2</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>
<tilecol>0</tilecol>

</tilerow>

 

the xml code (sample):

 

 

 

 

 

(above: the three different tiles)

 

If it was that somehow the tile 1 tiles were obscuring the tile 2 tiles, being sized wrong or something then I could  understand this, but I tested it by making the tiles on the top row ‘1,2,1,2,1,2,2,2,2’ and when I run the game it is plainly obvious that there is nothing there, it’s not putting a block down!

 

I don't know why I think writing a blog about it will help, but I may realize something stupid I have missed.

 

Well I havn't yet.  Anyway I have lots more stuff to add to the blog, loads more screen shots and things, which I will get around to doing eventually.

 

(note: I am using Adobe Contribute CS3 to edit this blog.  I previously tried using Microsoft Word which DOES have the ability to edit blogs, but I couldn't get it to upload images, and I found that other people had this problem by googling my problem.  However, I am having the same problem with Adobe Contribute although I can't see anything on the internet about similar problems).

Stuff I've been doing

I have redesigned my website, created a new banner, background, added links to other pages of mine, such as this blog, my flikr account, etc.


(http://www.guiblin.com/chris)



I have created the flickr account which I have been adding some pictures to, including 3D things created in maya and 2D stuff from Illistrator.



I have been doing a lot of sketching of random character designs and stuff, mostly quite abstract and cartoony. I have scanned a sketchbook in and created a PDF file of it. The upload for the whole PDF keeps timing out both on my server back at home and my file den account, blogger too is refusing to upload pictures any more to here, so I will try again later. (one page can be seen as the background image of my website).

I have also been doing some colouring in of images that I sketched and scanned in. (I uploaded these before i tried the sketches that wont upload)

Daily Routine by ~chrisguiblin on deviantART

Other stuff: been doing a little bit of playing around with Visual C#, learning some basic stuff. I haven't got anything really to show from that but just been learning to use different controls, create them at run time and other window related stuff.

Also got my hands on UnrealEd 3 and been doing some practicing of that in creating simple rooms and such. I have been watching videos on Game Trailers (http://www.gametrailers.com/player/usermovies/136126.html) showing basic tutorials on using the engine. I have been writing notes on stuff I have learnt from the tutorials I have watched so far and this will be very usefull for this terms project.

What else....I have been working a bit on my flash game from my last term's project, 'Wizards'. I have started it from scratch and using online tutorials about tiles used in tile based platform games I have created a basic way of using arrays to map out the levels. To do this on a bigger scale I need to look into using xml files to load in the level information, but I have given this a break for now to work on learning to use UnrealEd.