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

[Bug] Two removed lines in Calculation.php break errors about Cyclic References #3169

Closed
TobiasBg opened this issue Nov 13, 2022 · 0 comments · Fixed by #3170
Closed

[Bug] Two removed lines in Calculation.php break errors about Cyclic References #3169

TobiasBg opened this issue Nov 13, 2022 · 0 comments · Fixed by #3170

Comments

@TobiasBg
Copy link
Contributor

When evaluating a worksheet with formulas with cyclic references, the error output used to contain the list of cells in the cycle, like Worksheet!C2 -> Worksheet!A3 -> Worksheet!C2 -> Cyclic Reference in Formula.

befbc56 broke that, so that the output now is Worksheet!C2 -> Worksheet!C2 -> Worksheet!C2 -> Cyclic Reference in Formula (with everything the same cell).

I have traced this down to two lines in a catch block of /Calculation/Calculation.php that were removed by befbc56.

Once I add these two lines back, everything works as expected again.

What are the steps to reproduce?

This file might help to test this (compare checkouts of 6669588 and befbc56) :

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();

$table_data = array(
	array( 'foo', 'bar', 'baz' ),
	array( '1', '4', '=B3+A3' ),
	array( '=SUM(A2:C2)', '2', '=A2+B2' ),
);

$worksheet = $spreadsheet->setActiveSheetIndex( 0 );
$worksheet->fromArray( $table_data, '' );

// Don't allow cyclic references.
PhpOffice\PhpSpreadsheet\Calculation\Calculation::getInstance( $spreadsheet )->cyclicFormulaCount = 0;

// Loop through all table cells and replace formulas with evaluated values.
$cell_collection = $worksheet->getCellCollection();
foreach ( $table_data as $row_idx => &$row ) {
	foreach ( $row as $column_idx => &$cell_content ) {
		if ( strlen( $cell_content ) > 1 && '=' === $cell_content[0] ) {
			// Adapted from \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet::rangeToArray().
			$cell_reference = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex( $column_idx + 1 ) . ( $row_idx + 1 );
			if ( $cell_collection->has( $cell_reference ) ) {
				$cell = $cell_collection->get( $cell_reference );
				try {
					$cell_content = (string) $cell->getCalculatedValue();
				} catch ( \PhpOffice\PhpSpreadsheet\Calculation\Exception $exception ) {
					$cell_content = $exception->getMessage();
				}
			}
		}
	}
}
unset( $row, $cell_content ); // Unset use-by-reference parameters of foreach loops.

var_dump( $table_data );
oleibman added a commit to oleibman/PhpSpreadsheet that referenced this issue Nov 14, 2022
Fix PHPOffice#3169. Insufficient detail when calculation fails due to cyclic reference.
oleibman added a commit that referenced this issue Nov 22, 2022
Fix #3169. Insufficient detail when calculation fails due to cyclic reference.
MarkBaker added a commit that referenced this issue Dec 21, 2022
### Added

- Extended flag options for the Reader `load()` and Writer `save()` methods
- Apply Row/Column limits (1048576 and XFD) in ReferenceHelper [PR #3213](#3213)
- Allow the creation of In-Memory Drawings from a string of binary image data, or from a stream. [PR #3157](#3157)
- Xlsx Reader support for Pivot Tables [PR #2829](#2829)
- Permit Date/Time Entered on Spreadsheet to be calculated as Float [Issue #1416](#1416) [PR #3121](#3121)

### Changed

- Nothing

### Deprecated

- Direct update of Calculation::suppressFormulaErrors is replaced with setter.
- Font public static variable defaultColumnWidths replaced with constant DEFAULT_COLUMN_WIDTHS.
- ExcelError public static variable errorCodes replaced with constant ERROR_CODES.
- NumberFormat constant FORMAT_DATE_YYYYMMDD2 replaced with existing identical FORMAT_DATE_YYYYMMDD.

### Removed

- Nothing

### Fixed

- Fixed handling for `_xlws` prefixed functions from Office365 [Issue #3245](#3245) [PR #3247](#3247)
- Conditionals formatting rules applied to rows/columns are removed [Issue #3184](#3184) [PR #3213](#3213)
- Treat strings containing currency or accounting values as floats in Calculation Engine operations [Issue #3165](#3165) [PR #3189](#3189)
- Treat strings containing percentage values as floats in Calculation Engine operations [Issue #3155](#3155) [PR #3156](#3156) and [PR #3164](#3164)
- Xlsx Reader Accept Palette of Fewer than 64 Colors [Issue #3093](#3093) [PR #3096](#3096)
- Use Locale-Independent Float Conversion for Xlsx Writer Custom Property [Issue #3095](#3095) [PR #3099](#3099)
- Allow setting AutoFilter range on a single cell or row [Issue #3102](#3102) [PR #3111](#3111)
- Xlsx Reader External Data Validations Flag Missing [Issue #2677](#2677) [PR #3078](#3078)
- Reduces extra memory usage on `__destruct()` calls [PR #3092](#3092)
- Additional properties for Trendlines [Issue #3011](#3011) [PR #3028](#3028)
- Calculation suppressFormulaErrors fix [Issue #1531](#1531) [PR #3092](#3092)
- Permit Date/Time Entered on Spreadsheet to be Calculated as Float [Issue #1416](#1416) [PR #3121](#3121)
- Incorrect Handling of Data Validation Formula Containing Ampersand [Issue #3145](#3145) [PR #3146](#3146)
- Xlsx Namespace Handling of Drawings, RowAndColumnAttributes, MergeCells [Issue #3138](#3138) [PR #3136](#3137)
- Generation3 Copy With Image in Footer [Issue #3126](#3126) [PR #3140](#3140)
- MATCH Function Problems with Int/Float Compare and Wildcards [Issue #3141](#3141) [PR #3142](#3142)
- Fix ODS Read Filter on number-columns-repeated cell [Issue #3148](#3148) [PR #3149](#3149)
- Problems Formatting Very Small and Very Large Numbers [Issue #3128](#3128) [PR #3152](#3152)
- XlsxWrite preserve line styles for y-axis, not just x-axis [PR #3163](#3163)
- Xlsx Namespace Handling of Drawings, RowAndColumnAttributes, MergeCells [Issue #3138](#3138) [PR #3137](#3137)
- More Detail for Cyclic Error Messages [Issue #3169](#3169) [PR #3170](#3170)
- Improved Documentation for Deprecations - many PRs [Issue #3162](#3162)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Development

Successfully merging a pull request may close this issue.

1 participant