|
QuickUsageGuide
Introductionphp-excel is a very, very simple class to generate excel 2003 files from PHP5 using the excel XML-notation. The main advantage is its easy handling and quick implementation. Prerequisitesphp-excel expects a two-dimensional array and dumps it into columns and rows. Please first download the file from the branches directory. InclusionStep 01: Just put the class-excel-xml.inc.php in your project directory and include it using (of course you may choose to put it wherever you like): require (dirname (__FILE__) . "/class-excel-xml.inc.php"); Step 02: For testing, create a two dimensional array: $myarray = array (
1 => array ("Oliver", "Peter", "Paul"),
array ("Marlene", "Mica", "Lina")
);Step 03: Instanciate class and dump the array into the document. $xls = new Excel_XML; $xls->addArray ( $myarray ); $xls->generateXML ( "testfile" ); The document (with filename "testfile") will be delivered to your browser for downloading. All values of the array will be defined as "string" - I may at support for more types later. |
► Sign in to add a comment
Thnx a lot, this code really helped me :-) saved lot of time.
Kusal
Big thanks dude ! Worked like a charm first time I ran it, just the way I like it. You don't have a class like this for Word lying around, by any chance ?
Oh, and just an FYI: on my office 2003, everything works perfectly. On office 2007, the user is prompted with a security alert, saying the contents of the .xls is not really Excel data (duh, it's XML). It can still import it, the user just has to click 'Yes, I trust it!'.
// Here is a little modification to support numbers, by Eivind, Norway
// foreach key -> write value into cells foreach ($array as $k => $v):
if(is_numeric(utf8_encode($v)))
elseendforeach;
Building on Eivind's work, we had an additional requirement. Numbers that are zero-padded need to be preserved as strings (e.g., 000123). Here is the code change to accomplish this:
private function addRow ($array) { // initialize all cells for this row $cells = ""; // foreach key -> write value into cells foreach ($array as $k => $v): if (is_numeric($v)) { //if is numeric and starts with zero, and is not zero and is not zero-decimal: if (substr($v, 0, 1) == "0" && $v != 0 && substr($v, 0, 2) !== "0.") { $type = "String"; } else { $type = "Number"; } } else { $type = "String"; } $cells .= "<Cell><Data ss:Type=\"$type\">" . utf8_encode($v) . "</Data></Cell>\n"; endforeach; // transform $cells content into one row $this->lines[] = "<Row>\n" . $cells . "</Row>\n"; }I get the following error from your class-excel-xml.inc.php file: Parse error: parse error, unexpected T_STRING, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or '}' in /home/httpd/Application08/class-excel-xml.inc.php on line 63
Bret, this is a PHP4 vs PHP5 issue. You can either upgrade your PHP or downgrade the code. Upgrading PHP is better if possible. PHP4 does not recognize the public/private distinction in classes, so remove it to downgrade. Change the public or private in front of each variable to "var" and then remove it from in front of each function.
I needed tot save the XLS instead of pointing it to the browser:
/** * generateAndSaveXML * * Generate the XML and save to file * * @param unknown_type $filename */ function generateAndSaveXML ($path, $filename){ $content = stripslashes ($this->header); $content .= "\n<Worksheet ss:Name=\"" . $this->worksheet_title . "\">\n<Table>\n"; $content .= "<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" ss:Width=\"110\"/>\n"; $content .= implode ("\n", $this->lines); $content .= "</Table>\n</Worksheet>\n"; $content .= $this->footer; if (!$handle = @fopen($path . $filename, 'w+')){ return false; } if (@fwrite($handle, $content) === false){ return false; } @fclose($handle); return true; } // Call using: $xls->generateAndSaveXML($_SERVER['DOCUMENT_ROOT'] . '/excel/', 'output.xls');addRow() method is set to private. Should be public. Works great once set to public. Thanks!
Jack
Hi there, Firstly I would like to thank you bcoz of the code and secondly I would like to ask you guys that how can I get data from an EXCEL file, modify and update the EXCEL file again!
any suggestions plz email me at mohfazel@gmail.com or I will check here straight away.
I'm sad that doesn't work with OpenOffice? :(
This library worked for me and saved time. Just an hour to modify a php page and route output into Excel.
Would it be feasible to modify the class so it provides some basic formatting controls such as column width and bold fonts?
Thinks
thanks! worked perfectly
<?php
?>
lescoutinhovr@gmail.com
this certainly does make it easy! only one issue i am having... ie7 simply renders the xml in the browser... no download/save dialogue. works a treat in firefox though.
any ideas?
1. Excel fails to read in cells that contain un-encoded XML entities: ampersand, apostrophe, double-quote, less-than and greater-than. So in addRow, all cells should be encoded:
$cells .= "<Cell><Data ss:Type=\"$type\">" . utf8_encode($this->xmlspecialchars($v)) . "</Data></Cell>\n";
with this function: public function xmlspecialchars($text) { return str_replace(''', ''', htmlspecialchars($text, ENT_QUOTES)); }
another extrapolation:
just pass in an SQL command:
and process it (sorry for bloated code, you know PHP so do however you feel is best)
// add new var var worksheet_labels=true; function hideColumnLabels() { $this->worksheet_labels=false; } function AddRows_RS($sql) { include(dirname(__FILE__)."/configfile.php"); $connection = mysql_connect($dbServer, $dbUsername, $dbPassword); if($connection) { mysql_select_db($dbDatabase, $connection); $result = mysql_query($sql) or die($query . '<br>' . mysql_error()); // get the header row, column field names from SQL if($result) { $type = "String"; $cells = ""; if($this->worksheet_labels) { for($i=0;$i<mysql_numfields($result);$i++)$cells .= "<Cell><Data ss:Type=\"$type\">" . utf8_encode($this->xmlspecialchars(mysql_fieldname($result, $i))) . "</Data></Cell>\n"; $this->lines[] = "<Row>\n" . $cells . "</Row>\n"; } while($row = mysql_fetch_array($result)) { $cells = ""; for($i=0;$i<mysql_numfields($result);$i++) { //(retain strings like '00123')if is numeric and starts with zero, and is not zero and is not zero-decimal: if (is_numeric($row[$i])) { if (substr($row[$i], 0, 1) == "0" && $row[$i] != 0 && substr($row[$i], 0, 2) !== "0.") $type = "String"; else $type = "Number"; } else $type = "String"; $cells .= "<Cell><Data ss:Type=\"$type\">" . utf8_encode($this->xmlspecialchars($row[$i])) . "</Data></Cell>\n"; } $this->lines[] = "<Row>\n" . $cells . "</Row>\n"; } mysql_free_result($result); } else // no results else { return false; } mysql_close($connection); } else // connection failed else { return false; } return true; }Is there any way to make this work with OpenOffice??? If so, how? Thanks!!
I used this great Wiki to get exactly what I needed - output to an Excel Spreadsheet including the column headers: <?php // include the php-excel class require (dirname (FILE) . "/class-excel-xml.inc.php");
$connection = mysql_connect("server", "id", "password"); mysql_select_db("database", $connection);
$query = "SELECT FROM incidents";
$result = mysql_query($query) or die($query . '<br>' . mysql_error());
$myheaderarray = array(); $myheaderarray = array('STAMP','incident_id','fac_id','room_descr','oth_descr','person_type','medrec_num','acct_num','person_name', 'person_lname','person_street','person_city','person_state','person_zip','person_phone','dob','inc_date', 'inc_time','fall_type','assisted','observe','fall_type2','assisted2','observe2','treat_var','med_var','pt_outcome', 'pt_outcome_oth', 'ni_type','pt_rights','near_miss','equip','environ','desc_oth','inc_descr','injury','inj_descr', 'rel_factor_fall', 'rel_factor_fall2','rel_factor_fall3','rel_factor_fall4','rel_factor_meds','rel_factor_oth', 'severity','not_type','note_oth','not_time','not_type2','note2_oth','not_time2','not_type3','note3_oth','not_time3', 'not_type4','note4_oth','not_time4','witness1_type','witness1_name','witness1_street','witness1_city','witness1_st', 'witness1_zip','witness1_phone','witness2_type','witness2_name','witness2_street','witness2_city','witness2_st', 'witness2_zip','witness2_phone','reporting_name','reporting_id','reporting_date','assess_ortho','rescore_morse', 'det_contrib','id_rec_medment','req_pharm_review','rev_curr_int','id_add_int','addr_bwlblddr','assure_safety_equip', 'id_env_issues','id_learn_pref','comm_team','comm_fam','notify_attending','comm_name','actions_descr','followup_descr', 'rm_severity','notified_ceo','not_ceo_datetime','notified_ho','not_ho_datetime','notified_plant','not_plant_datetime', 'notified_food','not_food_datetime','notified_attending','not_sattending_datetime','notified_ins','not_ins_datetime', 'notified_pharm','not_pharm_datetime','notified_dno','not_dno_datetime','notified_dto','not_dto_datetime','notified_oth', 'not_oth_datetime','not_oth_descr','root_cause_an','rca_date','rm_name','rm_date','Done');
$myarray = array(); while($row = mysql_fetch_array($result)) {
}// generate excel file $xls = new Excel_XML; $xls->addArray ( $myheaderarray ); $xls->addArray ( $myarray ); $xls->generateXML ( "incidents" );
?>
ewmusso
I got it to work with open office.
Go to the generateXML function. Change the file extension at the second header() call from xls to xml.
Done.
can some one suggest how I could add images into the file?
Thanks a Lot - great simple thing
For Russian users (when content data in CP1251):
In class-excel-xml.inc.php replace all 'UTF-8' to 'WINDOWS-1251' and replace 'utf8_encode($v)' to just '$v' (private function addRow)
Here is a reduced version for php4:
<?php class Excel_XML { var $header = "<?xml version=\"1.0\" encoding=\"UTF-8\"?\> <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
var $footer = "</Workbook>"; var $lines = array (); var $worksheet_title = "Table1"; function addRow ($array) function addArray ($array) function setWorksheetTitle ($title) function generateXML ($filename) } ?>A quick comment on the PHP4 code above...
The "AutoFitWidth?" line should not have the question mark. At least it did not work for me with it in.
Just FYI. Thanks janssen1990!
hi one small doubt, I need to generate multiple sheets in same excel file and need to write data to those sheets can u please help me out..... Thanks in Advance
Seenu
hi one small doubt, When i try to upload the spread sheet that i got from the above code i get "the file is not readable error" can u please help me out..... Thanks in Advance
Seenu
It's nice and work fine! Thanks
nice work guys! would be nice to have column header formating options
That worked really well! Thanks!
I did however change the addRow to a public function since I really didn't want to feed the entirety of my db into a variable, and then feed that to a file; it seemed redundant. What is the reason for having it be private anyway?
Where is a problem with MS IE8 generateXML is not work with it (in IE7 and FF3 work well) The error is looks like:
"the file is not readable error"
found solution with "the file is not readable error": http://ru2.php.net/manual/ru/function.header.php#88038
Need add some headers in function generateXML:
header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Cache-Control: private",false); header("Content-Transfer-Encoding: binary");(exactly don't know wich one help me)
Nice tutorial.....It's very helpful.
Zamshed Farhan http://www.zamshed.info
how to put image with excel spredsheet ?
this is a great tool. is there a way to make a hyperlink cell?
Thanks a lot for all these comments. I haven't exactly reviewed all of them, but a few issues have been fixed with the new version 1.1. Please check the download section of the project. Here are a few answers to some comments:
@xmarkvh (from 2007): I will see if I can improve the issue with the automatic type conversion (strings <> numbers). Sometimes, you do not want to convert values to numbers (article numbers starting with zero). I will see, if I can improve this in later version.
@z1nkum (Jan 2009): Version 1.1 now provides the remote setting of the charset in the constructor:
$xls = new Excel_XML('WINDOWS-1251');should do the trick ;)
@z1nkum (May 2009): I haven't tested this yet in IE8. I may improve the header-directive settings in the next version. Thanks for the input!
@jimnau9 (Dec 2008): Thanks for the input. I will implement an MS Excel/OpenOffice? switch in the next versions.
@gmeader (Oct 2008): Converting chars while writing into the cells is a question of performance. This is a great piece of code you put there! I will see if I can adapt this. I have to do some tests for this.
How to make hyper link?
this class ain't generate binary XLS file (try to open it with any text editor and see the XLS tags it has) so unfortunately it won't be processed by Open Office correctly...or we need another proper format for it...
If you manually rename the .xls file to .xml or .ods (OpenDocument? Spreadsheet), then OpenOffice? can open the file correctly. Tested on OpenOffice? 3.2.1. What I believe is OpenOffice? can open any file that's of Microsoft's OOXML format, but it screws up with .xls.
Modify the header with xls to xlsx, this will specify that the original format is xml