Impossible to set already written cell to empty|undef|''

[fedov]

I didn't find documentation for this behavior. It seems to be impossible to overwrite an already written cell with undef and set it to blank/empty.

use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new( 'overwrite.xlsx' );

$worksheet = $workbook->add_worksheet();

$worksheet->write( 'A1', 'Empty' );
$worksheet->write( 'A2' );
$worksheet->write( 'B1', 'Zero' );
$worksheet->write( 'B2', 0 );

#        Empty|Zero
#             |0

$worksheet->write( 'A2', 0 );
$worksheet->write( 'B2', undef );

#        Empty|Zero
#        0    |0

$worksheet->write( 'A2' );
$worksheet->write( 'B2', '' );

#        Empty|Zero
#        0    |0

$workbook->close();

Perl version : 5.028001 OS name : linux Module versions: (not all are required) Excel::Writer::XLSX 0.98 Spreadsheet::WriteExcel (not installed) Archive::Zip 1.64 XML::Writer (not installed) IO::File 1.39 File::Temp 0.2304

[jmcnamara]

I didn't find documentation for this behavior. It seems to be impossible to overwrite an already written cell with undef and set it to blank/empty.

Thanks for the detailed example.

This behaviour is documented in the write() and write_blank() sections of the docs:

The write() method will ignore empty strings or undef tokens unless a format is also supplied. As such you needn't worry about special handling for empty or undef values in your data. See also the write_blank() method.

And:

Excel differentiates between an "Empty" cell and a "Blank" cell. An "Empty" cell is a cell which doesn't contain data whilst a "Blank" cell is a cell which doesn't contain data but does contain formatting. Excel stores "Blank" cells but ignores "Empty" cells.

As such, if you write an empty cell without formatting it is ignored:

$worksheet->write( 'A1', undef, $format );    # write_blank()
$worksheet->write( 'A2', undef );             # Ignored

So to overwrite a cell with blank data you need to use a default/General format:

$format = $workbook->add_format();

$worksheet->write( 'B2', '', $format );