Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

divide by 0 formula causes writer to crash #197

Closed
2 of 3 tasks
Kalloritis opened this issue Aug 7, 2017 · 3 comments
Closed
2 of 3 tasks

divide by 0 formula causes writer to crash #197

Kalloritis opened this issue Aug 7, 2017 · 3 comments

Comments

@Kalloritis
Copy link

Kalloritis commented Aug 7, 2017

This is:

What is the expected behavior?

writer successfully writing file by IFERROR handling the divide by 0 and inserting 0 into cell for the following formula:
"=IFERROR(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2)/ OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),0)"

PHP Fatal error: Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: TSM!K14 -> Formula Error: An unexpected error occured in includes/classes/PhpSpreadsheet/src/PhpSpreadsheet/Cell.php:288
Stack trace:
#0 includes/classes/PhpSpreadsheet/src/PhpSpreadsheet/Worksheet.php(761): PhpOffice\PhpSpreadsheet\Cell->getCalculatedValue()
#1 includes/classes/PhpSpreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(383): PhpOffice\PhpSpreadsheet\Worksheet->calculateColumnWidths()
#2 includes/classes/PhpSpreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(81): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCols(Object(PhpOffice\PhpSpreadsheet\Shared\XMLWriter), Object(PhpOffice\PhpSpreadsheet\Worksheet))
#3 includes/classes/PhpSpreadsheet/src/PhpSpr in includes/classes/PhpSpreadsheet/src/PhpSpreadsheet/Cell.php on line 288

What is the current behavior?

With the production code, crash upon first encounter by writer, with/without $writer->setPreCalculateFormulas(false);

With the test case code produces an XLSX file with errors .

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

<?php
/**
 * Created by PhpStorm.
 * User: Joe
 * Date: 2017-08-07
 * Time: 16:21
 */

require 'includes/classes/PhpSpreadsheet/vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$summary = $spreadsheet->getActiveSheet();
$c = 0;
$r = 1;
$summary->setCellValueByColumnAndRow($c++, $r, 0)
    ->setCellValueByColumnAndRow($c++, $r, 0)
    ->setCellValueByColumnAndRow($c++, $r,
        '=IFERROR(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-2)/ OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1),0)');
$c = 0;
$summary->getCellByColumnAndRow($c++,
    $r)->getStyle()->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD);
$summary->getCellByColumnAndRow($c++,
    $r)->getStyle()->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD);
$summary->getCellByColumnAndRow($c++,
    $r)->getStyle()->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE);

$writer = new Xlsx($spreadsheet);
$writer->setPreCalculateFormulas(false);
$writer->save('php://output');

Which versions of PhpSpreadsheet and PHP are affected?

$ php --version
PHP 7.0.21 (cli) (built: Jul 6 2017 17:50:00) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
with the ionCube PHP Loader (enabled) + Intrusion Protection from ioncube24.com (unconfigured) v6.0.7, Copyright (c) 2002-2016, by ionCube Ltd.
with Zend OPcache v7.0.21, Copyright (c) 1999-2017, by Zend Technologies

@Kalloritis
Copy link
Author

This issue will also happen if you do a divide by 0, period, with static numbers in the formula, even when surrounded by an 'IFERROR' and pre-compute formula's is turned off in the writer.

@stale
Copy link

stale bot commented Dec 4, 2017

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

@stale stale bot added the stale label Dec 4, 2017
@stale stale bot closed this as completed Dec 11, 2017
@oleibman
Copy link
Collaborator

Supplied code works without warning or exception (when ...byColumnAndRow is modernized, and columns are changed to origin-1).

@oleibman oleibman removed the stale label Jul 11, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

No branches or pull requests

2 participants