Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
package Excel::Writer::XLSX::Examples;
###############################################################################
#
# Examples - Excel::Writer::XLSX examples.
#
# A documentation only module showing the examples that are
# included in the Excel::Writer::XLSX distribution. This
# file was generated automatically via the gen_examples_pod.pl
# program that is also included in the examples directory.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# Documentation after __END__
#
use strict;
use warnings;
our $VERSION = '1.15';
1;
__END__
=pod
=encoding ISO8859-1
=head1 NAME
Examples - Excel::Writer::XLSX example programs.
=head1 DESCRIPTION
This is a documentation only module showing the examples that are
included in the L<Excel::Writer::XLSX> distribution.
This file was auto-generated via the gen_examples_pod.pl
program that is also included in the examples directory.
=head1 Example programs
The following is a list of the 96 example programs that are included in the Excel::Writer::XLSX distribution.
=over
=item * L<Example: a_simple.pl> A simple demo of some of the features.
=item * L<Example: bug_report.pl> A template for submitting bug reports.
=item * L<Example: demo.pl> A demo of some of the available features.
=item * L<Example: formats.pl> All the available formatting on several worksheets.
=item * L<Example: regions.pl> A simple example of multiple worksheets.
=item * L<Example: stats.pl> Basic formulas and functions.
=item * L<Example: autofit.pl> Examples of simulated worksheet autofit.
=item * L<Example: autofilter.pl> Examples of worksheet autofilters.
=item * L<Example: array_formula.pl> Examples of how to write array formulas.
=item * L<Example: cgi.pl> A simple CGI program.
=item * L<Example: chart_area.pl> A demo of area style charts.
=item * L<Example: chart_bar.pl> A demo of bar (vertical histogram) style charts.
=item * L<Example: chart_column.pl> A demo of column (histogram) style charts.
=item * L<Example: chart_line.pl> A demo of line style charts.
=item * L<Example: chart_pie.pl> A demo of pie style charts.
=item * L<Example: chart_doughnut.pl> A demo of doughnut style charts.
=item * L<Example: chart_radar.pl> A demo of radar style charts.
=item * L<Example: chart_scatter.pl> A demo of scatter style charts.
=item * L<Example: chart_secondary_axis.pl> A demo of a line chart with a secondary axis.
=item * L<Example: chart_combined.pl> A demo of a combined column and line chart.
=item * L<Example: chart_pareto.pl> A demo of a combined Pareto chart.
=item * L<Example: chart_stock.pl> A demo of stock style charts.
=item * L<Example: chart_data_table.pl> A demo of a chart with a data table on the axis.
=item * L<Example: chart_data_tools.pl> A demo of charts with data highlighting options.
=item * L<Example: chart_data_labels.pl> A demo of standard and custom chart data labels.
=item * L<Example: chart_clustered.pl> A demo of a chart with a clustered axis.
=item * L<Example: chart_styles.pl> A demo of the available chart styles.
=item * L<Example: chart_gauge.pl> A demo of a gauge style chart.
=item * L<Example: colors.pl> A demo of the colour palette and named colours.
=item * L<Example: comments1.pl> Add comments to worksheet cells.
=item * L<Example: comments2.pl> Add comments with advanced options.
=item * L<Example: conditional_format.pl> Add conditional formats to a range of cells.
=item * L<Example: data_validate.pl> An example of data validation and dropdown lists.
=item * L<Example: date_time.pl> Write dates and times with write_date_time().
=item * L<Example: defined_name.pl> Example of how to create defined names.
=item * L<Example: diag_border.pl> A simple example of diagonal cell borders.
=item * L<Example: dynamic_arrays.pl> Example of using new Excel 365 dynamic functions.
=item * L<Example: embedded_images.pl> Example of embedding imges in worksheet cells.
=item * L<Example: filehandle.pl> Examples of working with filehandles.
=item * L<Example: headers.pl> Examples of worksheet headers and footers.
=item * L<Example: hide_row_col.pl> Example of hiding rows and columns.
=item * L<Example: hide_sheet.pl> Simple example of hiding a worksheet.
=item * L<Example: hyperlink1.pl> Shows how to create web hyperlinks.
=item * L<Example: hyperlink2.pl> Examples of internal and external hyperlinks.
=item * L<Example: indent.pl> An example of cell indentation.
=item * L<Example: ignore_errors.pl> An example of turning off worksheet cells errors/warnings.
=item * L<Example: lambda.pl> Example of using the Excel 365 LAMBDA() function.
=item * L<Example: macros.pl> An example of adding macros from an existing file.
=item * L<Example: merge1.pl> A simple example of cell merging.
=item * L<Example: merge2.pl> A simple example of cell merging with formatting.
=item * L<Example: merge3.pl> Add hyperlinks to merged cells.
=item * L<Example: merge4.pl> An advanced example of merging with formatting.
=item * L<Example: merge5.pl> An advanced example of merging with formatting.
=item * L<Example: merge6.pl> An example of merging with Unicode strings.
=item * L<Example: mod_perl1.pl> A simple mod_perl 1 program.
=item * L<Example: mod_perl2.pl> A simple mod_perl 2 program.
=item * L<Example: outline.pl> An example of outlines and grouping.
=item * L<Example: outline_collapsed.pl> An example of collapsed outlines.
=item * L<Example: panes.pl> An example of how to create panes.
=item * L<Example: properties.pl> Add document properties to a workbook.
=item * L<Example: protection.pl> Example of cell locking and formula hiding.
=item * L<Example: rich_strings.pl> Example of strings with multiple formats.
=item * L<Example: right_to_left.pl> Change default sheet direction to right to left.
=item * L<Example: sales.pl> An example of a simple sales spreadsheet.
=item * L<Example: shape1.pl> Insert shapes in worksheet.
=item * L<Example: shape2.pl> Insert shapes in worksheet. With properties.
=item * L<Example: shape3.pl> Insert shapes in worksheet. Scaled.
=item * L<Example: shape4.pl> Insert shapes in worksheet. With modification.
=item * L<Example: shape5.pl> Insert shapes in worksheet. With connections.
=item * L<Example: shape6.pl> Insert shapes in worksheet. With connections.
=item * L<Example: shape7.pl> Insert shapes in worksheet. One to many connections.
=item * L<Example: shape8.pl> Insert shapes in worksheet. One to many connections.
=item * L<Example: shape_all.pl> Demo of all the available shape and connector types.
=item * L<Example: sparklines1.pl> Simple sparklines demo.
=item * L<Example: sparklines2.pl> Sparklines demo showing formatting options.
=item * L<Example: stats_ext.pl> Same as stats.pl with external references.
=item * L<Example: stocks.pl> Demonstrates conditional formatting.
=item * L<Example: watermark.pl> Example of how to set a watermark image for a worksheet.
=item * L<Example: background.pl> Example of how to set the background image for a worksheet.
=item * L<Example: tab_colors.pl> Example of how to set worksheet tab colours.
=item * L<Example: tables.pl> Add Excel tables to a worksheet.
=item * L<Example: write_handler1.pl> Example of extending the write() method. Step 1.
=item * L<Example: write_handler2.pl> Example of extending the write() method. Step 2.
=item * L<Example: write_handler3.pl> Example of extending the write() method. Step 3.
=item * L<Example: write_handler4.pl> Example of extending the write() method. Step 4.
=item * L<Example: write_to_scalar.pl> Example of writing an Excel file to a Perl scalar.
=item * L<Example: unicode_2022_jp.pl> Japanese: ISO-2022-JP.
=item * L<Example: unicode_8859_11.pl> Thai: ISO-8859_11.
=item * L<Example: unicode_8859_7.pl> Greek: ISO-8859_7.
=item * L<Example: unicode_big5.pl> Chinese: BIG5.
=item * L<Example: unicode_cp1251.pl> Russian: CP1251.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
=item * L<Example: unicode_polish_utf8.pl> Polish : UTF8.
=item * L<Example: unicode_shift_jis.pl> Japanese: Shift JIS.
=back
=head2 Example: a_simple.pl
A simple example of how to use the Excel::Writer::XLSX module to
write text and numbers to an Excel xlsx file.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=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
Source code for this example:
#!/usr/bin/perl -w
#######################################################################
#
# A simple example of how to use the Excel::Writer::XLSX module to
# write text and numbers to an Excel xlsx file.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use Excel::Writer::XLSX;
# Create a new workbook called simple.xls and add a worksheet
my $workbook = Excel::Writer::XLSX->new( 'a_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 ); # Writes 3
$worksheet->write( 3, 0, 3.00000 ); # Writes 3
$worksheet->write( 4, 0, 3.00001 ); # Writes 3.00001
$worksheet->write( 5, 0, 3.14159 ); # TeX revision no.?
# Write some formulas
$worksheet->write( 7, 0, '=A3 + A6' );
$worksheet->write( 8, 0, '=IF(A5>3,"Yes", "No")' );
# Write a hyperlink
$worksheet->write( 10, 0, 'http://www.perl.com/' );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/a_simple.pl>
=head2 Example: bug_report.pl
A template for submitting a bug report.
Run this program and read the output from the command line.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl -w
###############################################################################
#
# A template for submitting a bug report.
#
# Run this program and read the output from the command line.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
print << 'HINTS_1';
REPORTING A BUG OR ASKING A QUESTION
Feel free to report bugs or ask questions. However, to save time
consider the following steps first:
Read the documentation:
The Excel::Writer::XLSX documentation has been refined in
response to user questions. Therefore, if you have a question it is
possible that someone else has asked it before you and that it is
already addressed in the documentation. Since there is a lot of
documentation to get through you should at least read the table of
contents and search for keywords that you are interested in.
Look at the example programs:
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
}
$workbook->close();
__DATA__
Region Item Volume Month
East Apple 9000 July
East Apple 5000 July
South Orange 9000 September
North Apple 2000 November
West Apple 9000 November
South Pear 7000 October
North Pear 9000 August
West Orange 1000 December
West Grape 1000 November
South Pear 10000 April
West Grape 6000 January
South Orange 3000 May
North Apple 3000 December
South Apple 7000 February
West Grape 1000 December
East Grape 8000 February
South Grape 10000 June
West Pear 7000 December
South Apple 2000 October
East Grape 7000 December
North Grape 6000 April
East Pear 8000 February
North Apple 7000 August
North Orange 7000 July
North Apple 6000 June
South Grape 8000 September
West Apple 3000 October
South Orange 10000 November
West Grape 4000 July
North Orange 5000 August
East Orange 1000 November
East Orange 4000 October
North Grape 5000 August
East Apple 1000 December
South Apple 10000 March
East Grape 7000 October
West Grape 1000 September
East Grape 10000 October
South Orange 8000 March
North Apple 4000 July
South Orange 5000 July
West Apple 4000 June
East Apple 5000 April
North Pear 3000 August
East Grape 9000 November
North Orange 8000 October
East Apple 10000 June
South Pear 1000 December
North Grape 10000 July
East Grape 6000 February
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/autofilter.pl>
=head2 Example: array_formula.pl
Example of how to use the Excel::Writer::XLSX module to write simple
array formulas.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/array_formula.jpg" width="640" height="420" alt="Output from array_formula.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# Example of how to use the Excel::Writer::XLSX module to write simple
# array formulas.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
# Create a new workbook and add a worksheet
my $workbook = Excel::Writer::XLSX->new( 'array_formula.xlsx' );
my $worksheet = $workbook->add_worksheet();
# Write some test data.
$worksheet->write( 'B1', [ [ 500, 10 ], [ 300, 15 ] ] );
$worksheet->write( 'B5', [ [ 1, 2, 3 ], [ 20234, 21003, 10000 ] ] );
# Write an array formula that returns a single value
$worksheet->write( 'A1', '{=SUM(B1:C1*B2:C2)}' );
# Same as above but more verbose.
$worksheet->write_array_formula( 'A2:A2', '{=SUM(B1:C1*B2:C2)}' );
# Write an array formula that returns a range of values
$worksheet->write_array_formula( 'A5:A7', '{=TREND(C5:C7,B5:B7)}' );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/array_formula.pl>
=head2 Example: cgi.pl
Example of how to use the Excel::Writer::XLSX module to send an Excel
file to a browser in a CGI program.
On Windows the hash-bang line should be something like:
#!C:\Perl\bin\perl.exe
The "Content-Disposition" line will cause a prompt to be generated to save
the file. If you want to stream the file to the browser instead, comment out
that line as shown below.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
###############################################################################
#
# Example of how to use the Excel::Writer::XLSX module to send an Excel
# file to a browser in a CGI program.
#
# On Windows the hash-bang line should be something like:
#
# #!C:\Perl\bin\perl.exe
#
# The "Content-Disposition" line will cause a prompt to be generated to save
# the file. If you want to stream the file to the browser instead, comment out
# that line as shown below.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
# Set the filename and send the content type
my $filename = "cgitest.xlsx";
print "Content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet\n";
# The Content-Disposition will generate a prompt to save the file. If you want
# to stream the file to the browser, comment out the following line.
print "Content-Disposition: attachment; filename=$filename\n";
print "\n";
# Redirect the output to STDOUT. Binmode the filehandle in case it is needed.
binmode STDOUT;
my $workbook = Excel::Writer::XLSX->new( \*STDOUT );
my $worksheet = $workbook->add_worksheet();
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Turn off the chart legend.
$chart9->set_legend( none => 1 );
# Insert the chart into the worksheet (with an offset).
$worksheet->insert_chart( 'D130', $chart9, { x_offset => 25, y_offset => 10 } );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_data_labels.pl>
=head2 Example: chart_clustered.pl
A demo of a clustered category chart in Excel::Writer::XLSX.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# A demo of a clustered category chart in Excel::Writer::XLSX.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'chart_clustered.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $bold = $workbook->add_format( bold => 1 );
# Add the worksheet data that the charts will refer to.
my $headings = [ 'Types', 'Sub Type', 'Value 1', 'Value 2', 'Value 3' ];
my $data = [
[ 'Type 1', 'Sub Type A', 5000, 8000, 6000 ],
[ '', 'Sub Type B', 2000, 3000, 4000 ],
[ '', 'Sub Type C', 250, 1000, 2000 ],
[ 'Type 2', 'Sub Type D', 6000, 6000, 6500 ],
[ '', 'Sub Type E', 500, 300, 200 ],
];
$worksheet->write( 'A1', $headings, $bold );
$worksheet->write_col( 'A2', $data );
# Create a new chart object. In this case an embedded chart.
my $chart = $workbook->add_chart( type => 'column', embedded => 1 );
# Configure the series. Note, that the categories are 2D ranges (from column A
# to column B). This creates the clusters. The series are shown as formula
# strings for clarity but you can also use the array syntax. See the docs.
$chart->add_series(
name => '=Sheet1!$C$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$C$2:$C$6',
);
$chart->add_series(
name => '=Sheet1!$D$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$D$2:$D$6',
);
$chart->add_series(
name => '=Sheet1!$E$1',
categories => '=Sheet1!$A$2:$B$6',
values => '=Sheet1!$E$2:$E$6',
);
# Set the Excel chart style.
$chart->set_style( 37 );
# Turn off the legend.
$chart->set_legend( position => 'none' );
# Insert the chart into the worksheet.
$worksheet->insert_chart( 'G3', $chart );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/chart_clustered.pl>
=head2 Example: chart_styles.pl
An example showing all 48 default chart styles available in Excel 2007
using Excel::Writer::XLSX.. Note, these styles are not the same as the
styles available in Excel 2013.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl
#######################################################################
#
# An example showing all 48 default chart styles available in Excel 2007
# using Excel::Writer::XLSX.. Note, these styles are not the same as the
# styles available in Excel 2013.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
validate => 'list',
source => '=$E$4:$G$4',
}
);
#
# Example 7. Limiting input to a date in a fixed range.
#
$txt = 'Enter a date between 1/1/2008 and 12/12/2008';
$row += 2;
$worksheet->write( $row, 0, $txt );
$worksheet->data_validation(
$row, 1,
{
validate => 'date',
criteria => 'between',
minimum => '2008-01-01T',
maximum => '2008-12-12T',
}
);
#
# Example 8. Limiting input to a time in a fixed range.
#
$txt = 'Enter a time between 6:00 and 12:00';
$row += 2;
$worksheet->write( $row, 0, $txt );
$worksheet->data_validation(
$row, 1,
{
validate => 'time',
criteria => 'between',
minimum => 'T06:00',
maximum => 'T12:00',
}
);
#
# Example 9. Limiting input to a string greater than a fixed length.
#
$txt = 'Enter a string longer than 3 characters';
$row += 2;
$worksheet->write( $row, 0, $txt );
$worksheet->data_validation(
$row, 1,
{
validate => 'length',
criteria => '>',
value => 3,
}
);
#
# Example 10. Limiting input based on a formula.
#
$txt = 'Enter a value if the following is true "=AND(F5=50,G5=60)"';
$row += 2;
$worksheet->write( $row, 0, $txt );
$worksheet->data_validation(
$row, 1,
{
validate => 'custom',
value => '=AND(F5=50,G5=60)',
}
);
#
# Example 11. Displaying and modify data validation messages.
#
$txt = 'Displays a message when you select the cell';
$row += 2;
$worksheet->write( $row, 0, $txt );
$worksheet->data_validation(
$row, 1,
{
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
input_title => 'Enter an integer:',
input_message => 'between 1 and 100',
}
);
#
# Example 12. Displaying and modify data validation messages.
#
$txt = 'Display a custom error message when integer isn\'t between 1 and 100';
$row += 2;
$worksheet->write( $row, 0, $txt );
$worksheet->data_validation(
$row, 1,
{
validate => 'integer',
criteria => 'between',
minimum => 1,
maximum => 100,
input_title => 'Enter an integer:',
input_message => 'between 1 and 100',
error_title => 'Input value is not valid!',
error_message => 'It should be an integer between 1 and 100',
}
);
#
# Example 13. Displaying and modify data validation messages.
#
$txt =
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$worksheet->write( $row, 1, 'Invalid date. Written as string.', $bold );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/date_time.pl>
=head2 Example: defined_name.pl
Example of how to create defined names in an Excel::Writer::XLSX file.
This method is used to define a user friendly name to represent a value,
a single cell or a range of cells in a workbook.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/defined_name.jpg" width="640" height="420" alt="Output from defined_name.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
##############################################################################
#
# Example of how to create defined names in an Excel::Writer::XLSX file.
#
# This method is used to define a user friendly name to represent a value,
# a single cell or a range of cells in a workbook.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'defined_name.xlsx' );
my $worksheet1 = $workbook->add_worksheet();
my $worksheet2 = $workbook->add_worksheet();
# Define some global/workbook names.
$workbook->define_name( 'Exchange_rate', '=0.96' );
$workbook->define_name( 'Sales', '=Sheet1!$G$1:$H$10' );
# Define a local/worksheet name.
$workbook->define_name( 'Sheet2!Sales', '=Sheet2!$G$1:$G$10' );
# Write some text in the file and one of the defined names in a formula.
for my $worksheet ( $workbook->sheets() ) {
$worksheet->set_column( 'A:A', 45 );
$worksheet->write( 'A1', 'This worksheet contains some defined names.' );
$worksheet->write( 'A2', 'See Formulas -> Name Manager above.' );
$worksheet->write( 'A3', 'Example formula in cell B3 ->' );
$worksheet->write( 'B3', '=Exchange_rate' );
}
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/defined_name.pl>
=head2 Example: diag_border.pl
A simple formatting example that demonstrates how to add a diagonal cell
border with Excel::Writer::XLSX
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/diag_border.jpg" width="640" height="420" alt="Output from diag_border.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl -w
##############################################################################
#
# A simple formatting example that demonstrates how to add a diagonal cell
# border with Excel::Writer::XLSX
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'diag_border.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $format1 = $workbook->add_format( diag_type => 1 );
my $format2 = $workbook->add_format( diag_type => 2 );
my $format3 = $workbook->add_format( diag_type => 3 );
my $format4 = $workbook->add_format(
diag_type => 3,
diag_border => 7,
diag_color => 'red',
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$worksheet5->set_column_pixels('A:A', 100);
$worksheet5->set_column_pixels('D:D', 20);
#
# Example of using the XMATCH() function.
#
$worksheet6->write('D2', '=XMATCH(C2,A2:A6)');
# Write the data the function will work on.
$worksheet6->write('A1', 'Product', $header1);
$worksheet6->write('A2', 'Apple');
$worksheet6->write('A3', 'Grape');
$worksheet6->write('A4', 'Pear');
$worksheet6->write('A5', 'Banana');
$worksheet6->write('A6', 'Cherry');
$worksheet6->write('C1', 'Product' , $header2);
$worksheet6->write('D1', 'Position', $header2);
$worksheet6->write('C2', 'Grape');
$worksheet6->set_column_pixels('B:B', 20);
#
# Example of using the RANDARRAY() function.
#
$worksheet7->write('A1', '=RANDARRAY(5,3,1,100, TRUE)');
#
# Example of using the SEQUENCE() function.
#
$worksheet8->write('A1', '=SEQUENCE(4,5)');
#
# Example of using the Spill range operator.
#
$worksheet9->write('H2', '=ANCHORARRAY(F2)');
$worksheet9->write('J2', '=COUNTA(ANCHORARRAY(F2))');
# Write the data the to work on.
$worksheet9->write( 'F2', '=UNIQUE(B2:B17)');
$worksheet9->write('F1', 'Unique', $header2);
$worksheet9->write('H1', 'Spill', $header2);
$worksheet9->write('J1', 'Spill', $header2);
write_worksheet_data($worksheet9, $header1);
$worksheet9->set_column_pixels('E:E', 20);
$worksheet9->set_column_pixels('G:G', 20);
$worksheet9->set_column_pixels('I:I', 20);
#
# Example of using dynamic ranges with older Excel functions.
#
$worksheet10->write_dynamic_array_formula('B1:B3', '=LEN(A1:A3)');
# Write the data the to work on.
$worksheet10->write('A1', 'Foo');
$worksheet10->write('A2', 'Food');
$worksheet10->write('A3', 'Frood');
# Close the workbook.
$workbook->close();
# Utility function to write the data some of the functions work on.
sub write_worksheet_data {
my $worksheet = shift;
my $header = shift;
$worksheet->write('A1', 'Region', $header);
$worksheet->write('B1', 'Sales Rep', $header);
$worksheet->write('C1', 'Product', $header);
$worksheet->write('D1', 'Units', $header);
my @data = (
['East', 'Tom', 'Apple', 6380],
['West', 'Fred', 'Grape', 5619],
['North', 'Amy', 'Pear', 4565],
['South', 'Sal', 'Banana', 5323],
['East', 'Fritz', 'Apple', 4394],
['West', 'Sravan', 'Grape', 7195],
['North', 'Xi', 'Pear', 5231],
['South', 'Hector', 'Banana', 2427],
['East', 'Tom', 'Banana', 4213],
['West', 'Fred', 'Pear', 3239],
['North', 'Amy', 'Grape', 6520],
['South', 'Sal', 'Apple', 1310],
['East', 'Fritz', 'Banana', 6274],
['West', 'Sravan', 'Pear', 4894],
['North', 'Xi', 'Grape', 7580],
['South', 'Hector', 'Apple', 9814],
);
my $row_num = 1;
for my $row_data (@data) {
$worksheet->write_row($row_num, 0, $row_data);
$row_num++;
}
}
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/dynamic_arrays.pl>
=head2 Example: embedded_images.pl
An example of embedding images into a worksheet cells using the the
Excel::Writer::XLSX module. This is equivalent to Excel's "Place in cell"
image insert.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'indent.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $indent1 = $workbook->add_format( indent => 1 );
my $indent2 = $workbook->add_format( indent => 2 );
$worksheet->set_column( 'A:A', 40 );
$worksheet->write( 'A1', "This text is indented 1 level", $indent1 );
$worksheet->write( 'A2', "This text is indented 2 levels", $indent2 );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/indent.pl>
=head2 Example: ignore_errors.pl
An example of turning off worksheet cells errors/warnings using the
Excel::Writer::XLSX module.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#!/usr/bin/perl -w
#######################################################################
#
# An example of turning off worksheet cells errors/warnings using the
# Excel::Writer::XLSX module.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'ignore_errors.xlsx' );
my $worksheet = $workbook->add_worksheet();
# Write strings that looks like numbers. This will cause an Excel warning.
$worksheet->write_string('C2', '123');
$worksheet->write_string('C3', '123');
# Write a divide by zero formula. This will also cause an Excel warning.
$worksheet->write_formula('C5', '=1/0');
$worksheet->write_formula('C6', '=1/0');
# Turn off some of the warnings:
$worksheet->ignore_errors({number_stored_as_text => 'C3', eval_error => 'C6'});
# Write some descriptions for the cells and make the column wider for clarity.
$worksheet->set_column('B:B', 16);
$worksheet->write('B2', 'Warning:');
$worksheet->write('B3', 'Warning turned off:');
$worksheet->write('B5', 'Warning:');
$worksheet->write('B6', 'Warning turned off:');
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/ignore_errors.pl>
=head2 Example: lambda.pl
An example of using the new Excel LAMBDA() function with the
Excel::Writer::XLSX module. Note, this function is only currently available
if you are subscribed to the Microsoft Office Beta program.
#!/usr/bin/perl -w
#######################################################################
#
# An example of using the new Excel LAMBDA() function with the
# Excel::Writer::XLSX module. Note, this function is only currently available
# if you are subscribed to the Microsoft Office Beta program.
#
# Copyright 2000-2021, John McNamara, jmcnamara@cpan.org
#
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'lambda.xlsx' );
my $worksheet = $workbook->add_worksheet();
$worksheet->write('A1', 'Note: Lambda functions currently only work with the Beta Channel versions of Excel 365');
# Write a Lambda function to convert Fahrenheit to Celsius to a cell.
#
# Note that the lambda function parameters must be prefixed with
# "_xlpm.". These prefixes won't show up in Excel.
$worksheet->write('A2', '=LAMBDA(_xlpm.temp, (5/9) * (_xlpm.temp-32))(32)');
# Create the same formula (without an argument) as a defined name and use that
# to calculate a value.
#
# Note that the formula name is prefixed with "_xlfn." (this is normally
# converted automatically by write_formula() but isn't for defined names)
# and note that the lambda function parameters are prefixed with
# "_xlpm.". These prefixes won't show up in Excel.
$workbook->define_name('ToCelsius',
'=_xlfn.LAMBDA(_xlpm.temp, (5/9) * (_xlpm.temp-32))');
$worksheet->write_dynamic_array_formula( 'A3', '=ToCelsius(212)' );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/lambda.pl>
=head2 Example: macros.pl
An example of adding macros to an Excel::Writer::XLSX file using
a VBA project file extracted from an existing Excel xlsm file.
The C<extract_vba> utility supplied with Excel::Writer::XLSX can be
used to extract the vbaProject.bin file.
An embedded macro is connected to a form button on the worksheet.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/macros.jpg" width="640" height="420" alt="Output from macros.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# An example of adding macros to an Excel::Writer::XLSX file using
# a VBA project file extracted from an existing Excel xlsm file.
#
# The C<extract_vba> utility supplied with Excel::Writer::XLSX can be
# used to extract the vbaProject.bin file.
#
# An embedded macro is connected to a form button on the worksheet.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
# Note the file extension should be .xlsm.
my $workbook = Excel::Writer::XLSX->new( 'macros.xlsm' );
my $worksheet = $workbook->add_worksheet();
$worksheet->set_column( 'A:A', 30 );
# Add the VBA project binary.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
#
# ------------------------------------------
# 1 2 3 | | A | B | C | D | ...
# ------------------------------------------
# | 1 | A | | | | ...
# + | . | ... | ... | ... | ... | ...
#
# See the main Excel::Writer::XLSX documentation for more information.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
# Create a new workbook and add some worksheets
my $workbook = Excel::Writer::XLSX->new( 'outline.xlsx' );
my $worksheet1 = $workbook->add_worksheet( 'Outlined Rows' );
my $worksheet2 = $workbook->add_worksheet( 'Collapsed Rows' );
my $worksheet3 = $workbook->add_worksheet( 'Outline Columns' );
my $worksheet4 = $workbook->add_worksheet( 'Outline levels' );
# Add a general format
my $bold = $workbook->add_format( bold => 1 );
###############################################################################
#
# Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL()
# functions so that it looks like the type of automatic outlines that are
# generated when you use the Excel Data->SubTotals menu item.
#
# For outlines the important parameters are $hidden and $level. Rows with the
# same $level are grouped together. The group will be collapsed if $hidden is
# non-zero. $height and $XF are assigned default values if they are undef.
#
# The syntax is: set_row($row, $height, $XF, $hidden, $level, $collapsed)
#
$worksheet1->set_row( 1, undef, undef, 0, 2 );
$worksheet1->set_row( 2, undef, undef, 0, 2 );
$worksheet1->set_row( 3, undef, undef, 0, 2 );
$worksheet1->set_row( 4, undef, undef, 0, 2 );
$worksheet1->set_row( 5, undef, undef, 0, 1 );
$worksheet1->set_row( 6, undef, undef, 0, 2 );
$worksheet1->set_row( 7, undef, undef, 0, 2 );
$worksheet1->set_row( 8, undef, undef, 0, 2 );
$worksheet1->set_row( 9, undef, undef, 0, 2 );
$worksheet1->set_row( 10, undef, undef, 0, 1 );
# Add a column format for clarity
$worksheet1->set_column( 'A:A', 20 );
# Add the data, labels and formulas
$worksheet1->write( 'A1', 'Region', $bold );
$worksheet1->write( 'A2', 'North' );
$worksheet1->write( 'A3', 'North' );
$worksheet1->write( 'A4', 'North' );
$worksheet1->write( 'A5', 'North' );
$worksheet1->write( 'A6', 'North Total', $bold );
$worksheet1->write( 'B1', 'Sales', $bold );
$worksheet1->write( 'B2', 1000 );
$worksheet1->write( 'B3', 1200 );
$worksheet1->write( 'B4', 900 );
$worksheet1->write( 'B5', 1200 );
$worksheet1->write( 'B6', '=SUBTOTAL(9,B2:B5)', $bold );
$worksheet1->write( 'A7', 'South' );
$worksheet1->write( 'A8', 'South' );
$worksheet1->write( 'A9', 'South' );
$worksheet1->write( 'A10', 'South' );
$worksheet1->write( 'A11', 'South Total', $bold );
$worksheet1->write( 'B7', 400 );
$worksheet1->write( 'B8', 600 );
$worksheet1->write( 'B9', 500 );
$worksheet1->write( 'B10', 600 );
$worksheet1->write( 'B11', '=SUBTOTAL(9,B7:B10)', $bold );
$worksheet1->write( 'A12', 'Grand Total', $bold );
$worksheet1->write( 'B12', '=SUBTOTAL(9,B2:B10)', $bold );
###############################################################################
#
# Example 2: Create a worksheet with outlined rows. This is the same as the
# previous example except that the rows are collapsed.
# Note: We need to indicate the row that contains the collapsed symbol '+'
# with the optional parameter, $collapsed.
# The group will be collapsed if $hidden is non-zero.
# The syntax is: set_row($row, $height, $XF, $hidden, $level, $collapsed)
#
$worksheet2->set_row( 1, undef, undef, 1, 2 );
$worksheet2->set_row( 2, undef, undef, 1, 2 );
$worksheet2->set_row( 3, undef, undef, 1, 2 );
$worksheet2->set_row( 4, undef, undef, 1, 2 );
$worksheet2->set_row( 5, undef, undef, 1, 1 );
$worksheet2->set_row( 6, undef, undef, 1, 2 );
$worksheet2->set_row( 7, undef, undef, 1, 2 );
$worksheet2->set_row( 8, undef, undef, 1, 2 );
$worksheet2->set_row( 9, undef, undef, 1, 2 );
$worksheet2->set_row( 10, undef, undef, 1, 1 );
$worksheet2->set_row( 11, undef, undef, 0, 0, 1 );
# Add a column format for clarity
$worksheet2->set_column( 'A:A', 20 );
# Add the data, labels and formulas
$worksheet2->write( 'A1', 'Region', $bold );
$worksheet2->write( 'A2', 'North' );
$worksheet2->write( 'A3', 'North' );
$worksheet2->write( 'A4', 'North' );
$worksheet2->write( 'A5', 'North' );
$worksheet2->write( 'A6', 'North Total', $bold );
$worksheet2->write( 'B1', 'Sales', $bold );
$worksheet2->write( 'B2', 1000 );
$worksheet2->write( 'B3', 1200 );
$worksheet2->write( 'B4', 900 );
$worksheet2->write( 'B5', 1200 );
$worksheet2->write( 'B6', '=SUBTOTAL(9,B2:B5)', $bold );
$worksheet2->write( 'A7', 'South' );
$worksheet2->write( 'A8', 'South' );
$worksheet2->write( 'A9', 'South' );
$worksheet2->write( 'A10', 'South' );
$worksheet2->write( 'A11', 'South Total', $bold );
$worksheet2->write( 'B7', 400 );
$worksheet2->write( 'B8', 600 );
$worksheet2->write( 'B9', 500 );
$worksheet2->write( 'B10', 600 );
$worksheet2->write( 'B11', '=SUBTOTAL(9,B7:B10)', $bold );
$worksheet2->write( 'A12', 'Grand Total', $bold );
$worksheet2->write( 'B12', '=SUBTOTAL(9,B2:B10)', $bold );
###############################################################################
#
# Example 3: Create a worksheet with outlined columns.
#
my $data = [
[ 'Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', ' Total' ],
[ 'North', 50, 20, 15, 25, 65, 80, '=SUM(B2:G2)' ],
[ 'South', 10, 20, 30, 50, 50, 50, '=SUM(B3:G3)' ],
[ 'East', 45, 75, 50, 15, 75, 100, '=SUM(B4:G4)' ],
[ 'West', 15, 15, 55, 35, 20, 50, '=SUM(B5:G5)' ],
];
# Add bold format to the first row
$worksheet3->set_row( 0, undef, $bold );
# Syntax: set_column($col1, $col2, $width, $XF, $hidden, $level, $collapsed)
$worksheet3->set_column( 'A:A', 10, $bold );
$worksheet3->set_column( 'B:G', 5, undef, 0, 1 );
$worksheet3->set_column( 'H:H', 10 );
# Write the data and a formula
$worksheet3->write_col( 'A1', $data );
$worksheet3->write( 'H6', '=SUM(H2:H5)', $bold );
###############################################################################
#
# Example 4: Show all possible outline levels.
#
my $levels = [
"Level 1", "Level 2", "Level 3", "Level 4", "Level 5", "Level 6",
"Level 7", "Level 6", "Level 5", "Level 4", "Level 3", "Level 2",
"Level 1"
];
$worksheet4->write_col( 'A1', $levels );
$worksheet4->set_row( 0, undef, undef, undef, 1 );
$worksheet4->set_row( 1, undef, undef, undef, 2 );
$worksheet4->set_row( 2, undef, undef, undef, 3 );
$worksheet4->set_row( 3, undef, undef, undef, 4 );
$worksheet4->set_row( 4, undef, undef, undef, 5 );
$worksheet4->set_row( 5, undef, undef, undef, 6 );
$worksheet4->set_row( 6, undef, undef, undef, 7 );
$worksheet4->set_row( 7, undef, undef, undef, 6 );
$worksheet4->set_row( 8, undef, undef, undef, 5 );
$worksheet4->set_row( 9, undef, undef, undef, 4 );
$worksheet4->set_row( 10, undef, undef, undef, 3 );
$worksheet4->set_row( 11, undef, undef, undef, 2 );
$worksheet4->set_row( 12, undef, undef, undef, 1 );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/outline.pl>
=head2 Example: outline_collapsed.pl
Example of how to use Excel::Writer::XLSX to generate Excel outlines and
grouping.
These examples focus mainly on collapsed outlines. See also the
outlines.pl example program for more general examples.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/outline_collapsed.jpg" width="640" height="420" alt="Output from outline_collapsed.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
###############################################################################
#
# Example of how to use Excel::Writer::XLSX to generate Excel outlines and
# grouping.
#
# These examples focus mainly on collapsed outlines. See also the
# outlines.pl example program for more general examples.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
# Create a new workbook and add some worksheets
my $workbook = Excel::Writer::XLSX->new( 'outline_collapsed.xlsx' );
my $worksheet1 = $workbook->add_worksheet( 'Outlined Rows' );
my $worksheet2 = $workbook->add_worksheet( 'Collapsed Rows 1' );
my $worksheet3 = $workbook->add_worksheet( 'Collapsed Rows 2' );
my $worksheet4 = $workbook->add_worksheet( 'Collapsed Rows 3' );
my $worksheet5 = $workbook->add_worksheet( 'Outline Columns' );
my $worksheet6 = $workbook->add_worksheet( 'Collapsed Columns' );
# Add a general format
my $bold = $workbook->add_format( bold => 1 );
#
# This function will generate the same data and sub-totals on each worksheet.
#
sub create_sub_totals {
my $worksheet = $_[0];
# Add a column format for clarity
$worksheet->set_column( 'A:A', 20 );
# Add the data, labels and formulas
$worksheet->write( 'A1', 'Region', $bold );
$worksheet->write( 'A2', 'North' );
$worksheet->write( 'A3', 'North' );
$worksheet->write( 'A4', 'North' );
$worksheet->write( 'A5', 'North' );
$worksheet->write( 'A6', 'North Total', $bold );
$worksheet->write( 'B1', 'Sales', $bold );
$worksheet->write( 'B2', 1000 );
$worksheet->write( 'B3', 1200 );
$worksheet->write( 'B4', 900 );
$worksheet->write( 'B5', 1200 );
$worksheet->write( 'B6', '=SUBTOTAL(9,B2:B5)', $bold );
$worksheet->write( 'A7', 'South' );
$worksheet->write( 'A8', 'South' );
$worksheet->write( 'A9', 'South' );
$worksheet->write( 'A10', 'South' );
$worksheet->write( 'A11', 'South Total', $bold );
$worksheet->write( 'B7', 400 );
$worksheet->write( 'B8', 600 );
$worksheet->write( 'B9', 500 );
$worksheet->write( 'B10', 600 );
$worksheet->write( 'B11', '=SUBTOTAL(9,B7:B10)', $bold );
$worksheet->write( 'A12', 'Grand Total', $bold );
$worksheet->write( 'B12', '=SUBTOTAL(9,B2:B10)', $bold );
}
###############################################################################
#
# Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL()
# functions so that it looks like the type of automatic outlines that are
# generated when you use the Excel Data->SubTotals menu item.
#
# The syntax is: set_row($row, $height, $XF, $hidden, $level, $collapsed)
$worksheet1->set_row( 1, undef, undef, 0, 2 );
$worksheet1->set_row( 2, undef, undef, 0, 2 );
$worksheet1->set_row( 3, undef, undef, 0, 2 );
$worksheet1->set_row( 4, undef, undef, 0, 2 );
$worksheet1->set_row( 5, undef, undef, 0, 1 );
$worksheet1->set_row( 6, undef, undef, 0, 2 );
$worksheet1->set_row( 7, undef, undef, 0, 2 );
$worksheet1->set_row( 8, undef, undef, 0, 2 );
$worksheet1->set_row( 9, undef, undef, 0, 2 );
$worksheet1->set_row( 10, undef, undef, 0, 1 );
# Write the sub-total data that is common to the row examples.
create_sub_totals( $worksheet1 );
###############################################################################
#
# Example 2: Create a worksheet with collapsed outlined rows.
# This is the same as the example 1 except that the all rows are collapsed.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$worksheet3->set_row( 1, undef, undef, 1, 2 );
$worksheet3->set_row( 2, undef, undef, 1, 2 );
$worksheet3->set_row( 3, undef, undef, 1, 2 );
$worksheet3->set_row( 4, undef, undef, 1, 2 );
$worksheet3->set_row( 5, undef, undef, 0, 1, 1 );
$worksheet3->set_row( 6, undef, undef, 1, 2 );
$worksheet3->set_row( 7, undef, undef, 1, 2 );
$worksheet3->set_row( 8, undef, undef, 1, 2 );
$worksheet3->set_row( 9, undef, undef, 1, 2 );
$worksheet3->set_row( 10, undef, undef, 0, 1, 1 );
# Write the sub-total data that is common to the row examples.
create_sub_totals( $worksheet3 );
###############################################################################
#
# Example 4: Create a worksheet with outlined rows.
# Same as the example 1 except that the two sub-totals are collapsed.
$worksheet4->set_row( 1, undef, undef, 1, 2 );
$worksheet4->set_row( 2, undef, undef, 1, 2 );
$worksheet4->set_row( 3, undef, undef, 1, 2 );
$worksheet4->set_row( 4, undef, undef, 1, 2 );
$worksheet4->set_row( 5, undef, undef, 1, 1, 1 );
$worksheet4->set_row( 6, undef, undef, 1, 2 );
$worksheet4->set_row( 7, undef, undef, 1, 2 );
$worksheet4->set_row( 8, undef, undef, 1, 2 );
$worksheet4->set_row( 9, undef, undef, 1, 2 );
$worksheet4->set_row( 10, undef, undef, 1, 1, 1 );
$worksheet4->set_row( 11, undef, undef, 0, 0, 1 );
# Write the sub-total data that is common to the row examples.
create_sub_totals( $worksheet4 );
###############################################################################
#
# Example 5: Create a worksheet with outlined columns.
#
my $data = [
[ 'Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Total' ],
[ 'North', 50, 20, 15, 25, 65, 80,, '=SUM(B2:G2)' ],
[ 'South', 10, 20, 30, 50, 50, 50,, '=SUM(B3:G3)' ],
[ 'East', 45, 75, 50, 15, 75, 100,, '=SUM(B4:G4)' ],
[ 'West', 15, 15, 55, 35, 20, 50,, '=SUM(B5:G6)' ],
];
# Add bold format to the first row
$worksheet5->set_row( 0, undef, $bold );
# Syntax: set_column($col1, $col2, $width, $XF, $hidden, $level, $collapsed)
$worksheet5->set_column( 'A:A', 10, $bold );
$worksheet5->set_column( 'B:G', 5, undef, 0, 1 );
$worksheet5->set_column( 'H:H', 10 );
# Write the data and a formula
$worksheet5->write_col( 'A1', $data );
$worksheet5->write( 'H6', '=SUM(H2:H5)', $bold );
###############################################################################
#
# Example 6: Create a worksheet with collapsed outlined columns.
# This is the same as the previous example except collapsed columns.
# Add bold format to the first row
$worksheet6->set_row( 0, undef, $bold );
# Syntax: set_column($col1, $col2, $width, $XF, $hidden, $level, $collapsed)
$worksheet6->set_column( 'A:A', 10, $bold );
$worksheet6->set_column( 'B:G', 5, undef, 1, 1 );
$worksheet6->set_column( 'H:H', 10, undef, 0, 0, 1 );
# Write the data and a formula
$worksheet6->write_col( 'A1', $data );
$worksheet6->write( 'H6', '=SUM(H2:H5)', $bold );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/outline_collapsed.pl>
=head2 Example: panes.pl
Example of using the Excel::Writer::XLSX module to create worksheet panes.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/panes.jpg" width="640" height="420" alt="Output from panes.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# Example of using the Excel::Writer::XLSX module to create worksheet panes.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'panes.xlsx' );
my $worksheet1 = $workbook->add_worksheet( 'Panes 1' );
my $worksheet2 = $workbook->add_worksheet( 'Panes 2' );
my $worksheet3 = $workbook->add_worksheet( 'Panes 3' );
my $worksheet4 = $workbook->add_worksheet( 'Panes 4' );
# Freeze panes
$worksheet1->freeze_panes( 1, 0 ); # 1 row
$worksheet2->freeze_panes( 0, 1 ); # 1 column
$worksheet3->freeze_panes( 1, 1 ); # 1 row and column
# Split panes.
# The divisions must be specified in terms of row and column dimensions.
# The default row height is 15 and the default column width is 8.43
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
An example of adding document properties to a Excel::Writer::XLSX file.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/properties.jpg" width="640" height="420" alt="Output from properties.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
##############################################################################
#
# An example of adding document properties to a Excel::Writer::XLSX file.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'properties.xlsx' );
my $worksheet = $workbook->add_worksheet();
$workbook->set_properties(
title => 'This is an example spreadsheet',
subject => 'With document properties',
author => 'John McNamara',
manager => 'Dr. Heinz Doofenshmirtz',
company => 'of Wolves',
category => 'Example spreadsheets',
keywords => 'Sample, Example, Properties',
comments => 'Created with Perl and Excel::Writer::XLSX',
status => 'Quo',
);
$worksheet->set_column( 'A:A', 70 );
$worksheet->write( 'A1', qq{Select 'Office Button -> Prepare -> Properties' to see the file properties.} );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/properties.pl>
=head2 Example: protection.pl
Example of cell locking and formula hiding in an Excel worksheet via
the Excel::Writer::XLSX module.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/protection.jpg" width="640" height="420" alt="Output from protection.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
########################################################################
#
# Example of cell locking and formula hiding in an Excel worksheet via
# the Excel::Writer::XLSX module.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'protection.xlsx' );
my $worksheet = $workbook->add_worksheet();
# Create some format objects
my $unlocked = $workbook->add_format( locked => 0 );
my $hidden = $workbook->add_format( hidden => 1 );
# Format the columns
$worksheet->set_column( 'A:A', 45 );
$worksheet->set_selection( 'B3' );
# Protect the worksheet
$worksheet->protect();
# Examples of cell locking and hiding.
$worksheet->write( 'A1', 'Cell B1 is locked. It cannot be edited.' );
$worksheet->write_formula( 'B1', '=1+2', undef, 3 ); # Locked by default.
$worksheet->write( 'A2', 'Cell B2 is unlocked. It can be edited.' );
$worksheet->write_formula( 'B2', '=1+2', $unlocked, 3 );
$worksheet->write( 'A3', "Cell B3 is hidden. The formula isn't visible." );
$worksheet->write_formula( 'B3', '=1+2', $hidden, 3 );
$worksheet->write( 'A5', 'Use Menu->Tools->Protection->Unprotect Sheet' );
$worksheet->write( 'A6', 'to remove the worksheet protection.' );
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/protection.pl>
=head2 Example: rich_strings.pl
An Excel::Writer::XLSX example showing how to use "rich strings", i.e.,
strings with multiple formatting.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/rich_strings.jpg" width="640" height="420" alt="Output from rich_strings.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# An Excel::Writer::XLSX example showing how to use "rich strings", i.e.,
# strings with multiple formatting.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'rich_strings.xlsx' );
my $worksheet = $workbook->add_worksheet();
$worksheet->set_column( 'A:A', 30 );
# Set some formats to use.
my $bold = $workbook->add_format( bold => 1 );
my $italic = $workbook->add_format( italic => 1 );
my $red = $workbook->add_format( color => 'red' );
my $blue = $workbook->add_format( color => 'blue' );
my $center = $workbook->add_format( align => 'center' );
my $super = $workbook->add_format( font_script => 1 );
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Set up some formats
my %heading = (
bold => 1,
pattern => 1,
fg_color => '#C3FFC0',
border => 1,
align => 'center',
);
my %total = (
bold => 1,
top => 1,
num_format => '$#,##0.00'
);
my $heading = $workbook->add_format( %heading );
my $total_format = $workbook->add_format( %total );
my $price_format = $workbook->add_format( num_format => '$#,##0.00' );
my $date_format = $workbook->add_format( num_format => 'mmm d yyy' );
# Write the main headings
$worksheet->freeze_panes( 1 ); # Freeze the first row
$worksheet->write( 'A1', 'Item', $heading );
$worksheet->write( 'B1', 'Quantity', $heading );
$worksheet->write( 'C1', 'Price', $heading );
$worksheet->write( 'D1', 'Total', $heading );
$worksheet->write( 'E1', 'Date', $heading );
# Set the column widths
$worksheet->set_column( 'A:A', 25 );
$worksheet->set_column( 'B:B', 10 );
$worksheet->set_column( 'C:E', 16 );
# Extract the sales data from the __DATA__ section at the end of the file.
# In reality this information would probably come from a database
my @sales;
foreach my $line ( <DATA> ) {
chomp $line;
next if $line eq '';
# Simple-minded processing of CSV data. Refer to the Text::CSV_XS
# and Text::xSV modules for a more complete CSV handling.
my @items = split /,/, $line;
push @sales, \@items;
}
# Write out the items from each row
my $row = 1;
foreach my $sale ( @sales ) {
$worksheet->write( $row, 0, @$sale[0] );
$worksheet->write( $row, 1, @$sale[1] );
$worksheet->write( $row, 2, @$sale[2], $price_format );
# Create a formula like '=B2*C2'
my $formula =
'=' . xl_rowcol_to_cell( $row, 1 ) . "*" . xl_rowcol_to_cell( $row, 2 );
$worksheet->write( $row, 3, $formula, $price_format );
# Parse the date
my $date = xl_decode_date_US( @$sale[3] );
$worksheet->write( $row, 4, $date, $date_format );
$row++;
}
# Create a formula to sum the totals, like '=SUM(D2:D6)'
my $total = '=SUM(D2:' . xl_rowcol_to_cell( $row - 1, 3 ) . ")";
$worksheet->write( $row, 3, $total, $total_format );
$workbook->close();
__DATA__
586 card,20,125.50,5/12/01
Flat Screen Monitor,1,1300.00,5/12/01
64 MB dimms,45,49.99,5/13/01
15 GB HD,12,300.00,5/13/01
Speakers (pair),5,15.50,5/14/01
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/sales.pl>
=head2 Example: shape1.pl
A simple example of how to use the Excel::Writer::XLSX module to
add shapes to an Excel xlsx file.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/shape1.jpg" width="640" height="420" alt="Output from shape1.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# A simple example of how to use the Excel::Writer::XLSX module to
# add shapes to an Excel xlsx file.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'shape1.xlsx' );
my $worksheet = $workbook->add_worksheet();
# Add a circle, with centered text.
my $ellipse = $workbook->add_shape(
type => 'ellipse',
text => "Hello\nWorld",
width => 60,
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
=head2 Example: tab_colors.pl
Example of how to set Excel worksheet tab colours.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/tab_colors.jpg" width="640" height="420" alt="Output from tab_colors.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl
#######################################################################
#
# Example of how to set Excel worksheet tab colours.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'tab_colors.xlsx' );
my $worksheet1 = $workbook->add_worksheet();
my $worksheet2 = $workbook->add_worksheet();
my $worksheet3 = $workbook->add_worksheet();
my $worksheet4 = $workbook->add_worksheet();
# Worksheet1 will have the default tab colour.
$worksheet2->set_tab_color( 'red' );
$worksheet3->set_tab_color( 'green' );
$worksheet4->set_tab_color( '#FF6600'); # Orange
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/tab_colors.pl>
=head2 Example: tables.pl
Example of how to add tables to an Excel::Writer::XLSX worksheet.
Tables in Excel are used to group rows and columns of data into a single
structure that can be referenced in a formula or formatted collectively.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=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
Source code for this example:
#!/usr/bin/perl
###############################################################################
#
# Example of how to add tables to an Excel::Writer::XLSX worksheet.
#
# Tables in Excel are used to group rows and columns of data into a single
# structure that can be referenced in a formula or formatted collectively.
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'tables.xlsx' );
my $worksheet1 = $workbook->add_worksheet();
my $worksheet2 = $workbook->add_worksheet();
my $worksheet3 = $workbook->add_worksheet();
my $worksheet4 = $workbook->add_worksheet();
my $worksheet5 = $workbook->add_worksheet();
my $worksheet6 = $workbook->add_worksheet();
my $worksheet7 = $workbook->add_worksheet();
my $worksheet8 = $workbook->add_worksheet();
my $worksheet9 = $workbook->add_worksheet();
my $worksheet10 = $workbook->add_worksheet();
my $worksheet11 = $workbook->add_worksheet();
my $worksheet12 = $workbook->add_worksheet();
my $worksheet13 = $workbook->add_worksheet();
my $currency_format = $workbook->add_format( num_format => '$#,##0' );
# Some sample data for the table.
my $data = [
[ 'Apples', 10000, 5000, 8000, 6000 ],
[ 'Pears', 2000, 3000, 4000, 5000 ],
[ 'Bananas', 6000, 6000, 6500, 6000 ],
[ 'Oranges', 500, 300, 200, 700 ],
];
###############################################################################
#
# Example 1.
#
my $caption = 'Default table with no data.';
# Set the columns widths.
$worksheet1->set_column( 'B:G', 12 );
# Write the caption.
$worksheet1->write( 'B1', $caption );
# Add a table to the worksheet.
$worksheet1->add_table( 'B3:F7' );
###############################################################################
#
# Example 2.
#
$caption = 'Default table with data.';
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# Add a table to the worksheet.
$worksheet6->add_table( 'B3:F7', { banded_rows => 0, banded_columns => 1 } );
# Table data can also be written separately, as an array or individual cells.
$worksheet6->write_col( 'B4', $data );
###############################################################################
#
# Example 7.
#
$caption = 'Table with user defined column headers';
# Set the columns widths.
$worksheet7->set_column( 'B:G', 12 );
# Write the caption.
$worksheet7->write( 'B1', $caption );
# Add a table to the worksheet.
$worksheet7->add_table(
'B3:F7',
{
data => $data,
columns => [
{ header => 'Product' },
{ header => 'Quarter 1' },
{ header => 'Quarter 2' },
{ header => 'Quarter 3' },
{ header => 'Quarter 4' },
]
}
);
###############################################################################
#
# Example 8.
#
$caption = 'Table with user defined column headers';
# Set the columns widths.
$worksheet8->set_column( 'B:G', 12 );
# Write the caption.
$worksheet8->write( 'B1', $caption );
# Add a table to the worksheet.
$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]])'
},
]
}
);
###############################################################################
#
# Example 9.
#
$caption = 'Table with totals row (but no caption or totals).';
# Set the columns widths.
$worksheet9->set_column( 'B:G', 12 );
# Write the caption.
$worksheet9->write( 'B1', $caption );
# Add a table to the worksheet.
$worksheet9->add_table(
'B3:G8',
{
data => $data,
total_row => 1,
columns => [
{ header => 'Product' },
{ header => 'Quarter 1' },
{ header => 'Quarter 2' },
{ header => 'Quarter 3' },
{ header => 'Quarter 4' },
{
header => 'Year',
formula => '=SUM(Table9[@[Quarter 1]:[Quarter 4]])'
},
]
}
);
###############################################################################
#
# Example 10.
#
$caption = 'Table with totals row with user captions and functions.';
# Set the columns widths.
$worksheet10->set_column( 'B:G', 12 );
# Write the caption.
$worksheet10->write( 'B1', $caption );
# Add a table to the worksheet.
$worksheet10->add_table(
'B3:G8',
{
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' },
{
header => 'Year',
formula => '=SUM(Table10[@[Quarter 1]:[Quarter 4]])',
total_function => 'sum'
},
]
}
);
###############################################################################
#
# Example 11.
#
$caption = 'Table with alternative Excel style.';
# Set the columns widths.
$worksheet11->set_column( 'B:G', 12 );
# Write the caption.
$worksheet11->write( 'B1', $caption );
# Add a table to the worksheet.
$worksheet11->add_table(
'B3:G8',
{
data => $data,
style => 'Table Style Light 11',
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' },
{
header => 'Year',
formula => '=SUM(Table11[@[Quarter 1]:[Quarter 4]])',
total_function => 'sum'
},
]
}
);
###############################################################################
#
# Example 12.
#
$caption = 'Table with no Excel style.';
# Set the columns widths.
$worksheet12->set_column( 'B:G', 12 );
# Write the caption.
$worksheet12->write( 'B1', $caption );
# Add a table to the worksheet.
$worksheet12->add_table(
'B3:G8',
{
data => $data,
style => 'None',
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' },
{
header => 'Year',
formula => '=SUM(Table12[@[Quarter 1]:[Quarter 4]])',
total_function => 'sum'
},
]
}
);
###############################################################################
#
# Example 13.
#
$caption = 'Table with column formats.';
# Set the columns widths.
$worksheet13->set_column( 'B:G', 12 );
# Write the caption.
$worksheet13->write( 'B1', $caption );
# Add a table to the worksheet.
$worksheet13->add_table(
'B3:G8',
{
data => $data,
total_row => 1,
columns => [
{ header => 'Product', total_string => 'Totals' },
{
header => 'Quarter 1',
total_function => 'sum',
format => $currency_format,
},
{
header => 'Quarter 2',
total_function => 'sum',
format => $currency_format,
},
{
header => 'Quarter 3',
total_function => 'sum',
format => $currency_format,
},
{
header => 'Quarter 4',
total_function => 'sum',
format => $currency_format,
},
{
header => 'Year',
formula => '=SUM(Table13[@[Quarter 1]:[Quarter 4]])',
total_function => 'sum',
format => $currency_format,
},
]
}
);
$workbook->close();
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.15/examples/tables.pl>
=head2 Example: write_handler1.pl
Example of how to add a user defined data handler to the
Excel::Writer::XLSX write() method.
The following example shows how to add a handler for a 7 digit ID number.
SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
=begin html
<p><center><img src="http://jmcnamara.github.io/excel-writer-xlsx/images/examples/write_handler1.jpg" width="640" height="420" alt="Output from write_handler1.pl" /></center></p>
=end html
Source code for this example:
#!/usr/bin/perl -w
###############################################################################
#
# Example of how to add a user defined data handler to the
# Excel::Writer::XLSX write() method.
#
# The following example shows how to add a handler for a 7 digit ID number.
#
#
# Copyright 2000-2025, John McNamara, jmcnamara@cpan.org
#
# SPDX-License-Identifier: Artistic-1.0-Perl OR GPL-1.0-or-later
#
use strict;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( 'write_handler1.xlsx' );
( run in 0.701 second using v1.01-cache-2.11-cpan-39bf76dae61 )