Re^2: Win32::OLE and Excel: Iterating when certain row numbers are missing

Thanks for your prompt response!

Well, I knew that my approach would not work as-is, because inserting/deleting rows/columns will cause a one-off error, which the counter would have to account for by incrementing/decrementing.

I'm glad you pointed out that the spreadsheet does indeed have sequential rows, and the "missing" rows are only "invisible" rows. With this knowledge, I'm thinking that I could look for a "Hidden" attribute on rows/cells.

for (my $i = $min_row; $i <= $max_row; $i++) {
    my $current_cell = $sheet->cells($i, 1);
    if ( $current_cell->("Hidden") ) {
      next;
    }
    ... ### Do clever row insertions here.
        ### Increment $i as needed.
}
[download]

Of course, a little bit of googling suggests that Win32::OLE does not surface a "hidden" or "visible" property. Any other ideas?

Comment on Re^2: Win32::OLE and Excel: Iterating when certain row numbers are missing Download Code

Replies are listed 'Best First'.
Re^3: Win32::OLE and Excel: Iterating when certain row numbers are missing by davies (Monsignor) on Jun 21, 2021 at 15:01 UTC
I think you posted your reply as I was posting my second update. There is indeed a "hidden" property exposed, as demonstrated in my line `$sht->Cells(2, 1)->EntireRow->{Hidden} = 1;`. You can test this with something like `if ($row->{Hidden}) {do stuff}`. Update: I've now updated my code to demonstrate testing the hidden property. Regards, John Davies	[reply] [d/l] [select]
Re^3: Win32::OLE and Excel: Iterating when certain row numbers are missing by Corion (Patriarch) on Jun 21, 2021 at 15:03 UTC
You will want to look through the MS Office / Excel Object Model - the Row object has the `Hidden` property I think. An easy way to find such stuff without reading documentation is to Use The Macro Recorder, Luke (Re: Using Win32::OLE and Excel - Tips and Tricks). The whole of Using Win32::OLE and Excel - Tips and Tricks is worth a read.	[reply] [d/l]