PHP class for exporting data in multiple worksheets Excel XML
I was trying to find the code to generate multiple worksheet Excel file, and after half an hour of searching PHP classes, blogs, forums and whatnot, decided to write the class from scratch. On the MSDN there is enough info, but one still has to figure some things out when coding the xml generator for Excel. And just a remark for MS guys – frames are out. So without further ado, here’s the class and lower, you can find the usage instructions.
It is fairly simple to use the class to generate the XML, here are some examples:
include 'excel_xml.php'; $excel = new excel_xml(); /** * Add style to your worksheet, it's reference will be "header" * You add it as an array with the key being the modifier and the value parameter. * You can add: * - size in pt * - font like "Georgia" * - color in hex for font color * - bgcolor in hex for background color * - bold as boolean (bold => 1) * - italic as boolean * - strike as boolean */ $header_style = array( 'bold' => 1, 'size' => '12', 'color' => '#FFFFFF', 'bgcolor' => '#4F81BD' ); $excel->add_style('header', $header_style); /** * Add row and attach the style "header" to it */ $excel->add_row(array( 'Username', 'First name', 'Last name' ), 'header'); /** * Add some rows, if you encapsulate the string inside asterisks, * they will get bold using the predefined style "bold" * If you append "|x" where x is a number, that cell will be * merged with the x following cells */ $excel->add_row(array( 'Anorgan|2' )); $excel->add_row(array( '*Marin*', 'Crnković' )); /** * You don't like the arrays, or already have * some form of csv generating script that uses strings? * No biggie, just delimit the string with ";" or "," */ $excel->add_row('Some number:;12'); /** * Tell the object to create the worksheet. * The passed string is the name of the worksheet */ $excel->create_worksheet('Users'); /** * If you invoke the generate method, you will get the * XML returned or... */ $xml = $excel->generate(); /** * ... you can pass the whole thing for download with * the passed string as the filename */ $excel->download('Download.xml');
it is interesting how no one wants to comment “proffesional” posts like this. with programming themes.
Thank you very much for this script!
There is a bug with the styles. excel_xml::add_style is never called. My fix is changing the code in excel_xml::prepare_cell
stavite captchu, protiv spamanja :)
Just turned on Akismet, hopefully there will be no need for a captcha.
Nice script. Very well documented. Cheers
Pretty good script, other options that i try are expensive(specially phpexcel) in the resources usage (memory and cpu), cheers
Really useful script!!!
Thank you very much Anorgan.
Only one question, why do you use “preg_match” to decide whether a value is Number or String? You could just use “is_numeric”. In fact using “preg_match” is a problem since it doesn’t recognize decimal numbers as Number. For example if you try to give “23.5″ as a value for a cell it will be stored as string. If you replace:
$type = preg_match(’/^([\d]+)$/’, $str) ? ‘Number’ : ‘String’;
with:
$type = is_numeric($str) ? ‘Number’ : ‘String’;
it works fine.
Still thank you very much for the script.
@gkatz:
Thank you for your comment! I did consider it, and opted for my solution just because some numers can be formated as “23.5″, some as “23,5″ (comma), and some “12.324,53″. The last 2 numbers “behave” better as strings in Excel, and the letter produces an error if presented as “Number” to Excel. I think that it is safer to present “wierd” formated numbers as “String”.
Hello,
Awesome script. Love the documentation and style–completely readable–and therefore usable!
I had to make a slight mod to get multiple worksheets working. The code uses implode (join) to glue worksheets with an empty string, but proper xml requires a linebreak, so the whole of my changes are (line 57)
From:
$xml .= join(”, $this->worksheets).$this->nl;
To:
$xml .= join(”\n”, $this->worksheets).$this->nl;
there are alot of bugs in this but thanks for the xml part bro
Hello
Can you send me an example exporting mysql data,
I have used this example, I used while loop for getting records from mysql and used $excel->add_row($data). But file not loading and shows the following error in C:\Documents and Settings\dprakash\Local Settings\Temporary Internet Files\Content.MSO/filename.
XML PARSE ERROR: Missing end-tag
Error occurs at or below this element stack:
Please advice me on this.
Thanks in Advance.
what about add it here http://www.jooria.com/scripts/PHP-Class-Scripts-129/
well this scipts works fine when there is small amount of data. but when i tried to export 4522 records it throughs the error of memory limit i tried setting it to even 900M still it does not seems to be working any solution?
Love the class! It was exactly what I was looking for. I was wondering though, is there a way to save the XLS file to a folder on the server instead of downloading it to a local machine?
Awesome script…
Very useful script, specially the documentation. It helps me lot. Thanks and cheers..
thank’s alot for your script and the documentation. cheerrss..
thanks for providing this. it saved me a lot of time. just added it to the concrete5 stats package: http://inneroptics.net/concrete5/stats/
the bug fix on comment #9 was required for me to get this working.
thank’s alot for the script. its help me alot…yeah!!
thanks for providing this.
but i need to ask you about using create table function.
and how can i create a table with cells border.
please help.
Excellent script, really very helpful :)
Can I add an image through this script? Please confirm. If not, I’ll have to work on some other script from scratch again :(
Thank you very much. Excellent script. It just solved my problem to create a multi-sheet excel file easily.
You save my day man …
Thanks a lot Anorgan, this script is far less painful than others i had found after a half day digging.
Very good …
Hi, It only give with one sheet, when i change this code
$excel->download(’Download.xls’);
It give me excel file with only one sheet ‘user’, I tried to create another sheet by duplicating $excel->create_worksheet(’Users_new’); but end up excel error, cannot open the file.
please help.
Freddie
Sorry for the first comment. Now I can generate with multiple sheet.
Can I ask you, is there anyway to just print html code to spreadsheet?
Coz My report is very complicated.
Thanks
Can anyone please give me a simple example with two sheets, thx !
Hi thanks for sharing your knowlegde, is not your obligation but I will really appreciate if you can give me some advice.
I’m trying to make your example work by enclosing it between php brackets; when I try to open the generated file excell (excell 2003 eng) crashes; any ideas why?
Thanks in advance
Hi, thanks for the script!
would appreciate an example for filling two worksheets as well… thx!
Thanks so much for providing individuals with an exceptionally splendid opportunity to discover important secrets from this web site.
does this even work? where is this class and hes methods declared?
I view something genuinely special in this website .
thank you for this script
Hai
this is nice script, Good job
Random Google results can lead to fantastic blogs like this. You are doing a good job, and we share lots of thoughts.
I am glad to be a visitant of this everlasting website ! , regards for this rare information! .
Hi,
I have used this script.It saved me a lot of time..But one issue i am facing is When i tried to Download the file in IE i am getting an error and the file s not loaded it is HTTPS site.
I am getting the below error.
Error: Unable to download this from internet site.