Column Hiding FAST, Row Hiding SLOOOOOOOW!

[Calvin]

The last thing I want to do to the worksheet I'm exporting is hide all
the extra rows and columns.

This snippet hides the columns quickly and with no bloat:
$worksheet->set_column('Q:IV', undef, undef, 1);

However, set_row doesn't seem to work on more than one row at a time.
This only sets row 66 and ignores the rest:
$worksheet->set_row('65:65535', undef, undef, 1);

That requires this little gem:
for (my $row=65; $row<=65535; $row++) {
$worksheet->set_row($row, undef, undef, 1);
}

That works, but those three lines multiply the script's run time by 7x
(15s to 105s) and turn a 250KB workbook into 14.8MB. Is there a way
to hide all the rows at once or am I out of luck on this?

[jmcnamara]

On Oct 15, 6:24 pm, Calvin <mandtpr...@gmail.com> wrote:
> The last thing I want to do to the worksheet I'm exporting is hide all
> the extra rows and columns.
>
> This snippet hides the columns quickly and with no bloat:
> $worksheet->set_column('Q:IV', undef, undef, 1);
>
> However, set_row doesn't seem to work on more than one row at a time.
> This only sets row 66 and ignores the rest:
> $worksheet->set_row('65:65535', undef, undef, 1);

...

> That works, but those three lines multiply the script's run time by 7x
> (15s to 105s) and turn a 250KB workbook into 14.8MB.  Is there a way
> to hide all the rows at once or am I out of luck on this?

Hi Calvin,

In the Excel file format column widths are stored together in one
record. This allows WriteExcel to set them all in one go.

Rows, however, each have a separate Excel record so they need to be
set individually. Except in the case that you describe where Excel
uses a clever optimisation. It sets a default row height of zero and
then explicitly sets the row heights of the visible rows.

This optimisation isn't possible with the current WriteExcel interface
but I will add it in a future release.

John.
--