Excel-Writer-XLSX
view release on metacpan or search on metacpan
1.09 2021-05-14
+ Added support for background images in worksheets. See set_background().
+ Added support for GIF image files (and in Excel 365, animated GIF files).
+ Added support for pixel sizing in set_row() and set_column() via new
functions called set_row_pixels() and set_column_pixels().
+ Added initial support for dynamic arrays in formulas.
1.08 2021-03-31
+ Added ability to add accessibility options "description" and
"decorative" to images via insert_image().
+ Added the workbook read_only_recommended() method to set the Excel
"Read-only Recommended" option that is available when saving a file.
1.07 2020-08-06
+ Added support for Border, Fill, Pattern and Gradient formatting to chart
data labels and chart custom data labels.
1.06 2020-08-03
! Fix for issue where array formulas weren't included in the output file
for certain ranges/conditions.
1.05 2020-07-30
+ Added support for custom data labels in charts.
1.04 2020-05-31
! Fixed the mime-type reported by system "file(1)". The mime-type reported
by "file --mime-type"/magic was incorrect for Excel::Writer::XLSX files
since it expected the "[Content_types]" to be the first file in the zip
container.
0.99 2019-02-10
+ Added font and font_size parameters to write_comment().
+ Allow formulas in date field of data_validation().
+ Added top_left chart legend position.
+ Added legend formatting options.
+ Added set_tab_ratio() method to set the ratio between the worksheet tabs
and the horizontal slider.
+ Added worksheet hide_row_col_headers() method to turn off worksheet row
and column headings.
0.79 2014-10-16
+ Added option to add images to headers and footers.
+ Added option to not scale header/footer with page.
! Fixed issue where non 96dpi images were not scaled properly in Excel.
! Fix for issue where X axis title formula was overwritten by the
Y axis title.
0.78 2014-09-28
+ Added Doughnut chart with set_rotation() and set_hole_size()
methods.
+ Added set_rotation() method to Pie charts.
+ Added set_calc_mode() method to control automatic calculation of
formulas when worksheet is opened.
0.77 2014-05-06
! Fix for incorrect chart offsets in insert_chart() and set_size().
Reported by Kevin Gilpin.
0.76 2013-12-31
0.72 2013-08-28
! Fix for charts and images that cross rows and columns that are
hidden or formatted but which don’t have size changes.
0.71 2013-08-24
! Fixed issue in image handling.
! Added fix to ensure formula calculation on load regardless of
Excel version.
0.70 2013-07-30
! Fix for rendering images that are the same size as cell boundaries.
GitHub issue #70.
! Added fix for inaccurate column width calculation.
0.62 2012-12-12
+ Added option for adding a data table to a Chart X-axis.
See output from chart_data_table.pl example.
0.61 2012-12-11
+ Allow a cell url string to be over written with a number or formula
using a second write() call to the same cell. The url remains intact.
Issue #48.
+ Added set_default_row() method to set worksheet default values for
rows.
+ Added Chart set_size() method to set the chart dimensions.
0.60 2012-12-05
! Added warning about Excel limit to 65,530 urls per worksheet.
! Limit URLs to Excel's limit of 255 chars. Fixes Issue #26.
! Fix for whitespace in urls. Fixes Issue #25.
! Fix for solid fill of type 'none' is chart series.
Closes issue #27 reported on Stack Overflow.
! Modified write_array_formula() to apply format over full range.
Fixes issue #18.
! Fix for issue with chart formula referring to non-existent sheet name.
It is now a fatal error to specify a chart series formula that
refers to an non-existent worksheet name. Fixes issue #17.
0.50 2012-09-09
+ Added option to add secondary axes to charts.
Thanks to Eric Johnson and to Foxtons for sponsoring the work.
+ Added add_table() method to add Excel tables to worksheets.
0.34 2011-11-04
+ Added set_optimization() method to reduce memory usage for very large
data sets.
0.33 2011-10-28
+ Added addition conditional formatting types: cell, date, time_period,
text, average, duplicate, unique, top, bottom, blanks, no_blanks,
errors, no_errors, 2_color_scale, 3_color_scale, data_bar and formula.
0.32 2011-10-20
! Fix for format alignment bug.
Reported by Roderich Schupp.
0.31 2011-10-18
0.29 2011-10-05
+ Added the merge_range_type() method for finer control over the types
written using merge_range().
0.28 2011-10-04
+ Added default write_formula() value for compatibility with Google docs.
+ Updated Example.pm docs with Excel 2007 images.
0.27 2011-10-02
+ Excel::Writer::XLSX is now 100% functionally and API compatible
with Spreadsheet::WriteExcel.
+ Added outlines and grouping functionality.
0.19 2011-05-05
+ Added new chart formatting options for line properties,
markers, trendlines and data labels. See Chart.pm.
+ Added partial support for insert_image().
+ Improved backward compatibility for deprecated methods
store_formula() and repeat_formula().
! Fixed missing formatting for array formulas.
Reported by Cyrille Gourves.
! Fixed issue with chart scaling that caused "unreadable content"
Excel error.
0.18 2011-04-07
+ Added set_properties() method to add document properties.
Added properties.pl and tests.
set_paper()
center_horizontally()
center_vertically()
set_margins()
set_header()
set_footer()
0.05 2011-01-04
+ Added support for array_formulas. See the docs for write_array_formula()
and the example program.
0.04 2011-01-03
+ Added merge_range() for merging cells. With tests and examples.
0.03 2011-01-03
CONTRIBUTING.md
Changes
LICENSE_Artistic_Perl
LICENSE_GPL_1.0
MANIFEST
Makefile.PL
README
bin/extract_vba
examples/README
examples/a_simple.pl
examples/array_formula.pl
examples/autofilter.pl
examples/autofit.pl
examples/background.pl
examples/bug_report.pl
examples/cgi.pl
examples/chart_area.pl
examples/chart_bar.pl
examples/chart_clustered.pl
examples/chart_column.pl
examples/chart_combined.pl
t/chart/sub_write_marker_value.t
t/chart/sub_write_num_cache.t
t/chart/sub_write_number_format.t
t/chart/sub_write_order.t
t/chart/sub_write_orientation.t
t/chart/sub_write_page_margins.t
t/chart/sub_write_page_setup.t
t/chart/sub_write_plot_vis_only.t
t/chart/sub_write_pt.t
t/chart/sub_write_pt_count.t
t/chart/sub_write_series_formula.t
t/chart/sub_write_style.t
t/chart/sub_write_symbol.t
t/chart/sub_write_tick_lbl_pos.t
t/chart/sub_write_v.t
t/chart/sub_write_xml_declaration.t
t/chartsheet/chartsheet01.t
t/chartsheet/chartsheet_subs.t
t/chartsheet/sub_write_sheet_protection.t
t/chartsheet/sub_write_xml_declaration.t
t/drawing/drawing_chart_01.t
t/package/vml/sub_write_shadow.t
t/package/vml/sub_write_shapelayout.t
t/package/vml/sub_write_shapetype.t
t/package/vml/sub_write_size_with_cells.t
t/package/vml/sub_write_stroke.t
t/package/vml/sub_write_textbox.t
t/package/vml/vml_01.t
t/package/vml/vml_02.t
t/package/vml/vml_03.t
t/package/xml_writer/sub_write_xml_declaration.t
t/regression/array_formula01.t
t/regression/array_formula02.t
t/regression/array_formula03.t
t/regression/array_formula04.t
t/regression/autofilter00.t
t/regression/autofilter01.t
t/regression/autofilter02.t
t/regression/autofilter03.t
t/regression/autofilter04.t
t/regression/autofilter05.t
t/regression/autofilter06.t
t/regression/autofilter07.t
t/regression/autofilter08.t
t/regression/autofilter09.t
t/regression/format16.t
t/regression/format17.t
t/regression/format18.t
t/regression/format19.t
t/regression/format20.t
t/regression/format21.t
t/regression/format22.t
t/regression/format23.t
t/regression/format24.t
t/regression/format25.t
t/regression/formula_results01.t
t/regression/gridlines01.t
t/regression/header01.t
t/regression/header02.t
t/regression/header03.t
t/regression/header04.t
t/regression/header_image01.t
t/regression/header_image02.t
t/regression/header_image03.t
t/regression/header_image04.t
t/regression/header_image05.t
t/regression/utf8_06.t
t/regression/utf8_07.t
t/regression/utf8_08.t
t/regression/utf8_09.t
t/regression/utf8_10.t
t/regression/utf8_11.t
t/regression/vml01.t
t/regression/vml02.t
t/regression/vml03.t
t/regression/vml04.t
t/regression/xlsx_files/array_formula01.xlsx
t/regression/xlsx_files/array_formula02.xlsx
t/regression/xlsx_files/array_formula03.xlsx
t/regression/xlsx_files/array_formula04.xlsx
t/regression/xlsx_files/autofilter00.xlsx
t/regression/xlsx_files/autofilter01.xlsx
t/regression/xlsx_files/autofilter02.xlsx
t/regression/xlsx_files/autofilter03.xlsx
t/regression/xlsx_files/autofilter04.xlsx
t/regression/xlsx_files/autofilter05.xlsx
t/regression/xlsx_files/autofilter06.xlsx
t/regression/xlsx_files/autofilter07.xlsx
t/regression/xlsx_files/autofilter08.xlsx
t/regression/xlsx_files/autofilter09.xlsx
t/regression/xlsx_files/format16.xlsx
t/regression/xlsx_files/format17.xlsx
t/regression/xlsx_files/format18.xlsx
t/regression/xlsx_files/format19.xlsx
t/regression/xlsx_files/format20.xlsx
t/regression/xlsx_files/format21.xlsx
t/regression/xlsx_files/format22.xlsx
t/regression/xlsx_files/format23.xlsx
t/regression/xlsx_files/format24.xlsx
t/regression/xlsx_files/format25.xlsx
t/regression/xlsx_files/formula_results01.xlsx
t/regression/xlsx_files/gridlines01.xlsx
t/regression/xlsx_files/header01.xlsx
t/regression/xlsx_files/header02.xlsx
t/regression/xlsx_files/header03.xlsx
t/regression/xlsx_files/header04.xlsx
t/regression/xlsx_files/header_image01.xlsx
t/regression/xlsx_files/header_image02.xlsx
t/regression/xlsx_files/header_image03.xlsx
t/regression/xlsx_files/header_image04.xlsx
t/regression/xlsx_files/header_image05.xlsx
t/utility/xl_date_list.t
t/utility/xl_dec_col.t
t/utility/xl_dec_row.t
t/utility/xl_decode_date_EU.t
t/utility/xl_decode_date_US.t
t/utility/xl_inc_col.t
t/utility/xl_inc_row.t
t/utility/xl_parse_date.t
t/utility/xl_parse_time.t
t/utility/xl_range.t
t/utility/xl_range_formula.t
t/utility/xl_rowcol_to_cell.t
t/utility/xl_string_pixel_width.t
t/workbook/sub_close.t
t/workbook/sub_get_chart_range.t
t/workbook/sub_sort_defined_names.t
t/workbook/sub_write_book_views.t
t/workbook/sub_write_calc_pr.t
t/workbook/sub_write_defined_name.t
t/workbook/sub_write_defined_names.t
t/workbook/sub_write_ext.t
t/worksheet/sparkline_11.t
t/worksheet/sparkline_12.t
t/worksheet/sub_calculate_spans.t
t/worksheet/sub_convert_date_time_01.t
t/worksheet/sub_convert_date_time_02.t
t/worksheet/sub_convert_date_time_03.t
t/worksheet/sub_extract_filter_tokens.t
t/worksheet/sub_parse_filter_expression.t
t/worksheet/sub_pixels_to_row_col.t
t/worksheet/sub_position_object.t
t/worksheet/sub_prepare_formula.t
t/worksheet/sub_repeat_formula.t
t/worksheet/sub_write_auto_filter.t
t/worksheet/sub_write_brk.t
t/worksheet/sub_write_cell.t
t/worksheet/sub_write_cell_value.t
t/worksheet/sub_write_col_breaks.t
t/worksheet/sub_write_col_info.t
t/worksheet/sub_write_conditional_formatting.t
t/worksheet/sub_write_custom_filter.t
t/worksheet/sub_write_custom_filters.t
t/worksheet/sub_write_data_validations_01.t
t/worksheet/sub_write_sheet_views8.t
t/worksheet/sub_write_sheet_views9.t
t/worksheet/sub_write_tab_color.t
t/worksheet/sub_write_worksheet.t
t/worksheet/sub_write_xml_declaration.t
t/worksheet/test_range_return_values.t
t/worksheet/worksheet_01.t
t/worksheet/worksheet_02.t
t/worksheet/worksheet_03.t
t/worksheet/worksheet_04.t
t/worksheet/worksheet_array_formula_01.t
t/worksheet/worksheet_cond_format_01.t
t/worksheet/worksheet_cond_format_02.t
t/worksheet/worksheet_cond_format_03.t
t/worksheet/worksheet_cond_format_04.t
t/worksheet/worksheet_cond_format_05.t
t/worksheet/worksheet_cond_format_06.t
t/worksheet/worksheet_cond_format_07.t
t/worksheet/worksheet_cond_format_08.t
t/worksheet/worksheet_cond_format_09.t
t/worksheet/worksheet_cond_format_10.t
* Grouping/Outlines
* Cell comments
* Panes
* Page set-up and printing options
Excel::Writer::XLSX uses the same interface as Spreadsheet::WriteExcel.
SYNOPSIS
To write a string, a formatted string, a number and a formula to
the first worksheet in an Excel XML spreadsheet called perl.xls:
use Excel::Writer::XLSX;
# Create a new Excel workbook
my $workbook = Excel::Writer::XLSX->new('perl.xlsx');
# Add a worksheet
$worksheet = $workbook->add_worksheet();
$format = $workbook->add_format(); # Add a 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();
INSTALLATION
Use the standard Unix style installation.
examples/README view on Meta::CPAN
and options of the module.
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.
examples/README view on Meta::CPAN
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.
examples/a_simple.pl view on Meta::CPAN
$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__
examples/array_formula.pl view on Meta::CPAN
#!/usr/bin/perl
#######################################################################
#
# Example of how to use the Excel::Writer::XLSX module to write simple
# array formulas.
#
# Copyright 2000-2024, 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__
examples/chart_clustered.pl view on Meta::CPAN
[ '', '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',
examples/data_validate.pl view on Meta::CPAN
$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)',
examples/defined_name.pl view on Meta::CPAN
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__
examples/dynamic_arrays.pl view on Meta::CPAN
$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();
examples/gen_examples_pod.pl view on Meta::CPAN
print qq{</center></p>\n\n};
print "=end html\n\n";
print "Source code for this example:\n\n";
}
__END__
# Image files used in the documentation.
a_simple.jpg
array_formula.jpg
autofilter.jpg
autofit.jpg
bigfile.jpg
chart_area.jpg
chart_bar.jpg
chart_column.jpg
chart_line.jpg
chart_pie.jpg
chart_doughnut.jpg
chart_radar.jpg
examples/gen_examples_pod.pl view on Meta::CPAN
comments2.jpg
copyformat.jpg
conditional_format.jpg
data_validate.jpg
date_time.jpg
defined_name.jpg
demo.jpg
diag_border.jpg
filehandle.jpg
formats.jpg
formula_result.jpg
headers.jpg
hide_row_col.jpg
hide_sheet.jpg
hyperlink1.jpg
images.jpg
indent.jpg
macros.jpg
merge1.jpg
merge2.jpg
merge3.jpg
examples/ignore_errors.pl view on Meta::CPAN
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:');
examples/lambda.pl view on Meta::CPAN
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__
examples/outline.pl view on Meta::CPAN
$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 );
examples/outline.pl view on Meta::CPAN
$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 );
examples/outline.pl view on Meta::CPAN
];
# 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",
examples/outline_collapsed.pl view on Meta::CPAN
#
# 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 );
examples/outline_collapsed.pl view on Meta::CPAN
];
# 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__
examples/protection.pl view on Meta::CPAN
#!/usr/bin/perl
########################################################################
#
# Example of cell locking and formula hiding in an Excel worksheet via
# the Excel::Writer::XLSX module.
#
# Copyright 2000-2024, 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;
examples/protection.pl view on Meta::CPAN
# 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__
examples/sales.pl view on Meta::CPAN
# 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
examples/tables.pl view on Meta::CPAN
#!/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-2024, 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;
examples/tables.pl view on Meta::CPAN
{
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.
#
examples/tables.pl view on Meta::CPAN
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.
#
examples/tables.pl view on Meta::CPAN
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.
examples/tables.pl view on Meta::CPAN
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.
examples/tables.pl view on Meta::CPAN
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.
examples/tables.pl view on Meta::CPAN
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();
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
__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.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
# 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...
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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:
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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()
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
(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();
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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' );
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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' );
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=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...
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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 );
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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:
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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()
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=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()
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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()>.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
$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',
}
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
);
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},
],
}
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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',
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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:
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=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>.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
$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',
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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' },
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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 },
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=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.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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)
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
, 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
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=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...
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
$ 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' );
...
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
$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,
);
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
use 5.008002;
use strict;
use warnings;
use Carp;
use Excel::Writer::XLSX::Format;
use Excel::Writer::XLSX::Package::XMLwriter;
use Excel::Writer::XLSX::Utility qw(xl_cell_to_rowcol
xl_rowcol_to_cell
xl_col_to_name
xl_range_formula
quote_sheetname );
our @ISA = qw(Excel::Writer::XLSX::Package::XMLwriter);
our $VERSION = '1.14';
###############################################################################
#
# factory()
#
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->{_id} = -1;
$self->{_series_index} = 0;
$self->{_style_id} = 2;
$self->{_axis_ids} = [];
$self->{_axis2_ids} = [];
$self->{_cat_has_num_fmt} = 0;
$self->{_requires_category} = 0;
$self->{_legend} = {};
$self->{_cat_axis_position} = 'b';
$self->{_val_axis_position} = 'l';
$self->{_formula_ids} = {};
$self->{_formula_data} = [];
$self->{_horiz_cat_axis} = 0;
$self->{_horiz_val_axis} = 1;
$self->{_protection} = 0;
$self->{_chartarea} = {};
$self->{_plotarea} = {};
$self->{_x_axis} = {};
$self->{_y_axis} = {};
$self->{_y2_axis} = {};
$self->{_x2_axis} = {};
$self->{_chart_name} = '';
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
if ( $self->{_requires_category} && !exists $arg{categories} ) {
croak "Must specify 'categories' in add_series() for this chart type";
}
if ( @{ $self->{_series} } == 255 ) {
carp "The maximum number of series that can be added to an "
. "Excel Chart is 255";
return
}
# Convert aref params into a formula string.
my $values = $self->_aref_to_formula( $arg{values} );
my $categories = $self->_aref_to_formula( $arg{categories} );
# Switch name and name_formula parameters if required.
my ( $name, $name_formula ) =
$self->_process_names( $arg{name}, $arg{name_formula} );
# Get an id for the data equivalent to the range formula.
my $cat_id = $self->_get_data_id( $categories, $arg{categories_data} );
my $val_id = $self->_get_data_id( $values, $arg{values_data} );
my $name_id = $self->_get_data_id( $name_formula, $arg{name_data} );
# Set the line properties for the series.
my $line = $self->_get_line_properties( $arg{line} );
# Allow 'border' as a synonym for 'line' in bar/column style charts.
if ( $arg{border} ) {
$line = $self->_get_line_properties( $arg{border} );
}
# Set the fill properties for the series.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
else {
$self->{_series_overlap_1} = $arg{overlap};
}
}
# Add the user supplied data to the internal structures.
%arg = (
_values => $values,
_categories => $categories,
_name => $name,
_name_formula => $name_formula,
_name_id => $name_id,
_val_data_id => $val_id,
_cat_data_id => $cat_id,
_line => $line,
_fill => $fill,
_pattern => $pattern,
_gradient => $gradient,
_marker => $marker,
_trendline => $trendline,
_smooth => $smooth,
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
#
# set_title()
#
# Set the properties of the chart title.
#
sub set_title {
my $self = shift;
my %arg = @_;
my ( $name, $name_formula ) =
$self->_process_names( $arg{name}, $arg{name_formula} );
my $data_id = $self->_get_data_id( $name_formula, $arg{data} );
$self->{_title_name} = $name;
$self->{_title_formula} = $name_formula;
$self->{_title_data_id} = $data_id;
# Set the font properties if present.
$self->{_title_font} = $self->_convert_font_args( $arg{name_font} );
# Set the title layout.
$self->{_title_layout} = $self->_get_layout_properties( $arg{layout}, 1 );
# Set the title overlay option.
$self->{_title_overlay} = $arg{overlay};
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# _convert_axis_args()
#
# Convert user defined axis values into private hash values.
#
sub _convert_axis_args {
my $self = shift;
my $axis = shift;
my %arg = ( %{ $axis->{_defaults} }, @_ );
my ( $name, $name_formula ) =
$self->_process_names( $arg{name}, $arg{name_formula} );
my $data_id = $self->_get_data_id( $name_formula, $arg{data} );
$axis = {
_defaults => $axis->{_defaults},
_name => $name,
_formula => $name_formula,
_data_id => $data_id,
_reverse => $arg{reverse},
_min => $arg{min},
_max => $arg{max},
_minor_unit => $arg{minor_unit},
_major_unit => $arg{major_unit},
_minor_unit_type => $arg{minor_unit_type},
_major_unit_type => $arg{major_unit_type},
_log_base => $arg{log_base},
_crossing => $arg{crossing},
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
if ( $font->{_rotation} ) {
$font->{_rotation} = 60_000 * int( $font->{_rotation} );
}
return $font;
}
###############################################################################
#
# _aref_to_formula()
#
# Convert and aref of row col values to a range formula.
#
sub _aref_to_formula {
my $self = shift;
my $data = shift;
# If it isn't an array ref it is probably a formula already.
return $data if !ref $data;
my $formula = xl_range_formula( @$data );
return $formula;
}
###############################################################################
#
# _process_names()
#
# Switch name and name_formula parameters if required.
#
sub _process_names {
my $self = shift;
my $name = shift;
my $name_formula = shift;
if ( defined $name ) {
if ( ref $name eq 'ARRAY' ) {
my $cell = xl_rowcol_to_cell( $name->[1], $name->[2], 1, 1 );
$name_formula = quote_sheetname( $name->[0] ) . '!' . $cell;
$name = '';
}
elsif ( $name =~ m/^=[^!]+!\$/ ) {
# Name looks like a formula, use it to set name_formula.
$name_formula = $name;
$name = '';
}
}
return ( $name, $name_formula );
}
###############################################################################
#
# _get_data_type()
#
# Find the overall type of the data associated with a series.
#
# TODO. Need to handle date type.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# The series data was all numeric.
return 'num';
}
###############################################################################
#
# _get_data_id()
#
# Assign an id to a each unique series formula or title/axis formula. Repeated
# formulas such as for categories get the same id. If the series or title
# has user specified data associated with it then that is also stored. This
# data is used to populate cached Excel data when creating a chart.
# If there is no user defined data then it will be populated by the parent
# workbook in Workbook::_add_chart_data()
#
sub _get_data_id {
my $self = shift;
my $formula = shift;
my $data = shift;
my $id;
# Ignore series without a range formula.
return unless $formula;
# Strip the leading '=' from the formula.
$formula =~ s/^=//;
# Store the data id in a hash keyed by the formula and store the data
# in a separate array with the same id.
if ( !exists $self->{_formula_ids}->{$formula} ) {
# Haven't seen this formula before.
$id = @{ $self->{_formula_data} };
push @{ $self->{_formula_data} }, $data;
$self->{_formula_ids}->{$formula} = $id;
}
else {
# Formula already seen. Return existing id.
$id = $self->{_formula_ids}->{$formula};
# Store user defined data if it isn't already there.
if ( !defined $self->{_formula_data}->[$id] ) {
$self->{_formula_data}->[$id] = $data;
}
}
return $id;
}
###############################################################################
#
# _get_color()
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
my @custom = ();
for my $label ( @{ $labels->{custom} } ) {
if (! defined $label) {
push @custom, undef;
next;
}
my %property = %{$label};
# Convert formula.
if ( $property{value} && $property{value} =~ m/^=[^!]+!\$/) {
$property{formula} = $property{value};
}
if ( $property{formula} ) {
$property{formula} =~ s/^=//;
my $data_id = $self->_get_data_id( $property{formula},
$property{data} );
$property{data_id} = $data_id;
}
if ($property{font}) {
$property{font} = $self->_convert_font_args( $property{font} );
}
# Set the line properties for the data labels.
my $line = $self->_get_line_properties( $property{line} );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Write the chart title elements.
if ( $self->{_title_none} ) {
# Turn off the title.
$self->_write_auto_title_deleted();
}
else {
my $title;
if ( $title = $self->{_title_formula} ) {
$self->_write_title_formula(
$title,
$self->{_title_data_id},
undef,
$self->{_title_font},
$self->{_title_layout},
$self->{_title_overlay}
);
}
elsif ( $title = $self->{_title_name} ) {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# _write_series_name()
#
# Write the series name.
#
sub _write_series_name {
my $self = shift;
my $series = shift;
my $name;
if ( $name = $series->{_name_formula} ) {
$self->_write_tx_formula( $name, $series->{_name_id} );
}
elsif ( $name = $series->{_name} ) {
$self->_write_tx_value( $name );
}
}
##############################################################################
#
# _write_cat()
#
# Write the <c:cat> element.
#
sub _write_cat {
my $self = shift;
my $series = shift;
my $formula = $series->{_categories};
my $data_id = $series->{_cat_data_id};
my $data;
if ( defined $data_id ) {
$data = $self->{_formula_data}->[$data_id];
}
# Ignore <c:cat> elements for charts without category values.
return unless $formula;
$self->xml_start_tag( 'c:cat' );
# Check the type of cached data.
my $type = $self->_get_data_type( $data );
if ( $type eq 'str' ) {
$self->{_cat_has_num_fmt} = 0;
# Write the c:numRef element.
$self->_write_str_ref( $formula, $data, $type );
}
elsif ( $type eq 'multi_str') {
$self->{_cat_has_num_fmt} = 0;
# Write the c:multiLvLStrRef element.
$self->_write_multi_lvl_str_ref( $formula, $data );
}
else {
$self->{_cat_has_num_fmt} = 1;
# Write the c:numRef element.
$self->_write_num_ref( $formula, $data, $type );
}
$self->xml_end_tag( 'c:cat' );
}
##############################################################################
#
# _write_val()
#
# Write the <c:val> element.
#
sub _write_val {
my $self = shift;
my $series = shift;
my $formula = $series->{_values};
my $data_id = $series->{_val_data_id};
my $data = $self->{_formula_data}->[$data_id];
$self->xml_start_tag( 'c:val' );
# Unlike Cat axes data should only be numeric.
# Write the c:numRef element.
$self->_write_num_ref( $formula, $data, 'num' );
$self->xml_end_tag( 'c:val' );
}
##############################################################################
#
# _write_num_ref()
#
# Write the <c:numRef> element.
#
sub _write_num_ref {
my $self = shift;
my $formula = shift;
my $data = shift;
my $type = shift;
$self->xml_start_tag( 'c:numRef' );
# Write the c:f element.
$self->_write_series_formula( $formula );
if ( $type eq 'num' ) {
# Write the c:numCache element.
$self->_write_num_cache( $data );
}
elsif ( $type eq 'str' ) {
# Write the c:strCache element.
$self->_write_str_cache( $data );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
##############################################################################
#
# _write_str_ref()
#
# Write the <c:strRef> element.
#
sub _write_str_ref {
my $self = shift;
my $formula = shift;
my $data = shift;
my $type = shift;
$self->xml_start_tag( 'c:strRef' );
# Write the c:f element.
$self->_write_series_formula( $formula );
if ( $type eq 'num' ) {
# Write the c:numCache element.
$self->_write_num_cache( $data );
}
elsif ( $type eq 'str' ) {
# Write the c:strCache element.
$self->_write_str_cache( $data );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
##############################################################################
#
# _write_multi_lvl_str_ref()
#
# Write the <c:multiLvLStrRef> element.
#
sub _write_multi_lvl_str_ref {
my $self = shift;
my $formula = shift;
my $data = shift;
my $count = @$data;
return if !$count;
$self->xml_start_tag( 'c:multiLvlStrRef' );
# Write the c:f element.
$self->_write_series_formula( $formula );
$self->xml_start_tag( 'c:multiLvlStrCache' );
# Write the c:ptCount element.
$count = @{ $data->[-1] };
$self->_write_pt_count( $count );
# Write the data arrays in reverse order.
for my $aref ( reverse @$data ) {
$self->xml_start_tag( 'c:lvl' );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
}
$self->xml_end_tag( 'c:multiLvlStrCache' );
$self->xml_end_tag( 'c:multiLvlStrRef' );
}
##############################################################################
#
# _write_series_formula()
#
# Write the <c:f> element.
#
sub _write_series_formula {
my $self = shift;
my $formula = shift;
# Strip the leading '=' from the formula.
$formula =~ s/^=//;
$self->xml_data_element( 'c:f', $formula );
}
##############################################################################
#
# _write_axis_ids()
#
# Write the <c:axId> elements for the primary or secondary axes.
#
sub _write_axis_ids {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->_write_axis_pos( $position, $y_axis->{_reverse} );
# Write the c:majorGridlines element.
$self->_write_major_gridlines( $x_axis->{_major_gridlines} );
# Write the c:minorGridlines element.
$self->_write_minor_gridlines( $x_axis->{_minor_gridlines} );
# Write the axis title elements.
my $title;
if ( $title = $x_axis->{_formula} ) {
$self->_write_title_formula( $title, $x_axis->{_data_id}, $is_y_axis,
$x_axis->{_name_font}, $x_axis->{_layout} );
}
elsif ( $title = $x_axis->{_name} ) {
$self->_write_title_rich( $title, $is_y_axis, $x_axis->{_name_font},
$x_axis->{_layout} );
}
# Write the c:numFmt element.
$self->_write_cat_number_format( $x_axis );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->_write_axis_pos( $position, $x_axis->{_reverse} );
# Write the c:majorGridlines element.
$self->_write_major_gridlines( $y_axis->{_major_gridlines} );
# Write the c:minorGridlines element.
$self->_write_minor_gridlines( $y_axis->{_minor_gridlines} );
# Write the axis title elements.
my $title;
if ( $title = $y_axis->{_formula} ) {
$self->_write_title_formula( $title, $y_axis->{_data_id}, $is_y_axis,
$y_axis->{_name_font}, $y_axis->{_layout} );
}
elsif ( $title = $y_axis->{_name} ) {
$self->_write_title_rich( $title, $is_y_axis, $y_axis->{_name_font},
$y_axis->{_layout} );
}
# Write the c:numberFormat element.
$self->_write_number_format( $y_axis );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->_write_axis_pos( $position, $y_axis->{_reverse} );
# Write the c:majorGridlines element.
$self->_write_major_gridlines( $x_axis->{_major_gridlines} );
# Write the c:minorGridlines element.
$self->_write_minor_gridlines( $x_axis->{_minor_gridlines} );
# Write the axis title elements.
my $title;
if ( $title = $x_axis->{_formula} ) {
$self->_write_title_formula( $title, $x_axis->{_data_id}, $is_y_axis,
$x_axis->{_name_font}, $x_axis->{_layout} );
}
elsif ( $title = $x_axis->{_name} ) {
$self->_write_title_rich( $title, $is_y_axis, $x_axis->{_name_font},
$x_axis->{_layout} );
}
# Write the c:numberFormat element.
$self->_write_number_format( $x_axis );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->_write_axis_pos( $position, $y_axis->{_reverse} );
# Write the c:majorGridlines element.
$self->_write_major_gridlines( $x_axis->{_major_gridlines} );
# Write the c:minorGridlines element.
$self->_write_minor_gridlines( $x_axis->{_minor_gridlines} );
# Write the axis title elements.
my $title;
if ( $title = $x_axis->{_formula} ) {
$self->_write_title_formula( $title, $x_axis->{_data_id}, undef,
$x_axis->{_name_font}, $x_axis->{_layout} );
}
elsif ( $title = $x_axis->{_name} ) {
$self->_write_title_rich( $title, undef, $x_axis->{_name_font},
$x_axis->{_layout} );
}
# Write the c:numFmt element.
$self->_write_number_format( $x_axis );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Write the c:overlay element.
$self->_write_overlay() if $overlay;
$self->xml_end_tag( 'c:title' );
}
##############################################################################
#
# _write_title_formula()
#
# Write the <c:title> element for a rich string.
#
sub _write_title_formula {
my $self = shift;
my $title = shift;
my $data_id = shift;
my $is_y_axis = shift;
my $font = shift;
my $layout = shift;
my $overlay = shift;
$self->xml_start_tag( 'c:title' );
# Write the c:tx element.
$self->_write_tx_formula( $title, $data_id );
# Write the c:layout element.
$self->_write_layout( $layout, 'text' );
# Write the c:overlay element.
$self->_write_overlay() if $overlay;
# Write the c:txPr element.
$self->_write_tx_pr( $font, $is_y_axis );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Write the c:v element.
$self->_write_v( $title );
$self->xml_end_tag( 'c:tx' );
}
##############################################################################
#
# _write_tx_formula()
#
# Write the <c:tx> element.
#
sub _write_tx_formula {
my $self = shift;
my $title = shift;
my $data_id = shift;
my $data;
if ( defined $data_id ) {
$data = $self->{_formula_data}->[$data_id];
}
$self->xml_start_tag( 'c:tx' );
# Write the c:strRef element.
$self->_write_str_ref( $title, $data, 'str' );
$self->xml_end_tag( 'c:tx' );
}
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Write the a:r element.
$self->_write_a_r( $title, $font );
$self->xml_end_tag( 'a:p' );
}
##############################################################################
#
# _write_a_p_formula()
#
# Write the <a:p> element for formula titles.
#
sub _write_a_p_formula {
my $self = shift;
my $font = shift;
$self->xml_start_tag( 'a:p' );
# Write the a:pPr element.
$self->_write_a_p_pr_formula( $font );
# Write the a:endParaRPr element.
$self->_write_a_end_para_rpr();
$self->xml_end_tag( 'a:p' );
}
##############################################################################
#
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
# Write the a:defRPr element.
$self->_write_a_def_rpr( $font );
$self->xml_end_tag( 'a:pPr' );
}
##############################################################################
#
# _write_a_p_pr_formula()
#
# Write the <a:pPr> element for formula titles.
#
sub _write_a_p_pr_formula {
my $self = shift;
my $font = shift;
$self->xml_start_tag( 'a:pPr' );
# Write the a:defRPr element.
$self->_write_a_def_rpr( $font );
$self->xml_end_tag( 'a:pPr' );
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->xml_start_tag( 'c:txPr' );
# Write the a:bodyPr element.
$self->_write_a_body_pr( $rotation, $is_y_axis );
# Write the a:lstStyle element.
$self->_write_a_lst_style();
# Write the a:p element.
$self->_write_a_p_formula( $font );
$self->xml_end_tag( 'c:txPr' );
}
##############################################################################
#
# _write_marker()
#
# Write the <c:marker> element.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
next if !defined $label;
$self->xml_start_tag( 'c:dLbl' );
# Write the c:idx element.
$self->_write_idx( $index - 1 );
if ( defined $label->{delete} && $label->{delete} ) {
$self->_write_delete( 1 );
}
elsif ( defined $label->{formula} ) {
$self->_write_custom_label_formula( $label );
if ( $parent->{position} ) {
$self->_write_d_lbl_pos( $parent->{position} );
}
$self->_write_show_val() if $parent->{value};
$self->_write_show_cat_name() if $parent->{category};
$self->_write_show_ser_name() if $parent->{series_name};
}
elsif ( defined $label->{value} ) {
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
$self->_write_rich( $value, $font, $is_y_axis, !$has_formatting );
$self->xml_end_tag( 'c:tx' );
# Write the c:spPr element.
$self->_write_sp_pr( $label );
}
##############################################################################
#
# _write_custom_label_formula()
#
# Write parts of the <c:dLbl> element for formulas.
#
sub _write_custom_label_formula {
my $self = shift;
my $label = shift;
my $formula = $label->{formula};
my $data_id = $label->{data_id};
my $font = $label->{font};
my $has_formatting = _has_fill_formatting($label);
my $data;
if ( defined $data_id ) {
$data = $self->{_formula_data}->[$data_id];
}
# Write the c:layout element.
$self->_write_layout();
$self->xml_start_tag( 'c:tx' );
# Write the c:strRef element.
$self->_write_str_ref( $formula, $data, 'str' );
$self->xml_end_tag( 'c:tx' );
# Write the data label formating, if any.
$self->_write_custom_label_format_only($label);
}
##############################################################################
#
# _write_custom_label_format_only()
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
values => '=Sheet1!$B$2:$B$10', # Required.
line => { color => 'blue' },
);
The properties that can be set are:
=over
=item * C<values>
This is the most important property of a series and must be set for every chart object. It links the chart with the worksheet data that it displays. A formula or array ref can be used for the data range, see below.
=item * C<categories>
This sets the chart category labels. The category is more or less the same as the X axis. In most chart types the C<categories> property is optional and the chart will just assume a sequential series from C<1 .. n>.
=item * C<name>
Set the name for the series. The name is displayed in the chart legend and in the formula bar. The name property is optional and if it isn't supplied it will default to C<Series 1 .. n>.
=item * C<line>
Set the properties of the series line type such as colour and width. See the L</CHART FORMATTING> section below.
=item * C<border>
Set the border properties of the series such as colour and style. See the L</CHART FORMATTING> section below.
=item * C<fill>
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
=item * C<gap>
Set the gap between series in a Bar/Column chart. The range is 0 to 500. Default is 150.
gap => 200,
Note, it is only necessary to apply this property to one series of the chart.
=back
The C<categories> and C<values> can take either a range formula such as C<=Sheet1!$A$2:$A$7> or, more usefully when generating the range programmatically, an array ref with zero indexed row/column values:
[ $sheetname, $row_start, $row_end, $col_start, $col_end ]
The following are equivalent:
$chart->add_series( categories => '=Sheet1!$A$2:$A$7' ); # Same as ...
$chart->add_series( categories => [ 'Sheet1', 1, 6, 0, 0 ] ); # Zero-indexed.
You can add more than one series to a chart. In fact, some chart types such as C<stock> require it. The series numbering and order in the Excel chart will be the same as the order in which they are added in Excel::Writer::XLSX.
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
=over
=item * C<name>
Set the name (title or caption) for the axis. The name is displayed below the X axis. The C<name> property is optional. The default is to have no axis name. (Applicable to category and value axes).
$chart->set_x_axis( name => 'Quarterly results' );
The name can also be a formula such as C<=Sheet1!$A$1>.
=item * C<name_font>
Set the font properties for the axis title. (Applicable to category and value axes).
$chart->set_x_axis( name_font => { name => 'Arial', size => 10 } );
=item * C<name_layout>
Set the C<(x, y)> position of the axis caption in chart relative units. (Applicable to category and value axes).
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
The C<set_title()> method is used to set properties of the chart title.
$chart->set_title( name => 'Year End Results' );
The properties that can be set are:
=over
=item * C<name>
Set the name (title) for the chart. The name is displayed above the chart. The name can also be a formula such as C<=Sheet1!$A$1>. The name property is optional. The default is to have no chart title.
=item * C<name_font>
Set the font properties for the chart title. See the L</CHART FONTS> section below.
=item * C<overlay>
Allow the title to be overlaid on the chart. Generally used with the layout property below.
=item * C<layout>
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
All error bar types, except for C<standard_error> and C<custom> must also have a value associated with it for the error bounds:
$chart->add_series(
values => '=Sheet1!$B$1:$B$5',
y_error_bars => {
type => 'percentage',
value => 5,
},
);
The C<custom> error bar type must specify C<plus_values> and C<minus_values> which should either by a C<Sheet1!$A$1:$A$5> type range formula or an arrayref of
values:
$chart->add_series(
categories => '=Sheet1!$A$1:$A$5',
values => '=Sheet1!$B$1:$B$5',
y_error_bars => {
type => 'custom',
plus_values => '=Sheet1!$C$1:$C$5',
minus_values => '=Sheet1!$D$1:$D$5',
},
lib/Excel/Writer/XLSX/Chart.pm view on Meta::CPAN
The property elements of the C<custom> lists should be dicts with the following allowable keys/sub-properties:
value
font
border
fill
pattern
gradient
delete
The C<value> property should be a string, number or formula string that refers to a cell from which the value will be taken:
$custom_labels = [
{ value => '=Sheet1!$C$2' },
{ value => '=Sheet1!$C$3' },
{ value => '=Sheet1!$C$4' },
{ value => '=Sheet1!$C$5' },
{ value => '=Sheet1!$C$6' },
{ value => '=Sheet1!$C$7' },
];
lib/Excel/Writer/XLSX/Chart/Scatter.pm view on Meta::CPAN
##############################################################################
#
# _write_x_val()
#
# Write the <c:xVal> element.
#
sub _write_x_val {
my $self = shift;
my $series = shift;
my $formula = $series->{_categories};
my $data_id = $series->{_cat_data_id};
my $data = $self->{_formula_data}->[$data_id];
$self->xml_start_tag( 'c:xVal' );
# Check the type of cached data.
my $type = $self->_get_data_type( $data );
# TODO. Can a scatter plot have non-numeric data.
if ( $type eq 'str' ) {
# Write the c:numRef element.
$self->_write_str_ref( $formula, $data, $type );
}
else {
# Write the c:numRef element.
$self->_write_num_ref( $formula, $data, $type );
}
$self->xml_end_tag( 'c:xVal' );
}
##############################################################################
#
# _write_y_val()
#
# Write the <c:yVal> element.
#
sub _write_y_val {
my $self = shift;
my $series = shift;
my $formula = $series->{_values};
my $data_id = $series->{_val_data_id};
my $data = $self->{_formula_data}->[$data_id];
$self->xml_start_tag( 'c:yVal' );
# Unlike Cat axes data should only be numeric.
# Write the c:numRef element.
$self->_write_num_ref( $formula, $data, 'num' );
$self->xml_end_tag( 'c:yVal' );
}
##############################################################################
#
# _write_scatter_style()
#
# Write the <c:scatterStyle> element.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
=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.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
=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.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$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__
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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.14/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-2024, 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.14/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:
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
[ '', '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',
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$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)',
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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.14/examples/defined_name.pl>
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$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();
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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:');
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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.14/examples/lambda.pl>
=head2 Example: macros.pl
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$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 );
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
$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 );
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
];
# 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",
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
#
# 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 );
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
];
# 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.14/examples/outline_collapsed.pl>
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
__END__
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.14/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-2024, 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;
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# 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__
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
# 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
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
Download this example: L<http://cpansearch.perl.org/src/JMCNAMARA/Excel-Writer-XLSX-1.14/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-2024, 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;
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
{
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.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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.
#
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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.
lib/Excel/Writer/XLSX/Examples.pm view on Meta::CPAN
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();
lib/Excel/Writer/XLSX/Package/Table.pm view on Meta::CPAN
}
elsif ( $col_data->{_total_function} ) {
push @attributes, ( totalsRowFunction => $col_data->{_total_function} );
}
if ( defined $col_data->{_format} ) {
push @attributes, ( dataDxfId => $col_data->{_format} );
}
if ( $col_data->{_formula} || $col_data->{_custom_total} ) {
$self->xml_start_tag( 'tableColumn', @attributes );
if ($col_data->{_formula}) {
# Write the calculatedColumnFormula element.
$self->_write_calculated_column_formula( $col_data->{_formula} );
}
if ($col_data->{_custom_total}) {
# Write the totalsRowFormula element.
$self->_write_totals_row_formula( $col_data->{_custom_total} );
}
$self->xml_end_tag( 'tableColumn' );
}
else {
$self->xml_empty_tag( 'tableColumn', @attributes );
}
}
lib/Excel/Writer/XLSX/Package/Table.pm view on Meta::CPAN
push @attributes, ( 'showLastColumn' => $show_last_column );
push @attributes, ( 'showRowStripes' => $show_row_stripes );
push @attributes, ( 'showColumnStripes' => $show_column_stripes );
$self->xml_empty_tag( 'tableStyleInfo', @attributes );
}
##############################################################################
#
# _write_calculated_column_formula()
#
# Write the <calculatedColumnFormula> element.
#
sub _write_calculated_column_formula {
my $self = shift;
my $formula = shift;
$self->xml_data_element( 'calculatedColumnFormula', $formula );
}
##############################################################################
#
# _write_totals_row_formula()
#
# Write the <totalsRowFormula> element.
#
sub _write_totals_row_formula {
my $self = shift;
my $formula = shift;
$self->xml_data_element( 'totalsRowFormula', $formula );
}
1;
__END__
=pod
lib/Excel/Writer/XLSX/Package/VML.pm view on Meta::CPAN
'path' => $path,
'filled' => $filled,
'stroked' => $stroked,
);
$self->xml_start_tag( 'v:shapetype', @attributes );
# Write the v:stroke element.
$self->_write_stroke();
# Write the v:formulas element.
$self->_write_formulas();
# Write the v:path element.
$self->_write_image_path();
# Write the o:lock element.
$self->_write_aspect_ratio_lock();
$self->xml_end_tag( 'v:shapetype' );
}
lib/Excel/Writer/XLSX/Package/VML.pm view on Meta::CPAN
'o:title' => $o_title,
);
$self->xml_empty_tag( 'v:imagedata', @attributes );
}
##############################################################################
#
# _write_formulas()
#
# Write the <v:formulas> element.
#
sub _write_formulas {
my $self = shift;
$self->xml_start_tag( 'v:formulas' );
# Write the v:f elements.
$self->_write_f('if lineDrawn pixelLineWidth 0');
$self->_write_f('sum @0 1 0');
$self->_write_f('sum 0 0 @1');
$self->_write_f('prod @2 1 2');
$self->_write_f('prod @3 21600 pixelWidth');
$self->_write_f('prod @3 21600 pixelHeight');
$self->_write_f('sum @0 0 1');
$self->_write_f('prod @6 1 2');
$self->_write_f('prod @7 21600 pixelWidth');
$self->_write_f('sum @8 21600 0');
$self->_write_f('prod @7 21600 pixelHeight');
$self->_write_f('sum @10 21600 0');
$self->xml_end_tag( 'v:formulas' );
}
##############################################################################
#
# _write_f()
#
# Write the <v:f> element.
#
sub _write_f {
lib/Excel/Writer/XLSX/Package/XMLwriter.pm view on Meta::CPAN
$attr .= qq( $key="$value");
}
local $\ = undef;
print { $self->{_fh} } "<c$attr><v>$number</v></c>";
}
###############################################################################
#
# xml_formula_element()
#
# Optimised tag writer for <c> cell formula elements in the inner loop.
#
sub xml_formula_element {
my $self = shift;
my $formula = shift;
my $result = shift;
my $attr = '';
while ( @_ ) {
my $key = shift;
my $value = shift;
$attr .= qq( $key="$value");
}
$formula = _escape_data( $formula );
local $\ = undef;
print { $self->{_fh} } "<c$attr><f>$formula</f><v>$result</v></c>";
}
###############################################################################
#
# xml_inline_string()
#
# Optimised tag writer for inlineStr cell elements in the inner loop.
#
sub xml_inline_string {
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
our $VERSION = '1.14';
# Row and column functions
my @rowcol = qw(
xl_rowcol_to_cell
xl_cell_to_rowcol
xl_col_to_name
xl_range
xl_range_formula
xl_inc_row
xl_dec_row
xl_inc_col
xl_dec_col
xl_string_pixel_width
);
# Date and Time functions
my @dates = qw(
xl_date_list
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
return $range1;
}
else {
return $range1 . ':' . $range2;
}
}
###############################################################################
#
# xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
#
sub xl_range_formula {
my ( $sheetname, $row_1, $row_2, $col_1, $col_2 ) = @_;
$sheetname = quote_sheetname( $sheetname );
my $range = xl_range( $row_1, $row_2, $col_1, $col_2, 1, 1, 1, 1 );
return '=' . $sheetname . '!' . $range
}
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
=head1 DESCRIPTION
This module provides a set of functions to help with some common tasks encountered when using the L<Excel::Writer::XLSX> module. The two main categories of function are:
Row and column functions: these are used to deal with Excel's A1 representation of cells. The functions in this category are:
xl_rowcol_to_cell
xl_cell_to_rowcol
xl_col_to_name
xl_range
xl_range_formula
xl_inc_row
xl_dec_row
xl_inc_col
xl_dec_col
Date and Time functions: these are used to convert dates and times to the numeric format used by Excel. The functions in this category are:
xl_date_list
xl_date_1904
xl_parse_time
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
(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', '=H7+1' );
The functions in the following sections can be used for dealing with A1 notation, for example:
( $row, $col ) = xl_cell_to_rowcol('C2'); # (1, 2)
$str = xl_rowcol_to_cell( 1, 2 ); # C2
lib/Excel/Writer/XLSX/Utility.pm view on Meta::CPAN
Parameters: $sheetname String
$row_1: Integer
$row_2: Integer
$col_1: Integer
$col_2: Integer
$row_abs_1: Boolean (1/0) [optional, default is 0]
$row_abs_2: Boolean (1/0) [optional, default is 0]
$col_abs_1: Boolean (1/0) [optional, default is 0]
$col_abs_2: Boolean (1/0) [optional, default is 0]
Returns: A worksheet range formula as a string.
This function converts zero based row and column cell references to an A1 style range string:
my $str = xl_range( 0, 9, 0, 0 ); # A1:A10
my $str = xl_range( 1, 8, 2, 2 ); # C2:C9
my $str = xl_range( 0, 3, 0, 4 ); # A1:E4
my $str = xl_range( 0, 3, 0, 4, 1 ); # A$1:E4
my $str = xl_range( 0, 3, 0, 4, 1, 1 ); # A$1:E$
my $str = xl_range( 0, 0, 0, 0 ); # A1
=head2 xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
Parameters: $sheetname String
$row_1: Integer
$row_2: Integer
$col_1: Integer
$col_2: Integer
Returns: A worksheet range formula as a string.
This function converts zero based row and column cell references to an A1 style formula string:
my $str = xl_range_formula( 'Sheet1', 0, 9, 0, 0 ); # =Sheet1!$A$1:$A$10
my $str = xl_range_formula( 'Sheet2', 6, 65, 1, 1 ); # =Sheet2!$B$7:$B$66
my $str = xl_range_formula( 'New data', 1, 8, 2, 2 );# ='New data'!$C$2:$C$9
This is useful for setting ranges in Chart objects:
$chart->add_series(
categories => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
values => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
);
# Which is the same as:
$chart->add_series(
categories => '=Sheet1!$A$2:$A$10',
values => '=Sheet1!$B$2:$B$10',
);
=head2 xl_inc_row($string)
lib/Excel/Writer/XLSX/Workbook.pm view on Meta::CPAN
#
# define_name()
#
# Create a defined name in Excel. We handle global/workbook level names and
# local/worksheet names.
#
sub define_name {
my $self = shift;
my $name = shift;
my $formula = shift;
my $sheet_index = undef;
my $sheetname = '';
my $full_name = $name;
# Remove the = sign from the formula if it exists.
$formula =~ s/^=//;
# Local defined names are formatted like "Sheet1!name".
if ( $name =~ /^(.*)!(.*)$/ ) {
$sheetname = $1;
$name = $2;
$sheet_index = $self->_get_sheet_index( $sheetname );
}
else {
$sheet_index = -1; # Use -1 to indicate global names.
}
lib/Excel/Writer/XLSX/Workbook.pm view on Meta::CPAN
carp "Invalid name '$name' looks like a cell name in defined_name()";
return -1;
}
# Warn if the name looks like a R1C1.
if ( $name =~ m/^[rcRC]$/ || $name =~ m/^[rcRC]\d+[rcRC]\d+$/ ) {
carp "Invalid name '$name' like a RC cell ref in defined_name()";
return -1;
}
push @{ $self->{_defined_names} }, [ $name, $sheet_index, $formula ];
}
###############################################################################
#
# set_size()
#
# Set the workbook size.
#
sub set_size {
lib/Excel/Writer/XLSX/Workbook.pm view on Meta::CPAN
if ($chart->{_combined}) {
push @charts, $chart->{_combined};
}
}
CHART:
for my $chart ( @charts ) {
RANGE:
while ( my ( $range, $id ) = each %{ $chart->{_formula_ids} } ) {
# Skip if the series has user defined data.
if ( defined $chart->{_formula_data}->[$id] ) {
if ( !exists $seen_ranges{$range}
|| !defined $seen_ranges{$range} )
{
my $data = $chart->{_formula_data}->[$id];
$seen_ranges{$range} = $data;
}
next RANGE;
}
# Check to see if the data is already cached locally.
if ( exists $seen_ranges{$range} ) {
$chart->{_formula_data}->[$id] = $seen_ranges{$range};
next RANGE;
}
# Convert the range formula to a sheet name and cell range.
my ( $sheetname, @cells ) = $self->_get_chart_range( $range );
# Skip if we couldn't parse the formula.
next RANGE if !defined $sheetname;
# Handle non-contiguous ranges: (Sheet1!$A$1:$A$2,Sheet1!$A$4:$A$5).
# We don't try to parse the ranges. We just return an empty list.
if ( $sheetname =~ m/^\([^,]+,/ ) {
$chart->{_formula_data}->[$id] = [];
$seen_ranges{$range} = [];
next RANGE;
}
# Die if the name is unknown since it indicates a user error in
# a chart series formula.
if ( !exists $worksheets{$sheetname} ) {
die "Unknown worksheet reference '$sheetname' in range "
. "'$range' passed to add_series().\n";
}
# Find the worksheet object based on the sheet name.
my $worksheet = $worksheets{$sheetname};
# Get the data from the worksheet table.
my @data = $worksheet->_get_range_data( @cells );
lib/Excel/Writer/XLSX/Workbook.pm view on Meta::CPAN
$token = $self->{_str_array}->[ $token->{sst_id} ];
# Ignore rich strings for now. Deparse later if necessary.
if ( $token =~ m{^<r>} && $token =~ m{</r>$} ) {
$token = '';
}
}
}
# Add the data to the chart.
$chart->{_formula_data}->[$id] = \@data;
# Store range data locally to avoid lookup if seen again.
$seen_ranges{$range} = \@data;
}
}
}
###############################################################################
#
# _get_chart_range()
#
# Convert a range formula such as Sheet1!$B$1:$B$5 into a sheet name and cell
# range such as ( 'Sheet1', 0, 1, 4, 1 ).
#
sub _get_chart_range {
my $self = shift;
my $range = shift;
my $cell_1;
my $cell_2;
my $sheetname;
my $cells;
# Split the range formula into sheetname and cells at the last '!'.
my $pos = rindex $range, '!';
if ( $pos > 0 ) {
$sheetname = substr $range, 0, $pos;
$cells = substr $range, $pos + 1;
}
else {
return undef;
}
# Split the cell range into 2 cells or else use single cell for both.
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# Use the Excel standard widths for TRUE and FALSE.
if ( $token ) {
$length = 31;
}
else {
$length = 36;
}
}
elsif ( $type eq 'f' ) {
# Handle formulas.
#
# We only try to autofit a formula if it has a
# non-zero value.
my $value = $cell->[3];
if ( $value ) {
$length = xl_string_pixel_width( $value );
}
}
elsif ( $type eq 'a' || $type eq 'd' ) {
# Handle array and dynamic formulas.
my $value = $cell->[4];
if ( $value ) {
$length = xl_string_pixel_width( $value );
}
}
# If the cell is in an autofilter header we add an
# additional 16 pixels for the dropdown arrow.
if ( $length > 0
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# Match mailto:
elsif ( $token =~ m/^mailto:/ ) {
return $self->write_url( @_ );
}
# Match internal or external sheet link
elsif ( $token =~ m[^(?:in|ex)ternal:] ) {
return $self->write_url( @_ );
}
# Match formula
elsif ( $token =~ /^=/ ) {
return $self->write_formula( @_ );
}
# Match array formula
elsif ( $token =~ /^{=.*}$/ ) {
return $self->write_formula( @_ );
}
# Match blank
elsif ( $token eq '' ) {
splice @_, 2, 1; # remove the empty string from the parameter list
return $self->write_blank( @_ );
}
# Default: match string
else {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
$self->_write_single_row( $row );
}
$self->{_table}->{$row}->{$col} = [ $type, undef, $xf ];
return 0;
}
###############################################################################
#
# _prepare_formula($formula)
#
# Utility method to strip equal sign and array braces from a formula and also
# expand out future and dynamic array formulas.
#
sub _prepare_formula {
my $self = shift;
my $formula = shift;
my $expand_future_functions = shift;
# Ignore empty/null formulas.
return $formula if !$formula;
# Remove array formula braces and the leading =.
$formula =~ s/^{(.*)}$/$1/;
$formula =~ s/^=//;
# # Don't expand formulas that the user has already expanded.
return $formula if $formula =~ m/_xlfn\./;
# Expand dynamic array formulas.
$formula =~ s/\b(ANCHORARRAY\()/_xlfn.$1/g;
$formula =~ s/\b(BYCOL\()/_xlfn.$1/g;
$formula =~ s/\b(BYROW\()/_xlfn.$1/g;
$formula =~ s/\b(CHOOSECOLS\()/_xlfn.$1/g;
$formula =~ s/\b(CHOOSEROWS\()/_xlfn.$1/g;
$formula =~ s/\b(DROP\()/_xlfn.$1/g;
$formula =~ s/\b(EXPAND\()/_xlfn.$1/g;
$formula =~ s/\b(FILTER\()/_xlfn._xlws.$1/g;
$formula =~ s/\b(HSTACK\()/_xlfn.$1/g;
$formula =~ s/\b(LAMBDA\()/_xlfn.$1/g;
$formula =~ s/\b(MAKEARRAY\()/_xlfn.$1/g;
$formula =~ s/\b(MAP\()/_xlfn.$1/g;
$formula =~ s/\b(RANDARRAY\()/_xlfn.$1/g;
$formula =~ s/\b(REDUCE\()/_xlfn.$1/g;
$formula =~ s/\b(SCAN\()/_xlfn.$1/g;
$formula =~ s/\b(SEQUENCE\()/_xlfn.$1/g;
$formula =~ s/\b(SINGLE\()/_xlfn.$1/g;
$formula =~ s/\b(SORT\()/_xlfn._xlws.$1/g;
$formula =~ s/\b(SORTBY\()/_xlfn.$1/g;
$formula =~ s/\b(SWITCH\()/_xlfn.$1/g;
$formula =~ s/\b(TAKE\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTSPLIT\()/_xlfn.$1/g;
$formula =~ s/\b(TOCOL\()/_xlfn.$1/g;
$formula =~ s/\b(TOROW\()/_xlfn.$1/g;
$formula =~ s/\b(UNIQUE\()/_xlfn.$1/g;
$formula =~ s/\b(VSTACK\()/_xlfn.$1/g;
$formula =~ s/\b(WRAPCOLS\()/_xlfn.$1/g;
$formula =~ s/\b(WRAPROWS\()/_xlfn.$1/g;
$formula =~ s/\b(XLOOKUP\()/_xlfn.$1/g;
if ( !$self->{_use_future_functions} && !$expand_future_functions ) {
return $formula;
}
# Future functions.
$formula =~ s/\b(ACOTH\()/_xlfn.$1/g;
$formula =~ s/\b(ACOT\()/_xlfn.$1/g;
$formula =~ s/\b(AGGREGATE\()/_xlfn.$1/g;
$formula =~ s/\b(ARABIC\()/_xlfn.$1/g;
$formula =~ s/\b(ARRAYTOTEXT\()/_xlfn.$1/g;
$formula =~ s/\b(BASE\()/_xlfn.$1/g;
$formula =~ s/\b(BETA.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(BETA.INV\()/_xlfn.$1/g;
$formula =~ s/\b(BINOM.DIST.RANGE\()/_xlfn.$1/g;
$formula =~ s/\b(BINOM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(BINOM.INV\()/_xlfn.$1/g;
$formula =~ s/\b(BITAND\()/_xlfn.$1/g;
$formula =~ s/\b(BITLSHIFT\()/_xlfn.$1/g;
$formula =~ s/\b(BITOR\()/_xlfn.$1/g;
$formula =~ s/\b(BITRSHIFT\()/_xlfn.$1/g;
$formula =~ s/\b(BITXOR\()/_xlfn.$1/g;
$formula =~ s/\b(CEILING.MATH\()/_xlfn.$1/g;
$formula =~ s/\b(CEILING.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.DIST.RT\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.INV.RT\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.INV\()/_xlfn.$1/g;
$formula =~ s/\b(CHISQ.TEST\()/_xlfn.$1/g;
$formula =~ s/\b(COMBINA\()/_xlfn.$1/g;
$formula =~ s/\b(CONCAT\()/_xlfn.$1/g;
$formula =~ s/\b(CONFIDENCE.NORM\()/_xlfn.$1/g;
$formula =~ s/\b(CONFIDENCE.T\()/_xlfn.$1/g;
$formula =~ s/\b(COTH\()/_xlfn.$1/g;
$formula =~ s/\b(COT\()/_xlfn.$1/g;
$formula =~ s/\b(COVARIANCE.P\()/_xlfn.$1/g;
$formula =~ s/\b(COVARIANCE.S\()/_xlfn.$1/g;
$formula =~ s/\b(CSCH\()/_xlfn.$1/g;
$formula =~ s/\b(CSC\()/_xlfn.$1/g;
$formula =~ s/\b(DAYS\()/_xlfn.$1/g;
$formula =~ s/\b(DECIMAL\()/_xlfn.$1/g;
$formula =~ s/\b(ERF.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(ERFC.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(EXPON.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(F.DIST.RT\()/_xlfn.$1/g;
$formula =~ s/\b(F.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(F.INV.RT\()/_xlfn.$1/g;
$formula =~ s/\b(F.INV\()/_xlfn.$1/g;
$formula =~ s/\b(F.TEST\()/_xlfn.$1/g;
$formula =~ s/\b(FILTERXML\()/_xlfn.$1/g;
$formula =~ s/\b(FLOOR.MATH\()/_xlfn.$1/g;
$formula =~ s/\b(FLOOR.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS.CONFINT\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS.SEASONALITY\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS.STAT\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.ETS\()/_xlfn.$1/g;
$formula =~ s/\b(FORECAST.LINEAR\()/_xlfn.$1/g;
$formula =~ s/\b(FORMULATEXT\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMA.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMA.INV\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMALN.PRECISE\()/_xlfn.$1/g;
$formula =~ s/\b(GAMMA\()/_xlfn.$1/g;
$formula =~ s/\b(GAUSS\()/_xlfn.$1/g;
$formula =~ s/\b(HYPGEOM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(IFNA\()/_xlfn.$1/g;
$formula =~ s/\b(IFS\()/_xlfn.$1/g;
$formula =~ s/\b(IMAGE\()/_xlfn.$1/g;
$formula =~ s/\b(IMCOSH\()/_xlfn.$1/g;
$formula =~ s/\b(IMCOT\()/_xlfn.$1/g;
$formula =~ s/\b(IMCSCH\()/_xlfn.$1/g;
$formula =~ s/\b(IMCSC\()/_xlfn.$1/g;
$formula =~ s/\b(IMSECH\()/_xlfn.$1/g;
$formula =~ s/\b(IMSEC\()/_xlfn.$1/g;
$formula =~ s/\b(IMSINH\()/_xlfn.$1/g;
$formula =~ s/\b(IMTAN\()/_xlfn.$1/g;
$formula =~ s/\b(ISFORMULA\()/_xlfn.$1/g;
$formula =~ s/\b(ISOMITTED\()/_xlfn.$1/g;
$formula =~ s/\b(ISOWEEKNUM\()/_xlfn.$1/g;
$formula =~ s/\b(LET\()/_xlfn.$1/g;
$formula =~ s/\b(LOGNORM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(LOGNORM.INV\()/_xlfn.$1/g;
$formula =~ s/\b(MAXIFS\()/_xlfn.$1/g;
$formula =~ s/\b(MINIFS\()/_xlfn.$1/g;
$formula =~ s/\b(MODE.MULT\()/_xlfn.$1/g;
$formula =~ s/\b(MODE.SNGL\()/_xlfn.$1/g;
$formula =~ s/\b(MUNIT\()/_xlfn.$1/g;
$formula =~ s/\b(NEGBINOM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.INV\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.S.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(NORM.S.INV\()/_xlfn.$1/g;
$formula =~ s/\b(NUMBERVALUE\()/_xlfn.$1/g;
$formula =~ s/\b(PDURATION\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTILE.EXC\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTILE.INC\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTRANK.EXC\()/_xlfn.$1/g;
$formula =~ s/\b(PERCENTRANK.INC\()/_xlfn.$1/g;
$formula =~ s/\b(PERMUTATIONA\()/_xlfn.$1/g;
$formula =~ s/\b(PHI\()/_xlfn.$1/g;
$formula =~ s/\b(POISSON.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(QUARTILE.EXC\()/_xlfn.$1/g;
$formula =~ s/\b(QUARTILE.INC\()/_xlfn.$1/g;
$formula =~ s/\b(QUERYSTRING\()/_xlfn.$1/g;
$formula =~ s/\b(RANK.AVG\()/_xlfn.$1/g;
$formula =~ s/\b(RANK.EQ\()/_xlfn.$1/g;
$formula =~ s/\b(RRI\()/_xlfn.$1/g;
$formula =~ s/\b(SECH\()/_xlfn.$1/g;
$formula =~ s/\b(SEC\()/_xlfn.$1/g;
$formula =~ s/\b(SHEETS\()/_xlfn.$1/g;
$formula =~ s/\b(SHEET\()/_xlfn.$1/g;
$formula =~ s/\b(SKEW.P\()/_xlfn.$1/g;
$formula =~ s/\b(STDEV.P\()/_xlfn.$1/g;
$formula =~ s/\b(STDEV.S\()/_xlfn.$1/g;
$formula =~ s/\b(T.DIST.2T\()/_xlfn.$1/g;
$formula =~ s/\b(T.DIST.RT\()/_xlfn.$1/g;
$formula =~ s/\b(T.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(T.INV.2T\()/_xlfn.$1/g;
$formula =~ s/\b(T.INV\()/_xlfn.$1/g;
$formula =~ s/\b(T.TEST\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTAFTER\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTBEFORE\()/_xlfn.$1/g;
$formula =~ s/\b(TEXTJOIN\()/_xlfn.$1/g;
$formula =~ s/\b(UNICHAR\()/_xlfn.$1/g;
$formula =~ s/\b(UNICODE\()/_xlfn.$1/g;
$formula =~ s/\b(VALUETOTEXT\()/_xlfn.$1/g;
$formula =~ s/\b(VAR.P\()/_xlfn.$1/g;
$formula =~ s/\b(VAR.S\()/_xlfn.$1/g;
$formula =~ s/\b(WEBSERVICE\()/_xlfn.$1/g;
$formula =~ s/\b(WEIBULL.DIST\()/_xlfn.$1/g;
$formula =~ s/\b(XMATCH\()/_xlfn.$1/g;
$formula =~ s/\b(XOR\()/_xlfn.$1/g;
$formula =~ s/\b(Z.TEST\()/_xlfn.$1/g;
return $formula;
}
###############################################################################
#
# write_formula($row, $col, $formula, $format)
#
# Write a formula to the specified row and column (zero indexed).
#
# $format is optional.
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
#
sub write_formula {
my $self = shift;
# Check for a cell reference in A1 notation and substitute row and column
if ( $_[0] =~ /^\D/ ) {
@_ = $self->_substitute_cellref( @_ );
}
if ( @_ < 3 ) { return -1 } # Check the number of args
my $row = $_[0]; # Zero indexed row
my $col = $_[1]; # Zero indexed column
my $formula = $_[2]; # The formula text string
my $xf = $_[3]; # The format object.
my $value = $_[4]; # Optional formula value.
my $type = 'f'; # The data type
# Check for dynamic array functions.
local $_ = $formula;
if ( m{\bANCHORARRAY\(}
|| m{\bBYCOL\(}
|| m{\bBYROW\(}
|| m{\bCHOOSECOLS\(}
|| m{\bCHOOSEROWS\(}
|| m{\bDROP\(}
|| m{\bEXPAND\(}
|| m{\bFILTER\(}
|| m{\bHSTACK\(}
|| m{\bLAMBDA\(}
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
|| m{\bTAKE\(}
|| m{\bTEXTSPLIT\(}
|| m{\bTOCOL\(}
|| m{\bTOROW\(}
|| m{\bUNIQUE\(}
|| m{\bVSTACK\(}
|| m{\bWRAPCOLS\(}
|| m{\bWRAPROWS\(}
|| m{\bXLOOKUP\(} )
{
return $self->write_dynamic_array_formula( $row, $col, $row, $col,
$formula, $xf, $value );
}
# Hand off array formulas.
if ( $formula =~ /^{=.*}$/ ) {
return $self->write_array_formula( $row, $col, $row, $col, $formula,
$xf, $value );
}
# Check that row and col are valid and store max and min values
return -2 if $self->_check_dimensions( $row, $col );
# Expand out the formula.
$formula = $self->_prepare_formula($formula);
# Write previous row if in in-line string optimization mode.
if ( $self->{_optimization} == 1 && $row > $self->{_previous_row} ) {
$self->_write_single_row( $row );
}
$self->{_table}->{$row}->{$col} = [ $type, $formula, $xf, $value ];
return 0;
}
# Internal method shared by the write_array_formula() and
# write_dynamic_array_formula() methods.
sub _write_array_formula {
my $self = shift;
my $type = shift;
my @args = @_;
# Check for a cell reference in A1 notation and substitute row and column
if ( $args[0] =~ /^\D/ ) {
my $cellref = shift @args;
# Convert single cell to range.
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
@args = ( @dims, @args );
}
}
if ( @args < 5 ) { return -1 } # Check the number of args
my $row1 = $args[0]; # First row
my $col1 = $args[1]; # First column
my $row2 = $args[2]; # Last row
my $col2 = $args[3]; # Last column
my $formula = $args[4]; # The formula text string
my $xf = $args[5]; # The format object.
my $value = $args[6]; # Optional formula value.
# Swap last row/col with first row/col as necessary
( $row1, $row2 ) = ( $row2, $row1 ) if $row1 > $row2;
( $col1, $col2 ) = ( $col1, $col2 ) if $col1 > $col2;
# Check that row and col are valid and store max and min values.
return -2 if $self->_check_dimensions( $row1, $col1 );
return -2 if $self->_check_dimensions( $row2, $col2 );
# Define array range
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
if ( $row1 == $row2 and $col1 == $col2 ) {
$range = xl_rowcol_to_cell( $row1, $col1 );
}
else {
$range =
xl_rowcol_to_cell( $row1, $col1 ) . ':'
. xl_rowcol_to_cell( $row2, $col2 );
}
# Modify the formula string, as needed.
$formula = $self->_prepare_formula($formula);
# Write previous row if in in-line string optimization mode.
my $row = $row1;
if ( $self->{_optimization} == 1 && $row > $self->{_previous_row} ) {
$self->_write_single_row( $row );
}
$self->{_table}->{$row1}->{$col1} =
[ $type, $formula, $xf, $range, $value ];
# Pad out the rest of the area with formatted zeroes.
if ( !$self->{_optimization} ) {
for my $row ( $row1 .. $row2 ) {
for my $col ( $col1 .. $col2 ) {
next if $row == $row1 and $col == $col1;
$self->write_number( $row, $col, 0, $xf );
}
}
}
return 0;
}
###############################################################################
#
# write_array_formula($row1, $col1, $row2, $col2, $formula, $format)
#
# Write an array formula to the specified row and column (zero indexed).
#
# $format is optional.
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
#
sub write_array_formula {
my $self = shift;
return $self->_write_array_formula( 'a', @_ );
}
###############################################################################
#
# write_dynamic_array_formula($row1, $col1, $row2, $col2, $formula, $format)
#
# Write a dynamic formula to the specified row and column (zero indexed).
#
# $format is optional.
#
# Returns 0 : normal termination
# -1 : insufficient number of arguments
# -2 : row or column out of range
#
sub write_dynamic_array_formula {
my $self = shift;
my $error = $self->_write_array_formula( 'd', @_ );
if ( $error == 0 ) {
$self->{_has_dynamic_functions} = 1;
}
return $error;
}
###############################################################################
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
@_ = $self->_substitute_cellref( @_ );
}
my $row_first = shift;
my $col_first = shift;
my $row_last = shift;
my $col_last = shift;
my $format;
# Get the format. It can be in different positions for the different types.
if ( $type eq 'array_formula'
|| $type eq 'blank'
|| $type eq 'rich_string' )
{
# The format is the last element.
$format = $_[-1];
}
else {
# Or else it is after the token.
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
}
elsif ( $type eq 'date_time' ) {
$self->write_date_time( $row_first, $col_first, @_ );
}
elsif ( $type eq 'rich_string' ) {
$self->write_rich_string( $row_first, $col_first, @_ );
}
elsif ( $type eq 'url' ) {
$self->write_url( $row_first, $col_first, @_ );
}
elsif ( $type eq 'formula' ) {
$self->write_formula( $row_first, $col_first, @_ );
}
elsif ( $type eq 'array_formula' ) {
$self->write_formula_array( $row_first, $col_first, @_ );
}
else {
croak "Unknown type '$type'";
}
# Pad out the rest of the area with formatted blank cells.
for my $row ( $row_first .. $row_last ) {
for my $col ( $col_first .. $col_last ) {
next if $row == $row_first and $col == $col_first;
$self->write_blank( $row, $col, $format );
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
carp "Length of error message '$param->{error_message}'"
. " exceeds Excel's limit of 255";
return -3;
}
# Check that the input list don't exceed the maximum length.
if ( $param->{validate} eq 'list' ) {
if ( ref $param->{value} eq 'ARRAY' ) {
my $formula = join ',', @{ $param->{value} };
if ( length $formula > 255 ) {
carp "Length of list items '$formula' exceeds Excel's "
. "limit of 255, use a formula range instead";
return -3;
}
}
}
# Set some defaults if they haven't been defined by the user.
$param->{ignore_blank} = 1 if !defined $param->{ignore_blank};
$param->{dropdown} = 1 if !defined $param->{dropdown};
$param->{show_input} = 1 if !defined $param->{show_input};
$param->{show_error} = 1 if !defined $param->{show_error};
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
'bottom' => 'top10',
'text' => 'text',
'time_period' => 'timePeriod',
'blanks' => 'containsBlanks',
'no_blanks' => 'notContainsBlanks',
'errors' => 'containsErrors',
'no_errors' => 'notContainsErrors',
'2_color_scale' => '2_color_scale',
'3_color_scale' => '3_color_scale',
'data_bar' => 'dataBar',
'formula' => 'expression',
'icon_set' => 'iconSet',
);
# Check for valid validation types.
if ( not exists $valid_type{ lc( $param->{type} ) } ) {
carp "Unknown validation type '$param->{type}' for parameter "
. "'type' in conditional_formatting()";
return -3;
}
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
$param->{total_icons} = 5;
}
$param->{icons} =
$self->_set_icon_properties( $param->{total_icons}, $param->{icons} );
}
# Set the formatting range.
my $range = '';
my $start_cell = ''; # Use for formulas.
# Swap last row/col for first row/col as necessary
if ( $row1 > $row2 ) {
( $row1, $row2 ) = ( $row2, $row1 );
}
if ( $col1 > $col2 ) {
( $col1, $col2 ) = ( $col2, $col1 );
}
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
|| $param->{bar_direction} )
{
$param->{_is_data_bar_2010} = 1;
}
# Special handling of text criteria.
if ( $param->{type} eq 'text' ) {
if ( $param->{criteria} eq 'containsText' ) {
$param->{type} = 'containsText';
$param->{formula} = sprintf 'NOT(ISERROR(SEARCH("%s",%s)))',
$param->{value}, $start_cell;
}
elsif ( $param->{criteria} eq 'notContains' ) {
$param->{type} = 'notContainsText';
$param->{formula} = sprintf 'ISERROR(SEARCH("%s",%s))',
$param->{value}, $start_cell;
}
elsif ( $param->{criteria} eq 'beginsWith' ) {
$param->{type} = 'beginsWith';
$param->{formula} = sprintf 'LEFT(%s,%d)="%s"',
$start_cell, length( $param->{value} ), $param->{value};
}
elsif ( $param->{criteria} eq 'endsWith' ) {
$param->{type} = 'endsWith';
$param->{formula} = sprintf 'RIGHT(%s,%d)="%s"',
$start_cell, length( $param->{value} ), $param->{value};
}
else {
carp "Invalid text criteria '$param->{criteria}' "
. "in conditional_formatting()";
}
}
# Special handling of time time_period criteria.
if ( $param->{type} eq 'timePeriod' ) {
if ( $param->{criteria} eq 'yesterday' ) {
$param->{formula} = sprintf 'FLOOR(%s,1)=TODAY()-1', $start_cell;
}
elsif ( $param->{criteria} eq 'today' ) {
$param->{formula} = sprintf 'FLOOR(%s,1)=TODAY()', $start_cell;
}
elsif ( $param->{criteria} eq 'tomorrow' ) {
$param->{formula} = sprintf 'FLOOR(%s,1)=TODAY()+1', $start_cell;
}
elsif ( $param->{criteria} eq 'last7Days' ) {
$param->{formula} =
sprintf 'AND(TODAY()-FLOOR(%s,1)<=6,FLOOR(%s,1)<=TODAY())',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'lastWeek' ) {
$param->{formula} =
sprintf 'AND(TODAY()-ROUNDDOWN(%s,0)>=(WEEKDAY(TODAY())),'
. 'TODAY()-ROUNDDOWN(%s,0)<(WEEKDAY(TODAY())+7))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'thisWeek' ) {
$param->{formula} =
sprintf 'AND(TODAY()-ROUNDDOWN(%s,0)<=WEEKDAY(TODAY())-1,'
. 'ROUNDDOWN(%s,0)-TODAY()<=7-WEEKDAY(TODAY()))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'nextWeek' ) {
$param->{formula} =
sprintf 'AND(ROUNDDOWN(%s,0)-TODAY()>(7-WEEKDAY(TODAY())),'
. 'ROUNDDOWN(%s,0)-TODAY()<(15-WEEKDAY(TODAY())))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'lastMonth' ) {
$param->{formula} =
sprintf
'AND(MONTH(%s)=MONTH(TODAY())-1,OR(YEAR(%s)=YEAR(TODAY()),'
. 'AND(MONTH(%s)=1,YEAR(A1)=YEAR(TODAY())-1)))',
$start_cell, $start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'thisMonth' ) {
$param->{formula} =
sprintf 'AND(MONTH(%s)=MONTH(TODAY()),YEAR(%s)=YEAR(TODAY()))',
$start_cell, $start_cell;
}
elsif ( $param->{criteria} eq 'nextMonth' ) {
$param->{formula} =
sprintf
'AND(MONTH(%s)=MONTH(TODAY())+1,OR(YEAR(%s)=YEAR(TODAY()),'
. 'AND(MONTH(%s)=12,YEAR(%s)=YEAR(TODAY())+1)))',
$start_cell, $start_cell, $start_cell, $start_cell;
}
else {
carp "Invalid time_period criteria '$param->{criteria}' "
. "in conditional_formatting()";
}
}
# Special handling of blanks/error types.
if ( $param->{type} eq 'containsBlanks' ) {
$param->{formula} = sprintf 'LEN(TRIM(%s))=0', $start_cell;
}
if ( $param->{type} eq 'notContainsBlanks' ) {
$param->{formula} = sprintf 'LEN(TRIM(%s))>0', $start_cell;
}
if ( $param->{type} eq 'containsErrors' ) {
$param->{formula} = sprintf 'ISERROR(%s)', $start_cell;
}
if ( $param->{type} eq 'notContainsErrors' ) {
$param->{formula} = sprintf 'NOT(ISERROR(%s))', $start_cell;
}
# Special handling for 2 color scale.
if ( $param->{type} eq '2_color_scale' ) {
$param->{type} = 'colorScale';
# Color scales don't use any additional formatting.
$param->{format} = undef;
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
}
if ( $param->{max_type} eq 'max' && $param->{max_value} == 0 ) {
$param->{max_value} = undef;
}
# Store range for Excel 2010 data bars.
$param->{_range} = $range;
}
# Strip the leading = from formulas.
$param->{min_value} =~ s/^=// if defined $param->{min_value};
$param->{mid_value} =~ s/^=// if defined $param->{mid_value};
$param->{max_value} =~ s/^=// if defined $param->{max_value};
# Store the validation information until we close the worksheet.
push @{ $self->{_cond_formats}->{$range} }, $param;
}
###############################################################################
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
}
# Set the user defined 'type' property.
if ( defined $user_props->[$i]->{type} ) {
my $type = $user_props->[$i]->{type};
if ( $type ne 'percent'
&& $type ne 'percentile'
&& $type ne 'number'
&& $type ne 'formula' )
{
carp "Unknown icon property type '$props->{type}' for sub-"
. "property 'type' in conditional_formatting()";
}
else {
$props->[$i]->{type} = $type;
if ( $props->[$i]->{type} eq 'number' ) {
$props->[$i]->{type} = 'num';
}
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
my $col_id = 1;
for my $col_num ( $col1 .. $col2 ) {
# Set up the default column data.
my $col_data = {
_id => $col_id,
_name => 'Column' . $col_id,
_total_string => '',
_total_function => '',
_custom_total => '',
_formula => '',
_format => undef,
_name_format => undef,
};
# Overwrite the defaults with any use defined values.
if ( $param->{columns} ) {
# Check if there are user defined values for this column.
if ( my $user_data = $param->{columns}->[ $col_id - 1 ] ) {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
carp "add_table() contains duplicate name: '$name'";
return -1;
}
else {
$seen_names{$key} = 1;
}
# Get the header format if defined.
$col_data->{_name_format} = $user_data->{header_format};
# Handle the column formula.
if ( $user_data->{formula} ) {
my $formula = $user_data->{formula};
# Remove the leading = from formula.
$formula =~ s/^=//;
# Covert Excel 2010 "@" ref to 2007 "#This Row".
$formula =~ s/@/[#This Row],/g;
# Escape any future functions.
$formula = $self->_prepare_formula($formula, 1);
$col_data->{_formula} = $formula;
# We write the formulas below after the table data.
}
# Handle the function for the total row.
if ( $user_data->{total_function} ) {
my $formula = '';
my $function = $user_data->{total_function};
$function = 'countNums' if $function eq 'count_nums';
$function = 'stdDev' if $function eq 'std_dev';
my %subtotals = (
average => 101,
countNums => 102,
count => 103,
max => 104,
min => 105,
stdDev => 107,
sum => 109,
var => 110,
);
if ( exists $subtotals{$function} ) {
$formula =
_table_function_to_formula( $function,
$col_data->{_name} );
}
else {
$formula = $self->_prepare_formula($function, 1);
$col_data->{_custom_total} = $formula;
$function = 'custom';
}
$col_data->{_total_function} = $function;
my $value = $user_data->{total_value} || 0;
$self->write_formula( $row2, $col_num, $formula,
$user_data->{format}, $value );
}
elsif ( $user_data->{total_string} ) {
# Total label only (not a function).
my $total_string = $user_data->{total_string};
$col_data->{_total_string} = $total_string;
$self->write_string( $row2, $col_num, $total_string,
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
$self->write( $row, $col, $token, $col_formats[$j] );
}
$j++;
}
$i++;
}
}
# Write any columns formulas after the user supplied table data to
# overwrite it if required.
$col_id = 0;
for my $col_num ( $col1 .. $col2 ) {
my $column_data = $table{_columns}->[$col_id];
if ( $column_data && $column_data->{_formula} ) {
my $formula_format = $col_formats[$col_id];
my $formula = $column_data->{_formula};
for my $row ( $first_data_row .. $last_data_row ) {
$self->write_formula( $row, $col_num, $formula,
$formula_format );
}
}
$col_id++;
}
# Store the filter cell positions for use in the autofit calculation.
if ( $param->{autofilter} ) {
for my $col ( $col1 .. $col2 ) {
$self->{_filter_cells}->{"$row1:$col"} = 1;
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# Get the worksheet name for the range conversion below.
my $sheetname = quote_sheetname( $self->{_name} );
# Cleanup the input ranges.
for my $range ( @{ $sparkline->{_ranges} } ) {
# Remove the absolute reference $ symbols.
$range =~ s{\$}{}g;
# Remove the = from xl_range_formula(.
$range =~ s{^=}{};
# Convert a simple range into a full Sheet1!A1:D1 range.
if ( $range !~ /!/ ) {
$range = $sheetname . "!" . $range;
}
}
# Cleanup the input locations.
for my $location ( @{ $sparkline->{_locations} } ) {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
#
sub ignore_errors {
my $self = shift;
my $ignores = shift;
# List of valid input parameters.
my %valid_parameter = (
number_stored_as_text => 1,
eval_error => 1,
formula_differs => 1,
formula_range => 1,
formula_unlocked => 1,
empty_cell_reference => 1,
list_data_validation => 1,
calculated_column => 1,
two_digit_text_year => 1,
);
for my $param_key ( keys %$ignores ) {
if ( not exists $valid_parameter{$param_key} ) {
carp "Unknown parameter '$param_key' in ignore_errors()";
return -3;
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
###############################################################################
#
# Internal methods.
#
###############################################################################
###############################################################################
#
# _table_function_to_formula
#
# Convert a table total function to a worksheet formula.
#
sub _table_function_to_formula {
my $function = shift;
my $col_name = shift;
my $formula = '';
# Escape special characters, as required by Excel.
$col_name =~ s/'/''/g;
$col_name =~ s/#/'#/g;
$col_name =~ s/\[/'[/g;
$col_name =~ s/]/']/g;
my %subtotals = (
average => 101,
countNums => 102,
count => 103,
max => 104,
min => 105,
stdDev => 107,
sum => 109,
var => 110,
);
if ( exists $subtotals{$function} ) {
my $func_num = $subtotals{$function};
$formula = qq{SUBTOTAL($func_num,[$col_name])};
}
else {
carp "Unsupported function '$function' in add_table()";
}
return $formula;
}
###############################################################################
#
# _set_spark_color()
#
# Set the sparkline colour.
#
sub _set_spark_color {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# Store a string.
if ( $self->{_optimization} == 0 ) {
push @data, { 'sst_id' => $token };
}
else {
push @data, $token;
}
}
elsif ( $type eq 'f' ) {
# Store a formula.
push @data, $cell->[3] || 0;
}
elsif ( $type eq 'a' || $type eq 'd') {
# Store an array formula.
push @data, $cell->[4] || 0;
}
elsif ( $type eq 'b' ) {
# Store a empty cell.
push @data, '';
}
}
else {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# Check the number of args.
return -1 if @_ < 3;
# Convert UTF16 string to UTF8.
require Encode;
my $utf8_string = Encode::decode( 'UTF-16LE', $_[2] );
return $self->write_string( $_[0], $_[1], $utf8_string, $_[3] );
}
# No longer required. Was used to avoid slow formula parsing.
sub store_formula {
my $self = shift;
my $string = shift;
my @tokens = split /(\$?[A-I]?[A-Z]\$?\d+)/, $string;
return \@tokens;
}
# No longer required. Was used to avoid slow formula parsing.
sub repeat_formula {
my $self = shift;
# Convert A1 notation if present.
@_ = $self->_substitute_cellref( @_ ) if $_[0] =~ /^\D/;
if ( @_ < 2 ) { return -1 } # Check the number of args
my $row = shift; # Zero indexed row
my $col = shift; # Zero indexed column
my $formula_ref = shift; # Array ref with formula tokens
my $format = shift; # XF format
my @pairs = @_; # Pattern/replacement pairs
# Enforce an even number of arguments in the pattern/replacement list.
croak "Odd number of elements in pattern/replacement list" if @pairs % 2;
# Check that $formula is an array ref.
croak "Not a valid formula" if ref $formula_ref ne 'ARRAY';
my @tokens = @$formula_ref;
# Allow the user to specify the result of the formula by appending a
# result => $value pair to the end of the arguments.
my $value = undef;
if ( @pairs && $pairs[-2] eq 'result' ) {
$value = pop @pairs;
pop @pairs;
}
# Make the substitutions.
while ( @pairs ) {
my $pattern = shift @pairs;
my $replace = shift @pairs;
foreach my $token ( @tokens ) {
last if $token =~ s/$pattern/$replace/;
}
}
my $formula = join '', @tokens;
return $self->write_formula( $row, $col, $formula, $format, $value );
}
# Helper function to compare adjacent column information structures.
sub _compare_col_info {
my $col_options = shift;
my $previous_options = shift;
if ( defined $col_options->[0] != defined $previous_options->[0] ) {
return undef;
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
# Write the <cell> element. This is the innermost loop so efficiency is
# important where possible. The basic methodology is that the data of every
# cell type is passed in as follows:
#
# [ $row, $col, $aref]
#
# The aref, called $cell below, contains the following structure in all types:
#
# [ $type, $token, $xf, @args ]
#
# Where $type: represents the cell type, such as string, number, formula, etc.
# $token: is the actual data for the string, number, formula, etc.
# $xf: is the XF format object.
# @args: additional args relevant to the specific data type.
#
sub _write_cell {
my $self = shift;
my $row = shift;
my $col = shift;
my $cell = shift;
my $type = $cell->[0];
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
if ( $string =~ /^\s/ || $string =~ /\s$/ ) {
$preserve = 1;
}
$self->xml_inline_string( $string, $preserve, @attributes );
}
}
}
elsif ( $type eq 'f' ) {
# Write a formula.
my $value = $cell->[3];
$value = 0 if !defined $value;
# Check if the formula value is a string.
if ( $value
&& $value !~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ )
{
if ( exists $boolean{$value} ) {
push @attributes, ( 't' => 'b' );
$value = $boolean{$value};
}
elsif ( exists $error_codes{$value} ) {
push @attributes, ( 't' => 'e' );
}
else {
push @attributes, ( 't' => 'str' );
$value = Excel::Writer::XLSX::Package::XMLwriter::_escape_data(
$value );
}
}
$self->xml_formula_element( $token, $value, @attributes );
}
elsif ( $type eq 'a' || $type eq 'd') {
# Add metadata linkage for dynamic array formulas.
if ($type eq 'd') {
push @attributes, ( 'cm' => '1' );
}
# Write an array formula.
$self->xml_start_tag( 'c', @attributes );
$self->_write_cell_array_formula( $token, $cell->[3] );
$self->_write_cell_value( $cell->[4] );
$self->xml_end_tag( 'c' );
}
elsif ( $type eq 'l' ) {
# Write a boolean value.
push @attributes, ( 't' => 'b' );
$self->xml_start_tag( 'c', @attributes );
$self->_write_cell_value( $cell->[1] );
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
my $self = shift;
my $value = defined $_[0] ? $_[0] : '';
$self->xml_data_element( 'v', $value );
}
###############################################################################
#
# _write_cell_formula()
#
# Write the cell formula <f> element.
#
sub _write_cell_formula {
my $self = shift;
my $formula = defined $_[0] ? $_[0] : '';
$self->xml_data_element( 'f', $formula );
}
###############################################################################
#
# _write_cell_array_formula()
#
# Write the cell array formula <f> element.
#
sub _write_cell_array_formula {
my $self = shift;
my $formula = shift;
my $range = shift;
my @attributes = ( 't' => 'array', 'ref' => $range );
$self->xml_data_element( 'f', $formula, @attributes );
}
##############################################################################
#
# _write_sheet_calc_pr()
#
# Write the <sheetCalcPr> element for the worksheet calculation properties.
#
sub _write_sheet_calc_pr {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
if $param->{input_message};
push @attributes, ( 'sqref' => $sqref );
if ( $param->{validate} eq 'none' ) {
$self->xml_empty_tag( 'dataValidation', @attributes );
}
else {
$self->xml_start_tag( 'dataValidation', @attributes );
# Write the formula1 element.
$self->_write_formula_1( $param->{value} );
# Write the formula2 element.
$self->_write_formula_2( $param->{maximum} )
if defined $param->{maximum};
$self->xml_end_tag( 'dataValidation' );
}
}
##############################################################################
#
# _write_formula_1()
#
# Write the <formula1> element.
#
sub _write_formula_1 {
my $self = shift;
my $formula = shift;
# Convert a list array ref into a comma separated string.
if ( ref $formula eq 'ARRAY' ) {
$formula = join ',', @$formula;
$formula = qq("$formula");
}
$formula =~ s/^=//; # Remove formula symbol.
$self->xml_data_element( 'formula1', $formula );
}
##############################################################################
#
# _write_formula_2()
#
# Write the <formula2> element.
#
sub _write_formula_2 {
my $self = shift;
my $formula = shift;
$formula =~ s/^=//; # Remove formula symbol.
$self->xml_data_element( 'formula2', $formula );
}
##############################################################################
#
# _write_conditional_formats()
#
# Write the Worksheet conditional formats.
#
sub _write_conditional_formats {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
push @attributes, ( 'stopIfTrue' => 1 )
if $param->{stop_if_true};
if ( $param->{type} eq 'cellIs' ) {
push @attributes, ( 'operator' => $param->{criteria} );
$self->xml_start_tag( 'cfRule', @attributes );
if ( defined $param->{minimum} && defined $param->{maximum} ) {
$self->_write_formula( $param->{minimum} );
$self->_write_formula( $param->{maximum} );
}
else {
my $value = $param->{value};
# String "Cell" values must be quoted, apart from ranges.
if ( $value !~ /(\$?)([A-Z]{1,3})(\$?)(\d+)/
&& $value !~
/^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/ )
{
if ( $value !~ /^".*"$/ ) {
$value = qq("$value");
}
}
$self->_write_formula( $value );
}
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'aboveAverage' ) {
if ( $param->{criteria} =~ /below/ ) {
push @attributes, ( 'aboveAverage' => 0 );
}
if ( $param->{criteria} =~ /equal/ ) {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
}
elsif ($param->{type} eq 'containsText'
|| $param->{type} eq 'notContainsText'
|| $param->{type} eq 'beginsWith'
|| $param->{type} eq 'endsWith' )
{
push @attributes, ( 'operator' => $param->{criteria} );
push @attributes, ( 'text' => $param->{value} );
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{formula} );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'timePeriod' ) {
push @attributes, ( 'timePeriod' => $param->{criteria} );
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{formula} );
$self->xml_end_tag( 'cfRule' );
}
elsif ($param->{type} eq 'containsBlanks'
|| $param->{type} eq 'notContainsBlanks'
|| $param->{type} eq 'containsErrors'
|| $param->{type} eq 'notContainsErrors' )
{
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{formula} );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'colorScale' ) {
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_color_scale( $param );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'dataBar' ) {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
if ($param->{_is_data_bar_2010}) {
$self->_write_data_bar_ext( $param );
}
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'expression' ) {
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_formula( $param->{criteria} );
$self->xml_end_tag( 'cfRule' );
}
elsif ( $param->{type} eq 'iconSet' ) {
$self->xml_start_tag( 'cfRule', @attributes );
$self->_write_icon_set( $param );
$self->xml_end_tag( 'cfRule' );
}
}
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
$icon->{'value'},
$icon->{'criteria'}
);
}
$self->xml_end_tag( 'iconSet' );
}
##############################################################################
#
# _write_formula()
#
# Write the <formula> element.
#
sub _write_formula {
my $self = shift;
my $data = shift;
# Remove equality from formula.
$data =~ s/^=//;
$self->xml_data_element( 'formula', $data );
}
##############################################################################
#
# _write_color_scale()
#
# Write the <colorScale> element.
#
sub _write_color_scale {
lib/Excel/Writer/XLSX/Worksheet.pm view on Meta::CPAN
if ( exists $ignore->{number_stored_as_text} ) {
my $range = $ignore->{number_stored_as_text};
$self->_write_ignored_error( 'numberStoredAsText', $range );
}
if ( exists $ignore->{eval_error} ) {
my $range = $ignore->{eval_error};
$self->_write_ignored_error( 'evalError', $range );
}
if ( exists $ignore->{formula_differs} ) {
my $range = $ignore->{formula_differs};
$self->_write_ignored_error( 'formula', $range );
}
if ( exists $ignore->{formula_range} ) {
my $range = $ignore->{formula_range};
$self->_write_ignored_error( 'formulaRange', $range );
}
if ( exists $ignore->{formula_unlocked} ) {
my $range = $ignore->{formula_unlocked};
$self->_write_ignored_error( 'unlockedFormula', $range );
}
if ( exists $ignore->{empty_cell_reference} ) {
my $range = $ignore->{empty_cell_reference};
$self->_write_ignored_error( 'emptyCellReference', $range );
}
if ( exists $ignore->{list_data_validation} ) {
my $range = $ignore->{list_data_validation};
t/chart/sub_add_series.t view on Meta::CPAN
###############################################################################
#
# Test the add_series() method.
#
$caption = " \tChart: add_series()";
$expected = {
_categories => undef,
_values => '=Sheet1!$A$1:$A$5',
_name => undef,
_name_formula => undef,
_name_id => undef,
_cat_data_id => undef,
_val_data_id => 0,
_line => { _defined => 0 },
_fill => { _defined => 0 },
_pattern => undef,
_gradient => undef,
_marker => undef,
_trendline => undef,
_smooth => undef,
t/chart/sub_add_series.t view on Meta::CPAN
###############################################################################
#
# Test the add_series() method.
#
$caption = " \tChart: add_series()";
$expected = [
{
_categories => '=Sheet1!$A$1:$A$5',
_values => '=Sheet1!$B$1:$B$5',
_name => 'Text',
_name_formula => undef,
_name_id => undef,
_cat_data_id => 0,
_val_data_id => 1,
_line => { _defined => 0 },
_fill => { _defined => 0 },
_gradient => undef,
_pattern => undef,
_marker => undef,
_trendline => undef,
_smooth => undef,
t/chart/sub_add_series.t view on Meta::CPAN
###############################################################################
#
# Test the add_series() method.
#
$caption = " \tChart: add_series()";
$expected = [
{
_categories => undef,
_values => '=Sheet1!$A$1:$A$5',
_name => undef,
_name_formula => undef,
_name_id => undef,
_cat_data_id => undef,
_val_data_id => 0,
_line => { _defined => 0 },
_fill => { _defined => 0 },
_gradient => undef,
_pattern => undef,
_marker => undef,
_trendline => undef,
_smooth => undef,
t/chart/sub_add_series.t view on Meta::CPAN
###############################################################################
#
# Test the add_series() method.
#
$caption = " \tChart: add_series()";
$expected = {
_categories => '=Sheet1!$A$1:$A$5',
_values => '=Sheet1!$B$1:$B$5',
_name => 'Text',
_name_formula => undef,
_name_id => undef,
_cat_data_id => 0,
_val_data_id => 1,
_line => { _defined => 0 },
_fill => { _defined => 0 },
_gradient => undef,
_pattern => undef,
_marker => undef,
_trendline => undef,
_smooth => undef,
t/chart/sub_add_series.t view on Meta::CPAN
###############################################################################
#
# Test the add_series() method.
#
$caption = " \tChart: add_series()";
$expected = {
_categories => '=Sheet1!$A$1:$A$5',
_values => '=Sheet1!$B$1:$B$5',
_name => 'Text',
_name_formula => undef,
_name_id => undef,
_cat_data_id => 0,
_val_data_id => 1,
_line => { _defined => 0 },
_fill => { _defined => 0 },
_gradient => undef,
_pattern => undef,
_marker => undef,
_trendline => undef,
_smooth => undef,
t/chart/sub_write_series_formula.t view on Meta::CPAN
###############################################################################
#
# Test the _write_f() method.
#
$caption = " \tChart: _write_f()";
$expected = '<c:f>Sheet1!$A$1:$A$5</c:f>';
$chart = _new_object( \$got, 'Excel::Writer::XLSX::Chart' );
$chart->_write_series_formula( 'Sheet1!$A$1:$A$5' );
is( $got, $expected, $caption );
__END__
( run in 0.485 second using v1.01-cache-2.11-cpan-3cd7ad12f66 )