Sometimes, it's very useful to give the user the ability to download data from your website. One of the most common formats is as *.csv file, which can be imported to many applications.
However: What if we generate an already formatted *.xls file, which could be already opened and saved by Excel? Besides data, we can give format to it and we'll give the user a ready to use spreadsheet!!
This article will show you how to generate an Excel file "on the fly", using DW, PHP and data from a MySQL database.
To achieve this, all you need is a blank php file and a table. No special libraries are needed. Ready? Let's start then.
1.- Add some code:
First of all, you need to add these lines at the very beginning of your document:
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
And, If you want to give the user that "open or save" dialog box, simply add another line between the PHP tags (thanks Bergin for the tip):
Now, give it a try: After you've written it, press F12 and see how Excel will open it.
Save the file (for example as "test_xls.php).
What have we done so far? We are telling the browser that the file we are sending will be an xls file despite its "php" extension, so it should call excel to open it, and make sure that previous content is not cached. That's all.
Now, we just need to put the data into it.
2.- Add a table:
Let's see what happens when we add a simple table to our document, just like the one above:
Hit F12 again: The spreadsheet now has data!
3.- Give it some format:
We can give the table the appearance that we want: You can modify the cells' width and background color, apply bold or italics to the text, change its font and color... Just use the property inspector to modify the properties (CSS Styles are not supported, sorry).
4.- Make it more dynamic:
We can also make the spreadsheet display dynamic results. For example, we can make it display the sum of Apples and Oranges automatically. Where's the trick? Well, simply adding Excel formulas to our table in DW:
Why B1:B2? Think of it this way: every cell in our table will be a cell in Excel, so the coordinates must match:
Modify the values for oranges and apples in the resulting Excel spreadsheet: Total is also updated!!
Important Notice: Remember that localized versions of Excel use different function names!: English versions use SUM() and Spanish versions, for example, use SUMA(). Be careful with this when you write them! However, you can write some code to find visitor's default language and change the function names. Any volunteers?...
Download sample file (English version):Just copy it into your PHP Site, open it with DW and hit F12 to test it. If you are running a localized version of MS Excel, you should change function names to make it work as expected
Next Part: Adding Data using MySQL