Columns can be hidden only in groups they were created

Question

I am working on automatization of exporting some data to xlsx-files with Perl, particularly with the module Excel::Writer::XLSX. In case some already created columns are empty or irrelevant, I want them to be hidden. While in some cases that was easily done with common command:

$worksheet->set_column( 'I:J', undef, undef, 1);

in some particular case they would not disappear as supposed to. After a lot of attempts, it turned out that the problem can be solved by changing the way they are originally set.

For example, if I've created them like this:

$worksheet->set_column( 'I:I', 40 );
$worksheet->set_column( 'J:M', 60 );
$worksheet->set_column( 'N:N', 40 );

Then command

$worksheet->set_column( 'K:N', undef, undef, 1);

will only hide column 'N'.

The solution was to create them like that

$worksheet->set_column( 'J:J', 60 );
$worksheet->set_column( 'K:M', 60 );
$worksheet->set_column( 'N:N', 40 );

So it works, but the code looks stupid and the whole situation just makes no sense to me. Does anybody know why it happens, and if yes, then is there any other solution to the problem?

Answer 1

The reason for the strange behaviour is that set_column() doesn't handle the ranges like sets. For example if you set a column range for A:F and then another for C:D then you don't automagically set 3 ranges (A:B, C:D, E:F). So you need to do that split manually.

In your case it would be better to use the numeric range to set_column() like:

$worksheet->set_column( 8, 8, 40 );

# Instead of:
$worksheet->set_column( 'I:I', 40 );

I'd suggest setting up an initial array of arrays (or hashes) with the column widths you will use for each column and then overwriting for the ones you want to hide, and finally looping over array and calling set_column() for each column.