If Formula Not working

Oct 15, 2009 at 7:57 AM

Hi,

iam using excel 2007 to write new excel

For The Formula it give error:

Fatal error: Uncaught exception 'Exception' with message 'Internal error - Operand value missing from stack' in /home/cms/excel/PHPExcel/Calculation.php:2860 Stack trace: #0 /home/cms/excel/PHPExcel/Calculation.php(2482): PHPExcel_Calculation->_raiseFormulaError('Internal error ...') #1 /home/cms/excel/PHPExcel/Calculation.php(1883): PHPExcel_Calculation->_processTokenStack(Array, 'I10', Object(PHPExcel_Cell)) #2 /home/cms/excel/PHPExcel/Calculation.php(1780): PHPExcel_Calculation->_calculateFormulaValue('=IF(CArray>0.01...', 'I10', Object(PHPExcel_Cell)) #3 /home/cms/excel/PHPExcel/Cell.php(281): PHPExcel_Calculation->calculateCellValue(Object(PHPExcel_Cell), true) #4 /home/cms/excel/PHPExcel/Writer/Excel2007/Worksheet.php(999): PHPExcel_Cell->getCalculatedValue() #5 /home/cms/excel/PHPExcel/Writer/Excel2007/Worksheet.php(943): PHPExcel_Writer_Excel2007_Worksheet->_writ in /home/cms/excel/PHPExcel/Calculation.php on line 2860


$php_excel->setActiveSheetIndex(0);
$sheet = $php_excel->getActiveSheet();

$sheet->getCellByColumnAndRow($col,$cell_row)->setValue('');
$sheet->setCellValue('I'.$cell_row, '=IF(H'.$cell_row.'="C",E'.$cell_row.'+F'.$cell_row.'/24+G'.$cell_row.'/24*60,0)');
$sheet->getStyleByColumnAndRow($col,$cell_row)->applyFromArray($main_style);

$writer =PHPExcel_IOFactory::createWriter($php_excel,'Excel2007');
$writer->save('myExcel.xlsx');

 

How Can i Solve This issue.

Thx.

Developer
Oct 15, 2009 at 11:13 AM

Hmm... I cannot reproduce the error.

I tried to run this, but no with error message:

$php_excel = new PHPExcel();
$sheet = $php_excel->getActiveSheet();

$col = 'A'; $cell_row = 1;
$main_style = array('font'=>array('size'=>20));

$sheet->getCellByColumnAndRow($col,$cell_row)->setValue('');
$sheet->setCellValue('I'.$cell_row, '=IF(H'.$cell_row.'="C",E'.$cell_row.'+F'.$cell_row.'/24+G'.$cell_row.'/24*60,0)');
$sheet->getStyleByColumnAndRow($col,$cell_row)->applyFromArray($main_style);

$writer =PHPExcel_IOFactory::createWriter($php_excel,'Excel2007');
$writer->save('myExcel.xlsx');

Can you post a script we can run to reproduce the error?

Oct 15, 2009 at 2:42 PM

i try the same code u put it & gave same error:

File (export_excel.php):

-------------------------

include_once ("../excel/PHPExcel.php");
include_once ("../excel/PHPExcel/IOFactory.php");

$php_excel = new PHPExcel();
$sheet = $php_excel->getActiveSheet();

$col = 'A'; $cell_row = 1;
$main_style = array('font'=>array('size'=>20));

$sheet->getCellByColumnAndRow($col,$cell_row)->setValue('');
$sheet->setCellValue('I'.$cell_row, '=IF(H'.$cell_row.'="C",E'.$cell_row.'+F'.$cell_row.'/24+G'.$cell_row.'/24*60,0)');
$sheet->getStyleByColumnAndRow($col,$cell_row)->applyFromArray($main_style);

$writer =PHPExcel_IOFactory::createWriter($php_excel,'Excel2007');
$writer->save('myExcel.xlsx');

 

so give me this error:

------------------------

Fatal error: Uncaught exception 'Exception' with message 'Could not close zip file myExcel.xlsx.' in /home/ap/excel/PHPExcel/Writer/Excel2007.php:400 Stack trace: #0 /home/ap/report/export_excel.php(16): PHPExcel_Writer_Excel2007->save('myExcel.xlsx') #1 {main} thrown in /home/ap/excel/PHPExcel/Writer/Excel2007.php on line 400

Developer
Oct 15, 2009 at 2:48 PM

That is another error, try to delete the file 'myExcel.xlsx' and run the script again.

Oct 15, 2009 at 7:26 PM

ok

That's right the script work with no error alone but when i put it in the full file i have it not work it will give the first error except if i change to excel5:

i upload the full file you can download from here

http://www.4shared.com/file/141137096/c8293ead/my_excel.html

Thanks Again for your replay.

 

Developer
Oct 15, 2009 at 11:13 PM

Perhaps a missing parenthesis?

Can you try to change from this:

$sheet->setCellValueByColumnAndRow($col,$cell_row,'=IF(I'.($cell_row-1).'="LAYTIME CONSUMED","LAYTIME CONSUMED",IF((I'.$port_rmain_laytime_cell_no[count($port_rmain_laytime_cell_no)-1].'-I'.($cell_row-1).')<0,"LAYTIME CONSUMED",I'.($cell_row-1).'-I'.($cell_row-1).')');

to this:

$sheet->setCellValueByColumnAndRow($col,$cell_row,'=IF(I'.($cell_row-1).'="LAYTIME CONSUMED","LAYTIME CONSUMED",IF((I'.$port_rmain_laytime_cell_no[count($port_rmain_laytime_cell_no)-1].'-I'.($cell_row-1).')<0,"LAYTIME CONSUMED",I'.($cell_row-1).'-I'.($cell_row-1).'))');

Oct 16, 2009 at 5:38 AM
Edited Oct 16, 2009 at 5:45 AM

yes that's true parenthesis is missing but no problem it works in Excel5 even if i add or remove this parenthesis but in Excel2007 i try to more than adding parenthesis, i try to stop the line all & same error. i want to use Excel2007 since iam using Data Validation that not work on Excel5.

you can try it using the file i uploaded to u later, thanks.

Coordinator
Oct 16, 2009 at 7:54 AM

The original error is definitely a calculation engine error, but I haven't been able to replicate it so far. It may be that one of the cells referenced in the formula contains something that's triggering the failure, because there's nothing wrong with the formula itself.

Is it possible to identify exactly what the data values are for the cells referenced in the formula (ie the values in columns C, E, F, G and H) for the cell that triggers the error

Oct 16, 2009 at 11:25 AM
Edited Oct 16, 2009 at 2:38 PM
<?php
include_once ("../../framework/controls/excel/PHPExcel.php");
include_once ("../../framework/controls/excel/PHPExcel/IOFactory.php");



$file_name = "myExcel";
$merge_version = 'Excel2007'; //or Excel5 $extension='.xlsx'; // or .xls //Not if u remove the comment 2 lines below & make it Excel5 will work //$merge_version = 'Excel5'; //or Excel5 //$extension='.xls'; // or .xls $file_name=$file_name.$extension; $file_save=$file_name; $php_excel = new PHPExcel();

$main_style = array('font'=>array('size'=>20));
//Excel Page Setup $php_excel->setActiveSheetIndex(0); $sheet = $php_excel->getActiveSheet(); //Begin Set Data $cell_row=1; $is_first = true; $port_rmain_laytime_cell_no = array();
$row = array("Alex","Mdc","Libc");
foreach($row as $port_name)
{

$col=0;
$cell_row++;

$sheet->getCellByColumnAndRow($col,$cell_row)->setValue('');
$sheet->setCellValueByColumnAndRow($col, $cell_row, '=IF(H'.$cell_row.'="C",E'.$cell_row.'+F'.$cell_row.'/24+G'.$cell_row.'/24*60,0)'); $sheet->getStyleByColumnAndRow($col,$cell_row)->applyFromArray($main_style); $sheet->getStyle('I'.$cell_row.':I'.$cell_row)->getNumberFormat()->setFormatCode('dd" day(s) "hh" hrs "mm" min"');
$col++;


$col=0;
$cell_row++;
$sheet->mergeCellsByColumnAndRow($col,$cell_row,$col+3,$cell_row);
$sheet->getCellByColumnAndRow($col,$cell_row)->setValue("Remaining Laytime");
$sheet->getStyle('A'.$cell_row.':D'.$cell_row)->applyFromArray($main_style);
$col++;
if($is_first)
$sheet->setCellValueByColumnAndRow($col,$cell_row,'=IF(I'.($cell_row-1).'="LAYTIME CONSUMED","LAYTIME CONSUMED",I'.($cell_row-1).'-I'.($cell_row-1).')');
else $sheet->setCellValueByColumnAndRow($col,$cell_row,'=IF(I'.($cell_row-1).'="LAYTIME CONSUMED","LAYTIME CONSUMED",IF((I'.$port_rmain_laytime_cell_no[count($port_rmain_laytime_cell_no)-1].'-I'.($cell_row-1).')<0,"LAYTIME CONSUMED",I'.($cell_row-1).'-I'.($cell_row-1).'))');
$port_rmain_laytime_cell_no[] = "$cell_row";
$sheet->getStyle('I'.$cell_row.':I'.$cell_row)->getNumberFormat()->setFormatCode('dd" day(s) "hh" hrs "mm" min"');
$sheet->getStyleByColumnAndRow($col,$cell_row)->applyFromArray($main_style);

$cell_row+=2;
$is_first = false;
}

//if There is Merge It will not give good result /*for($i=0;$i<=10;$i++)
$sheet->getColumnDimensionByColumn($i)->setAutoSize(true);*/
//excel write $writer =PHPExcel_IOFactory::createWriter($php_excel,$merge_version); $writer->save($file_save); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$file_name.'"'); //if we replace "attachment" by "inline" it will open inside the iframe header('Cache-Control: max-age=0');

$fh=fopen($file_save, "rb");
fpassthru($fh);
unlink($file_save);

?>

 

 

This is the Full Short Script if i delete this line it will work:

$sheet->setCellValueByColumnAndRow($col, $cell_row, '=IF(H'.$cell_row.'="C",E'.$cell_row.'+F'.$cell_row.'/24+G'.$cell_row.'/24*60,0)');

What is the error of the above line?

Note: why work correctly in Excel5 can you justify to me plz?

Thank All...

Developer
Oct 16, 2009 at 2:01 PM

With your last script where the missing parenthesis has been inserted I don't get any errors. What error are you getting this time?

Oct 16, 2009 at 2:37 PM

it give the same initial error:


Fatal error: Uncaught exception 'Exception' with message 'Internal error - Operand value missing from stack' in D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Calculation.php:2860 Stack trace: #0 D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Calculation.php(2482): PHPExcel_Calculation->_raiseFormulaError('Internal error ...') #1 D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Calculation.php(1883): PHPExcel_Calculation->_processTokenStack(Array, 'B3', Object(PHPExcel_Cell)) #2 D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Calculation.php(1780): PHPExcel_Calculation->_calculateFormulaValue('=IF(I2="LAYTIME...', 'B3', Object(PHPExcel_Cell)) #3 D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Cell.php(281): PHPExcel_Calculation->calculateCellValue(Object(PHPExcel_Cell), true) #4 D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Writer\Excel2007\Worksheet.php(985): PHPExcel_Cell->getCalculatedValue() #5 D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Writer\Excel2007\Worksheet.php(929): PH in D:\wamp\www\ems\ap\framework\controls\excel\PHPExcel\Calculation.php on line 2860

you can try my last script i write it & see the error...

Developer
Oct 16, 2009 at 3:02 PM

I am now able to reconstruct the error. I will try to reduce the script and post it in a suitable form in the issue tracker.

>> Note: why work correctly in Excel5 can you justify to me plz?

The reason is that Excel5 writer does not store the calculated values in the generated Excel file.

You can disable pre-calculation of formulas in Excel2007 writer like this.

$writer =PHPExcel_IOFactory::createWriter($php_excel,$merge_version);
$writer->setPrecalculateFormulas(false);
$writer->save($file_save);

Normally, this should be ok, and the error message should disappear. You can use that as a workaround.

Developer
Oct 16, 2009 at 3:47 PM

No need to post anything in issue tracker. Looks like this is fixed in latest source code.
http://phpexcel.codeplex.com/SourceControl/ListDownloadableCommits.aspx

Reduced test script:

<?php
$version = '170';
require_once 'PHPExcel' . $version . '/Classes/PHPExcel.php';
require_once 'PHPExcel' . $version . '/Classes/PHPExcel/IOFactory.php';

$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();

$sheet->getCell('I2');
$sheet->getCell('B3')->setValue('=IF(I2="x",0,1)');

var_dump($sheet->getCell('B3')->getCalculatedValue());
?>


With PHPExcel 1.7.0:

Fatal error: Uncaught exception 'Exception' with message 'Internal error - Operand value missing from stack' in C:\xampp\htdocs\_phpexcel\PHPExcel170\Classes\PHPExcel\Calculation.php:2860 Stack trace:
#0 C:\xampp\htdocs\_phpexcel\PHPExcel170\Classes\PHPExcel\Calculation.php(2482): PHPExcel_Calculation->_raiseFormulaError('Internal error ...')
#1 C:\xampp\htdocs\_phpexcel\PHPExcel170\Classes\PHPExcel\Calculation.php(1883): PHPExcel_Calculation->_processTokenStack(Array, 'B3', Object(PHPExcel_Cell))
#2 C:\xampp\htdocs\_phpexcel\PHPExcel170\Classes\PHPExcel\Calculation.php(1780): PHPExcel_Calculation->_calculateFormulaValue('=IF(I2="x",0,1)', 'B3', Object(PHPExcel_Cell))
#3 C:\xampp\htdocs\_phpexcel\PHPExcel170\Classes\PHPExcel\Cell.php(281): PHPExcel_Calculation->calculateCellValue(Object(PHPExcel_Cell), true)
#4 C:\xampp\htdocs\_phpexcel\my_excel.php(12): PHPExcel_Cell->getCalculatedValue()
#5 {main} thrown in C:\xampp\htdocs\_phpexcel\PHPExcel170\Classes\PHPExcel\Calculation.php on line 2860


With latest source code:

int(1)

Oct 17, 2009 at 5:40 AM

OK,

it's work

Thanks Koyama, MarkBaker Thanks All.