HOWTO: 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 working

nhojnivron 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? TIA

Kamal (03-10-2008 14:37) :
How to read comments from cell

ajmacaro (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

Roberto

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.html

Jacob (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 77

Sundar (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 it

daniel (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. Thanks

Post a comment (fixed now)

Back to articles list      |     

This page was last modified on 2010-03-10 21:47:03