PhpSpreadsheet: Column limit exceeded when reading a file and then saving it.

This is:

- [X] a bug report

What is the expected behavior?

Excel file should open in LibreOffice and Excel without warnings.

What is the current behavior?

I get a warning about column size exceeding the sheet limit in LibreOffice. Excel gives another warning.

What are the steps to reproduce?

Make sure your machine has LibreOffice installed (the test script uses soffice commandline).
Run the script below.

<?php


use PhpOffice\PhpSpreadsheet\IOFactory;

require 'src/vendor/autoload.php';

$testFile = 'src/vendor/phpoffice/phpspreadsheet/samples/Reader/sampleData/example1.xls';

// CONVERSION WITH PHPSPREADSHEET
$writer = IOFactory::createWriter(IOFactory::load($testFile), 'Xlsx');
$testFile1 = tempnam(sys_get_temp_dir(), 'test') . '.xlsx';
$writer->save($testFile1);

// CONVERSION WITH LIBREOFFICE
passthru('soffice --headless --convert-to xlsx --outdir ' . sys_get_temp_dir() . ' ' . $testFile);
$testFile2 = sys_get_temp_dir() . '/' . basename($testFile) . 'x';

foreach([$testFile1, $testFile2] as $file) {
    $writer = IOFactory::createWriter(IOFactory::load($file), 'Xlsx');
    $outFile = strtr($file, ['.xlsx' => '-output.xlsx']);
    $writer->save($outFile);
    echo "Created $outFile from $file\n";
}

Confirm that:

The scripts executes without error.
One of the created XLSX files throws error(s) when opening in Calc or MS Excel.

Which versions of PhpSpreadsheet and PHP are affected?

1.3.1 on PHP 7.2

About this issue

Original URL
State: closed
Created 6 years ago
Comments: 23 (1 by maintainers)

Most upvoted comments

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[bot] on Sep 4, 2018

@siarheipashkevich Yes. I wrote two functions to remove bad indexes:

function removeTooHighColumnDimensions($worksheet)
{
    $maxColumnIndex = Coordinate::columnIndexFromString($worksheet->getHighestDataColumn());

    $columnDimensions = $worksheet->getColumnDimensions();
    foreach ($columnDimensions as $col => $dimension) {
        if (Coordinate::columnIndexFromString($col) > $maxColumnIndex) {
            unset($columnDimensions[$col]);
        }
    }

    $reflector = new ReflectionClass($worksheet);
    $property = $reflector->getProperty('columnDimensions');
    $property->setAccessible(true);
    $property->setValue($worksheet, $columnDimensions);
}

function removeTooHighRowDimensions($worksheet)
{
    $maxRowIndex = $worksheet->getHighestDataRow();

    $rowDimensions = $worksheet->getRowDimensions();
    foreach ($rowDimensions as $row => $dimension) {
        if ($row > $maxRowIndex) {
            unset($rowDimensions[$row]);
        }
    }

    $reflector = new ReflectionClass($worksheet);
    $property = $reflector->getProperty('rowDimensions');
    $property->setAccessible(true);
    $property->setValue($worksheet, $rowDimensions);
}

yar3333 on Oct 9, 2019

LibreOffice Calc only supports 1024 columns, but it saves the last column of each sheet with a max of 1025, which PhpSpreadsheet reads as an individual column.

PhpSpreadsheet should probably subtract 1 from the last column (or better yet, treat all the extra columns as a range), but a simple workaround is to load the spreadsheet with a Read Filter:

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\IReadFilter;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class LOReadFilter implements IReadFilter
{
    public function readCell($column, $row, $worksheetName = '') {
        if (Coordinate::columnIndexFromString($column) > 1024) {
            return false;
        }
        return true;
    }
}

$reader = IOFactory::createReader('Xlsx');
$reader->setReadFilter(new LOReadFilter());
$spreadsheet = $reader->load('./sampleData/example1.xls');

tstaylor7 on Oct 4, 2019

I can approve, that problem is in rowDimensions and columnDimensions. I fix this in my project via reflection hack (remove bad keys). @PowerKiKi, can you reopen this issue?

yar3333 on Sep 11, 2019

Believed resolved

MarkBaker on Feb 23, 2019