CODEWITHSUNDEEP
phpmysqlphpexcel
Anggie Aziz
Anggie Aziz

Reputation: 59

PHPExcel generate data to array from defined sheet

I want to generate an array from my xlsx file first, before I insert it to Mysql, so I am using phpexcel because this one is good enough (I know it from every website's review). But there is a problem when I am trying to making an array from the xlsx defined sheet, here is my script :

<?php

error_reporting(E_ALL);
set_time_limit(0);

date_default_timezone_set('Europe/London');

?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

<title>Automatic Importer</title>

</head>
<body>

<h1>PHPExcel Reader Example #07</h1>
<h2>Simple File Reader Loading a Single Named WorkSheet</h2>
<?php

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


//$inputFileType = 'Excel5';
    $inputFileType = 'Excel2007';
//  $inputFileType = 'Excel2003XML';
//  $inputFileType = 'OOCalc';
//  $inputFileType = 'Gnumeric';
$inputFileName = 'file.xlsx';
$sheetname = 'My Sheet 4';

echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
echo 'Loading Sheet "',$sheetname,'" only<br />';
$objReader->setLoadSheetsOnly($sheetname);
$objPHPExcel = $objReader->load($inputFileName);


echo '<hr />';

echo $objPHPExcel->getSheetCount(),' worksheet',(($objPHPExcel->getSheetCount() == 1) ? '' : 's'),' loaded<br /><br />';
$loadedSheetNames = $objPHPExcel->getSheetNames();
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
var_dump($sheetData);


?>
<body>
</html>

and this is the error says :

Fatal error: Call to a member function cellExists() on a non-object in C:\xampp\htdocs\chi\import\Classes\PHPExcel\Calculation.php on line 3241

Can someone tell where is the error is? My OOP is not good, thanks

Upvotes: 3

Views: 7957

Answers (2)

Mark Baker
Mark Baker

Reputation: 212412

The problem is something in one of the spreadsheet formulae that PHPExcel is having problems evaluating, when you call toArray() with the 3rd argument as TRUE telling the method to evaluate all the formulae in the worksheet. I did add a fix to this last night where a named range containing a reference to a non-existent cell is used in a formula... a bug that would trigger this message; but in this case (with the error at that particular line number) it looks as though it could be a formula that references a non-existent worksheet. The fact that you're only loading a single named worksheet makes this quite possible.

If possible, can you "walk the spreadsheet", using getCalculatedValue() on each cell to find which cell contains the formula that is triggering the error; and then see what that formula is?

Alternatively, load the entire workbook rather than just the one individual worksheet, and see if the error still occurs then.

I'll look at patching the calculation engine code to prevent this error; but it does mean that the formula can't be correctly evaluated if a sheet that it references isn't accessible.

Upvotes: 1

ronalds
ronalds

Reputation: 298

You could start by setting up Xdebug, that will give clearer picture where the error is coming from. From this code, I can assume, that xlsx file doesn't get read due to some error or there's some problem with sheets that are supposed to be read.

Upvotes: 0

Related Questions