Skip to content

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

Closed
@TobiasBg

Description

@TobiasBg

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 );

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions