Read Excel File

Topics: Developer Forum, Project Management Forum, User Forum
Feb 12, 2015 at 10:41 AM

I have an excel file. It contains approx. 15 sheets. I want to show only one sheet (FCR). but that sheet is depend on others by some formulas. I am unable to convert those formula values into html table value.

I am getting error:
"FCR!B1 -> Cash Rec 1!E5 -> Invalid cell coordinate C "

My Excel file is here :
Feb 12, 2015 at 1:54 PM
Edited Feb 12, 2015 at 1:55 PM
Here is my Code:-

require_once 'Classes/PHPExcel.php';
$objPHPExcel = PHPExcel_IOFactory::load("Pilgrim_Excel.xlsx");
PHPExcel_Calculation::getInstance()->cyclicFormulaCount = 100;
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'HTML');
Feb 12, 2015 at 4:00 PM
Take a look at the value in cell Cash Rec 1!E5. It looks as though it contains a column reference as C, which isn't something supported by PHPExcel's calculation engine, though I'd need to know the exact formula to confirm.

Note that if you only want to display a single sheet in HTML, then you shouldn't be using
but specifying the sheet that you do want to display
Feb 13, 2015 at 5:36 AM
Thanks for the quick reply.

As per the mentioned Error and as far as i can see in my excel sheet, There is not so much hard calculation.

In FCR Cell B1 is based on ='Cash Rec 1'!C5
and in Cash Rec 1 cell C5 is =LOOKUP(E5,CTRL!C:C,CTRL!A:A) that it takes E5 value (210) and search in CTRL sheet and get correspondence CTRL A value.

if still PHPEXCEL not supported this then is there any alternative solution for it ?

you can see and check the the Excel Sheet. I have mentioned the link.

Please Help me out.

Feb 13, 2015 at 8:18 AM
Either modify the formula to specify an actual range of cells (if you know the maximum number of rows):

Or possibly look at some of the alternatives such as COM with MS Excel installed on the server, PUNO with Open/LibreOffice installed on the server, or Ilia's extension that requires the commercial libXL
Feb 13, 2015 at 12:06 PM
Thank you sir, you are genius .

that's going good but now i am getting another error.

PHPExcel_Calculation_Exception' with message 'FCR!C5 -> Cash Rec 1!E5 -> Cash Rec 1!E5 -> Daily Close!O8 -> Cash Rec 1!E5 -> Invalid cell coordinate C
I believe it is different from the previous one. at Cash Rec 1 cell no. E5 there is no formula . but still i am getting error.

thanks in advance.