All worksheets in spreadsheet appear to be a reference to sheet 0

Question

I am writing a price import script which reads from an Excel spreadsheet.

The spreadsheet is generated using Office 365 Excel however I am using LibreOffice Calc on Ubuntu 18.04 to view it during development - no issues here.

I'm using phpoffice/phpspreadsheet at version 1.10.1:

        "name": "phpoffice/phpspreadsheet",
        "version": "1.10.1",
        "source": {
            "type": "git",
            "url": "https://github.com/PHPOffice/PhpSpreadsheet.git",
            "reference": "1648dc9ebef6ebe0c5a172e16cf66732918416e0"
        },

I am trying to convert the data of each worksheet within the spreadsheet to an array.

There are 3 worksheets, each representing 'Zones' - Zone 1, Zone 2 and Zone 3.

I appear to be getting the same data for Zone 2 and Zone 3 as Zone 1 - the worksheet title is correctly returned however the data is not changing between worksheets.

   /**
     * @param Spreadsheet $spreadsheet
     *
     * @return array
     */
    private function parseZones(Spreadsheet $spreadsheet): array
    {
        $zones = [];

        foreach ([0, 1, 2] as $sheetIndex) {
            $sheet = $spreadsheet->getSheet($sheetIndex);
            // this is correctly reporting 'Zone 1', 'Zone 2' and 'Zone 3' - sheet title is accurate
            $sheetName = $sheet->getTitle();

            // sheet 0 is accurate
            $sheetData = $sheet->toArray();

            // on sheet index 1 and 2 - $sheetData is identical to that of sheet index 0
            // the XLSX file in OpenOffice / Excel has distinctly different row data - 50% less rows in both cases
            // feels like a memory cache issue / some mis-referencing?
        }

        // retrieving rows using this approach yields the same result:
        foreach ($spreadsheet->getAllSheets() as $sheet) {
            // this is correctly reporting 'Zone 1', 'Zone 2' and 'Zone 3' - sheet title is accurate
            $sheetName = $sheet->getTitle();
            // on sheet index 1 and 2 - $sheetData is identical to that of sheet index 0
            $sheetData = $sheet->toArray();
        }

        return $zones;
    }

Any ideas? Thanks

mgldev · Answer 1 · 2020-02-22T18:10:04.997

I'm a numpty - completely failed to see / check the row filtering in the spreadsheet.

It's returning the correct data.

None issue, sorry!

I've since started to investigate how to read a worksheet whilst obeying the filters embedded in the spreadsheet, and it appears Worksheet::toArray() does not automatically take filters in to account - nor does iterating columns and rows manually, see:

https://phpspreadsheet.readthedocs.io/en/latest/topics/autofilters/

You must manually test a row's visibility settings, as per the docs.

Hope this helps!

OO7 · Answer 2 · 2020-02-22T19:45:36.933

Try just change current active sheet before reads.

$spreadsheet->setActiveSheetIndex($sheetIndex);
$sheet = $spreadsheet->getActiveSheet();

$dataArray = $sheet
 ->rangeToArray(
     'A4:O07',    // The worksheet range that we want to retrieve
     NULL,        // Value that should be returned for empty cells
     TRUE,        // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
     TRUE,        // Should values be formatted (the equivalent of getFormattedValue() for each cell)
     TRUE         // Should the array be indexed by cell row and cell column
 );

PhpSpreadsheet

All worksheets in spreadsheet appear to be a reference to sheet 0

2 Answers2