0

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?

melkun
  • 1
  • 2
  • It also turned out that some excel readers do not treat this way of hiding columns the same: some might not get hidden. – melkun May 16 '19 at 07:00

1 Answers1

2

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.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
  • It also turned out that different excel readers treat this hiding differently, e.g. some columns at the border of the range might not get hidden. For an unclear reason, giving the original width of a column instead of `undef`, as follows $worksheet->set_column( 'K:M', 60, undef, 1); That would hide columns for all readers I managed to had access to, but also doesn't make much sense to me - at least, if that is important to keep the original width then why it is not mentioned in docs where using `undef` is suggested instead – melkun May 16 '19 at 07:10