Reading data from Excel files using PHP class Spreadsheet_Excel_Reader
If you have already read th article about generating Excel workbooks using PHP, this is an addition. Here's an example how to read these files. It is useful if you have large database and need to update it often. Easy way to do this is to export to an Excel file, make the changes and import rather than designing interface for this purpose.
Supplying utilities
We'll use class Spreadsheet_Excel_Reader. Latest version of the class may be found here.After downloading the class, we will use it:
$reader=new Spreadsheet_Excel_Reader();
$reader->setUTFEncoder('iconv');
$reader->setOutputEncoding('UTF-8');
$reader->read($filename);
As data is internally written in UTF-16 encoding, we should specify what should be output encoding. Here comes an issue: this class uses iconv() or mb_convert_encoding() for conversion between encodings. So at least one of hose two should be enabled in PHP. In this case setUTFEncoder() specifies iconv(). We can change the output encoding to windows-1251 for example:
$reader->setOutputEncoding('CP-1251');
Displaying output data
Now let's output the data read. Information about sheets is stored in boundsheets variable. Here's a code for displaying each sheet's name:foreach ($reader->boundsheets as $k=>$sheet)
{
echo "\n$k: $sheet";
}
Data of the sheets is stored in sheets variable. For every sheet, a two dimensional array holding table is created. Here's how to print all data.
foreach($reader->sheets as $k=>$data)
{
echo "\n\n ".$reader->boundsheets[$k]."\n\n";
foreach($data['cells'] as $row)
{
foreach($row as $cell)
{
echo "$cell\t";
}
echo "\n";
}
}
Comments:
Irshad Ahmad (14-05-2008 12:33) :
Class that is available on sourceforge.net doesn't work properly. Do you have any other idea to do this?Jacob (30-05-2008 21:36) :
I noticed the sourceforge reader file attempts to load the oleread.inc file as Spreadsheet/Excel/Reader/OLERead.php so you could rename the oleread.inc file to that or edit the reader.php file to just require once the proper file oleread.inc .
Dave F (16-06-2008 22:34) :
Receiving this error on running the example:
Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 33030145 bytes) in C:\xampp\htdocs\PHPExcelReader\Excel\oleread.inc on line 172
I did have to rename the reference for reader.php
Marcio Roger Ferrari (14-07-2008 15:31) :
It seems that xls file that came as example is broken. I replaced it and now its workingnhojnivron at gmail dot com (20-08-2008 17:29) :
There is a problem reading the date format the expected output is mm/dd/yyyy but i get a ??/??/???????? like this 00/0606/08080808? anyone got a fix? TIAKamal (03-10-2008 14:37) :
How to read comments from cellajmacaro (07-10-2008 06:55) :
to fix the mm/dd/yyyy format
i did this simple fix.
in reader.php search for "function createDate" and replace it with this.
<pre>
function createDate($numValue)
{
if ($numValue > 1) {
$utcDays = $numValue - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS);
$utcValue = round(($utcDays) * SPREADSHEET_EXCEL_READER_MSINADAY);
$this->curformat = strtolower($this->curformat);
if ($this->curformat == 'mm/dd/yyyy' || $this->curformat == 'i/dd/yyyy') {
$this->curformat = 'Y-m-d';
}
$string = date ($this->curformat, $utcValue);
$raw = $utcValue;
} else {
$raw = $numValue;
$hours = floor($numValue * 24);
$mins = floor($numValue * 24 * 60) - $hours * 60;
$secs = floor($numValue * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60;
$string = date ($this->curformat, mktime($hours, $mins, $secs));
}
return array($string, $raw);
}
</pre>Nelson Hereveri (09-11-2008 18:54) :
<pre>
function createDate($numValue)
{
$parteEntera = intval($numValue);
//$parteDecimal = bcsub($numValue,$parteEntera,12);
$parteDecimal = $numValue - $parteEntera;
if ($parteDecimal > 0) {
$rawD = $parteDecimal;
$hours = intval(floor($parteDecimal * 24));
$mins = intval(floor($parteDecimal * 24 * 60) - $hours * 60);
$secs = intval(floor($parteDecimal * SPREADSHEET_EXCEL_READER_MSINADAY) - $hours * 60 * 60 - $mins * 60);
/*if($secs == 59) {
$mins++;
$secs = 0;
if($mins == 60) {
$hours++;
$mins = 0;
}
}*/
$stringD = date (AIRVIRO_HOUR_FORMAT , mktime($hours, $mins, $secs));
} else {
$stringD = '';
$rawD = 0;
}
if ($parteEntera > 0) {
$utcDays = $parteEntera - ($this->nineteenFour ? SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS1904 : SPREADSHEET_EXCEL_READER_UTCOFFSETDAYS);
$utcValue = round(($utcDays+1) * SPREADSHEET_EXCEL_READER_MSINADAY);
//$string = date ($this->curformat, $utcValue);
$stringE = date (AIRVIRO_DATE_FORMAT , $utcValue);
$rawE = $utcValue;
} else {
$stringE = '';
$rawE = 0;
}
if($parteEntera > 0 && $parteDecimal > 0) $string = $stringE . ' ' . $stringD;
elseif ($parteEntera > 0) $string = $stringE;
elseif($parteDecimal > 0) $string = $stringD; //($parteDecimal > 0 && $parteEntera == 0)
else $string = date (AIRVIRO_HOUR_FORMAT, mktime(0,0,0)); // $parteEntera == 0 && $parteDecimal == 0, implica zero horas
//return array($string , bcadd($rawE,$rawD,12));
//return array($string , $rawE + $rawD);
return array($string , $numValue);
}
</pre>ilde giron (25-01-2009 22:24) :
It works ok but only when excel file has three or less sheets.
Do I have to state the number of sheets I expect to read? How?
Is there a way to read single sheets instead of the whole file?
Also note that next code doesn't print the names of the sheets, instead, it displays the names of the arrays:
foreach ($reader->boundsheets as $k=>$sheet) {
echo "\n$k: $sheet";
}
Thanks in advance.Michael (09-02-2009 19:06) :
Hello,
I have a problem. I have in an .xls file the following field that I need to read:
Nunca se perderá!
I have called setOutputEncoding('UTF-8'); but it returns Nunca se perder!0000 in the web page.
Can anyone help me with this one?
Pradeep (19-02-2009 14:21) :
Hello,
It works fine. But, if any cell contain "588%" then output is displayed as "6%". If i want only "588%" then what can i do ?
Thanks in advance.Roberto (21-02-2009 23:22) :
Hi,
I am using the class for reading .xls files.
It works properly, but with cells whose data type is "percent".
In this case sometimes I get the cell contents divided by 100, sometimes not.
Is there some constant that let me to have a more stable output for "percentage" cells?
Many thanks in advance
RobertoCarl (15-04-2009 06:57) :
hi there .. im trying to get date values from excel with format "m/d/Y".
e.g 04/30/2009 but then it returns with this value Apr/Thu/2009200920092009.
any ideas?carl (15-04-2009 07:06) :
hi there .. im trying to get date values from excel with format "m/d/Y".
e.g 04/30/2009 but then it returns with this value Apr/Thu/2009200920092009.
any ideas?Kevin (22-07-2009 23:50) :
Hi,
I'm trying to download the SourceForge php class, but the download is not working. Is anyone else having difficulties downloading the file?Windshadow2005 (27-07-2009 13:17) :
Hi
I have problem while using SourceForge php class for reading data from xls file. But it is working fine in my local system but while I am putting same on server it's showing error "Notice: Uninitialized string offset: -512 in /home6/alleytec/public_html/mk/e-signingagent/excelRead/Excel/oleread.inc on line 27" something like this. Even the downloaded sample files are showing same error on server. Please help me out...Locoluis (06-08-2009 18:50) :
You probably have a 64-bit machine. There's a function in oleread.inc that doesn't work there.
Follow the following link for a fix.
http://www.phpbuilder.com/board/archive/index.php/t-10328608.htmlJacob (19-09-2009 14:19) :
My script works fine but it display following error.
Warning: Invalid argument supplied for foreach() in C:\Program Files\xampp\htdocs\web\siteadmin\importProperties.php on line 77Sundar (22-09-2009 13:01) :
I think development of this package has been stopped and if we use open office numbers show general anyone has any idea to fix itdaniel (06-03-2010 09:41) :
excel reader is working fine but i am facing one problem when i insert the value in database from excel, the ID inserts as 2407497 and auto increment onwards. I want it to from last ID iserted and auto increment onwards. i don't understand why it is taking ID like this? Help me out. ThanksFreeGroup (06-04-2010 13:09) :
Hi,
you can use the all in one solution
http://www.dbTube.org which is based on
Spreadsheet_Excel_Reader.
Advantage: visual excel composer....no coding.
greetings
AndreasChaitanya Mutyala (20-04-2010 20:29) :
is there a way to read a .xlsm file using these classes?Bharathi (21-04-2010 09:41) :
I need to read xlsx file?
Is it possible?pantek (04-06-2010 11:29) :
can it's used in linux?
i have tried this excel reader, but it can't work..developergtl (04-09-2010 11:38) :
I need to read xlsx file?
Is it possible?Shan (23-09-2010 08:11) :
Hi, if i try to read my own excel file named books.xls, the error message coming is: The filename books.xls is not readable
please suggest.Emyl (12-12-2010 11:05) :
Excellent work! Thanks.Rupesh (22-12-2010 07:28) :
Nice Thankskittu (15-03-2011 13:48) :
hi...
m getting following message...
Deprecated: Assigning the return value of new by reference is deprecated in D:\wamp\www\Neha\ymca\Excel\reader.php on line 261
The filename jxlrwtest.xls is not readable
will nebody plz help me out???
its urgent......
Karol (22-03-2011 22:43) :
hey,
you must get into this line and delete '&' sign. The same can be with split function. You must replace it by explode.
I got the same problems and it works perfectly now!Jacob (23-04-2011 21:58) :
which line and delete which sign....
even i have the problem that the file is not readable... its important please!!!PHP_uk (09-08-2011 09:47) :
Hi friends,
can any one help me to get out of from "how to select a particular row from excel to php"
Thanks in advance.
yashpal singh (29-09-2011 16:46) :
How can write data we make it vise-versaAlex (07-10-2011 18:56) :
Looks good. Does this library calculate the value of cells with formulas ?Shad (22-10-2011 01:22) :
I need to remove special character and also remove the column header. please help.ehtisham (31-10-2011 22:10) :
It workd fine thanks alot , well it needs to change the file path only which i did and it has done, one another i showed me in a paragraph format i mean without "\n" then i used "<br>" . all the things are ok but i have icons in my excel cell as i did copy data from a website and now want to read that icon how i can ?
thanks to all for reply :)Sem (18-11-2011 17:45) :
Hello,
I am having a hard time resolving a problem regarding utf-8 characterset, Turkish to be specific. I am trying to import a username that has some of these characters, İŞĞÇÜÖ.
if the column has İ in it, it interprets the rest of these unique characters just fine, meaning that it prints them out ok. but if there is no İ but other characters like ŞĞÇÜÖ, it does not interprets them as it should.
Any idea why?priyanka (20-11-2011 00:28) :
thanks a lot to giving this type of Class, i had a lot of problems but now using this i have solved all the issues.Per (11-05-2012 13:00) :
Just for info - A new version of the sourcecode are found here :
http://code.google.com/p/php-excel-reader/
Version 2.21Pulak Das (22-08-2012 13:26) :
jxlrwtest.xls This xls file is corrupted.so,change this file and upload a new xls file.then change the filename into the example.php.then it is fine to run.Manoj Gupta (04-04-2013 14:02) :
given example is not working..kkk (07-06-2013 09:32) :
When I going to upload an excel sheet to mysql database using php I got this error,
Your requested sheet index: 0 is out of bounds. The actual number of sheets is 0. in C:\wamp\www\research_pub-6-07\Pages\excel\PHPExcel.php on line 337
Please Help!!!!!!!!Fahad Mahmood (13-06-2013 09:48) :
It is working for me with the latest version.
ThanksJigar (06-09-2013 16:16) :
I have xls file generate automatically from the system. when I try to import into mysql database that time it is not taking last letter of each cell but If I open same file and save it and then import into mysql that time it import successfully. I compare size and it will reduce compare to without open xls file. I think, problem with BIFF version. Please let me know solution.
This page was last modified on 2024-04-19 10:05:27