Uncaught exception 'PHPExcel_Calculation_Exception' Countifs()

Topics: Developer Forum
Apr 9, 2014 at 8:03 AM
Edited Apr 9, 2014 at 8:04 AM
Hello,
I have a problem with PHPExcel and can’t seem to find the solution to fix it.
I wrote a script based on the basic examples provided in the PHPExcel documentation to create a PDF file out of a XLSX file my code looks like this:
$inputfile = “test.xlsx”;
$objReader = PHPExcel_IOFactory::createReaderForFile($inputfile);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputfile);

[…]

$objWriter = new PHPExcel_Writer_PDF($objPHPExcel);
$objWriter->setSheetIndex(0);
$objWriter->setPreCalculateFormulas(true);
$objWriter->save("test.pdf");
Now the problem is that this will return me an Error
Fatal error: Uncaught exception 'PHPExcel_Calculation_Exception' with message 'Sheet!C5 -> Formula Error: Wrong number of arguments for COUNTIFS() function: 4 given, 2 expected' in D:\xampp\htdocs\doc\phpxls\Classes\PHPExcel\Cell.php:300 Stack trace: #0 D:\xampp\htdocs\doc\phpxls\Classes\PHPExcel\Writer\HTML.php(1174): PHPExcel_Cell->getCalculatedValue() #1 D:\xampp\htdocs\doc\phpxls\Classes\PHPExcel\Writer\HTML.php(436): PHPExcel_Writer_HTML->_generateRow(Object(PHPExcel_Worksheet), Array, 4) #2 D:\xampp\htdocs\doc\phpxls\Classes\PHPExcel\Writer\PDF\DomPDF.php(109): PHPExcel_Writer_HTML->generateSheetData() #3 [internal function]: PHPExcel_Writer_PDF_DomPDF->save('testing.pdf') #4 D:\xampp\htdocs\doc\phpxls\Classes\PHPExcel\Writer\PDF.php(87): call_user_func_array(Array, Array) #5 D:\xampp\htdocs\doc\interface\xls2pdf_2.php(59): PHPExcel_Writer_PDF->__call('save', Array) #6 D:\xampp\htdocs\doc\interface\xls2pdf_2.php(59): PHPExcel_Writer_PDF->save('testing.pdf') #7 {main} thrown in D:\xampp\htdocs\doc\phpxls\Classes\PHPExcel\Cell.php on line 300
You can see it’s something about “COUNTIFS()” my assumption was that setReadDataOnly(true) would only give me the values of the cells and ignore the formulas after reading the documentation I realize this is not true. Is there a way to only get the values of the Cell without formula?

If I set
$objWriter->setPreCalculateFormulas(true); 
To
$objWriter->setPreCalculateFormulas(false);
It will work without error but the returned PDF file will show the formulas instead of the calculated values in the cells (obviously)

Is there any way to make this work like I intended it to? Just reading / writing the values and ignoring the formula?

My suspicion is that the error has something to do with the separator in the formula which in the local format I am using (German) is semicolon but in the US/Standard format is comma so the formula looks like this in the original file
=ZÄHLENWENNS(Migration_LSA!$AR$2:$AR$121;">=1/4/2014";Migration_LSA!$AR$2:$AR$121;"<=30/4/2014")
And PHPExcel is looking for this
=COUNTIFS(Migration_LSA!$AR$2:$AR$121,">=1/4/2014",Migration_LSA!$AR$2:$AR$121,"<=30/4/2014")
I accidentally submitted this post under the Issues section first sorry about that
https://phpexcel.codeplex.com/workitem/20897/edit
Apr 9, 2014 at 8:20 AM
Coordinator
Apr 9, 2014 at 8:26 AM
The problem isn't to do with the separator at all, this is only an issue if you were inserting the formula into PHPExcel directly as
    =ZÄHLENWENNS(Migration_LSA!$AR$2:$AR$121;">=1/4/2014";Migration_LSA!$AR$2:$AR$121;"<=30/4/2014")
when you would need to set the locale, and use the translate methods before inserting it. Internally (in both PHPExcel and MS Excel) formulae are represented in their English/US format. The MS Excel GUI hides this from you with automatic translation on insert/display.


At the moment, the COUNTIFS() formula in PHPExcel only works with a single range/criteria pair.

You can work round this by splitting your multiple range/criteria pairs into single range/criteria pairs and adding those together, so:
    =COUNTIFS(Migration_LSA!$AR$2:$AR$121,">=1/4/2014") +
        COUNTIFS(Migration_LSA!$AR$2:$AR$121,"<=30/4/2014")
setReadDataOnly(true) tells the PHPExcel Reader only to read the cell content, not to read styles, format masks, print settings, conditional styles, etc... but formulae are still cell content
Marked as answer by iD0QS on 4/9/2014 at 1:12 AM