Spreadsheet::ParseExcel and Hidden Rows/Columns

[jdh]

<I've already sent the email below directly to John's CPAN email
address; sorry! this seems the better approach>

I'm hoping you can point me in the right direction.

I'm now guessing that I was doing it incorrectly but my code had
been assuming that if a row had a height of 0 or a column had a width
of 0 that it was hidden. Normally our business rules are to ignore
hidden row or columns. It appears that when I upgraded to ver 0.54
the height and widths of hidden rows and columns are being handled
differently. I'm having trouble figuring out how to identify that a
row or a column is hidden. I can see that '$format->{Hidden}' will
let me know that a cell is hidden.

And while I'm already bending your ear; is there a way to identify
that an entire sheet is hidden?

I'm still trying to find the answers on my own, like re-reading the
documentation and stepping through the object code and searching the
internet; but I've had no luck yet.

[jdh]

OK I still haven't found a way with ver 0.54 or 0.55 to identify
hidden rows or column. By reviewing ParseExcel.pm, I see a couple of
commented ToDo items related to hidden rows & columns. For the time
being I inserted a line of code into both _rowSub and _subColInfo to
create 2 new worksheet properties:

$worksheet->{ColHidden}->[$col]
$worksheet->{RowHidden}->[$col]

Here are the changes I made:

Changes to _rowSub
# TODO. we need to handle hidden rows:
# $iGr & 0x20
$oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{RowHidden}[$iR] =
($iGr & 0x20) ? 1 : 0; <--Added this line
$oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{RowHeight}[$iR] =
$iHght / 20;


Changes to _subColInfo
# TODO. we need to handle hidden cols: $iGr & 0x01.
$oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{ColHidden}[$i] =
($iGr & 0x01) ? 1 : 0; <--Added this line

Questions, comments, concerns or any other feedback greatly
appreciated.

[jdh]

Sorry I'm rambling. It just occurred to me that I also needed to
create get_row_hiddens and get_col_hiddens methods in Worksheet.pm to
return the RowHidden and ColHidden arrays.

[jmcnamara]

On Oct 27, 4:51 pm, jdh <jeffery.hamm...@gmail.com> wrote:
> Here are the changes I made:
>
> Changes to _rowSub
>     # TODO. we need to handle hidden rows:
>     # $iGr & 0x20
>     $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{RowHidden}[$iR] =
> ($iGr & 0x20) ? 1 : 0;  <--Added this line
>     $oBook->{Worksheet}[ $oBook->{_CurSheet} ]->{RowHeight}[$iR] =
> $iHght / 20;
>

Hi,

That is the correct approach.

This feature is on the TODO list and will be implemented in a future
release.

John.
--

[Mark Emery]

Hi,
Is 0.59 supposed to deal with hidden rows and cels? My .xls created with Excel 2007 has a hidden row and col, but all cells come back with a Hidden value of 0.
Thanks,
Mark

[jmcnamara]

Hi Mark,

Sorry for not responding sooner. That bug is still there.

Regards,

John.
--