Reading Excel files

Nov 12, 2009 at 2:37 PM

Hi, I want to ask you if can I read Excel files using PHPExcel class. Specially, I'm interested about reading the Currency format cells. Waiting for your answer, thanks.

Nov 12, 2009 at 2:53 PM

I've just found something in documentation, PHPExcel_Reader_IReadFilter, etc.. But I don't understand complettly how to use it. Can someone give me a complete php file about reading an excel file?

Coordinator
Nov 12, 2009 at 4:13 PM

Take a look at the 20readexcel5.php script in the /Tests directory of the distribution

error_reporting(E_ALL);

require_once './Classes/PHPExcel/IOFactory.php';

$filename = 'myExcelFile.xls'
if (!file_exists($filename)) {
	die("File $filename does not exist.<br />");
}
$objPHPExcel = PHPExcel_IOFactory::load($filename);



You can then access cell data using the normal PHPExcel methods

$cellValue = $objPHPExcel->getActiveSheet()->getCell('B14')->getValue();

or

$cellValue = $objPHPExcel->getActiveSheet()->getCell('B14')->getCalculatedValue();

You can identify if a cell is formatted for currency by reading the cell formatting

$cellStyle = $objPHPExcel->getActiveSheet()->getStyle('B14')->getNumberFormat();

and then scanning the $cellStyle value to see if it contains the relevant currency symbol (e.g. $ or [$EUR ])

Nov 13, 2009 at 7:51 AM

Thank you..

I've done what you wrote, but I receive this:

10:48:25 Load from Excel5 file
Fatal error: Class 'ZipArchive' not found in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel2007.php on line 200

Do you know what problem is this?

Developer
Nov 13, 2009 at 7:56 AM

Are you trying to read xls or xlsx file?

Nov 13, 2009 at 8:11 AM

<?
/** Error reporting */
error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../classes/phpexcel/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$filename = 'asbis - work.xls';
if (!file_exists($filename)) {
	die("File $filename does not exist.<br />");
}


echo date('H:i:s') . " Load from Excel5 file\n";
$objPHPExcel = PHPExcel_IOFactory::load($filename);

?>

Nov 13, 2009 at 8:20 AM

I just enabled php_zip PHP extension and that error disappears. But I have now others errors:

Notice: Undefined offset: 27 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4237
Notice: Undefined offset: 27 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4238

 

 

Developer
Nov 13, 2009 at 8:26 AM

Can you upload the Excel file that is giving you those errors? This must be a bug.

Please upload the file here so we can check:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10749

 

Nov 13, 2009 at 8:29 AM

http://phpexcel.codeplex.com/Project/Download/AttachmentDownload.ashx?ProjectName=PHPExcel&WorkItemId=10749&FileAttachmentId=3535

 

Nov 13, 2009 at 8:30 AM

I'm waiting for some news, thank you for your help..

 

Nov 13, 2009 at 9:30 AM

<?
/** Error reporting */
ini_set('memory_limit', -1);
set_time_limit(0);

error_reporting(E_ALL);

/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../../classes/phpexcel/');

/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';

$filename = 'Deluo - 11 nov.xls';
if (!file_exists($filename)) {
	die("File $filename does not exist.<br />");
}


echo date('H:i:s') . " Load from Excel5 file<br>";
$objPHPExcel = PHPExcel_IOFactory::load($filename);

echo $objPHPExcel->getActiveSheet()->getCell('E17')->getValue()."<br>";
echo $objPHPExcel->getActiveSheet()->getCell('E17')->getCalculatedValue()."<br>";
echo $objPHPExcel->getActiveSheet()->getStyle('E17')->getNumberFormat();


?>
This is working:

http://phpexcel.codeplex.com/Project/Download/AttachmentDownload.ashx?ProjectName=PHPExcel&WorkItemId=10749&FileAttachmentId=3536

But I still receive some notices:

12:22:52 Load from Excel5 file

Notice: Uninitialized string offset: 0 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4274

Notice: Uninitialized string offset: 1 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4621

Notice: Uninitialized string offset: 2 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4621
21.5
21.5

Catchable fatal error: Object of class PHPExcel_Style_NumberFormat could not be converted to string in D:\xServer\www\neoland.ro\xpanel\xscripts\excelreader3\x.php on line 25

 

Developer
Nov 13, 2009 at 10:45 AM

Notice: Uninitialized string offset: 0 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4274

Notice: Uninitialized string offset: 1 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4621

Notice: Uninitialized string offset: 2 in D:\xServer\www\neoland.ro\xpanel\classes\phpexcel\PHPExcel\Reader\Excel5.php on line 4621

 

I can verify these notices. Will investigate today and create necessary work item to resolve this.

Developer
Nov 13, 2009 at 1:58 PM

Bug was identified:

http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=10936

Use patch or just wait for tomorrow source code:

http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx