Nov 12, 2013 at 8:26 PM
Edited Nov 12, 2013 at 10:06 PM
Hello, first of all let me say PHPExcel is a great library! Thanks to everyone who has worked on it!
PHPExcel version: latest 1.7.9
Apache 2.2.15/PHP 5.3.3
I am having a problem attempting to output to HTML. The data in the cells is correct, but when otuput to HTML, the values are changed! This is not a formatting or number range issue, but it appears rather the HTML writer is writing the wrong values to the cells.
For example, the value of a cell should be 0.28 but instead the rendered cell contains "600", or possibly the value of a cell nearby.
If I write the PHPExcel object to an XLSX file instead of HTML, the values are correct.
I used the following code to test. C9 contains a simple formula: =B9/B12. The correct answer is approximately 0.28 to 0.30. However, when rendering to HTML, the cell contains the value 266, which is actually the value of cell B9 in this case.
On another test, the cell contained the value 900, which was actually the value of cell B12.
$sheet = $excel->setActiveSheetIndex(1);
$formula = $sheet->getCell('C9')->getValue();
$log->write ("Cell formula is : $formula");
$log->write ("Old value was: " . $sheet->getCell('C9')->getOldCalculatedValue());
$tokens = PHPExcel_Calculation::getInstance()->parseFormula($formula, $sheet->getCell('C9'));
$log->write("Debug log: ");
$log->write("New value: " . $sheet->getCell('C9')->getCalculatedValue());
Here is the excerpt from the log file generated by this code:
11/12/2013 15:05:54 /home/public_html/content/bip_ajax.php(126) - Calculating C9...
11/12/2013 15:05:54 /home/public_html/content/bip_ajax.php(127) - Cell formula is : =B9/B12
11/12/2013 15:05:54 /home/public_html/content/bip_ajax.php(128) - Old value was: 0.2819047619047619
11/12/2013 15:05:54 /home/public_html/content/bip_ajax.php(130) - Tokens:
11/12/2013 15:05:54 /home/public_html/content/bip_ajax.php(131) - array (
'type' => 'Cell Reference',
'value' => 'B9',
'reference' => 'B9',
'type' => 'Cell Reference',
'value' => 'B12',
'reference' => 'B12',
'type' => 'Binary Operator',
'value' => '/',
'reference' => NULL,
11/12/2013 15:05:54 /home/public_html/content/bip_ajax.php(132) - Debug log:
11/12/2013 15:05:54 /home/public_html/content/bip_ajax.php(134) - New value: 0.2955555555555556
You can clearly see the calculated new value of C9 is approximately 0.295...
Immediately after this code, I create the HTML file by executing this code:
$filename = "temp_dir/bip_temp/" . microtime();
$excel_writer = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$excel_writer -> save(SERVER_DIR . CONTENT_DIR . $filename . ".html");
However, here is a screenshot of the HTML file:
If I then proceed to write the PHPExcel object to a XLSX file, the correct
values appear in all cells.
I even tried opening this generated XLSX file and writing it to HTML, and the same problem occurs. :P
Unfortunately I can't share the original XLSX file, because it is a confidential business document. However it's just a simple three-sheet worksheet with SUM's and basic math calculations.
Any insight/help/suggestions appreciated!