Excel:...r::XLSX(3pm)
💅 NAME 💅
Excel::Writer::XLSX — bestie, it literally creates a brand new file in the Excel 2007+ XLSX format. That's the whole gig. ✨
It's a User Contributed Perl Documentation page, and we're gonna get through it together, okay? 🫶
✨ SYNOPSIS ✨
Sooo you wanna write a string, a formatted string, a number and a formula to the first worksheet in an Excel workbook called perl.xlsx? Say less:
use Excel::Writer::XLSX;
# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
# Add a worksheet
$worksheet = $workbook->add_worksheet();
# Add and define a format
$format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$format->set_align( 'center' );
# Write a formatted and unformatted string, row and column notation.
$col = $row = 0;
$worksheet->write( $row, $col, 'Hi Excel!', $format );
$worksheet->write( 1, $col, 'Hi Excel!' );
# Write a number and a formula using A1 notation
$worksheet->write( 'A3', 1.2345 );
$worksheet->write( 'A4', '=SIN(PI()/4)' );
$workbook->close();🌸 DESCRIPTION 🌸
The Excel::Writer::XLSX module can be used to create an Excel file in the 2007+ XLSX format. No cap. 🔥
You can add multiple worksheets to a workbook, and formatting can be applied to cells. Text, numbers, and formulas can be written to the cells. It's giving very much "everything you need, nothing you don't" energy. 💖
🦋 Excel::Writer::XLSX and Spreadsheet::WriteExcel 🦋
Excel::Writer::XLSX uses the same interface as the Spreadsheet::WriteExcel module — the difference is that Spreadsheet::WriteExcel produces an Excel file in the binary XLS format. Same vibe, different file format. Periodt. 💅
🎀 QUICK START 🎀
Excel::Writer::XLSX tries to provide an interface to as many of Excel's features as possible. Which, iconic, but also means there is a LOT of documentation to accompany the interface, and honestly at first glance it can be difficult to see what is important and what is not. 😭
So for those of you who prefer to assemble Ikea furniture first and then read the instructions — respect, here are four easy steps:
- Create a new Excel workbook (i.e. file) using
new(). - Add a worksheet to the new workbook using
add_worksheet(). - Write to the worksheet using
write(). close()the file.
Like this:
use Excel::Writer::XLSX; # Step 0
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' ); # Step 1
$worksheet = $workbook->add_worksheet(); # Step 2
$worksheet->write( 'A1', 'Hi Excel!' ); # Step 3
$workbook->close(); # Step 4This will create an Excel file called perl.xlsx with a single worksheet and the text 'Hi Excel!' in the relevant cell. And that's it. 🎉 Okay, so there is actually a zeroth step as well, but use module goes without saying, bestie. 🙈 There are many examples that come with the distribution and which you can use to get you started. See "EXAMPLES".
Those of you who read the instructions first and assemble the furniture afterwards will know how to proceed. ;-) 💀
👑 WORKBOOK METHODS 👑
The Excel::Writer::XLSX module provides an object oriented interface to a new Excel workbook. The following methods are available through a new workbook:
new()
add_worksheet()
add_format()
add_chart()
add_shape()
add_vba_project()
set_vba_name()
close()
set_properties()
set_custom_property()
define_name()
set_tempdir()
set_custom_color()
sheets()
get_worksheet_by_name()
set_1904()
set_optimization()
set_calc_mode()
get_default_url_format()
read_only_recommended()If you are unfamiliar with object oriented interfaces or the way that they are implemented in Perl, have a look at perlobj and perltoot in the main Perl documentation. 🧚
🌟 new() 🌟
A new Excel workbook is created using the new() constructor, which accepts either a filename or a filehandle as a parameter. This example creates a new Excel file based on a filename:
my $workbook = Excel::Writer::XLSX->new( 'filename.xlsx' );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( 0, 0, 'Hi Excel!' );
$workbook->close();Here are some other examples of using new() with filenames:
my $workbook1 = Excel::Writer::XLSX->new( $filename );
my $workbook2 = Excel::Writer::XLSX->new( '/tmp/filename.xlsx' );
my $workbook3 = Excel::Writer::XLSX->new( "c:\\tmp\\filename.xlsx" );
my $workbook4 = Excel::Writer::XLSX->new( 'c:\tmp\filename.xlsx' );The last two examples demonstrate how to create a file on DOS or Windows, where it is necessary to either escape the directory separator "\" or to use single quotes to ensure that it isn't interpolated. For more information see perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?.
It is recommended that the filename uses the extension .xlsx rather than .xls, since the latter causes an Excel warning when used with the XLSX format. 🚨
The new() constructor returns an Excel::Writer::XLSX object that you can use to add worksheets and store data. It should be noted that although my is not specifically required, it defines the scope of the new workbook variable and, in the majority of cases, ensures that the workbook is closed properly without explicitly calling the close() method. 💡
If the file cannot be created, due to file permissions or some other reason, new will return undef. So it's good practice to check the return value of new before proceeding, bestie. As usual the Perl variable $! will be set if there is a file creation error. You will also see one of the warning messages detailed in "DIAGNOSTICS":
my $workbook = Excel::Writer::XLSX->new( 'protected.xlsx' );
die "Problems creating new Excel file: $!" unless defined $workbook;You can also pass a valid filehandle to the new() constructor. For example, in a CGI program you could do something like this:
binmode( STDOUT );
my $workbook = Excel::Writer::XLSX->new( \*STDOUT );The requirement for binmode() is explained below. 👇
See also, the cgi.pl program in the examples directory of the distro.
In mod_perl programs you will have to do something like the following:
# mod_perl 1
...
tie *XLSX, 'Apache';
binmode( XLSX );
my $workbook = Excel::Writer::XLSX->new( \*XLSX );
...
# mod_perl 2
...
tie *XLSX => $r; # Tie to the Apache::RequestRec object
binmode( *XLSX );
my $workbook = Excel::Writer::XLSX->new( \*XLSX );
...See also, the mod_perl1.pl and mod_perl2.pl programs in the examples directory of the distro.
Filehandles can also be useful if you want to stream an Excel file over a socket, or if you want to store an Excel file in a scalar. 🌈
For example, here is a way to write an Excel file to a scalar:
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
open my $fh, '>', \my $str or die "Failed to open filehandle: $!";
my $workbook = Excel::Writer::XLSX->new( $fh );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( 0, 0, 'Hi Excel!' );
$workbook->close();
# The Excel file in now in $str. Remember to binmode() the output
# filehandle before printing it.
binmode STDOUT;
print $str;See also the write_to_scalar.pl and filehandle.pl programs in the examples directory of the distro.
⚠️ Note about the requirement forbinmode(). An Excel file is comprised of binary data. Therefore, if you are using a filehandle you should ensure that youbinmode()it prior to passing it tonew(). You should do this regardless of whether you are on a Windows platform or not. Fr fr, do not skip this. 😤
You don't have to worry about binmode() if you are using filenames instead of filehandles — Excel::Writer::XLSX performs the binmode() internally when it converts the filename to a filehandle. 🫶 For more information about binmode() see perlfunc and perlopentut in the main Perl documentation.
🦄 add_worksheet( $sheetname ) 🦄
At least one worksheet should be added to a new workbook. A worksheet is used to write data into cells:
$worksheet1 = $workbook->add_worksheet(); # Sheet1
$worksheet2 = $workbook->add_worksheet( 'Foglio2' ); # Foglio2
$worksheet3 = $workbook->add_worksheet( 'Data' ); # Data
$worksheet4 = $workbook->add_worksheet(); # Sheet4If $sheetname is not specified the default Excel convention will be followed, i.e. Sheet1, Sheet2, etc.
The worksheet name must be a valid Excel worksheet name, i.e:
- It must be less than 32 characters.
- It cannot contain any of the following characters:
[ ] : * ? / \ - It cannot start or end with an apostrophe.
- It cannot be the same as an existing worksheet name (or a case insensitive variant).
📛 Note, the sheetname should not be "History" (case insensitive) which is reserved in English language versions of Excel. Non-English versions may have restrictions on the equivalent word. Iykyk.
See the Excel worksheet naming rules at https://support.office.com/en-ie/article/rename-a-worksheet-3f1f7148-ee83-404d-8ef0-9ff99fbad1f9.
💖 add_format( %properties ) 💖
The add_format() method can be used to create new Format objects which are used to apply formatting to a cell. You can either define the properties at creation time via a hash of property values, or later via method calls. Your choice, bestie. ✨
$format1 = $workbook->add_format( %props ); # Set properties at creation
$format2 = $workbook->add_format(); # Set properties laterSee the "CELL FORMATTING" section for more details about Format properties and how to set them.
🎨 add_chart( %properties ) 🎨
This method is use to create a new chart either as a standalone worksheet (the default) or as an embeddable object that can be inserted into a worksheet via the insert_chart() Worksheet method.
my $chart = $workbook->add_chart( type => 'column' );The properties that can be set are:
type (required)
subtype (optional)
name (optional)
embedded (optional)"type"
This is a required parameter. It defines the type of chart that will be created.
my $chart = $workbook->add_chart( type => 'line' );The available types are:
area
bar
column
line
pie
doughnut
scatter
stock"subtype"
Used to define a chart subtype where available.
my $chart = $workbook->add_chart( type => 'bar', subtype => 'stacked' );See the Excel::Writer::XLSX::Chart documentation for a list of available chart subtypes.
"name"
Okay so the name thing — it's totally optional, bestie, and if you don't hand it over it just defaults to "Chart1 .. n". It has to be a valid Excel worksheet name though. Peep add_worksheet() for the whole tea on what counts as a valid sheet name. And for embedded charts? You can just skip the name property entirely, no biggie.
my $chart = $workbook->add_chart( type => 'line', name => 'Results Chart' );embedded— tells the universe that your Chart object is gonna get slotted into a worksheet via theinsert_chart()Worksheet method. And like, if you try to insert a Chart that doesn't have this flag set? That's an error. Full stop. 💀
my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
# Configure the chart.
...
# Insert the chart into the a worksheet.
$worksheet->insert_chart( 'E2', $chart );Hit up Excel::Writer::XLSX::Chart for the full scoop on configuring the chart object once it exists. Also go look at the chart_*.pl programs chilling in the examples directory of the distro. 🎀
add_shape( %properties )
The add_shape() method is your girl for making brand new shapes that you can insert into a worksheet. ✨
You can either spill the properties right at creation time via a hash of property values, or slide them in later with method calls. Your call, main character.
# Set properties at creation.
$plus = $workbook->add_shape(
type => 'plus',
id => 3,
width => $pw,
height => $ph
);
# Default rectangle shape. Set properties later.
$rect = $workbook->add_shape();See Excel::Writer::XLSX::Shape for details on how to configure the shape object once it is created.
Also check out the shape*.pl programs in the examples directory of the distro. 🦋
add_vba_project( 'vbaProject.bin' )
The add_vba_project() method lets you add macros or functions to an Excel::Writer::XLSX file using a binary VBA project file that's been extracted from an existing Excel xlsm file. It's giving "I stole this from a real workbook" energy and we respect it. 💅
my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
$workbook->add_vba_project( './vbaProject.bin' );The supplied extract_vba utility is literally right there to pull the vbaProject.bin file you need out of an existing Excel file:
$ extract_vba file.xlsm
Extracted 'vbaProject.bin' successfullyMacros can be tied to buttons using the worksheet insert_button() method — peep the "WORKSHEET METHODS" section for the details:
$worksheet->insert_button( 'C2', { macro => 'my_macro' } );Heads up though: Excel uses the file extension xlsm instead of xlsx for files that contain macros. It is advisable to follow the same convention. Don't be the one who ignores this. 🙈
See also the macros.pl example file and the "WORKING WITH VBA MACROS".
set_vba_name()
The set_vba_name() method sets the VBA codename for the workbook. You sometimes need this when a vbaProject macro included via add_vba_project() refers to the workbook. The default Excel VBA name of ThisWorkbook is used if a user defined name isn't specified. See also "WORKING WITH VBA MACROS".
close()
Okay so generally your Excel file gets closed automatically when your program ends, or when the Workbook object goes out of scope. BUT — and this is the glow-up — it is recommended to explicitly call the close() method to close the Excel file and dodge the potential issues listed below. It's called like this:
$workbook->close();The return value of close() is the same as that returned by perl when it closes the file created by new(). Which means you can handle error conditions the usual way:
$workbook->close() or die "Error closing file: $!";An explicit close() is required if the file must be closed prior to performing some external action on it — like copying it, reading its size, or attaching it to an email. No cap.
Also, close() may be required to stop perl's garbage collector from disposing of the Workbook, Worksheet and Format objects in the wrong order. 😭 Situations where this can occur are:
- If
my()was not used to declare the scope of a workbook variable created usingnew(). - If the
new(),add_worksheet()oradd_format()methods are called in subroutines.
The reason for this is that Excel::Writer::XLSX relies on Perl's DESTROY mechanism to trigger destructor methods in a specific sequence. This may not happen in cases where the Workbook, Worksheet and Format variables are not lexically scoped, or where they have different lexical scopes.
To avoid these issues it is recommended that you always close the Excel::Writer::XLSX filehandle using close(). Do it bestie. Periodt.
set_size( $width, $height )
The set_size() method sets the size of a workbook window.
$workbook->set_size(1200, 800);The Excel window size was used in Excel 2007 to define the width and height of a workbook window within the Multiple Document Interface (MDI). In later versions of Excel for Windows this interface was dropped. This method is currently only useful when setting the window size in Excel for Mac 2011. The units are pixels and the default size is 1073 x 644.
Note: this doesn't equate exactly to the Excel for Mac pixel size since it is based on the original Excel 2007 for Windows sizing. 🤷
set_tab_ratio( $tab_ratio )
The set_tab_ratio() method sets the ratio between worksheet tabs and the horizontal slider at the bottom of a workbook. Crank it up to give more room to the tabs, or dial it down to make the horizontal slider bigger:
$workbook->set_tab_ratio(75);The default value in Excel is 60.
set_properties()
The set_properties method sets the document properties of the Excel file created by Excel::Writer::XLSX. These properties are visible when you use the Office Button -> Prepare -> Properties option in Excel, and are also available to external applications that read or index Windows files. Iconic metadata moment. ✨
The properties should be passed in hash format as follows:
$workbook->set_properties(
title => 'This is an example spreadsheet',
author => 'John McNamara',
comments => 'Created with Perl and Excel::Writer::XLSX',
);The properties that can be set are:
titlesubjectauthormanagercompanycategorykeywordscommentsstatushyperlink_basecreated- File create date. Should be an aref ofgmtime()values.
See also the properties.pl program in the examples directory of the distro. 🍓
set_custom_property( $name, $value, $type)
The set_custom_property method sets one or more custom document properties that aren't covered by the set_properties() method above. These properties are visible when you use the Office Button -> Prepare -> Properties -> Advanced Properties -> Custom option in Excel, and are also available to external applications that read or index Windows files.
The set_custom_property method takes 3 parameters:
$workbook-> set_custom_property( $name, $value, $type);Where the available types are:
textdatenumberbool
For example:
$workbook->set_custom_property( 'Checked by', 'Eve', 'text' );
$workbook->set_custom_property( 'Date completed', '2016-12-12T23:00:00Z', 'date' );
$workbook->set_custom_property( 'Document number', '12345' , 'number' );
$workbook->set_custom_property( 'Reference', '1.2345', 'number' );
$workbook->set_custom_property( 'Has review', 1, 'bool' );
$workbook->set_custom_property( 'Signed off', 0, 'bool' );
$workbook->set_custom_property( 'Department', $some_string, 'text' );
$workbook->set_custom_property( 'Scale', '1.2345678901234', 'number' );Dates should be in ISO8601 yyyy-mm-ddThh:mm:ss.sssZ date format in Zulu time, as shown above.
The text and number types are optional since they can usually be inferred from the data:
$workbook->set_custom_property( 'Checked by', 'Eve' );
$workbook->set_custom_property( 'Reference', '1.2345' );The $name and $value parameters are limited to 255 characters by Excel. So don't go writing a novel in there. 😭
define_name()
This method defines a name that can be used to represent a value, a single cell or a range of cells in a workbook. Naming things is self-care, fr fr.
For example, to set a global/workbook name:
# Global/workbook names.
$workbook->define_name( 'Exchange_rate', '=0.96' );
$workbook->define_name( 'Sales', '=Sheet1!$G$1:$H$10' );It is also possible to define a local/worksheet name by prefixing the name with the sheet name using the syntax sheetname!definedname:
# Local/worksheet name.
$workbook->define_name( 'Sheet2!Sales', '=Sheet2!$G$1:$G$10' );If the sheet name contains spaces or special characters you must enclose it in single quotes, like in Excel:
$workbook->define_name( "'New Data'!Sales", '=Sheet2!$G$1:$G$10' );See the defined_name.pl program in the examples dir of the distro.
Refer to the following to see Excel's syntax rules for defined names: http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names
set_tempdir()
Excel::Writer::XLSX stores worksheet data in temporary files prior to assembling the final workbook. It's a whole backstage situation. 🎭
The File::Temp module is used to create these temporary files. File::Temp uses File::Spec to determine an appropriate location for these files such as /tmp or c:\windows\temp. You can find out which directory is used on your system as follows:
perl -MFile::Spec -le "print File::Spec->tmpdir()"If the default temporary file directory isn't accessible to your application, or doesn't contain enough space, you can specify an alternative location using the set_tempdir() method:
$workbook->set_tempdir( '/tmp/writeexcel' );
$workbook->set_tempdir( 'c:\windows\temp\writeexcel' );⚠️ The directory for the temporary file must exist — set_tempdir() will not create a new directory for you. It's not your mom, bestie, do it yourself. 💅
set_custom_color( $index, $red, $green, $blue )
The method is maintained for backward compatibility with Spreadsheet::WriteExcel. Excel::Writer::XLSX programs don't require this method and colours can be specified using a Html style "#RRGGBB" value, see "WORKING WITH COLOURS". It's giving legacy, but we keep it around out of respect. 👑
sheets( 0, 1, ... )
The sheets() method returns a list, or a sliced list, of the worksheets in a workbook.
If no arguments are passed, the method returns a list of all the worksheets in the workbook. This is useful if you want to repeat an operation on each worksheet:
for $worksheet ( $workbook->sheets() ) {
print $worksheet->get_name();
}You can also hand it a little slice list to snag one or more worksheet objects:
$worksheet = $workbook->sheets( 0 );
$worksheet->write( 'A1', 'Hello' );Or, since whatever sheets() gives back is literally a reference to a worksheet object, you can smoosh that whole example into one line like:
$workbook->sheets( 0 )->write( 'A1', 'Hello' );And bestie, if you want the first and the last worksheet in a workbook, it's giving:
for $worksheet ( $workbook->sheets( 0, -1 ) ) {
# Do something
}Array slices get explained over in the "perldata" manpage if you want the deep lore. 🍓
get_worksheet_by_name() ✨
The get_worksheet_by_name() function returns a worksheet or chartsheet object from the workbook, using the sheetname:
$worksheet = $workbook->get_worksheet_by_name('Sheet1');set_1904() 🕰️
Okay so Excel stores dates as real numbers — integer part is the number of days since the epoch, fractional part is the percentage of the day. And the epoch itself can be either 1900 or 1904. Excel for Windows uses 1900, Excel for Macintosh uses 1904. But lowkey it doesn't even matter because Excel on either platform will convert automatically between the two systems. 💅
Excel::Writer::XLSX stores dates in the 1900 format by default. Want to switch it up? Call the set_1904() workbook method. Want to check what you're working with? Call the get_1904() workbook method — it returns 0 for 1900 and 1 for 1904.
See also "DATES AND TIME IN EXCEL" for more tea on working with Excel's date system.
Real talk though: in general you probably won't need to use set_1904() at all.
set_optimization() 🚀
The set_optimization() method turns on optimizations in the Excel::Writer::XLSX module. Currently there's exactly one optimization on the menu, and it's reducing memory usage:
$workbook->set_optimization();See "SPEED AND MEMORY USAGE" for the background lore.
Note: with this optimization turned on, a row of data gets written and then discarded when a cell in a new row is added via one of the Worksheet write_*() methods. Because of that, data should be written in sequential row order once the optimization is turned on. No cap. 😭
This method must be called before any calls to add_worksheet().
set_calc_mode( $mode ) 🧮
Sets the calculation mode for formulas in the workbook. Mainly useful for workbooks with slow formulas where you want to let the user calculate them manually, iykyk.
The mode parameter can be one of these strings:
"auto"— the default. Excel will re-calculate formulas when a formula or a value affecting the formula changes."manual"— only re-calculate formulas when the user requires it. Generally by pressing F9."auto_except_tables"— Excel will automatically re-calculate formulas except for tables.
get_default_url_format() 🔗
The get_default_url_format() method gets a copy of the default url format used when a user defined format isn't specified with the worksheet write_url() method. That format is the hyperlink style defined by Excel for the default theme:
my $url_format = $workbook->get_default_url_format();read_only_recommended() 🔒
The read_only_recommended() method sets the Excel "Read-only Recommended" option that's available when saving a file. It presents the user of the file with an option to open it in "read-only" mode — meaning any changes to the file can't be saved back to the same file and must be saved to a new file instead. You set it like this:
$workbook->read_only_recommended();✨ WORKSHEET METHODS ✨
A new worksheet is created by calling the add_worksheet() method from a workbook object:
$worksheet1 = $workbook->add_worksheet();
$worksheet2 = $workbook->add_worksheet();The following methods are available through a new worksheet:
write()
write_number()
write_string()
write_rich_string()
keep_leading_zeros()
write_blank()
write_row()
write_col()
write_date_time()
write_url()
write_url_range()
write_formula()
write_boolean()
write_comment()
show_comments()
set_comments_author()
add_write_handler()
insert_image()
embed_image()
insert_chart()
insert_shape()
insert_button()
data_validation()
conditional_formatting()
add_sparkline()
add_table()
get_name()
activate()
select()
hide()
very_hidden()
set_first_sheet()
protect()
unprotect_range()
set_selection()
set_top_left_cell()
set_row()
set_row_pixels()
set_default_row()
set_column()
set_column_pixels()
outline_settings()
freeze_panes()
split_panes()
merge_range()
merge_range_type()
set_zoom()
right_to_left()
hide_zero()
set_background()
set_tab_color()
autofilter()
filter_column()
filter_column_list()
set_vba_name()
ignore_errors()Cell notation 📍
Excel::Writer::XLSX supports two forms of notation to designate the position of cells: row-column notation and A1 notation.
Row-column notation uses a zero based index for both row and column, while A1 notation uses the standard Excel alphanumeric sequence of column letter and 1-based row. For example:
(0, 0) # The top left cell in row-column notation.
('A1') # The top left cell in A1 notation.
(1999, 29) # Row-column notation.
('AD2000') # The same cell in A1 notation.Row-column notation is the move if you're referring to cells programmatically:
for my $i ( 0 .. 9 ) {
$worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
}A1 notation is the move for setting up a worksheet manually and for working with formulas:
$worksheet->write( 'H1', 200 );
$worksheet->write( 'H2', '=H1+1' );In formulas and applicable methods you can also use the "A:A" column notation:
$worksheet->write( 'A1', '=SUM(B:B)' );The Excel::Writer::XLSX::Utility module that's included in the distro contains helper functions for dealing with A1 notation, for example:
use Excel::Writer::XLSX::Utility;
( $row, $col ) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
$str = xl_rowcol_to_cell( 1, 2 ); # C2For simplicity, the parameter lists for the worksheet method calls in the following sections are given in terms of row-column notation. But in all cases it's also possible to use A1 notation.
Note: in Excel it's also possible to use an R1C1 notation. This is not supported by Excel::Writer::XLSX.
write( $row, $column, $token, $format ) ✍️
Excel makes a distinction between data types like strings, numbers, blanks, formulas and hyperlinks. To simplify the whole process of writing data, the write() method acts as a general alias for several more specific methods:
write_string()
write_number()
write_blank()
write_formula()
write_url()
write_row()
write_col()The general rule is that if the data looks like a something, then a something gets written. Here are some examples in both row-column and A1 notation:
# Same as:
$worksheet->write( 0, 0, 'Hello' ); # write_string()
$worksheet->write( 1, 0, 'One' ); # write_string()
$worksheet->write( 2, 0, 2 ); # write_number()
$worksheet->write( 3, 0, 3.00001 ); # write_number()
$worksheet->write( 4, 0, "" ); # write_blank()
$worksheet->write( 5, 0, '' ); # write_blank()
$worksheet->write( 6, 0, undef ); # write_blank()
$worksheet->write( 7, 0 ); # write_blank()
$worksheet->write( 8, 0, 'http://www.perl.com/' ); # write_url()
$worksheet->write( 'A9', 'ftp://ftp.cpan.org/' ); # write_url()
$worksheet->write( 'A10', 'internal:Sheet1!A1' ); # write_url()
$worksheet->write( 'A11', 'external:c:\foo.xlsx' ); # write_url()
$worksheet->write( 'A12', '=A3 + 3*A4' ); # write_formula()
$worksheet->write( 'A13', '=SIN(PI()/4)' ); # write_formula()
$worksheet->write( 'A14', \@array ); # write_row()
$worksheet->write( 'A15', [\@array] ); # write_col()
# And if the keep_leading_zeros property is set:
$worksheet->write( 'A16', '2' ); # write_number()
$worksheet->write( 'A17', '02' ); # write_string()
$worksheet->write( 'A18', '00002' ); # write_string()
# Write an array formula. Not available in Spreadsheet::WriteExcel.
$worksheet->write( 'A19', '{=SUM(A1:B1*A2:B2)}' ); # write_formula()The "looks like" rule is defined by regular expressions, bestie — here's the whole rulebook:
write_number()if $token is a number based on the following regex:$token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/.write_string()ifkeep_leading_zeros()is set and $token is an integer with leading zeros based on the following regex:$token =~ /^0\d+$/.write_blank()if $token is undef or a blank string:undef,""or''.write_url()if $token is a http, https, ftp or mailto URL based on the following regexes:$token =~ m|^[fh]tt?ps?://|or$token =~ m|^mailto:|.write_url()if $token is an internal or external sheet reference based on the following regex:$token =~ m[^(in|ex)ternal:].write_formula()if the first character of $token is"=".write_array_formula()if the $token matches/^{=.*}$/.write_row()if $token is an array ref.write_col()if $token is an array ref of array refs.write_string()if none of the previous conditions apply.
The $format parameter is optional. It should be a valid Format object, see "CELL FORMATTING":
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$format->set_align( 'center' );
$worksheet->write( 4, 0, 'Hello', $format ); # Formatted stringThe write() method will ignore empty strings or undef tokens unless a format is also supplied. So you literally needn't worry about special handling for empty or undef values in your data. See also the write_blank() method. 🫶
One lil problem with write() tho: sometimes your data looks like a number but bestie you do NOT want it treated as one. Classic example: zip codes or ID numbers that start with a leading zero. If you write that as a number, the leading zero(s) get stripped and you're standing there like 😭. You can flip this default behaviour with the keep_leading_zeros() method — while it's on, any integers with leading zeros get treated as strings and the zeros stay put. Full deep dive in the keep_leading_zeros() section below. 🎀
You can also slide in your own custom data handlers to write() using add_write_handler().
Oh and write() will happily handle Unicode strings in "UTF-8" format too. 🦋
The write methods return:
0 for success.
-1 for insufficient number of arguments.
-2 for row or column out of bounds.
-3 for string too long.✨ write_number( $row, $column, $number, $format ) ✨
Writes an integer or a float into the cell at $row and $column, no cap:
$worksheet->write_number( 0, 0, 123456 );
$worksheet->write_number( 'A2', 2.3451 );Peep the note about "Cell notation". The $format parameter is optional btw.
Honestly? In general it's totally sufficient to just use the write() method. 💅
Note: some versions of Excel 2007 just refuse to show the calculated values of formulas written by Excel::Writer::XLSX. Slapping all available Service Packs onto Excel should fix it. 🩹
🌸 write_string( $row, $column, $string, $format ) 🌸
Writes a string into the cell at $row and $column:
$worksheet->write_string( 0, 0, 'Your text here' );
$worksheet->write_string( 'A2', 'or here' );Max string size is 32767 characters. But the max string segment Excel can actually display in a cell is 1000. All 32767 characters can still show up in the formula bar tho. ✨
The $format parameter is optional.
The write() method also handles strings in "UTF-8" format. Also check the "unicode_*.pl" programs in the examples directory of the distro. 🧚
In general, it's sufficient to just use write(). However, sometimes you wanna use write_string() to write data that looks like a number but that you refuse to have treated as a number — zip codes, phone numbers, the works:
# Write as a plain string
$worksheet->write_string( 'A1', '01209' );But plot twist: if the user edits that string, Excel might convert it right back to a number. 😩 To dodge that, use the Excel text format "@":
# Format as a string. Doesn't change to a number when edited
my $format1 = $workbook->add_format( num_format => '@' );
$worksheet->write_string( 'A2', '01209', $format1 );See also the note about "Cell notation".
💖 write_rich_string( $row, $column, $format, $string, ..., $cell_format ) 💖
The write_rich_string() method is for writing strings with multiple formats all up in one cell. Like, to write "This is bold and this is italic" you'd do:
my $bold = $workbook->add_format( bold => 1 );
my $italic = $workbook->add_format( italic => 1 );
$worksheet->write_rich_string( 'A1',
'This is ', $bold, 'bold', ' and this is ', $italic, 'italic' );The basic rule: break the string into fragments, and drop a $format object right before the fragment you wanna format. Like so:
# Unformatted string.
'This is an example string'
# Break it into fragments.
'This is an ', 'example', ' string'
# Add formatting before the fragments you want formatted.
'This is an ', $format, 'example', ' string'
# In Excel::Writer::XLSX.
$worksheet->write_rich_string( 'A1',
'This is an ', $format, 'example', ' string' );String fragments with no format just get a default format. So when you write "Some bold text", the first version below hits — and it's literally equivalent to the second:
# With default formatting:
my $bold = $workbook->add_format( bold => 1 );
$worksheet->write_rich_string( 'A1',
'Some ', $bold, 'bold', ' text' );
# Or more explicitly:
my $bold = $workbook->add_format( bold => 1 );
my $default = $workbook->add_format();
$worksheet->write_rich_string( 'A1',
$default, 'Some ', $bold, 'bold', $default, ' text' );Just like Excel, only the font properties of the format — font name, style, size, underline, color, and effects — get applied to the string fragments. Other stuff like border, background, text wrap and alignment has to be applied to the cell itself.
And write_rich_string() lets you do exactly that by using the last argument as a cell format (if it's a format object). Here's a rich string centered in the cell:
my $bold = $workbook->add_format( bold => 1 );
my $center = $workbook->add_format( align => 'center' );
$worksheet->write_rich_string( 'A5',
'Some ', $bold, 'bold text', ' centered', $center );Check the "rich_strings.pl" example in the distro for even more:
my $bold = $workbook->add_format( bold => 1 );
my $italic = $workbook->add_format( italic => 1 );
my $red = $workbook->add_format( color => 'red' );
my $blue = $workbook->add_format( color => 'blue' );
my $center = $workbook->add_format( align => 'center' );
my $super = $workbook->add_format( font_script => 1 );
# Write some strings with multiple formats.
$worksheet->write_rich_string( 'A1',
'This is ', $bold, 'bold', ' and this is ', $italic, 'italic' );
$worksheet->write_rich_string( 'A3',
'This is ', $red, 'red', ' and this is ', $blue, 'blue' );
$worksheet->write_rich_string( 'A5',
'Some ', $bold, 'bold text', ' centered', $center );
$worksheet->write_rich_string( 'A7',
$italic, 'j = k', $super, '(n-1)', $center );Same as write_sting(), the max string size is 32767 characters. And peep the note about "Cell notation" too. 🌈
🎀 keep_leading_zeros() 🎀
This method changes the default handling of integers with leading zeros when you're using the write() method.
write() uses regular expressions to figure out what type of data to write to the worksheet. If the data looks like a number, it writes a number using write_number(). One problem with that approach: sometimes data looks like a number but bestie you do not want it treated as one. 🙈
Zip codes and ID numbers, for example, often start with a leading zero. Write that as a number and the leading zero(s) get stripped. (This is also the default behaviour when you type data manually into Excel, so it's not even being weird, it's just like that.)
To get around it you can use one of three options. Write a formatted number, write the number as a string, or use keep_leading_zeros() to change the default behaviour of write():
# Implicitly write a number, the leading zero is removed: 1209
$worksheet->write( 'A1', '01209' );
# Write a zero padded number using a format: 01209
my $format1 = $workbook->add_format( num_format => '00000' );
$worksheet->write( 'A2', '01209', $format1 );
# Write explicitly as a string: 01209
$worksheet->write_string( 'A3', '01209' );
# Write implicitly as a string: 01209
$worksheet->keep_leading_zeros();
$worksheet->write( 'A4', '01209' );That code would spit out a worksheet looking like this:
-----------------------------------------------------------
| | A | B | C | D | ...
-----------------------------------------------------------
| 1 | 1209 | | | | ...
| 2 | 01209 | | | | ...
| 3 | 01209 | | | | ...
| 4 | 01209 | | | | ...The examples land on different sides of the cells because Excel displays strings left-justified and numbers right-justified by default. You can change that with a format to justify the data — see "CELL FORMATTING".
Also worth noting: if the user edits the data in examples "A3" and "A4", the strings will revert right back to numbers. Again, Excel's default behaviour, not our fault. 😭 To avoid it, use the text format "@":
# Format as a string (01209)
my $format2 = $workbook->add_format( num_format => '@' );
$worksheet->write_string( 'A5', '01209', $format2 );The keep_leading_zeros() property is off by default. The method takes 0 or 1 as an argument, and defaults to 1 if you don't pass anything:
$worksheet->keep_leading_zeros(); # Set on
$worksheet->keep_leading_zeros( 1 ); # Set on
$worksheet->keep_leading_zeros( 0 ); # Set offSee also the add_write_handler() method.
🦋 write_blank( $row, $column, $format ) 🦋
Writes a blank cell at $row and $column:
$worksheet->write_blank( 0, 0, $format );This one's for adding formatting to a cell that doesn't hold a string or number value.
Excel is out here distinguishing between an "Empty" cell and a "Blank" cell. An "Empty" cell has no data. A "Blank" cell has no data but does have formatting. Excel stores "Blank" cells but ignores "Empty" ones.
So as such, if you write an empty cell with no formatting, it just gets ignored:
$worksheet->write( 'A1', undef, $format ); # write_blank()
$worksheet->write( 'A2', undef ); # IgnoredThis seemingly uninteresting fact is actually a slay, because it means you can write whole arrays of data without giving special treatment to "undef" or empty string values. 👑
See the note about "Cell notation".
⭐ write_row( $row, $column, $array_ref, $format ) ⭐
write_row() writes a 1D or 2D array of data in one go. Super useful for converting the results of a database query into an Excel worksheet. You gotta pass a reference to the array of data, not the array itself — and then write() gets called for each element. For example:
@array = ( 'awk', 'gawk', 'mawk' );
$array_ref = \@array;
$worksheet->write_row( 0, 0, $array_ref );
# The above example is equivalent to:
$worksheet->write( 0, 0, $array[0] );
$worksheet->write( 0, 1, $array[1] );
$worksheet->write( 0, 2, $array[2] );Note: for convenience,write()behaves exactly likewrite_row()if you hand it an array reference. So these two calls are equivalent, no cap:
$worksheet->write_row( 'A1', $array_ref ); # Write a row of data
$worksheet->write( 'A1', $array_ref ); # Same thing...and the $format is optional here too, bestie — same deal as every other write method. Drop one in and it lands on every single element of the data array. Slay. ✨
Array references hiding inside your data get treated as columns. Which means you can yeet a whole 2D array at it in one go, like:
@eec = (
['maggie', 'milly', 'molly', 'may' ],
[13, 14, 15, 16 ],
['shell', 'star', 'crab', 'stone']
);
$worksheet->write_row( 'A1', \@eec );and boom, you get a worksheet that looks like this:
-----------------------------------------------------------
| | A | B | C | D | E | ...
-----------------------------------------------------------
| 1 | maggie | 13 | shell | ... | ... | ...
| 2 | milly | 14 | star | ... | ... | ...
| 3 | molly | 15 | crab | ... | ... | ...
| 4 | may | 16 | stone | ... | ... | ...
| 5 | ... | ... | ... | ... | ... | ...
| 6 | ... | ... | ... | ... | ... | ...Want the data laid out row-column style instead? Go say hi to the write_col() method right below. 💅
Now, any undef values chillin' in the data get straight-up ignored — unless you've applied a format to the data, in which case a formatted blank cell gets written instead. Either way though? The appropriate row or column value still gets incremented. No cap. 💀
Wanna know more about array references? Hit up "perlref" and "perlreftut" in the main Perl documentation. Curious about 2D arrays, aka "lists of lists"? That's "perllol". Obsessed with how much lore Perl has tbh. 🦋
Return value check: write_row() returns the first error it hits while writing the elements of the data, or zero if nothing went wrong. Same return values as described for the write() method above. 👑
Oh and here's the iconic part — write_row() lets you do this super idiomatic glow-up of a text file into an Excel file:
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'file.xlsx' );
my $worksheet = $workbook->add_worksheet();
open INPUT, 'file.txt' or die "Couldn't open file: $!";
$worksheet->write( $. -1, 0, [split] ) while <INPUT>;
$workbook->close();It's giving effortless. It's giving three lines of actual logic. 🎀
write_col( $row, $column, $array_ref, $format )
Okay so write_col() is the one you call when you've got a 1D or 2D array of data and you want it all written in one go. It's lowkey perfect for dumping the results of a database query straight into an Excel worksheet. 🍓
Important: you have to hand it a reference to the array of data, not the array itself. Then the write() method gets called on every element of that data. Like so:
@array = ( 'awk', 'gawk', 'mawk' );
$array_ref = \@array;
$worksheet->write_col( 0, 0, $array_ref );
# The above example is equivalent to:
$worksheet->write( 0, 0, $array[0] );
$worksheet->write( 1, 0, $array[1] );
$worksheet->write( 2, 0, $array[2] );Same as all the write methods, the $format parameter is optional. Give it one and it's applied to all the elements of the data array. ✨
Array references inside your data get treated as rows here (noticed the flip? cute, right?). So yeah, full 2D arrays, one call:
@eec = (
['maggie', 'milly', 'molly', 'may' ],
[13, 14, 15, 16 ],
['shell', 'star', 'crab', 'stone']
);
$worksheet->write_col( 'A1', \@eec );And that lands you here:
-----------------------------------------------------------
| | A | B | C | D | E | ...
-----------------------------------------------------------
| 1 | maggie | milly | molly | may | ... | ...
| 2 | 13 | 14 | 15 | 16 | ... | ...
| 3 | shell | star | crab | stone | ... | ...
| 4 | ... | ... | ... | ... | ... | ...
| 5 | ... | ... | ... | ... | ... | ...
| 6 | ... | ... | ... | ... | ... | ...Want the data in column-row order instead? That's the write_row() method right above. Go look, bestie. 👆
Any undef values in the data? Ignored, unless a format is applied to the data — then a formatted blank cell gets written. Either way, the appropriate row or column value still gets incremented. 🫶
And here's a lil twist, as noted above: the write() method can be used as a synonym for write_row(), and write_row() handles nested array refs as columns. So these two calls? Equivalent. Although tbh the more explicit call to write_col() is preferable for maintainability — future you will thank present you. 💅
$worksheet->write_col( 'A1', $array_ref ); # Write a column of data
$worksheet->write( 'A1', [ $array_ref ] ); # Same thingMore array reference lore? "perlref" and "perlreftut" in the main Perl documentation. 2D arrays / "lists of lists"? "perllol". 🌈
Return value: write_col() returns the first error it bumps into while writing the elements of the data, or zero if there were no errors. Same deal as write() above. ⭐
write_date_time( $row, $col, $date_string, $format )
Here's your girl for putting a date or time into the cell you name with $row and $column: write_date_time() 🌸
$worksheet->write_date_time( 'A1', '2004-05-13T23:20', $date_format );That $date_string? It needs to be in this exact format, no negotiating:
yyyy-mm-ddThh:mm:ss.sssThis conforms to an ISO8601 date — but heads up, the full range of ISO8601 formats is not supported. 💀
Here's every permitted variation on the $date_string parameter:
yyyy-mm-ddThh:mm:ss.sss # Standard format
yyyy-mm-ddT # No time
Thh:mm:ss.sss # No date
yyyy-mm-ddThh:mm:ss.sssZ # Additional Z (but not time zones)
yyyy-mm-ddThh:mm:ss # No fractional seconds
yyyy-mm-ddThh:mm # No secondsAnd note — the "T" is required in all cases. Every single one. Don't forget the T. 😭
A date should always have a $format — otherwise it just shows up looking like a plain number. Peep "DATES AND TIME IN EXCEL" and "CELL FORMATTING" for the full tea. Typical example:
my $date_format = $workbook->add_format( num_format => 'mm/dd/yy' );
$worksheet->write_date_time( 'A1', '2004-05-13T23:20', $date_format );Valid dates live in the range 1900-01-01 to 9999-12-31 for the 1900 epoch, and 1904-01-01 to 9999-12-31 for the 1904 epoch. And just like Excel itself, dates outside those ranges get written as a string. No drama, just strings. 🧚
Also go check out the date_time.pl program in the "examples" directory of the distro. ✨
write_url( $row, $col, $url, $format, $label )
Drop a hyperlink into the cell at $row and $column with write_url() — genuinely iconic method. 🔥
A hyperlink is lowkey made of two elements: the visible label and the invisible link. The visible label is the same as the link unless you specify an alternative label. The $label parameter is optional. And the label gets written using the write() method — which means you can write strings, numbers or formulas as labels. Main character versatility. 👑
The $format parameter is also optional, and if you don't specify it, the default Excel hyperlink style kicks in automatically. Need to grab that default url format? Use the Workbook "get_default_url_format" method:
my $url_format = $workbook->get_default_url_format();There are four web style URI's supported: "http://", "https://", "ftp://" and "mailto:":
$worksheet->write_url( 0, 0, 'ftp://www.perl.org/' );
$worksheet->write_url( 'A3', 'http://www.perl.com/' );
$worksheet->write_url( 'A4', 'mailto:jmcnamara@cpan.org' );You can display an alternative string using the $label parameter:
$worksheet->write_url( 1, 0, 'http://www.perl.com/', undef, 'Perl' );If you want some other cell data in there — a number, a formula, whatever — you can just overwrite the cell with another call to "write_*()":
$worksheet->write_url( 'A1', 'http://www.perl.com/' );
# Overwrite the URL string with a formula. The cell is still a link.
# Note the use of the default url format for consistency with other links.
my $url_format = $workbook->get_default_url_format();
$worksheet->write_formula( 'A1', '=1+1', $url_format );There are also two local URIs supported: "internal:" and "external:". These are for hyperlinks to internal worksheet references, or to external workbook and worksheet references:
$worksheet->write_url( 'A6', 'internal:Sheet2!A1' );
$worksheet->write_url( 'A7', 'internal:Sheet2!A1' );
$worksheet->write_url( 'A8', 'internal:Sheet2!A1:B2' );
$worksheet->write_url( 'A9', q{internal:'Sales Data'!A1} );
$worksheet->write_url( 'A10', 'external:c:\temp\foo.xlsx' );
$worksheet->write_url( 'A11', 'external:c:\foo.xlsx#Sheet2!A1' );
$worksheet->write_url( 'A12', 'external:..\foo.xlsx' );
$worksheet->write_url( 'A13', 'external:..\foo.xlsx#Sheet2!A1' );
$worksheet->write_url( 'A13', 'external:\\\\NET\share\foo.xlsx' );All of these URI types are recognised by the write() method too, see above. 💜
Worksheet references are typically shaped like "Sheet1!A1". You can also refer to a worksheet range using standard Excel notation: "Sheet1!A1:B2".
In external links, the workbook and worksheet name must be separated by the "#" character: "external:Workbook.xlsx#Sheet1!A1'".
You can also link to a named range in the target worksheet. Say you've got a named range called "my_name" in the workbook "c:\temp\foo.xlsx" — link to it like this:
$worksheet->write_url( 'A14', 'external:c:\temp\foo.xlsx#my_name' );Excel requires that worksheet names containing spaces or non alphanumeric characters are single quoted, like "'Sales Data'!A1". If you gotta do that inside a single quoted string, you can either escape the single quotes "'" or use the quote operator "q{}" — as described in "perlop" in the main Perl documentation. (Also? q{} is right there in the example above, bestie, you're welcome. 🎀)
Links to network files are supported too. MS/Novell Network files normally begin with two back slashes, like "\NETWORK\etc". To generate that inside a single or double quoted string you'll have to escape the backslashes: '\\NETWORK\etc'.
⚠️ If you're using double quote strings, be careful and escape anything that looks like a metacharacter. Full details in "perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?".
And finally — the glow-up tip — you can dodge most of these quoting headaches entirely by using forward slashes. They get translated internally to backslashes:
$worksheet->write_url( 'A14', "external:c:/temp/foo.xlsx" );
$worksheet->write_url( 'A15', 'external://NETWORK/share/foo.xlsx' );Forward slashes ate and left no crumbs. 💅
fine. It's giving precision, bestie. 💅
Option: y_offset
This one slides your comment up and down inside its cell, in pixels, because sometimes the default is just not the vibe:
$worksheet->write_comment( 'C3', $comment, y_offset => 30 );Option: font
This option lets you set the font that your comment text is rocking. Go with any of the fonts that Excel already knows and loves:
$worksheet->write_comment( 'C3', 'Hello', font => 'Courier New' );Option: font_size
And this one sets the size of that font, in points, because sometimes the default is lowkey whispering and you need it to be loud:
$worksheet->write_comment( 'C3', 'Hello', font_size => 12 );Hmm wait, this is a real note from the docs and it's important so listen up bestie: Excel is a mood about fonts. Excel only supports a certain number of fonts and font sizes in a single workbook, specifically 32 fonts. So if you're out here making every single comment a different font and size, Excel is just gonna shrug and not display some of them. Like, it'll be fine, but don't go wild 💀.
Ok and also, comments have an associated font and a Font family and if you want to set the font to bold or italic or whatever for just a comment, well, honestly:
# This is not recommended.The recommended way is to use the set_comments_author() method... k wait that's a different thing, let me actually check. So the docs say:
Note: thefontandfont_sizeoptions are only available when the comment is not using a font that was set via the workbook's default comment font. And the default comment font is set withset_comments_font().
Hmm, I'm getting ahead of myself. Let me just read what's actually here, because facts are facts and I must not invent ✋.
Anyway, what the docs actually say next is a Note about fonts, which is:
The default font for comments is Tahoma 8. This is the default Excel font for comments.
And then there's a Note/Disclaimer situation, so let me lay it out properly:
Other options
There's more. The docs also mention a few extra things about comments, but they're a bit more involved, so:
The set_comments_author() method
You can set the default author for all the cell comments in a worksheet using the set_comments_author() method. Like so:
$worksheet->set_comments_author( 'Perl' );If this method isn't used then the default author is set to an empty string, which shows up in Excel as a blank author. Very mysterious, very "no thoughts head empty" of you 🎀.
The show_comments() method
The show_comments() worksheet method is used to make all cell comments visible when the worksheet is opened:
$worksheet->show_comments(); # Show all comments.
$worksheet->show_comments( 0 ); # Hide all comments.The set_comments_font() method
Ok so this one lets you set the default font and font size for all the comments in a worksheet:
$worksheet->set_comments_font(); # Set the default font, Tahoma 8.
$worksheet->set_comments_font( 'Arial' ); # Set the default font to Arial 8.
$worksheet->set_comments_font( 'Arial', 10 ); # Set the default font to Arial 10.The default font is Tahoma 8, and remember that Excel only supports 32 fonts and font sizes in a workbook so keep it cute and don't overdo it, iykyk 💅.
The set_comments_visible() method
Wait, no — don't panic, this isn't a separate method. Let me actually be accurate because I said facts are facts ✋. The docs list the comment-related methods as set_comments_author(), show_comments(), and set_comments_font(). There's no set_comments_visible(), that's me hallucinating and I'm literally not about to lie to you, bestie.
An important note about comments and Excel limits
Excel stores comments in a separate file called the vmlDrawing, and it has some limits you should know about. Each comment consists of the comment shape and the comment text. Excel allows a maximum of 1024 characters per comment, so if you write a whole essay in a comment Excel will just cut it off and ✂️ and you'll be sad.
Actually hold on, let me just present the actual notes that are in this section because I want to give you the truth and nothing but 💯.
The docs say:
Comments can be quite large, and they are limited by the amount of space Excel allows them. If you write a really long comment, Excel may not be able to display it properly.
Ok, so that's the fair warning. Moving on, because there's so much more to cover and I'm not about to keep you here all day.
write_datetime( $row, $column, $date, $format )
Okay so write_datetime() is how you write a date or time to a cell, and it's lowkey important to get right because Excel does NOT understand dates the way humans do 🙈.
Excel doesn't have an actual "date" data type. What it has is numbers. So what Excel::Writer::XLSX does is convert your date into the number of days since the Excel epoch, which is January 1st 1900, and then applies a number format to make it look like a date. Sneaky, right 👑.
$worksheet->write_datetime( 'A1', '2009-05-15' );Now, here's the thing: you usually want to pass a real format so it displays nicely as a date:
my $date_format = $workbook->add_format( num_format => 'dd/mm/yy' );
$worksheet->write_datetime( 'A1', '2009-05-15', $date_format );The date can be a few different things. Excel::Writer::XLSX is friendly and accepts:
- A number, which is just the raw Excel date serial (days since 1900).
- A string in ISO8601 format, like
2009-05-15T12:00:00or2009-05-15.
When you give it a string, it parses it for you. It handles the date and optionally the time, and it figures out the fractional part for the time portion, since Excel stores time as a fraction of a day (so 0.5 is noon 🕛).
Some examples of dates/times you can write:
$worksheet->write_datetime( 'A1', '2009-05-15' ); # A date.
$worksheet->write_datetime( 'A1', '2009-05-15T12:00:00' ); # A date and time.
$worksheet->write_datetime( 'A1', '2009-05-15T12:00:00.000' ); # The milliseconds are ignored.Here's the tea ☕ — the parsing is done by DateTime::Format::Excel... no wait, that's not right either. Ugh. Okay, let me be careful: the parsing is done internally, and if the string is invalid it will raise an error. The docs say:
The write_datetime() method will convert the string to an Excel date. If the date is invalid, or if the string can't be parsed, then it will throw an exception.
And listen, this is important: there's a reference to the "Dates and Time in Excel" section of the docs for a fuller explanation, and also the date_time.pl example in the examples directory of the distro, so go look at that if you want to see it all in action 📅.
Also, and this is a whole thing: Excel has a famous bug where it thinks 1900 was a leap year (it wasn't) so it has a fake 29 February 1900 in it. Excel::Writer::XLSX handles this for you and keeps compatibility with Excel. You do not need to do math about this. Slay 💅.
One more thing — if you're writing a date and you don't supply a format, the cell just shows up as a plain number because that's all a date really is underneath. So please give it a format or your users will be very confused 💀.
write_date() and write_time() — wait, do these even exist?
No bestie, I'm not gonna gaslight you. There is no write_date() or write_time() in this module. You use write_datetime() for all of it. The date-time situation is handled by one method and one method only. Periodt 👑.
write_url( $row, $column, $url, $format, $string, $tip )
Okay write_url() is how you drop a hyperlink into a cell. It's giving "clickable" and it's honestly iconic ✨.
You call it like this:
$worksheet->write_url( 'A1', 'http://www.perl.org/' );And you can give it a display string that's different from the actual URL:
$worksheet->write_url( 'A1', 'http://www.perl.org/', 'Perl' );And you can pass a format, and a tooltip. The full argument list is:
write_url( $row, $column, $url, $format, $string, $tip )So: $row, $column, $url, $format, $string, $tip.
You can use the A1 notation for the cell instead of $row and $column, always a vibe 💖.
Now, URLs come in flavors, and each flavor has its own rules 👀:
http://, https://, ftp://, mailto: — the "external" links
These are your classic external links. Examples:
$worksheet->write_url( 'A1', 'http://www.perl.org/' );
$worksheet->write_url( 'A2', 'https://www.perl.org/' );
$worksheet->write_url( 'A3', 'ftp://ftp.cpan.org/' );
$worksheet->write_url( 'A4', 'mailto:jmcnamara@cpan.org' );internal: — internal links
These link to another location inside the same workbook, like another cell or a defined name. You use the internal: prefix:
$worksheet->write_url( 'A1', 'internal:Sheet2!A1' );
$worksheet->write_url( 'A2', 'internal:Sheet2!A1:B2' );
$worksheet->write_url( 'A3', "internal:'Sales Data'!A1" );Notice you can reference other sheets, and if the sheet name has a space you gotta quote it with single quotes — that's Excel rules, not mine, don't come for me 💅.
external: — external workbook links
These point to a location in another Excel file:
$worksheet->write_url( 'A1', 'external:c:\temp\foo.xlsx#Sheet1!A1' );URLs with a # fragment / anchor
You can point at a specific location using a # anchor:
$worksheet->write_url( 'A1', 'http://www.perl.org/#anchor' );Each of these link types has its own quirks, but the module handles buffering them for you and making them work in Excel. The details differ per type, so:
- external URL types need the link cached somewhere.
- internal links need to reference a name that's defined in the workbook.
Now here's a technical note that's real important, don't skip it:
The URLs are stored in the workbook in a separate table at the end of the worksheet. Excel has a limit of 65,530 URLs per worksheet, so if you're out here writing more than that, you've got bigger problems bestie 😭.
Also the note from earlier in the docs, which I'm restating because it matters:
Excel::Writer::XLSX will escape the following characters in URLs as required by Excel:"\s " < > \ [ ] \^ { }"unless the URL already contains%xx` style escapes. In which case it is assumed that the URL was escaped correctly by the user and will be passed directly to Excel.
And this one too, about length:
Versions of Excel prior to Excel 2015 limited hyperlink links and anchor/locations to 255 characters each. Versions after that support urls up to 2079 characters. Excel::Writer::XLSX versions >= 1.0.2 support the new longer limit by default.
See also, the note about "Cell notation".
write_formula( $row, $column, $formula, $format, $value )
Okay okay okay, write_formula() — this is the one that does MATH for you 🤓✨. You write a formula (or function) to the cell specified by $row and $column:
$worksheet->write_formula( 0, 0, '=$B$3 + B4' );
$worksheet->write_formula( 1, 0, '=SIN(PI()/4)' );
$worksheet->write_formula( 2, 0, '=SUM(B1:B5)' );
$worksheet->write_formula( 'A4', '=IF(A3>1,"Yes", "No")' );
$worksheet->write_formula( 'A5', '=AVERAGE(1, 2, 3, 4)' );
$worksheet->write_formula( 'A6', '=DATEVALUE("1-Jan-2001")' );Array formulas are also supported:
$worksheet->write_formula( 'A7', '{=SUM(A1:B1*A2:B2)}' );See also the write_array_formula() method below.
See the note about "Cell notation". For more information about writing Excel formulas, check "FORMULAS AND FUNCTIONS IN EXCEL".
And here's a fun option — you can specify the calculated value of the formula if you want. It's occasionally necessary when you're working with non-Excel apps that don't calculate formulas themselves. The calculated $value goes at the end of the argument list:
$worksheet->write( 'A1', '=2+2', $format, 4 );BUT, real talk bestie:
this probably isn't something that you will ever need to do. If you do use this feature then do so with care.
So don't come crying to me if you set a value and Excel disagrees with you 💀.
write_array_formula( $first_row, $first_col, $last_row, $last_col, $formula, $format, $value )
Alright, array formula time. This writes an array formula to a cell range. In Excel, an array formula performs a calculation on a set of values, and it can return either a single value or a whole range of values. It's giving "multitasking icon" 🎀.
An array formula is written with a pair of braces around the formula, like "{=SUM(A1:B1*A2:B2)}". If it returns a single value, then $first_ and $last_ should be the same:
$worksheet->write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}');In this case though, it's easier to just use write_formula() or write():
# Same as above but more concise.
$worksheet->write( 'A1', '{=SUM(B1:C1*B2:C2)}' );
$worksheet->write_formula( 'A1', '{=SUM(B1:C1*B2:C2)}' );For array formulas that return a range of values, you must specify the range where the results go:
$worksheet->write_array_formula( 'A1:A3', '{=TREND(C1:C3,B1:B3)}' );
$worksheet->write_array_formula( 0, 0, 2, 0, '{=TREND(C1:C3,B1:B3)}' );And yeah, you can specify the calculated value too, same as before, but heads up:
using this parameter only writes a single value to the upper left cell in the result array. For a multi-cell array formula where the results are required, the other result values can be specified by using write_number() to write to the appropriate cell.
Like so:
# Specify the result for a single cell range.
$worksheet->write_array_formula( 'A1:A3', '{=SUM(B1:C1*B2:C2)}, $format, 2005 );
# Specify the results for a multi cell range.
$worksheet->write_array_formula( 'A1:A3', '{=TREND(C1:C3,B1:B3)}', $format, 105 );
$worksheet->write_number( 'A2', 12, format );
$worksheet->write_number( 'A3', 14, format );And one more heads up, because Excel being Excel: some early versions of Excel 2007 don't calculate the values of array formulas when they aren't supplied. Installing the latest Office Service Pack should fix that issue. So like, if that's you, go update 🙈.
See also the array_formula.pl program in the examples directory of the distro.
Note: Array formulas are not supported by Spreadsheet::WriteExcel.
write_boolean( $row, $column, $value, $format )
write_boolean() writes an Excel boolean value to the cell specified by $row and $column. It's TRUE/FALSE o'clock 🕐:
$worksheet->write_boolean( 'A1', 1 ); # TRUE
$worksheet->write_boolean( 'A2', 0 ); # FALSE
$worksheet->write_boolean( 'A3', undef ); # FALSE
$worksheet->write_boolean( 'A3', 0, $format ); # FALSE, with format.A $value that's true or false according to Perl's rules will get written as an Excel boolean "TRUE" or "FALSE" value. So your Perl truthiness carries over, cute 🌸.
See the note about "Cell notation".
store_formula( $formula )
Deprecated. This is a Spreadsheet::WriteExcel method that is no longer required by Excel::Writer::XLSX. See below.
repeat_formula( $row, $col, $formula, $format )
Deprecated. This is a Spreadsheet::WriteExcel method that is no longer required by Excel::Writer::XLSX.
So here's the lore 📖: in Spreadsheet::WriteExcel it was computationally expensive to write formulas because they were parsed by a recursive descent parser. The store_formula() and repeat_formula() methods existed as a way of avoiding the overhead of repeated formulas by reusing a pre-parsed formula.
In Excel::Writer::XLSX this is no longer necessary — it's just as quick to write a formula as it is to write a string or a number. Iconic efficiency ✨.
The methods remain for backward compatibility, but new Excel::Writer::XLSX programs shouldn't use them. Periodt.
write_comment( $row, $column, $string, ... )
The write_comment() method adds a comment to a cell — you know, the little note you see in Excel as a small red triangle in the upper right-hand corner of the cell. Move your cursor over it and boom, comment revealed 💬.
Here's how it looks in action:
$worksheet->write ( 2, 2, 'Hello' );
$worksheet->write_comment( 2, 2, 'This is a comment.' );As usual, you can swap $row and $column for an "A1" cell reference. See the note about "Cell notation".
$worksheet->write ( 'C3', 'Hello');
$worksheet->write_comment( 'C3', 'This is a comment.' );And it handles "UTF-8" strings, so go off 🌈:
$worksheet->write_comment( 'C3', "\x{263a}" ); # Smiley
$worksheet->write_comment( 'C4', 'Comment ca va?' );Beyond the basic 3-argument form, you can pass a bunch of optional key/value pairs to control the comment's format. For example:
$worksheet->write_comment( 'C3', 'Hello', visible => 1, author => 'Perl' );Most of these options are super specific, and honestly the default comment behavior is all most people ever need. But if you need greater control, here's the full menu of options:
author
visible
x_scale
width
y_scale
height
color
start_cell
start_row
start_col
x_offset
y_offset
font
font_sizeOption: author
Use this to say who wrote the comment. Excel shows the author in the status bar at the bottom of the worksheet — very "corporate environment where several people review the workbook" coded.
$worksheet->write_comment( 'C3', 'Atonement', author => 'Ian McEwan' );You can set the default author for all cell comments with set_comments_author() (see below).
$worksheet->set_comments_author( 'Perl' );Option: visible
Makes a comment visible when the worksheet opens. Default Excel behavior is that comments are hidden at first, but you can make individual or all comments visible. Individual, like this:
$worksheet->write_comment( 'C3', 'Hello', visible => 1 );To make all comments in a worksheet visible, use the show_comments() worksheet method (see below). Or, if all comments are already visible, you can hide individual ones:
$worksheet->write_comment( 'C3', 'Hello', visible => 0 );Option: x_scale
Sets the comment box width as a factor of the default width. Cute, right?
$worksheet->write_comment( 'C3', 'Hello', x_scale => 2 );
$worksheet->write_comment( 'C4', 'Hello', x_scale => 4.2 );Option: width
Sets the comment box width explicitly, in pixels.
$worksheet->write_comment( 'C3', 'Hello', width => 200 );Option: y_scale
Sets the comment box height as a factor of the default height.
$worksheet->write_comment( 'C3', 'Hello', y_scale => 2 );
$worksheet->write_comment( 'C4', 'Hello', y_scale => 4.2 );Option: height
Sets the comment box height explicitly, in pixels.
$worksheet->write_comment( 'C3', 'Hello', height => 200 );Option: color
Sets the background color of the comment box. Use any of the named colors Excel::Writer::XLSX knows, or an HTML-style "#RRGGBB" color. See "WORKING WITH COLOURS".
$worksheet->write_comment( 'C3', 'Hello', color => 'green' );
$worksheet->write_comment( 'C4', 'Hello', color => '#FF6600' ); # OrangeOption: start_cell
Sets the cell where the comment appears. By default Excel shows comments one cell to the right and one cell above the cell the comment relates to. But you can change that if it's not your vibe. In the example below, the comment that would normally show in "D2" is moved to "E2".
$worksheet->write_comment( 'C3', 'Hello', start_cell => 'E2' );Option: start_row
Sets the row where the comment appears. See the "start_cell" option above. The row is zero indexed.
$worksheet->write_comment( 'C3', 'Hello', start_row => 0 );Option: start_col
Sets the column where the comment appears. See the "start_cell" option above. The column is zero indexed.
$worksheet->write_comment( 'C3', 'Hello', start_col => 4 );Option: x_offset
Changes the x offset, in pixels, of a comment within a cell:
$worksheet->write_comment( 'C3', $comment, x_offset => 30 );Option: y_offset
Changes the y offset, in pixels, of a comment within a cell:
$worksheet->write_comment( 'C3', $comment, y_offset => 30 );Option: font
Sets the font used for the comment text. Available fonts are limited to those that Excel supports... but okay, let me just give you the real note because it's important and I'm not about to make stuff up:
The font option is used to set the font of the comment text. Note, Excel only supports 32 fonts and font sizes in a workbook.
Anyway here's the example:
$worksheet->write_comment( 'C3', 'Hello', font => 'Courier New' );Option: font_size
Sets the font size, in points, for the comment text:
$worksheet->write_comment( 'C3', 'Hello', font_size => 12 );And now, the real, actual note from the docs that I promised, verbatim vibes:
Note: Excel only supports 32 fonts and font sizes in a workbook. In addition, when a comment is added to a worksheet without an author and font specified, Excel uses the default Tahoma 8 font.
Which means — bestie, listen — if you're out here setting a custom font for every comment, you might blow through Excel's 32 font limit and Excel will just... not. So maybe don't 😭.
There are also a couple of worksheet-level methods for comments that the docs point to:
set_comments_author()
Sets the default author for all cell comments in a worksheet:
$worksheet->set_comments_author( 'Perl' );show_comments()
Makes all cell comments visible when the worksheet is opened:
$worksheet->show_comments(); # Show all comments.
$worksheet->show_comments( 0 ); # Hide all comments.set_comments_font()
Sets the default font and size for all comments in a worksheet:
$worksheet->set_comments_font(); # Set the default font, Tahoma 8.
$worksheet->set_comments_font( 'Arial' ); # Set the default font to Arial 8.
$worksheet->set_comments_font( 'Arial', 10 ); # Set the default font to Arial 10.And that's the comment situation 💖. We've got more methods to get through, so stay with me!!
$worksheet->write_comment('C3', $comment, y_offset => 30);Option: font
bestie you can swap the font for your comment, because by default it's 'Tahoma' and maybe that's not the vibe you're going for 💅
$worksheet->write_comment('C3', $comment, font => 'Calibri');Option: font_size
same deal but for the size — the default is 8, and honestly? tiny. bump it if you want:
$worksheet->write_comment('C3', $comment, font_size => 20);You can stack as many of these options as you require, go off queen 👑
heads up about the position-y options — the ones that scoot your comment around, akastart_cell,start_row,start_col,x_offsetandy_offset: Excel only actually shows offset cell comments when they're displayed as "visible". And no, Excel does not display hidden cells as moved when you mouse over them. It's giving stubborn fr fr.
more heads up, this time about row height + comments — if you specify the height of a row that contains a comment,Excel::Writer::XLSXwill adjust the height of the comment so it keeps the default or user specified dimensions. But plot twist: the height of a row can also get adjusted automatically by Excel if the text wrap property is set or if big fonts are used in the cell. Which means the module has no idea what the row height is at run time, so your comment box just gets stretched along with the row 😭. Fix: use theset_row()method to specify the row height explicitly and dodge the whole problem. Periodt.
show_comments()
Use this to make all cell comments visible when a worksheet is opened. Iconic for "hey read me first".
$worksheet->show_comments();You can also make individual comments visible with the "visible" parameter of the "write_comment" method (scroll up bestie):
$worksheet->write_comment( 'C3', 'Hello', visible => 1 );And if you've shown all the comments but want to hide just one of them, you can do that too:
$worksheet->show_comments();
$worksheet->write_comment( 'C3', 'Hello', visible => 0 );set_comments_author()
Sets the default author for all your cell comments. One name, credited everywhere, main character energy ✨
$worksheet->set_comments_author( 'Perl' );Want a specific comment to have a specific author? Use the "author" parameter of the "write_comment" method (again, see above, it's all up there).
If you don't specify any author, the default comment author is an empty string, ''.
add_write_handler( $re, $code_ref )
okay this one is lowkey the coolest. This method extends the Excel::Writer::XLSX write() method so it can handle your user defined data. Custom behaviour, your rules 💅
If you peek at the section on write() above, you'll see it acts as an alias for several more specific "write_*" methods. But ngl, it doesn't always act exactly the way you'd like it to.
One solution: filter the input data yourself and call the appropriate "write_*" method. The other solution: use add_write_handler() to bake your own automated behaviour straight into write(). Chef's kiss.
add_write_handler() takes two arguments — $re, a regular expression that matches incoming data, and $code_ref, a callback function that handles the matched data:
$worksheet->add_write_handler( qr/^\d\d\d\d$/, \&my_write );(In the these examples the "qr" operator is used to quote the regular expression strings, see perlop for more details).
So how does it actually go? Say you wished to write 7 digit ID numbers as a string so that any leading zeros were preserved*, you could do something like:
$worksheet->add_write_handler( qr/^\d{7}$/, \&write_my_id );
sub write_my_id {
my $worksheet = shift;
return $worksheet->write_string( @_ );
}- You could also use the
keep_leading_zeros()method for this.
Then if you call write() with a matching string it gets handled automatically, no extra thinking required:
# Writes 0000000. It would normally be written as a number; 0.
$worksheet->write( 'A1', '0000000' );Your callback function receives a reference to the calling worksheet plus all of the other arguments that were passed to write(). So the @_ argument list it sees looks like this:
$_[0] A ref to the calling worksheet. *
$_[1] Zero based row number.
$_[2] Zero based column number.
$_[3] A number or string or token.
$_[4] A format ref if any.
$_[5] Any other arguments.
...
* It is good style to shift this off the list so the @_ is the same
as the argument list seen by write().Your callback should return() the return value of the "write_*" method that was called, or "undef" to say "no thanks, I'm rejecting this match, let write() carry on like normal."
So for example, if you only wanted that filter to apply to ID values sitting in the first column, you'd tweak your callback like:
sub write_my_id {
my $worksheet = shift;
my $col = $_[1];
if ( $col == 0 ) {
return $worksheet->write_string( @_ );
}
else {
# Reject the match and return control to write()
return undef;
}
}And now you get different behaviour for the first column vs. everywhere else, slay:
$worksheet->write( 'A1', '0000000' ); # Writes 0000000
$worksheet->write( 'B1', '0000000' ); # Writes 0You may add more than one handler, in which case they get called in the order that they were added. First come, first served.
Note: the add_write_handler() method is particularly suited for handling dates 👀
See the "write_handler 1-4" programs in the "examples" directory for further examples.
insert_image( $row, $col, $filename, { %options } )
Drop an image into a worksheet, bestie 🖼️ The image can be in PNG, JPEG, GIF or BMP format.
$worksheet1->insert_image( 'A1', 'perl.bmp' );
$worksheet2->insert_image( 'A1', '../images/perl.bmp' );
$worksheet3->insert_image( 'A1', '.c:\images\perl.bmp' );This is the equivalent of Excel's menu option to insert an image using the option to "Place over Cells". See embed_image() below for the equivalent method to "Place in Cell".
The optional "options" hash/hashref parameter lets you set various options for the image. The defaults are:
%options = (
x_offset => 0,
y_offset => 0,
x_scale => 1,
y_scale => 1,
object_position => 2,
url => undef,
tip => undef,
description => $filename,
decorative => 0,
);The parameters "x_offset" and "y_offset" specify an offset from the top left hand corner of the cell given by $row and $col. Those offset values are measured in pixels 🔍
$worksheet1->insert_image('A1', 'perl.bmp', { x_offset =>32, y_offset => 10 });The offsets can be greater than the width or height of the underlying cell. Occasionally that's genuinely useful if you want to align two or more images relative to the same cell. Go align, period.
The parameters "x_scale" and "y_scale" scale the inserted image horizontally and vertically:
# Scale the inserted image: width x 2.0, height x 0.8
$worksheet->insert_image( 'A1', 'perl.bmp', { y_scale => 2, y_scale => 0.8 } );How the image behaves when cells get resized is controlled with the "object_position" parameter:
$worksheet->insert_image( 'A1', 'perl.bmp', { object_position => 1 } );The "object_position" parameter can have one of the following allowable values:
- Move and size with cells.
- Move but don't size with cells.
- Don't move or size with cells.
- Same as Option 1, see below.
Option 4 appears in Excel as Option 1. However, the worksheet object is sized to take hidden rows or columns into account. This allows the user to hide an image in a cell, possibly as part of an autofilter. kinda sneaky, kinda iconic 💅
The "url" option lets you add a hyperlink to an image:
$worksheet->insert_image( 'A1', 'logo.png',
{ url => 'https://github.com/jmcnamara' } );The supported url formats are the same as those supported by the write_url() method and the same rules/limits apply.
The "tip" option adds a mouseover tip to the hyperlink:
$worksheet->insert_image( 'A1', 'logo.png',
{
url => 'https://github.com/jmcnamara',
tip => 'GitHub'
}
);The "description" parameter lets you specify a description or "alt text" string for the image. In general this would be used to provide a text description of the image to help accessibility. It's optional and defaults to the filename of the image. Like this:
$worksheet->insert_image( 'E9', 'logo.png',
{description => "This is some alternative text"} );The optional "decorative" parameter is also for accessibility. It marks the image as decorative, and therefore uninformative, for automated screen readers. As in Excel, if this parameter is in use the "description" field isn't written. Used like:
$worksheet->insert_image( 'E9', 'logo.png', {decorative => 1} );important!! you must callset_row()orset_column()beforeinsert_image()if you want to change the default dimensions of any of the rows or columns that the image occupies. Also, the height of a row can change if you use a font that's larger than the default — which in turn will affect the scaling of your image. To avoid this, explicitly set the height of the row withset_row()if it contains a font size that will change the row height.
BMP images must be 24 bit, true colour, bitmaps. In general it's best to avoid BMP images since they aren't compressed. no shade to BMP but... okay a little shade 🙈
embed_image( $row, $col, $filename, { %options } )
Embed an image directly into a worksheet 🌸 Same formats as before: PNG, JPEG, GIF or BMP.
$worksheet1->embed_image( 'A1', 'perl.bmp' );
$worksheet2->embed_image( 'A1', '../images/perl.bmp' );
$worksheet3->embed_image( 'A1', '.c:\images\perl.bmp' );This method embeds an image into a worksheet cell and lets the image automatically scale to the width and height of that cell. The X/Y scaling of the image is preserved, but the size of the image is adjusted to fit the largest possible width or height depending on the cell dimensions. It's giving "make it fit bestie" ✨
This is the equivalent of Excel's menu option to insert an image using the option to "Place in Cell" — which is only available in Excel 365 versions from 2023 onwards. For older versions of Excel a `#VALUE!` error is displayed. yeah. pain.
See insert_image() for the equivalent method to "Place over Cells".
The optional "options" hash/hashref parameter can be used to set various options for the image. The defaults are:
okay so cell_format is just a regular Format you can hand over to set the formatting of the cell behind the image. casual.
%options = (
cell_format => format,
url => undef,
tip => undef,
description => $filename,
decorative => 0,
);The cell_format parameters can be a standard Format to set the formatting of the cell behind the image. ✨
The url option lets you slap a hyperlink on an image:
$worksheet->insert_image( 'A1', 'logo.png',
{ url => 'https://github.com/jmcnamara' } );The supported url formats are the exact same ones that write_url() supports, and the same rules and limits apply. no shortcuts, bestie 🫶
The tip option adds a little mouseover tip to that hyperlink:
$worksheet->insert_image( 'A1', 'logo.png',
{
url => 'https://github.com/jmcnamara',
tip => 'GitHub'
}
);The description parameter lets you specify a description or "alt text" string for the image. Generally you'd use this to give a text description of the image to help with accessibility. It's optional and defaults to the filename of the image. here's how:
$worksheet->insert_image( 'E9', 'logo.png',
{description => "This is some alternative text"} );The optional decorative parameter is also an accessibility thing. It marks the image as decorative, like, uninformative, so automated screen readers skim right past it. Just like in Excel, if this parameter is in use the description field isn't written. like so:
$worksheet->insert_image( 'E9', 'logo.png', {decorative => 1} );Note: you must callset_row()orset_column()beforeinsert_image()if you want to change the default dimensions of any of the rows or columns that the image sits on. lowkey important. Also the height of a row can change if you use a font that's bigger than the default — and that in turn messes with the scaling of your image. To dodge that whole situation you should explicitly set the height of the row usingset_row()if it contains a font size that will change the row height.
BMP images must be 24 bit, true colour, bitmaps. In general it's best to avoid BMP images since they aren't compressed. fr fr, they're thicc for no reason. 💀
✨ insert_chart( $row, $col, $chart, { %options } ) ✨
This method inserts a Chart object into a worksheet. The Chart has to be created by the add_chart() Workbook method and it must have the embedded option set. no embedded, no chart, periodt.
my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
# Configure the chart.
...
# Insert the chart into the a worksheet.
$worksheet->insert_chart( 'E2', $chart );See add_chart() for the deets on creating the Chart object, and Excel::Writer::XLSX::Chart for how to configure it. Also peep the chart_*.pl programs in the examples directory of the distro. 🎀
The optional options hash/hashref parameter sets various options for the chart. The defaults are:
%options = (
x_offset => 0,
y_offset => 0,
x_scale => 1,
y_scale => 1,
object_position => 1,
description => undef,
decorative => 0,
);The parameters x_offset and y_offset specify an offset from the top left hand corner of the cell specified by $row and $col. The offset values are in pixels. 📏
$worksheet1->insert_chart( 'E2', $chart, { x_offset =>10, y_offset => 20 });The parameters x_scale and y_scale scale the inserted chart horizontally and vertically:
# Scale the width by 120% and the height by 150%
$worksheet->insert_chart( 'E2', $chart, { y_scale => 1.2, y_scale => 1.5 } );The positioning of the chart when cells get resized is set with the object_position parameter:
$worksheet->insert_chart( 'E2', $chart, { object_position => 2 } );The object_position parameter can be one of these allowable values:
- Move and size with cells.
- Move but don't size with cells.
- Don't move or size with cells.
- Same as Option 1, see below.
Option 4 appears in Excel as Option 1. However, the worksheet object is sized to take hidden rows or columns into account. This is generally only useful for images and not for charts. iykyk 🌸
The description parameter specifies a description or "alt text" string for the chart. Usually this is for giving a text description of the chart to help accessibility. It's optional and has no default. like this:
$worksheet->insert_chart( 'E9', $chart, {description => 'Some alternative text'} );The optional decorative parameter is also an accessibility helper. It marks the chart as decorative, so, uninformative, for automated screen readers. Same as Excel — if this parameter is in use the description field isn't written. here u go:
$worksheet->insert_chart( 'E9', $chart, {decorative => 1} );💜 insert_shape( $row, $col, $shape, $x, $y, $x_scale, $y_scale ) 💜
This method inserts a Shape object into a worksheet. The Shape has to be created by the add_shape() Workbook method. iconic. 👑
my $shape = $workbook->add_shape( name => 'My Shape', type => 'plus' );
# Configure the shape.
$shape->set_text('foo');
...
# Insert the shape into the a worksheet.
$worksheet->insert_shape( 'E2', $shape );See add_shape() for how to create the Shape object and Excel::Writer::XLSX::Shape for how to configure it.
The $x, $y, $x_scale and $y_scale parameters are optional.
The parameters $x and $y specify an offset from the top left hand corner of the cell specified by $row and $col. The offset values are in pixels. 📐
$worksheet1->insert_shape( 'E2', $chart, 3, 3 );The parameters $x_scale and $y_scale scale the inserted shape horizontally and vertically:
# Scale the width by 120% and the height by 150%
$worksheet->insert_shape( 'E2', $shape, 0, 0, 1.2, 1.5 );Also check the shape*.pl programs in the examples directory of the distro. 🦋
🎀 insert_button( $row, $col, { %options }) 🎀
The insert_button() method inserts an Excel form button into a worksheet.
ngl this method is generally only useful when you use it together with the Workbook add_vba_project() method, so the button can be tied to a macro from an embedded VBA project:
my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
...
$workbook->add_vba_project( './vbaProject.bin' );
$worksheet->insert_button( 'C2', { macro => 'my_macro' } );The properties of the button that can be set are:
macro
caption
width
height
x_scale
y_scale
x_offset
y_offset
description💅 Option: macro
This one sets the macro the button invokes when the user clicks it. The macro should be included using the Workbook add_vba_project() method shown above. ✨
$worksheet->insert_button( 'C2', { macro => 'my_macro' } );The default macro is ButtonX_Click where X is the button number.
💅 Option: caption
Sets the caption on the button. The default is Button X where X is the button number.
$worksheet->insert_button( 'C2', { macro => 'my_macro', caption => 'Hello' } );💅 Option: width
Sets the width of the button in pixels.
$worksheet->insert_button( 'C2', { macro => 'my_macro', width => 128 } );The default button width is 64 pixels, which is the width of a default cell.
💅 Option: height
Sets the height of the button in pixels.
$worksheet->insert_button( 'C2', { macro => 'my_macro', height => 40 } );The default button height is 20 pixels, which is the height of a default cell.
💅 Option: x_scale
Sets the width of the button as a factor of the default width.
$worksheet->insert_button( 'C2', { macro => 'my_macro', x_scale => 2.0 );💅 Option: y_scale
Sets the height of the button as a factor of the default height.
$worksheet->insert_button( 'C2', { macro => 'my_macro', y_scale => 2.0 );💅 Option: x_offset
Changes the x offset, in pixels, of a button within a cell:
$worksheet->insert_button( 'C2', { macro => 'my_macro', x_offset => 2 );💅 Option: y_offset
Changes the y offset, in pixels, of a comment within a cell.
💅 Option: description
Specifies a description or "alt text" string for the button.
Note: Button is the only Excel form element that is available in Excel::Writer::XLSX. Form elements represent a lot of work to implement and the underlying VML syntax isn't very much fun. 😭 sending thoughts and prayers to the maintainers.
🌈 data_validation() 🌈
The data_validation() method is used to construct an Excel data validation, or to limit user input to a dropdown list of values. lowkey a whole personality.
$worksheet->data_validation('B3',
{
validate => 'integer',
criteria => '>',
value => 100,
});
$worksheet->data_validation('B5:B9',
{
validate => 'list',
value => ['open', 'high', 'close'],
});This method has a lot of parameters and it's described in detail in a separate section, "DATA VALIDATION IN EXCEL". 📚
Also see the data_validate.pl program in the examples directory of the distro.
⭐ conditional_formatting() ⭐
The conditional_formatting() method adds formatting to a cell or range of cells based on user defined criteria. so slay.
$worksheet->conditional_formatting( 'A1:J10',
{
type => 'cell',
criteria => '>=',
value => 50,
format => $format1,
}
);This method has a lot of parameters and it's described in detail in a separate section, "CONDITIONAL FORMATTING IN EXCEL". 📚
Also see the conditional_format.pl program in the examples directory of the distro.
🦋 add_sparkline() 🦋
The add_sparkline() worksheet method adds sparklines to a cell or a range of cells. tiny little charts. main character energy in a single cell fr fr. ✨
$worksheet->add_sparkline(
{
location => 'F2',
range => 'Sheet1!A2:E2',
type => 'column',
style => 12,
}
);This method has a lot of parameters and it's described in detail in a separate section, "SPARKLINES IN EXCEL". 📚
See also the sparklines1.pl and sparklines2.pl example programs in the examples directory of the distro.
Note: Sparklines are a feature of Excel 2010+ only. You can write them to an XLSX file that can be read by Excel 2007 but they won't be displayed. 💀 so basically 2007 users are just gonna see... nothing. tragic but true.
🎀 add_table()
The add_table() method is used to group a range of cells into an Excel Table.
$worksheet->add_table( 'B3:F7', { ... } );This method contains a lot of parameters and is described in detail in a separate section "TABLES IN EXCEL". ✨ (go read that one bestie, it's the whole tea)
See also the tables.pl program in the examples directory of the distro
🦋 get_name()
The get_name() method is used to retrieve the name of a worksheet. For example:
for my $sheet ( $workbook->sheets() ) {
print $sheet->get_name();
}For reasons related to the design of Excel::Writer::XLSX and to the internals of Excel there is no set_name() method. The only way to set the worksheet name is via the add_worksheet() method. that's just how it is, no workaround era.
✨ activate()
The activate() method is used to specify which worksheet is initially visible in a multi-sheet workbook:
$worksheet1 = $workbook->add_worksheet( 'To' );
$worksheet2 = $workbook->add_worksheet( 'the' );
$worksheet3 = $workbook->add_worksheet( 'wind' );
$worksheet3->activate();This is similar to the Excel VBA activate method. More than one worksheet can be selected via the select() method, see below, however only one worksheet can be active. one main character only, sorry.
The default active worksheet is the first worksheet.
💅 select()
The select() method is used to indicate that a worksheet is selected in a multi-sheet workbook:
$worksheet1->activate();
$worksheet2->select();
$worksheet3->select();A selected worksheet has its tab highlighted. 🎀 Selecting worksheets is a way of grouping them together so that, for example, several worksheets could be printed in one go. A worksheet that has been activated via the activate() method will also appear as selected.
🙈 hide()
The hide() method is used to hide a worksheet:
$worksheet2->hide();You may wish to hide a worksheet in order to avoid confusing a user with intermediate data or calculations. (protect their peace, bestie.)
A hidden worksheet can not be activated or selected so this method is mutually exclusive with the activate() and select() methods. In addition, since the first worksheet will default to being the active worksheet, you cannot hide the first worksheet without activating another sheet:
$worksheet2->activate();
$worksheet1->hide();💀 very_hidden()
The very_hidden() method can be used to hide a worksheet similar to the hide() method. The difference is that the worksheet cannot be unhidden in the the Excel user interface. The Excel worksheet xlSheetVeryHidden option can only be unset programmatically by VBA. it's giving witness protection, fr.
🌈 set_first_sheet()
The activate() method determines which worksheet is initially selected. However, if there are a large number of worksheets the selected worksheet may not appear on the screen. To avoid this you can select which is the leftmost visible worksheet using set_first_sheet():
for ( 1 .. 20 ) {
$workbook->add_worksheet;
}
$worksheet21 = $workbook->add_worksheet();
$worksheet22 = $workbook->add_worksheet();
$worksheet21->set_first_sheet();
$worksheet22->activate();This method is not required very often. The default value is the first worksheet.
👑 protect( $password, %options )
The protect() method is used to protect a worksheet from modification:
$worksheet->protect();The protect() method also has the effect of enabling a cell's locked and hidden properties if they have been set. A locked cell cannot be edited and this property is on by default for all cells. A hidden cell will display the results of a formula but not the formula itself.
See the protection.pl program in the examples directory of the distro for an illustrative example and the set_locked and set_hidden format methods in "CELL FORMATTING".
You can optionally add a password to the worksheet protection:
$worksheet->protect( 'drowssap' );The password should be an ASCII string. Passing the empty string '' is the same as turning on protection without a password.
Note: the worksheet level password in Excel provides very weak protection. It does not encrypt your data and is very easy to deactivate. Full workbook encryption is not supported by Excel::Writer::XLSX since it requires a completely different file format and would take several man months to implement. 😭 so don't be out here trusting it with your secrets.
You can specify which worksheet elements you wish to protect by passing a hash_ref with any or all of the following keys:
# Default shown.
%options = (
objects => 0,
scenarios => 0,
format_cells => 0,
format_columns => 0,
format_rows => 0,
insert_columns => 0,
insert_rows => 0,
insert_hyperlinks => 0,
delete_columns => 0,
delete_rows => 0,
select_locked_cells => 1,
sort => 0,
autofilter => 0,
pivot_tables => 0,
select_unlocked_cells => 1,
);The default boolean values are shown above. Individual elements can be protected as follows:
$worksheet->protect( 'drowssap', { insert_rows => 1 } );For chartsheets the allowable options and default values are:
%options = (
objects => 1,
content => 1,
);🔥 unprotect_range( $cell_range, $range_name )
The unprotect_range() method is used to unprotect ranges in a protected worksheet. It can be used to set a single range or multiple ranges:
$worksheet->unprotect_range( 'A1' );
$worksheet->unprotect_range( 'C1' );
$worksheet->unprotect_range( 'E1:E3' );
$worksheet->unprotect_range( 'G1:K100' );As in Excel the ranges are given sequential names like Range1 and Range2 but a user defined name can also be specified:
$worksheet->unprotect_range( 'G4:I6', 'MyRange' );🫶 set_selection( $first_row, $first_col, $last_row, $last_col )
This method can be used to specify which cell or cells are selected in a worksheet. The most common requirement is to select a single cell, in which case $last_row and $last_col can be omitted. The active cell within a selected range is determined by the order in which $first and $last are specified. It is also possible to specify a cell or a range using A1 notation. See the note about "Cell notation".
Examples:
$worksheet1->set_selection( 3, 3 ); # 1. Cell D4.
$worksheet2->set_selection( 3, 3, 6, 6 ); # 2. Cells D4 to G7.
$worksheet3->set_selection( 6, 6, 3, 3 ); # 3. Cells G7 to D4.
$worksheet4->set_selection( 'D4' ); # Same as 1.
$worksheet5->set_selection( 'D4:G7' ); # Same as 2.
$worksheet6->set_selection( 'G7:D4' ); # Same as 3.The default cell selections is (0, 0), 'A1'.
⭐ set_top_left_cell( $row, $col )
This method can be used to set the top leftmost visible cell in the worksheet:
$worksheet->set_top_left_cell( 31, 26 );
# Same as:
$worksheet->set_top_left_cell( 'AA32' );You can also use A1 notation, as shown above, see the note about "Cell notation".
🌸 set_row( $row, $height, $format, $hidden, $level, $collapsed )
This method can be used to change the default properties of a row. All parameters apart from $row are optional.
The most common use for this method is to change the height of a row.
$worksheet->set_row( 0, 20 ); # Row 1 height set to 20Note: the row height is in Excel character units. To set the height in pixels use the set_row_pixels() method, see below.
If you wish to set the format without changing the height you can pass undef as the height parameter:
$worksheet->set_row( 0, undef, $format );The $format parameter will be applied to any cells in the row that don't have a format. For example
$worksheet->set_row( 0, undef, $format1 ); # Set the format for row 1
$worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
$worksheet->write( 'B1', 'Hello', $format2 ); # Keeps $format2If you wish to define a row format in this way you should call the method before any calls to write(). Calling it afterwards will overwrite any format that was previously specified. order matters bestie, don't play yourself.
The $hidden parameter should be set to 1 if you wish to hide a row. This can be used, for example, to hide intermediary steps in a complicated calculation:
$worksheet->set_row( 0, 20, $format, 1 );
$worksheet->set_row( 1, undef, undef, 1 );The $level parameter is used to set the outline level of the row. Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent rows with the same outline level are grouped together into a single outline.
The following example sets an outline level of 1 for rows 2 and 3 (zero-indexed):
$worksheet->set_row( 1, undef, undef, 0, 1 );
$worksheet->set_row( 2, undef, undef, 0, 1 );The $hidden parameter can also be used to hide collapsed outlined rows when used in conjunction with the $level parameter.
$worksheet->set_row( 1, undef, undef, 1, 1 );
$worksheet->set_row( 2, undef, undef, 1, 1 );For collapsed outlines you should also indicate which row has the collapsed + symbol using the optional $collapsed parameter.
$worksheet->set_row( 3, undef, undef, 0, 0, 1 );For a more complete example see the outline.pl and outline_collapsed.pl programs in the examples directory of the distro.
Excel allows up to 7 outline levels. Therefore the $level parameter should be in the range 0 <= $level <= 7.
🧚 set_row_pixels( $row, $height, $format, $hidden, $level, $collapsed )
This method is the same as set_row() except that $height is in pixels.
$worksheet->set_row ( 0, 24 ); # Set row height in character units
$worksheet->set_row_pixels( 1, 18 ); # Set row to same height in pixels💜 set_column( $first_col, $last_col, $width, $format, $hidden, $level, $collapsed )
This method can be used to change the default properties of a single column or a range of columns. All parameters apart from $first_col and $last_col are optional.
If set_column() is applied to a single column the value of $first_col and $last_col should be the same. In the case where $last_col is zero it is set to the same value as $first_col.
It is also possible, and generally clearer, to specify a column range using the form of A1 notation used for columns. See the note about "Cell notation".
Examples:
$worksheet->set_column( 0, 0, 20 ); # Column A width set to 20
$worksheet->set_column( 1, 3, 30 ); # Columns B-D width set to 30
$worksheet->set_column( 'E:E', 20 ); # Column E width set to 20
$worksheet->set_column( 'F:H', 30 ); # Columns F-H width set to 30Okay so the $width is literally the same column width number Excel itself uses. It's approximately equal to how long a string is in Excel's default font, which is Calibri 11 — so like, that's the unit bestie, that's the vibe. Want pixels instead of vibes-units? Use the set_column_pixels() method, it's down below ⬇️.
Also go peek at the autofit() method if you want the column widths to come from the actual data in the column. Approximately. 🦋
And yeah, $format is optional like always — for the whole scoop see "CELL FORMATTING". But listen: if you wanna set the format without touching the width, just pass undef as the width parameter 💅
$worksheet->set_column( 0, 0, undef, $format );That $format gets applied to any cells in the column that don't have a format of their own. Watch:
$worksheet->set_column( 'A:A', undef, $format1 ); # Set format for col 1
$worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
$worksheet->write( 'A2', 'Hello', $format2 ); # Keeps $format2⚠️ IMPORTANT: if you're defining a column format this way, you have to call this method before any calls to write(). Call it after and it does nothing. Zero. Zilch. It's giving ghosted 👻
Also a default row format beats a default column format. Row format said main character energy:
$worksheet->set_row( 0, undef, $format1 ); # Set format for row 1
$worksheet->set_column( 'A:A', undef, $format2 ); # Set format for col 1
$worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
$worksheet->write( 'A2', 'Hello' ); # Defaults to $format2Now the $hidden parameter — set it to 1 if you wanna hide a column 🫣. Super useful for hiding the intermediary steps of a complicated calculation, y'know, the ✨behind the scenes✨ math:
$worksheet->set_column( 'D:D', 20, $format, 1 );
$worksheet->set_column( 'E:E', undef, undef, 1 );The $level parameter sets the outline level of the column. Outlines get the full explanation in "OUTLINES AND GROUPING IN EXCEL". Adjacent columns with the same outline level get grouped together into a single outline. Cute, they're matching 🎀
This sets outline level 1 for columns B to G:
$worksheet->set_column( 'B:G', undef, undef, 0, 1 );And $hidden can also hide collapsed outlined columns when you use it together with $level:
$worksheet->set_column( 'B:G', undef, undef, 1, 1 );For collapsed outlines you should also say which row has the collapsed "+" symbol, using the optional $collapsed parameter:
$worksheet->set_column( 'H:H', undef, undef, 0, 0, 1 );Want the whole picture? Go look at the outline.pl and outline_collapsed.pl programs in the examples directory of the distro 💖
Heads up: Excel allows up to 7 outline levels, so $level has to live in the range 0 <= $level <= 7. No going over, bestie, Excel will not be impressed 😭
set_column_pixels( $first_col, $last_col, $width, $format, $hidden, $level, $collapsed )
Literally the same as set_column() except $width is in pixels instead of character units. That's it, that's the whole twist ✨
$worksheet->set_column ( 0, 0, 10 ); # Column A width set to 10 in character units
$worksheet->set_column_pixels( 1, 1, 75 ); # Column B set to the same width in pixelsset_default_row( $height, $hide_unused_rows )
This one sets the limited number of default row properties Excel actually allows. Which is: the default height, and the option to hide unused rows 🌸
$worksheet->set_default_row( 24 ); # Set the default row height to 24.So the hide-unused-rows thing? Excel uses it as an optimisation so you can hide a huge pile of rows without generating a giant file that has an entry for every single hidden row. Iconic efficiency honestly:
$worksheet->set_default_row( undef, 1 );Peep the hide_row_col.pl example program for the full look 👑
autofit($max_width)
Autofits the worksheet column widths to the widest data in the column. Approximately 🌈
$worksheet->autofit();Okay so here's the tea: Excel autofits columns at runtime, because it has access to all the required worksheet info AND the Windows functions for calculating display areas based on fonts and formatting. Excel::Writer::XLSX does not have those Windows functions. So it simulates autofit by calculating string widths from metrics taken from Excel. It's not perfect, but for most cases it should be enough and indistinguishable from actual Excel output. However — limitations, bestie, be aware:
- It's based on the default Excel font type and size of Calibri 11. Other fonts or font sizes = inaccurate results, no cap.
- It doesn't take formatting of numbers or dates into account, though this may get addressed in a later version. 🙈
- Autofit is a relatively expensive operation since it does a calculation for all the populated cells in a worksheet. See the note on performance below. 💀
For cases where the output isn't what you wanted, you can set explicit column widths via set_column() or set_column_pixels() — that method ignores columns that have already been explicitly set if the width is greater than the calculated autofit width. Alternatively, setting the column width explicitly after calling autofit() will override the autofit value. And you can also cap it with the optional $max_width parameter, more on that below ⬇️
Excel autofits very long strings up to a limit of 1790 pixels/255 characters. Which is honestly often way too wide to display on one screen at normal zoom 😭. So the optional $max_width parameter exists to give you a smaller upper pixel limit for autofitting long strings. A value of 300 pixels is recommended as a good compromise between column width and readability:
$worksheet->autofit(300);Need even more control over the autofit effect? You can use the Excel::Writer::XLSX::Utility xl_cell_autofit_width() function to calculate the autofit width for a cell value. Same calculation that autofit() uses internally, so it's fr fr consistent 💜
outline_settings( $visible, $symbols_below, $symbols_right, $auto_style )
This controls how outlines look in Excel. Outlines themselves are explained in "OUTLINES AND GROUPING IN EXCEL".
$visible decides whether outlines are visible at all. Set it to 0 and all outlines on the worksheet get hidden. You can unhide them in Excel with the "Show Outline Symbols" command button. Default is 1 = visible 👀
$worksheet->outline_settings( 0 );$symbols_below controls whether the row outline symbol shows up above or below the outline level bar. Default is 1, symbols below the bar.
$symbols_right controls whether the column outline symbol shows up to the left or the right of the outline level bar. Default is 1, symbols to the right.
$auto_style controls whether the automatic outline generator in Excel uses automatic styles when creating an outline. This has no effect on a file generated by "Excel::Writer::XLSX", but it does affect how the worksheet behaves after it's created. Default is 0, meaning "Automatic Styles" are turned off.
And the defaults for all of these match Excel's own default parameters, so it's all aligned ✨
Lowkey the parameters controlled by outline_settings() are rarely used. But now you know. 🦋
freeze_panes( $row, $col, $top_row, $left_col )
This splits a worksheet into horizontal or vertical regions called panes, and then freezes them so the splitter bars aren't visible. Same thing as the "Window->Freeze Panes" menu command in Excel 🧊
$row and $col say where the split goes. But careful: the split is specified at the top or left of a cell, and the method uses zero based indexing. So to freeze the first row of a worksheet you specify the split at row 2 — which is 1 in zero-based indexing. Which might trick you into thinking it's 1-based indexing, but it is not. Do not be fooled bestie 😭
You can set either $row or $col to zero if you don't want a vertical or horizontal split.
Examples:
$worksheet->freeze_panes( 1, 0 ); # Freeze the first row
$worksheet->freeze_panes( 'A2' ); # Same using A1 notation
$worksheet->freeze_panes( 0, 1 ); # Freeze the first column
$worksheet->freeze_panes( 'B1' ); # Same using A1 notation
$worksheet->freeze_panes( 1, 2 ); # Freeze first row and first 2 columns
$worksheet->freeze_panes( 'C2' ); # Same using A1 notation$top_row and $left_col are optional 🫶 they specify the top-most or left-most visible row or column in the scrolling region of the panes. Like, freeze the first row but have the scrolling region start at row twenty:
$worksheet->freeze_panes( 1, 0, 20, 0 );Nope alert: you cannot use A1 notation for$top_rowand$left_col. Numbers only bestie 💅
Also go check the "panes.pl" program in the "examples" directory of the distribution 🌸
split_panes( $y, $x, $top_row, $left_col )
This also splits a worksheet into horizontal or vertical regions called panes. The difference from freeze_panes() is that with this one the splits between the panes are visible to the user, and each pane gets its own scroll bars 👑
$y and $x specify the vertical and horizontal position of the split. The units are the same ones Excel uses for row height and column width — but plot twist, the vertical and horizontal units are different from each other 😵 So you have to specify $y and $x in terms of the row heights and column widths you've actually set, or the default values, which are 15 for a row and 8.43 for a column.
You can set either $y or $x to zero if you don't want a vertical or horizontal split. $top_row and $left_col are optional, and they specify the top-most or left-most visible row or column in the bottom-right pane.
Example:
$worksheet->split_panes( 15, 0, ); # First row
$worksheet->split_panes( 0, 8.43 ); # First column
$worksheet->split_panes( 15, 8.43 ); # First row and columnAlso nope: you cannot use A1 notation with this method either 🚫
See also the freeze_panes() method and the "panes.pl" program in the "examples" directory of the distribution 💖
merge_range( $first_row, $first_col, $last_row, $last_col, $token, $format )
The merge_range() method allows you to merge cells that contain other types of alignment in addition to the merging:
my $format = $workbook->add_format(
border => 6,
valign => 'vcenter',
align => 'center',
);
$worksheet->merge_range( 'B3:D4', 'Vertical and horizontal', $format );okay so merge_range() writes its $token argument using the worksheet write() method ✨ So it'll handle numbers, strings, formulas or urls as required, nbd. But if you need to specify the required "write_*()" method, use the merge_range_type() method instead — peep below 👇
The full possibilities of this method are shown in the "merge3.pl" to "merge6.pl" programs in the "examples" directory of the distribution 📁 (yes, there's a whole little squad of example files, iconic).
merge_range_type( $type, $first_row, $first_col, $last_row, $last_col, ... ) 💅
So the merge_range() method (see above, bestie) uses write() to insert the required data into a merged range. But ngl there may be times where this isn't what you require?? so as an alternative the "merge_range_type ()" method lets you specify the type of data you wish to write. For example:
$worksheet->merge_range_type( 'number', 'B2:C2', 123, $format1 );
$worksheet->merge_range_type( 'string', 'B4:C4', 'foo', $format2 );
$worksheet->merge_range_type( 'formula', 'B6:C6', '=1+2', $format3 );The $type must be one of the following, which corresponds to a "write_*()" method 🎀:
'number'
'string'
'formula'
'array_formula'
'blank'
'rich_string'
'date_time'
'url'Any arguments after the range should be whatever the appropriate method accepts:
$worksheet->merge_range_type( 'rich_string', 'B8:C8',
'This is ', $bold, 'bold', $format4 );⚠️ Note, you must always pass a $format object as an argument, even if it is a default format. No skipping this, fr fr.
set_zoom( $scale ) 🔍
Set the worksheet zoom factor in the range 10 <= $scale <= 400:
$worksheet1->set_zoom( 50 );
$worksheet2->set_zoom( 75 );
$worksheet3->set_zoom( 300 );
$worksheet4->set_zoom( 400 );The default zoom factor is 100. You cannot zoom to "Selection" because it is calculated by Excel at run-time 😤 (computer says no).
💡 Note,set_zoom()does not affect the scale of the printed page. For that you should useset_print_scale(). Different vibes, different methods, periodt.
right_to_left() 🌏
The right_to_left() method is used to change the default direction of the worksheet from left-to-right, with the A1 cell in the top left, to right-to-left, with the A1 cell in the top right 🔁
$worksheet->right_to_left();This is useful when creating Arabic, Hebrew or other near or far eastern worksheets that use right-to-left as the default direction ✨ main character energy for your multilingual spreadsheets, we love to see it.
hide_zero() 🙈
The hide_zero() method is used to hide any zero values that appear in cells:
$worksheet->hide_zero();In Excel this option is found under Tools->Options->View 👀 iykyk.
set_background( $filename ) 🖼️
The set_background() method can be used to set the background image for the worksheet:
$worksheet->set_background( 'logo.png' )The set_background() method supports all the image formats supported by insert_image() 🎨
Some people use this method to add a watermark background to their document. However, Microsoft recommends using a header image to set a watermark <https://support.microsoft.com/en-us/office/add-a-watermark-in-excel-a372182a-d733-484e-825c-18ddf3edf009>. The choice of method depends on whether you want the watermark to be visible in normal viewing mode or just when the file is printed 👀 In Excel::Writer::XLSX you can get the header watermark effect using set_header():
$worksheet->set_header( '&C&G', undef, { image_center => 'watermark.png' } )set_tab_color() 🎨
The set_tab_color() method is used to change the colour of the worksheet tab 🌈 You can use one of the standard colour names provided by the Format object or a Html style "#RRGGBB" colour. See "WORKING WITH COLOURS".
$worksheet1->set_tab_color( 'red' );
$worksheet2->set_tab_color( '#FF6600' );See the "tab_colors.pl" program in the examples directory of the distro 📂 a little rainbow of tabs, obsessed.
autofilter( $first_row, $first_col, $last_row, $last_col ) 🔽
This method allows an autofilter to be added to a worksheet ✨ An autofilter is a way of adding drop down lists to the headers of a 2D range of worksheet data. This allows users to filter the data based on simple criteria so that some data is shown and some is hidden 👻 (the hidden data is lowkey minding its own business).
To add an autofilter to a worksheet:
$worksheet->autofilter( 0, 0, 10, 3 );
$worksheet->autofilter( 'A1:D11' ); # Same as above in A1 notation.Filter conditions can be applied using the filter_column() or filter_column_list() method 💅
See the "autofilter.pl" program in the examples directory of the distro for a more detailed example 📖
filter_column( $column, $expression ) 🕵️
The "filter_column" method can be used to filter columns in a autofilter range based on simple conditions.
⚠️ NOTE: It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. Rows are hidden using theset_row()"visible"parameter."Excel::Writer::XLSX"cannot do this automatically since it isn't part of the file format 😭 See the"autofilter.pl"program in the examples directory of the distro for an example.
The conditions for the filter are specified using simple expressions:
$worksheet->filter_column( 'A', 'x > 2000' );
$worksheet->filter_column( 'B', 'x > 2000 and x < 5000' );The $column parameter can either be a zero indexed column number or a string column name ✨
The following operators are available:
Operator Synonyms
== = eq =~
!= <> ne !=
>
<
>=
<=
and &&
or ||The operator synonyms are just syntactic sugar to make you more comfortable using the expressions 🍬 It is important to remember that the expressions will be interpreted by Excel and not by perl 👑 (so don't come at it with your perl brain, bestie).
An expression can comprise a single statement or two statements separated by the "and" and "or" operators. For example:
'x < 2000'
'x > 2000'
'x == 2000'
'x > 2000 and x < 5000'
'x == 2000 or x == 5000'Filtering of blank or non-blank data can be achieved by using a value of "Blanks" or "NonBlanks" in the expression:
'x == Blanks'
'x == NonBlanks'Excel also allows some simple string matching operations:
'x =~ b*' # begins with b
'x !~ b*' # doesn't begin with b
'x =~ *b' # ends with b
'x !~ *b' # doesn't end with b
'x =~ *b*' # contains b
'x !~ *b*' # doesn't contains bYou can also use "*" to match any character or number and "?" to match any single character or number 🎯 No other regular expression quantifier is supported by Excel's filters. Excel's regular expression characters can be escaped using "~" ✨
The placeholder variable "x" in the above examples can be replaced by any simple string. The actual placeholder name is ignored internally so the following are all equivalent:
'x < 2000'
'col < 2000'
'Price < 2000'Also, note that a filter condition can only be applied to a column in a range specified by the autofilter() Worksheet method 💅 (one column at a time, no overachieving).
See the "autofilter.pl" program in the examples directory of the distro for a more detailed example 📖
📌 NoteSpreadsheet::WriteExcelsupports Top 10 style filters. These aren't currently supported byExcel::Writer::XLSXbut may be added later. (manifesting it for u 🕯️)
filter_column_list( $column, @matches ) 📋
Prior to Excel 2007 it was only possible to have either 1 or 2 filter conditions such as the ones shown above in the "filter_column" method 💀 (that's right, only 1 or 2, the struggle was real).
Excel 2007 introduced a new list style filter where it is possible to specify 1 or more 'or' style criteria ✨ For example if your column contained data for the first six months the initial data would be displayed as all selected as shown on the left. Then if you selected 'March', 'April' and 'May' they would be displayed as shown on the right 👇
No criteria selected Some criteria selected.
[/] (Select all) [X] (Select all)
[/] January [ ] January
[/] February [ ] February
[/] March [/] March
[/] April [/] April
[/] May [/] May
[/] June [ ] JuneThe filter_column_list() method can be used to represent these types of filters:
$worksheet->filter_column_list( 'A', 'March', 'April', 'May' );The $column parameter can either be a zero indexed column number or a string column name ✨
One or more criteria can be selected:
$worksheet->filter_column_list( 0, 'March' );
$worksheet->filter_column_list( 1, 100, 110, 120, 130 );⚠️ NOTE: It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. Rows are hidden using theset_row()"visible"parameter."Excel::Writer::XLSX"cannot do this automatically since it isn't part of the file format 😭 See the"autofilter.pl"program in the examples directory of the distro for an example.
convert_date_time( $date_string ) ⏰
The convert_date_time() method is used internally by the write_date_time() method to convert date strings to a number that represents an Excel date and time 📅
It is exposed as a public method for utility purposes 🛠️ (you're welcome).
The $date_string format is detailed in the write_date_time() method 📝
set_vba_name() 🧙
The Worksheet set_vba_name() method can be used to set the VBA codename for the worksheet (there is a similar method for the workbook VBA name) ✨ This is sometimes required when a "vbaProject" macro included via add_vba_project() refers to the worksheet. The default Excel VBA name of "Sheet1", etc., is used if a user defined name isn't specified 👑
See also "WORKING WITH VBA MACROS" 🔮
ignore_errors() 🚦
The ignore_errors() method can be used to ignore various worksheet cell errors/warnings ⚠️ For example the following code writes a string that looks like a number:
$worksheet->write_string('D2', '123');This causes Excel to display a small green triangle in the top left hand corner of the cell to indicate an error/warning 🟢📐 (the little green triangle of judgment, we all know her).
Sometimes these warnings are useful indicators that there is an issue in the spreadsheet but sometimes it is preferable to turn them off 🙈 Warnings can be turned off at the Excel level for all workbooks and worksheets by using the using "Excel options -> Formulas -> Error checking rules" ✨ Alternatively you can turn them off for individual cells in a worksheet, or ranges of cells, using the ignore_errors() method with a hashref of options and ranges like this:
$worksheet->ignore_errors({number_stored_as_text => 'A1:H50'});
# Or for more than one option:
$worksheet->ignore_errors({number_stored_as_text => 'A1:H50',
eval_error => 'A1:H50'});and your range can be literally anything bestie — one single cell, a whole range, or a bunch of cells and ranges all smooshed together with spaces 💅:
# Single cell.
$worksheet->ignore_errors({eval_error => 'C6'});
# Or a single range:
$worksheet->ignore_errors({eval_error => 'C6:G8'});
# Or multiple cells and ranges:
$worksheet->ignore_errors({eval_error => 'C6 E6 G1:G20 J2:J6'});⚠️ Note: calling ignore_errors more than once overwrites whatever you set before. It does not stack. It does not add. It's a fresh start every time, no cap. 😭
bestie you can also silence a whole entire column by giving the range from the first cell of that column all the way down to the last cell:
$worksheet->ignore_errors({number_stored_as_text => 'A1:A1048576'});or, if you're feeling extra extra, do the WHOLE worksheet by going from the very first cell to the very last cell in the sheet 👑:
$worksheet->ignore_errors({number_stored_as_text => 'A1:XFD1048576'});and here's every error/warning you're allowed to shut up ✨:
number_stored_as_text— kill the errors/warnings for numbers stores as text.eval_error— kill the errors/warnings for formula errors (like dividing by zero, we've all been there 🙈).formula_differs— kill the errors/warnings for formulas that differ from the surrounding formulas.formula_range— kill the errors/warnings for formulas that omit cells in a range.formula_unlocked— kill the errors/warnings for unlocked cells that contain formulas.empty_cell_reference— kill the errors/warnings for formulas that refer to empty cells.list_data_validation— kill the errors/warnings for cells in a table that do not comply with applicable data validation rules.calculated_column— kill the errors/warnings for cell formulas that differ from the column formula.two_digit_text_year— kill the errors/warnings for formulas that contain a two digit text representation of a year.
🎀 PAGE SET-UP METHODS 🎀
okay so these methods? they're the glow-up methods 💖 they control how your worksheet actually looks when it gets printed. We're talking page headers, footers, margins — the whole aesthetic. Technically they're just regular worksheet methods, but they live in their own lil section here because honestly, clarity slays.
here's the full roster of page set-up methods:
set_landscape()
set_portrait()
set_page_view()
set_pagebreak_view()
set_paper()
center_horizontally()
center_vertically()
set_margins()
set_header()
set_footer()
repeat_rows()
repeat_columns()
hide_gridlines()
print_row_col_headers()
print_area()
print_across()
fit_to_pages()
set_start_page()
set_print_scale()
print_black_and_white()
set_h_pagebreaks()
set_v_pagebreaks()and here's a super common thing you'll wanna do with Excel::Writer::XLSX — applying the same page set-up to every single worksheet in the workbook. to do that, use the sheets() method from the workbook class to grab the array of worksheets 🌈:
for $worksheet ( $workbook->sheets() ) {
$worksheet->set_landscape();
}set_landscape()
this one sets the orientation of your worksheet's printed page to landscape 🖼️:
$worksheet->set_landscape(); # Landscape modeset_portrait()
this one sets the orientation of your worksheet's printed page to portrait. and fun fact: portrait is already the default, so honestly you probably won't ever need to call this one 💅:
$worksheet->set_portrait(); # Portrait modeset_page_view()
this one displays the worksheet in "Page View/Layout" mode 👀:
$worksheet->set_page_view();set_pagebreak_view()
this one displays the worksheet in "Page Break Preview" mode:
$worksheet->set_pagebreak_view();set_paper( $index )
this one sets the paper format for the printed output of a worksheet 🖨️. here's every paper style on offer:
Index Paper format Paper size
===== ============ ==========
0 Printer default -
1 Letter 8 1/2 x 11 in
2 Letter Small 8 1/2 x 11 in
3 Tabloid 11 x 17 in
4 Ledger 17 x 11 in
5 Legal 8 1/2 x 14 in
6 Statement 5 1/2 x 8 1/2 in
7 Executive 7 1/4 x 10 1/2 in
8 A3 297 x 420 mm
9 A4 210 x 297 mm
10 A4 Small 210 x 297 mm
11 A5 148 x 210 mm
12 B4 250 x 354 mm
13 B5 182 x 257 mm
14 Folio 8 1/2 x 13 in
15 Quarto 215 x 275 mm
16 - 10x14 in
17 - 11x17 in
18 Note 8 1/2 x 11 in
19 Envelope 9 3 7/8 x 8 7/8
20 Envelope 10 4 1/8 x 9 1/2
21 Envelope 11 4 1/2 x 10 3/8
22 Envelope 12 4 3/4 x 11
23 Envelope 14 5 x 11 1/2
24 C size sheet -
25 D size sheet -
26 E size sheet -
27 Envelope DL 110 x 220 mm
28 Envelope C3 324 x 458 mm
29 Envelope C4 229 x 324 mm
30 Envelope C5 162 x 229 mm
31 Envelope C6 114 x 162 mm
32 Envelope C65 114 x 229 mm
33 Envelope B4 250 x 353 mm
34 Envelope B5 176 x 250 mm
35 Envelope B6 176 x 125 mm
36 Envelope 110 x 230 mm
37 Monarch 3.875 x 7.5 in
38 Envelope 3 5/8 x 6 1/2 in
39 Fanfold 14 7/8 x 11 in
40 German Std Fanfold 8 1/2 x 12 in
41 German Legal Fanfold 8 1/2 x 13 in⚠️ real talk: it's likely that not all of these paper types will be available to the end user, because it depends entirely on what paper formats their printer actually supports 😬. so the move is to stick to standard paper types, bestie. don't be out here picking index 41 and acting surprised.
$worksheet->set_paper( 1 ); # US Letter
$worksheet->set_paper( 9 ); # A4and if you don't specify a paper type at all? the worksheet just prints using the printer's default paper. simple as that ✨
center_horizontally()
centers your worksheet data horizontally between the margins on the printed page 🎯:
$worksheet->center_horizontally();center_vertically()
centers your worksheet data vertically between the margins on the printed page:
$worksheet->center_vertically();set_margins( $inches )
there are a whole bunch of methods for setting worksheet margins on the printed page, and here they all are 🌸:
set_margins() # Set all margins to the same value
set_margins_LR() # Set left and right margins to the same value
set_margins_TB() # Set top and bottom margins to the same value
set_margin_left(); # Set left margin
set_margin_right(); # Set right margin
set_margin_top(); # Set top margin
set_margin_bottom(); # Set bottom marginevery single one of these takes a distance in inches as its parameter. 📏 and btw: 1 inch = 25.4mm. ";-)"
the default left and right margin is 0.7 inch. the default top and bottom margin is 0.75 inch.
⚠️ heads up: these defaults are different from the defaults used in the binary file format by Spreadsheet::WriteExcel. so if you're porting stuff over, that's the vibe check you needed 🫶
set_header( $string, $margin )
headers and footers get generated using a $string, which is a combo of plain text and control characters 🎨. the $margin parameter is optional.
here are the available control character:
Control Category Description
======= ======== ===========
&L Justification Left
&C Center
&R Right
&P Information Page number
&N Total number of pages
&D Date
&T Time
&F File name
&A Worksheet name
&Z Workbook path
&fontsize Font Font size
&"font,style" Font name and style
&U Single underline
&E Double underline
&S Strikethrough
&X Superscript
&Y Subscript
&[Picture] Images Image placeholder
&G Same as &[Picture]
&& Miscellaneous Literal ampersand &so the text in your headers and footers can be justified (aligned) left, center or right by prefixing it with the control characters &L, &C and &R 👑
for example (with an ASCII art representation of the results, we love a visual 💅):
$worksheet->set_header('&LHello');
---------------------------------------------------------------
| |
| Hello |
| |
$worksheet->set_header('&CHello');
---------------------------------------------------------------
| |
| Hello |
| |
$worksheet->set_header('&RHello');
---------------------------------------------------------------
| |
| Hello |
| |and for simple text, if you don't specify any justification at all, the text gets centred by default. BUT — and this is the important bit — you must prefix the text with &C if you specify a font name or any other formatting:
$worksheet->set_header('Hello'); ---------------------------------------------------------------
| |
| Hello |
| |You can also drop text into each of the justification regions if you're feeling fancy:
$worksheet->set_header('&LCiao&CBello&RCielo'); ---------------------------------------------------------------
| |
| Ciao Bello Cielo |
| |The information control characters are basically little variables that Excel updates for you whenever the workbook or worksheet changes. Times and dates come out in whatever the user's default format is, so it's giving personalised:
$worksheet->set_header('&CPage &P of &N'); ---------------------------------------------------------------
| |
| Page 1 of 6 |
| |$worksheet->set_header('&CUpdated at &T'); ---------------------------------------------------------------
| |
| Updated at 12:30 PM |
| |✨ Images can be inserted too, using the options shown below. Every image needs a placeholder in the header string using the &[Picture] or &G control characters:
$worksheet->set_header( '&L&G', 0.3, { image_left => 'logo.jpg' });You can set the font size of a section of text by prefixing it with the control character &n where n is the font size:
$worksheet1->set_header( '&C&30Hello Big' );
$worksheet2->set_header( '&C&10Hello Small' );And you can set the font of a section of text by prefixing it with the control sequence &"font,style" where fontname is a font name such as Courier New or Times New Roman and style is one of the standard Windows font descriptions: Regular, Italic, Bold or Bold Italic:
$worksheet1->set_header( '&C&"Courier New,Italic"Hello' );
$worksheet2->set_header( '&C&"Courier New,Bold Italic"Hello' );
$worksheet3->set_header( '&C&"Times New Roman,Regular"Hello' );ngl you can combine all of these features together to create genuinely sophisticated headers and footers. Pro tip for setting up complicated ones: record a page set-up as a macro in Excel and go look at the format strings that VBA produces. BUT — and this is important — remember that VBA uses two double quotes "" to mean a single double quote. So for that last example above, the equivalent VBA code looks like this:
.LeftHeader = ""
.CenterHeader = "&""Times New Roman,Regular""Hello"
.RightHeader = ""To include a single literal ampersand & in a header or footer you gotta use a double ampersand &&:
$worksheet1->set_header('&CCuriouser && Curiouser - Attorneys at Law');As stated above, the margin parameter is optional. Just like the other margins, the value should be in inches. The default header and footer margin is 0.3 inch. Heads up though — this default margin is different from the default used in the binary file format by Spreadsheet::WriteExcel. You can set the header and footer margin size like so:
$worksheet->set_header( '&CHello', 0.75 );The header and footer margins are independent of the top and bottom margins. iykyk.
Here are the available options:
image_left— The path to the image. Requires a&Gor&[Picture]placeholder.image_center— Same as above.image_right— Same as above.scale_with_doc— Scale header with document. Defaults to true.align_with_margins— Align header to margins. Defaults to true.
The image options must have an accompanying &[Picture] or &G control character in the header string:
$worksheet->set_header(
'&L&[Picture]&C&[Picture]&R&[Picture]',
undef, # If you don't want to change the margin.
{
image_left => 'red.jpg',
image_center => 'blue.jpg',
image_right => 'yellow.jpg'
}
);⚠️ Note: the header or footer string must be less than 255 characters. Strings longer than that will not be written and a warning will be generated. No cap, that's a hard limit bestie.
The set_header() method can also handle Unicode strings in UTF-8 format. Obsessed with her:
$worksheet->set_header( "&C\x{263a}" )See, also the headers.pl program in the examples directory of the distribution. Go peek, it's cute. 💖
🌸 set_footer( $string, $margin )
The syntax of the set_footer() method is the same as set_header(), see above. Literally identical vibes, it just goes at the bottom of the page. 🎀
🌸 repeat_rows( $first_row, $last_row )
Set the number of rows to repeat at the top of each printed page.
For big Excel documents it's often desirable to have the first row or rows of the worksheet print out at the top of every page. The repeat_rows() method is how you eat that. The parameters $first_row and $last_row are zero based. The $last_row parameter is optional if you only want to specify one row:
$worksheet1->repeat_rows( 0 ); # Repeat the first row
$worksheet2->repeat_rows( 0, 1 ); # Repeat the first two rows🌸 repeat_columns( $first_col, $last_col )
Set the columns to repeat at the left hand side of each printed page.
Same energy as above: for large Excel documents it's often desirable to have the first column or columns of the worksheet print out at the left hand side of each page. That's what the repeat_columns() method is for. The parameters $first_column and $last_column are zero based. The $last_column parameter is optional if you only want to specify one column. You can also specify the columns using A1 column notation, see the note about Cell notation.
$worksheet1->repeat_columns( 0 ); # Repeat the first column
$worksheet2->repeat_columns( 0, 1 ); # Repeat the first two columns
$worksheet3->repeat_columns( 'A:A' ); # Repeat the first column
$worksheet4->repeat_columns( 'A:B' ); # Repeat the first two columns🌸 hide_gridlines( $option )
This method hides the gridlines on the screen and the printed page. Gridlines are, like, the lines that divide the cells on a worksheet. Screen and printed gridlines are turned on by default in an Excel worksheet. If you've defined your own cell borders you may want to hide the default gridlines — they can be a little bit much when you've already got your own ✨lewk✨ going on.
$worksheet->hide_gridlines();The following values of $option are valid:
0 : Don't hide gridlines
1 : Hide printed gridlines only
2 : Hide screen and printed gridlinesIf you don't supply an argument or use undef the default option is 1, i.e. only the printed gridlines are hidden.
🌸 print_row_col_headers()
Set the option to print the row and column headers on the printed page.
An Excel worksheet looks something like the following;
------------------------------------------
| | A | B | C | D | ...
------------------------------------------
| 1 | | | | | ...
| 2 | | | | | ...
| 3 | | | | | ...
| 4 | | | | | ...
|...| ... | ... | ... | ... | ...The headers are the letters and numbers at the top and the left of the worksheet. Since these headers serve mainly as an indication of position on the worksheet, they generally don't appear on the printed page. But if you do want them printed you can use the print_row_col_headers() method:
$worksheet->print_row_col_headers();🫶 Do not confuse these headers with page headers as described in the set_header() section above. Different thing entirely, bestie.
🌸 hide_row_col_headers()
Similar to print_row_col_headers() above, but sets the option to hide the row and column headers within Excel so that they aren't visible to the user:
$worksheet->hide_row_col_headers();🌸 print_area( $first_row, $first_col, $last_row, $last_col )
This method specifies the area of the worksheet that will be printed. All four parameters must be specified. You can also use A1 notation, see the note about Cell notation.
$worksheet1->print_area( 'A1:H20' ); # Cells A1 to H20
$worksheet2->print_area( 0, 0, 19, 7 ); # The same
$worksheet2->print_area( 'A:H' ); # Columns A to H if rows have data🌸 print_across()
The print_across method changes the default print direction. Excel calls this the sheet "page order".
$worksheet->print_across();The default page order is shown below for a worksheet that extends over 4 pages. The order is called "down then across":
[1] [3]
[2] [4]But if you use the print_across method, the print order changes to "across then down":
[1] [2]
[3] [4]🌸 fit_to_pages( $width, $height )
The fit_to_pages() method fits the printed area to a specific number of pages both vertically and horizontally. If the printed area exceeds the number of pages you specified, it gets scaled down to fit. This guarantees that the printed area will always appear on the specified number of pages even if the page size or margins change. Iconic, honestly.
$worksheet1->fit_to_pages( 1, 1 ); # Fit to 1x1 pages
$worksheet2->fit_to_pages( 2, 1 ); # Fit to 2x1 pages
$worksheet3->fit_to_pages( 1, 2 ); # Fit to 1x2 pagesThe print area can be defined using the print_area() method as described above.
A common requirement is to fit the printed output to n pages wide but have the height be as long as necessary. To achieve this, set the $height to zero:
$worksheet1->fit_to_pages( 1, 0 ); # 1 page wide and as long as necessary⚠️ Note that although it's valid to use bothfit_to_pages()andset_print_scale()on the same worksheet, only one of these options can be active at a time. The last method call made will set the active option. Last one to the party wins fr fr.
⚠️ Note that fit_to_pages() will override any manual page breaks that are defined in the worksheet. 💀
⚠️ Note: when usingfit_to_pages()it may also be required to set the printer paper size usingset_paper()or else Excel will default toUS Letter.
🌸 set_start_page( $start_page )
The set_start_page() method sets the number of the starting page when the worksheet is printed out. The default value is 1.
$worksheet->set_start_page( 2 );🌸 set_print_scale( $scale )
Set the scale factor of the printed page. Scale factors in the range 10 <= $scale <= 400 are valid:
$worksheet1->set_print_scale( 50 );
$worksheet2->set_print_scale( 75 );
$worksheet3->set_print_scale( 300 );
$worksheet4->set_print_scale( 400 );The default scale factor is 100. But bestie, listen: set_print_scale() does not change the scale of the page you actually see in Excel. For that you want set_zoom(). Different thing entirely, ok? 💅
Also, quick heads up — you can technically use both fit_to_pages() and set_print_scale() on the same worksheet, but only one of them is active at a time. Whichever method you called last? That's the one that's serving. Periodt. 👑
print_black_and_white()
Sets the option to print the worksheet in black and white:
$worksheet->print_black_and_white();Monochrome era, we love a minimalist moment. 🖤
set_h_pagebreaks( @breaks )
Adds horizontal page breaks to a worksheet. A page break makes everything after it print on the next page. Horizontal ones act between rows. So if you wanna break between rows 20 and 21, you specify the break at row 21 — but in zero-index notation that's actually row 20. Which means you can just pretend, for a tiny little while, that you're living in 1-index notation:
$worksheet1->set_h_pagebreaks( 20 ); # Break between row 20 and 21It accepts a whole list of page breaks, and you can call it more than once:
$worksheet2->set_h_pagebreaks( 20, 40, 60, 80, 100 ); # Add breaks
$worksheet2->set_h_pagebreaks( 120, 140, 160, 180, 200 ); # Add some more⚠️ Note: if you specify the "fit to page" option via fit_to_pages() it will override all manual page breaks. Your pretty little breaks? Gone. Swept.
Also there's a silent limitation of about 1000 horizontal page breaks per worksheet, matching an Excel internal limitation. Silent as in it just won't tell you, bestie, so don't push it. 🙈
set_v_pagebreaks( @breaks )
Adds vertical page breaks to a worksheet. A page break makes everything after it print on the next page. Vertical ones act between columns. So for a break between columns 20 and 21, you specify the break at column 21 — which in zero-index notation is actually column 20. Again: pretend you're in 1-index notation, just for a sec:
$worksheet1->set_v_pagebreaks(20); # Break between column 20 and 21It takes a list of page breaks too, and you can call it repeatedly:
$worksheet2->set_v_pagebreaks( 20, 40, 60, 80, 100 ); # Add breaks
$worksheet2->set_v_pagebreaks( 120, 140, 160, 180, 200 ); # Add some more⚠️ Note: if you specify the "fit to page" option via fit_to_pages() it will override all manual page breaks. Same energy as before. It simply does not care about your plans.
🎨 CELL FORMATTING 🎨
Okay bestie, this section is the glow-up section. It covers all the methods and properties you can use to format cells in Excel. The stuff a cell can be formatted with includes: fonts, colours, patterns, borders, alignment and number formatting. Basically ✨everything✨.
Creating and using a Format object
Cell formatting is defined through a Format object. You make one by calling the workbook's add_format() method, like this:
my $format1 = $workbook->add_format(); # Set properties later
my $format2 = $workbook->add_format( %props ); # Set at creationThe Format object holds all the formatting properties you can apply to a cell, a row or a column. Setting those properties is the next section's whole storyline.
Once a Format object is built and its properties are set, you can pass it as an argument to the worksheet write methods, like so:
$worksheet->write( 0, 0, 'One', $format );
$worksheet->write_string( 1, 0, 'Two', $format );
$worksheet->write_number( 2, 0, 3, $format );
$worksheet->write_blank( 3, 0, $format );Formats also get passed to the worksheet set_row() and set_column() methods, to define the default properties for a whole row or column:
$worksheet->set_row( 0, 15, $format );
$worksheet->set_column( 0, 0, 15, $format );Format methods and Format properties
So here's the tea: the Excel format categories, the formatting properties you can apply, and the equivalent object method. Every single one, no skips. 💖
Font
- Font type — property
font— methodset_font() - Font size — property
size— methodset_size() - Font color — property
color— methodset_color() - Bold — property
bold— methodset_bold() - Italic — property
italic— methodset_italic() - Underline — property
underline— methodset_underline() - Strikeout — property
font_strikeout— methodset_font_strikeout() - Super/Subscript — property
font_script— methodset_font_script() - Outline — property
font_outline— methodset_font_outline() - Shadow — property
font_shadow— methodset_font_shadow()
Number
- Numeric format — property
num_format— methodset_num_format()
Protection
- Lock cells — property
locked— methodset_locked() - Hide formulas — property
hidden— methodset_hidden()
Alignment
- Horizontal align — property
align— methodset_align() - Vertical align — property
valign— methodset_align() - Rotation — property
rotation— methodset_rotation() - Text wrap — property
text_wrap— methodset_text_wrap() - Justify last — property
text_justlast— methodset_text_justlast() - Center across — property
center_across— methodset_center_across() - Indentation — property
indent— methodset_indent() - Shrink to fit — property
shrink— methodset_shrink()
Pattern
- Cell pattern — property
pattern— methodset_pattern() - Background color — property
bg_color— methodset_bg_color() - Foreground color — property
fg_color— methodset_fg_color()
Border
- Cell border — property
border— methodset_border() - Bottom border — property
bottom— methodset_bottom() - Top border — property
top— methodset_top() - Left border — property
left— methodset_left() - Right border — property
right— methodset_right() - Border color — property
border_color— methodset_border_color() - Bottom color — property
bottom_color— methodset_bottom_color() - Top color — property
top_color— methodset_top_color() - Left color — property
left_color— methodset_left_color() - Right color — property
right_color— methodset_right_color() - Diagonal type — property
diag_type— methodset_diag_type() - Diagonal border — property
diag_border— methodset_diag_border() - Diagonal color — property
diag_color— methodset_diag_color()
There are two ways to set Format properties: through the object method interface, or by setting the property directly. Typical method-interface use looks like this:
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );Meanwhile, you can set the properties directly by passing a hash of properties to the Format constructor:
my $format = $workbook->add_format( bold => 1, color => 'red' );...or after the Format's been constructed, using the set_format_properties() method:
my $format = $workbook->add_format();
$format->set_format_properties( bold => 1, color => 'red' );You can also stash the properties in one or more named hashes and pass them to whichever method needs them:
my %font = (
font => 'Calibri',
size => 12,
color => 'blue',
bold => 1,
);
my %shading = (
bg_color => 'green',
pattern => 1,
);
my $format1 = $workbook->add_format( %font ); # Font only
my $format2 = $workbook->add_format( %font, %shading ); # Font and shadingNow, having two ways to do this might have you going "okay but which one is the way?" 👀 The method mechanism might be better if you prefer setting properties via method calls — which is what the author did when the code was first written. Otherwise, passing properties to the constructor has turned out to be a little more flexible and self-documenting in practice. And an extra little bonus of working with property hashes: it lets you share formatting between workbook objects, exactly like the example above. Iconic, honestly.
The Perl/Tk style of adding properties is supported too:
my %font = (
-font => 'Calibri',
-size => 12,
-color => 'blue',
-bold => 1,
);Working with formats
The default format is Calibri 11 with all other properties off. Basic. Clean. A blank canvas. 🎀
Every unique format in Excel::Writer::XLSX needs its own corresponding Format object. You cannot use a Format with a write() method and then redefine that Format for later use, bestie. Why? Because a Format gets applied to a cell not in its current state, but in its final state. Consider this:
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$worksheet->write( 'A1', 'Cell A1', $format );
$format->set_color( 'green' );
$worksheet->write( 'B1', 'Cell B1', $format );Cell A1 gets the Format $format, which is initially set to red. But then the colour gets changed to green afterwards. When Excel shows Cell A1, it shows the final state of the Format — which in this case is green. Plot twist, but make it predictable. 💀
Generally speaking, a method call without an argument will turn a property on, like:
my $format1 = $workbook->add_format();
$format1->set_bold(); # Turns bold on
$format1->set_bold( 1 ); # Also turns bold on
$format1->set_bold( 0 ); # Turns bold off💅 FORMAT METHODS 💅
The Format object methods get described in more detail in the following sections. Also! There's a Perl program called formats.pl in the examples directory of the WriteExcel distribution. That program creates an Excel workbook called formats.xlsx, which contains examples of almost all the format types. Go look, it's giving reference material. 📚✨
The following Format methods are available:
set_font()set_size()set_color()set_bold()set_italic()set_underline()set_font_strikeout()set_font_script()set_font_outline()set_font_shadow()set_num_format()set_locked()set_hidden()set_align()set_rotation()set_text_wrap()set_text_justlast()set_center_across()set_indent()set_shrink()set_pattern()set_bg_color()set_fg_color()set_border()set_bottom()set_top()set_left()set_right()set_border_color()set_bottom_color()set_top_color()set_left_color()set_right_color()set_diag_type()set_diag_border()set_diag_color()
okay bestie, plot twist: all of those above can also be handed over as plain properties 💅 like, $format->set_bold() is literally the same thing as $workbook->add_format(bold => 1). Same energy, different font. ✨
🌸 set_format_properties( %properties ) 🌸
If you've already got a Format object and you wanna go back and tweak its properties, set_format_properties() has you covered:
my $format = $workbook->add_format();
$format->set_format_properties( bold => 1, color => 'red' );But ngl, this one's here mainly for legacy reasons. The preferable glow-up is to set everything right in the format constructor:
my $format = $workbook->add_format( bold => 1, color => 'red' );🎀 set_font( $fontname ) 🎀
- Default state: Font is
Calibri - Default action: None
- Valid args: Any valid font name
Pick which font you're using, periodt:
$format->set_font('Times New Roman');Real talk though: Excel can only display fonts that are installed on the system that it's running on. So fr fr, stick to the fonts that come as standard — think 'Calibri', 'Times New Roman' and 'Courier New'. Also go peek at the Fonts worksheet created by formats.pl. 🦋
🌈 set_size() 🌈
- Default state: Font size is
10 - Default action: Set font size to
1 - Valid args: Integer values from
1to as big as your screen. 👀
Set the font size, bestie. Excel adjusts the height of a row to accommodate the largest font size in the row. And if you wanna be extra, you can also explicitly specify the height of a row using the set_row() worksheet method. 💅
my $format = $workbook->add_format();
$format->set_size( 30 );💖 set_color() 💖
- Default state: Excels default color, usually black
- Default action: Set the default color
- Valid args: Html RGB strings like
"#FF0000"or the following shortcut strings:'black''blue''brown''cyan''gray''green''lime''magenta''navy''orange''pink''purple''red''silver''white''yellow'
Set the font colour, the moment we've all been waiting for. 🎨 The set_color() method goes like this:
my $format = $workbook->add_format();
$format->set_color( 'red' );
$worksheet->write( 0, 0, 'wheelbarrow', $format );Note: Theset_color()method is used to set the colour of the font in a cell. To set the colour of a cell use theset_bg_color()andset_pattern()methods. Don't mix them up, iykyk. 🙈
For additional examples see the 'Named colors' and 'Standard colors' worksheets created by formats.pl in the examples directory.
See also ["WORKING WITH COLOURS"](#working-with-colours).
⭐ set_bold() ⭐
- Default state: bold is off
- Default action: Turn bold on
- Valid args:
0,1
Set the bold property of the font:
$format->set_bold(); # Turn bold on🦋 set_italic() 🦋
- Default state: Italic is off
- Default action: Turn italic on
- Valid args:
0,1
Set the italic property of the font:
$format->set_italic(); # Turn italic on🎀 set_underline() 🎀
- Default state: Underline is off
- Default action: Turn on single underline
- Valid args:
0= No underline1= Single underline2= Double underline33= Single accounting underline34= Double accounting underline
Set the underline property of the font.
$format->set_underline(); # Single underline✨ set_font_strikeout() ✨
- Default state: Strikeout is off
- Default action: Turn strikeout on
- Valid args:
0,1
Set the strikeout property of the font. 💀 (affectionate)
💜 set_font_script() 💜
- Default state: Super/Subscript is off
- Default action: Turn Superscript on
- Valid args:
0= Normal1= Superscript2= Subscript
Set the superscript/subscript property of the font.
🌸 set_font_outline() 🌸
- Default state: Outline is off
- Default action: Turn outline on
- Valid args:
0,1
Macintosh only.
🌈 set_font_shadow() 🌈
- Default state: Shadow is off
- Default action: Turn shadow on
- Valid args:
0,1
Macintosh only.
👑 set_num_format() 👑 — the main character of this section, ngl
- Default state: General format
- Default action: Format index
1 - Valid args: See the following table
This method is used to define the numerical format of a number in Excel. It controls whether a number is displayed as an integer, a floating point number, a date, a currency value or some other user defined format. So much power in one little method. 🔥
The numerical format of a cell can be specified by using a format string or an index to one of Excel's built-in formats:
my $format1 = $workbook->add_format();
my $format2 = $workbook->add_format();
$format1->set_num_format( 'd mmm yyyy' ); # Format string
$format2->set_num_format( 0x0f ); # Format index
$worksheet->write( 0, 0, 36892.521, $format1 ); # 1 Jan 2001
$worksheet->write( 0, 0, 36892.521, $format2 ); # 1-Jan-01Using format strings you can define very sophisticated formatting of numbers. Like, look at all this:
$format01->set_num_format( '0.000' );
$worksheet->write( 0, 0, 3.1415926, $format01 ); # 3.142
$format02->set_num_format( '#,##0' );
$worksheet->write( 1, 0, 1234.56, $format02 ); # 1,235
$format03->set_num_format( '#,##0.00' );
$worksheet->write( 2, 0, 1234.56, $format03 ); # 1,234.56
$format04->set_num_format( '$0.00' );
$worksheet->write( 3, 0, 49.99, $format04 ); # $49.99
# Note you can use other currency symbols such as the pound or yen as well.
# Other currencies may require the use of Unicode.
$format07->set_num_format( 'mm/dd/yy' );
$worksheet->write( 6, 0, 36892.521, $format07 ); # 01/01/01
$format08->set_num_format( 'mmm d yyyy' );
$worksheet->write( 7, 0, 36892.521, $format08 ); # Jan 1 2001
$format09->set_num_format( 'd mmmm yyyy' );
$worksheet->write( 8, 0, 36892.521, $format09 ); # 1 January 2001
$format10->set_num_format( 'dd/mm/yyyy hh:mm AM/PM' );
$worksheet->write( 9, 0, 36892.521, $format10 ); # 01/01/2001 12:30 PM
$format11->set_num_format( '0 "dollar and" .00 "cents"' );
$worksheet->write( 10, 0, 1.87, $format11 ); # 1 dollar and .87 cents
# Conditional numerical formatting.
$format12->set_num_format( '[Green]General;[Red]-General;General' );
$worksheet->write( 11, 0, 123, $format12 ); # > 0 Green
$worksheet->write( 12, 0, -45, $format12 ); # < 0 Red
$worksheet->write( 13, 0, 0, $format12 ); # = 0 Default colour
# Zip code
$format13->set_num_format( '00000' );
$worksheet->write( 14, 0, '01209', $format13 );The number system used for dates is described in "DATES AND TIME IN EXCEL".
The colour format should have one of the following values:
[Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]Alternatively you can specify the colour based on a colour index as follows: "[Color n]", where n is a standard Excel colour index - 7. See the 'Standard colors' worksheet created by formats.pl.
For more information refer to the documentation on formatting in the "docs" directory of the Excel::Writer::XLSX distro, the Excel on-line help or <http://office.microsoft.com/en-gb/assistance/HP051995001033.aspx>.
Heads up, bestie: You should ensure that the format string is valid in Excel prior to using it in WriteExcel. No cap. 💅
Excel's built-in formats are shown in the following table:
Index Index Format String
0 0x00 General
1 0x01 0
2 0x02 0.00
3 0x03 #,##0
4 0x04 #,##0.00
5 0x05 ($#,##0_);($#,##0)
6 0x06 ($#,##0_);[Red]($#,##0)
7 0x07 ($#,##0.00_);($#,##0.00)
8 0x08 ($#,##0.00_);[Red]($#,##0.00)
9 0x09 0%
10 0x0a 0.00%
11 0x0b 0.00E+00
12 0x0c # ?/?
13 0x0d # ??/??
14 0x0e m/d/yy
15 0x0f d-mmm-yy
16 0x10 d-mmm
17 0x11 mmm-yy
18 0x12 h:mm AM/PM
19 0x13 h:mm:ss AM/PM
20 0x14 h:mm
21 0x15 h:mm:ss
22 0x16 m/d/yy h:mm
.. .... ...........
37 0x25 (#,##0_);(#,##0)
38 0x26 (#,##0_);[Red](#,##0)
39 0x27 (#,##0.00_);(#,##0.00)
40 0x28 (#,##0.00_);[Red](#,##0.00)
41 0x29 _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
42 0x2a _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
43 0x2b _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
44 0x2c _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
45 0x2d mm:ss
46 0x2e [h]:mm:ss
47 0x2f mm:ss.0
48 0x30 ##0.0E+0
49 0x31 @For examples of these formatting codes see the 'Numerical formats' worksheet created by formats.pl. See also the number_formats1.html and the number_formats2.html documents in the "docs" directory of the distro. 🫶
Note 1. Numeric formats23to36are not documented by Microsoft and may differ in international versions. 👀
Note 2. The built-in formats are localised according to the locale settings (regional settings on Windows) of the user when opening the file in Excel:
- The dot appears as the defined local decimal separator.
- The comma appears as the defined local digit groups separator.
- The dollar sign appears as the defined local currency symbol. 💵
- The date, time and duration formats appear as the local equivalent date or time format. 📅
set_locked() 🔒
- Default state: Cell locking is on
- Default action: Turn locking on
- Valid args:
0,1
Okay so this one lets you stop people from messing with what's inside a cell. Following Excel's convention, cell locking is turned on by default. BUT — and this is the plot twist — it only actually does anything if the worksheet has been protected, see the worksheet protect() method. 🫣
my $locked = $workbook->add_format();
$locked->set_locked( 1 ); # A non-op
my $unlocked = $workbook->add_format();
$unlocked->set_locked( 0 );
# Enable worksheet protection
$worksheet->protect();
# This cell cannot be edited.
$worksheet->write( 'A1', '=1+2', $locked );
# This cell can be edited.
$worksheet->write( 'A2', '=1+2', $unlocked );Note: This offers weak protection even with a password, see the note in relation to the protect() method. it's giving "vibes of security" not actual security, bestie 💀
set_hidden() 🙈
- Default state: Formula hiding is off
- Default action: Turn hiding on
- Valid args:
0,1
This one hides a formula while still showing its result. The whole point is usually to hide your big complicated calculations from end users who literally only care about the answer. Same deal as locking though: it only has an effect if the worksheet has been protected, see the worksheet protect() method. ✨
my $hidden = $workbook->add_format();
$hidden->set_hidden();
# Enable worksheet protection
$worksheet->protect();
# The formula in this cell isn't visible
$worksheet->write( 'A1', '=1+2', $hidden );Note: This offers weak protection even with a password, see the note in relation to the protect() method. again, weak, don't trust it with your secrets 🎀
set_align() 📐
- Default state: Alignment is off
- Default action: Left alignment
- Valid args:
'left'— Horizontal'center'— Horizontal'right'— Horizontal'fill'— Horizontal'justify'— Horizontal'center_across'— Horizontal'top'— Vertical'vcenter'— Vertical'bottom'— Vertical'vjustify'— Vertical
So this is the one that sets where your text sits horizontally and vertically inside a cell. You can combo them however you like, vertical AND horizontal together, slay. Here's the vibe:
my $format = $workbook->add_format();
$format->set_align( 'center' );
$format->set_align( 'vcenter' );
$worksheet->set_row( 0, 30 );
$worksheet->write( 0, 0, 'X', $format );You can align text across two or more adjacent cells with the "center_across" property. That said, if you want genuine merged cells, it's better to use the merge_range() worksheet method — that's the main character move. 👑
The "vjustify" (vertical justify) option gives you automatic text wrapping in a cell, and the cell height gets adjusted to fit the wrapped text. If you wanna control where the text wraps, use the set_text_wrap() method.
For further examples see the 'Alignment' worksheet created by formats.pl. 💅
set_center_across() ↔️
- Default state: Center across selection is off
- Default action: Turn center across on
- Valid args:
1
You can align text across two or more adjacent cells with the set_center_across() method. It's literally just an alias for the set_align('center_across') method call, no cap.
Only one cell should contain the text, the other cells should be blank:
my $format = $workbook->add_format();
$format->set_center_across();
$worksheet->write( 1, 1, 'Center across selection', $format );
$worksheet->write_blank( 1, 2, $format );See also the "merge1.pl" to "merge6.pl" programs in the "examples" directory and the merge_range() method. 🦋
set_text_wrap() 🎀
- Default state: Text wrap is off
- Default action: Turn text wrap on
- Valid args:
0,1
Here's an example using the text wrap property — the escape character "\n" is how you say "end of line" around here:
my $format = $workbook->add_format();
$format->set_text_wrap();
$worksheet->write( 0, 0, "It's\na bum\nwrap", $format );Excel will adjust the height of the row to accommodate the wrapped text. You can get a similar effect without newlines using the set_align('vjustify') method. See the "textwrap.pl" program in the "examples" directory. 🌸
set_rotation() 🔄
- Default state: Text rotation is off
- Default action: None
- Valid args: Integers in the range
-90to90and270
Set the rotation of the text in a cell. The rotation can be any angle in the range -90 to 90 degrees. tilt it, bestie 📐
my $format = $workbook->add_format();
$format->set_rotation( 30 );
$worksheet->write( 0, 0, 'This text is rotated', $format );The angle 270 is also supported. This indicates text where the letters run from top to bottom. iconic, honestly ⭐
set_indent() ➡️
- Default state: Text indentation is off
- Default action: Indent text 1 level
- Valid args: Positive integers
This method indents your text. The argument, which should be an integer, is taken as the level of indentation:
my $format = $workbook->add_format();
$format->set_indent( 2 );
$worksheet->write( 0, 0, 'This text is indented', $format );Indentation is a horizontal alignment property. It will override any other horizontal properties, but it can be used in conjunction with vertical properties. so it's highkey the boss of the horizontal axis 💅
set_shrink() 🤏
- Default state: Text shrinking is off
- Default action: Turn "shrink to fit" on
- Valid args:
1
This method shrinks your text so it fits in a cell. tiny text, big energy.
my $format = $workbook->add_format();
$format->set_shrink();
$worksheet->write( 0, 0, 'Honey, I shrunk the text!', $format );set_text_justlast() 🌏
- Default state: Justify last is off
- Default action: Turn justify last on
- Valid args:
0,1
Only applies to Far Eastern versions of Excel. that's it, that's the whole tea 🍵
set_pattern() 🎨
- Default state: Pattern is off
- Default action: Solid fill is on
- Valid args:
0..18
Set the background pattern of a cell.
Examples of the available patterns are shown in the 'Patterns' worksheet created by formats.pl. However, it is unlikely that you will ever need anything other than Pattern 1, which is a solid fill of the background color. fr fr, Pattern 1 is the moment. ✨
set_bg_color() 🟢
- Default state: Color is off
- Default action: Solid fill.
- Valid args: See
set_color()
The set_bg_color() method sets the background colour of a pattern. Patterns are defined via the set_pattern() method. If a pattern hasn't been defined then a solid fill pattern is used as the default.
Here's how you set up a solid fill in a cell:
my $format = $workbook->add_format();
$format->set_pattern(); # This is optional when using a solid fill
$format->set_bg_color( 'green' );
$worksheet->write( 'A1', 'Ray', $format );For further examples see the 'Patterns' worksheet created by formats.pl. 🌈
set_fg_color() 🟣
- Default state: Color is off
- Default action: Solid fill.
- Valid args: See
set_color()
The set_fg_color() method sets the foreground colour of a pattern.
For further examples see the 'Patterns' worksheet created by formats.pl. 🎀
set_border() 🖼️
- Also applies to:
set_bottom()set_top()set_left()set_right()
- Default state: Border is off
- Default action: Set border type 1
- Valid args:
0-13, See below.
A cell border is made up of a border on the bottom, top, left and right. You can set them all to the same value with set_border(), or individually with the relevant method calls shown above. mix and match, bestie 💖
The following shows the border styles sorted by Excel::Writer::XLSX index number:
Index Name Weight Style
===== ============= ====== ===========
0 None 0
1 Continuous 1 -----------
2 Continuous 2 -----------
3 Dash 1 - - - - - -
4 Dot 1 . . . . . .
5 Continuous 3 -----------
6 Double 3 ===========
7 Continuous 0 -----------
8 Dash 2 - - - - - -
9 Dash Dot 1 - . - . - .
10 Dash Dot 2 - . - . - .
11 Dash Dot Dot 1 - . . - . .
12 Dash Dot Dot 2 - . . - . .
13 SlantDash Dot 2 / - . / - .The following shows the borders sorted by style:
Name Weight Style Index
============= ====== =========== =====
Continuous 0 ----------- 7
Continuous 1 ----------- 1
Continuous 2 ----------- 2
Continuous 3 ----------- 5
Dash 1 - - - - - - 3
Dash 2 - - - - - - 8
Dash Dot 1 - . - . - . 9
Dash Dot 2 - . - . - . 10
Dash Dot Dot 1 - . . - . . 11
Dash Dot Dot 2 - . . - . . 12
Dot 1 . . . . . . 4
Double 3 =========== 6
None 0 0
SlantDash Dot 2 / - . / - . 13The following shows the borders in the order shown in the Excel Dialog.
Index Style Index Style
===== ===== ===== =====
0 None 12 - . . - . .
7 ----------- 13 / - . / - .
4 . . . . . . 10 - . - . - .
11 - . . - . . 8 - - - - - -
9 - . - . - . 2 -----------
3 - - - - - - 5 -----------
1 ----------- 6 ===========Examples of the available border styles are shown in the 'Borders' worksheet created by formats.pl. 🔥
set_border_color() 🌈
- Also applies to:
set_bottom_color()set_top_color()set_left_color()set_right_color()
- Default state: Color is off
- Default action: Undefined
- Valid args: See
set_color()
Set the colour of the cell borders. A cell border is literally four separate borders — bottom, top, left, right — and you can slap the same colour on all of them with set_border_color() or do them one at a time with the individual methods listed above. You can peep examples of the border styles and colours in the 'Borders' worksheet that formats.pl makes for you. 💅
✨ set_diag_type()
- Default state: Diagonal border is off.
- Default action: None.
- Valid args:
1-3, See below.
Sets the diagonal border type for the cell. Excel gives you three flavours of diagonal border, and they're giving very much "I drew a line through my notes" energy:
- From bottom left to top right.
- From top left to bottom right.
- Same as 1 and 2 combined.
For example:
$format->set_diag_type( 3 );✨ set_diag_border()
- Default state: Border is off
- Default action: Set border type 1
- Valid args:
0-13, See below.
Sets the diagonal border style. Same deal as the parameter you pass to set_border() above, no cap.
✨ set_diag_color()
- Default state: Color is off
- Default action: Undefined
- Valid args: See
set_color()
Sets the colour of the diagonal cell border, like so:
$format->set_diag_type( 3 );
$format->set_diag_border( 7 );
$format->set_diag_color( 'red' );✨ set_quote_prefix()
- Default state: quote prefix is off
- Default action: Turn quote prefix on
- Valid args:
0,1
Sets the quote prefix property of a format, which forces a string to be treated as a string even after someone edits it. It's the exact same thing as putting a single quote in front of the string in Excel — except you don't need to add the quote to the string, you just need to add the format. Bestie, it's the sneaky little shortcut and it slays. 🫶
Turn the quote prefix on:
$format->set_quote_prefix(); # Turn quote prefix on✨ copy( $format )
This method copies every single property from one Format object over to another. Watch:
my $lorry1 = $workbook->add_format();
$lorry1->set_bold();
$lorry1->set_italic();
$lorry1->set_color( 'red' ); # lorry1 is bold, italic and red
my $lorry2 = $workbook->add_format();
$lorry2->copy( $lorry1 );
$lorry2->set_color( 'yellow' ); # lorry2 is bold, italic and yellowcopy() is only useful if you're out here using the method interface for Format properties. If you're setting Format properties directly with hashes, you generally won't need it at all.
Note: this is not a copy constructor. Both objects have to already exist before you copy. No sneaking one in mid-air. 😭
🦄 UNICODE IN EXCEL 🦄
Okay so here's your quick lil intro to handling Unicode in Excel::Writer::XLSX.
If you want the bigger picture on Unicode handling in Perl in general, go read perlunitut and perluniintro.
Here's the tea: Excel::Writer::XLSX the writer is different from Spreadsheet::WriteExcel because it only handles Unicode data in UTF-8 format, and it doesn't even try to deal with the legacy UTF-16 Excel formats. Periodt.
If your data is already in UTF-8 format? Excel::Writer::XLSX handles it automatically. Iconic behaviour, no notes. ✨
If you're dealing with non-ASCII characters that are not in UTF-8, then Perl has your back with some very useful tools hiding inside the Encode module, ready to convert things into the format you need. For example:
use Encode 'decode';
my $string = 'some string with koi8-r characters';
$string = decode('koi8-r', $string); # koi8-r to utf8Or alternatively, you can read data out of an encoded file and convert it to UTF-8 as you're reading it in:
my $file = 'unicode_koi8r.txt';
open FH, '<:encoding(koi8-r)', $file or die "Couldn't open $file: $!\n";
my $row = 0;
while ( <FH> ) {
# Data read in is now in utf8 format.
chomp;
$worksheet->write( $row++, 0, $_ );
}These methodologies get explained in way more detail over in perlunitut, perluniintro and perlunicode.
And if your program itself contains UTF-8 text, you'll also need to add use utf8 to your includes:
use utf8;
...
$worksheet->write( 'A1', 'Some UTF-8 string' );Also go check out the unicode_*.pl programs in the examples directory of the distro. They're giving demo, they're giving helpful. 🌸
🌈 WORKING WITH COLOURS 🌈
All throughout Excel::Writer::XLSX you can specify colours using an Html style #RRGGBB value. For example, with a Format object:
$format->set_color( '#FF0000' );And for backward compatibility, a limited number of color names are supported:
$format->set_color( 'red' );The color names you can use are:
blackbluebrowncyangraygreenlimemagentanavyorangepinkpurpleredsilverwhiteyellow
See also colors.pl in the examples directory. 🎀
📅 DATES AND TIME IN EXCEL 📅
There are two important things to understand about dates and times in Excel, and honestly bestie, if you get these two you're set:
- A date/time in Excel is a real number plus an Excel number format.
- Excel::Writer::XLSX doesn't automatically convert date/time strings in
write()to an Excel date/time.
Both of these are explained in more detail below, along with some suggestions on how to convert times and dates into the required format.
🌟 An Excel date/time is a number plus a format
If you write a date string with write() then all you'll get back is... a string. That's it. That's the whole thing. 😭
$worksheet->write( 'A1', '02/03/04' ); # !! Writes a string not a date. !!Dates and times in Excel are represented by real numbers. For example Jan 1 2001 12:30 PM is represented by the number 36892.521.
The integer part of the number stores the number of days since the epoch, and the fractional part stores the percentage of the day.
A date or time in Excel is just like any other number, fr fr. To make that number display as a date, you must apply an Excel number format to it. Here are some examples:
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'date_examples.xlsx' );
my $worksheet = $workbook->add_worksheet();
$worksheet->set_column( 'A:A', 30 ); # For extra visibility.
my $number = 39506.5;
$worksheet->write( 'A1', $number ); # 39506.5
my $format2 = $workbook->add_format( num_format => 'dd/mm/yy' );
$worksheet->write( 'A2', $number, $format2 ); # 28/02/08
my $format3 = $workbook->add_format( num_format => 'mm/dd/yy' );
$worksheet->write( 'A3', $number, $format3 ); # 02/28/08
my $format4 = $workbook->add_format( num_format => 'd-m-yyyy' );
$worksheet->write( 'A4', $number, $format4 ); # 28-2-2008
my $format5 = $workbook->add_format( num_format => 'dd/mm/yy hh:mm' );
$worksheet->write( 'A5', $number, $format5 ); # 28/02/08 12:00
my $format6 = $workbook->add_format( num_format => 'd mmm yyyy' );
$worksheet->write( 'A6', $number, $format6 ); # 28 Feb 2008
my $format7 = $workbook->add_format( num_format => 'mmm d yyyy hh:mm AM/PM' );
$worksheet->write('A7', $number , $format7); # Feb 28 2008 12:00 PM
$workbook->close();🌟 Excel::Writer::XLSX doesn't automatically convert date/time strings
Excel::Writer::XLSX does not automatically convert input date strings into Excel's formatted date numbers. Why? Because there are a huge number of possible date formats out there, and also because of the very real possibility of misinterpretation. It's giving chaos, honestly.
For example: does 02/03/04 mean March 2 2004, February 3 2004, or even March 4 2002? Exactly. Nobody knows. 💀
So therefore, to handle dates you'll have to convert them to numbers and apply an Excel format. Some methods for converting dates are listed in the next section.
The most direct way is to convert your dates to the ISO8601 yyyy-mm-ddThh:mm:ss.sss date format and use the write_date_time() worksheet method:
$worksheet->write_date_time( 'A2', '2001-01-01T12:20', $format );See the write_date_time() section of the documentation for more details.
A general methodology for handling date strings with write_date_time() goes like this:
- Identify incoming date/time strings with a regex.
- Extract the component parts of the date/time using the same regex.
- Convert the date/time to the ISO8601 format.
- Write the date/time using
write_date_time()and a number format.
Here's an example:
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'example.xlsx' );
my $worksheet = $workbook->add_worksheet();
# Set the default format for dates.
my $date_format = $workbook->add_format( num_format => 'mmm d yyyy' );
# Increase column width to improve visibility of data.
$worksheet->set_column( 'A:C', 20 );
# Simulate reading from a data source.
my $row = 0;
while ( <DATA> ) {
chomp;
my $col = 0;
my @data = split ' ';
for my $item ( @data ) {
# Match dates in the following formats: d/m/yy, d/m/yyyy
if ( $item =~ qr[^(\d{1,2})/(\d{1,2})/(\d{4})$] ) {
# Change to the date format required by write_date_time().
my $date = sprintf "%4d-%02d-%02dT", $3, $2, $1;
$worksheet->write_date_time( $row, $col++, $date,
$date_format );
}
else {
# Just plain data
$worksheet->write( $row, $col++, $item );
}
}
$row++;
}
$workbook->close();
__DATA__
Item Cost Date
Book 10 1/9/2007
Beer 4 12/9/2007
Bed 500 5/10/2007For a slightly more advanced solution, you can modify the write() method to handle date formats of your choice via the add_write_handler() method. Check out the add_write_handler() section of the docs, plus the write_handler3.pl and write_handler4.pl programs in the examples directory of the distro. 👑
🌟 Converting dates and times to an Excel date or time
The write_date_time() method above is just one way of handling dates and times.
You can also use the convert_date_time() worksheet method to convert from an ISO8601 style date string into an Excel date and time number.
The Excel::Writer::XLSX::Utility module, which is included in the distro, has date/time handling functions:
use Excel::Writer::XLSX::Utility;
$date = xl_date_list(2002, 1, 1); # 37257
$date = xl_parse_date("11 July 1997"); # 35622
$time = xl_parse_time('3:21:36 PM'); # 0.64
$date = xl_decode_date_EU("13 May 2002"); # 37389Note: some of these functions require additional CPAN modules. Don't get got. 🙈
For date conversions using the CPAN DateTime framework, see DateTime::Format::Excel — http://search.cpan.org/search?dist=DateTime-Format-Excel.
🌸 OUTLINES AND GROUPING IN EXCEL 🌸
okay bestie, so Excel lets you group rows or columns together so you can hide or show them all with literally one mouse click 💅 and that whole vibe is called an outline. iconic, honestly.
Outlines can squish complex data down to a few cute salient sub-totals or summaries. main character energy for spreadsheets.
This feature is best viewed in Excel (obvi) but here's an ASCII representation of what a worksheet with three outlines might look like. Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at level 1. Those lines on the left hand side are called outline level bars.
------------------------------------------
1 2 3 | | A | B | C | D | ...
------------------------------------------
_ | 1 | A | | | | ...
| _ | 2 | B | | | | ...
| | | 3 | (C) | | | | ...
| | | 4 | (D) | | | | ...
| - | 5 | E | | | | ...
| _ | 6 | F | | | | ...
| | | 7 | (G) | | | | ...
| | | 8 | (H) | | | | ...
| - | 9 | I | | | | ...
- | . | ... | ... | ... | ... | ...Clicking the minus sign on each of the level 2 outlines will collapse and hide the data, like in the next figure. The minus sign changes to a plus sign to show that the data in the outline is hidden. sneaky little plus 🫣
------------------------------------------
1 2 3 | | A | B | C | D | ...
------------------------------------------
_ | 1 | A | | | | ...
| | 2 | B | | | | ...
| + | 5 | E | | | | ...
| | 6 | F | | | | ...
| + | 9 | I | | | | ...
- | . | ... | ... | ... | ... | ...Clicking on the minus sign on the level 1 outline will collapse the remaining rows as follows:
------------------------------------------
1 2 3 | | A | B | C | D | ...
------------------------------------------
| 1 | A | | | | ...
+ | . | ... | ... | ... | ... | ...Grouping in Excel::Writer::XLSX is achieved by setting the outline level via the set_row() and set_column() worksheet methods:
set_row( $row, $height, $format, $hidden, $level, $collapsed )
set_column( $first_col, $last_col, $width, $format, $hidden, $level, $collapsed )The following example sets an outline level of 1 for rows 2 and 3 (zero-indexed) and columns B to G. The parameters $height and $XF are assigned default values since they are undefined:
$worksheet->set_row( 1, undef, undef, 0, 1 );
$worksheet->set_row( 2, undef, undef, 0, 1 );
$worksheet->set_column( 'B:G', undef, undef, 0, 1 );Excel allows up to 7 outline levels. Therefore the $level parameter should be in the range 0 <= $level <= 7. yeah, seven. that's the ceiling, don't fight it 🌈
Rows and columns can be collapsed by setting the $hidden flag for the hidden rows/columns and setting the $collapsed flag for the row/column that has the collapsed + symbol:
$worksheet->set_row( 1, undef, undef, 1, 1 );
$worksheet->set_row( 2, undef, undef, 1, 1 );
$worksheet->set_row( 3, undef, undef, 0, 0, 1 ); # Collapsed flag.
$worksheet->set_column( 'B:G', undef, undef, 1, 1 );
$worksheet->set_column( 'H:H', undef, undef, 0, 0, 1 ); # Collapsed flag.Note: setting the $collapsed flag is particularly important for compatibility with OpenOffice.org and Gnumeric. fr fr, don't skip it bestie 💖
For a more complete example see the outline.pl and outline_collapsed.pl programs in the examples directory of the distro.
Some additional outline properties can be set via the outline_settings() worksheet method, see above. 👀
✨ DATA VALIDATION IN EXCEL ✨
Data validation is a feature of Excel which allows you to restrict the data that a user enters in a cell and to display help and warning messages. It also lets you restrict input to values in a drop down list. lowkey the most gatekeeping feature and honestly we love that for it 💅
A typical use case might be to restrict data in a cell to integer values in a certain range, to provide a help message to indicate the required value, and to issue a warning if the input data doesn't meet the stated criteria. In Excel::Writer::XLSX we could do that as follows:
$worksheet->data_validation('B3',
{
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
input_title => 'Input an integer:',
input_message => 'Between 1 and 100',
error_message => 'Sorry, try again.',
});For more information on data validation see the following Microsoft support article Description and examples of data validation in Excel: <http://support.microsoft.com/kb/211485>. yes it's a link, no you can't click it in a terminal, but it's there 🎀
The following sections describe how to use the data_validation() method and its various options.
🌷 data_validation( $row, $col, { parameter => 'value', ... } )
The data_validation() method is used to construct an Excel data validation.
It can be applied to a single cell or a range of cells. You can pass 3 parameters such as ($row, $col, {...}) or 5 parameters such as ($first_row, $first_col, $last_row, $last_col, {...}). You can also use A1 style notation. For example:
$worksheet->data_validation( 0, 0, {...} );
$worksheet->data_validation( 0, 0, 4, 1, {...} );
# Which are the same as:
$worksheet->data_validation( 'A1', {...} );
$worksheet->data_validation( 'A1:B5', {...} );See also the note about Cell notation for more information.
The last parameter in data_validation() must be a hash ref containing the parameters that describe the type and style of the data validation. The allowable parameters are:
validate
criteria
value | minimum | source
maximum
ignore_blank
dropdown
input_title
input_message
show_input
error_title
error_message
error_type
show_errorThese parameters are explained in the following sections. Most of the parameters are optional, however, you will generally require the three main options validate, criteria and value. the big three, the trilogy, the moment 🫶
$worksheet->data_validation('B3',
{
validate => 'integer',
criteria => '>',
value => 100,
});The data_validation method returns:
0for success. slay ✅-1for insufficient number of arguments.-2for row or column out of bounds.-3for incorrect parameter or value. 😭
🌸 validate
This parameter is passed in a hash ref to data_validation().
The validate parameter is used to set the type of data that you wish to validate. It is always required and it has no default value. Allowable values are:
any
integer
decimal
list
date
time
length
customanyis used to specify that the type of data is unrestricted. This is useful to display an input message without restricting the data that can be entered. no rules, just vibes 🦋integerrestricts the cell to integer values. Excel refers to this as 'whole number'.
validate => 'integer',
criteria => '>',
value => 100,decimalrestricts the cell to decimal values.
validate => 'decimal',
criteria => '>',
value => 38.6,listrestricts the cell to a set of user specified values. These can be passed in an array ref or as a cell range (named ranges aren't currently supported):
validate => 'list',
value => ['open', 'high', 'close'],
# Or like this:
value => 'B1:B3',Excel requires that range references are only to cells on the same worksheet. stay on your own sheet bestie 😤
daterestricts the cell to date values. Dates in Excel are expressed as integer values but you can also pass an ISO8601 style string as used inwrite_date_time(). See alsoDATES AND TIME IN EXCELfor more information about working with Excel's dates.
validate => 'date',
criteria => '>',
value => 39653, # 24 July 2008
# Or like this:
value => '2008-07-24T',timerestricts the cell to time values. Times in Excel are expressed as decimal values but you can also pass an ISO8601 style string as used inwrite_date_time(). See alsoDATES AND TIME IN EXCELfor more information about working with Excel's times.
validate => 'time',
criteria => '>',
value => 0.5, # Noon
# Or like this:
value => 'T12:00:00',lengthrestricts the cell data based on an integer string length. Excel refers to this as 'Text length'.
validate => 'length',
criteria => '>',
value => 10,customrestricts the cell based on an external Excel formula that returns aTRUE/FALSEvalue.
validate => 'custom',
value => '=IF(A10>B10,TRUE,FALSE)',💜 criteria
This parameter is passed in a hash ref to data_validation().
The criteria parameter is used to set the criteria by which the data in the cell is validated. It is almost always required except for the list and custom validate options. It has no default value. Allowable values are:
'between'
'not between'
'equal to' | '==' | '='
'not equal to' | '!=' | '<>'
'greater than' | '>'
'less than' | '<'
'greater than or equal to' | '>='
'less than or equal to' | '<='You can either use Excel's textual description strings, in the first column above, or the more common symbolic alternatives. The following are equivalent:
validate => 'integer',
criteria => 'greater than',
value => 100,
validate => 'integer',
criteria => '>',
value => 100,The list and custom validate options don't require a criteria. If you specify one it will be ignored. literally just ignored, it's giving ignored 💀
validate => 'list',
value => ['open', 'high', 'close'],
validate => 'custom',
value => '=IF(A10>B10,TRUE,FALSE)',🎀 value | minimum | source
This parameter is passed in a hash ref to data_validation().
💖 value 💖
The value parameter sets the limiting value that the criteria gets applied to. It's always required, bestie, no default, no skipping, periodt. You can also use the synonyms minimum or source if you want your code to read a lil clearer and match up with how Excel itself describes the parameter:
# Use 'value'
validate => 'integer',
criteria => '>',
value => 100,
# Use 'minimum'
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
# Use 'source'
validate => 'list',
source => '$B$1:$B$3',🍓 maximum 🍓
This parameter gets passed in a hash ref to data_validation().
maximum sets the upper limiting value, and it matters when criteria is either 'between' or 'not between':
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,🌸 ignore_blank 🌸
This parameter gets passed in a hash ref to data_validation().
ignore_blank flips the 'Ignore blank' option in the Excel data validation dialog on and off. When it's on, the data validation is not applied to blank data in the cell. And guess what — it's on by default, king behaviour honestly:
ignore_blank => 0, # Turn the option off🦋 dropdown 🦋
This parameter gets passed in a hash ref to data_validation().
dropdown toggles the 'In-cell dropdown' option in the Excel data validation dialog. When it's on, a dropdown list shows up for list validations. On by default, as it should be:
dropdown => 0, # Turn the option off✨ input_title ✨
This parameter gets passed in a hash ref to data_validation().
input_title sets the title of the input message that pops up when a cell is entered. No default value, and it only shows if the input message itself is showing — check the input_message parameter below, they're a package deal:
input_title => 'This is the input title',The maximum title length is 32 characters. Can't be out here writing a whole essay 💀
🎀 input_message 🎀
This parameter gets passed in a hash ref to data_validation().
input_message sets the input message that shows when a cell is entered. It has no default value:
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
input_title => 'Enter the applied discount:',
input_message => 'between 1 and 100',You can split the message over several lines using newlines — that's "\n" in double quoted strings:
input_message => "This is\na test.",The maximum message length is 255 characters.
💅 show_input 💅
This parameter gets passed in a hash ref to data_validation().
show_input toggles the 'Show input message when cell is selected' option in the Excel data validation dialog. When it's off, an input message won't display even if you set one with input_message. It's on by default:
show_input => 0, # Turn the option off👑 error_title 👑
This parameter gets passed in a hash ref to data_validation().
error_title sets the title of the error message that gets displayed when the data validation criteria isn't met. The default error title is 'Microsoft Excel':
error_title => 'Input value is not valid',The maximum title length is 32 characters.
🔥 error_message 🔥
This parameter gets passed in a hash ref to data_validation().
error_message sets the error message that displays when a cell is entered. The default error message is "The value you entered is not valid.\nA user has restricted values that can be entered into the cell.":
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
error_title => 'Input value is not valid',
error_message => 'It should be an integer between 1 and 100',Just like the input message, you can split it over several lines with newlines, "\n" in double quoted strings:
input_message => "This is\na test.",The maximum message length is 255 characters.
🌈 error_type 🌈
This parameter gets passed in a hash ref to data_validation().
error_type picks which flavour of error dialog shows up. There are 3 options:
'stop'
'warning'
'information'The default is 'stop'.
💜 show_error 💜
This parameter gets passed in a hash ref to data_validation().
show_error toggles the 'Show error alert after invalid data is entered' option in the Excel data validation dialog. When it's off, an error message is not displayed even if you set one with error_message. On by default:
show_error => 0, # Turn the option off🧚 Data Validation Examples 🧚
Example 1. Limiting input to an integer greater than a fixed value.
$worksheet->data_validation('A1',
{
validate => 'integer',
criteria => '>',
value => 0,
});Example 2. Limiting input to an integer greater than a fixed value where the value is referenced from a cell.
$worksheet->data_validation('A2',
{
validate => 'integer',
criteria => '>',
value => '=E3',
});Example 3. Limiting input to a decimal in a fixed range.
$worksheet->data_validation('A3',
{
validate => 'decimal',
criteria => 'between',
minimum => 0.1,
maximum => 0.5,
});Example 4. Limiting input to a value in a dropdown list.
$worksheet->data_validation('A4',
{
validate => 'list',
source => ['open', 'high', 'close'],
});Example 5. Limiting input to a value in a dropdown list where the list is specified as a cell range.
$worksheet->data_validation('A5',
{
validate => 'list',
source => '=$E$4:$G$4',
});Example 6. Limiting input to a date in a fixed range.
$worksheet->data_validation('A6',
{
validate => 'date',
criteria => 'between',
minimum => '2008-01-01T',
maximum => '2008-12-12T',
});Example 7. Displaying a message when the cell is selected.
$worksheet->data_validation('A7',
{
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
input_title => 'Enter an integer:',
input_message => 'between 1 and 100',
});Also go peek at the data_validate.pl program in the examples directory of the distro, it's so good fr fr 🫶
💖 CONDITIONAL FORMATTING IN EXCEL 💖
Conditional formatting is that Excel feature where you apply a format to a cell — or a whole range of cells — based on a certain criteria. Iconic, lowkey the moment.
For example, here's the criteria used to highlight cells >= 50 in red in the conditional_format.pl example from the distro:
# Write a conditional format over a range.
$worksheet->conditional_formatting( 'B3:K12',
{
type => 'cell',
criteria => '>=',
value => 50,
format => $format1,
}
);✨ conditional_formatting( $row, $col, { parameter => 'value', ... } ) ✨
The conditional_formatting() method applies formatting based on user defined criteria to an Excel::Writer::XLSX file.
It works on a single cell or a range of cells. You can pass 3 parameters like ($row, $col, {...}) or 5 parameters like ($first_row, $first_col, $last_row, $last_col, {...}). You can also use A1 style notation. Like so:
$worksheet->conditional_formatting( 0, 0, {...} );
$worksheet->conditional_formatting( 0, 0, 4, 1, {...} );
# Which are the same as:
$worksheet->conditional_formatting( 'A1', {...} );
$worksheet->conditional_formatting( 'A1:B5', {...} );Also go read the note about Cell notation for more, bestie.
With A1 style notation you can even specify non-contiguous ranges, separated by a comma. Obsessed:
$worksheet->conditional_formatting( 'A1:D5,A8:D12', {...} );The last parameter in conditional_formatting() has to be a hash ref holding the parameters that describe the type and style of the data validation. The main parameters are:
type
format
criteria
value
minimum
maximumOther, less commonly used parameters are:
min_type
mid_type
max_type
min_value
mid_value
max_value
min_color
mid_color
max_color
bar_color
bar_only
bar_solid
bar_negative_color
bar_border_color
bar_negative_border_color
bar_negative_color_same
bar_negative_border_color_same
bar_no_border
bar_direction
bar_axis_position
bar_axis_color
data_bar_2010
icon_style
icons
reverse_icons
icons_only
stop_if_true
multi_rangeExtra parameters used for specific conditional format types are shown in the relevant sections below, so keep scrolling 💅
🌟 type 🌟
This parameter gets passed in a hash ref to conditional_formatting().
type sets which kind of conditional formatting you want to apply. It is always required and it has no default value. The allowable type values and their associated parameters are:
cell— parameters:criteria,value,minimum,maximum,formatdate— parameters:criteria,value,minimum,maximum,formattime_period— parameters:criteria,formattext— parameters:criteria,value,formataverage— parameters:criteria,formatduplicate— parameters:formatunique— parameters:formattop— parameters:criteria,value,formatbottom— parameters:criteria,value,formatblanks— parameters:formatno_blanks— takesformaterrors— takesformatno_errors— takesformatformula— takescriteria,format2_color_scale— takesmin_type,max_type,min_value,max_value,min_color,max_color3_color_scale— takesmin_type,mid_type,max_type,min_value,mid_value,max_value,min_color,mid_color,max_colordata_bar— takesmin_type,max_type,min_value,max_value,bar_only,bar_color,bar_solid*,bar_negative_color*,bar_border_color*,bar_negative_border_color*,bar_negative_color_same*,bar_negative_border_color_same*,bar_no_border*,bar_direction*,bar_axis_position*,bar_axis_color*,data_bar_2010*icon_set— takesicon_style,reverse_icons,icons,icons_only
🫶 Data bar parameters marked with (*) are only available in Excel 2010 and later, bestie. Files that use these properties can still be opened in Excel 2007 but the data bars will be displayed without them. So no cap: they open, they just don't get the glow-up. 💅
type => 'cell'
This is the most common conditional formatting type, like the main character of conditional formats fr fr. It is used when a format is applied to a cell based on a simple criterion. For example:
$worksheet->conditional_formatting( 'A1',
{
type => 'cell',
criteria => 'greater than',
value => 5,
format => $red_format,
}
);Or, using the "between" criteria:
$worksheet->conditional_formatting( 'C1:C4',
{
type => 'cell',
criteria => 'between',
minimum => 20,
maximum => 30,
format => $green_format,
}
);criteria
The criteria parameter is used to set the criteria by which the cell data will be evaluated. It has no default value — you gotta tell it, it's not a mind reader 💀. The most common criteria as applied to { type => 'cell' } are:
'between'
'not between'
'equal to' | '==' | '='
'not equal to' | '!=' | '<>'
'greater than' | '>'
'less than' | '<'
'greater than or equal to' | '>='
'less than or equal to' | '<='You can either use Excel's textual description strings, in the first column above, or the more common symbolic alternatives. Both slay equally 💖.
Additional criteria which are specific to other conditional format types are shown in the relevant sections below.
value
The value is generally used along with the criteria parameter to set the rule by which the cell data will be evaluated.
type => 'cell',
criteria => '>',
value => 5
format => $format,The value property can also be a cell reference, iykyk:
type => 'cell',
criteria => '>',
value => '$C$1',
format => $format,format
The format parameter is used to specify the format that will be applied to the cell when the conditional formatting criterion is met. The format is created using the add_format() method in the same way as cell formats:
$format = $workbook->add_format( bold => 1, italic => 1 );
$worksheet->conditional_formatting( 'A1',
{
type => 'cell',
criteria => '>',
value => 5
format => $format,
}
);The conditional format follows the same rules as in Excel: it is superimposed over the existing cell format and not all font and border properties can be modified. Font properties that can't be modified are font name, font size, superscript and subscript. The border property that cannot be modified is diagonal borders. So yeah — some things are just off limits, no negotiations 🚫.
Excel specifies some default formats to be used with conditional formatting. You can replicate them using the following Excel::Writer::XLSX formats:
# Light red fill with dark red text.
my $format1 = $workbook->add_format(
bg_color => '#FFC7CE',
color => '#9C0006',
);
# Light yellow fill with dark yellow text.
my $format2 = $workbook->add_format(
bg_color => '#FFEB9C',
color => '#9C6500',
);
# Green fill with dark green text.
my $format3 = $workbook->add_format(
bg_color => '#C6EFCE',
color => '#006100',
);minimum
The minimum parameter is used to set the lower limiting value when the criteria is either 'between' or 'not between':
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,maximum
The maximum parameter is used to set the upper limiting value when the criteria is either 'between' or 'not between'. See the previous example, bestie 👆.
type => 'date'
The date type is the same as the cell type and uses the same criteria and values. However it allows the value, minimum and maximum properties to be specified in the ISO8601 yyyy-mm-ddThh:mm:ss.sss date format which is detailed in the write_date_time() method. ✨
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'date',
criteria => 'greater than',
value => '2011-01-01T',
format => $format,
}
);type => 'time_period'
The time_period type is used to specify Excel's "Dates Occurring" style conditional format.
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'time_period',
criteria => 'yesterday',
format => $format,
}
);The period is set in the criteria and can have one of the following values:
criteria => 'yesterday',
criteria => 'today',
criteria => 'last 7 days',
criteria => 'last week',
criteria => 'this week',
criteria => 'next week',
criteria => 'last month',
criteria => 'this month',
criteria => 'next month'type => 'text'
The text type is used to specify Excel's "Specific Text" style conditional format. It is used to do simple string matching using the criteria and value parameters:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'text',
criteria => 'containing',
value => 'foo',
format => $format,
}
);The criteria can have one of the following values:
criteria => 'containing',
criteria => 'not containing',
criteria => 'begins with',
criteria => 'ends with',The value parameter should be a string or single character. 🍓
type => 'average'
The average type is used to specify Excel's "Average" style conditional format.
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'average',
criteria => 'above',
format => $format,
}
);The type of average for the conditional format range is specified by the criteria:
criteria => 'above',
criteria => 'below',
criteria => 'equal or above',
criteria => 'equal or below',
criteria => '1 std dev above',
criteria => '1 std dev below',
criteria => '2 std dev above',
criteria => '2 std dev below',
criteria => '3 std dev above',
criteria => '3 std dev below',type => 'duplicate'
The duplicate type is used to highlight duplicate cells in a range. Twin energy 👯✨:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'duplicate',
format => $format,
}
);type => 'unique'
The unique type is used to highlight unique cells in a range — one of one, literally 💅:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'unique',
format => $format,
}
);type => 'top'
The top type is used to specify the top "n" values by number or percentage in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'top',
value => 10,
format => $format,
}
);The criteria can be used to indicate that a percentage condition is required:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'top',
value => 10,
criteria => '%',
format => $format,
}
);type => 'bottom'
The bottom type is used to specify the bottom "n" values by number or percentage in a range.
It takes the same parameters as top, see above. 👑 (well, more like the opposite of the crown, but you get it)
type => 'blanks'
The blanks type is used to highlight blank cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'blanks',
format => $format,
}
);type => 'no_blanks'
The no_blanks type is used to highlight non blank cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'no_blanks',
format => $format,
}
);type => 'errors'
The errors type is used to highlight error cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'errors',
format => $format,
}
);type => 'no_errors'
The no_errors type is used to highlight non error cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'no_errors',
format => $format,
}
);type => 'formula'
The formula type is used to specify a conditional format based on a user defined formula. This is where you get to be the main character and write it yourself 🔥:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'formula',
criteria => '=$A$1 > 5',
format => $format,
}
);The formula is specified in the criteria. 💅
🌈 type => '2_color_scale'
The 2_color_scale type is your ticket to Excel's "2 Color Scale" style conditional format, bestie. Two colours, one gradient, absolute slay. ✨
$worksheet->conditional_formatting( 'A1:A12',
{
type => '2_color_scale',
}
);You can customize this conditional type with min_type, max_type, min_value, max_value, min_color and max_color, all explained down below. 👇
🎀 type => '3_color_scale'
The 3_color_scale type is for Excel's "3 Color Scale" style conditional format — three colours this time, because two was simply not enough drama. 💖
$worksheet->conditional_formatting( 'A1:A12',
{
type => '3_color_scale',
}
);This conditional type can be modified with min_type, mid_type, max_type, min_value, mid_value, max_value, min_color, mid_color and max_color — see below. 🫶
📊 type => 'data_bar'
The data_bar type gets you Excel's "Data Bar" style conditional format. Bars in your cells. It's giving chart but make it inline. 🔥
$worksheet->conditional_formatting( 'A1:A12',
{
type => 'data_bar',
}
);This data bar conditional type can be modified with the following parameters, which are all explained in the sections below. These properties were available in the original xlsx file specification used in Excel 2007:
min_type
max_type
min_value
max_value
bar_color
bar_onlyIn Excel 2010 additional data bar properties were added such as solid (non-gradient) bars and control over how negative values are displayed. These properties can be set using the following parameters:
bar_solid
bar_negative_color
bar_border_color
bar_negative_border_color
bar_negative_color_same
bar_negative_border_color_same
bar_no_border
bar_direction
bar_axis_position
bar_axis_color
data_bar_2010Heads up bestie: files that use these Excel 2010 properties can still be opened in Excel 2007, but the data bars will be displayed without them. So the file isn't broken, it just loses the glow-up. 💀
🚦 type => 'icon_set'
The icon_set type specifies a conditional format with a set of icons — think traffic lights 🚦 or arrows ⬆️⬇️:
$worksheet->conditional_formatting( 'A1:C1',
{
type => 'icon_set',
icon_style => '3_traffic_lights',
}
);The icon set style is specified by the icon_style parameter. Valid options are:
3_arrows
3_arrows_gray
3_flags
3_signs
3_symbols
3_symbols_circled
3_traffic_lights
3_traffic_lights_rimmed
4_arrows
4_arrows_gray
4_ratings
4_red_to_black
4_traffic_lights
5_arrows
5_arrows_gray
5_quarters
5_ratingsThe criteria, type and value of each icon can be specified using the icon array of hash refs, with optional criteria, type and value parameters:
$worksheet->conditional_formatting( 'A1:D1',
{
type => 'icon_set',
icon_style => '4_red_to_black',
icons => [ {criteria => '>', type => 'number', value => 90},
{criteria => '>=', type => 'percentile', value => 50},
{criteria => '>', type => 'percent', value => 25},
],
}
);The icons criteria parameter should be either >= or >. The default criteria is >=. 🎯
The icons type parameter should be one of the following values:
number
percentile
percent
formulaThe default type is percent.
The icons value parameter can be a value or a formula:
$worksheet->conditional_formatting( 'A1:D1',
{
type => 'icon_set',
icon_style => '4_red_to_black',
icons => [ {value => 90},
{value => 50},
{value => 25},
],
}
);Note bestie: theiconsparameters should start with the highest value and with each subsequent one being lower. The defaultvalueis(n * 100) / number_of_icons. And listen — the lowest number icon in an icon set has properties defined by Excel itself. Therefore in annicon set, there is non-1hash of parameters. That's not a bug, that's just the way it is. 🙈
The order of the icons can be reversed using the reverse_icons parameter:
$worksheet->conditional_formatting( 'A1:C1',
{
type => 'icon_set',
icon_style => '3_arrows',
reverse_icons => 1,
}
);The icons can be displayed without the cell value using the icons_only parameter:
$worksheet->conditional_formatting( 'A1:C1',
{
type => 'icon_set',
icon_style => '3_flags',
icons_only => 1,
}
);🎚️ min_type, mid_type, max_type
The min_type and max_type properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_type is available for 3_color_scale. Here's how you use them:
$worksheet->conditional_formatting( 'A1:A12',
{
type => '2_color_scale',
min_type => 'percent',
max_type => 'percent',
}
);The available min/mid/max types are:
min (for min_type only)
num
percent
percentile
formula
max (for max_type only)🔢 min_value, mid_value, max_value
The min_value and max_value properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_value is available for 3_color_scale. Like this:
$worksheet->conditional_formatting( 'A1:A12',
{
type => '2_color_scale',
min_value => 10,
max_value => 90,
}
);🎨 min_color, mid_color, max_color, bar_color
The min_color and max_color properties are available when the conditional formatting type is 2_color_scale, 3_color_scale or data_bar. The mid_color is available for 3_color_scale. Obsessed with this one honestly:
$worksheet->conditional_formatting( 'A1:A12',
{
type => '2_color_scale',
min_color => "#C5D9F1",
max_color => "#538ED5",
}
);The color can be specified as an Excel::Writer::XLSX color index or, more usefully, as a HTML style RGB hex number, as shown above. 💅
📏 bar_only
The bar_only parameter property displays a bar data but not the data in the cells:
$worksheet->conditional_formatting( 'D3:D14',
{
type => 'data_bar',
bar_only => 1
}
);🧊 bar_solid
The bar_solid parameter turns on a solid (non-gradient) fill for data bars:
$worksheet->conditional_formatting( 'H3:H14',
{
type => 'data_bar',
bar_solid => 1
}
);Note: this property is only visible in Excel 2010 and later. 🫠
➖ bar_negative_color
The bar_negative_color parameter sets the color fill for the negative portion of a data bar.
The color can be specified as an Excel::Writer::XLSX color index or as a HTML style RGB hex number, as shown in the other examples.
Note: this property is only visible in Excel 2010 and later.
🖌️ bar_border_color
The bar_border_color parameter sets the border color of a data bar.
The color can be specified as an Excel::Writer::XLSX color index or as a HTML style RGB hex number, as shown in the other examples.
Note: this property is only visible in Excel 2010 and later.
🖍️ bar_negative_border_color
The bar_negative_border_color parameter sets the border color of the negative portion of a data bar.
The color can be specified as an Excel::Writer::XLSX color index or as a HTML style RGB hex number, as shown in the other examples.
Note: this property is only visible in Excel 2010 and later.
🪞 bar_negative_color_same
The bar_negative_color_same parameter sets the fill color for the negative portion of a data bar to be the same as the fill color for the positive portion of the data bar:
$worksheet->conditional_formatting( 'N3:N14',
{
type => 'data_bar',
bar_negative_color_same => 1,
bar_negative_border_color_same => 1
}
);Note: this property is only visible in Excel 2010 and later.
🪞 bar_negative_border_color_same
The bar_negative_border_color_same parameter sets the border color for the negative portion of a data bar to be the same as the border color for the positive portion of the data bar.
Note: this property is only visible in Excel 2010 and later.
🚫 bar_no_border
The bar_no_border parameter turns off the border of a data bar.
Note: this property is only visible in Excel 2010 and later, however the default in Excel 2007 is not to have a border. So there's that. 💀
⬅️➡️ bar_direction
The bar_direction parameter sets the direction for data bars. This property can be either left for left-to-right or right for right-to-left. If the property isn't set then Excel will adjust the position automatically based on the context:
$worksheet->conditional_formatting( 'J3:J14',
{
type => 'data_bar',
bar_direction => 'right'
}
);Note: this property is only visible in Excel 2010 and later.
🎯 bar_axis_position
The bar_axis_position parameter sets the position within the cells for the axis that is shown in data bars when there are negative values to display. The property can be either middle or none. If the property isn't set then Excel will position the axis based on the range of positive and negative values.
Note: this property is only visible in Excel 2010 and later.
🌈 bar_axis_color
The bar_axis_color parameter sets the color for the axis that is shown in data bars when there are negative values to display.
The color can be specified as an Excel::Writer::XLSX color index or as a HTML style RGB hex number, as shown in the other examples.
Note: this property is only visible in Excel 2010 and later.
data_bar_2010 💅
okay so the data_bar_2010 parameter makes your data bars look like the Excel 2010 style, even when you're not using any of the other Excel 2010 specific properties. why would you want that? consistency, bestie!! it's giving "all my data bars match" energy, and honestly? iconic.
$worksheet->conditional_formatting( 'L3:L14',
{
type => 'data_bar',
data_bar_2010 => 1
}
);heads up 🫶 this property is only visible in Excel 2010 and later. older versions literally cannot see it, no cap.
stop_if_true 🛑
the stop_if_true parameter, if you set it to a true value, turns on the "stop if true" feature on the conditional formatting rule. translation? once a cell hits the conditions for this rule, subsequent rules are not examined for that cell. it's the bouncer at the club — once you're in, the rest of the line doesn't matter 💀
✨ Conditional Formatting Examples ✨
Example 1. Highlight cells greater than an integer value.
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'cell',
criteria => 'greater than',
value => 5,
format => $format,
}
);Example 2. Highlight cells greater than a value in a reference cell.
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'cell',
criteria => 'greater than',
value => '$H$1',
format => $format,
}
);Example 3. Highlight cells greater than a certain date:
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'date',
criteria => 'greater than',
value => '2011-01-01T',
format => $format,
}
);Example 4. Highlight cells with a date in the last seven days:
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'time_period',
criteria => 'last 7 days',
format => $format,
}
);Example 5. Highlight cells with strings starting with the letter "b":
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'text',
criteria => 'begins with',
value => 'b',
format => $format,
}
);Example 6. Highlight cells that are 1 std deviation above the average for the range:
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'average',
format => $format,
}
);Example 7. Highlight duplicate cells in a range:
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'duplicate',
format => $format,
}
);Example 8. Highlight unique cells in a range.
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'unique',
format => $format,
}
);Example 9. Highlight the top 10 cells.
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'top',
value => 10,
format => $format,
}
);Example 10. Highlight blank cells.
$worksheet->conditional_formatting( 'A1:F10',
{
type => 'blanks',
format => $format,
}
);Example 11. Set traffic light icons in 3 cells:
$worksheet->conditional_formatting( 'A1:C1',
{
type => 'icon_set',
icon_style => '3_traffic_lights',
}
);also!! peep the conditional_format.pl example program in EXAMPLES if you wanna see even more, iykyk 🚦💖
✨ SPARKLINES IN EXCEL ✨
okay bestie, sparklines are literally the cutest lil feature — they're an Excel 2010+ thing where you get to tuck a teeny tiny chart inside a worksheet cell 🌸 they're so good for showing visual trends in your data in a compact format, like a whole vibe check for your numbers at a glance 💖
In Excel::Writer::XLSX you sprinkle sparklines into cells with the add_sparkline() worksheet method:
$worksheet->add_sparkline(
{
location => 'F2',
range => 'Sheet1!A2:E2',
type => 'column',
style => 12,
}
);Note: Sparklines are a feature of Excel 2010+ only. You can write them to an XLSX file that gets read by Excel 2007, but bestie… they just won't be displayed. 😭 nothing, gone, invisible.
🎀 add_sparkline( { parameter => 'value', ... } ) 🎀
The add_sparkline() worksheet method is the one that adds sparklines to a cell — or to a whole range of cells, if you're feeling ambitious 💅
The parameters to add_sparkline() must be passed in a hash ref. The main sparkline parameters are:
location (required)
range (required)
type
style
markers
negative_points
axis
reverseOther, less commonly used parameters are:
high_point
low_point
first_point
last_point
max
min
empty_cells
show_hidden
date_axis
weight
series_color
negative_color
markers_color
first_color
last_color
high_color
low_colorThese parameters are explained in the sections below 👇
📍 location
This is the cell where the sparkline will be displayed:
location => 'F1'The "location" should be a single cell. (For multiple cells, scroll down to "Grouped Sparklines" babe 🫶)
To specify the location in row-column notation, use the xl_rowcol_to_cell() function from the Excel::Writer::XLSX::Utility module:
use Excel::Writer::XLSX::Utility ':rowcol';
...
location => xl_rowcol_to_cell( 0, 5 ), # F1📊 range
This specifies the cell data range that the sparkline will plot:
$worksheet->add_sparkline(
{
location => 'F1',
range => 'A1:E1',
}
);The "range" should be a 2D array. (For 3D arrays of cells, see "Grouped Sparklines" below ✨)
If "range" is not on the same worksheet, you can specify its location using the usual Excel notation:
range => 'Sheet1!A1:E1',If the worksheet contains spaces or special characters, you should quote the worksheet name in the same way that Excel does:
range => q('Monthly Data'!A1:E1),To specify the location in row-column notation, use the xl_range() or xl_range_formula() functions from the Excel::Writer::XLSX::Utility module:
use Excel::Writer::XLSX::Utility ':rowcol';
...
range => xl_range( 1, 1, 0, 4 ), # 'A1:E1'
range => xl_range_formula( 'Sheet1', 0, 0, 0, 4 ), # 'Sheet1!A2:E2'🌈 type
Specifies the type of sparkline. There are 3 available sparkline types:
line (default)
column
win_lossFor example:
{
location => 'F1',
range => 'A1:E1',
type => 'column',
}👑 style
Excel provides 36 built-in Sparkline styles in 6 groups of 6. The "style" parameter can be used to replicate these, and it should be a corresponding number from 1 .. 36.
{
location => 'A14',
range => 'Sheet2!A2:J2',
style => 3,
}The style number starts in the top left of the style grid and runs left to right. The default style is 1. And yes, it's possible to override the colour elements of the sparklines using the *_color parameters below 🎨
✨ markers
Turn on the markers for "line" style sparklines:
{
location => 'A6',
range => 'Sheet2!A1:J1',
markers => 1,
}Markers aren't shown in Excel for "column" and "win_loss" sparklines. that's just how it is 💀
🍓 negative_points
Highlight negative values in a sparkline range. This is usually required with "win_loss" sparklines:
{
location => 'A21',
range => 'Sheet2!A3:J3',
type => 'win_loss',
negative_points => 1,
}➖ axis
Display a horizontal axis in the sparkline:
{
location => 'A10',
range => 'Sheet2!A1:J1',
axis => 1,
}🔄 reverse
Plot the data from right-to-left instead of the default left-to-right:
{
location => 'A24',
range => 'Sheet2!A4:J4',
type => 'column',
reverse => 1,
}🧵 weight
Adjust the default line weight (thickness) for "line" style sparklines:
weight => 0.25,The weight value should be one of the following values allowed by Excel:
0.25 0.5 0.75
1 1.25
2.25
3
4.25
6🌟 high_point, low_point, first_point, last_point
Highlight points in a sparkline range:
high_point => 1,
low_point => 1,
first_point => 1,
last_point => 1,📈 max, min
Specify the maximum and minimum vertical axis values:
max => 0.5,
min => -0.5,As a special case, you can set the maximum and minimum to be for a group of sparklines rather than one:
max => 'group',See "Grouped Sparklines" below 💜
🕳️ empty_cells
Define how empty cells are handled in a sparkline:
empty_cells => 'zero',The available options are:
gaps— show empty cells as gaps (the default).zero— plot empty cells as0.connect— Connect points with a line ("line"type sparklines only).
🙈 show_hidden
Plot data in hidden rows and columns:
show_hidden => 1,Note, this option is off by default.
🗓️ date_axis
Specify an alternative date axis for the sparkline. This is useful if the data being plotted isn't at fixed width intervals:
{
location => 'F3',
range => 'A3:E3',
date_axis => 'A4:E4',
}The number of cells in the date range should correspond to the number of cells in the data range. math is math, bestie 🧮
🎨 series_color
It's possible to override the colour of a sparkline style using the following parameters:
series_color
negative_color
markers_color
first_color
last_color
high_color
low_colorThe color should be specified as a HTML style "#rrggbb" hex value:
{
location => 'A18',
range => 'Sheet2!A2:J2',
type => 'column',
series_color => '#E965E0',
}🫂 Grouped Sparklines
The add_sparkline() worksheet method can be used multiple times to write as many sparklines as are required in a worksheet.
However, it's sometimes necessary to group contiguous sparklines so that changes that are applied to one are applied to all. In Excel this is achieved by selecting a 3D range of cells for the data "range" and a 2D range of cells for the "location".
In Excel::Writer::XLSX, you can simulate this by passing an array refs of values to "location" and "range":
{
location => [ 'A27', 'A28', 'A29' ],
range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ],
markers => 1,
}squad goals, honestly 🌈
👀 Sparkline examples
See the "sparklines1.pl" and "sparklines2.pl" example programs in the "examples" directory of the distro. go look, they ate 💅✨
🎀 TABLES IN EXCEL 🎀
okay bestie, tables!! ✨ So a table in Excel is basically a way of grouping a range of cells into one single entity that's got common formatting, or that you can reference from formulas. Think of it as a lil squad for your cells. 💅 Tables can have column headers, autofilters, total rows, column formulas and default formatting. It's giving organized. It's giving put-together. We love.
For more info you can go peek at "An Overview of Excel Tables" <http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx>.
real talk tho, bestie 👀 — tables do NOT work inExcel::Writer::XLSXwhenset_optimization()mode is on. Like, at all. Don't try it. 💀
💖 add_table( $row1, $col1, $row2, $col2, { parameter => 'value', ... }) 💖
You add tables to a worksheet with the add_table() method, periodt:
$worksheet->add_table( 'B3:F7', { %parameters } );The data range can be specified in 'A1' notation or in 'row/col' notation (check the note about "Cell notation" for more on that):
$worksheet->add_table( 'B3:F7' );
# Same as:
$worksheet->add_table( 2, 1, 6, 5 );The last parameter in add_table() should be a hash ref holding the parameters that describe the table options and data. obsessed with a good hash ref tbh. The available parameters are:
data
autofilter
header_row
banded_columns
banded_rows
first_column
last_column
style
total_row
columns
nameEvery single one of these is detailed below, don't worry 🫶. And guess what — there are no required parameters, and the hash ref itself isn't even required if you're not specifying any options. lowkey chill of it.
🍓 data 🍓
The "data" parameter lets you specify the data in the cells of the table:
my $data = [
[ 'Apples', 10000, 5000, 8000, 6000 ],
[ 'Pears', 2000, 3000, 4000, 5000 ],
[ 'Bananas', 6000, 6000, 6500, 6000 ],
[ 'Oranges', 500, 300, 200, 700 ],
];
$worksheet->add_table( 'B3:F7', { data => $data } );But also?? you can write that table data separately, either as an array or as individual cells. Slay, flexible:
# These two statements are the same as the single statement above.
$worksheet->add_table( 'B3:F7' );
$worksheet->write_col( 'B4', $data );Writing the cell data separately is occasionally required when you need to control which "write_*()" method gets used to populate the cells, or if you wanna tweak the cell formatting yourself. iykyk.
The "data" structure should be an array ref of array refs holding the row data, like the example up there shows. ✨
🌸 header_row 🌸
The "header_row" parameter turns the header row of the table on or off. It's on by default:
$worksheet->add_table( 'B4:F7', { header_row => 0 } ); # Turn header off.The header row will contain default captions like "Column 1", "Column 2", etc. — but you can override those captions using the "columns" parameter below. 💅
🦋 autofilter 🦋
The "autofilter" parameter turns on or off the autofilter in the header row. Also on by default:
$worksheet->add_table( 'B3:F7', { autofilter => 0 } ); # Turn autofilter off.Heads up: the "autofilter" is only shown if the "header_row" is on. And filters within the table? Not supported. nope. 🙈
✨ banded_rows ✨
The "banded_rows" parameter creates rows of alternating colour in the table. It's on by default:
$worksheet->add_table( 'B3:F7', { banded_rows => 0 } );✨ banded_columns ✨
The "banded_columns" parameter creates columns of alternating colour in the table. It's off by default:
$worksheet->add_table( 'B3:F7', { banded_columns => 1 } );👑 first_column 👑
The "first_column" parameter highlights the first column of the table. What kind of highlighting, you ask? Depends on the "style" of the table — could be bold text or a different colour. It's off by default:
$worksheet->add_table( 'B3:F7', { first_column => 1 } );👑 last_column 👑
The "last_column" parameter highlights the last column of the table. Same deal — the highlighting type depends on the table "style", might be bold text or a diff colour. Also off by default:
$worksheet->add_table( 'B3:F7', { last_column => 1 } );💜 style 💜
The "style" parameter sets the style of the table. Use standard Excel table format names, and please respect the capitalisation bestie, matching matters:
$worksheet11->add_table(
'B3:F7',
{
data => $data,
style => 'Table Style Light 11',
}
);The default table style is 'Table Style Medium 9'.
And you can turn the table style off entirely by setting it to 'None':
$worksheet11->add_table( 'B3:F7', { style => 'None' } );⭐ name ⭐
By default tables are named "Table1", "Table2", etc. The "name" parameter sets the name of the table:
$worksheet->add_table( 'B3:F7', { name => 'SalesData' } );important bestie!! 😭 If you override the table name you must make sure it doesn't clash with an existing table name, and that it follows Excel's requirements for table names <http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names>.
If you need to know the name of the table (say, to use it in a formula), you can grab it like this:
my $table = $worksheet2->add_table( 'B3:F7' );
my $table_name = $table->{_name};🌈 total_row 🌈
The "total_row" parameter turns on the total row in the last row of a table. It stands out from the other rows with a different formatting, plus dropdown "SUBTOTAL" functions. fancy.
$worksheet->add_table( 'B3:F7', { total_row => 1 } );The default total row doesn't have any captions or functions — you have to specify those via the "columns" parameter below. no freebies. 💅
🧚 columns 🧚
The "columns" parameter sets properties for columns within the table.
The sub-properties you can set are:
header
formula
total_string
total_function
total_value
format
header_formatThe column data must be specified as an array ref of hash refs. For example, to override the default 'Column n' style table headers:
$worksheet->add_table(
'B3:F7',
{
data => $data,
columns => [
{ header => 'Product' },
{ header => 'Quarter 1' },
{ header => 'Quarter 2' },
{ header => 'Quarter 3' },
{ header => 'Quarter 4' },
]
}
);If you don't wanna specify properties for a specific column, just pass an empty hash ref and the defaults get applied:
...
columns => [
{ header => 'Product' },
{ header => 'Quarter 1' },
{ }, # Defaults to 'Column 3'.
{ header => 'Quarter 3' },
{ header => 'Quarter 4' },
]
...Column formulas can be applied using the "formula" column property:
$worksheet8->add_table(
'B3:G7',
{
data => $data,
columns => [
{ header => 'Product' },
{ header => 'Quarter 1' },
{ header => 'Quarter 2' },
{ header => 'Quarter 3' },
{ header => 'Quarter 4' },
{
header => 'Year',
formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
},
]
}
);The Excel 2007 "[#This Row]" and Excel 2010 "@" structural references are supported inside the formula. 🫶
So yeah, like we said, the "total_row" table parameter turns on the "Total" row in the table but it does not populate it with any defaults. Total captions and functions have to be specified via the "columns" property, using the "total_string", "total_function" and "total_value" sub properties:
$worksheet10->add_table(
'B3:F8',
{
data => $data,
total_row => 1,
columns => [
{ header => 'Product', total_string => 'Totals' },
{ header => 'Quarter 1', total_function => 'sum' },
{ header => 'Quarter 2', total_function => 'sum' },
{ header => 'Quarter 3', total_function => 'sum' },
{ header => 'Quarter 4', total_function => 'sum' },
]
}
);The supported totals row "SUBTOTAL" functions are:
average
count_nums
count
max
min
std_dev
sum
varUser defined functions or formulas aren't supported. 💀 just so you know.
It's also possible to set a calculated value for the "total_function" using the "total_value" sub property. This is only necessary when you're creating workbooks for applications that can't calculate the value of formulas automatically. It's similar to setting the "value" optional property in write_formula():
$worksheet10->add_table(
'B3:F8',
{
data => $data,
total_row => 1,
columns => [
{ total_string => 'Totals' },
{ total_function => 'sum', total_value => 100 },
{ total_function => 'sum', total_value => 200 },
{ total_function => 'sum', total_value => 100 },
{ total_function => 'sum', total_value => 400 },
]
}
);Formatting can also be applied to columns — to the column data using "format", and to the header using "header_format":
my $currency_format = $workbook->add_format( num_format => '$#,##0' );
$worksheet->add_table(
'B3:D8',
{
data => $data,
total_row => 1,
columns => [
{ header => 'Product', total_string => 'Totals' },
{
header => 'Quarter 1',
total_function => 'sum',
format => $currency_format,
},
{
header => 'Quarter 2',
header_format => $bold,
total_function => 'sum',
format => $currency_format,
},
]
}
);Standard Excel::Writer::XLSX format objects can be used here. However 👀 — they should be limited to numerical formats for the columns, and simple formatting like text wrap for the headers. Overriding other table formatting may produce inconsistent results. Consider yourself warned, bestie. 🙈
💖 FORMULAS AND FUNCTIONS IN EXCEL 💖
✨ Introduction ✨
okay so bestie, here's your lil crash course on formulas and functions in Excel and in Excel::Writer::XLSX. and honestly? it's giving "easier than you think". 🫶
A formula is a string that begins with an equals sign:
'=A1+B1'
'=AVERAGE(1, 2, 3)'The formula can contain numbers, strings, boolean values, cell references, cell ranges and functions. Named ranges are not supported, sorry babe 😭. Formulas should be written as they appear in Excel, that is cells and functions must be in uppercase.
Cells in Excel are referenced using the A1 notation system where the column is designated by a letter and the row by a number. Columns range from A to XFD i.e. 0 to 16384, rows range from 1 to 1048576. The Excel::Writer::XLSX::Utility module that is included in the distro contains helper functions for dealing with A1 notation, for example:
use Excel::Writer::XLSX::Utility;
( $row, $col ) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
$str = xl_rowcol_to_cell( 1, 2 ); # C2The Excel $ notation in cell references is also supported 💅. This allows you to specify whether a row or column is relative or absolute. This only has an effect if the cell is copied. The following examples show relative and absolute values.
'=A1' # Column and row are relative
'=$A1' # Column is absolute and row is relative
'=A$1' # Column is relative and row is absolute
'=$A$1' # Column and row are absoluteFormulas can also refer to cells in other worksheets of the current workbook. For example:
'=Sheet2!A1'
'=Sheet2!A1:A5'
'=Sheet2:Sheet3!A1'
'=Sheet2:Sheet3!A1:A5'
q{='Test Data'!A1}
q{='Test Data1:Test Data2'!A1}The sheet reference and the cell reference are separated by ! the exclamation mark symbol. If worksheet names contain spaces, commas or parentheses then Excel requires that the name is enclosed in single quotes, just like the last two examples up there 👆. In order to avoid using a lot of escape characters you can use the quote operator q{} to protect the quotes. See "perlop" in the main Perl documentation. Only valid sheet names that have been added using the add_worksheet() method can be used in formulas. You cannot reference external workbooks. periodt 🚫
The following list covers the operators that are available in Excel's formulas. The majority of the operators are the same as Perl's, differences are indicated:
- Arithmetic operators:
+— Addition —1+2-— Subtraction —2-1*— Multiplication —2*3/— Division —1/4^— Exponentiation —2^3# Equivalent to**-— Unary minus —-(1+2)%— Percent (Not modulus) —13%
- Comparison operators:
=— Equal to —A1 = B1# Equivalent to==<>— Not equal to —A1 <> B1# Equivalent to!=>— Greater than —A1 > B1<— Less than —A1 < B1>=— Greater than or equal to —A1 >= B1<=— Less than or equal to —A1 <= B1
- String operator:
&— Concatenation —"Hello " & "World!"# [1]
- Reference operators:
:— Range operator —A1:A4# [2],— Union operator —SUM(1, 2+2, B3)# [3]
Notes:
- [1]: Equivalent to
"Hello " . "World!"in Perl. - [2]: This range is equivalent to cells
A1,A2,A3andA4. - [3]: The comma behaves like the list separator in Perl.
The range and comma operators can have different symbols in non-English versions of Excel, see below. 👀
For a general introduction to Excel's formulas and an explanation of the syntax of the function refer to the Excel help files or the following: http://office.microsoft.com/en-us/assistance/CH062528031033.aspx.
In most cases a formula in Excel can be used directly in the write_formula method. However, there are a few potential issues and differences that the user should be aware of. These are explained in the following sections. 🫡
🌍 Non US Excel functions and syntax 🌍
Excel stores formulas in the format of the US English version, regardless of the language or locale of the end-user's version of Excel. So all formula function names written using Excel::Writer::XLSX must be in English:
worksheet->write_formula('A1', '=SUM(1, 2, 3)'); # OK
worksheet->write_formula('A2', '=SOMME(1, 2, 3)'); # French. Error on load.Also, formulas must be written with the US style separator/range operator which is a comma (not semi-colon). Therefore a formula with multiple values should be written as follows:
worksheet->write_formula('A1', '=SUM(1, 2, 3)'); # OK
worksheet->write_formula('A2', '=SUM(1; 2; 3)'); # Semi-colon. Error on load.If you have a non-English version of Excel you can use the following multi-lingual Formula Translator (http://en.excel-translator.de/language/) to help you convert the formula. It can also replace semi-colons with commas. slay ✨
🕰️ Formulas added in Excel 2010 and later 🕰️
Excel 2010 and later added functions which weren't defined in the original file specification. These functions are referred to by Microsoft as future functions. Examples of these functions are "ACOT", "CHISQ.DIST.RT" , "CONFIDENCE.NORM", "STDEV.P", "STDEV.S" and "WORKDAY.INTL".
When written using write_formula() these functions need to be fully qualified with a _xlfn. (or other) prefix as they are shown the list below. For example:
worksheet->write_formula('A1', '=_xlfn.STDEV.S(B1:B10)')They will appear without the prefix in Excel. 🪄
The following list is taken from the MS XLSX extensions documentation on future functions: http://msdn.microsoft.com/en-us/library/dd907480%28v=office.12%29.aspx:
_xlfn.ACOT
_xlfn.ACOTH
_xlfn.AGGREGATE
_xlfn.ARABIC
_xlfn.ARRAYTOTEXT
_xlfn.BASE
_xlfn.BETA.DIST
_xlfn.BETA.INV
_xlfn.BINOM.DIST
_xlfn.BINOM.DIST.RANGE
_xlfn.BINOM.INV
_xlfn.BITAND
_xlfn.BITLSHIFT
_xlfn.BITOR
_xlfn.BITRSHIFT
_xlfn.BITXOR
_xlfn.CEILING.MATH
_xlfn.CEILING.PRECISE
_xlfn.CHISQ.DIST
_xlfn.CHISQ.DIST.RT
_xlfn.CHISQ.INV
_xlfn.CHISQ.INV.RT
_xlfn.CHISQ.TEST
_xlfn.COMBINA
_xlfn.CONCAT
_xlfn.CONFIDENCE.NORM
_xlfn.CONFIDENCE.T
_xlfn.COT
_xlfn.COTH
_xlfn.COVARIANCE.P
_xlfn.COVARIANCE.S
_xlfn.CSC
_xlfn.CSCH
_xlfn.DAYS
_xlfn.DECIMAL
ECMA.CEILING
_xlfn.ERF.PRECISE
_xlfn.ERFC.PRECISE
_xlfn.EXPON.DIST
_xlfn.F.DIST
_xlfn.F.DIST.RT
_xlfn.F.INV
_xlfn.F.INV.RT
_xlfn.F.TEST
_xlfn.FILTERXML
_xlfn.FLOOR.MATH
_xlfn.FLOOR.PRECISE
_xlfn.FORECAST.ETS
_xlfn.FORECAST.ETS.CONFINT
_xlfn.FORECAST.ETS.SEASONALITY
_xlfn.FORECAST.ETS.STAT
_xlfn.FORECAST.LINEAR
_xlfn.FORMULATEXT
_xlfn.GAMMA
_xlfn.GAMMA.DIST
_xlfn.GAMMA.INV
_xlfn.GAMMALN.PRECISE
_xlfn.GAUSS
_xlfn.HYPGEOM.DIST
_xlfn.IFNA
_xlfn.IFS
_xlfn.IMAGE
_xlfn.IMCOSH
_xlfn.IMCOT
_xlfn.IMCSC
_xlfn.IMCSCH
_xlfn.IMSEC
_xlfn.IMSECH
_xlfn.IMSINH
_xlfn.IMTAN
_xlfn.ISFORMULA
ISO.CEILING
_xlfn.ISOMITTED
_xlfn.ISOWEEKNUM
_xlfn.LET
_xlfn.LOGNORM.DIST
_xlfn.LOGNORM.INV
_xlfn.MAXIFS
_xlfn.MINIFS
_xlfn.MODE.MULT
_xlfn.MODE.SNGL
_xlfn.MUNIT
_xlfn.NEGBINOM.DIST
NETWORKDAYS.INTL
_xlfn.NORM.DIST
_xlfn.NORM.INV
_xlfn.NORM.S.DIST
_xlfn.NORM.S.INV
_xlfn.NUMBERVALUE
_xlfn.PDURATION
_xlfn.PERCENTILE.EXC
_xlfn.PERCENTILE.INC
_xlfn.PERCENTRANK.EXC
_xlfn.PERCENTRANK.INC
_xlfn.PERMUTATIONA
_xlfn.PHI
_xlfn.POISSON.DIST
_xlfn.QUARTILE.EXC
_xlfn.QUARTILE.INC
_xlfn.QUERYSTRING
_xlfn.RANK.AVG
_xlfn.RANK.EQ
_xlfn.RRI
_xlfn.SEC
_xlfn.SECH
_xlfn.SHEET
_xlfn.SHEETS
_xlfn.SKEW.P
_xlfn.STDEV.P
_xlfn.STDEV.S
_xlfn.T.DIST
_xlfn.T.DIST.2T
_xlfn.T.DIST.RT
_xlfn.T.INV
_xlfn.T.INV.2T
_xlfn.T.TEST
_xlfn.TEXTAFTER
_xlfn.TEXTBEFORE
_xlfn.TEXTJOIN
_xlfn.UNICHAR
_xlfn.UNICODE
_xlfn.VALUETOTEXT
_xlfn.VAR.P
_xlfn.VAR.S
_xlfn.WEBSERVICE
_xlfn.WEIBULL.DIST
WORKDAY.INTL
_xlfn.XMATCH
_xlfn.XOR
_xlfn.Z.TESTheads up babe 👑 — a couple of these have no_xlfn.prefix at all (ECMA.CEILING,ISO.CEILING,NETWORKDAYS.INTL,WORKDAY.INTL), so copy them exactly as they're written here. no fixing, no guessing. 💅
📊 Using Tables in Formulas 📊
Worksheet tables can be added with Excel::Writer::XLSX using the add_table() method:
worksheet->add_table('B3:F7', {options});By default tables are named "Table1", "Table2", etc., in the order that they are added. However it can also be set by the user using the "name" parameter:
worksheet->add_table('B3:F7', {'name': 'SalesData'});If you need to know the name of the table, for example to use it in a formula, you can get it as follows:
table = worksheet->add_table('B3:F7');
table_name = table->{_name};When used in a formula a table name such as "TableX" should be referred to as "TableX[]" (like a Perl array):
worksheet->write_formula('A5', '=VLOOKUP("Sales", Table1[], 2, FALSE');💀 Dealing with #NAME? errors 💀
If there is an error in the syntax of a formula it is usually displayed in Excel as "#NAME?". If you encounter an error like this you can debug it as follows:
- Lowkey test it first fr. Copy-paste that formula into an actual cell in Excel and make sure it works. And btw do this in Excel — not OpenOffice, not LibreOffice — because they lowkey have slightly different syntax and will gaslight you. 😭
- Make sure the formula is using commas instead of semi-colons, see "Non US Excel functions and syntax" above. Periodt.
- Make sure the formula is in English, see "Non US Excel functions and syntax" above. (Yes bestie, English, we're not doing localization drama right now.)
- Make sure that the formula doesn't contain an Excel 2010+ future function as listed in "Formulas added in Excel 2010 and later" above. If it does, make sure the correct prefix is used. 🫶
Finally, if you have completed all the previous steps and you're still getting a #NAME? error, you can peek at a valid Excel file to see what the correct syntax should be. Do this: create a valid formula in Excel and save the file, then examine the XML inside the unzipped file. ✨
The following shows how to do that using Linux unzip and libxml's xmllint <http://xmlsoft.org/xmllint.html> to format the XML for clarity:
$ unzip myfile.xlsx -d myfile
$ xmllint --format myfile/xl/worksheets/sheet1.xml | grep '<f>'
<f>SUM(1, 2, 3)</f>💖 Formula Results 💖
Okay so here's the tea: Excel::Writer::XLSX doesn't calculate the result of a formula. Instead it stores the value 0 as the formula result. It then sets a global flag in the XLSX file to say that all formulas and functions should be recalculated when the file is opened. 🎀
This is the method recommended in the Excel documentation and in general it works fine with spreadsheet applications. However, applications that don't have a facility to calculate formulas will only display the 0 results. Examples of such applications are Excel Viewer, PDF Converters, and some mobile device applications. 🙈
If required, it is also possible to specify the calculated result of the formula using the optional last value parameter in write_formula:
worksheet->write_formula('A1', '=2+2', num_format, 4);The value parameter can be a number, a string, a boolean string (TRUE or FALSE) or one of the following Excel error codes:
#DIV/0!
#N/A
#NAME?
#NULL!
#NUM!
#REF!
#VALUE!It is also possible to specify the calculated result of an array formula created with write_array_formula:
# Specify the result for a single cell range.
worksheet->write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}', format, 2005);However, using this parameter only writes a single value to the upper left cell in the result array. For a multi-cell array formula where the results are required, the other result values can be specified by using write_number() to write to the appropriate cell:
# Specify the results for a multi cell range.
worksheet->write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}', format, 15);
worksheet->write_number('A2', 12, format);
worksheet->write_number('A3', 14, format);🔥 WORKING WITH VBA MACROS 🔥
An Excel xlsm file is exactly the same as a xlsx file except that it includes an additional vbaProject.bin file which contains functions and/or macros. Excel uses a different extension to differentiate between the two file formats since files containing macros are usually subject to additional security checks. 👑
The vbaProject.bin file is a binary OLE COM container. This was the format used in older xls versions of Excel prior to Excel 2007. Unlike all of the other components of an xlsx/xlsm file the data isn't stored in XML format. Instead the functions and macros are stored as pre-parsed binary format. As such it wouldn't be feasible to define macros and create a vbaProject.bin file from scratch (at least not in the remaining lifespan and interest levels of the author). 💀
Instead a workaround is used to extract vbaProject.bin files from existing xlsm files and then add these to Excel::Writer::XLSX files.
🧚 The extract_vba utility 🧚
The extract_vba utility is used to extract the vbaProject.bin binary from an Excel 2007+ xlsm file. The utility is included in the Excel::Writer::XLSX bin directory and is also installed as a standalone executable file:
$ extract_vba macro_file.xlsm
Extracted: vbaProject.bin🌸 Adding the VBA macros to a Excel::Writer::XLSX file 🌸
Once the vbaProject.bin file has been extracted it can be added to the Excel::Writer::XLSX workbook using the add_vba_project() method:
$workbook->add_vba_project( './vbaProject.bin' );If the VBA file contains functions you can then refer to them in calculations using write_formula:
$worksheet->write_formula( 'A1', '=MyMortgageCalc(200000, 25)' );Excel files that contain functions and macros should use an xlsm extension or else Excel will complain and possibly not open the file: 😭
my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );It is also possible to assign a macro to a button that is inserted into a worksheet using the insert_button() method:
my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
...
$workbook->add_vba_project( './vbaProject.bin' );
$worksheet->insert_button( 'C2', { macro => 'my_macro' } );It may be necessary to specify a more explicit macro name prefixed by the workbook VBA name as follows:
$worksheet->insert_button( 'C2', { macro => 'ThisWorkbook.my_macro' } );See the macros.pl from the examples directory for a working example. ✨
Note: Button is the only VBA Control supported by Excel::Writer::XLSX. Due to the large effort in implementation (1+ man months) it is unlikely that any other form elements will be added in the future.
💜 Setting the VBA codenames 💜
VBA macros generally refer to workbook and worksheet objects. If the VBA codenames aren't specified then Excel::Writer::XLSX will use the Excel defaults of ThisWorkbook and Sheet1, Sheet2 etc.
If the macro uses other codenames you can set them using the workbook and worksheet set_vba_name() methods as follows:
$workbook->set_vba_name( 'MyWorkbook' );
$worksheet->set_vba_name( 'MySheet' );You can find the names that are used in the VBA editor or by unzipping the xlsm file and grepping the files. The following shows how to do that using libxml's xmllint <http://xmlsoft.org/xmllint.html> to format the XML for clarity:
$ unzip myfile.xlsm -d myfile
$ xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"
<workbookPr codeName="MyWorkbook" defaultThemeVersion="124226"/>
<sheetPr codeName="MySheet"/>Note: This step is particularly important for macros created with non-English versions of Excel. 🌈
🙈 What to do if it doesn't work 🙈
This feature should be considered experimental and there is no guarantee that it will work in all cases. Some effort may be required and some knowledge of VBA will certainly help. If things don't work out here are some things to try:
- Start with a simple macro file, ensure that it works and then add complexity.
- Try to extract the macros from an Excel 2007 file. The method should work with macros from later versions (it was also tested with Excel 2010 macros). However there may be features in the macro files of more recent version of Excel that aren't backward compatible.
- Check the code names that macros use to refer to the workbook and worksheets (see the previous section above). In general VBA uses a code name of
ThisWorkbookto refer to the current workbook and the sheet name (such asSheet1) to refer to the worksheets. These are the defaults used byExcel::Writer::XLSX. If the macro uses other names then you can specify these using the workbook and worksheetset_vba_name()methods:
$workbook>set_vba_name( 'MyWorkbook' );
$worksheet->set_vba_name( 'MySheet' );🌟 EXAMPLES 🌟
See Excel::Writer::XLSX::Examples for a full list of examples. 👑
⭐ Example 1 ⭐
The following example shows some of the basic features of Excel::Writer::XLSX.
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
# Create a new workbook called simple.xlsx and add a worksheet
my $workbook = Excel::Writer::XLSX->new( 'simple.xlsx' );
my $worksheet = $workbook->add_worksheet();
# The general syntax is write($row, $column, $token). Note that row and
# column are zero indexed
# Write some text
$worksheet->write( 0, 0, 'Hi Excel!' );
# Write some numbers
$worksheet->write( 2, 0, 3 );
$worksheet->write( 3, 0, 3.00000 );
$worksheet->write( 4, 0, 3.00001 );
$worksheet->write( 5, 0, 3.14159 );
# Write some formulas
$worksheet->write( 7, 0, '=A3 + A6' );
$worksheet->write( 8, 0, '=IF(A5>3,"Yes", "No")' );
# Write a hyperlink
my $hyperlink_format = $workbook->add_format(
color => 'blue',
underline => 1,
);
$worksheet->write( 10, 0, 'http://www.perl.com/', $hyperlink_format );
$workbook->close();🦋 Example 2 🦋
The following is a general example which demonstrates some features of working with multiple worksheets.
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new( 'regions.xlsx' );
# Add some worksheets
my $north = $workbook->add_worksheet( 'North' );
my $south = $workbook->add_worksheet( 'South' );
my $east = $workbook->add_worksheet( 'East' );
my $west = $workbook->add_worksheet( 'West' );
# Add a Format
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'blue' );
# Add a caption to each worksheet
for my $worksheet ( $workbook->sheets() ) {
$worksheet->write( 0, 0, 'Sales', $format );
}
# Write some data
$north->write( 0, 1, 200000 );
$south->write( 0, 1, 100000 );
$east->write( 0, 1, 150000 );
$west->write( 0, 1, 100000 );
# Set the active worksheet
$south->activate();
# Set the width of the first column
$south->set_column( 0, 0, 20 );
# Set the active cell
$south->set_selection( 0, 1 );
$workbook->close();🎀 Example 3 🎀
Example of how to add conditional formatting to an Excel::Writer::XLSX file. The example below highlights cells that have a value greater than or equal to 50 in red and cells below that value in green. 🔥
#!/usr/bin/perl
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'conditional_format.xlsx' );
my $worksheet = $workbook->add_worksheet();# This example below highlights cells that have a value greater than or
# equal to 50 in red and cells below that value in green.
# Light red fill with dark red text.
my $format1 = $workbook->add_format(
bg_color => '#FFC7CE',
color => '#9C0006',
);
# Green fill with dark green text.
my $format2 = $workbook->add_format(
bg_color => '#C6EFCE',
color => '#006100',
);
# Some sample data to run the conditional formatting against.
my $data = [
[ 34, 72, 38, 30, 75, 48, 75, 66, 84, 86 ],
[ 6, 24, 1, 84, 54, 62, 60, 3, 26, 59 ],
[ 28, 79, 97, 13, 85, 93, 93, 22, 5, 14 ],
[ 27, 71, 40, 17, 18, 79, 90, 93, 29, 47 ],
[ 88, 25, 33, 23, 67, 1, 59, 79, 47, 36 ],
[ 24, 100, 20, 88, 29, 33, 38, 54, 54, 88 ],
[ 6, 57, 88, 28, 10, 26, 37, 7, 41, 48 ],
[ 52, 78, 1, 96, 26, 45, 47, 33, 96, 36 ],
[ 60, 54, 81, 66, 81, 90, 80, 93, 12, 55 ],
[ 70, 5, 46, 14, 71, 19, 66, 36, 41, 21 ],
];
my $caption = 'Cells with values >= 50 are in light red. '
. 'Values < 50 are in light green';
# Write the data.
$worksheet->write( 'A1', $caption );
$worksheet->write_col( 'B3', $data );
# Write a conditional format over a range.
$worksheet->conditional_formatting( 'B3:K12',
{
type => 'cell',
criteria => '>=',
value => 50,
format => $format1,
}
);
# Write another conditional format over the same range.
$worksheet->conditional_formatting( 'B3:K12',
{
type => 'cell',
criteria => '<',
value => 50,
format => $format2,
}
);
$workbook->close();Example 4 ✨
Okay so this next one is the functions glow-up bestie — a lil' stats sheet where every formula is doing its main character moment 💅
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
# Create a new workbook and add a worksheet
my $workbook = Excel::Writer::XLSX->new( 'stats.xlsx' );
my $worksheet = $workbook->add_worksheet( 'Test data' );
# Set the column width for columns 1
$worksheet->set_column( 0, 0, 20 );
# Create a format for the headings
my $format = $workbook->add_format();
$format->set_bold();
# Write the sample data
$worksheet->write( 0, 0, 'Sample', $format );
$worksheet->write( 0, 1, 1 );
$worksheet->write( 0, 2, 2 );
$worksheet->write( 0, 3, 3 );
$worksheet->write( 0, 4, 4 );
$worksheet->write( 0, 5, 5 );
$worksheet->write( 0, 6, 6 );
$worksheet->write( 0, 7, 7 );
$worksheet->write( 0, 8, 8 );
$worksheet->write( 1, 0, 'Length', $format );
$worksheet->write( 1, 1, 25.4 );
$worksheet->write( 1, 2, 25.4 );
$worksheet->write( 1, 3, 24.8 );
$worksheet->write( 1, 4, 25.0 );
$worksheet->write( 1, 5, 25.3 );
$worksheet->write( 1, 6, 24.9 );
$worksheet->write( 1, 7, 25.2 );
$worksheet->write( 1, 8, 24.8 );
# Write some statistical functions
$worksheet->write( 4, 0, 'Count', $format );
$worksheet->write( 4, 1, '=COUNT(B1:I1)' );
$worksheet->write( 5, 0, 'Sum', $format );
$worksheet->write( 5, 1, '=SUM(B2:I2)' );
$worksheet->write( 6, 0, 'Average', $format );
$worksheet->write( 6, 1, '=AVERAGE(B2:I2)' );
$worksheet->write( 7, 0, 'Min', $format );
$worksheet->write( 7, 1, '=MIN(B2:I2)' );
$worksheet->write( 8, 0, 'Max', $format );
$worksheet->write( 8, 1, '=MAX(B2:I2)' );
$worksheet->write( 9, 0, 'Standard Deviation', $format );
$worksheet->write( 9, 1, '=STDEV(B2:I2)' );
$worksheet->write( 10, 0, 'Kurtosis', $format );
$worksheet->write( 10, 1, '=KURT(B2:I2)' );
$workbook->close();Example 5 🦋
And THIS one? It's the little file-converter that could. It takes a tab-separated file called "tab.txt" and turns it into an Excel file called "tab.xlsx". Glow up achieved, no notes 💖
#!/usr/bin/perl -w
use strict;
use Excel::Writer::XLSX;
open( TABFILE, 'tab.txt' ) or die "tab.txt: $!";
my $workbook = Excel::Writer::XLSX->new( 'tab.xlsx' );
my $worksheet = $workbook->add_worksheet();
# Row and column are zero indexed
my $row = 0;
while ( <TABFILE> ) {
chomp;
# Split on single tab
my @fields = split( '\t', $_ );
my $col = 0;
for my $token ( @fields ) {
$worksheet->write( $row, $col, $token );
$col++;
}
$row++;
}
$workbook->close();⚠️ NOTE: This is a simple conversion program for illustrative purposes only bestie — it is not the rigorous baddie you want for real work. For converting a CSV or Tab separated or any other type of delimited text file to Excel, the recommendation is the much more rigorouscsv2xlsprogram, which is part of H.Merijn Brand'sText::CSV_XSmodule distro. She ate and left no crumbs, fr fr.
See the examples/csv2xls link here: http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST.
Additional Examples 🎀
So the standard Excel::Writer::XLSX distribution ships with a whole folder of example files, and like… they're iconic. Each one shows off a different feature or option of the module. Pop over to Excel::Writer::XLSX::Examples for all the deets 👑
Getting started
===============
a_simple.pl A simple demo of some of the features.
bug_report.pl A template for submitting bug reports.
demo.pl A demo of some of the available features.
formats.pl All the available formatting on several worksheets.
regions.pl A simple example of multiple worksheets.
stats.pl Basic formulas and functions.…and there's more where that came from, so hold on bestie, we're not done yet 💜
💅 Intermediate
okay so these are your step-it-up examples bestie, the ones where you actually start doing real stuff ✨
autofit.pl— simulates worksheet autofit, like Excel's "ooh lemme resize this column for u" behavior 🎀autofilter.pl— worksheet autofilters, the lil dropdown arrows situationarray_formula.pl— showing you how to write array formulas 🧚cgi.pl— a simple CGI program, yes literally, we're in the browser nowchart_area.pl— demo of area style chartschart_bar.pl— demo of bar (vertical histogram) style chartschart_column.pl— demo of column (histogram) style chartschart_line.pl— demo of line style chartschart_pie.pl— demo of pie style charts 🥧chart_doughnut.pl— demo of doughnut style charts 🍩 obsessedchart_radar.pl— demo of radar style chartschart_scatter.pl— demo of scatter style chartschart_secondary_axis.pl— demo of a line chart with a secondary axischart_combined.pl— demo of a combined column and line chartchart_pareto.pl— demo of a combined Pareto chartchart_stock.pl— demo of stock style chartschart_data_table.pl— demo of a chart with a data table on the axischart_data_tools.pl— demo of charts with data highlighting optionschart_data_labels.pl— demo of standard and custom chart data labelschart_clustered.pl— demo of a chart with a clustered axischart_styles.pl— demo of the available chart styleschart_gauge.pl— demo of a gauge style chartcolors.pl— demo of the colour palette and named colours 🌈comments1.pl— add comments to worksheet cellscomments2.pl— add comments with advanced optionsconditional_format.pl— add conditional formats to a range of cellsdata_validate.pl— an example of data validation and dropdown listsdate_time.pl— write dates and times withwrite_date_time()defined_name.pl— example of how to create defined namesdiag_border.pl— a simple example of diagonal cell bordersdynamic_arrays.pl— example of using new Excel 365 dynamic functionsembedded_images.pl— example of embedding images in worksheet cellsfilehandle.pl— examples of working with filehandlesheaders.pl— examples of worksheet headers and footershide_row_col.pl— example of hiding rows and columns 🙈hide_sheet.pl— simple example of hiding a worksheethyperlink1.pl— shows how to create web hyperlinkshyperlink2.pl— examples of internal and external hyperlinksindent.pl— an example of cell indentationignore_errors.pl— an example of turning off worksheet cells errors/warningslambda.pl— example of using the Excel 365LAMBDA()functionmacros.pl— an example of adding macros from an existing filemerge1.pl— a simple example of cell mergingmerge2.pl— a simple example of cell merging with formattingmerge3.pl— add hyperlinks to merged cellsmerge4.pl— an advanced example of merging with formattingmerge5.pl— an advanced example of merging with formattingmerge6.pl— an example of merging with Unicode stringsmod_perl1.pl— a simple mod_perl 1 programmod_perl2.pl— a simple mod_perl 2 programoutline.pl— an example of outlines and groupingoutline_collapsed.pl— an example of collapsed outlinespanes.pl— an example of how to create panesproperties.pl— add document properties to a workbookprotection.pl— example of cell locking and formula hidingrich_strings.pl— example of strings with multiple formatsright_to_left.pl— change default sheet direction to right to leftsales.pl— an example of a simple sales spreadsheetshape1.pl— insert shapes in worksheetshape2.pl— insert shapes in worksheet. With properties.shape3.pl— insert shapes in worksheet. Scaled.shape4.pl— insert shapes in worksheet. With modification.shape5.pl— insert shapes in worksheet. With connections.shape6.pl— insert shapes in worksheet. With connections.shape7.pl— insert shapes in worksheet. One to many connections.shape8.pl— insert shapes in worksheet. One to many connections.shape_all.pl— demo of all the available shape and connector typessparklines1.pl— simple sparklines demosparklines2.pl— sparklines demo showing formatting optionsstats_ext.pl— same asstats.plwith external referencesstocks.pl— demonstrates conditional formattingwatermark.pl— example of how to set a watermark image for a worksheetbackground.pl— example of how to set the background image for a worksheettab_colors.pl— example of how to set worksheet tab colourstables.pl— add Excel tables to a worksheetwrite_handler1.pl— example of extending thewrite()method. Step 1.write_handler2.pl— example of extending thewrite()method. Step 2.write_handler3.pl— example of extending thewrite()method. Step 3.write_handler4.pl— example of extending thewrite()method. Step 4.write_to_scalar.pl— example of writing an Excel file to a Perl scalar
🌸 Unicode
the multilingual slay 💖 these ones show you how to write in encodings that aren't just boring ASCII:
unicode_2022_jp.pl— Japanese:ISO-2022-JP.unicode_8859_11.pl— Thai:ISO-8859_11.unicode_8859_7.pl— Greek:ISO-8859_7.unicode_big5.pl— Chinese:BIG5.unicode_cp1251.pl— Russian:CP1251.unicode_cp1256.pl— Arabic:CP1256.unicode_cyrillic.pl— Russian:Cyrillic.unicode_koi8r.pl— Russian:KOI8-R.unicode_polish_utf8.pl— Polish :UTF8.unicode_shift_jis.pl— Japanese:Shift JIS.
🚧 LIMITATIONS
Excel 2007+ said "here's your ceiling bestie, don't test me" and these are the limits it imposes:
- Maximum number of chars in a string —
32,767 - Maximum number of columns —
16,384 - Maximum number of rows —
1,048,576 - Maximum chars in a sheet name —
31 - Maximum chars in a header/footer —
254 - Maximum characters in hyperlink url (1) —
2079 - Maximum number of unique hyperlinks (2) —
65,530
(1) Versions of Excel before Excel 2015 capped hyperlink links and anchor/locations at 255 characters each. Versions after that support urls up to 2079 characters. Excel::Writer::XLSX versions >= 1.0.2 support the new longer limit by default. ✨
(2) Per worksheet. Excel actually allows a greater number of non-unique hyperlinks if they are contiguous and can be grouped into a single range. That is not supported by Excel::Writer::XLSX. 💀
📦 REQUIREMENTS
<http://search.cpan.org/search?dist=Archive-Zip/>- Perl
5.8.2.
⚡ SPEED AND MEMORY USAGE
Spreadsheet::WriteExcel was written to optimise speed and reduce memory usage. But lowkey those design goals meant it wasn't easy to implement features that lots of users kept asking for, like writing formatting and data separately. 🙈
So as a result, Excel::Writer::XLSX takes a different design approach and holds a lot more data in memory so that it's functionally more flexible. main character energy honestly.
The effect of this is that Excel::Writer::XLSX is about 30% slower than Spreadsheet::WriteExcel and uses 5 times more memory. 😭
Plus the extended row and column ranges in Excel 2007+ mean you can genuinely run out of memory creating large files. This was almost never an issue with Spreadsheet::WriteExcel.
This memory usage can be reduced almost completely by using the Workbook set_optimization() method:
$workbook->set_optimization();The trade-off? You won't be able to take advantage of features that manipulate cell data after it's written. One such feature is Tables. so pick your fighter 💅
💾 DOWNLOADING
The latest version of this module is always available at: <http://search.cpan.org/search?dist=Excel-Writer-XLSX/>.
🔧 INSTALLATION
The module can be installed using the standard Perl procedure:
perl Makefile.PL
make
make test
make install # You may need to be sudo/root🩺 DIAGNOSTICS
Filename required by Excel::Writer::XLSX->new()— A filename must be given in the constructor. like bestie u have to tell it what to write 😭Can't open filename. It may be in use or protected.— The file cannot be opened for writing. The directory that you are writing to may be protected, or the file may be in use by another program.Can't call method "XXX" on an undefined value at someprogram.pl.— On Windows this is usually caused by the file that you are trying to create clashing with a version that is already open and locked by Excel.The file you are trying to open 'file.xls' is in a different format than specified by the file extension.— This warning occurs when you create an XLSX file but give it an xls extension. 🫠
📝 WRITING EXCEL FILES
Depending on your requirements, background and general sensibilities 😌 you may prefer one of the following methods of getting data into Excel:
Spreadsheet::WriteExcel
This module is the precursor to Excel::Writer::XLSX and uses the same interface. It produces files in the Excel Biff xls format that was used in Excel versions 97-2003. These files can still be read by Excel 2007 but have some limitations in relation to the number of rows and columns that the format supports.
Spreadsheet::WriteExcel.
Win32::OLEmodule and office automation
This requires a Windows platform and an installed copy of Excel. This is the most powerful and complete method for interfacing with Excel.
Win32::OLE
- CSV, comma separated variables or text
Excel will open and automatically convert files with a "csv" extension.
To create CSV files refer to the Text::CSV_XS module.
- DBI with
DBD::ADOorDBD::ODBC
Excel files contain an internal index table that allows them to act like a database file. Using one of the standard Perl database modules you can connect to an Excel file as a database.
For other Perl-Excel modules try the following search: <http://search.cpan.org/search?mode=module&query=excel>.
💖 and that's the tea so far — keep scrolling bestie, we're not done yet ✨
📖 READING EXCEL FILES 🌈
okay so you wanna go the other way — read stuff out of Excel files instead of writing them? bestie, this module is a writer, but here's the whole roster of read-y options, no cap:
Spreadsheet::ParseXLSX
a module for reading data from XLSX files 💖 it also imports most, if not all, of the metadata that lives inside Excel XLSX files. and per its own author: "This module is an adaptor for Spreadsheet::ParseExcel that reads XLSX files. For documentation about the various data that you can retrieve from these classes, please see Spreadsheet::ParseExcel, Spreadsheet::ParseExcel::Workbook, Spreadsheet::ParseExcel::Worksheet, and Spreadsheet::ParseExcel::Cell."
Spreadsheet::XLSX
a module for reading formatted or unformatted data from XLSX files ✨
Spreadsheet::XLSX
SimpleXlsx
a lightweight lil module for reading data from XLSX files 🦋
SimpleXlsx
Spreadsheet::ParseExcel
this one can read data from an Excel XLS file, but it does not support the XLSX format, sorry bestie 😭
Spreadsheet::ParseExcel
Win32::OLEmodule and office automation (reading)
see above 👆
DBIwithDBD::ADOorDBD::ODBC.
also see above 👆
for other Perl-Excel modules try this search: <http://search.cpan.org/search?mode=module&query=excel>. it's giving "let me google that for you" but make it CPAN 💅
🐛 BUGS (yes really, we're airing it out) 💀
- Memory usage is very high for large worksheets.
if you're running out of memory while creating big worksheets, use the set_optimization() method. peep "SPEED AND MEMORY USAGE" for the whole tea ☕
- Perl packaging programs can't find chart modules.
when you're using Excel::Writer::XLSX charts with Perl packagers like PAR or Cava, you should explicitly include the chart you're trying to create in your use statements. it isn't a bug as such, but it might save somebody from banging their head off a wall 🙈:
...
use Excel::Writer::XLSX;
use Excel::Writer::XLSX::Chart::Column;
...if you wanna submit a bug report, run the bug_report.pl program in the examples directory of the distro 🫶
the bug tracker lives on Github: <https://github.com/jmcnamara/excel-writer-xlsx/issues>.
🗂️ REPOSITORY 👑
the Excel::Writer::XLSX source code is hosted on github: <http://github.com/jmcnamara/excel-writer-xlsx>. go star it, it's free ✨
💸 DONATIONS and SPONSORSHIP 💖
if you'd care to donate to the Excel::Writer::XLSX project, or sponsor a new feature, you can do so via PayPal: <http://tinyurl.com/7ayes>. genuinely iconic of you 🎀
👀 SEE ALSO 🌸
Spreadsheet::WriteExcel: <http://search.cpan.org/dist/Spreadsheet-WriteExcel>.
Spreadsheet::ParseExcel: <http://search.cpan.org/dist/Spreadsheet-ParseExcel>.
Spreadsheet::XLSX: <http://search.cpan.org/dist/Spreadsheet-XLSX>.
🫶 ACKNOWLEDGEMENTS 👑
these people all contributed to the debugging, testing or enhancement of Excel::Writer::XLSX, and they deserve their flowers fr fr 🌷
Rob Messer of IntelliSurvey gave me the initial prompt to port Spreadsheet::WriteExcel to the XLSX format. IntelliSurvey (<http://www.intellisurvey.com>) also sponsored large files optimisations and the charting feature.
Bariatric Advantage (<http://www.bariatricadvantage.com>) sponsored work on chart formatting.
Eric Johnson provided the ability to use secondary axes with charts. Thanks to Foxtons (<http://foxtons.co.uk>) for sponsoring this work.
BuildFax (<http://www.buildfax.com>) sponsored the Tables feature and the Chart point formatting feature. 💅
⚠️ DISCLAIMER OF WARRANTY ⚠️
not me having to be the responsible one, but here we go, bestie — read this part for real:
Because this software is licensed free of charge, there is no warranty for the software, to the extent permitted by applicable law. Except when otherwise stated in writing the copyright holders and/or other parties provide the software "as is" without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the software is with you. Should the software prove defective, you assume the cost of all necessary servicing, repair, or correction.
In no event unless required by applicable law or agreed to in writing will any copyright holder, or any other party who may modify and/or redistribute the software as permitted by the above licence, be liable to you for damages, including any general, special, incidental, or consequential damages arising out of the use or inability to use the software (including but not limited to loss of data or data being rendered inaccurate or losses sustained by you or third parties or a failure of the software to operate with any other software), even if such holder or other party has been advised of the possibility of such damages.
translation: it's free, it's "as is", and if it goes sideways that's a you problem 😭 the full legal text above is the actual vibe, don't skip it.
📜 LICENSE
either the Perl Artistic Licence <https://dev.perl.org/licenses/artistic.html> or the GNU General Public License v1.0 or later <https://dev.perl.org/licenses/gpl1.html>. pick your fighter 💅
✍️ AUTHOR
John McNamara jmcnamara@cpan.org — the main character, lowkey 🎀
©️ COPYRIGHT
Copyright MM-MMXXV, John McNamara.
All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself. periodt 💖
and that's a wrap on the whole page, bestie!! 🌈✨ this was Excel::Writer::XLSX as documented in perl v5.40.1, dated 2025-12-22 — screenshot that footer energy, staple it to your moodboard, and go write some absolutely iconic spreadsheets 🦋💅👑
📦 where this page comes from
- ubuntu resolute: libexcel-writer-xlsx-perl 1.15-1
- ubuntu stonking: libexcel-writer-xlsx-perl 1.15-1
- 💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on : a language model's rewrite of the original, under its terms