How do I set a workbook-wide default format with Excel::Writer::XLSX?

Question

Is it possible to change or declare a default font with Excel::Writer::XLSX? I can set it for charts and cells every time I create or add them. But I think there should be a more simple way.

Answer 1

Maybe.

Disclaimer: the author of Excel::Writer::XLSX explains this is not a good idea and is not guaranteed to work here and here.

You can set the property xf_index to 0 when creating a Format object. That will set that Format as the default one.

use Excel::Writer::XLSX;

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

# set the default format
$workbook->add_format(xf_index => 0, font => 'Comic Sans MS' ); # I'm on Linux

my $worksheet = $workbook->add_worksheet();
$worksheet->write( 0, 0, 'Hi Excel!' );      # will be in Comic Sans

my $format = $workbook->add_format(font => 'Arial' );
$worksheet->write( 1, 1, 'In Arial!', $format );

This is not described in the docs, but reading the code explains it.

It works even if you add more formats later. You do not need to keep the Format object of that initial call. It will be used throughout the workbook (probably also on different sheets, but I didn't test that).

I suggest explaining what you're doing in a code comment because it's really not very obvious.

After a bit of additional research I found it explained in the PDF document OpenOffice.org's Documentation of the Microsoft Excel File Format, in chapter 4.6.2 at the bottom of page 89 (emphasis mine).

The default cell format is always present in an Excel file, described by the XF record with the fixed index 15 (0-based). By default, it uses the worksheet/workbook default cell style, described by the very first XF record (index 0).

So now we also know why it's xf_index => 0.

---

Old answer with general advice:

In general that's not supported. You need to work with formats. The docs say that there is a default format with Calibri in size 11.

The default format is Calibri 11 with all other properties off.

You need to create a format object for a specific combination of formatting, font family, font size and so on and then you can reuse that throughout the document in each cell or chart. If one of the options differs in a specific cell, you need to make an additional Format object for that.

my $default_format = $workbook->add_format( font => 'Comic Sans' );

# ...

$worksheet->write( 'A1', 'Cell A1', $default_format );

# later ...

$worksheet->write( 'Z3', 'Cell Z3', $default_format );

If you need a lot of different formatting, but always want to use the same font, it might be useful to set the font string as a format hash and use that.

my %default_formatting = ( font => 'Comic Sans' );
my $bold_format = $workbook->add_format( %default_formatting, bold => 1 );

Maybe even make a sub that helps to create the Format objects and already knows about the default font.

sub create_format {
    my ($workbook, %formats) = @_;
    return $workbook->add_format( font => 'Comic Sans', %formats );
}

# somewhere
my $bold_format = create_format( $workbook, bold => 1 );
$worksheet->write( 'A1', 'Cell A1', $bold_format );

That would also allow throw-away formats like this:

$worksheet->write( 'D1', 'Weird stuff', create_format( 
    $workbook, 
    bold      => 1,
    strikeout => 1, 
    shadow    => 1,
    bg_color  => 'pink',
));