Not calculating formulas when read filter applied

[noumanhabib]

This is:

- [x] a bug report

What is the expected behavior?

This code is not calculating formulas with read filters. If I change $chunk_read_filter->setRows(1500, 3000); to $chunk_read_filter->setRows(1, 3000); it works. It need to calculate formulas with the range. My all formulas are in range and within same row.

What is the current behavior?

Not calculating formulas correctly with read filters applied.

What are the steps to reproduce?

Below is usage code

<?php
...
public function test()
{
    //This file is attached below
    $file_path  = storage_path('app/import/test-files/Test Template 2- TotalRecord 10000.xlsx');
    //This class code is below
    $chunk_read_filter = new ChunkReadFilter();
    $chunk_read_filter->setRows(1500, 3000);
    $reader = IOFactory::createReaderForFile($file_path);
    $reader->setReadFilter($chunk_read_filter);
    $reader->setReadDataOnly(true);
    $reader->setReadEmptyCells(false);
    $spreadsheet = $reader->load($file_path)->getActiveSheet();

    $header_row = $spreadsheet->rangeToArray("A1500:" . $spreadsheet->getHighestColumn() . "3000");
    //Print or echo or dd or var_dump or return view etc...
    return view('test', ['content' => json_encode($header_row)]);
}
...

ChunkReadFilter.PHP

<?php

namespace App\Helpers;

use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;

class ChunkReadFilter implements IReadFilter
{
    private $startRow = 0;
    private $endRow   = 0;

    /**  Set the list of rows that we want to read  */
    public function setRows($startRow, $chunkSize)
    {
        $this->startRow = $startRow;
        $this->endRow   = $startRow + $chunkSize;
    }

    public function readCell($columnAddress, $row, $worksheetName = '')
    {
        if (($row == 1) || ($row >= $this->startRow && $row < $this->endRow)) {
            return true;
        }
        return false;
    }
}

What features do you think are causing the issue

• Reader
• Writer
• Styles
• Data Validations
• Formula Calculations
• Charts
• AutoFilter
• Form Elements

Does an issue affect all spreadsheet file formats? If not, which formats are affected?

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet: "1.27"
PHP: "8.2"

I am also attaching excel file
Test Template 2- TotalRecord 10000.xlsx

[MarkBaker]

For some reason in this file, the formula attribute f isn't set for the cells outside the filter range that contain the shared formula definition, so it isn't being read.

[noumanhabib]

For some reason in this file, the formula attribute f isn't set for the cells outside the filter range that contain the shared formula definition, so it isn't being read.

But in this file formulas are referencing to same row so they are in range that I am selecting, like F2=H2&E2 , so please let me know if problem in excel file or problem with the library.

[MarkBaker]

It's a shared formula: the formula itself is only actually stored for one cell (e.g. R2). Cells R3, R4, R5, R1500 etc don't store the formula itself, they simply store a pointer saying that they should use the formula that was read from R2 after adjusting it for the current row.

Normally cell R2 would have the f attribute set to indicate that it contained a formula. Even though it's outside the filter range, the Reader still sees that f tag and looks to see if the formula itself is defined as shared. If so, it stores that formula in the Shared Formula Table, even though it doesn't load the Cell.
Then, when it reads a cell R1500 that is inside the filter range, and cell R1500 says to use the formula from R2, it can look for that formula in the Shared Formula Table.

In this file, the f attribute wasn't set for Cell R2, so the Reader was simply ignoring it, and the formula wasn't being added to the Shared Formula Table. When cell R1500 says to use the shared formula for R2, that formula isn't in the Shared Formula Table, so it returns an empty formula.

The code change that I've made and merged into the master branch ready for the next release means that the Reader doesn't only look to see if the f attribute is set when the cell is outside the filter range, but also looks further to see if the cell actually contains a formula that is defined as shared, so that it will now get added to the Shared Formula Table.

[noumanhabib]

Yes, I get the latest code from github and now it is working. Thanks