PHP Classes
elePHPant
Icontem

rows() returns rows without row indexes and skip empty rows

Recommend this page to a friend!

      Simple XLSX  >  All threads  >  rows() returns rows without row...  >  (Un) Subscribe thread alerts  
Subject:rows() returns rows without row...
Summary:...
Messages:3
Author:Dmitry Agutin
Date:2014-02-10 15:21:51
 

  1. rows() returns rows without row...   Reply   Report abuse  
Picture of Dmitry Agutin Dmitry Agutin - 2014-02-10 15:21:52
Hello,

$xlsx->rows() skips empty rows and returns rows with no real index information.

Here is an example file:
sendspace.com/file/0fbh1i

It has data only on the 3rd row, in cella A3 and C3.

Let's consider the following code then:

$xlsx = new SimpleXLSX($fileName); // our test.xlsx
$dimensions = $xlsx->dimension();
$cells = $xlsx->rows();
$rowsEx = $xlsx->rowsEx();
print_r($dimensions);
print_r($cells);
print_r($rowsEx);

The following text is printed:

Array
(
[0] => 3
[1] => 3
)
Array
(
[0] => Array
(
[0] => test
[1] =>
[2] => testC
)

)
Array
(
[0] => Array
(
[0] => Array
(
[type] => s
[name] => A3
[value] => test
[href] =>
[f] =>
[format] =>
)

[1] => Array
(
[type] =>
[name] => B1
[value] =>
[href] =>
[f] =>
[format] =>
)

[2] => Array
(
[type] => s
[name] => C3
[value] => testC
[href] =>
[f] =>
[format] =>
)

)

)

I see the following problems here:

1) rows() skips empty rows that could lead to bugs if a client doesn't know about that. So I would at least write a PHPDoc comment that the function skips empty rows.
2) rows() returns an array in which there is no way to know real row indexes. As you can see in the printed array above (print_r($cells)), the single item in the array has an index of 0. I believe it would be much better if the key was 3, which would mean it's a 3rd row.
3) rowsEx() function returns information about cells, but you need to parse it to get row number and also it looks weird to me that the second cell in the row item has name B1 - shouldn't it be B3?

At the moment the most critical for me is to know real row index. For example, the old Spreadsheet_Excel_Reader returns an array of rows where keys are real row indexes (it's what I suggested above).

  2. Re: rows() returns rows without row...   Reply   Report abuse  
Picture of Adam Friedman Adam Friedman - 2015-08-16 20:21:12 - In reply to message 1 from Dmitry Agutin
I am having the exact same issue. IMO it is critical for the class to detect blank rows (or provide a configuration setting to turn blank row honoring on or off) given that a user may need to use blank rows themselves as delimiters; for instance for visually segregating groups of data in a spreadsheet.

If you know how to "turn this on" in the code and paste a patch, I'd appreciate it. When I have time I'll look into that and report back.

  3. Re: rows() returns rows without row...   Reply   Report abuse  
Picture of Adam Friedman Adam Friedman - 2015-08-17 15:05:29 - In reply to message 2 from Adam Friedman
OK, I figured it out.

First of all, if you look at the XML form of an XLSX file (rename the file extension from XLSX to ZIP, then unzip it into its components and open up the sheet1.xml file), you will notice that blank rows do not appear in the source XML at all. Instead, the r attribute on each <row>, which indicates the actual row index, will skip a number for every blank row. This was probably by design in order to save in file size.

This creates a problem for this Simple XLSX parser, however. It quietly just iterates through the tags that do exist, in effect leaving nonexistent those that don't, and not bothering with true indexes, as you discuss.

The fix, however, is quite easy. In v0.6.8 of the class, find the foreach loop that spans from line 207 through 220. This is where you simply have to refactor to use the real index ($row['r']) as your parent key in $rows output array, rather than the simple iterator set up for this loop ($curR).

My patch, linked below, is a full replace of the ::rows() method definition:

gist.github.com/amfriedman/8e6bab13 ...