Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
package Excel::Writer::XLSX;
###############################################################################
#
# Excel::Writer::XLSX - Create a new file in the Excel 2007+ XLSX format.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
# Documentation after __END__
#
use 5.008002;
use strict;
use warnings;
use Exporter;
use Excel::Writer::XLSX::Workbook;
our @ISA = qw(Excel::Writer::XLSX::Workbook Exporter);
our $VERSION = '1.15';
###############################################################################
#
# new()
#
sub new {
my $class = shift;
my $self = Excel::Writer::XLSX::Workbook->new( @_ );
# Check for file creation failures before re-blessing
bless $self, $class if defined $self;
return $self;
}
1;
__END__
=head1 NAME
Excel::Writer::XLSX - Create a new file in the Excel 2007+ XLSX format.
=head1 SYNOPSIS
To write a string, a formatted string, a number and a formula to the first worksheet in an Excel workbook called perl.xlsx:
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();
=head1 DESCRIPTION
The C<Excel::Writer::XLSX> module can be used to create an Excel file in the 2007+ XLSX format.
Multiple worksheets can be added to a workbook and formatting can be applied to cells. Text, numbers, and formulas can be written to the cells.
=head1 Excel::Writer::XLSX and Spreadsheet::WriteExcel
C<Excel::Writer::XLSX> uses the same interface as the L<Spreadsheet::WriteExcel> module which produces an Excel file in binary XLS format.
=head1 QUICK START
Excel::Writer::XLSX tries to provide an interface to as many of Excel's features as possible. As a result there is a lot of documentation to accompany the interface and it can be difficult at first glance to see what it important and what is not. So ...
1. Create a new Excel I<workbook> (i.e. file) using C<new()>.
2. Add a worksheet to the new workbook using C<add_worksheet()>.
3. Write to the worksheet using C<write()>.
4. C<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 4
This will create an Excel file called C<perl.xlsx> with a single worksheet and the text C<'Hi Excel!'> in the relevant cell. And that's it. Okay, so there is actually a zeroth step as well, but C<use module> goes without saying. There are many exampl...
Those of you who read the instructions first and assemble the furniture afterwards will know how to proceed. ;-)
=head1 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()
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=head2 set_custom_property( $name, $value, $type)
The C<set_custom_property> method can be used to set one of more custom document properties not covered by the C<set_properties()> method above. These properties are visible when you use the C<< Office Button -> Prepare -> Properties -> Advanced Prop...
The C<set_custom_property> method takes 3 parameters:
$workbook-> set_custom_property( $name, $value, $type);
Where the available types are:
text
date
number
bool
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 by in ISO8601 C<yyyy-mm-ddThh:mm:ss.sssZ> date format in Zulu time, as shown above.
The C<text> and C<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 C<$name> and C<$value> parameters are limited to 255 characters by Excel.
=head2 define_name()
This method is used to defined a name that can be used to represent a value, a single cell or a range of cells in a workbook.
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 C<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: L<http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names>
=head2 set_tempdir()
C<Excel::Writer::XLSX> stores worksheet data in temporary files prior to assembling the final workbook.
The C<File::Temp> module is used to create these temporary files. File::Temp uses C<File::Spec> to determine an appropriate location for these files such as C</tmp> or C<c:\windows\temp>. You can find out which directory is used on your system as fol...
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 C<set_tempdir()> method:
$workbook->set_tempdir( '/tmp/writeexcel' );
$workbook->set_tempdir( 'c:\windows\temp\writeexcel' );
The directory for the temporary file must exist, C<set_tempdir()> will not create a new directory.
=head2 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 C<#RRGGBB> value, see L</WORKING WITH COLOURS>.
=head2 sheets( 0, 1, ... )
The C<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 specify a slice list to return one or more worksheet objects:
$worksheet = $workbook->sheets( 0 );
$worksheet->write( 'A1', 'Hello' );
Or since the return value from C<sheets()> is a reference to a worksheet object you can write the above example as:
$workbook->sheets( 0 )->write( 'A1', 'Hello' );
The following example returns the first and last worksheet in a workbook:
for $worksheet ( $workbook->sheets( 0, -1 ) ) {
# Do something
}
Array slices are explained in the C<perldata> manpage.
=head2 get_worksheet_by_name()
The C<get_worksheet_by_name()> function return a worksheet or chartsheet object in the workbook using the sheetname:
$worksheet = $workbook->get_worksheet_by_name('Sheet1');
=head2 set_1904()
Excel stores dates as real numbers where the integer part stores the number of days since the epoch and the fractional part stores the percentage of the day. The epoch can be either 1900 or 1904. Excel for Windows uses 1900 and Excel for Macintosh us...
Excel::Writer::XLSX stores dates in the 1900 format by default. If you wish to change this you can call the C<set_1904()> workbook method. You can query the current value by calling the C<get_1904()> workbook method. This returns 0 for 1900 and 1 for...
See also L</DATES AND TIME IN EXCEL> for more information about working with Excel's date system.
In general you probably won't need to use C<set_1904()>.
=head2 set_optimization()
The C<set_optimization()> method is used to turn on optimizations in the Excel::Writer::XLSX module. Currently there is only one optimization available and that is to reduce memory usage.
$workbook->set_optimization();
See L</SPEED AND MEMORY USAGE> for more background information.
Note, that with this optimization turned on a row of data is written and then discarded when a cell in a new row is added via one of the Worksheet C<write_*()> methods. As such data should be written in sequential row order once the optimization is t...
This method must be called before any calls to C<add_worksheet()>.
=head2 set_calc_mode( $mode )
Set the calculation mode for formulas in the workbook. This is mainly of use for workbooks with slow formulas where you want to allow the user to calculate them manually.
The mode parameter can be one of the following strings:
=over
=item C<auto>
The default. Excel will re-calculate formulas when a formula or a value affecting the formula changes.
=item C<manual>
Only re-calculate formulas when the user requires it. Generally by pressing F9.
=item C<auto_except_tables>
Excel will automatically re-calculate formulas except for tables.
=back
=head2 get_default_url_format()
The C<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 C<write_url()> method. The format is the hyperlink style defined by Excel for the default theme:
my $url_format = $workbook->get_default_url_format();
=head2 read_only_recommended()
The C<read_only_recommended()> method can be used to set the Excel "Read-only Recommended" option that is available when saving a file. This presents the user of the file with an option to open it in "read-only" mode. This means that any changes to t...
$workbook->read_only_recommended();
=head1 WORKSHEET METHODS
A new worksheet is created by calling the C<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()
=head2 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 useful if you are referring to cells programmatically:
for my $i ( 0 .. 9 ) {
$worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
}
A1 notation is useful 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 C<A:A> column notation:
$worksheet->write( 'A1', '=SUM(B:B)' );
The C<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 ); # C2
For simplicity, the parameter lists for the worksheet method calls in the following sections are given in terms of row-column notation. In all cases it is also possible to use A1 notation.
Note: in Excel it is also possible to use a R1C1 notation. This is not supported by Excel::Writer::XLSX.
=head2 write( $row, $column, $token, $format )
Excel makes a distinction between data types such as strings, numbers, blanks, formulas and hyperlinks. To simplify the process of writing data the C<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 I<something> then a I<something> is 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:
C<write_number()> if C<$token> is a number based on the following regex: C<$token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/>.
C<write_string()> if C<keep_leading_zeros()> is set and C<$token> is an integer with leading zeros based on the following regex: C<$token =~ /^0\d+$/>.
C<write_blank()> if C<$token> is undef or a blank string: C<undef>, C<""> or C<''>.
C<write_url()> if C<$token> is a http, https, ftp or mailto URL based on the following regexes: C<$token =~ m|^[fh]tt?ps?://|> or C<$token =~ m|^mailto:|>.
C<write_url()> if C<$token> is an internal or external sheet reference based on the following regex: C<$token =~ m[^(in|ex)ternal:]>.
C<write_formula()> if the first character of C<$token> is C<"=">.
C<write_array_formula()> if the C<$token> matches C</^{=.*}$/>.
C<write_row()> if C<$token> is an array ref.
C<write_col()> if C<$token> is an array ref of array refs.
C<write_string()> if none of the previous conditions apply.
The C<$format> parameter is optional. It should be a valid Format object, see L</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 string
The write() method will ignore empty strings or C<undef> tokens unless a format is also supplied. As such you needn't worry about special handling for empty or C<undef> values in your data. See also the C<write_blank()> method.
One problem with the C<write()> method is that occasionally data looks like a number but you don't want it treated as a number. For example, zip codes or ID numbers often start with a leading zero. If you write this data as a number then the leading ...
You can also add your own data handlers to the C<write()> method using C<add_write_handler()>.
The C<write()> method will also handle Unicode strings in C<UTF-8> format.
The C<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.
=head2 write_number( $row, $column, $number, $format )
Write an integer or a float to the cell specified by C<$row> and C<$column>:
$worksheet->write_number( 0, 0, 123456 );
$worksheet->write_number( 'A2', 2.3451 );
See the note about L</Cell notation>. The C<$format> parameter is optional.
In general it is sufficient to use the C<write()> method.
B<Note>: some versions of Excel 2007 do not display the calculated values of formulas written by Excel::Writer::XLSX. Applying all available Service Packs to Excel should fix this.
=head2 write_string( $row, $column, $string, $format )
Write a string to the cell specified by C<$row> and C<$column>:
$worksheet->write_string( 0, 0, 'Your text here' );
$worksheet->write_string( 'A2', 'or here' );
The maximum string size is 32767 characters. However the maximum string segment that Excel can display in a cell is 1000. All 32767 characters can be displayed in the formula bar.
The C<$format> parameter is optional.
The C<write()> method will also handle strings in C<UTF-8> format. See also the C<unicode_*.pl> programs in the examples directory of the distro.
In general it is sufficient to use the C<write()> method. However, you may sometimes wish to use the C<write_string()> method to write data that looks like a number but that you don't want treated as a number. For example, zip codes or phone numbers:
# Write as a plain string
$worksheet->write_string( 'A1', '01209' );
However, if the user edits this string Excel may convert it back to a number. To get around this you can use the Excel text format C<@>:
# 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 L</Cell notation>.
=head2 write_rich_string( $row, $column, $format, $string, ..., $cell_format )
The C<write_rich_string()> method is used to write strings with multiple formats. For example to write the string "This is B<bold> and this is I<italic>" you would use the following:
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 is to break the string into fragments and put a C<$format> object before the fragment that you want to format. For example:
# 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 that don't have a format are given a default format. So for example when writing the string "Some B<bold> text" you would use the first example below but it would be 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' );
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
| 3 | shell | star | crab | stone | ... | ...
| 4 | ... | ... | ... | ... | ... | ...
| 5 | ... | ... | ... | ... | ... | ...
| 6 | ... | ... | ... | ... | ... | ...
To write the data in a column-row order refer to the C<write_row()> method above.
Any C<undef> values in the data will be ignored unless a format is applied to the data, in which case a formatted blank cell will be written. In either case the appropriate row or column value will still be incremented.
As noted above the C<write()> method can be used as a synonym for C<write_row()> and C<write_row()> handles nested array refs as columns. Therefore, the following two method calls are equivalent although the more explicit call to C<write_col()> would...
$worksheet->write_col( 'A1', $array_ref ); # Write a column of data
$worksheet->write( 'A1', [ $array_ref ] ); # Same thing
To find out more about array references refer to C<perlref> and C<perlreftut> in the main Perl documentation. To find out more about 2D arrays or "lists of lists" refer to C<perllol>.
The C<write_col()> method returns the first error encountered when writing the elements of the data or zero if no errors were encountered. See the return values described for the C<write()> method above.
=head2 write_date_time( $row, $col, $date_string, $format )
The C<write_date_time()> method can be used to write a date or time to the cell specified by C<$row> and C<$column>:
$worksheet->write_date_time( 'A1', '2004-05-13T23:20', $date_format );
The C<$date_string> should be in the following format:
yyyy-mm-ddThh:mm:ss.sss
This conforms to an ISO8601 date but it should be noted that the full range of ISO8601 formats are not supported.
The following variations on the C<$date_string> parameter are permitted:
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 seconds
Note that the C<T> is required in all cases.
A date should always have a C<$format>, otherwise it will appear as a number, see L</DATES AND TIME IN EXCEL> and L</CELL FORMATTING>. Here is a 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 should be 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. As with Excel, dates outside these ranges will be written as a string.
See also the date_time.pl program in the C<examples> directory of the distro.
=head2 write_url( $row, $col, $url, $format, $label )
Write a hyperlink to a URL in the cell specified by C<$row> and C<$column>. The hyperlink is comprised of two elements: the visible label and the invisible link. The visible label is the same as the link unless an alternative label is specified. The ...
The C<$format> parameter is also optional and the default Excel hyperlink style will be used if it isn't specified. If required you can access the default url format using the Workbook C<get_default_url_format> method:
my $url_format = $workbook->get_default_url_format();
There are four web style URI's supported: C<http://>, C<https://>, C<ftp://> and C<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 C<$label> parameter:
$worksheet->write_url( 1, 0, 'http://www.perl.com/', undef, 'Perl' );
If you wish to have some other cell data such as a number or a formula you can overwrite the cell using another call to C<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 two local URIs supported: C<internal:> and C<external:>. These are used for hyperlinks to internal worksheet references or 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 the these URI types are recognised by the C<write()> method, see above.
Worksheet references are typically of the form C<Sheet1!A1>. You can also refer to a worksheet range using the standard Excel notation: C<Sheet1!A1:B2>.
In external links the workbook and worksheet name must be separated by the C<#> character: C<external:Workbook.xlsx#Sheet1!A1'>.
You can also link to a named range in the target worksheet. For example say you have a named range called C<my_name> in the workbook C<c:\temp\foo.xlsx> you could link to it as follows:
$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 as follows C<'Sales Data'!A1>. If you need to do this in a single quoted string then you can either escape the single quotes C<\'> or use the quote...
Links to network files are also supported. MS/Novell Network files normally begin with two back slashes as follows C<\\NETWORK\etc>. In order to generate this in a single or double quoted string you will have to escape the backslashes, C<'\\\\NETWOR...
If you are using double quote strings then you should be careful to escape anything that looks like a metacharacter. For more information see C<perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?>.
Finally, you can avoid most of these quoting problems by using forward slashes. These are translated internally to backslashes:
$worksheet->write_url( 'A14', "external:c:/temp/foo.xlsx" );
$worksheet->write_url( 'A15', 'external://NETWORK/share/foo.xlsx' );
Note: Excel::Writer::XLSX will escape the following characters in URLs as required by Excel: C<< \s " < > \ [ ] ` ^ { } >> unless the URL already contains C<%xx> style escapes. In which case it is assumed that the URL was escaped correctly by the us...
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 L</Cell notation>.
=head2 write_formula( $row, $column, $formula, $format, $value )
Write a formula or function to the cell specified by C<$row> and C<$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 C<write_array_formula()> method below.
See the note about L</Cell notation>. For more information about writing Excel formulas see L</FORMULAS AND FUNCTIONS IN EXCEL>
If required, it is also possible to specify the calculated value of the formula. This is occasionally necessary when working with non-Excel applications that don't calculate the value of the formula. The calculated C<$value> is added at the end of th...
$worksheet->write( 'A1', '=2+2', $format, 4 );
However, this probably isn't something that you will ever need to do. If you do use this feature then do so with care.
=head2 write_array_formula($first_row, $first_col, $last_row, $last_col, $formula, $format, $value)
Write an array formula to a cell range. In Excel an array formula is a formula that performs a calculation on a set of values. It can return a single value or a range of values.
An array formula is indicated by a pair of braces around the formula: C<{=SUM(A1:B1*A2:B2)}>. If the array formula returns a single value then the C<$first_> and C<$last_> parameters should be the same:
$worksheet->write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}');
It this case however it is easier to just use the C<write_formula()> or C<write()> methods:
# 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 that the return values will be written to:
$worksheet->write_array_formula( 'A1:A3', '{=TREND(C1:C3,B1:B3)}' );
$worksheet->write_array_formula( 0, 0, 2, 0, '{=TREND(C1:C3,B1:B3)}' );
If required, it is also possible to specify the calculated value of the formula. This is occasionally necessary when working with non-Excel applications that don't calculate the value of the formula. However, using this parameter only writes a single...
# 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 );
In addition, 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 this issue.
See also the C<array_formula.pl> program in the C<examples> directory of the distro.
Note: Array formulas are not supported by Spreadsheet::WriteExcel.
=head2 write_boolean( $row, $column, $value, $format )
Write an Excel boolean value to the cell specified by C<$row> and C<$column>:
$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 C<$value> that is true or false using Perl's rules will be written as an Excel boolean C<TRUE> or C<FALSE> value.
See the note about L</Cell notation>.
=head2 store_formula( $formula )
Deprecated. This is a Spreadsheet::WriteExcel method that is no longer required by Excel::Writer::XLSX. See below.
=head2 repeat_formula( $row, $col, $formula, $format )
Deprecated. This is a Spreadsheet::WriteExcel method that is no longer required by Excel::Writer::XLSX.
In Spreadsheet::WriteExcel it was computationally expensive to write formulas since they were parsed by a recursive descent parser. The C<store_formula()> and C<repeat_formula()> methods were used as a way of avoiding the overhead of repeated formula...
In Excel::Writer::XLSX this is no longer necessary since it is just as quick to write a formula as it is to write a string or a number.
The methods remain for backward compatibility but new Excel::Writer::XLSX programs shouldn't use them.
=head2 write_comment( $row, $column, $string, ... )
The C<write_comment()> method is used to add a comment to a cell. A cell comment is indicated in Excel by a small red triangle in the upper right-hand corner of the cell. Moving the cursor over the red triangle will reveal the comment.
The following example shows how to add a comment to a cell:
$worksheet->write ( 2, 2, 'Hello' );
$worksheet->write_comment( 2, 2, 'This is a comment.' );
As usual you can replace the C<$row> and C<$column> parameters with an C<A1> cell reference. See the note about L</Cell notation>.
$worksheet->write ( 'C3', 'Hello');
$worksheet->write_comment( 'C3', 'This is a comment.' );
The C<write_comment()> method will also handle strings in C<UTF-8> format.
$worksheet->write_comment( 'C3', "\x{263a}" ); # Smiley
$worksheet->write_comment( 'C4', 'Comment ca va?' );
In addition to the basic 3 argument form of C<write_comment()> you can pass in several optional key/value pairs to control the format of the comment. For example:
$worksheet->write_comment( 'C3', 'Hello', visible => 1, author => 'Perl' );
Most of these options are quite specific and in general the default comment behaves will be all that you need. However, should you need greater control over the format of the cell comment the following options are available:
author
visible
x_scale
width
y_scale
height
color
start_cell
start_row
start_col
x_offset
y_offset
font
font_size
=over 4
=item Option: author
This option is used to indicate who is the author of the cell comment. Excel displays the author of the comment in the status bar at the bottom of the worksheet. This is usually of interest in corporate environments where several people might review ...
$worksheet->write_comment( 'C3', 'Atonement', author => 'Ian McEwan' );
The default author for all cell comments can be set using the C<set_comments_author()> method (see below).
$worksheet->set_comments_author( 'Perl' );
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
$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 C<activate()> method will also appe...
=head2 hide()
The C<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.
A hidden worksheet can not be activated or selected so this method is mutually exclusive with the C<activate()> and C<select()> methods. In addition, since the first worksheet will default to being the active worksheet, you cannot hide the first work...
$worksheet2->activate();
$worksheet1->hide();
=head2 very_hidden()
The C<very_hidden()> method can be used to hide a worksheet similar to the
C<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.
=head2 set_first_sheet()
The C<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 us...
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.
=head2 protect( $password, \%options )
The C<protect()> method is used to protect a worksheet from modification:
$worksheet->protect();
The C<protect()> method also has the effect of enabling a cell's C<locked> and C<hidden> properties if they have been set. A I<locked> cell cannot be edited and this property is on by default for all cells. A I<hidden> cell will display the results o...
See the C<protection.pl> program in the examples directory of the distro for an illustrative example and the C<set_locked> and C<set_hidden> format methods in L</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 C<''> 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 C<Excel::Writer::XLSX> since it requires a completely different file...
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,
);
=head2 unprotect_range( $cell_range, $range_name )
The C<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 C<Range1> and C<Range2> but a user defined name can also be specified:
$worksheet->unprotect_range( 'G4:I6', 'MyRange' );
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
The parameters C<$row> and C<$col> are used to specify the location of the split. It should be noted that the split is specified at the top or left of a cell and that the method uses zero based indexing. Therefore to freeze the first row of a workshe...
You can set one of the C<$row> and C<$col> parameters as zero if you do not want either 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
The parameters C<$top_row> and C<$left_col> are optional. They are used to specify the top-most or left-most visible row or column in the scrolling region of the panes. For example to freeze the first row and to have the scrolling region begin at row...
$worksheet->freeze_panes( 1, 0, 20, 0 );
You cannot use A1 notation for the C<$top_row> and C<$left_col> parameters.
See also the C<panes.pl> program in the C<examples> directory of the distribution.
=head2 split_panes( $y, $x, $top_row, $left_col )
This method can be used to divide a worksheet into horizontal or vertical regions known as panes. This method is different from the C<freeze_panes()> method in that the splits between the panes will be visible to the user and each pane will have its ...
The parameters C<$y> and C<$x> are used to specify the vertical and horizontal position of the split. The units for C<$y> and C<$x> are the same as those used by Excel to specify row height and column width. However, the vertical and horizontal units...
You can set one of the C<$y> and C<$x> parameters as zero if you do not want either a vertical or horizontal split. The parameters C<$top_row> and C<$left_col> are optional. They are used to specify the top-most or left-most visible row or column in ...
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 column
You cannot use A1 notation with this method.
See also the C<freeze_panes()> method and the C<panes.pl> program in the C<examples> directory of the distribution.
=head2 merge_range( $first_row, $first_col, $last_row, $last_col, $token, $format )
The C<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 );
C<merge_range()> writes its C<$token> argument using the worksheet C<write()> method. Therefore it will handle numbers, strings, formulas or urls as required. If you need to specify the required C<write_*()> method use the C<merge_range_type()> metho...
The full possibilities of this method are shown in the C<merge3.pl> to C<merge6.pl> programs in the C<examples> directory of the distribution.
=head2 merge_range_type( $type, $first_row, $first_col, $last_row, $last_col, ... )
The C<merge_range()> method, see above, uses C<write()> to insert the required data into to a merged range. However, there may be times where this isn't what you require so as an alternative the C<merge_range_type ()> method allows you to specify the...
$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 C<$type> must be one of the following, which corresponds to a C<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 C<$format> object as an argument, even if it is a default format.
=head2 set_zoom( $scale )
Set the worksheet zoom factor in the range C<10 E<lt>= $scale E<lt>= 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.
Note, C<set_zoom()> does not affect the scale of the printed page. For that you should use C<set_print_scale()>.
=head2 right_to_left()
The C<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.
=head2 hide_zero()
The C<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.
=head2 set_background( $filename )
The C<set_background()> method can be used to set the background image for the
worksheet:
$worksheet->set_background( 'logo.png' )
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
The C<convert_date_time()> method is used internally by the C<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.
The C<$date_string> format is detailed in the C<write_date_time()> method.
=head2 set_vba_name()
The Worksheet C<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 C<vbaProject> macro included via C<add_vba_project()> refers to the ...
See also L<WORKING WITH VBA MACROS>.
=head2 ignore_errors()
The C<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.
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...
$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'});
The range can be a single cell, a range of cells, or multiple cells and ranges separated by 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 C<ignore_errors> multiple times will overwrite the previous settings.
You can turn off warnings for an entire column by specifying the range from the first cell in the column to the last cell in the column:
$worksheet->ignore_errors({number_stored_as_text => 'A1:A1048576'});
Or for the entire worksheet by specifying the range from the first cell in the worksheet to the last cell in the worksheet:
$worksheet->ignore_errors({number_stored_as_text => 'A1:XFD1048576'});
The worksheet errors/warnings that can be ignored are:
=over
=item * C<number_stored_as_text>: Turn off errors/warnings for numbers stores as text.
=item * C<eval_error>: Turn off errors/warnings for formula errors (such as divide by zero).
=item * C<formula_differs>: Turn off errors/warnings for formulas that differ from surrounding formulas.
=item * C<formula_range>: Turn off errors/warnings for formulas that omit cells in a range.
=item * C<formula_unlocked>: Turn off errors/warnings for unlocked cells that contain formulas.
=item * C<empty_cell_reference>: Turn off errors/warnings for formulas that refer to empty cells.
=item * C<list_data_validation>: Turn off errors/warnings for cells in a table that do not comply with applicable data validation rules.
=item * C<calculated_column>: Turn off errors/warnings for cell formulas that differ from the column formula.
=item * C<two_digit_text_year>: Turn off errors/warnings for formulas that contain a two digit text representation of a year.
=back
=head1 PAGE SET-UP METHODS
Page set-up methods affect the way that a worksheet looks when it is printed. They control features such as page headers and footers and margins. These methods are really just standard worksheet methods. They are documented here in a separate section...
The following methods are available for page set-up:
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()
A common requirement when working with Excel::Writer::XLSX is to apply the same page set-up features to all of the worksheets in a workbook. To do this you can use the C<sheets()> method of the C<workbook> class to access the array of worksheets in a...
for $worksheet ( $workbook->sheets() ) {
$worksheet->set_landscape();
}
=head2 set_landscape()
This method is used to set the orientation of a worksheet's printed page to landscape:
$worksheet->set_landscape(); # Landscape mode
=head2 set_portrait()
This method is used to set the orientation of a worksheet's printed page to portrait. The default worksheet orientation is portrait, so you won't generally need to call this method.
$worksheet->set_portrait(); # Portrait mode
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
The C<set_v_pagebreaks()> method will accept a list of page breaks and you can call it more than once:
$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 the C<fit_to_pages()> method it will override all manual page breaks.
=head1 CELL FORMATTING
This section describes the methods and properties that are available for formatting cells in Excel. The properties of a cell that can be formatted include: fonts, colours, patterns, borders, alignment and number formatting.
=head2 Creating and using a Format object
Cell formatting is defined through a Format object. Format objects are created by calling the workbook C<add_format()> method as follows:
my $format1 = $workbook->add_format(); # Set properties later
my $format2 = $workbook->add_format( %props ); # Set at creation
The format object holds all the formatting properties that can be applied to a cell, a row or a column. The process of setting these properties is discussed in the next section.
Once a Format object has been constructed and its properties have been set it can be passed as an argument to the worksheet C<write> methods as follows:
$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 can also be passed to the worksheet C<set_row()> and C<set_column()> methods to define the default property for a row or column.
$worksheet->set_row( 0, 15, $format );
$worksheet->set_column( 0, 0, 15, $format );
=head2 Format methods and Format properties
The following table shows the Excel format categories, the formatting properties that can be applied and the equivalent object method:
Category Description Property Method Name
-------- ----------- -------- -----------
Font Font type font set_font()
Font size size set_size()
Font color color set_color()
Bold bold set_bold()
Italic italic set_italic()
Underline underline set_underline()
Strikeout font_strikeout set_font_strikeout()
Super/Subscript font_script set_font_script()
Outline font_outline set_font_outline()
Shadow font_shadow set_font_shadow()
Number Numeric format num_format set_num_format()
Protection Lock cells locked set_locked()
Hide formulas hidden set_hidden()
Alignment Horizontal align align set_align()
Vertical align valign set_align()
Rotation rotation set_rotation()
Text wrap text_wrap set_text_wrap()
Justify last text_justlast set_text_justlast()
Center across center_across set_center_across()
Indentation indent set_indent()
Shrink to fit shrink set_shrink()
Pattern Cell pattern pattern set_pattern()
Background color bg_color set_bg_color()
Foreground color fg_color set_fg_color()
Border Cell border border set_border()
Bottom border bottom set_bottom()
Top border top set_top()
Left border left set_left()
Right border right set_right()
Border color border_color set_border_color()
Bottom color bottom_color set_bottom_color()
Top color top_color set_top_color()
Left color left_color set_left_color()
Right color right_color set_right_color()
Diagonal type diag_type set_diag_type()
Diagonal border diag_border set_diag_border()
Diagonal color diag_color set_diag_color()
There are two ways of setting Format properties: by using the object method interface or by setting the property directly. For example, a typical use of the method interface would be as follows:
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
By comparison the properties can be set directly by passing a hash of properties to the Format constructor:
my $format = $workbook->add_format( bold => 1, color => 'red' );
or after the Format has been constructed by means of the C<set_format_properties()> method as follows:
my $format = $workbook->add_format();
$format->set_format_properties( bold => 1, color => 'red' );
You can also store the properties in one or more named hashes and pass them to the required method:
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 shading
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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 C<docs> directory of the distro.
Note 1. Numeric formats 23 to 36 are 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:
=over
=item * The dot appears as the defined local decimal separator.
=item * The comma appears as the defined local digit groups separator.
=item * The dollar sign appears as the defined local currency symbol.
=item * The date, time and duration formats appear as the local equivalent date or time format.
=back
=head2 set_locked()
Default state: Cell locking is on
Default action: Turn locking on
Valid args: 0, 1
This property can be used to prevent modification of a cells contents. Following Excel's convention, cell locking is turned on by default. However, it only has an effect if the worksheet has been protected, see the worksheet C<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 C<protect()> method.
=head2 set_hidden()
Default state: Formula hiding is off
Default action: Turn hiding on
Valid args: 0, 1
This property is used to hide a formula while still displaying its result. This is generally used to hide complex calculations from end users who are only interested in the result. It only has an effect if the worksheet has been protected, see the wo...
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 C<protect()> method.
=head2 set_align()
Default state: Alignment is off
Default action: Left alignment
Valid args: 'left' Horizontal
'center'
'right'
'fill'
'justify'
'center_across'
'top' Vertical
'vcenter'
'bottom'
'vjustify'
This method is used to set the horizontal and vertical text alignment within a cell. Vertical and horizontal alignments can be combined. The method is used as follows:
my $format = $workbook->add_format();
$format->set_align( 'center' );
$format->set_align( 'vcenter' );
$worksheet->set_row( 0, 30 );
$worksheet->write( 0, 0, 'X', $format );
Text can be aligned across two or more adjacent cells using the C<center_across> property. However, for genuine merged cells it is better to use the C<merge_range()> worksheet method.
The C<vjustify> (vertical justify) option can be used to provide automatic text wrapping in a cell. The height of the cell will be adjusted to accommodate the wrapped text. To specify where the text wraps use the C<set_text_wrap()> method.
For further examples see the 'Alignment' worksheet created by formats.pl.
=head2 set_center_across()
Default state: Center across selection is off
Default action: Turn center across on
Valid args: 1
Text can be aligned across two or more adjacent cells using the C<set_center_across()> method. This is an alias for the C<set_align('center_across')> method call.
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 C<merge1.pl> to C<merge6.pl> programs in the C<examples> directory and the C<merge_range()> method.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
This parameter is passed in a hash ref to C<data_validation()>.
The C<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
custom
=over
=item * B<any> is 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.
=item * B<integer> restricts the cell to integer values. Excel refers to this as 'whole number'.
validate => 'integer',
criteria => '>',
value => 100,
=item * B<decimal> restricts the cell to decimal values.
validate => 'decimal',
criteria => '>',
value => 38.6,
=item * B<list> restricts 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.
=item * B<date> restricts the cell to date values. Dates in Excel are expressed as integer values but you can also pass an ISO8601 style string as used in C<write_date_time()>. See also L</DATES AND TIME IN EXCEL> for more information about working w...
validate => 'date',
criteria => '>',
value => 39653, # 24 July 2008
# Or like this:
value => '2008-07-24T',
=item * B<time> restricts the cell to time values. Times in Excel are expressed as decimal values but you can also pass an ISO8601 style string as used in C<write_date_time()>. See also L</DATES AND TIME IN EXCEL> for more information about working w...
validate => 'time',
criteria => '>',
value => 0.5, # Noon
# Or like this:
value => 'T12:00:00',
=item * B<length> restricts the cell data based on an integer string length. Excel refers to this as 'Text length'.
validate => 'length',
criteria => '>',
value => 10,
=item * B<custom> restricts the cell based on an external Excel formula that returns a C<TRUE/FALSE> value.
validate => 'custom',
value => '=IF(A10>B10,TRUE,FALSE)',
=back
=head2 criteria
This parameter is passed in a hash ref to C<data_validation()>.
The C<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 C<list> and C<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 C<list> and C<custom> validate options don't require a C<criteria>. If you specify one it will be ignored.
validate => 'list',
value => ['open', 'high', 'close'],
validate => 'custom',
value => '=IF(A10>B10,TRUE,FALSE)',
=head2 value | minimum | source
This parameter is passed in a hash ref to C<data_validation()>.
The C<value> parameter is used to set the limiting value to which the C<criteria> is applied. It is always required and it has no default value. You can also use the synonyms C<minimum> or C<source> to make the validation a little clearer and closer ...
# Use 'value'
validate => 'integer',
criteria => '>',
value => 100,
# Use 'minimum'
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
# Use 'source'
validate => 'list',
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
data_bar_2010
icon_style
icons
reverse_icons
icons_only
stop_if_true
multi_range
Additional parameters which are used for specific conditional format types are shown in the relevant sections below.
=head2 type
This parameter is passed in a hash ref to C<conditional_formatting()>.
The C<type> parameter is used to set the type of conditional formatting that you wish to apply. It is always required and it has no default value. Allowable C<type> values and their associated parameters are:
Type Parameters
==== ==========
cell criteria
value
minimum
maximum
format
date criteria
value
minimum
maximum
format
time_period criteria
format
text criteria
value
format
average criteria
format
duplicate format
unique format
top criteria
value
format
bottom criteria
value
format
blanks format
no_blanks format
errors format
no_errors format
formula criteria
format
2_color_scale min_type
max_type
min_value
max_value
min_color
max_color
3_color_scale min_type
mid_type
max_type
min_value
mid_value
max_value
min_color
mid_color
max_color
data_bar min_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 icon_style
reverse_icons
icons
icons_only
Data bar parameters marked with (*) are only available in Excel 2010 and later. Files that use these properties can still be opened in Excel 2007 but the data bars will be displayed without them.
=head2 type => 'cell'
This is the most common conditional formatting type. 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 C<between> criteria:
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
}
);
=head2 type => 'bottom'
The C<bottom> type is used to specify the bottom C<n> values by number or percentage in a range.
It takes the same parameters as C<top>, see above.
=head2 type => 'blanks'
The C<blanks> type is used to highlight blank cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'blanks',
format => $format,
}
);
=head2 type => 'no_blanks'
The C<no_blanks> type is used to highlight non blank cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'no_blanks',
format => $format,
}
);
=head2 type => 'errors'
The C<errors> type is used to highlight error cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'errors',
format => $format,
}
);
=head2 type => 'no_errors'
The C<no_errors> type is used to highlight non error cells in a range:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'no_errors',
format => $format,
}
);
=head2 type => 'formula'
The C<formula> type is used to specify a conditional format based on a user defined formula:
$worksheet->conditional_formatting( 'A1:A4',
{
type => 'formula',
criteria => '=$A$1 > 5',
format => $format,
}
);
The formula is specified in the C<criteria>.
=head2 type => '2_color_scale'
The C<2_color_scale> type is used to specify Excel's "2 Color Scale" style conditional format.
$worksheet->conditional_formatting( 'A1:A12',
{
type => '2_color_scale',
}
);
This conditional type can be modified with C<min_type>, C<max_type>, C<min_value>, C<max_value>, C<min_color> and C<max_color>, see below.
=head2 type => '3_color_scale'
The C<3_color_scale> type is used to specify Excel's "3 Color Scale" style conditional format.
$worksheet->conditional_formatting( 'A1:A12',
{
type => '3_color_scale',
}
);
This conditional type can be modified with C<min_type>, C<mid_type>, C<max_type>, C<min_value>, C<mid_value>, C<max_value>, C<min_color>, C<mid_color> and C<max_color>, see below.
=head2 type => 'data_bar'
The C<data_bar> type is used to specify Excel's "Data Bar" style conditional format.
$worksheet->conditional_formatting( 'A1:A12',
{
type => 'data_bar',
}
);
This data bar conditional type can be modified with the following parameters, which are 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_only
In 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_2010
Files that use these Excel 2010 properties can still be opened in Excel 2007 but the data bars will be displayed without them.
=head2 type => 'icon_set'
The C<icon_set> type is used to specify a conditional format with a set of icons such as 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 C<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_ratings
The criteria, type and value of each icon can be specified using the C<icon> array of hash refs with optional C<criteria>, C<type> and C<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 C<icons criteria> parameter should be either C<< >= >> or C<< > >>. The default C<criteria> is C<< >= >>.
The C<icons type> parameter should be one of the following values:
number
percentile
percent
formula
The default C<type> is C<percent>.
The C<icons value> parameter can be a value or formula:
$worksheet->conditional_formatting( 'A1:D1',
{
type => 'icon_set',
icon_style => '4_red_to_black',
icons => [ {value => 90},
{value => 50},
{value => 25},
],
}
);
Note: The C<icons> parameters should start with the highest value and with each subsequent one being lower. The default C<value> is C<(n * 100) / number_of_icons>. The lowest number icon in an icon set has properties defined by Excel. Therefore in a ...
The order of the icons can be reversed using the C<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 C<icons_only> parameter:
$worksheet->conditional_formatting( 'A1:C1',
{
type => 'icon_set',
icon_style => '3_flags',
icons_only => 1,
}
);
=head2 min_type, mid_type, max_type
The C<min_type> and C<max_type> properties are available when the conditional formatting type is C<2_color_scale>, C<3_color_scale> or C<data_bar>. The C<mid_type> is available for C<3_color_scale>. The properties are used as follows:
$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)
=head2 min_value, mid_value, max_value
The C<min_value> and C<max_value> properties are available when the conditional formatting type is C<2_color_scale>, C<3_color_scale> or C<data_bar>. The C<mid_value> is available for C<3_color_scale>. The properties are used as follows:
$worksheet->conditional_formatting( 'A1:A12',
{
type => '2_color_scale',
min_value => 10,
max_value => 90,
}
);
=head2 min_color, mid_color, max_color, bar_color
The C<min_color> and C<max_color> properties are available when the conditional formatting type is C<2_color_scale>, C<3_color_scale> or C<data_bar>. The C<mid_color> is available for C<3_color_scale>. The properties are used as follows:
$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.
=head2 bar_only
The C<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
}
);
=head2 bar_solid
The C<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.
=head2 bar_negative_color
The C<bar_negative_color> parameter is used to set the color fill for the negative portion of a data bar.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
Other, 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_color
These parameters are explained in the sections below:
=head2 location
This is the cell where the sparkline will be displayed:
location => 'F1'
The C<location> should be a single cell. (For multiple cells see L<Grouped Sparklines> below).
To specify the location in row-column notation use the C<xl_rowcol_to_cell()> function from the L<Excel::Writer::XLSX::Utility> module.
use Excel::Writer::XLSX::Utility ':rowcol';
...
location => xl_rowcol_to_cell( 0, 5 ), # F1
=head2 range
This specifies the cell data range that the sparkline will plot:
$worksheet->add_sparkline(
{
location => 'F1',
range => 'A1:E1',
}
);
The C<range> should be a 2D array. (For 3D arrays of cells see L<Grouped Sparklines> below).
If C<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 C<xl_range()> or C<xl_range_formula()> functions from the L<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'
=head2 type
Specifies the type of sparkline. There are 3 available sparkline types:
line (default)
column
win_loss
For example:
{
location => 'F1',
range => 'A1:E1',
type => 'column',
}
=head2 style
Excel provides 36 built-in Sparkline styles in 6 groups of 6. The C<style> parameter can be used to replicate these and 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. It is possible to override colour elements of the sparklines using the C<*_color> parameters below.
=head2 markers
Turn on the markers for C<line> style sparklines.
{
location => 'A6',
range => 'Sheet2!A1:J1',
markers => 1,
}
Markers aren't shown in Excel for C<column> and C<win_loss> sparklines.
=head2 negative_points
Highlight negative values in a sparkline range. This is usually required with C<win_loss> sparklines.
{
location => 'A21',
range => 'Sheet2!A3:J3',
type => 'win_loss',
negative_points => 1,
}
=head2 axis
Display a horizontal axis in the sparkline:
{
location => 'A10',
range => 'Sheet2!A1:J1',
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
Note, this option is off by default.
=head2 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.
=head2 series_color
It is 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_color
The color should be specified as a HTML style C<#rrggbb> hex value:
{
location => 'A18',
range => 'Sheet2!A2:J2',
type => 'column',
series_color => '#E965E0',
}
=head2 Grouped Sparklines
The C<add_sparkline()> worksheet method can be used multiple times to write as many sparklines as are required in a worksheet.
However, it is 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 C<range> and a 2D range of cells for the C<location>...
In Excel::Writer::XLSX, you can simulate this by passing an array refs of values to C<location> and C<range>:
{
location => [ 'A27', 'A28', 'A29' ],
range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ],
markers => 1,
}
=head2 Sparkline examples
See the C<sparklines1.pl> and C<sparklines2.pl> example programs in the C<examples> directory of the distro.
=head1 TABLES IN EXCEL
Tables in Excel are a way of grouping a range of cells into a single entity that has common formatting or that can be referenced from formulas. Tables can have column headers, autofilters, total rows, column formulas and default formatting.
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/tables.jpg" width="640" height="420" alt="Output from tables.pl" /></center></p>
=end html
For more information see "An Overview of Excel Tables" L<http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx>.
Note, tables don't work in Excel::Writer::XLSX when C<set_optimization()> mode in on.
=head2 add_table( $row1, $col1, $row2, $col2, { parameter => 'value', ... })
Tables are added to a worksheet using the C<add_table()> method:
$worksheet->add_table( 'B3:F7', { %parameters } );
The data range can be specified in 'A1' or 'row/col' notation (see also the note about L</Cell notation> for more information):
$worksheet->add_table( 'B3:F7' );
# Same as:
$worksheet->add_table( 2, 1, 6, 5 );
The last parameter in C<add_table()> should be a hash ref containing the parameters that describe the table options and data. The available parameters are:
data
autofilter
header_row
banded_columns
banded_rows
first_column
last_column
style
total_row
columns
name
The table parameters are detailed below. There are no required parameters and the hash ref isn't required if no options are specified.
=head2 data
The C<data> parameter can be used to 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 } );
Table data can also be written separately, as an array or individual cells.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
$worksheet->add_table( 'B3:F7', { autofilter => 0 } ); # Turn autofilter off.
The C<autofilter> is only shown if the C<header_row> is on. Filters within the table are not supported.
=head2 banded_rows
The C<banded_rows> parameter can be used to used to create rows of alternating colour in the table. It is on by default.
$worksheet->add_table( 'B3:F7', { banded_rows => 0 } );
=head2 banded_columns
The C<banded_columns> parameter can be used to used to create columns of alternating colour in the table. It is off by default.
$worksheet->add_table( 'B3:F7', { banded_columns => 1 } );
=head2 first_column
The C<first_column> parameter can be used to highlight the first column of the table. The type of highlighting will depend on the C<style> of the table. It may be bold text or a different colour. It is off by default.
$worksheet->add_table( 'B3:F7', { first_column => 1 } );
=head2 last_column
The C<last_column> parameter can be used to highlight the last column of the table. The type of highlighting will depend on the C<style> of the table. It may be bold text or a different colour. It is off by default.
$worksheet->add_table( 'B3:F7', { last_column => 1 } );
=head2 style
The C<style> parameter can be used to set the style of the table. Standard Excel table format names should be used (with matching capitalisation):
$worksheet11->add_table(
'B3:F7',
{
data => $data,
style => 'Table Style Light 11',
}
);
The default table style is 'Table Style Medium 9'.
You can also turn the table style off by setting it to 'None':
$worksheet11->add_table( 'B3:F7', { style => 'None' } );
=head2 name
By default tables are named C<Table1>, C<Table2>, etc. The C<name> parameter can be used to set the name of the table:
$worksheet->add_table( 'B3:F7', { name => 'SalesData' } );
If you override the table name you must ensure that it doesn't clash with an existing table name and that it follows Excel's requirements for table names L<http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.asp...
If you need to know the name of the table, for example to use it in a formula, you can get it as follows:
my $table = $worksheet2->add_table( 'B3:F7' );
my $table_name = $table->{_name};
=head2 total_row
The C<total_row> parameter can be used to turn on the total row in the last row of a table. It is distinguished from the other rows by a different formatting and also with dropdown C<SUBTOTAL> functions.
$worksheet->add_table( 'B3:F7', { total_row => 1 } );
The default total row doesn't have any captions or functions. These must by specified via the C<columns> parameter below.
=head2 columns
The C<columns> parameter can be used to set properties for columns within the table.
The sub-properties that can be set are:
header
formula
total_string
total_function
total_value
format
header_format
The 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 wish to specify properties for a specific column you pass an empty hash ref and the defaults will be applied:
...
columns => [
{ header => 'Product' },
{ header => 'Quarter 1' },
{ }, # Defaults to 'Column 3'.
{ header => 'Quarter 3' },
{ header => 'Quarter 4' },
]
...
Column formulas can by applied using the C<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 C<[#This Row]> and Excel 2010 C<@> structural references are supported within the formula.
As stated above the C<total_row> table parameter turns on the "Total" row in the table but it doesn't populate it with any defaults. Total captions and functions must be specified via the C<columns> property and the C<total_string>, C<total_function>...
$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 C<SUBTOTAL> functions are:
average
count_nums
count
max
min
std_dev
sum
var
User defined functions or formulas aren't supported.
It is also possible to set a calculated value for the C<total_function> using the C<total_value> sub property. This is only necessary when creating workbooks for applications that cannot calculate the value of formulas automatically. This is similar ...
$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 C<format> and to the header using C<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. 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.
=head1 FORMULAS AND FUNCTIONS IN EXCEL
=head2 Introduction
The following is a brief introduction to formulas and functions in Excel and Excel::Writer::XLSX.
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. 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 C<Excel::Writer::XLSX::Utility> module that ...
use Excel::Writer::XLSX::Utility;
( $row, $col ) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
$str = xl_rowcol_to_cell( 1, 2 ); # C2
The Excel C<$> 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 absolute
Formulas 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 C<!> the exclamation mark symbol. If worksheet names contain spaces, commas or parentheses then Excel requires that the name is enclosed in single quotes as shown in the last two examples ab...
The following table lists 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:
=====================
Operator Meaning Example
+ 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:
=====================
Operator Meaning Example
= 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:
================
Operator Meaning Example
& Concatenation "Hello " & "World!" # [1]
Reference operators:
====================
Operator Meaning Example
: 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, A3 and A4.
[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: L<http://office.microsoft.com/en-us/assistance/CH062528031033.aspx>.
In most cases a formula in Excel can be used directly in the C<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.
=head2 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. Therefore 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 (L<http://en.excel-translator.de/language/>) to help you convert the formula. It can also replace semi-colons with commas.
=head2 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 I<future> functions. Examples of these functions are C<ACOT>, C<CHISQ.DIST.RT> , C<CONFIDENCE.NORM>, C<STDE...
When written using C<write_formula()> these functions need to be fully qualified with a C<_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: L<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
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
_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.TEST
=head2 Using Tables in Formulas
Worksheet tables can be added with Excel::Writer::XLSX using the C<add_table()> method:
worksheet->add_table('B3:F7', {options});
By default tables are named C<Table1>, C<Table2>, etc., in the order that they are added. However it can also be set by the user using the C<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 C<TableX> should be referred to as C<TableX[]> (like a Perl array):
worksheet->write_formula('A5', '=VLOOKUP("Sales", Table1[], 2, FALSE');
=head2 Dealing with #NAME? errors
If there is an error in the syntax of a formula it is usually displayed in
Excel as C<#NAME?>. If you encounter an error like this you can debug it as
follows:
=over
=item 1. Ensure the formula is valid in Excel by copying and pasting it into a cell. Note, this should be done in Excel and not other applications such as OpenOffice or LibreOffice since they may have slightly different syntax.
=item 2. Ensure the formula is using comma separators instead of semi-colons, see L<Non US Excel functions and syntax> above.
=item 3. Ensure the formula is in English, see L<Non US Excel functions and syntax> above.
=item 4. Ensure that the formula doesn't contain an Excel 2010+ future function as listed in L<Formulas added in Excel 2010 and later> above. If it does then ensure that the correct prefix is used.
=back
Finally if you have completed all the previous steps and still get a C<#NAME?> error you can examine a valid Excel file to see what the correct syntax should be. To do this you should create a valid formula in Excel and save the file. You can then ex...
The following shows how to do that using Linux C<unzip> and libxml's xmllint
L<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>
=head2 Formula Results
Excel::Writer::XLSX doesn't calculate the result of a formula and instead 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 application...
If required, it is also possible to specify the calculated result of the
formula using the optional last C<value> parameter in C<write_formula>:
worksheet->write_formula('A1', '=2+2', num_format, 4);
The C<value> parameter can be a number, a string, a boolean sting (C<'TRUE'> or C<'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 C<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 C<write_number()> to write to the ap...
# 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);
=head1 WORKING WITH VBA MACROS
An Excel C<xlsm> file is exactly the same as a C<xlsx> file except that is includes an additional C<vbaProject.bin> file which contains functions and/or macros. Excel uses a different extension to differentiate between the two file formats since file...
The C<vbaProject.bin> file is a binary OLE COM container. This was the format used in older C<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 functio...
Instead a workaround is used to extract C<vbaProject.bin> files from existing xlsm files and then add these to Excel::Writer::XLSX files.
=head2 The extract_vba utility
The C<extract_vba> utility is used to extract the C<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
=head2 Adding the VBA macros to a Excel::Writer::XLSX file
Once the C<vbaProject.bin> file has been extracted it can be added to the Excel::Writer::XLSX workbook using the C<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 C<write_formula>:
$worksheet->write_formula( 'A1', '=MyMortgageCalc(200000, 25)' );
Excel files that contain functions and macros should use an C<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 C<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 C<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.
=head2 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 C<ThisWorkbook> and C<Sheet1>, C<Sheet2> etc.
If the macro uses other codenames you can set them using the workbook and worksheet C<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 C<xlsm> file and grepping the files. The following shows how to do that using libxml's xmllint L<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.
=head2 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:
=over
=item *
Start with a simple macro file, ensure that it works and then add complexity.
=item *
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 backwa...
=item *
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 C<ThisWorkbook> to refer to the current workbook and the sheet name (such as C<Sheet1>) to refer to the ...
$workbook>set_vba_name( 'MyWorkbook' );
$worksheet->set_vba_name( 'MySheet' );
=back
=head1 EXAMPLES
See L<Excel::Writer::XLSX::Examples> for a full list of examples.
=head2 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();
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/a_simple.jpg" width="640" height="420" alt="Output from a_simple.pl" /></center></p>
=end html
=head2 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();
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/stats.jpg" width="640" height="420" alt="Output from stats.pl" /></center></p>
=end html
=head2 Example 5
The following example converts a tab separated file called C<tab.txt> into an Excel file called C<tab.xlsx>.
#!/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. For converting a CSV or Tab separated or any other type of delimited text file to Excel I recommend the more rigorous csv2xls program that is part of H.Merijn Brand's L<Text::C...
See the examples/csv2xls link here: L<http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>.
=head2 Additional Examples
The following is a description of the example files that are provided
in the standard Excel::Writer::XLSX distribution. They demonstrate the
different features and options of the module. See L<Excel::Writer::XLSX::Examples> for more details.
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.
Intermediate
============
autofit.pl Examples of simulated worksheet autofit.
autofilter.pl Examples of worksheet autofilters.
array_formula.pl Examples of how to write array formulas.
cgi.pl A simple CGI program.
chart_area.pl A demo of area style charts.
chart_bar.pl A demo of bar (vertical histogram) style charts.
chart_column.pl A demo of column (histogram) style charts.
chart_line.pl A demo of line style charts.
chart_pie.pl A demo of pie style charts.
chart_doughnut.pl A demo of doughnut style charts.
chart_radar.pl A demo of radar style charts.
chart_scatter.pl A demo of scatter style charts.
chart_secondary_axis.pl A demo of a line chart with a secondary axis.
chart_combined.pl A demo of a combined column and line chart.
chart_pareto.pl A demo of a combined Pareto chart.
chart_stock.pl A demo of stock style charts.
chart_data_table.pl A demo of a chart with a data table on the axis.
chart_data_tools.pl A demo of charts with data highlighting options.
chart_data_labels.pl A demo of standard and custom chart data labels.
chart_clustered.pl A demo of a chart with a clustered axis.
chart_styles.pl A demo of the available chart styles.
chart_gauge.pl A demo of a gauge style chart.
colors.pl A demo of the colour palette and named colours.
comments1.pl Add comments to worksheet cells.
comments2.pl Add comments with advanced options.
conditional_format.pl Add conditional formats to a range of cells.
data_validate.pl An example of data validation and dropdown lists.
date_time.pl Write dates and times with write_date_time().
defined_name.pl Example of how to create defined names.
diag_border.pl A simple example of diagonal cell borders.
dynamic_arrays.pl Example of using new Excel 365 dynamic functions.
embedded_images.pl Example of embedding images in worksheet cells.
filehandle.pl Examples of working with filehandles.
headers.pl Examples of worksheet headers and footers.
hide_row_col.pl Example of hiding rows and columns.
hide_sheet.pl Simple example of hiding a worksheet.
hyperlink1.pl Shows how to create web hyperlinks.
hyperlink2.pl Examples of internal and external hyperlinks.
indent.pl An example of cell indentation.
ignore_errors.pl An example of turning off worksheet cells errors/warnings.
lambda.pl Example of using the Excel 365 LAMBDA() function.
macros.pl An example of adding macros from an existing file.
merge1.pl A simple example of cell merging.
merge2.pl A simple example of cell merging with formatting.
merge3.pl Add hyperlinks to merged cells.
merge4.pl An advanced example of merging with formatting.
merge5.pl An advanced example of merging with formatting.
merge6.pl An example of merging with Unicode strings.
mod_perl1.pl A simple mod_perl 1 program.
mod_perl2.pl A simple mod_perl 2 program.
outline.pl An example of outlines and grouping.
outline_collapsed.pl An example of collapsed outlines.
panes.pl An example of how to create panes.
properties.pl Add document properties to a workbook.
protection.pl Example of cell locking and formula hiding.
rich_strings.pl Example of strings with multiple formats.
right_to_left.pl Change default sheet direction to right to left.
sales.pl An example of a simple sales spreadsheet.
shape1.pl Insert shapes in worksheet.
shape2.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 types.
sparklines1.pl Simple sparklines demo.
sparklines2.pl Sparklines demo showing formatting options.
stats_ext.pl Same as stats.pl with external references.
stocks.pl Demonstrates conditional formatting.
watermark.pl Example of how to set a watermark image for a worksheet.
background.pl Example of how to set the background image for a worksheet.
tab_colors.pl Example of how to set worksheet tab colours.
tables.pl Add Excel tables to a worksheet.
write_handler1.pl Example of extending the write() method. Step 1.
write_handler2.pl Example of extending the write() method. Step 2.
write_handler3.pl Example of extending the write() method. Step 3.
write_handler4.pl Example of extending the write() method. Step 4.
write_to_scalar.pl Example of writing an Excel file to a Perl scalar.
Unicode
=======
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.
=head1 LIMITATIONS
The following limits are imposed by Excel 2007+:
Description Limit
-------------------------------------- ------
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 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.
(2) Per worksheet. Excel allows a greater number of non-unique hyperlinks if they are contiguous and can be grouped into a single range. This isn't supported by Excel::Writer::XLSX.
( run in 0.503 second using v1.01-cache-2.11-cpan-39bf76dae61 )