| Subject: | Column formats are not applied |
The series continues (same environment as previous tickets)
When a column has a default format, it is not applied to cells without one, including undefined cells.
To reproduce, open an empty spreadsheet in Excel.
Enter 'x' in cells A5 and B1 (To define a non-empty range).
Select column B, and add a fill color.
Now save and process the sheet.
B1 will have the background color, but b2-b5 will not.
One can patch this with a bit of post-processing, but the Spreadsheet::ParseExcel should return the column format for those cells.
Here is the post-processing logic that I use:
for( my $col = 0; $col < $lastCol; $col++ ) {
my $colFmtNo = $wks->{ColFmtNo}[$col];
if ( defined $colFmtNo
&& ( my $colFmt = $workbook->{Format}[$colFmtNo] ) )
{
for ( my $row = 0 ; $row <= $lastRow ; $row++ ) {
my $cell = $wks->get_cell( $row, $col );
unless ($cell) {
$cell = Spreadsheet::ParseExcel::Cell->new(
Val => '',
FormatNo => $colFmtNo,
Code => undef,
Format => $colFmt,
Type => 'Text',
_Kind => 'BLANK',
_Value => '',
);
$wks->{Cells}[$row][$col] = $cell;
next;
}
my $format = $cell->get_format;
unless ($format) {
$cell->{Format} = $colFmt;
next;
}
}
(This looks a little strange because the code it comes from is also doing other things.)
It appears that if a cell has a format, it completely defines the format of the cell; that is, it's not necessary to merge format fields from the cell with the column. For example, if you add a border to b3, b3 will already have a format structure that has the background color as well as the border.