listWorksheetInfo method counts rows with empty strings

[karinarastsinskagia]

This is:

- [ ] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

When reading an XLSX file, ignore rows with empty strings

What is the current behavior?

I am trying to read the data of a XLSX file and import them into DB. Until now, I produced excel files using my LibreOffice and everything works fine. However, when I created the same Excel file with the same data in Google Docs I realized that google docs create rows with empty strings. The problem comes when the listWorksheetInfo method calculates the total number of rows.
I noticed that while it's reading the file and storing the total numbers of rows, it jumps suddenly from the "real" total number of rows to a "magic" number of rows.

Please find below a file example with this problem.

SampleExample.xlsx

Does anyone have an explanation about this? Or how can I manage it in order to ignore these empty lines?
Is it possible to set a counter about the total number of rows instead of reading $xml->getAttribute('r') ?

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?

Only detected with XLSX.

[MarkBaker]

The problem lies in people having different definitions of "empty".

Is a cell empty if there is no entry for it in the file, if it contains a null value, it it contains an empty string, if it contains a string of spaces? Is it empty of there it has no value, but there is a data validation rule for the cell, or a conditional formatting rule?

Is a row empty if there is still an entry for it in the file? If there are cell entries that seem empty according to one or other of the definitions above? Is it empty if there is style information for that row?

[MarkBaker]

When you actually load a spreadsheet file, we have an option to ignore empty cells with a fairly arbitrary definition of empty (if the cell contains a null value, an empty string or a string containing only whitespace characters). It's easy to do that check, because we're accessing every cell when we do a load.
But listWorksheetInfo() doesn't check every cell; it's written to be a fast, simple check on basic information about the spreadsheet. If we were to check if a row was empty, we'd have to access any cells for that row, which would make it slower; and we'd still get people disagreeing on the definition of empty

[karinarastsinskagia]

When you actually load a spreadsheet file, we have an option to ignore empty cells with a fairly arbitrary definition of empty (if the cell contains a null value, an empty string or a string containing only whitespace characters). It's easy to do that check, because we're accessing every cell when we do a load. But listWorksheetInfo() doesn't check every cell; it's written to be a fast, simple check on basic information about the spreadsheet. If we were to check if a row was empty, we'd have to access any cells for that row, which would make it slower; and we'd still get people disagreeing on the definition of empty

Thank you a lot for your response.

Which option do you use to ignore empty cells with a fairly arbitrary definition of empty ?
Also how do you explain the total number of rows ? In my screenshots above, you can see that the method jumps suddenly from the 16 rows to 1048576. How is that possible?

[MarkBaker]

Which option do you use to ignore empty cells with a fairly arbitrary definition of empty ?

Set the Reader to ignore empty cells by calling its setReadEmptyCells() with an argument of false before loading the file.

Also how do you explain the total number of rows ? In my screenshots above, you can see that the method jumps suddenly from the 16 rows to 1048576. How is that possible?

As I always explain, it's possible because the Excel file contains information about row number 1048576.

The row may not contain any cells, but it does contain style information, specifically a custom height

[karinarastsinskagia]

he row may not contain any cells, but it does contain style information, specifically a custom height

Set the Reader to ignore empty cells by calling its setReadEmptyCells() with an argument of false before loading the file.

I tried these one but I still get the row number 1048576.

$reader = IOFactory::createReader(IOFactory::READER_XLSX)->setReadEmptyCells(false); $spreadsheet = $reader->load(Storage::path(fileName));

The row may not contain any cells, but it does contain style information, specifically a custom height

Is there any way to ignore this style information or customize it ?

[MarkBaker]

he row may not contain any cells, but it does contain style information, specifically a custom height

Set the Reader to ignore empty cells by calling its setReadEmptyCells() with an argument of false before loading the file.

I tried these one but I still get the row number 1048576.

$reader = IOFactory::createReader(IOFactory::READER_XLSX)->setReadEmptyCells(false); $spreadsheet = $reader->load(Storage::path(fileName));

That's right. setReadEmptyCells() is telling PhpSpreadsheet not to read empty cells; but there are no cells in row 1048576 anyway, so ne cells will be read from that row. The row still exists in the worksheet though, because it has that style information.

The row may not contain any cells, but it does contain style information, specifically a custom height

Is there any way to ignore this style information or customize it ?

The only way to tell PhpSpreadsheet not to read that row is to tell it not to load style information using the Reader's readDataOnly option, calling setReadDataOnly() with a value of true before loading the spreadsheet; but this will load all cells without any style information, or structural information like merges.

[MarkBaker]

So step back a bit, you're trying to load data from the worksheet to store in a db; how are you processing that data? Why are you calling listWorksheetInfo() in the first place?

Are you using something like toArray()? If so, then consider using rangeToArray() instead. You have to specify the range, but you can use the getHighestDataRow() and getHighestDataColumn() to identify the rows and columns that actually contain data.
Or use the Row Iterator to loop through the worksheet; you can tell that to ignore empty rows

[karinarastsinskagia]

I am processing that data using the maatwebsite/excel package which is combined with PHPOffice/PhpSpreadsheet.
The maatwebsite/excel uses the total number of rows which is calculated from the listWorksheetInfo() method.

The maatwebsite/excel supports some events which take place before the data processing. I tried to figure out from there to customize the total rows using the getHighestDataRow() but nothing changed.

Probably now, first I will load and recreate the excel file using the PHPOffice/PhpSpreadsheet Reader and calling setReadDataOnly(), save the new excel and then give it as input to maatwebsite/excel import method to process the data.

Maybe you have a better idea ? I will appreciate it