1Excel::Writer::XLSX(3)User Contributed Perl DocumentationExcel::Writer::XLSX(3)
2
6 Excel::Writer::XLSX - Create a new file in the Excel 2007+ XLSX format.
7
9 To write a string, a formatted string, a number and a formula to the
10 first worksheet in an Excel workbook called perl.xlsx:
11 use Excel::Writer::XLSX;
13 # Create a new Excel workbook
15 my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
16 # Add a worksheet
18 $worksheet = $workbook->add_worksheet();
19 # Add and define a format
21 $format = $workbook->add_format();
22 $format->set_bold();
23 $format->set_color( 'red' );
24 $format->set_align( 'center' );
25 # Write a formatted and unformatted string, row and column notation.
27 $col = $row = 0;
28 $worksheet->write( $row, $col, 'Hi Excel!', $format );
29 $worksheet->write( 1, $col, 'Hi Excel!' );
30 # Write a number and a formula using A1 notation
32 $worksheet->write( 'A3', 1.2345 );
33 $worksheet->write( 'A4', '=SIN(PI()/4)' );
34 $workbook->close();
36
38 The "Excel::Writer::XLSX" module can be used to create an Excel file in
39 the 2007+ XLSX format.
40 Multiple worksheets can be added to a workbook and formatting can be
42 applied to cells. Text, numbers, and formulas can be written to the
43 cells.
44
46 "Excel::Writer::XLSX" uses the same interface as the
47 Spreadsheet::WriteExcel module which produces an Excel file in binary
48 XLS format.
49
51 Excel::Writer::XLSX tries to provide an interface to as many of Excel's
52 features as possible. As a result there is a lot of documentation to
53 accompany the interface and it can be difficult at first glance to see
54 what it important and what is not. So for those of you who prefer to
55 assemble Ikea furniture first and then read the instructions, here are
56 four easy steps:
57 1. Create a new Excel workbook (i.e. file) using new().
59 2. Add a worksheet to the new workbook using add_worksheet().
61 3. Write to the worksheet using write().
63 4. close() the file.
65 Like this:
67 use Excel::Writer::XLSX; # Step 0
69 my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' ); # Step 1
71 $worksheet = $workbook->add_worksheet(); # Step 2
72 $worksheet->write( 'A1', 'Hi Excel!' ); # Step 3
73 $workbook->close(); # Step 4
75 This will create an Excel file called "perl.xlsx" with a single
77 worksheet and the text 'Hi Excel!' in the relevant cell. And that's it.
78 Okay, so there is actually a zeroth step as well, but "use module" goes
79 without saying. There are many examples that come with the distribution
80 and which you can use to get you started. See "EXAMPLES".
81 Those of you who read the instructions first and assemble the furniture
83 afterwards will know how to proceed. ;-)
84
86 The Excel::Writer::XLSX module provides an object oriented interface to
87 a new Excel workbook. The following methods are available through a new
88 workbook.
89 new()
91 add_worksheet()
92 add_format()
93 add_chart()
94 add_shape()
95 add_vba_project()
96 set_vba_name()
97 close()
98 set_properties()
99 set_custom_property()
100 define_name()
101 set_tempdir()
102 set_custom_color()
103 sheets()
104 get_worksheet_by_name()
105 set_1904()
106 set_optimization()
107 set_calc_mode()
108 get_default_url_format()
109 read_only_recommended()
110 If you are unfamiliar with object oriented interfaces or the way that
112 they are implemented in Perl have a look at "perlobj" and "perltoot" in
113 the main Perl documentation.
114 new()
116 A new Excel workbook is created using the new() constructor which
117 accepts either a filename or a filehandle as a parameter. The following
118 example creates a new Excel file based on a filename:
119 my $workbook = Excel::Writer::XLSX->new( 'filename.xlsx' );
121 my $worksheet = $workbook->add_worksheet();
122 $worksheet->write( 0, 0, 'Hi Excel!' );
123 $workbook->close();
124 Here are some other examples of using new() with filenames:
126 my $workbook1 = Excel::Writer::XLSX->new( $filename );
128 my $workbook2 = Excel::Writer::XLSX->new( '/tmp/filename.xlsx' );
129 my $workbook3 = Excel::Writer::XLSX->new( "c:\\tmp\\filename.xlsx" );
130 my $workbook4 = Excel::Writer::XLSX->new( 'c:\tmp\filename.xlsx' );
131 The last two examples demonstrates how to create a file on DOS or
133 Windows where it is necessary to either escape the directory separator
134 "\" or to use single quotes to ensure that it isn't interpolated. For
135 more information see "perlfaq5: Why can't I use "C:\temp\foo" in DOS
136 paths?".
137 It is recommended that the filename uses the extension ".xlsx" rather
139 than ".xls" since the latter causes an Excel warning when used with the
140 XLSX format.
141 The new() constructor returns a Excel::Writer::XLSX object that you can
143 use to add worksheets and store data. It should be noted that although
144 "my" is not specifically required it defines the scope of the new
145 workbook variable and, in the majority of cases, ensures that the
146 workbook is closed properly without explicitly calling the close()
147 method.
148 If the file cannot be created, due to file permissions or some other
150 reason, "new" will return "undef". Therefore, it is good practice to
151 check the return value of "new" before proceeding. As usual the Perl
152 variable $! will be set if there is a file creation error. You will
153 also see one of the warning messages detailed in "DIAGNOSTICS":
154 my $workbook = Excel::Writer::XLSX->new( 'protected.xlsx' );
156 die "Problems creating new Excel file: $!" unless defined $workbook;
157 You can also pass a valid filehandle to the new() constructor. For
159 example in a CGI program you could do something like this:
160 binmode( STDOUT );
162 my $workbook = Excel::Writer::XLSX->new( \*STDOUT );
163 The requirement for binmode() is explained below.
165 See also, the "cgi.pl" program in the "examples" directory of the
167 distro.
168 In "mod_perl" programs where you will have to do something like the
170 following:
171 # mod_perl 1
173 ...
174 tie *XLSX, 'Apache';
175 binmode( XLSX );
176 my $workbook = Excel::Writer::XLSX->new( \*XLSX );
177 ...
178 # mod_perl 2
180 ...
181 tie *XLSX => $r; # Tie to the Apache::RequestRec object
182 binmode( *XLSX );
183 my $workbook = Excel::Writer::XLSX->new( \*XLSX );
184 ...
185 See also, the "mod_perl1.pl" and "mod_perl2.pl" programs in the
187 "examples" directory of the distro.
188 Filehandles can also be useful if you want to stream an Excel file over
190 a socket or if you want to store an Excel file in a scalar.
191 For example here is a way to write an Excel file to a scalar:
193 #!/usr/bin/perl -w
195 use strict;
197 use Excel::Writer::XLSX;
198 open my $fh, '>', \my $str or die "Failed to open filehandle: $!";
200 my $workbook = Excel::Writer::XLSX->new( $fh );
202 my $worksheet = $workbook->add_worksheet();
203 $worksheet->write( 0, 0, 'Hi Excel!' );
205 $workbook->close();
207 # The Excel file in now in $str. Remember to binmode() the output
209 # filehandle before printing it.
210 binmode STDOUT;
211 print $str;
212 See also the "write_to_scalar.pl" and "filehandle.pl" programs in the
214 "examples" directory of the distro.
215 Note about the requirement for binmode(). An Excel file is comprised of
217 binary data. Therefore, if you are using a filehandle you should ensure
218 that you binmode() it prior to passing it to new().You should do this
219 regardless of whether you are on a Windows platform or not.
220 You don't have to worry about binmode() if you are using filenames
222 instead of filehandles. Excel::Writer::XLSX performs the binmode()
223 internally when it converts the filename to a filehandle. For more
224 information about binmode() see "perlfunc" and "perlopentut" in the
225 main Perl documentation.
226 add_worksheet( $sheetname )
228 At least one worksheet should be added to a new workbook. A worksheet
229 is used to write data into cells:
230 $worksheet1 = $workbook->add_worksheet(); # Sheet1
232 $worksheet2 = $workbook->add_worksheet( 'Foglio2' ); # Foglio2
233 $worksheet3 = $workbook->add_worksheet( 'Data' ); # Data
234 $worksheet4 = $workbook->add_worksheet(); # Sheet4
235 If $sheetname is not specified the default Excel convention will be
237 followed, i.e. Sheet1, Sheet2, etc.
238 The worksheet name must be a valid Excel worksheet name, i.e:
240 • It must be less than 32 characters.
242 • It cannot contain any of the following characters: "[ ] : * ? / \"
244 • It cannot start or end with an apostrophe.
246 • It cannot be the same as an existing worksheet name (or a case
248 insensitive variant).
249 Note, the sheetname should not be "History" (case insensitive) which is
251 reserved in English language versions of Excel. Non-English versions
252 may have restrictions on the equivalent word.
253 See the Excel worksheet naming rules at
255 <https://support.office.com/en-ie/article/rename-a-worksheet-3f1f7148-ee83-404d-8ef0-9ff99fbad1f9>.
256 add_format( %properties )
258 The add_format() method can be used to create new Format objects which
259 are used to apply formatting to a cell. You can either define the
260 properties at creation time via a hash of property values or later via
261 method calls.
262 $format1 = $workbook->add_format( %props ); # Set properties at creation
264 $format2 = $workbook->add_format(); # Set properties later
265 See the "CELL FORMATTING" section for more details about Format
267 properties and how to set them.
268 add_chart( %properties )
270 This method is use to create a new chart either as a standalone
271 worksheet (the default) or as an embeddable object that can be inserted
272 into a worksheet via the insert_chart() Worksheet method.
273 my $chart = $workbook->add_chart( type => 'column' );
275 The properties that can be set are:
277 type (required)
279 subtype (optional)
280 name (optional)
281 embedded (optional)
282 • "type"
284 This is a required parameter. It defines the type of chart that
286 will be created.
287 my $chart = $workbook->add_chart( type => 'line' );
289 The available types are:
291 area
293 bar
294 column
295 line
296 pie
297 doughnut
298 scatter
299 stock
300 • "subtype"
302 Used to define a chart subtype where available.
304 my $chart = $workbook->add_chart( type => 'bar', subtype => 'stacked' );
306 See the Excel::Writer::XLSX::Chart documentation for a list of
308 available chart subtypes.
309 • "name"
311 Set the name for the chart sheet. The name property is optional and
313 if it isn't supplied will default to "Chart1 .. n". The name must
314 be a valid Excel worksheet name. See add_worksheet() for more
315 details on valid sheet names. The "name" property can be omitted
316 for embedded charts.
317 my $chart = $workbook->add_chart( type => 'line', name => 'Results Chart' );
319 • "embedded"
321 Specifies that the Chart object will be inserted in a worksheet via
323 the insert_chart() Worksheet method. It is an error to try insert a
324 Chart that doesn't have this flag set.
325 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
327 # Configure the chart.
329 ...
330 # Insert the chart into the a worksheet.
332 $worksheet->insert_chart( 'E2', $chart );
333 See Excel::Writer::XLSX::Chart for details on how to configure the
335 chart object once it is created. See also the "chart_*.pl" programs in
336 the examples directory of the distro.
337 add_shape( %properties )
339 The add_shape() method can be used to create new shapes that may be
340 inserted into a worksheet.
341 You can either define the properties at creation time via a hash of
343 property values or later via method calls.
344 # Set properties at creation.
346 $plus = $workbook->add_shape(
347 type => 'plus',
348 id => 3,
349 width => $pw,
350 height => $ph
351 );
352 # Default rectangle shape. Set properties later.
355 $rect = $workbook->add_shape();
356 See Excel::Writer::XLSX::Shape for details on how to configure the
358 shape object once it is created.
359 See also the "shape*.pl" programs in the examples directory of the
361 distro.
362 add_vba_project( 'vbaProject.bin' )
364 The add_vba_project() method can be used to add macros or functions to
365 an Excel::Writer::XLSX file using a binary VBA project file that has
366 been extracted from an existing Excel "xlsm" file.
367 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
369 $workbook->add_vba_project( './vbaProject.bin' );
371 The supplied "extract_vba" utility can be used to extract the required
373 "vbaProject.bin" file from an existing Excel file:
374 $ extract_vba file.xlsm
376 Extracted 'vbaProject.bin' successfully
377 Macros can be tied to buttons using the worksheet insert_button()
379 method (see the "WORKSHEET METHODS" section for details):
380 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
382 Note, Excel uses the file extension "xlsm" instead of "xlsx" for files
384 that contain macros. It is advisable to follow the same convention.
385 See also the "macros.pl" example file and the "WORKING WITH VBA
387 MACROS".
388 set_vba_name()
390 The set_vba_name() method can be used to set the VBA codename for the
391 workbook. This is sometimes required when a "vbaProject macro" included
392 via add_vba_project() refers to the workbook. The default Excel VBA
393 name of "ThisWorkbook" is used if a user defined name isn't specified.
394 See also "WORKING WITH VBA MACROS".
395 close()
397 In general your Excel file will be closed automatically when your
398 program ends or when the Workbook object goes out of scope. However it
399 is recommended to explicitly call the close() method close the Excel
400 file and avoid the potential issues outlined below. The close() method
401 is called like this:
402 $workbook->close();
404 The return value of close() is the same as that returned by perl when
406 it closes the file created by new(). This allows you to handle error
407 conditions in the usual way:
408 $workbook->close() or die "Error closing file: $!";
410 An explicit close() is required if the file must be closed prior to
412 performing some external action on it such as copying it, reading its
413 size or attaching it to an email.
414 In addition, close() may be required to prevent perl's garbage
416 collector from disposing of the Workbook, Worksheet and Format objects
417 in the wrong order. Situations where this can occur are:
418 • If my() was not used to declare the scope of a workbook variable
420 created using new().
421 • If the new(), add_worksheet() or add_format() methods are called in
423 subroutines.
424 The reason for this is that Excel::Writer::XLSX relies on Perl's
426 "DESTROY" mechanism to trigger destructor methods in a specific
427 sequence. This may not happen in cases where the Workbook, Worksheet
428 and Format variables are not lexically scoped or where they have
429 different lexical scopes.
430 To avoid these issues it is recommended that you always close the
432 Excel::Writer::XLSX filehandle using close().
433 set_size( $width, $height )
435 The set_size() method can be used to set the size of a workbook window.
436 $workbook->set_size(1200, 800);
438 The Excel window size was used in Excel 2007 to define the width and
440 height of a workbook window within the Multiple Document Interface
441 (MDI). In later versions of Excel for Windows this interface was
442 dropped. This method is currently only useful when setting the window
443 size in Excel for Mac 2011. The units are pixels and the default size
444 is 1073 x 644.
445 Note, this doesn't equate exactly to the Excel for Mac pixel size since
447 it is based on the original Excel 2007 for Windows sizing.
448 set_tab_ratio( $tab_ratio )
450 The set_tab_ratio() method can be used to set the ratio between
451 worksheet tabs and the horizontal slider at the bottom of a workbook.
452 This can be increased to give more room to the tabs or reduced to
453 increase the size of the horizontal slider:
454 $workbook->set_tab_ratio(75);
456 The default value in Excel is 60.
458 set_properties()
460 The "set_properties" method can be used to set the document properties
461 of the Excel file created by "Excel::Writer::XLSX". These properties
462 are visible when you use the "Office Button -> Prepare -> Properties"
463 option in Excel and are also available to external applications that
464 read or index Windows files.
465 The properties should be passed in hash format as follows:
467 $workbook->set_properties(
469 title => 'This is an example spreadsheet',
470 author => 'John McNamara',
471 comments => 'Created with Perl and Excel::Writer::XLSX',
472 );
473 The properties that can be set are:
475 title
477 subject
478 author
479 manager
480 company
481 category
482 keywords
483 comments
484 status
485 hyperlink_base
486 created - File create date. Should be an aref of gmtime() values.
487 See also the "properties.pl" program in the examples directory of the
489 distro.
490 set_custom_property( $name, $value, $type)
492 The "set_custom_property" method can be used to set one of more custom
493 document properties not covered by the set_properties() method above.
494 These properties are visible when you use the "Office Button -> Prepare
495 -> Properties -> Advanced Properties -> Custom" option in Excel and are
496 also available to external applications that read or index Windows
497 files.
498 The "set_custom_property" method takes 3 parameters:
500 $workbook-> set_custom_property( $name, $value, $type);
502 Where the available types are:
504 text
506 date
507 number
508 bool
509 For example:
511 $workbook->set_custom_property( 'Checked by', 'Eve', 'text' );
513 $workbook->set_custom_property( 'Date completed', '2016-12-12T23:00:00Z', 'date' );
514 $workbook->set_custom_property( 'Document number', '12345' , 'number' );
515 $workbook->set_custom_property( 'Reference', '1.2345', 'number' );
516 $workbook->set_custom_property( 'Has review', 1, 'bool' );
517 $workbook->set_custom_property( 'Signed off', 0, 'bool' );
518 $workbook->set_custom_property( 'Department', $some_string, 'text' );
519 $workbook->set_custom_property( 'Scale', '1.2345678901234', 'number' );
520 Dates should by in ISO8601 "yyyy-mm-ddThh:mm:ss.sssZ" date format in
522 Zulu time, as shown above.
523 The "text" and "number" types are optional since they can usually be
525 inferred from the data:
526 $workbook->set_custom_property( 'Checked by', 'Eve' );
528 $workbook->set_custom_property( 'Reference', '1.2345' );
529 The $name and $value parameters are limited to 255 characters by Excel.
531 define_name()
533 This method is used to defined a name that can be used to represent a
534 value, a single cell or a range of cells in a workbook.
535 For example to set a global/workbook name:
537 # Global/workbook names.
539 $workbook->define_name( 'Exchange_rate', '=0.96' );
540 $workbook->define_name( 'Sales', '=Sheet1!$G$1:$H$10' );
541 It is also possible to define a local/worksheet name by prefixing the
543 name with the sheet name using the syntax "sheetname!definedname":
544 # Local/worksheet name.
546 $workbook->define_name( 'Sheet2!Sales', '=Sheet2!$G$1:$G$10' );
547 If the sheet name contains spaces or special characters you must
549 enclose it in single quotes like in Excel:
550 $workbook->define_name( "'New Data'!Sales", '=Sheet2!$G$1:$G$10' );
552 See the defined_name.pl program in the examples dir of the distro.
554 Refer to the following to see Excel's syntax rules for defined names:
556 <http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names>
557 set_tempdir()
559 "Excel::Writer::XLSX" stores worksheet data in temporary files prior to
560 assembling the final workbook.
561 The "File::Temp" module is used to create these temporary files.
563 File::Temp uses "File::Spec" to determine an appropriate location for
564 these files such as "/tmp" or "c:\windows\temp". You can find out which
565 directory is used on your system as follows:
566 perl -MFile::Spec -le "print File::Spec->tmpdir()"
568 If the default temporary file directory isn't accessible to your
570 application, or doesn't contain enough space, you can specify an
571 alternative location using the set_tempdir() method:
572 $workbook->set_tempdir( '/tmp/writeexcel' );
574 $workbook->set_tempdir( 'c:\windows\temp\writeexcel' );
575 The directory for the temporary file must exist, set_tempdir() will not
577 create a new directory.
578 set_custom_color( $index, $red, $green, $blue )
580 The method is maintained for backward compatibility with
581 Spreadsheet::WriteExcel. Excel::Writer::XLSX programs don't require
582 this method and colours can be specified using a Html style "#RRGGBB"
583 value, see "WORKING WITH COLOURS".
584 sheets( 0, 1, ... )
586 The sheets() method returns a list, or a sliced list, of the worksheets
587 in a workbook.
588 If no arguments are passed the method returns a list of all the
590 worksheets in the workbook. This is useful if you want to repeat an
591 operation on each worksheet:
592 for $worksheet ( $workbook->sheets() ) {
594 print $worksheet->get_name();
595 }
596 You can also specify a slice list to return one or more worksheet
598 objects:
599 $worksheet = $workbook->sheets( 0 );
601 $worksheet->write( 'A1', 'Hello' );
602 Or since the return value from sheets() is a reference to a worksheet
604 object you can write the above example as:
605 $workbook->sheets( 0 )->write( 'A1', 'Hello' );
607 The following example returns the first and last worksheet in a
609 workbook:
610 for $worksheet ( $workbook->sheets( 0, -1 ) ) {
612 # Do something
613 }
614 Array slices are explained in the "perldata" manpage.
616 get_worksheet_by_name()
618 The get_worksheet_by_name() function return a worksheet or chartsheet
619 object in the workbook using the sheetname:
620 $worksheet = $workbook->get_worksheet_by_name('Sheet1');
622 set_1904()
624 Excel stores dates as real numbers where the integer part stores the
625 number of days since the epoch and the fractional part stores the
626 percentage of the day. The epoch can be either 1900 or 1904. Excel for
627 Windows uses 1900 and Excel for Macintosh uses 1904. However, Excel on
628 either platform will convert automatically between one system and the
629 other.
630 Excel::Writer::XLSX stores dates in the 1900 format by default. If you
632 wish to change this you can call the set_1904() workbook method. You
633 can query the current value by calling the get_1904() workbook method.
634 This returns 0 for 1900 and 1 for 1904.
635 See also "DATES AND TIME IN EXCEL" for more information about working
637 with Excel's date system.
638 In general you probably won't need to use set_1904().
640 set_optimization()
642 The set_optimization() method is used to turn on optimizations in the
643 Excel::Writer::XLSX module. Currently there is only one optimization
644 available and that is to reduce memory usage.
645 $workbook->set_optimization();
647 See "SPEED AND MEMORY USAGE" for more background information.
649 Note, that with this optimization turned on a row of data is written
651 and then discarded when a cell in a new row is added via one of the
652 Worksheet "write_*()" methods. As such data should be written in
653 sequential row order once the optimization is turned on.
654 This method must be called before any calls to add_worksheet().
656 set_calc_mode( $mode )
658 Set the calculation mode for formulas in the workbook. This is mainly
659 of use for workbooks with slow formulas where you want to allow the
660 user to calculate them manually.
661 The mode parameter can be one of the following strings:
663 "auto"
665 The default. Excel will re-calculate formulas when a formula or a
666 value affecting the formula changes.
667 "manual"
669 Only re-calculate formulas when the user requires it. Generally by
670 pressing F9.
671 "auto_except_tables"
673 Excel will automatically re-calculate formulas except for tables.
674 get_default_url_format()
676 The get_default_url_format() method gets a copy of the default url
677 format used when a user defined format isn't specified with the
678 worksheet write_url() method. The format is the hyperlink style defined
679 by Excel for the default theme:
680 my $url_format = $workbook->get_default_url_format();
682 read_only_recommended()
684 The read_only_recommended() method can be used to set the Excel "Read-
685 only Recommended" option that is available when saving a file. This
686 presents the user of the file with an option to open it in "read-only"
687 mode. This means that any changes to the file can't be saved back to
688 the same file and must be saved to a new file. It can be set as
689 follows:
690 $workbook->read_only_recommended();
692
694 A new worksheet is created by calling the add_worksheet() method from a
695 workbook object:
696 $worksheet1 = $workbook->add_worksheet();
698 $worksheet2 = $workbook->add_worksheet();
699 The following methods are available through a new worksheet:
701 write()
703 write_number()
704 write_string()
705 write_rich_string()
706 keep_leading_zeros()
707 write_blank()
708 write_row()
709 write_col()
710 write_date_time()
711 write_url()
712 write_url_range()
713 write_formula()
714 write_boolean()
715 write_comment()
716 show_comments()
717 set_comments_author()
718 add_write_handler()
719 insert_image()
720 insert_chart()
721 insert_shape()
722 insert_button()
723 data_validation()
724 conditional_formatting()
725 add_sparkline()
726 add_table()
727 get_name()
728 activate()
729 select()
730 hide()
731 set_first_sheet()
732 protect()
733 unprotect_range()
734 set_selection()
735 set_top_left_cell()
736 set_row()
737 set_row_pixels()
738 set_default_row()
739 set_column()
740 set_column_pixels()
741 outline_settings()
742 freeze_panes()
743 split_panes()
744 merge_range()
745 merge_range_type()
746 set_zoom()
747 right_to_left()
748 hide_zero()
749 set_background()
750 set_tab_color()
751 autofilter()
752 filter_column()
753 filter_column_list()
754 set_vba_name()
755 ignore_errors()
756 Cell notation
758 Excel::Writer::XLSX supports two forms of notation to designate the
759 position of cells: Row-column notation and A1 notation.
760 Row-column notation uses a zero based index for both row and column
762 while A1 notation uses the standard Excel alphanumeric sequence of
763 column letter and 1-based row. For example:
764 (0, 0) # The top left cell in row-column notation.
766 ('A1') # The top left cell in A1 notation.
767 (1999, 29) # Row-column notation.
769 ('AD2000') # The same cell in A1 notation.
770 Row-column notation is useful if you are referring to cells
772 programmatically:
773 for my $i ( 0 .. 9 ) {
775 $worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
776 }
777 A1 notation is useful for setting up a worksheet manually and for
779 working with formulas:
780 $worksheet->write( 'H1', 200 );
782 $worksheet->write( 'H2', '=H1+1' );
783 In formulas and applicable methods you can also use the "A:A" column
785 notation:
786 $worksheet->write( 'A1', '=SUM(B:B)' );
788 The "Excel::Writer::XLSX::Utility" module that is included in the
790 distro contains helper functions for dealing with A1 notation, for
791 example:
792 use Excel::Writer::XLSX::Utility;
794 ( $row, $col ) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
796 $str = xl_rowcol_to_cell( 1, 2 ); # C2
797 For simplicity, the parameter lists for the worksheet method calls in
799 the following sections are given in terms of row-column notation. In
800 all cases it is also possible to use A1 notation.
801 Note: in Excel it is also possible to use a R1C1 notation. This is not
803 supported by Excel::Writer::XLSX.
804 write( $row, $column, $token, $format )
806 Excel makes a distinction between data types such as strings, numbers,
807 blanks, formulas and hyperlinks. To simplify the process of writing
808 data the write() method acts as a general alias for several more
809 specific methods:
810 write_string()
812 write_number()
813 write_blank()
814 write_formula()
815 write_url()
816 write_row()
817 write_col()
818 The general rule is that if the data looks like a something then a
820 something is written. Here are some examples in both row-column and A1
821 notation:
822 # Same as:
824 $worksheet->write( 0, 0, 'Hello' ); # write_string()
825 $worksheet->write( 1, 0, 'One' ); # write_string()
826 $worksheet->write( 2, 0, 2 ); # write_number()
827 $worksheet->write( 3, 0, 3.00001 ); # write_number()
828 $worksheet->write( 4, 0, "" ); # write_blank()
829 $worksheet->write( 5, 0, '' ); # write_blank()
830 $worksheet->write( 6, 0, undef ); # write_blank()
831 $worksheet->write( 7, 0 ); # write_blank()
832 $worksheet->write( 8, 0, 'http://www.perl.com/' ); # write_url()
833 $worksheet->write( 'A9', 'ftp://ftp.cpan.org/' ); # write_url()
834 $worksheet->write( 'A10', 'internal:Sheet1!A1' ); # write_url()
835 $worksheet->write( 'A11', 'external:c:\foo.xlsx' ); # write_url()
836 $worksheet->write( 'A12', '=A3 + 3*A4' ); # write_formula()
837 $worksheet->write( 'A13', '=SIN(PI()/4)' ); # write_formula()
838 $worksheet->write( 'A14', \@array ); # write_row()
839 $worksheet->write( 'A15', [\@array] ); # write_col()
840 # And if the keep_leading_zeros property is set:
842 $worksheet->write( 'A16', '2' ); # write_number()
843 $worksheet->write( 'A17', '02' ); # write_string()
844 $worksheet->write( 'A18', '00002' ); # write_string()
845 # Write an array formula. Not available in Spreadsheet::WriteExcel.
847 $worksheet->write( 'A19', '{=SUM(A1:B1*A2:B2)}' ); # write_formula()
848 The "looks like" rule is defined by regular expressions:
850 write_number() if $token is a number based on the following regex:
852 "$token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/".
853 write_string() if keep_leading_zeros() is set and $token is an integer
855 with leading zeros based on the following regex: "$token =~ /^0\d+$/".
856 write_blank() if $token is undef or a blank string: "undef", "" or ''.
858 write_url() if $token is a http, https, ftp or mailto URL based on the
860 following regexes: "$token =~ m|^[fh]tt?ps?://|" or "$token =~
861 m|^mailto:|".
862 write_url() if $token is an internal or external sheet reference based
864 on the following regex: "$token =~ m[^(in|ex)ternal:]".
865 write_formula() if the first character of $token is "=".
867 write_array_formula() if the $token matches "/^{=.*}$/".
869 write_row() if $token is an array ref.
871 write_col() if $token is an array ref of array refs.
873 write_string() if none of the previous conditions apply.
875 The $format parameter is optional. It should be a valid Format object,
877 see "CELL FORMATTING":
878 my $format = $workbook->add_format();
880 $format->set_bold();
881 $format->set_color( 'red' );
882 $format->set_align( 'center' );
883 $worksheet->write( 4, 0, 'Hello', $format ); # Formatted string
885 The write() method will ignore empty strings or "undef" tokens unless a
887 format is also supplied. As such you needn't worry about special
888 handling for empty or "undef" values in your data. See also the
889 write_blank() method.
890 One problem with the write() method is that occasionally data looks
892 like a number but you don't want it treated as a number. For example,
893 zip codes or ID numbers often start with a leading zero. If you write
894 this data as a number then the leading zero(s) will be stripped. You
895 can change this default behaviour by using the keep_leading_zeros()
896 method. While this property is in place any integers with leading zeros
897 will be treated as strings and the zeros will be preserved. See the
898 keep_leading_zeros() section for a full discussion of this issue.
899 You can also add your own data handlers to the write() method using
901 add_write_handler().
902 The write() method will also handle Unicode strings in "UTF-8" format.
904 The "write" methods return:
906 0 for success.
908 -1 for insufficient number of arguments.
909 -2 for row or column out of bounds.
910 -3 for string too long.
911 write_number( $row, $column, $number, $format )
913 Write an integer or a float to the cell specified by $row and $column:
914 $worksheet->write_number( 0, 0, 123456 );
916 $worksheet->write_number( 'A2', 2.3451 );
917 See the note about "Cell notation". The $format parameter is optional.
919 In general it is sufficient to use the write() method.
921 Note: some versions of Excel 2007 do not display the calculated values
923 of formulas written by Excel::Writer::XLSX. Applying all available
924 Service Packs to Excel should fix this.
925 write_string( $row, $column, $string, $format )
927 Write a string to the cell specified by $row and $column:
928 $worksheet->write_string( 0, 0, 'Your text here' );
930 $worksheet->write_string( 'A2', 'or here' );
931 The maximum string size is 32767 characters. However the maximum string
933 segment that Excel can display in a cell is 1000. All 32767 characters
934 can be displayed in the formula bar.
935 The $format parameter is optional.
937 The write() method will also handle strings in "UTF-8" format. See also
939 the "unicode_*.pl" programs in the examples directory of the distro.
940 In general it is sufficient to use the write() method. However, you may
942 sometimes wish to use the write_string() method to write data that
943 looks like a number but that you don't want treated as a number. For
944 example, zip codes or phone numbers:
945 # Write as a plain string
947 $worksheet->write_string( 'A1', '01209' );
948 However, if the user edits this string Excel may convert it back to a
950 number. To get around this you can use the Excel text format "@":
951 # Format as a string. Doesn't change to a number when edited
953 my $format1 = $workbook->add_format( num_format => '@' );
954 $worksheet->write_string( 'A2', '01209', $format1 );
955 See also the note about "Cell notation".
957 write_rich_string( $row, $column, $format, $string, ..., $cell_format )
959 The write_rich_string() method is used to write strings with multiple
960 formats. For example to write the string "This is bold and this is
961 italic" you would use the following:
962 my $bold = $workbook->add_format( bold => 1 );
964 my $italic = $workbook->add_format( italic => 1 );
965 $worksheet->write_rich_string( 'A1',
967 'This is ', $bold, 'bold', ' and this is ', $italic, 'italic' );
968 The basic rule is to break the string into fragments and put a $format
970 object before the fragment that you want to format. For example:
971 # Unformatted string.
973 'This is an example string'
974 # Break it into fragments.
976 'This is an ', 'example', ' string'
977 # Add formatting before the fragments you want formatted.
979 'This is an ', $format, 'example', ' string'
980 # In Excel::Writer::XLSX.
982 $worksheet->write_rich_string( 'A1',
983 'This is an ', $format, 'example', ' string' );
984 String fragments that don't have a format are given a default format.
986 So for example when writing the string "Some bold text" you would use
987 the first example below but it would be equivalent to the second:
988 # With default formatting:
990 my $bold = $workbook->add_format( bold => 1 );
991 $worksheet->write_rich_string( 'A1',
993 'Some ', $bold, 'bold', ' text' );
994 # Or more explicitly:
996 my $bold = $workbook->add_format( bold => 1 );
997 my $default = $workbook->add_format();
998 $worksheet->write_rich_string( 'A1',
1000 $default, 'Some ', $bold, 'bold', $default, ' text' );
1001 As with Excel, only the font properties of the format such as font
1003 name, style, size, underline, color and effects are applied to the
1004 string fragments. Other features such as border, background, text wrap
1005 and alignment must be applied to the cell.
1006 The write_rich_string() method allows you to do this by using the last
1008 argument as a cell format (if it is a format object). The following
1009 example centers a rich string in the cell:
1010 my $bold = $workbook->add_format( bold => 1 );
1012 my $center = $workbook->add_format( align => 'center' );
1013 $worksheet->write_rich_string( 'A5',
1015 'Some ', $bold, 'bold text', ' centered', $center );
1016 See the "rich_strings.pl" example in the distro for more examples.
1018 my $bold = $workbook->add_format( bold => 1 );
1020 my $italic = $workbook->add_format( italic => 1 );
1021 my $red = $workbook->add_format( color => 'red' );
1022 my $blue = $workbook->add_format( color => 'blue' );
1023 my $center = $workbook->add_format( align => 'center' );
1024 my $super = $workbook->add_format( font_script => 1 );
1025 # Write some strings with multiple formats.
1028 $worksheet->write_rich_string( 'A1',
1029 'This is ', $bold, 'bold', ' and this is ', $italic, 'italic' );
1030 $worksheet->write_rich_string( 'A3',
1032 'This is ', $red, 'red', ' and this is ', $blue, 'blue' );
1033 $worksheet->write_rich_string( 'A5',
1035 'Some ', $bold, 'bold text', ' centered', $center );
1036 $worksheet->write_rich_string( 'A7',
1038 $italic, 'j = k', $super, '(n-1)', $center );
1039 As with write_sting() the maximum string size is 32767 characters. See
1041 also the note about "Cell notation".
1042 keep_leading_zeros()
1044 This method changes the default handling of integers with leading zeros
1045 when using the write() method.
1046 The write() method uses regular expressions to determine what type of
1048 data to write to an Excel worksheet. If the data looks like a number it
1049 writes a number using write_number(). One problem with this approach is
1050 that occasionally data looks like a number but you don't want it
1051 treated as a number.
1052 Zip codes and ID numbers, for example, often start with a leading zero.
1054 If you write this data as a number then the leading zero(s) will be
1055 stripped. This is the also the default behaviour when you enter data
1056 manually in Excel.
1057 To get around this you can use one of three options. Write a formatted
1059 number, write the number as a string or use the keep_leading_zeros()
1060 method to change the default behaviour of write():
1061 # Implicitly write a number, the leading zero is removed: 1209
1063 $worksheet->write( 'A1', '01209' );
1064 # Write a zero padded number using a format: 01209
1066 my $format1 = $workbook->add_format( num_format => '00000' );
1067 $worksheet->write( 'A2', '01209', $format1 );
1068 # Write explicitly as a string: 01209
1070 $worksheet->write_string( 'A3', '01209' );
1071 # Write implicitly as a string: 01209
1073 $worksheet->keep_leading_zeros();
1074 $worksheet->write( 'A4', '01209' );
1075 The above code would generate a worksheet that looked like the
1077 following:
1078 -----------------------------------------------------------
1080 | | A | B | C | D | ...
1081 -----------------------------------------------------------
1082 | 1 | 1209 | | | | ...
1083 | 2 | 01209 | | | | ...
1084 | 3 | 01209 | | | | ...
1085 | 4 | 01209 | | | | ...
1086 The examples are on different sides of the cells due to the fact that
1088 Excel displays strings with a left justification and numbers with a
1089 right justification by default. You can change this by using a format
1090 to justify the data, see "CELL FORMATTING".
1091 It should be noted that if the user edits the data in examples "A3" and
1093 "A4" the strings will revert back to numbers. Again this is Excel's
1094 default behaviour. To avoid this you can use the text format "@":
1095 # Format as a string (01209)
1097 my $format2 = $workbook->add_format( num_format => '@' );
1098 $worksheet->write_string( 'A5', '01209', $format2 );
1099 The keep_leading_zeros() property is off by default. The
1101 keep_leading_zeros() method takes 0 or 1 as an argument. It defaults to
1102 1 if an argument isn't specified:
1103 $worksheet->keep_leading_zeros(); # Set on
1105 $worksheet->keep_leading_zeros( 1 ); # Set on
1106 $worksheet->keep_leading_zeros( 0 ); # Set off
1107 See also the add_write_handler() method.
1109 write_blank( $row, $column, $format )
1111 Write a blank cell specified by $row and $column:
1112 $worksheet->write_blank( 0, 0, $format );
1114 This method is used to add formatting to a cell which doesn't contain a
1116 string or number value.
1117 Excel differentiates between an "Empty" cell and a "Blank" cell. An
1119 "Empty" cell is a cell which doesn't contain data whilst a "Blank" cell
1120 is a cell which doesn't contain data but does contain formatting. Excel
1121 stores "Blank" cells but ignores "Empty" cells.
1122 As such, if you write an empty cell without formatting it is ignored:
1124 $worksheet->write( 'A1', undef, $format ); # write_blank()
1126 $worksheet->write( 'A2', undef ); # Ignored
1127 This seemingly uninteresting fact means that you can write arrays of
1129 data without special treatment for "undef" or empty string values.
1130 See the note about "Cell notation".
1132 write_row( $row, $column, $array_ref, $format )
1134 The write_row() method can be used to write a 1D or 2D array of data in
1135 one go. This is useful for converting the results of a database query
1136 into an Excel worksheet. You must pass a reference to the array of data
1137 rather than the array itself. The write() method is then called for
1138 each element of the data. For example:
1139 @array = ( 'awk', 'gawk', 'mawk' );
1141 $array_ref = \@array;
1142 $worksheet->write_row( 0, 0, $array_ref );
1144 # The above example is equivalent to:
1146 $worksheet->write( 0, 0, $array[0] );
1147 $worksheet->write( 0, 1, $array[1] );
1148 $worksheet->write( 0, 2, $array[2] );
1149 Note: For convenience the write() method behaves in the same way as
1151 write_row() if it is passed an array reference. Therefore the following
1152 two method calls are equivalent:
1153 $worksheet->write_row( 'A1', $array_ref ); # Write a row of data
1155 $worksheet->write( 'A1', $array_ref ); # Same thing
1156 As with all of the write methods the $format parameter is optional. If
1158 a format is specified it is applied to all the elements of the data
1159 array.
1160 Array references within the data will be treated as columns. This
1162 allows you to write 2D arrays of data in one go. For example:
1163 @eec = (
1165 ['maggie', 'milly', 'molly', 'may' ],
1166 [13, 14, 15, 16 ],
1167 ['shell', 'star', 'crab', 'stone']
1168 );
1169 $worksheet->write_row( 'A1', \@eec );
1171 Would produce a worksheet as follows:
1173 -----------------------------------------------------------
1175 | | A | B | C | D | E | ...
1176 -----------------------------------------------------------
1177 | 1 | maggie | 13 | shell | ... | ... | ...
1178 | 2 | milly | 14 | star | ... | ... | ...
1179 | 3 | molly | 15 | crab | ... | ... | ...
1180 | 4 | may | 16 | stone | ... | ... | ...
1181 | 5 | ... | ... | ... | ... | ... | ...
1182 | 6 | ... | ... | ... | ... | ... | ...
1183 To write the data in a row-column order refer to the write_col() method
1185 below.
1186 Any "undef" values in the data will be ignored unless a format is
1188 applied to the data, in which case a formatted blank cell will be
1189 written. In either case the appropriate row or column value will still
1190 be incremented.
1191 To find out more about array references refer to "perlref" and
1193 "perlreftut" in the main Perl documentation. To find out more about 2D
1194 arrays or "lists of lists" refer to "perllol".
1195 The write_row() method returns the first error encountered when writing
1197 the elements of the data or zero if no errors were encountered. See the
1198 return values described for the write() method above.
1199 The write_row() method allows the following idiomatic conversion of a
1201 text file to an Excel file:
1202 #!/usr/bin/perl -w
1204 use strict;
1206 use Excel::Writer::XLSX;
1207 my $workbook = Excel::Writer::XLSX->new( 'file.xlsx' );
1209 my $worksheet = $workbook->add_worksheet();
1210 open INPUT, 'file.txt' or die "Couldn't open file: $!";
1212 $worksheet->write( $. -1, 0, [split] ) while <INPUT>;
1214 $workbook->close();
1216 write_col( $row, $column, $array_ref, $format )
1218 The write_col() method can be used to write a 1D or 2D array of data in
1219 one go. This is useful for converting the results of a database query
1220 into an Excel worksheet. You must pass a reference to the array of data
1221 rather than the array itself. The write() method is then called for
1222 each element of the data. For example:
1223 @array = ( 'awk', 'gawk', 'mawk' );
1225 $array_ref = \@array;
1226 $worksheet->write_col( 0, 0, $array_ref );
1228 # The above example is equivalent to:
1230 $worksheet->write( 0, 0, $array[0] );
1231 $worksheet->write( 1, 0, $array[1] );
1232 $worksheet->write( 2, 0, $array[2] );
1233 As with all of the write methods the $format parameter is optional. If
1235 a format is specified it is applied to all the elements of the data
1236 array.
1237 Array references within the data will be treated as rows. This allows
1239 you to write 2D arrays of data in one go. For example:
1240 @eec = (
1242 ['maggie', 'milly', 'molly', 'may' ],
1243 [13, 14, 15, 16 ],
1244 ['shell', 'star', 'crab', 'stone']
1245 );
1246 $worksheet->write_col( 'A1', \@eec );
1248 Would produce a worksheet as follows:
1250 -----------------------------------------------------------
1252 | | A | B | C | D | E | ...
1253 -----------------------------------------------------------
1254 | 1 | maggie | milly | molly | may | ... | ...
1255 | 2 | 13 | 14 | 15 | 16 | ... | ...
1256 | 3 | shell | star | crab | stone | ... | ...
1257 | 4 | ... | ... | ... | ... | ... | ...
1258 | 5 | ... | ... | ... | ... | ... | ...
1259 | 6 | ... | ... | ... | ... | ... | ...
1260 To write the data in a column-row order refer to the write_row() method
1262 above.
1263 Any "undef" values in the data will be ignored unless a format is
1265 applied to the data, in which case a formatted blank cell will be
1266 written. In either case the appropriate row or column value will still
1267 be incremented.
1268 As noted above the write() method can be used as a synonym for
1270 write_row() and write_row() handles nested array refs as columns.
1271 Therefore, the following two method calls are equivalent although the
1272 more explicit call to write_col() would be preferable for
1273 maintainability:
1274 $worksheet->write_col( 'A1', $array_ref ); # Write a column of data
1276 $worksheet->write( 'A1', [ $array_ref ] ); # Same thing
1277 To find out more about array references refer to "perlref" and
1279 "perlreftut" in the main Perl documentation. To find out more about 2D
1280 arrays or "lists of lists" refer to "perllol".
1281 The write_col() method returns the first error encountered when writing
1283 the elements of the data or zero if no errors were encountered. See the
1284 return values described for the write() method above.
1285 write_date_time( $row, $col, $date_string, $format )
1287 The write_date_time() method can be used to write a date or time to the
1288 cell specified by $row and $column:
1289 $worksheet->write_date_time( 'A1', '2004-05-13T23:20', $date_format );
1291 The $date_string should be in the following format:
1293 yyyy-mm-ddThh:mm:ss.sss
1295 This conforms to an ISO8601 date but it should be noted that the full
1297 range of ISO8601 formats are not supported.
1298 The following variations on the $date_string parameter are permitted:
1300 yyyy-mm-ddThh:mm:ss.sss # Standard format
1302 yyyy-mm-ddT # No time
1303 Thh:mm:ss.sss # No date
1304 yyyy-mm-ddThh:mm:ss.sssZ # Additional Z (but not time zones)
1305 yyyy-mm-ddThh:mm:ss # No fractional seconds
1306 yyyy-mm-ddThh:mm # No seconds
1307 Note that the "T" is required in all cases.
1309 A date should always have a $format, otherwise it will appear as a
1311 number, see "DATES AND TIME IN EXCEL" and "CELL FORMATTING". Here is a
1312 typical example:
1313 my $date_format = $workbook->add_format( num_format => 'mm/dd/yy' );
1315 $worksheet->write_date_time( 'A1', '2004-05-13T23:20', $date_format );
1316 Valid dates should be in the range 1900-01-01 to 9999-12-31, for the
1318 1900 epoch and 1904-01-01 to 9999-12-31, for the 1904 epoch. As with
1319 Excel, dates outside these ranges will be written as a string.
1320 See also the date_time.pl program in the "examples" directory of the
1322 distro.
1323 write_url( $row, $col, $url, $format, $label )
1325 Write a hyperlink to a URL in the cell specified by $row and $column.
1326 The hyperlink is comprised of two elements: the visible label and the
1327 invisible link. The visible label is the same as the link unless an
1328 alternative label is specified. The $label parameter is optional. The
1329 label is written using the write() method. Therefore it is possible to
1330 write strings, numbers or formulas as labels.
1331 The $format parameter is also optional and the default Excel hyperlink
1333 style will be used if it isn't specified. If required you can access
1334 the default url format using the Workbook "get_default_url_format"
1335 method:
1336 my $url_format = $workbook->get_default_url_format();
1338 There are four web style URI's supported: "http://", "https://",
1340 "ftp://" and "mailto:":
1341 $worksheet->write_url( 0, 0, 'ftp://www.perl.org/' );
1343 $worksheet->write_url( 'A3', 'http://www.perl.com/' );
1344 $worksheet->write_url( 'A4', 'mailto:jmcnamara@cpan.org' );
1345 You can display an alternative string using the $label parameter:
1347 $worksheet->write_url( 1, 0, 'http://www.perl.com/', undef, 'Perl' );
1349 If you wish to have some other cell data such as a number or a formula
1351 you can overwrite the cell using another call to "write_*()":
1352 $worksheet->write_url( 'A1', 'http://www.perl.com/' );
1354 # Overwrite the URL string with a formula. The cell is still a link.
1356 # Note the use of the default url format for consistency with other links.
1357 my $url_format = $workbook->get_default_url_format();
1358 $worksheet->write_formula( 'A1', '=1+1', $url_format );
1359 There are two local URIs supported: "internal:" and "external:". These
1361 are used for hyperlinks to internal worksheet references or external
1362 workbook and worksheet references:
1363 $worksheet->write_url( 'A6', 'internal:Sheet2!A1' );
1365 $worksheet->write_url( 'A7', 'internal:Sheet2!A1' );
1366 $worksheet->write_url( 'A8', 'internal:Sheet2!A1:B2' );
1367 $worksheet->write_url( 'A9', q{internal:'Sales Data'!A1} );
1368 $worksheet->write_url( 'A10', 'external:c:\temp\foo.xlsx' );
1369 $worksheet->write_url( 'A11', 'external:c:\foo.xlsx#Sheet2!A1' );
1370 $worksheet->write_url( 'A12', 'external:..\foo.xlsx' );
1371 $worksheet->write_url( 'A13', 'external:..\foo.xlsx#Sheet2!A1' );
1372 $worksheet->write_url( 'A13', 'external:\\\\NET\share\foo.xlsx' );
1373 All of the these URI types are recognised by the write() method, see
1375 above.
1376 Worksheet references are typically of the form "Sheet1!A1". You can
1378 also refer to a worksheet range using the standard Excel notation:
1379 "Sheet1!A1:B2".
1380 In external links the workbook and worksheet name must be separated by
1382 the "#" character: "external:Workbook.xlsx#Sheet1!A1'".
1383 You can also link to a named range in the target worksheet. For example
1385 say you have a named range called "my_name" in the workbook
1386 "c:\temp\foo.xlsx" you could link to it as follows:
1387 $worksheet->write_url( 'A14', 'external:c:\temp\foo.xlsx#my_name' );
1389 Excel requires that worksheet names containing spaces or non
1391 alphanumeric characters are single quoted as follows "'Sales Data'!A1".
1392 If you need to do this in a single quoted string then you can either
1393 escape the single quotes "\'" or use the quote operator "q{}" as
1394 described in "perlop" in the main Perl documentation.
1395 Links to network files are also supported. MS/Novell Network files
1397 normally begin with two back slashes as follows "\\NETWORK\etc". In
1398 order to generate this in a single or double quoted string you will
1399 have to escape the backslashes, '\\\\NETWORK\etc'.
1400 If you are using double quote strings then you should be careful to
1402 escape anything that looks like a metacharacter. For more information
1403 see "perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?".
1404 Finally, you can avoid most of these quoting problems by using forward
1406 slashes. These are translated internally to backslashes:
1407 $worksheet->write_url( 'A14', "external:c:/temp/foo.xlsx" );
1409 $worksheet->write_url( 'A15', 'external://NETWORK/share/foo.xlsx' );
1410 Note: Excel::Writer::XLSX will escape the following characters in URLs
1412 as required by Excel: "\s " < > \ [ ] ` ^ { }" unless the URL already
1413 contains %xx style escapes. In which case it is assumed that the URL
1414 was escaped correctly by the user and will by passed directly to Excel.
1415 Versions of Excel prior to Excel 2015 limited hyperlink links and
1417 anchor/locations to 255 characters each. Versions after that support
1418 urls up to 2079 characters. Excel::Writer::XLSX versions >= 1.0.2
1419 support the new longer limit by default.
1420 See also, the note about "Cell notation".
1422 write_formula( $row, $column, $formula, $format, $value )
1424 Write a formula or function to the cell specified by $row and $column:
1425 $worksheet->write_formula( 0, 0, '=$B$3 + B4' );
1427 $worksheet->write_formula( 1, 0, '=SIN(PI()/4)' );
1428 $worksheet->write_formula( 2, 0, '=SUM(B1:B5)' );
1429 $worksheet->write_formula( 'A4', '=IF(A3>1,"Yes", "No")' );
1430 $worksheet->write_formula( 'A5', '=AVERAGE(1, 2, 3, 4)' );
1431 $worksheet->write_formula( 'A6', '=DATEVALUE("1-Jan-2001")' );
1432 Array formulas are also supported:
1434 $worksheet->write_formula( 'A7', '{=SUM(A1:B1*A2:B2)}' );
1436 See also the write_array_formula() method below.
1438 See the note about "Cell notation". For more information about writing
1440 Excel formulas see "FORMULAS AND FUNCTIONS IN EXCEL"
1441 If required, it is also possible to specify the calculated value of the
1443 formula. This is occasionally necessary when working with non-Excel
1444 applications that don't calculate the value of the formula. The
1445 calculated $value is added at the end of the argument list:
1446 $worksheet->write( 'A1', '=2+2', $format, 4 );
1448 However, this probably isn't something that you will ever need to do.
1450 If you do use this feature then do so with care.
1451 write_array_formula($first_row, $first_col, $last_row, $last_col, $formula,
1453 $format, $value)
1454 Write an array formula to a cell range. In Excel an array formula is a
1455 formula that performs a calculation on a set of values. It can return a
1456 single value or a range of values.
1457 An array formula is indicated by a pair of braces around the formula:
1459 "{=SUM(A1:B1*A2:B2)}". If the array formula returns a single value
1460 then the $first_ and $last_ parameters should be the same:
1461 $worksheet->write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}');
1463 It this case however it is easier to just use the write_formula() or
1465 write() methods:
1466 # Same as above but more concise.
1468 $worksheet->write( 'A1', '{=SUM(B1:C1*B2:C2)}' );
1469 $worksheet->write_formula( 'A1', '{=SUM(B1:C1*B2:C2)}' );
1470 For array formulas that return a range of values you must specify the
1472 range that the return values will be written to:
1473 $worksheet->write_array_formula( 'A1:A3', '{=TREND(C1:C3,B1:B3)}' );
1475 $worksheet->write_array_formula( 0, 0, 2, 0, '{=TREND(C1:C3,B1:B3)}' );
1476 If required, it is also possible to specify the calculated value of the
1478 formula. This is occasionally necessary when working with non-Excel
1479 applications that don't calculate the value of the formula. However,
1480 using this parameter only writes a single value to the upper left cell
1481 in the result array. For a multi-cell array formula where the results
1482 are required, the other result values can be specified by using
1483 write_number() to write to the appropriate cell:
1484 # Specify the result for a single cell range.
1486 $worksheet->write_array_formula( 'A1:A3', '{=SUM(B1:C1*B2:C2)}, $format, 2005 );
1487 # Specify the results for a multi cell range.
1489 $worksheet->write_array_formula( 'A1:A3', '{=TREND(C1:C3,B1:B3)}', $format, 105 );
1490 $worksheet->write_number( 'A2', 12, format );
1491 $worksheet->write_number( 'A3', 14, format );
1492 In addition, some early versions of Excel 2007 don't calculate the
1494 values of array formulas when they aren't supplied. Installing the
1495 latest Office Service Pack should fix this issue.
1496 See also the "array_formula.pl" program in the "examples" directory of
1498 the distro.
1499 Note: Array formulas are not supported by Spreadsheet::WriteExcel.
1501 write_boolean( $row, $column, $value, $format )
1503 Write an Excel boolean value to the cell specified by $row and $column:
1504 $worksheet->write_boolean( 'A1', 1 ); # TRUE
1506 $worksheet->write_boolean( 'A2', 0 ); # FALSE
1507 $worksheet->write_boolean( 'A3', undef ); # FALSE
1508 $worksheet->write_boolean( 'A3', 0, $format ); # FALSE, with format.
1509 A $value that is true or false using Perl's rules will be written as an
1511 Excel boolean "TRUE" or "FALSE" value.
1512 See the note about "Cell notation".
1514 store_formula( $formula )
1516 Deprecated. This is a Spreadsheet::WriteExcel method that is no longer
1517 required by Excel::Writer::XLSX. See below.
1518 repeat_formula( $row, $col, $formula, $format )
1520 Deprecated. This is a Spreadsheet::WriteExcel method that is no longer
1521 required by Excel::Writer::XLSX.
1522 In Spreadsheet::WriteExcel it was computationally expensive to write
1524 formulas since they were parsed by a recursive descent parser. The
1525 store_formula() and repeat_formula() methods were used as a way of
1526 avoiding the overhead of repeated formulas by reusing a pre-parsed
1527 formula.
1528 In Excel::Writer::XLSX this is no longer necessary since it is just as
1530 quick to write a formula as it is to write a string or a number.
1531 The methods remain for backward compatibility but new
1533 Excel::Writer::XLSX programs shouldn't use them.
1534 write_comment( $row, $column, $string, ... )
1536 The write_comment() method is used to add a comment to a cell. A cell
1537 comment is indicated in Excel by a small red triangle in the upper
1538 right-hand corner of the cell. Moving the cursor over the red triangle
1539 will reveal the comment.
1540 The following example shows how to add a comment to a cell:
1542 $worksheet->write ( 2, 2, 'Hello' );
1544 $worksheet->write_comment( 2, 2, 'This is a comment.' );
1545 As usual you can replace the $row and $column parameters with an "A1"
1547 cell reference. See the note about "Cell notation".
1548 $worksheet->write ( 'C3', 'Hello');
1550 $worksheet->write_comment( 'C3', 'This is a comment.' );
1551 The write_comment() method will also handle strings in "UTF-8" format.
1553 $worksheet->write_comment( 'C3', "\x{263a}" ); # Smiley
1555 $worksheet->write_comment( 'C4', 'Comment ca va?' );
1556 In addition to the basic 3 argument form of write_comment() you can
1558 pass in several optional key/value pairs to control the format of the
1559 comment. For example:
1560 $worksheet->write_comment( 'C3', 'Hello', visible => 1, author => 'Perl' );
1562 Most of these options are quite specific and in general the default
1564 comment behaves will be all that you need. However, should you need
1565 greater control over the format of the cell comment the following
1566 options are available:
1567 author
1569 visible
1570 x_scale
1571 width
1572 y_scale
1573 height
1574 color
1575 start_cell
1576 start_row
1577 start_col
1578 x_offset
1579 y_offset
1580 font
1581 font_size
1582 Option: author
1584 This option is used to indicate who is the author of the cell
1585 comment. Excel displays the author of the comment in the status bar
1586 at the bottom of the worksheet. This is usually of interest in
1587 corporate environments where several people might review and
1588 provide comments to a workbook.
1589 $worksheet->write_comment( 'C3', 'Atonement', author => 'Ian McEwan' );
1591 The default author for all cell comments can be set using the
1593 set_comments_author() method (see below).
1594 $worksheet->set_comments_author( 'Perl' );
1596 Option: visible
1598 This option is used to make a cell comment visible when the
1599 worksheet is opened. The default behaviour in Excel is that
1600 comments are initially hidden. However, it is also possible in
1601 Excel to make individual or all comments visible. In
1602 Excel::Writer::XLSX individual comments can be made visible as
1603 follows:
1604 $worksheet->write_comment( 'C3', 'Hello', visible => 1 );
1606 It is possible to make all comments in a worksheet visible using
1608 the show_comments() worksheet method (see below). Alternatively, if
1609 all of the cell comments have been made visible you can hide
1610 individual comments:
1611 $worksheet->write_comment( 'C3', 'Hello', visible => 0 );
1613 Option: x_scale
1615 This option is used to set the width of the cell comment box as a
1616 factor of the default width.
1617 $worksheet->write_comment( 'C3', 'Hello', x_scale => 2 );
1619 $worksheet->write_comment( 'C4', 'Hello', x_scale => 4.2 );
1620 Option: width
1622 This option is used to set the width of the cell comment box
1623 explicitly in pixels.
1624 $worksheet->write_comment( 'C3', 'Hello', width => 200 );
1626 Option: y_scale
1628 This option is used to set the height of the cell comment box as a
1629 factor of the default height.
1630 $worksheet->write_comment( 'C3', 'Hello', y_scale => 2 );
1632 $worksheet->write_comment( 'C4', 'Hello', y_scale => 4.2 );
1633 Option: height
1635 This option is used to set the height of the cell comment box
1636 explicitly in pixels.
1637 $worksheet->write_comment( 'C3', 'Hello', height => 200 );
1639 Option: color
1641 This option is used to set the background colour of cell comment
1642 box. You can use one of the named colours recognised by
1643 Excel::Writer::XLSX or a Html style "#RRGGBB" colour. See "WORKING
1644 WITH COLOURS".
1645 $worksheet->write_comment( 'C3', 'Hello', color => 'green' );
1647 $worksheet->write_comment( 'C4', 'Hello', color => '#FF6600' ); # Orange
1648 Option: start_cell
1650 This option is used to set the cell in which the comment will
1651 appear. By default Excel displays comments one cell to the right
1652 and one cell above the cell to which the comment relates. However,
1653 you can change this behaviour if you wish. In the following example
1654 the comment which would appear by default in cell "D2" is moved to
1655 "E2".
1656 $worksheet->write_comment( 'C3', 'Hello', start_cell => 'E2' );
1658 Option: start_row
1660 This option is used to set the row in which the comment will
1661 appear. See the "start_cell" option above. The row is zero indexed.
1662 $worksheet->write_comment( 'C3', 'Hello', start_row => 0 );
1664 Option: start_col
1666 This option is used to set the column in which the comment will
1667 appear. See the "start_cell" option above. The column is zero
1668 indexed.
1669 $worksheet->write_comment( 'C3', 'Hello', start_col => 4 );
1671 Option: x_offset
1673 This option is used to change the x offset, in pixels, of a comment
1674 within a cell:
1675 $worksheet->write_comment( 'C3', $comment, x_offset => 30 );
1677 Option: y_offset
1679 This option is used to change the y offset, in pixels, of a comment
1680 within a cell:
1681 $worksheet->write_comment('C3', $comment, x_offset => 30);
1683 Option: font
1685 This option is used to change the font used in the comment from
1686 'Tahoma' which is the default.
1687 $worksheet->write_comment('C3', $comment, font => 'Calibri');
1689 Option: font_size
1691 This option is used to change the font size used in the comment
1692 from 8 which is the default.
1693 $worksheet->write_comment('C3', $comment, font_size => 20);
1695 You can apply as many of these options as you require.
1697 Note about using options that adjust the position of the cell comment
1699 such as start_cell, start_row, start_col, x_offset and y_offset: Excel
1700 only displays offset cell comments when they are displayed as
1701 "visible". Excel does not display hidden cells as moved when you mouse
1702 over them.
1703 Note about row height and comments. If you specify the height of a row
1705 that contains a comment then Excel::Writer::XLSX will adjust the height
1706 of the comment to maintain the default or user specified dimensions.
1707 However, the height of a row can also be adjusted automatically by
1708 Excel if the text wrap property is set or large fonts are used in the
1709 cell. This means that the height of the row is unknown to the module at
1710 run time and thus the comment box is stretched with the row. Use the
1711 set_row() method to specify the row height explicitly and avoid this
1712 problem.
1713 show_comments()
1715 This method is used to make all cell comments visible when a worksheet
1716 is opened.
1717 $worksheet->show_comments();
1719 Individual comments can be made visible using the "visible" parameter
1721 of the "write_comment" method (see above):
1722 $worksheet->write_comment( 'C3', 'Hello', visible => 1 );
1724 If all of the cell comments have been made visible you can hide
1726 individual comments as follows:
1727 $worksheet->show_comments();
1729 $worksheet->write_comment( 'C3', 'Hello', visible => 0 );
1730 set_comments_author()
1732 This method is used to set the default author of all cell comments.
1733 $worksheet->set_comments_author( 'Perl' );
1735 Individual comment authors can be set using the "author" parameter of
1737 the "write_comment" method (see above).
1738 The default comment author is an empty string, '', if no author is
1740 specified.
1741 add_write_handler( $re, $code_ref )
1743 This method is used to extend the Excel::Writer::XLSX write() method to
1744 handle user defined data.
1745 If you refer to the section on write() above you will see that it acts
1747 as an alias for several more specific "write_*" methods. However, it
1748 doesn't always act in exactly the way that you would like it to.
1749 One solution is to filter the input data yourself and call the
1751 appropriate "write_*" method. Another approach is to use the
1752 add_write_handler() method to add your own automated behaviour to
1753 write().
1754 The add_write_handler() method take two arguments, $re, a regular
1756 expression to match incoming data and $code_ref a callback function to
1757 handle the matched data:
1758 $worksheet->add_write_handler( qr/^\d\d\d\d$/, \&my_write );
1760 (In the these examples the "qr" operator is used to quote the regular
1762 expression strings, see perlop for more details).
1763 The method is used as follows. say you wished to write 7 digit ID
1765 numbers as a string so that any leading zeros were preserved*, you
1766 could do something like the following:
1767 $worksheet->add_write_handler( qr/^\d{7}$/, \&write_my_id );
1769 sub write_my_id {
1772 my $worksheet = shift;
1773 return $worksheet->write_string( @_ );
1774 }
1775 * You could also use the keep_leading_zeros() method for this.
1777 Then if you call write() with an appropriate string it will be handled
1779 automatically:
1780 # Writes 0000000. It would normally be written as a number; 0.
1782 $worksheet->write( 'A1', '0000000' );
1783 The callback function will receive a reference to the calling worksheet
1785 and all of the other arguments that were passed to write(). The
1786 callback will see an @_ argument list that looks like the following:
1787 $_[0] A ref to the calling worksheet. *
1789 $_[1] Zero based row number.
1790 $_[2] Zero based column number.
1791 $_[3] A number or string or token.
1792 $_[4] A format ref if any.
1793 $_[5] Any other arguments.
1794 ...
1795 * It is good style to shift this off the list so the @_ is the same
1797 as the argument list seen by write().
1798 Your callback should return() the return value of the "write_*" method
1800 that was called or "undef" to indicate that you rejected the match and
1801 want write() to continue as normal.
1802 So for example if you wished to apply the previous filter only to ID
1804 values that occur in the first column you could modify your callback
1805 function as follows:
1806 sub write_my_id {
1808 my $worksheet = shift;
1809 my $col = $_[1];
1810 if ( $col == 0 ) {
1812 return $worksheet->write_string( @_ );
1813 }
1814 else {
1815 # Reject the match and return control to write()
1816 return undef;
1817 }
1818 }
1819 Now, you will get different behaviour for the first column and other
1821 columns:
1822 $worksheet->write( 'A1', '0000000' ); # Writes 0000000
1824 $worksheet->write( 'B1', '0000000' ); # Writes 0
1825 You may add more than one handler in which case they will be called in
1827 the order that they were added.
1828 Note, the add_write_handler() method is particularly suited for
1830 handling dates.
1831 See the "write_handler 1-4" programs in the "examples" directory for
1833 further examples.
1834 insert_image( $row, $col, $filename, { %options } )
1836 This method can be used to insert a image into a worksheet. The image
1837 can be in PNG, JPEG, GIF or BMP format.
1838 $worksheet1->insert_image( 'A1', 'perl.bmp' );
1840 $worksheet2->insert_image( 'A1', '../images/perl.bmp' );
1841 $worksheet3->insert_image( 'A1', '.c:\images\perl.bmp' );
1842 The optional "options" hash/hashref parameter can be used to set
1844 various options for the image. The defaults are:
1845 %options = (
1847 x_offset => 0,
1848 y_offset => 0,
1849 x_scale => 1,
1850 y_scale => 1,
1851 object_position => 2,
1852 url => undef,
1853 tip => undef,
1854 description => $filename,
1855 decorative => 0,
1856 );
1857 The parameters "x_offset" and "y_offset" can be used to specify an
1859 offset from the top left hand corner of the cell specified by $row and
1860 $col. The offset values are in pixels.
1861 $worksheet1->insert_image('A1', 'perl.bmp', { x_offset =>32, y_offset => 10 });
1863 The offsets can be greater than the width or height of the underlying
1865 cell. This can be occasionally useful if you wish to align two or more
1866 images relative to the same cell.
1867 The parameters "x_scale" and "y_scale" can be used to scale the
1869 inserted image horizontally and vertically:
1870 # Scale the inserted image: width x 2.0, height x 0.8
1872 $worksheet->insert_image( 'A1', 'perl.bmp', { y_scale => 2, y_scale => 0.8 } );
1873 The positioning of the image when cells are resized can be set with the
1875 "object_position" parameter:
1876 $worksheet->insert_image( 'A1', 'perl.bmp', { object_position => 1 } );
1878 The "object_position" parameter can have one of the following allowable
1880 values:
1881 1. Move and size with cells.
1883 2. Move but don't size with cells.
1884 3. Don't move or size with cells.
1885 4. Same as Option 1, see below.
1886 Option 4 appears in Excel as Option 1. However, the worksheet object is
1888 sized to take hidden rows or columns into account. This allows the user
1889 to hide an image in a cell, possibly as part of an autofilter.
1890 The "url" option can be use to used to add a hyperlink to an image:
1892 $worksheet->insert_image( 'A1', 'logo.png',
1894 { url => 'https://github.com/jmcnamara' } );
1895 The supported url formats are the same as those supported by the
1897 write_url() method and the same rules/limits apply.
1898 The "tip" option can be use to used to add a mouseover tip to the
1900 hyperlink:
1901 $worksheet->insert_image( 'A1', 'logo.png',
1903 {
1904 url => 'https://github.com/jmcnamara',
1905 tip => 'GitHub'
1906 }
1907 );
1908 The "description" parameter can be used to specify a description or
1910 "alt text" string for the image. In general this would be used to
1911 provide a text description of the image to help accessibility. It is an
1912 optional parameter and defaults to the filename of the image. It can be
1913 used as follows:
1914 $worksheet->insert_image( 'E9', 'logo.png',
1916 {description => "This is some alternative text"} );
1917 The optional "decorative" parameter is also used to help accessibility.
1919 It is used to mark the image as decorative, and thus uninformative, for
1920 automated screen readers. As in Excel, if this parameter is in use the
1921 "description" field isn't written. It is used as follows:
1922 $worksheet->insert_image( 'E9', 'logo.png', {decorative => 1} );
1924 Note: you must call set_row() or set_column() before insert_image() if
1926 you wish to change the default dimensions of any of the rows or columns
1927 that the image occupies. The height of a row can also change if you use
1928 a font that is larger than the default. This in turn will affect the
1929 scaling of your image. To avoid this you should explicitly set the
1930 height of the row using set_row() if it contains a font size that will
1931 change the row height.
1932 BMP images must be 24 bit, true colour, bitmaps. In general it is best
1934 to avoid BMP images since they aren't compressed.
1935 insert_chart( $row, $col, $chart, { %options } )
1937 This method can be used to insert a Chart object into a worksheet. The
1938 Chart must be created by the add_chart() Workbook method and it must
1939 have the "embedded" option set.
1940 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
1942 # Configure the chart.
1944 ...
1945 # Insert the chart into the a worksheet.
1947 $worksheet->insert_chart( 'E2', $chart );
1948 See add_chart() for details on how to create the Chart object and
1950 Excel::Writer::XLSX::Chart for details on how to configure it. See also
1951 the "chart_*.pl" programs in the examples directory of the distro.
1952 The optional "options" hash/hashref parameter can be used to set
1954 various options for the chart. The defaults are:
1955 %options = (
1957 x_offset => 0,
1958 y_offset => 0,
1959 x_scale => 1,
1960 y_scale => 1,
1961 object_position => 1,
1962 description => undef,
1963 decorative => 0,
1964 );
1965 The parameters "x_offset" and "y_offset" can be used to specify an
1967 offset from the top left hand corner of the cell specified by $row and
1968 $col. The offset values are in pixels.
1969 $worksheet1->insert_chart( 'E2', $chart, { x_offset =>10, y_offset => 20 });
1971 The parameters "x_scale" and "y_scale" can be used to scale the
1973 inserted chart horizontally and vertically:
1974 # Scale the width by 120% and the height by 150%
1976 $worksheet->insert_chart( 'E2', $chart, { y_scale => 1.2, y_scale => 1.5 } );
1977 The positioning of the chart when cells are resized can be set with the
1979 "object_position" parameter:
1980 $worksheet->insert_chart( 'E2', $chart, { object_position => 2 } );
1982 The "object_position" parameter can have one of the following allowable
1984 values:
1985 1. Move and size with cells.
1987 2. Move but don't size with cells.
1988 3. Don't move or size with cells.
1989 4. Same as Option 1, see below.
1990 Option 4 appears in Excel as Option 1. However, the worksheet object is
1992 sized to take hidden rows or columns into account. This is generally
1993 only useful for images and not for charts.
1994 The "description" parameter can be used to specify a description or
1996 "alt text" string for the chart. In general this would be used to
1997 provide a text description of the chart to help accessibility. It is an
1998 optional parameter and has no default. It can be used as follows:
1999 $worksheet->insert_chart( 'E9', $chart, {description => 'Some alternative text'} );
2001 The optional "decorative" parameter is also used to help accessibility.
2003 It is used to mark the chart as decorative, and thus uninformative, for
2004 automated screen readers. As in Excel, if this parameter is in use the
2005 "description" field isn't written. It is used as follows:
2006 $worksheet->insert_chart( 'E9', $chart, {decorative => 1} );
2008 insert_shape( $row, $col, $shape, $x, $y, $x_scale, $y_scale )
2010 This method can be used to insert a Shape object into a worksheet. The
2011 Shape must be created by the add_shape() Workbook method.
2012 my $shape = $workbook->add_shape( name => 'My Shape', type => 'plus' );
2014 # Configure the shape.
2016 $shape->set_text('foo');
2017 ...
2018 # Insert the shape into the a worksheet.
2020 $worksheet->insert_shape( 'E2', $shape );
2021 See add_shape() for details on how to create the Shape object and
2023 Excel::Writer::XLSX::Shape for details on how to configure it.
2024 The $x, $y, $x_scale and $y_scale parameters are optional.
2026 The parameters $x and $y can be used to specify an offset from the top
2028 left hand corner of the cell specified by $row and $col. The offset
2029 values are in pixels.
2030 $worksheet1->insert_shape( 'E2', $chart, 3, 3 );
2032 The parameters $x_scale and $y_scale can be used to scale the inserted
2034 shape horizontally and vertically:
2035 # Scale the width by 120% and the height by 150%
2037 $worksheet->insert_shape( 'E2', $shape, 0, 0, 1.2, 1.5 );
2038 See also the "shape*.pl" programs in the examples directory of the
2040 distro.
2041 insert_button( $row, $col, { %options })
2043 The insert_button() method can be used to insert an Excel form button
2044 into a worksheet.
2045 This method is generally only useful when used in conjunction with the
2047 Workbook add_vba_project() method to tie the button to a macro from an
2048 embedded VBA project:
2049 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
2051 ...
2052 $workbook->add_vba_project( './vbaProject.bin' );
2053 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
2055 The properties of the button that can be set are:
2057 macro
2059 caption
2060 width
2061 height
2062 x_scale
2063 y_scale
2064 x_offset
2065 y_offset
2066 description
2067 Option: macro
2069 This option is used to set the macro that the button will invoke
2070 when the user clicks on it. The macro should be included using the
2071 Workbook add_vba_project() method shown above.
2072 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
2074 The default macro is "ButtonX_Click" where X is the button number.
2076 Option: caption
2078 This option is used to set the caption on the button. The default
2079 is "Button X" where X is the button number.
2080 $worksheet->insert_button( 'C2', { macro => 'my_macro', caption => 'Hello' } );
2082 Option: width
2084 This option is used to set the width of the button in pixels.
2085 $worksheet->insert_button( 'C2', { macro => 'my_macro', width => 128 } );
2087 The default button width is 64 pixels which is the width of a
2089 default cell.
2090 Option: height
2092 This option is used to set the height of the button in pixels.
2093 $worksheet->insert_button( 'C2', { macro => 'my_macro', height => 40 } );
2095 The default button height is 20 pixels which is the height of a
2097 default cell.
2098 Option: x_scale
2100 This option is used to set the width of the button as a factor of
2101 the default width.
2102 $worksheet->insert_button( 'C2', { macro => 'my_macro', x_scale => 2.0 );
2104 Option: y_scale
2106 This option is used to set the height of the button as a factor of
2107 the default height.
2108 $worksheet->insert_button( 'C2', { macro => 'my_macro', y_scale => 2.0 );
2110 Option: x_offset
2112 This option is used to change the x offset, in pixels, of a button
2113 within a cell:
2114 $worksheet->insert_button( 'C2', { macro => 'my_macro', x_offset => 2 );
2116 Option: y_offset
2118 This option is used to change the y offset, in pixels, of a comment
2119 within a cell.
2120 Option: description
2122 The option is used to specify a description or "alt text" string
2123 for the button.
2124 Note: Button is the only Excel form element that is available in
2126 Excel::Writer::XLSX. Form elements represent a lot of work to implement
2127 and the underlying VML syntax isn't very much fun.
2128 data_validation()
2130 The data_validation() method is used to construct an Excel data
2131 validation or to limit the user input to a dropdown list of values.
2132 $worksheet->data_validation('B3',
2134 {
2135 validate => 'integer',
2136 criteria => '>',
2137 value => 100,
2138 });
2139 $worksheet->data_validation('B5:B9',
2141 {
2142 validate => 'list',
2143 value => ['open', 'high', 'close'],
2144 });
2145 This method contains a lot of parameters and is described in detail in
2147 a separate section "DATA VALIDATION IN EXCEL".
2148 See also the "data_validate.pl" program in the examples directory of
2150 the distro
2151 conditional_formatting()
2153 The conditional_formatting() method is used to add formatting to a cell
2154 or range of cells based on user defined criteria.
2155 $worksheet->conditional_formatting( 'A1:J10',
2157 {
2158 type => 'cell',
2159 criteria => '>=',
2160 value => 50,
2161 format => $format1,
2162 }
2163 );
2164 This method contains a lot of parameters and is described in detail in
2166 a separate section "CONDITIONAL FORMATTING IN EXCEL".
2167 See also the "conditional_format.pl" program in the examples directory
2169 of the distro
2170 add_sparkline()
2172 The add_sparkline() worksheet method is used to add sparklines to a
2173 cell or a range of cells.
2174 $worksheet->add_sparkline(
2176 {
2177 location => 'F2',
2178 range => 'Sheet1!A2:E2',
2179 type => 'column',
2180 style => 12,
2181 }
2182 );
2183 This method contains a lot of parameters and is described in detail in
2185 a separate section "SPARKLINES IN EXCEL".
2186 See also the "sparklines1.pl" and "sparklines2.pl" example programs in
2188 the "examples" directory of the distro.
2189 Note: Sparklines are a feature of Excel 2010+ only. You can write them
2191 to an XLSX file that can be read by Excel 2007 but they won't be
2192 displayed.
2193 add_table()
2195 The add_table() method is used to group a range of cells into an Excel
2196 Table.
2197 $worksheet->add_table( 'B3:F7', { ... } );
2199 This method contains a lot of parameters and is described in detail in
2201 a separate section "TABLES IN EXCEL".
2202 See also the "tables.pl" program in the examples directory of the
2204 distro
2205 get_name()
2207 The get_name() method is used to retrieve the name of a worksheet. For
2208 example:
2209 for my $sheet ( $workbook->sheets() ) {
2211 print $sheet->get_name();
2212 }
2213 For reasons related to the design of Excel::Writer::XLSX and to the
2215 internals of Excel there is no set_name() method. The only way to set
2216 the worksheet name is via the add_worksheet() method.
2217 activate()
2219 The activate() method is used to specify which worksheet is initially
2220 visible in a multi-sheet workbook:
2221 $worksheet1 = $workbook->add_worksheet( 'To' );
2223 $worksheet2 = $workbook->add_worksheet( 'the' );
2224 $worksheet3 = $workbook->add_worksheet( 'wind' );
2225 $worksheet3->activate();
2227 This is similar to the Excel VBA activate method. More than one
2229 worksheet can be selected via the select() method, see below, however
2230 only one worksheet can be active.
2231 The default active worksheet is the first worksheet.
2233 select()
2235 The select() method is used to indicate that a worksheet is selected in
2236 a multi-sheet workbook:
2237 $worksheet1->activate();
2239 $worksheet2->select();
2240 $worksheet3->select();
2241 A selected worksheet has its tab highlighted. Selecting worksheets is a
2243 way of grouping them together so that, for example, several worksheets
2244 could be printed in one go. A worksheet that has been activated via the
2245 activate() method will also appear as selected.
2246 hide()
2248 The hide() method is used to hide a worksheet:
2249 $worksheet2->hide();
2251 You may wish to hide a worksheet in order to avoid confusing a user
2253 with intermediate data or calculations.
2254 A hidden worksheet can not be activated or selected so this method is
2256 mutually exclusive with the activate() and select() methods. In
2257 addition, since the first worksheet will default to being the active
2258 worksheet, you cannot hide the first worksheet without activating
2259 another sheet:
2260 $worksheet2->activate();
2262 $worksheet1->hide();
2263 set_first_sheet()
2265 The activate() method determines which worksheet is initially selected.
2266 However, if there are a large number of worksheets the selected
2267 worksheet may not appear on the screen. To avoid this you can select
2268 which is the leftmost visible worksheet using set_first_sheet():
2269 for ( 1 .. 20 ) {
2271 $workbook->add_worksheet;
2272 }
2273 $worksheet21 = $workbook->add_worksheet();
2275 $worksheet22 = $workbook->add_worksheet();
2276 $worksheet21->set_first_sheet();
2278 $worksheet22->activate();
2279 This method is not required very often. The default value is the first
2281 worksheet.
2282 protect( $password, \%options )
2284 The protect() method is used to protect a worksheet from modification:
2285 $worksheet->protect();
2287 The protect() method also has the effect of enabling a cell's "locked"
2289 and "hidden" properties if they have been set. A locked cell cannot be
2290 edited and this property is on by default for all cells. A hidden cell
2291 will display the results of a formula but not the formula itself.
2292 See the "protection.pl" program in the examples directory of the distro
2294 for an illustrative example and the "set_locked" and "set_hidden"
2295 format methods in "CELL FORMATTING".
2296 You can optionally add a password to the worksheet protection:
2298 $worksheet->protect( 'drowssap' );
2300 The password should be an ASCII string. Passing the empty string '' is
2302 the same as turning on protection without a password.
2303 Note, the worksheet level password in Excel provides very weak
2305 protection. It does not encrypt your data and is very easy to
2306 deactivate. Full workbook encryption is not supported by
2307 "Excel::Writer::XLSX" since it requires a completely different file
2308 format and would take several man months to implement.
2309 You can specify which worksheet elements you wish to protect by passing
2311 a hash_ref with any or all of the following keys:
2312 # Default shown.
2314 %options = (
2315 objects => 0,
2316 scenarios => 0,
2317 format_cells => 0,
2318 format_columns => 0,
2319 format_rows => 0,
2320 insert_columns => 0,
2321 insert_rows => 0,
2322 insert_hyperlinks => 0,
2323 delete_columns => 0,
2324 delete_rows => 0,
2325 select_locked_cells => 1,
2326 sort => 0,
2327 autofilter => 0,
2328 pivot_tables => 0,
2329 select_unlocked_cells => 1,
2330 );
2331 The default boolean values are shown above. Individual elements can be
2333 protected as follows:
2334 $worksheet->protect( 'drowssap', { insert_rows => 1 } );
2336 For chartsheets the allowable options and default values are:
2338 %options = (
2340 objects => 1,
2341 content => 1,
2342 );
2343 unprotect_range( $cell_range, $range_name )
2345 The unprotect_range() method is used to unprotect ranges in a protected
2346 worksheet. It can be used to set a single range or multiple ranges:
2347 $worksheet->unprotect_range( 'A1' );
2349 $worksheet->unprotect_range( 'C1' );
2350 $worksheet->unprotect_range( 'E1:E3' );
2351 $worksheet->unprotect_range( 'G1:K100' );
2352 As in Excel the ranges are given sequential names like "Range1" and
2354 "Range2" but a user defined name can also be specified:
2355 $worksheet->unprotect_range( 'G4:I6', 'MyRange' );
2357 set_selection( $first_row, $first_col, $last_row, $last_col )
2359 This method can be used to specify which cell or cells are selected in
2360 a worksheet. The most common requirement is to select a single cell, in
2361 which case $last_row and $last_col can be omitted. The active cell
2362 within a selected range is determined by the order in which $first and
2363 $last are specified. It is also possible to specify a cell or a range
2364 using A1 notation. See the note about "Cell notation".
2365 Examples:
2367 $worksheet1->set_selection( 3, 3 ); # 1. Cell D4.
2369 $worksheet2->set_selection( 3, 3, 6, 6 ); # 2. Cells D4 to G7.
2370 $worksheet3->set_selection( 6, 6, 3, 3 ); # 3. Cells G7 to D4.
2371 $worksheet4->set_selection( 'D4' ); # Same as 1.
2372 $worksheet5->set_selection( 'D4:G7' ); # Same as 2.
2373 $worksheet6->set_selection( 'G7:D4' ); # Same as 3.
2374 The default cell selections is (0, 0), 'A1'.
2376 set_top_left_cell( $row, $col )
2378 This method can be used to set the top leftmost visible cell in the
2379 worksheet:
2380 $worksheet->set_top_left_cell( 31, 26 );
2382 # Same as:
2384 $worksheet->set_top_left_cell( 'AA32' );
2385 You can also use A1 notation, as shown above, see the note about "Cell
2387 notation".
2388 set_row( $row, $height, $format, $hidden, $level, $collapsed )
2390 This method can be used to change the default properties of a row. All
2391 parameters apart from $row are optional.
2392 The most common use for this method is to change the height of a row.
2394 $worksheet->set_row( 0, 20 ); # Row 1 height set to 20
2396 Note: the row height is in Excel character units. To set the height in
2398 pixels use the set_row_pixels() method, see below.
2399 If you wish to set the format without changing the height you can pass
2401 "undef" as the height parameter:
2402 $worksheet->set_row( 0, undef, $format );
2404 The $format parameter will be applied to any cells in the row that
2406 don't have a format. For example
2407 $worksheet->set_row( 0, undef, $format1 ); # Set the format for row 1
2409 $worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
2410 $worksheet->write( 'B1', 'Hello', $format2 ); # Keeps $format2
2411 If you wish to define a row format in this way you should call the
2413 method before any calls to write(). Calling it afterwards will
2414 overwrite any format that was previously specified.
2415 The $hidden parameter should be set to 1 if you wish to hide a row.
2417 This can be used, for example, to hide intermediary steps in a
2418 complicated calculation:
2419 $worksheet->set_row( 0, 20, $format, 1 );
2421 $worksheet->set_row( 1, undef, undef, 1 );
2422 The $level parameter is used to set the outline level of the row.
2424 Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent
2425 rows with the same outline level are grouped together into a single
2426 outline.
2427 The following example sets an outline level of 1 for rows 2 and 3
2429 (zero-indexed):
2430 $worksheet->set_row( 1, undef, undef, 0, 1 );
2432 $worksheet->set_row( 2, undef, undef, 0, 1 );
2433 The $hidden parameter can also be used to hide collapsed outlined rows
2435 when used in conjunction with the $level parameter.
2436 $worksheet->set_row( 1, undef, undef, 1, 1 );
2438 $worksheet->set_row( 2, undef, undef, 1, 1 );
2439 For collapsed outlines you should also indicate which row has the
2441 collapsed "+" symbol using the optional $collapsed parameter.
2442 $worksheet->set_row( 3, undef, undef, 0, 0, 1 );
2444 For a more complete example see the "outline.pl" and
2446 "outline_collapsed.pl" programs in the examples directory of the
2447 distro.
2448 Excel allows up to 7 outline levels. Therefore the $level parameter
2450 should be in the range "0 <= $level <= 7".
2451 set_row_pixels( $row, $height, $format, $hidden, $level, $collapsed )
2453 This method is the same as set_row() except that $height is in pixels.
2454 $worksheet->set_row ( 0, 24 ); # Set row height in character units
2456 $worksheet->set_row_pixels( 1, 18 ); # Set row to same height in pixels
2457 set_column( $first_col, $last_col, $width, $format, $hidden, $level,
2459 $collapsed )
2460 This method can be used to change the default properties of a single
2461 column or a range of columns. All parameters apart from $first_col and
2462 $last_col are optional.
2463 If set_column() is applied to a single column the value of $first_col
2465 and $last_col should be the same. In the case where $last_col is zero
2466 it is set to the same value as $first_col.
2467 It is also possible, and generally clearer, to specify a column range
2469 using the form of A1 notation used for columns. See the note about
2470 "Cell notation".
2471 Examples:
2473 $worksheet->set_column( 0, 0, 20 ); # Column A width set to 20
2475 $worksheet->set_column( 1, 3, 30 ); # Columns B-D width set to 30
2476 $worksheet->set_column( 'E:E', 20 ); # Column E width set to 20
2477 $worksheet->set_column( 'F:H', 30 ); # Columns F-H width set to 30
2478 The width corresponds to the column width value that is specified in
2480 Excel. It is approximately equal to the length of a string in the
2481 default font of Calibri 11. To set the width in pixels use the
2482 set_column_pixels() method, see below.
2483 Unfortunately, there is no way to specify "AutoFit" for a column in the
2485 Excel file format. This feature is only available at runtime from
2486 within Excel.
2487 As usual the $format parameter is optional, for additional information,
2489 see "CELL FORMATTING". If you wish to set the format without changing
2490 the width you can pass "undef" as the width parameter:
2491 $worksheet->set_column( 0, 0, undef, $format );
2493 The $format parameter will be applied to any cells in the column that
2495 don't have a format. For example
2496 $worksheet->set_column( 'A:A', undef, $format1 ); # Set format for col 1
2498 $worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
2499 $worksheet->write( 'A2', 'Hello', $format2 ); # Keeps $format2
2500 If you wish to define a column format in this way you should call the
2502 method before any calls to write(). If you call it afterwards it won't
2503 have any effect.
2504 A default row format takes precedence over a default column format
2506 $worksheet->set_row( 0, undef, $format1 ); # Set format for row 1
2508 $worksheet->set_column( 'A:A', undef, $format2 ); # Set format for col 1
2509 $worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
2510 $worksheet->write( 'A2', 'Hello' ); # Defaults to $format2
2511 The $hidden parameter should be set to 1 if you wish to hide a column.
2513 This can be used, for example, to hide intermediary steps in a
2514 complicated calculation:
2515 $worksheet->set_column( 'D:D', 20, $format, 1 );
2517 $worksheet->set_column( 'E:E', undef, undef, 1 );
2518 The $level parameter is used to set the outline level of the column.
2520 Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent
2521 columns with the same outline level are grouped together into a single
2522 outline.
2523 The following example sets an outline level of 1 for columns B to G:
2525 $worksheet->set_column( 'B:G', undef, undef, 0, 1 );
2527 The $hidden parameter can also be used to hide collapsed outlined
2529 columns when used in conjunction with the $level parameter.
2530 $worksheet->set_column( 'B:G', undef, undef, 1, 1 );
2532 For collapsed outlines you should also indicate which row has the
2534 collapsed "+" symbol using the optional $collapsed parameter.
2535 $worksheet->set_column( 'H:H', undef, undef, 0, 0, 1 );
2537 For a more complete example see the "outline.pl" and
2539 "outline_collapsed.pl" programs in the examples directory of the
2540 distro.
2541 Excel allows up to 7 outline levels. Therefore the $level parameter
2543 should be in the range "0 <= $level <= 7".
2544 set_column_pixels( $first_col, $last_col, $width, $format, $hidden, $level,
2546 $collapsed )
2547 This method is the same as set_column() except that $width is in
2548 pixels.
2549 $worksheet->set_column( 0, 0, 10 ); # Column A width set to 20 in character units
2551 $worksheet->set_column( 1, 1, 75 ); # Column B set to the same width in pixels
2552 set_default_row( $height, $hide_unused_rows )
2554 The set_default_row() method is used to set the limited number of
2555 default row properties allowed by Excel. These are the default height
2556 and the option to hide unused rows.
2557 $worksheet->set_default_row( 24 ); # Set the default row height to 24.
2559 The option to hide unused rows is used by Excel as an optimisation so
2561 that the user can hide a large number of rows without generating a very
2562 large file with an entry for each hidden row.
2563 $worksheet->set_default_row( undef, 1 );
2565 See the "hide_row_col.pl" example program.
2567 outline_settings( $visible, $symbols_below, $symbols_right, $auto_style )
2569 The outline_settings() method is used to control the appearance of
2570 outlines in Excel. Outlines are described in "OUTLINES AND GROUPING IN
2571 EXCEL".
2572 The $visible parameter is used to control whether or not outlines are
2574 visible. Setting this parameter to 0 will cause all outlines on the
2575 worksheet to be hidden. They can be unhidden in Excel by means of the
2576 "Show Outline Symbols" command button. The default setting is 1 for
2577 visible outlines.
2578 $worksheet->outline_settings( 0 );
2580 The $symbols_below parameter is used to control whether the row outline
2582 symbol will appear above or below the outline level bar. The default
2583 setting is 1 for symbols to appear below the outline level bar.
2584 The $symbols_right parameter is used to control whether the column
2586 outline symbol will appear to the left or the right of the outline
2587 level bar. The default setting is 1 for symbols to appear to the right
2588 of the outline level bar.
2589 The $auto_style parameter is used to control whether the automatic
2591 outline generator in Excel uses automatic styles when creating an
2592 outline. This has no effect on a file generated by
2593 "Excel::Writer::XLSX" but it does have an effect on how the worksheet
2594 behaves after it is created. The default setting is 0 for "Automatic
2595 Styles" to be turned off.
2596 The default settings for all of these parameters correspond to Excel's
2598 default parameters.
2599 The worksheet parameters controlled by outline_settings() are rarely
2601 used.
2602 freeze_panes( $row, $col, $top_row, $left_col )
2604 This method can be used to divide a worksheet into horizontal or
2605 vertical regions known as panes and to also "freeze" these panes so
2606 that the splitter bars are not visible. This is the same as the
2607 "Window->Freeze Panes" menu command in Excel
2608 The parameters $row and $col are used to specify the location of the
2610 split. It should be noted that the split is specified at the top or
2611 left of a cell and that the method uses zero based indexing. Therefore
2612 to freeze the first row of a worksheet it is necessary to specify the
2613 split at row 2 (which is 1 as the zero-based index). This might lead
2614 you to think that you are using a 1 based index but this is not the
2615 case.
2616 You can set one of the $row and $col parameters as zero if you do not
2618 want either a vertical or horizontal split.
2619 Examples:
2621 $worksheet->freeze_panes( 1, 0 ); # Freeze the first row
2623 $worksheet->freeze_panes( 'A2' ); # Same using A1 notation
2624 $worksheet->freeze_panes( 0, 1 ); # Freeze the first column
2625 $worksheet->freeze_panes( 'B1' ); # Same using A1 notation
2626 $worksheet->freeze_panes( 1, 2 ); # Freeze first row and first 2 columns
2627 $worksheet->freeze_panes( 'C2' ); # Same using A1 notation
2628 The parameters $top_row and $left_col are optional. They are used to
2630 specify the top-most or left-most visible row or column in the
2631 scrolling region of the panes. For example to freeze the first row and
2632 to have the scrolling region begin at row twenty:
2633 $worksheet->freeze_panes( 1, 0, 20, 0 );
2635 You cannot use A1 notation for the $top_row and $left_col parameters.
2637 See also the "panes.pl" program in the "examples" directory of the
2639 distribution.
2640 split_panes( $y, $x, $top_row, $left_col )
2642 This method can be used to divide a worksheet into horizontal or
2643 vertical regions known as panes. This method is different from the
2644 freeze_panes() method in that the splits between the panes will be
2645 visible to the user and each pane will have its own scroll bars.
2646 The parameters $y and $x are used to specify the vertical and
2648 horizontal position of the split. The units for $y and $x are the same
2649 as those used by Excel to specify row height and column width. However,
2650 the vertical and horizontal units are different from each other.
2651 Therefore you must specify the $y and $x parameters in terms of the row
2652 heights and column widths that you have set or the default values which
2653 are 15 for a row and 8.43 for a column.
2654 You can set one of the $y and $x parameters as zero if you do not want
2656 either a vertical or horizontal split. The parameters $top_row and
2657 $left_col are optional. They are used to specify the top-most or left-
2658 most visible row or column in the bottom-right pane.
2659 Example:
2661 $worksheet->split_panes( 15, 0, ); # First row
2663 $worksheet->split_panes( 0, 8.43 ); # First column
2664 $worksheet->split_panes( 15, 8.43 ); # First row and column
2665 You cannot use A1 notation with this method.
2667 See also the freeze_panes() method and the "panes.pl" program in the
2669 "examples" directory of the distribution.
2670 merge_range( $first_row, $first_col, $last_row, $last_col, $token, $format
2672 )
2673 The merge_range() method allows you to merge cells that contain other
2674 types of alignment in addition to the merging:
2675 my $format = $workbook->add_format(
2677 border => 6,
2678 valign => 'vcenter',
2679 align => 'center',
2680 );
2681 $worksheet->merge_range( 'B3:D4', 'Vertical and horizontal', $format );
2683 merge_range() writes its $token argument using the worksheet write()
2685 method. Therefore it will handle numbers, strings, formulas or urls as
2686 required. If you need to specify the required "write_*()" method use
2687 the merge_range_type() method, see below.
2688 The full possibilities of this method are shown in the "merge3.pl" to
2690 "merge6.pl" programs in the "examples" directory of the distribution.
2691 merge_range_type( $type, $first_row, $first_col, $last_row, $last_col, ...
2693 )
2694 The merge_range() method, see above, uses write() to insert the
2695 required data into to a merged range. However, there may be times where
2696 this isn't what you require so as an alternative the "merge_range_type
2697 ()" method allows you to specify the type of data you wish to write.
2698 For example:
2699 $worksheet->merge_range_type( 'number', 'B2:C2', 123, $format1 );
2701 $worksheet->merge_range_type( 'string', 'B4:C4', 'foo', $format2 );
2702 $worksheet->merge_range_type( 'formula', 'B6:C6', '=1+2', $format3 );
2703 The $type must be one of the following, which corresponds to a
2705 "write_*()" method:
2706 'number'
2708 'string'
2709 'formula'
2710 'array_formula'
2711 'blank'
2712 'rich_string'
2713 'date_time'
2714 'url'
2715 Any arguments after the range should be whatever the appropriate method
2717 accepts:
2718 $worksheet->merge_range_type( 'rich_string', 'B8:C8',
2720 'This is ', $bold, 'bold', $format4 );
2721 Note, you must always pass a $format object as an argument, even if it
2723 is a default format.
2724 set_zoom( $scale )
2726 Set the worksheet zoom factor in the range "10 <= $scale <= 400":
2727 $worksheet1->set_zoom( 50 );
2729 $worksheet2->set_zoom( 75 );
2730 $worksheet3->set_zoom( 300 );
2731 $worksheet4->set_zoom( 400 );
2732 The default zoom factor is 100. You cannot zoom to "Selection" because
2734 it is calculated by Excel at run-time.
2735 Note, set_zoom() does not affect the scale of the printed page. For
2737 that you should use set_print_scale().
2738 right_to_left()
2740 The right_to_left() method is used to change the default direction of
2741 the worksheet from left-to-right, with the A1 cell in the top left, to
2742 right-to-left, with the A1 cell in the top right.
2743 $worksheet->right_to_left();
2745 This is useful when creating Arabic, Hebrew or other near or far
2747 eastern worksheets that use right-to-left as the default direction.
2748 hide_zero()
2750 The hide_zero() method is used to hide any zero values that appear in
2751 cells.
2752 $worksheet->hide_zero();
2754 In Excel this option is found under Tools->Options->View.
2756 set_background( $filename )
2758 The set_background() method can be used to set the background image for
2759 the worksheet:
2760 $worksheet->set_background( 'logo.png' )
2762 The set_background() method supports all the image formats supported by
2764 insert_image().
2765 Some people use this method to add a watermark background to their
2767 document. However, Microsoft recommends using a header image to set a
2768 watermark <https://support.microsoft.com/en-us/office/add-a-watermark-
2769 in-excel-a372182a-d733-484e-825c-18ddf3edf009>. The choice of method
2770 depends on whether you want the watermark to be visible in normal
2771 viewing mode or just when the file is printed. In Excel::Writer::XLSX
2772 you can get the header watermark effect using set_header():
2773 $worksheet->set_header( '&C&G', undef, { image_center => 'watermark.png' } )
2775 set_tab_color()
2777 The set_tab_color() method is used to change the colour of the
2778 worksheet tab. You can use one of the standard colour names provided by
2779 the Format object or a Html style "#RRGGBB" colour. See "WORKING WITH
2780 COLOURS".
2781 $worksheet1->set_tab_color( 'red' );
2783 $worksheet2->set_tab_color( '#FF6600' );
2784 See the "tab_colors.pl" program in the examples directory of the
2786 distro.
2787 autofilter( $first_row, $first_col, $last_row, $last_col )
2789 This method allows an autofilter to be added to a worksheet. An
2790 autofilter is a way of adding drop down lists to the headers of a 2D
2791 range of worksheet data. This allows users to filter the data based on
2792 simple criteria so that some data is shown and some is hidden.
2793 To add an autofilter to a worksheet:
2795 $worksheet->autofilter( 0, 0, 10, 3 );
2797 $worksheet->autofilter( 'A1:D11' ); # Same as above in A1 notation.
2798 Filter conditions can be applied using the filter_column() or
2800 filter_column_list() method.
2801 See the "autofilter.pl" program in the examples directory of the distro
2803 for a more detailed example.
2804 filter_column( $column, $expression )
2806 The "filter_column" method can be used to filter columns in a
2807 autofilter range based on simple conditions.
2808 NOTE: It isn't sufficient to just specify the filter condition. You
2810 must also hide any rows that don't match the filter condition. Rows are
2811 hidden using the set_row() "visible" parameter. "Excel::Writer::XLSX"
2812 cannot do this automatically since it isn't part of the file format.
2813 See the "autofilter.pl" program in the examples directory of the distro
2814 for an example.
2815 The conditions for the filter are specified using simple expressions:
2817 $worksheet->filter_column( 'A', 'x > 2000' );
2819 $worksheet->filter_column( 'B', 'x > 2000 and x < 5000' );
2820 The $column parameter can either be a zero indexed column number or a
2822 string column name.
2823 The following operators are available:
2825 Operator Synonyms
2827 == = eq =~
2828 != <> ne !=
2829 >
2830 <
2831 >=
2832 <=
2833 and &&
2835 or ||
2836 The operator synonyms are just syntactic sugar to make you more
2838 comfortable using the expressions. It is important to remember that the
2839 expressions will be interpreted by Excel and not by perl.
2840 An expression can comprise a single statement or two statements
2842 separated by the "and" and "or" operators. For example:
2843 'x < 2000'
2845 'x > 2000'
2846 'x == 2000'
2847 'x > 2000 and x < 5000'
2848 'x == 2000 or x == 5000'
2849 Filtering of blank or non-blank data can be achieved by using a value
2851 of "Blanks" or "NonBlanks" in the expression:
2852 'x == Blanks'
2854 'x == NonBlanks'
2855 Excel also allows some simple string matching operations:
2857 'x =~ b*' # begins with b
2859 'x !~ b*' # doesn't begin with b
2860 'x =~ *b' # ends with b
2861 'x !~ *b' # doesn't end with b
2862 'x =~ *b*' # contains b
2863 'x !~ *b*' # doesn't contains b
2864 You can also use "*" to match any character or number and "?" to match
2866 any single character or number. No other regular expression quantifier
2867 is supported by Excel's filters. Excel's regular expression characters
2868 can be escaped using "~".
2869 The placeholder variable "x" in the above examples can be replaced by
2871 any simple string. The actual placeholder name is ignored internally so
2872 the following are all equivalent:
2873 'x < 2000'
2875 'col < 2000'
2876 'Price < 2000'
2877 Also, note that a filter condition can only be applied to a column in a
2879 range specified by the autofilter() Worksheet method.
2880 See the "autofilter.pl" program in the examples directory of the distro
2882 for a more detailed example.
2883 Note Spreadsheet::WriteExcel supports Top 10 style filters. These
2885 aren't currently supported by Excel::Writer::XLSX but may be added
2886 later.
2887 filter_column_list( $column, @matches )
2889 Prior to Excel 2007 it was only possible to have either 1 or 2 filter
2890 conditions such as the ones shown above in the "filter_column" method.
2891 Excel 2007 introduced a new list style filter where it is possible to
2893 specify 1 or more 'or' style criteria. For example if your column
2894 contained data for the first six months the initial data would be
2895 displayed as all selected as shown on the left. Then if you selected
2896 'March', 'April' and 'May' they would be displayed as shown on the
2897 right.
2898 No criteria selected Some criteria selected.
2900 [/] (Select all) [X] (Select all)
2902 [/] January [ ] January
2903 [/] February [ ] February
2904 [/] March [/] March
2905 [/] April [/] April
2906 [/] May [/] May
2907 [/] June [ ] June
2908 The filter_column_list() method can be used to represent these types of
2910 filters:
2911 $worksheet->filter_column_list( 'A', 'March', 'April', 'May' );
2913 The $column parameter can either be a zero indexed column number or a
2915 string column name.
2916 One or more criteria can be selected:
2918 $worksheet->filter_column_list( 0, 'March' );
2920 $worksheet->filter_column_list( 1, 100, 110, 120, 130 );
2921 NOTE: It isn't sufficient to just specify the filter condition. You
2923 must also hide any rows that don't match the filter condition. Rows are
2924 hidden using the set_row() "visible" parameter. "Excel::Writer::XLSX"
2925 cannot do this automatically since it isn't part of the file format.
2926 See the "autofilter.pl" program in the examples directory of the distro
2927 for an example.
2928 convert_date_time( $date_string )
2930 The convert_date_time() method is used internally by the
2931 write_date_time() method to convert date strings to a number that
2932 represents an Excel date and time.
2933 It is exposed as a public method for utility purposes.
2935 The $date_string format is detailed in the write_date_time() method.
2937 set_vba_name()
2939 The Worksheet set_vba_name() method can be used to set the VBA codename
2940 for the worksheet (there is a similar method for the workbook VBA
2941 name). This is sometimes required when a "vbaProject" macro included
2942 via add_vba_project() refers to the worksheet. The default Excel VBA
2943 name of "Sheet1", etc., is used if a user defined name isn't specified.
2944 See also "WORKING WITH VBA MACROS".
2946 ignore_errors()
2948 The ignore_errors() method can be used to ignore various worksheet cell
2949 errors/warnings. For example the following code writes a string that
2950 looks like a number:
2951 $worksheet->write_string('D2', '123');
2953 This causes Excel to display a small green triangle in the top left
2955 hand corner of the cell to indicate an error/warning.
2956 Sometimes these warnings are useful indicators that there is an issue
2958 in the spreadsheet but sometimes it is preferable to turn them off.
2959 Warnings can be turned off at the Excel level for all workbooks and
2960 worksheets by using the using "Excel options -> Formulas -> Error
2961 checking rules". Alternatively you can turn them off for individual
2962 cells in a worksheet, or ranges of cells, using the ignore_errors()
2963 method with a hashref of options and ranges like this:
2964 $worksheet->ignore_errors({number_stored_as_text => 'A1:H50'});
2966 # Or for more than one option:
2968 $worksheet->ignore_errors({number_stored_as_text => 'A1:H50',
2969 eval_error => 'A1:H50'});
2970 The range can be a single cell, a range of cells, or multiple cells and
2972 ranges separated by spaces:
2973 # Single cell.
2975 $worksheet->ignore_errors({eval_error => 'C6'});
2976 # Or a single range:
2978 $worksheet->ignore_errors({eval_error => 'C6:G8'});
2979 # Or multiple cells and ranges:
2981 $worksheet->ignore_errors({eval_error => 'C6 E6 G1:G20 J2:J6'});
2982 Note: calling "ignore_errors" multiple times will overwrite the
2984 previous settings.
2985 You can turn off warnings for an entire column by specifying the range
2987 from the first cell in the column to the last cell in the column:
2988 $worksheet->ignore_errors({number_stored_as_text => 'A1:A1048576'});
2990 Or for the entire worksheet by specifying the range from the first cell
2992 in the worksheet to the last cell in the worksheet:
2993 $worksheet->ignore_errors({number_stored_as_text => 'A1:XFD1048576'});
2995 The worksheet errors/warnings that can be ignored are:
2997 • "number_stored_as_text": Turn off errors/warnings for numbers
2999 stores as text.
3000 • "eval_error": Turn off errors/warnings for formula errors (such as
3002 divide by zero).
3003 • "formula_differs": Turn off errors/warnings for formulas that
3005 differ from surrounding formulas.
3006 • "formula_range": Turn off errors/warnings for formulas that omit
3008 cells in a range.
3009 • "formula_unlocked": Turn off errors/warnings for unlocked cells
3011 that contain formulas.
3012 • "empty_cell_reference": Turn off errors/warnings for formulas that
3014 refer to empty cells.
3015 • "list_data_validation": Turn off errors/warnings for cells in a
3017 table that do not comply with applicable data validation rules.
3018 • "calculated_column": Turn off errors/warnings for cell formulas
3020 that differ from the column formula.
3021 • "two_digit_text_year": Turn off errors/warnings for formulas that
3023 contain a two digit text representation of a year.
3024
3026 Page set-up methods affect the way that a worksheet looks when it is
3027 printed. They control features such as page headers and footers and
3028 margins. These methods are really just standard worksheet methods. They
3029 are documented here in a separate section for the sake of clarity.
3030 The following methods are available for page set-up:
3032 set_landscape()
3034 set_portrait()
3035 set_page_view()
3036 set_paper()
3037 center_horizontally()
3038 center_vertically()
3039 set_margins()
3040 set_header()
3041 set_footer()
3042 repeat_rows()
3043 repeat_columns()
3044 hide_gridlines()
3045 print_row_col_headers()
3046 print_area()
3047 print_across()
3048 fit_to_pages()
3049 set_start_page()
3050 set_print_scale()
3051 print_black_and_white()
3052 set_h_pagebreaks()
3053 set_v_pagebreaks()
3054 A common requirement when working with Excel::Writer::XLSX is to apply
3056 the same page set-up features to all of the worksheets in a workbook.
3057 To do this you can use the sheets() method of the "workbook" class to
3058 access the array of worksheets in a workbook:
3059 for $worksheet ( $workbook->sheets() ) {
3061 $worksheet->set_landscape();
3062 }
3063 set_landscape()
3065 This method is used to set the orientation of a worksheet's printed
3066 page to landscape:
3067 $worksheet->set_landscape(); # Landscape mode
3069 set_portrait()
3071 This method is used to set the orientation of a worksheet's printed
3072 page to portrait. The default worksheet orientation is portrait, so you
3073 won't generally need to call this method.
3074 $worksheet->set_portrait(); # Portrait mode
3076 set_page_view()
3078 This method is used to display the worksheet in "Page View/Layout"
3079 mode.
3080 $worksheet->set_page_view();
3082 set_paper( $index )
3084 This method is used to set the paper format for the printed output of a
3085 worksheet. The following paper styles are available:
3086 Index Paper format Paper size
3088 ===== ============ ==========
3089 0 Printer default -
3090 1 Letter 8 1/2 x 11 in
3091 2 Letter Small 8 1/2 x 11 in
3092 3 Tabloid 11 x 17 in
3093 4 Ledger 17 x 11 in
3094 5 Legal 8 1/2 x 14 in
3095 6 Statement 5 1/2 x 8 1/2 in
3096 7 Executive 7 1/4 x 10 1/2 in
3097 8 A3 297 x 420 mm
3098 9 A4 210 x 297 mm
3099 10 A4 Small 210 x 297 mm
3100 11 A5 148 x 210 mm
3101 12 B4 250 x 354 mm
3102 13 B5 182 x 257 mm
3103 14 Folio 8 1/2 x 13 in
3104 15 Quarto 215 x 275 mm
3105 16 - 10x14 in
3106 17 - 11x17 in
3107 18 Note 8 1/2 x 11 in
3108 19 Envelope 9 3 7/8 x 8 7/8
3109 20 Envelope 10 4 1/8 x 9 1/2
3110 21 Envelope 11 4 1/2 x 10 3/8
3111 22 Envelope 12 4 3/4 x 11
3112 23 Envelope 14 5 x 11 1/2
3113 24 C size sheet -
3114 25 D size sheet -
3115 26 E size sheet -
3116 27 Envelope DL 110 x 220 mm
3117 28 Envelope C3 324 x 458 mm
3118 29 Envelope C4 229 x 324 mm
3119 30 Envelope C5 162 x 229 mm
3120 31 Envelope C6 114 x 162 mm
3121 32 Envelope C65 114 x 229 mm
3122 33 Envelope B4 250 x 353 mm
3123 34 Envelope B5 176 x 250 mm
3124 35 Envelope B6 176 x 125 mm
3125 36 Envelope 110 x 230 mm
3126 37 Monarch 3.875 x 7.5 in
3127 38 Envelope 3 5/8 x 6 1/2 in
3128 39 Fanfold 14 7/8 x 11 in
3129 40 German Std Fanfold 8 1/2 x 12 in
3130 41 German Legal Fanfold 8 1/2 x 13 in
3131 Note, it is likely that not all of these paper types will be available
3133 to the end user since it will depend on the paper formats that the
3134 user's printer supports. Therefore, it is best to stick to standard
3135 paper types.
3136 $worksheet->set_paper( 1 ); # US Letter
3138 $worksheet->set_paper( 9 ); # A4
3139 If you do not specify a paper type the worksheet will print using the
3141 printer's default paper.
3142 center_horizontally()
3144 Center the worksheet data horizontally between the margins on the
3145 printed page:
3146 $worksheet->center_horizontally();
3148 center_vertically()
3150 Center the worksheet data vertically between the margins on the printed
3151 page:
3152 $worksheet->center_vertically();
3154 set_margins( $inches )
3156 There are several methods available for setting the worksheet margins
3157 on the printed page:
3158 set_margins() # Set all margins to the same value
3160 set_margins_LR() # Set left and right margins to the same value
3161 set_margins_TB() # Set top and bottom margins to the same value
3162 set_margin_left(); # Set left margin
3163 set_margin_right(); # Set right margin
3164 set_margin_top(); # Set top margin
3165 set_margin_bottom(); # Set bottom margin
3166 All of these methods take a distance in inches as a parameter. Note: 1
3168 inch = 25.4mm. ";-)" The default left and right margin is 0.7 inch. The
3169 default top and bottom margin is 0.75 inch. Note, these defaults are
3170 different from the defaults used in the binary file format by
3171 Spreadsheet::WriteExcel.
3172 set_header( $string, $margin )
3174 Headers and footers are generated using a $string which is a
3175 combination of plain text and control characters. The $margin parameter
3176 is optional.
3177 The available control character are:
3179 Control Category Description
3181 ======= ======== ===========
3182 &L Justification Left
3183 &C Center
3184 &R Right
3185 &P Information Page number
3187 &N Total number of pages
3188 &D Date
3189 &T Time
3190 &F File name
3191 &A Worksheet name
3192 &Z Workbook path
3193 &fontsize Font Font size
3195 &"font,style" Font name and style
3196 &U Single underline
3197 &E Double underline
3198 &S Strikethrough
3199 &X Superscript
3200 &Y Subscript
3201 &[Picture] Images Image placeholder
3203 &G Same as &[Picture]
3204 && Miscellaneous Literal ampersand &
3206 Text in headers and footers can be justified (aligned) to the left,
3208 center and right by prefixing the text with the control characters &L,
3209 &C and &R.
3210 For example (with ASCII art representation of the results):
3212 $worksheet->set_header('&LHello');
3214 ---------------------------------------------------------------
3216 | |
3217 | Hello |
3218 | |
3219 $worksheet->set_header('&CHello');
3222 ---------------------------------------------------------------
3224 | |
3225 | Hello |
3226 | |
3227 $worksheet->set_header('&RHello');
3230 ---------------------------------------------------------------
3232 | |
3233 | Hello |
3234 | |
3235 For simple text, if you do not specify any justification the text will
3237 be centred. However, you must prefix the text with &C if you specify a
3238 font name or any other formatting:
3239 $worksheet->set_header('Hello');
3241 ---------------------------------------------------------------
3243 | |
3244 | Hello |
3245 | |
3246 You can have text in each of the justification regions:
3248 $worksheet->set_header('&LCiao&CBello&RCielo');
3250 ---------------------------------------------------------------
3252 | |
3253 | Ciao Bello Cielo |
3254 | |
3255 The information control characters act as variables that Excel will
3257 update as the workbook or worksheet changes. Times and dates are in the
3258 users default format:
3259 $worksheet->set_header('&CPage &P of &N');
3261 ---------------------------------------------------------------
3263 | |
3264 | Page 1 of 6 |
3265 | |
3266 $worksheet->set_header('&CUpdated at &T');
3269 ---------------------------------------------------------------
3271 | |
3272 | Updated at 12:30 PM |
3273 | |
3274 Images can be inserted using the options shown below. Each image must
3276 have a placeholder in header string using the "&[Picture]" or &G
3277 control characters:
3278 $worksheet->set_header( '&L&G', 0.3, { image_left => 'logo.jpg' });
3280 You can specify the font size of a section of the text by prefixing it
3282 with the control character &n where "n" is the font size:
3283 $worksheet1->set_header( '&C&30Hello Big' );
3285 $worksheet2->set_header( '&C&10Hello Small' );
3286 You can specify the font of a section of the text by prefixing it with
3288 the control sequence "&"font,style"" where "fontname" is a font name
3289 such as "Courier New" or "Times New Roman" and "style" is one of the
3290 standard Windows font descriptions: "Regular", "Italic", "Bold" or
3291 "Bold Italic":
3292 $worksheet1->set_header( '&C&"Courier New,Italic"Hello' );
3294 $worksheet2->set_header( '&C&"Courier New,Bold Italic"Hello' );
3295 $worksheet3->set_header( '&C&"Times New Roman,Regular"Hello' );
3296 It is possible to combine all of these features together to create
3298 sophisticated headers and footers. As an aid to setting up complicated
3299 headers and footers you can record a page set-up as a macro in Excel
3300 and look at the format strings that VBA produces. Remember however that
3301 VBA uses two double quotes "" to indicate a single double quote. For
3302 the last example above the equivalent VBA code looks like this:
3303 .LeftHeader = ""
3305 .CenterHeader = "&""Times New Roman,Regular""Hello"
3306 .RightHeader = ""
3307 To include a single literal ampersand "&" in a header or footer you
3309 should use a double ampersand "&&":
3310 $worksheet1->set_header('&CCuriouser && Curiouser - Attorneys at Law');
3312 As stated above the margin parameter is optional. As with the other
3314 margins the value should be in inches. The default header and footer
3315 margin is 0.3 inch. Note, the default margin is different from the
3316 default used in the binary file format by Spreadsheet::WriteExcel. The
3317 header and footer margin size can be set as follows:
3318 $worksheet->set_header( '&CHello', 0.75 );
3320 The header and footer margins are independent of the top and bottom
3322 margins.
3323 The available options are:
3325 • "image_left" The path to the image. Requires a &G or "&[Picture]"
3327 placeholder.
3328 • "image_center" Same as above.
3330 • "image_right" Same as above.
3332 • "scale_with_doc" Scale header with document. Defaults to true.
3334 • "align_with_margins" Align header to margins. Defaults to true.
3336 The image options must have an accompanying "&[Picture]" or &G control
3338 character in the header string:
3339 $worksheet->set_header(
3341 '&L&[Picture]&C&[Picture]&R&[Picture]',
3342 undef, # If you don't want to change the margin.
3343 {
3344 image_left => 'red.jpg',
3345 image_center => 'blue.jpg',
3346 image_right => 'yellow.jpg'
3347 }
3348 );
3349 Note, the header or footer string must be less than 255 characters.
3351 Strings longer than this will not be written and a warning will be
3352 generated.
3353 The set_header() method can also handle Unicode strings in "UTF-8"
3355 format.
3356 $worksheet->set_header( "&C\x{263a}" )
3358 See, also the "headers.pl" program in the "examples" directory of the
3360 distribution.
3361 set_footer( $string, $margin )
3363 The syntax of the set_footer() method is the same as set_header(), see
3364 above.
3365 repeat_rows( $first_row, $last_row )
3367 Set the number of rows to repeat at the top of each printed page.
3368 For large Excel documents it is often desirable to have the first row
3370 or rows of the worksheet print out at the top of each page. This can be
3371 achieved by using the repeat_rows() method. The parameters $first_row
3372 and $last_row are zero based. The $last_row parameter is optional if
3373 you only wish to specify one row:
3374 $worksheet1->repeat_rows( 0 ); # Repeat the first row
3376 $worksheet2->repeat_rows( 0, 1 ); # Repeat the first two rows
3377 repeat_columns( $first_col, $last_col )
3379 Set the columns to repeat at the left hand side of each printed page.
3380 For large Excel documents it is often desirable to have the first
3382 column or columns of the worksheet print out at the left hand side of
3383 each page. This can be achieved by using the repeat_columns() method.
3384 The parameters $first_column and $last_column are zero based. The
3385 $last_column parameter is optional if you only wish to specify one
3386 column. You can also specify the columns using A1 column notation, see
3387 the note about "Cell notation".
3388 $worksheet1->repeat_columns( 0 ); # Repeat the first column
3390 $worksheet2->repeat_columns( 0, 1 ); # Repeat the first two columns
3391 $worksheet3->repeat_columns( 'A:A' ); # Repeat the first column
3392 $worksheet4->repeat_columns( 'A:B' ); # Repeat the first two columns
3393 hide_gridlines( $option )
3395 This method is used to hide the gridlines on the screen and printed
3396 page. Gridlines are the lines that divide the cells on a worksheet.
3397 Screen and printed gridlines are turned on by default in an Excel
3398 worksheet. If you have defined your own cell borders you may wish to
3399 hide the default gridlines.
3400 $worksheet->hide_gridlines();
3402 The following values of $option are valid:
3404 0 : Don't hide gridlines
3406 1 : Hide printed gridlines only
3407 2 : Hide screen and printed gridlines
3408 If you don't supply an argument or use "undef" the default option is 1,
3410 i.e. only the printed gridlines are hidden.
3411 print_row_col_headers()
3413 Set the option to print the row and column headers on the printed page.
3414 An Excel worksheet looks something like the following;
3416 ------------------------------------------
3418 | | A | B | C | D | ...
3419 ------------------------------------------
3420 | 1 | | | | | ...
3421 | 2 | | | | | ...
3422 | 3 | | | | | ...
3423 | 4 | | | | | ...
3424 |...| ... | ... | ... | ... | ...
3425 The headers are the letters and numbers at the top and the left of the
3427 worksheet. Since these headers serve mainly as a indication of position
3428 on the worksheet they generally do not appear on the printed page. If
3429 you wish to have them printed you can use the print_row_col_headers()
3430 method:
3431 $worksheet->print_row_col_headers();
3433 Do not confuse these headers with page headers as described in the
3435 set_header() section above.
3436 hide_row_col_headers()
3438 Similar to print_row_col_headers() above but set the option to hide the
3439 row and column headers within Excel so that they aren't visible to the
3440 user:
3441 $worksheet->hide_row_col_headers();
3443 print_area( $first_row, $first_col, $last_row, $last_col )
3445 This method is used to specify the area of the worksheet that will be
3446 printed. All four parameters must be specified. You can also use A1
3447 notation, see the note about "Cell notation".
3448 $worksheet1->print_area( 'A1:H20' ); # Cells A1 to H20
3450 $worksheet2->print_area( 0, 0, 19, 7 ); # The same
3451 $worksheet2->print_area( 'A:H' ); # Columns A to H if rows have data
3452 print_across()
3454 The "print_across" method is used to change the default print
3455 direction. This is referred to by Excel as the sheet "page order".
3456 $worksheet->print_across();
3458 The default page order is shown below for a worksheet that extends over
3460 4 pages. The order is called "down then across":
3461 [1] [3]
3463 [2] [4]
3464 However, by using the "print_across" method the print order will be
3466 changed to "across then down":
3467 [1] [2]
3469 [3] [4]
3470 fit_to_pages( $width, $height )
3472 The fit_to_pages() method is used to fit the printed area to a specific
3473 number of pages both vertically and horizontally. If the printed area
3474 exceeds the specified number of pages it will be scaled down to fit.
3475 This guarantees that the printed area will always appear on the
3476 specified number of pages even if the page size or margins change.
3477 $worksheet1->fit_to_pages( 1, 1 ); # Fit to 1x1 pages
3479 $worksheet2->fit_to_pages( 2, 1 ); # Fit to 2x1 pages
3480 $worksheet3->fit_to_pages( 1, 2 ); # Fit to 1x2 pages
3481 The print area can be defined using the print_area() method as
3483 described above.
3484 A common requirement is to fit the printed output to n pages wide but
3486 have the height be as long as necessary. To achieve this set the
3487 $height to zero:
3488 $worksheet1->fit_to_pages( 1, 0 ); # 1 page wide and as long as necessary
3490 Note that although it is valid to use both fit_to_pages() and
3492 set_print_scale() on the same worksheet only one of these options can
3493 be active at a time. The last method call made will set the active
3494 option.
3495 Note that fit_to_pages() will override any manual page breaks that are
3497 defined in the worksheet.
3498 Note: When using fit_to_pages() it may also be required to set the
3500 printer paper size using set_paper() or else Excel will default to "US
3501 Letter".
3502 set_start_page( $start_page )
3504 The set_start_page() method is used to set the number of the starting
3505 page when the worksheet is printed out. The default value is 1.
3506 $worksheet->set_start_page( 2 );
3508 set_print_scale( $scale )
3510 Set the scale factor of the printed page. Scale factors in the range
3511 "10 <= $scale <= 400" are valid:
3512 $worksheet1->set_print_scale( 50 );
3514 $worksheet2->set_print_scale( 75 );
3515 $worksheet3->set_print_scale( 300 );
3516 $worksheet4->set_print_scale( 400 );
3517 The default scale factor is 100. Note, set_print_scale() does not
3519 affect the scale of the visible page in Excel. For that you should use
3520 set_zoom().
3521 Note also that although it is valid to use both fit_to_pages() and
3523 set_print_scale() on the same worksheet only one of these options can
3524 be active at a time. The last method call made will set the active
3525 option.
3526 print_black_and_white()
3528 Set the option to print the worksheet in black and white:
3529 $worksheet->print_black_and_white();
3531 set_h_pagebreaks( @breaks )
3533 Add horizontal page breaks to a worksheet. A page break causes all the
3534 data that follows it to be printed on the next page. Horizontal page
3535 breaks act between rows. To create a page break between rows 20 and 21
3536 you must specify the break at row 21. However in zero index notation
3537 this is actually row 20. So you can pretend for a small while that you
3538 are using 1 index notation:
3539 $worksheet1->set_h_pagebreaks( 20 ); # Break between row 20 and 21
3541 The set_h_pagebreaks() method will accept a list of page breaks and you
3543 can call it more than once:
3544 $worksheet2->set_h_pagebreaks( 20, 40, 60, 80, 100 ); # Add breaks
3546 $worksheet2->set_h_pagebreaks( 120, 140, 160, 180, 200 ); # Add some more
3547 Note: If you specify the "fit to page" option via the fit_to_pages()
3549 method it will override all manual page breaks.
3550 There is a silent limitation of about 1000 horizontal page breaks per
3552 worksheet in line with an Excel internal limitation.
3553 set_v_pagebreaks( @breaks )
3555 Add vertical page breaks to a worksheet. A page break causes all the
3556 data that follows it to be printed on the next page. Vertical page
3557 breaks act between columns. To create a page break between columns 20
3558 and 21 you must specify the break at column 21. However in zero index
3559 notation this is actually column 20. So you can pretend for a small
3560 while that you are using 1 index notation:
3561 $worksheet1->set_v_pagebreaks(20); # Break between column 20 and 21
3563 The set_v_pagebreaks() method will accept a list of page breaks and you
3565 can call it more than once:
3566 $worksheet2->set_v_pagebreaks( 20, 40, 60, 80, 100 ); # Add breaks
3568 $worksheet2->set_v_pagebreaks( 120, 140, 160, 180, 200 ); # Add some more
3569 Note: If you specify the "fit to page" option via the fit_to_pages()
3571 method it will override all manual page breaks.
3572
3574 This section describes the methods and properties that are available
3575 for formatting cells in Excel. The properties of a cell that can be
3576 formatted include: fonts, colours, patterns, borders, alignment and
3577 number formatting.
3578 Creating and using a Format object
3580 Cell formatting is defined through a Format object. Format objects are
3581 created by calling the workbook add_format() method as follows:
3582 my $format1 = $workbook->add_format(); # Set properties later
3584 my $format2 = $workbook->add_format( %props ); # Set at creation
3585 The format object holds all the formatting properties that can be
3587 applied to a cell, a row or a column. The process of setting these
3588 properties is discussed in the next section.
3589 Once a Format object has been constructed and its properties have been
3591 set it can be passed as an argument to the worksheet "write" methods as
3592 follows:
3593 $worksheet->write( 0, 0, 'One', $format );
3595 $worksheet->write_string( 1, 0, 'Two', $format );
3596 $worksheet->write_number( 2, 0, 3, $format );
3597 $worksheet->write_blank( 3, 0, $format );
3598 Formats can also be passed to the worksheet set_row() and set_column()
3600 methods to define the default property for a row or column.
3601 $worksheet->set_row( 0, 15, $format );
3603 $worksheet->set_column( 0, 0, 15, $format );
3604 Format methods and Format properties
3606 The following table shows the Excel format categories, the formatting
3607 properties that can be applied and the equivalent object method:
3608 Category Description Property Method Name
3610 -------- ----------- -------- -----------
3611 Font Font type font set_font()
3612 Font size size set_size()
3613 Font color color set_color()
3614 Bold bold set_bold()
3615 Italic italic set_italic()
3616 Underline underline set_underline()
3617 Strikeout font_strikeout set_font_strikeout()
3618 Super/Subscript font_script set_font_script()
3619 Outline font_outline set_font_outline()
3620 Shadow font_shadow set_font_shadow()
3621 Number Numeric format num_format set_num_format()
3623 Protection Lock cells locked set_locked()
3625 Hide formulas hidden set_hidden()
3626 Alignment Horizontal align align set_align()
3628 Vertical align valign set_align()
3629 Rotation rotation set_rotation()
3630 Text wrap text_wrap set_text_wrap()
3631 Justify last text_justlast set_text_justlast()
3632 Center across center_across set_center_across()
3633 Indentation indent set_indent()
3634 Shrink to fit shrink set_shrink()
3635 Pattern Cell pattern pattern set_pattern()
3637 Background color bg_color set_bg_color()
3638 Foreground color fg_color set_fg_color()
3639 Border Cell border border set_border()
3641 Bottom border bottom set_bottom()
3642 Top border top set_top()
3643 Left border left set_left()
3644 Right border right set_right()
3645 Border color border_color set_border_color()
3646 Bottom color bottom_color set_bottom_color()
3647 Top color top_color set_top_color()
3648 Left color left_color set_left_color()
3649 Right color right_color set_right_color()
3650 Diagonal type diag_type set_diag_type()
3651 Diagonal border diag_border set_diag_border()
3652 Diagonal color diag_color set_diag_color()
3653 There are two ways of setting Format properties: by using the object
3655 method interface or by setting the property directly. For example, a
3656 typical use of the method interface would be as follows:
3657 my $format = $workbook->add_format();
3659 $format->set_bold();
3660 $format->set_color( 'red' );
3661 By comparison the properties can be set directly by passing a hash of
3663 properties to the Format constructor:
3664 my $format = $workbook->add_format( bold => 1, color => 'red' );
3666 or after the Format has been constructed by means of the
3668 set_format_properties() method as follows:
3669 my $format = $workbook->add_format();
3671 $format->set_format_properties( bold => 1, color => 'red' );
3672 You can also store the properties in one or more named hashes and pass
3674 them to the required method:
3675 my %font = (
3677 font => 'Calibri',
3678 size => 12,
3679 color => 'blue',
3680 bold => 1,
3681 );
3682 my %shading = (
3684 bg_color => 'green',
3685 pattern => 1,
3686 );
3687 my $format1 = $workbook->add_format( %font ); # Font only
3690 my $format2 = $workbook->add_format( %font, %shading ); # Font and shading
3691 The provision of two ways of setting properties might lead you to
3693 wonder which is the best way. The method mechanism may be better if you
3694 prefer setting properties via method calls (which the author did when
3695 the code was first written) otherwise passing properties to the
3696 constructor has proved to be a little more flexible and self
3697 documenting in practice. An additional advantage of working with
3698 property hashes is that it allows you to share formatting between
3699 workbook objects as shown in the example above.
3700 The Perl/Tk style of adding properties is also supported:
3702 my %font = (
3704 -font => 'Calibri',
3705 -size => 12,
3706 -color => 'blue',
3707 -bold => 1,
3708 );
3709 Working with formats
3711 The default format is Calibri 11 with all other properties off.
3712 Each unique format in Excel::Writer::XLSX must have a corresponding
3714 Format object. It isn't possible to use a Format with a write() method
3715 and then redefine the Format for use at a later stage. This is because
3716 a Format is applied to a cell not in its current state but in its final
3717 state. Consider the following example:
3718 my $format = $workbook->add_format();
3720 $format->set_bold();
3721 $format->set_color( 'red' );
3722 $worksheet->write( 'A1', 'Cell A1', $format );
3723 $format->set_color( 'green' );
3724 $worksheet->write( 'B1', 'Cell B1', $format );
3725 Cell A1 is assigned the Format $format which is initially set to the
3727 colour red. However, the colour is subsequently set to green. When
3728 Excel displays Cell A1 it will display the final state of the Format
3729 which in this case will be the colour green.
3730 In general a method call without an argument will turn a property on,
3732 for example:
3733 my $format1 = $workbook->add_format();
3735 $format1->set_bold(); # Turns bold on
3736 $format1->set_bold( 1 ); # Also turns bold on
3737 $format1->set_bold( 0 ); # Turns bold off
3738
3740 The Format object methods are described in more detail in the following
3741 sections. In addition, there is a Perl program called "formats.pl" in
3742 the "examples" directory of the WriteExcel distribution. This program
3743 creates an Excel workbook called "formats.xlsx" which contains examples
3744 of almost all the format types.
3745 The following Format methods are available:
3747 set_font()
3749 set_size()
3750 set_color()
3751 set_bold()
3752 set_italic()
3753 set_underline()
3754 set_font_strikeout()
3755 set_font_script()
3756 set_font_outline()
3757 set_font_shadow()
3758 set_num_format()
3759 set_locked()
3760 set_hidden()
3761 set_align()
3762 set_rotation()
3763 set_text_wrap()
3764 set_text_justlast()
3765 set_center_across()
3766 set_indent()
3767 set_shrink()
3768 set_pattern()
3769 set_bg_color()
3770 set_fg_color()
3771 set_border()
3772 set_bottom()
3773 set_top()
3774 set_left()
3775 set_right()
3776 set_border_color()
3777 set_bottom_color()
3778 set_top_color()
3779 set_left_color()
3780 set_right_color()
3781 set_diag_type()
3782 set_diag_border()
3783 set_diag_color()
3784 The above methods can also be applied directly as properties. For
3786 example "$format->set_bold()" is equivalent to
3787 "$workbook->add_format(bold => 1)".
3788 set_format_properties( %properties )
3790 The properties of an existing Format object can be also be set by means
3791 of set_format_properties():
3792 my $format = $workbook->add_format();
3794 $format->set_format_properties( bold => 1, color => 'red' );
3795 However, this method is here mainly for legacy reasons. It is
3797 preferable to set the properties in the format constructor:
3798 my $format = $workbook->add_format( bold => 1, color => 'red' );
3800 set_font( $fontname )
3802 Default state: Font is Calibri
3803 Default action: None
3804 Valid args: Any valid font name
3805 Specify the font used:
3807 $format->set_font('Times New Roman');
3809 Excel can only display fonts that are installed on the system that it
3811 is running on. Therefore it is best to use the fonts that come as
3812 standard such as 'Calibri', 'Times New Roman' and 'Courier New'. See
3813 also the Fonts worksheet created by formats.pl
3814 set_size()
3816 Default state: Font size is 10
3817 Default action: Set font size to 1
3818 Valid args: Integer values from 1 to as big as your screen.
3819 Set the font size. Excel adjusts the height of a row to accommodate the
3821 largest font size in the row. You can also explicitly specify the
3822 height of a row using the set_row() worksheet method.
3823 my $format = $workbook->add_format();
3825 $format->set_size( 30 );
3826 set_color()
3828 Default state: Excels default color, usually black
3829 Default action: Set the default color
3830 Valid args: Integers from 8..63 or the following strings:
3831 'black'
3832 'blue'
3833 'brown'
3834 'cyan'
3835 'gray'
3836 'green'
3837 'lime'
3838 'magenta'
3839 'navy'
3840 'orange'
3841 'pink'
3842 'purple'
3843 'red'
3844 'silver'
3845 'white'
3846 'yellow'
3847 Set the font colour. The set_color() method is used as follows:
3849 my $format = $workbook->add_format();
3851 $format->set_color( 'red' );
3852 $worksheet->write( 0, 0, 'wheelbarrow', $format );
3853 Note: The set_color() method is used to set the colour of the font in a
3855 cell. To set the colour of a cell use the set_bg_color() and
3856 set_pattern() methods.
3857 For additional examples see the 'Named colors' and 'Standard colors'
3859 worksheets created by formats.pl in the examples directory.
3860 See also "WORKING WITH COLOURS".
3862 set_bold()
3864 Default state: bold is off
3865 Default action: Turn bold on
3866 Valid args: 0, 1
3867 Set the bold property of the font:
3869 $format->set_bold(); # Turn bold on
3871 set_italic()
3873 Default state: Italic is off
3874 Default action: Turn italic on
3875 Valid args: 0, 1
3876 Set the italic property of the font:
3878 $format->set_italic(); # Turn italic on
3880 set_underline()
3882 Default state: Underline is off
3883 Default action: Turn on single underline
3884 Valid args: 0 = No underline
3885 1 = Single underline
3886 2 = Double underline
3887 33 = Single accounting underline
3888 34 = Double accounting underline
3889 Set the underline property of the font.
3891 $format->set_underline(); # Single underline
3893 set_font_strikeout()
3895 Default state: Strikeout is off
3896 Default action: Turn strikeout on
3897 Valid args: 0, 1
3898 Set the strikeout property of the font.
3900 set_font_script()
3902 Default state: Super/Subscript is off
3903 Default action: Turn Superscript on
3904 Valid args: 0 = Normal
3905 1 = Superscript
3906 2 = Subscript
3907 Set the superscript/subscript property of the font.
3909 set_font_outline()
3911 Default state: Outline is off
3912 Default action: Turn outline on
3913 Valid args: 0, 1
3914 Macintosh only.
3916 set_font_shadow()
3918 Default state: Shadow is off
3919 Default action: Turn shadow on
3920 Valid args: 0, 1
3921 Macintosh only.
3923 set_num_format()
3925 Default state: General format
3926 Default action: Format index 1
3927 Valid args: See the following table
3928 This method is used to define the numerical format of a number in
3930 Excel. It controls whether a number is displayed as an integer, a
3931 floating point number, a date, a currency value or some other user
3932 defined format.
3933 The numerical format of a cell can be specified by using a format
3935 string or an index to one of Excel's built-in formats:
3936 my $format1 = $workbook->add_format();
3938 my $format2 = $workbook->add_format();
3939 $format1->set_num_format( 'd mmm yyyy' ); # Format string
3940 $format2->set_num_format( 0x0f ); # Format index
3941 $worksheet->write( 0, 0, 36892.521, $format1 ); # 1 Jan 2001
3943 $worksheet->write( 0, 0, 36892.521, $format2 ); # 1-Jan-01
3944 Using format strings you can define very sophisticated formatting of
3946 numbers.
3947 $format01->set_num_format( '0.000' );
3949 $worksheet->write( 0, 0, 3.1415926, $format01 ); # 3.142
3950 $format02->set_num_format( '#,##0' );
3952 $worksheet->write( 1, 0, 1234.56, $format02 ); # 1,235
3953 $format03->set_num_format( '#,##0.00' );
3955 $worksheet->write( 2, 0, 1234.56, $format03 ); # 1,234.56
3956 $format04->set_num_format( '$0.00' );
3958 $worksheet->write( 3, 0, 49.99, $format04 ); # $49.99
3959 # Note you can use other currency symbols such as the pound or yen as well.
3961 # Other currencies may require the use of Unicode.
3962 $format07->set_num_format( 'mm/dd/yy' );
3964 $worksheet->write( 6, 0, 36892.521, $format07 ); # 01/01/01
3965 $format08->set_num_format( 'mmm d yyyy' );
3967 $worksheet->write( 7, 0, 36892.521, $format08 ); # Jan 1 2001
3968 $format09->set_num_format( 'd mmmm yyyy' );
3970 $worksheet->write( 8, 0, 36892.521, $format09 ); # 1 January 2001
3971 $format10->set_num_format( 'dd/mm/yyyy hh:mm AM/PM' );
3973 $worksheet->write( 9, 0, 36892.521, $format10 ); # 01/01/2001 12:30 AM
3974 $format11->set_num_format( '0 "dollar and" .00 "cents"' );
3976 $worksheet->write( 10, 0, 1.87, $format11 ); # 1 dollar and .87 cents
3977 # Conditional numerical formatting.
3979 $format12->set_num_format( '[Green]General;[Red]-General;General' );
3980 $worksheet->write( 11, 0, 123, $format12 ); # > 0 Green
3981 $worksheet->write( 12, 0, -45, $format12 ); # < 0 Red
3982 $worksheet->write( 13, 0, 0, $format12 ); # = 0 Default colour
3983 # Zip code
3985 $format13->set_num_format( '00000' );
3986 $worksheet->write( 14, 0, '01209', $format13 );
3987 The number system used for dates is described in "DATES AND TIME IN
3989 EXCEL".
3990 The colour format should have one of the following values:
3992 [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
3994 Alternatively you can specify the colour based on a colour index as
3996 follows: "[Color n]", where n is a standard Excel colour index - 7. See
3997 the 'Standard colors' worksheet created by formats.pl.
3998 For more information refer to the documentation on formatting in the
4000 "docs" directory of the Excel::Writer::XLSX distro, the Excel on-line
4001 help or
4002 <http://office.microsoft.com/en-gb/assistance/HP051995001033.aspx>.
4003 You should ensure that the format string is valid in Excel prior to
4005 using it in WriteExcel.
4006 Excel's built-in formats are shown in the following table:
4008 Index Index Format String
4010 0 0x00 General
4011 1 0x01 0
4012 2 0x02 0.00
4013 3 0x03 #,##0
4014 4 0x04 #,##0.00
4015 5 0x05 ($#,##0_);($#,##0)
4016 6 0x06 ($#,##0_);[Red]($#,##0)
4017 7 0x07 ($#,##0.00_);($#,##0.00)
4018 8 0x08 ($#,##0.00_);[Red]($#,##0.00)
4019 9 0x09 0%
4020 10 0x0a 0.00%
4021 11 0x0b 0.00E+00
4022 12 0x0c # ?/?
4023 13 0x0d # ??/??
4024 14 0x0e m/d/yy
4025 15 0x0f d-mmm-yy
4026 16 0x10 d-mmm
4027 17 0x11 mmm-yy
4028 18 0x12 h:mm AM/PM
4029 19 0x13 h:mm:ss AM/PM
4030 20 0x14 h:mm
4031 21 0x15 h:mm:ss
4032 22 0x16 m/d/yy h:mm
4033 .. .... ...........
4034 37 0x25 (#,##0_);(#,##0)
4035 38 0x26 (#,##0_);[Red](#,##0)
4036 39 0x27 (#,##0.00_);(#,##0.00)
4037 40 0x28 (#,##0.00_);[Red](#,##0.00)
4038 41 0x29 _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
4039 42 0x2a _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
4040 43 0x2b _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
4041 44 0x2c _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
4042 45 0x2d mm:ss
4043 46 0x2e [h]:mm:ss
4044 47 0x2f mm:ss.0
4045 48 0x30 ##0.0E+0
4046 49 0x31 @
4047 For examples of these formatting codes see the 'Numerical formats'
4049 worksheet created by formats.pl. See also the number_formats1.html and
4050 the number_formats2.html documents in the "docs" directory of the
4051 distro.
4052 Note 1. Numeric formats 23 to 36 are not documented by Microsoft and
4054 may differ in international versions.
4055 Note 2. The built-in formats are localised according to the locale
4057 settings (regional settings on Windows) of the user when opening the
4058 file in Excel:
4059 • The dot appears as the defined local decimal separator.
4061 • The comma appears as the defined local digit groups separator.
4063 • The dollar sign appears as the defined local currency symbol.
4065 • The date, time and duration formats appear as the local equivalent
4067 date or time format.
4068 set_locked()
4070 Default state: Cell locking is on
4071 Default action: Turn locking on
4072 Valid args: 0, 1
4073 This property can be used to prevent modification of a cells contents.
4075 Following Excel's convention, cell locking is turned on by default.
4076 However, it only has an effect if the worksheet has been protected, see
4077 the worksheet protect() method.
4078 my $locked = $workbook->add_format();
4080 $locked->set_locked( 1 ); # A non-op
4081 my $unlocked = $workbook->add_format();
4083 $unlocked->set_locked( 0 );
4084 # Enable worksheet protection
4086 $worksheet->protect();
4087 # This cell cannot be edited.
4089 $worksheet->write( 'A1', '=1+2', $locked );
4090 # This cell can be edited.
4092 $worksheet->write( 'A2', '=1+2', $unlocked );
4093 Note: This offers weak protection even with a password, see the note in
4095 relation to the protect() method.
4096 set_hidden()
4098 Default state: Formula hiding is off
4099 Default action: Turn hiding on
4100 Valid args: 0, 1
4101 This property is used to hide a formula while still displaying its
4103 result. This is generally used to hide complex calculations from end
4104 users who are only interested in the result. It only has an effect if
4105 the worksheet has been protected, see the worksheet protect() method.
4106 my $hidden = $workbook->add_format();
4108 $hidden->set_hidden();
4109 # Enable worksheet protection
4111 $worksheet->protect();
4112 # The formula in this cell isn't visible
4114 $worksheet->write( 'A1', '=1+2', $hidden );
4115 Note: This offers weak protection even with a password, see the note in
4117 relation to the protect() method.
4118 set_align()
4120 Default state: Alignment is off
4121 Default action: Left alignment
4122 Valid args: 'left' Horizontal
4123 'center'
4124 'right'
4125 'fill'
4126 'justify'
4127 'center_across'
4128 'top' Vertical
4130 'vcenter'
4131 'bottom'
4132 'vjustify'
4133 This method is used to set the horizontal and vertical text alignment
4135 within a cell. Vertical and horizontal alignments can be combined. The
4136 method is used as follows:
4137 my $format = $workbook->add_format();
4139 $format->set_align( 'center' );
4140 $format->set_align( 'vcenter' );
4141 $worksheet->set_row( 0, 30 );
4142 $worksheet->write( 0, 0, 'X', $format );
4143 Text can be aligned across two or more adjacent cells using the
4145 "center_across" property. However, for genuine merged cells it is
4146 better to use the merge_range() worksheet method.
4147 The "vjustify" (vertical justify) option can be used to provide
4149 automatic text wrapping in a cell. The height of the cell will be
4150 adjusted to accommodate the wrapped text. To specify where the text
4151 wraps use the set_text_wrap() method.
4152 For further examples see the 'Alignment' worksheet created by
4154 formats.pl.
4155 set_center_across()
4157 Default state: Center across selection is off
4158 Default action: Turn center across on
4159 Valid args: 1
4160 Text can be aligned across two or more adjacent cells using the
4162 set_center_across() method. This is an alias for the
4163 set_align('center_across') method call.
4164 Only one cell should contain the text, the other cells should be blank:
4166 my $format = $workbook->add_format();
4168 $format->set_center_across();
4169 $worksheet->write( 1, 1, 'Center across selection', $format );
4171 $worksheet->write_blank( 1, 2, $format );
4172 See also the "merge1.pl" to "merge6.pl" programs in the "examples"
4174 directory and the merge_range() method.
4175 set_text_wrap()
4177 Default state: Text wrap is off
4178 Default action: Turn text wrap on
4179 Valid args: 0, 1
4180 Here is an example using the text wrap property, the escape character
4182 "\n" is used to indicate the end of line:
4183 my $format = $workbook->add_format();
4185 $format->set_text_wrap();
4186 $worksheet->write( 0, 0, "It's\na bum\nwrap", $format );
4187 Excel will adjust the height of the row to accommodate the wrapped
4189 text. A similar effect can be obtained without newlines using the
4190 set_align('vjustify') method. See the "textwrap.pl" program in the
4191 "examples" directory.
4192 set_rotation()
4194 Default state: Text rotation is off
4195 Default action: None
4196 Valid args: Integers in the range -90 to 90 and 270
4197 Set the rotation of the text in a cell. The rotation can be any angle
4199 in the range -90 to 90 degrees.
4200 my $format = $workbook->add_format();
4202 $format->set_rotation( 30 );
4203 $worksheet->write( 0, 0, 'This text is rotated', $format );
4204 The angle 270 is also supported. This indicates text where the letters
4206 run from top to bottom.
4207 set_indent()
4209 Default state: Text indentation is off
4210 Default action: Indent text 1 level
4211 Valid args: Positive integers
4212 This method can be used to indent text. The argument, which should be
4214 an integer, is taken as the level of indentation:
4215 my $format = $workbook->add_format();
4217 $format->set_indent( 2 );
4218 $worksheet->write( 0, 0, 'This text is indented', $format );
4219 Indentation is a horizontal alignment property. It will override any
4221 other horizontal properties but it can be used in conjunction with
4222 vertical properties.
4223 set_shrink()
4225 Default state: Text shrinking is off
4226 Default action: Turn "shrink to fit" on
4227 Valid args: 1
4228 This method can be used to shrink text so that it fits in a cell.
4230 my $format = $workbook->add_format();
4232 $format->set_shrink();
4233 $worksheet->write( 0, 0, 'Honey, I shrunk the text!', $format );
4234 set_text_justlast()
4236 Default state: Justify last is off
4237 Default action: Turn justify last on
4238 Valid args: 0, 1
4239 Only applies to Far Eastern versions of Excel.
4241 set_pattern()
4243 Default state: Pattern is off
4244 Default action: Solid fill is on
4245 Valid args: 0 .. 18
4246 Set the background pattern of a cell.
4248 Examples of the available patterns are shown in the 'Patterns'
4250 worksheet created by formats.pl. However, it is unlikely that you will
4251 ever need anything other than Pattern 1 which is a solid fill of the
4252 background color.
4253 set_bg_color()
4255 Default state: Color is off
4256 Default action: Solid fill.
4257 Valid args: See set_color()
4258 The set_bg_color() method can be used to set the background colour of a
4260 pattern. Patterns are defined via the set_pattern() method. If a
4261 pattern hasn't been defined then a solid fill pattern is used as the
4262 default.
4263 Here is an example of how to set up a solid fill in a cell:
4265 my $format = $workbook->add_format();
4267 $format->set_pattern(); # This is optional when using a solid fill
4269 $format->set_bg_color( 'green' );
4271 $worksheet->write( 'A1', 'Ray', $format );
4272 For further examples see the 'Patterns' worksheet created by
4274 formats.pl.
4275 set_fg_color()
4277 Default state: Color is off
4278 Default action: Solid fill.
4279 Valid args: See set_color()
4280 The set_fg_color() method can be used to set the foreground colour of a
4282 pattern.
4283 For further examples see the 'Patterns' worksheet created by
4285 formats.pl.
4286 set_border()
4288 Also applies to: set_bottom()
4289 set_top()
4290 set_left()
4291 set_right()
4292 Default state: Border is off
4294 Default action: Set border type 1
4295 Valid args: 0-13, See below.
4296 A cell border is comprised of a border on the bottom, top, left and
4298 right. These can be set to the same value using set_border() or
4299 individually using the relevant method calls shown above.
4300 The following shows the border styles sorted by Excel::Writer::XLSX
4302 index number:
4303 Index Name Weight Style
4305 ===== ============= ====== ===========
4306 0 None 0
4307 1 Continuous 1 -----------
4308 2 Continuous 2 -----------
4309 3 Dash 1 - - - - - -
4310 4 Dot 1 . . . . . .
4311 5 Continuous 3 -----------
4312 6 Double 3 ===========
4313 7 Continuous 0 -----------
4314 8 Dash 2 - - - - - -
4315 9 Dash Dot 1 - . - . - .
4316 10 Dash Dot 2 - . - . - .
4317 11 Dash Dot Dot 1 - . . - . .
4318 12 Dash Dot Dot 2 - . . - . .
4319 13 SlantDash Dot 2 / - . / - .
4320 The following shows the borders sorted by style:
4322 Name Weight Style Index
4324 ============= ====== =========== =====
4325 Continuous 0 ----------- 7
4326 Continuous 1 ----------- 1
4327 Continuous 2 ----------- 2
4328 Continuous 3 ----------- 5
4329 Dash 1 - - - - - - 3
4330 Dash 2 - - - - - - 8
4331 Dash Dot 1 - . - . - . 9
4332 Dash Dot 2 - . - . - . 10
4333 Dash Dot Dot 1 - . . - . . 11
4334 Dash Dot Dot 2 - . . - . . 12
4335 Dot 1 . . . . . . 4
4336 Double 3 =========== 6
4337 None 0 0
4338 SlantDash Dot 2 / - . / - . 13
4339 The following shows the borders in the order shown in the Excel Dialog.
4341 Index Style Index Style
4343 ===== ===== ===== =====
4344 0 None 12 - . . - . .
4345 7 ----------- 13 / - . / - .
4346 4 . . . . . . 10 - . - . - .
4347 11 - . . - . . 8 - - - - - -
4348 9 - . - . - . 2 -----------
4349 3 - - - - - - 5 -----------
4350 1 ----------- 6 ===========
4351 Examples of the available border styles are shown in the 'Borders'
4353 worksheet created by formats.pl.
4354 set_border_color()
4356 Also applies to: set_bottom_color()
4357 set_top_color()
4358 set_left_color()
4359 set_right_color()
4360 Default state: Color is off
4362 Default action: Undefined
4363 Valid args: See set_color()
4364 Set the colour of the cell borders. A cell border is comprised of a
4366 border on the bottom, top, left and right. These can be set to the same
4367 colour using set_border_color() or individually using the relevant
4368 method calls shown above. Examples of the border styles and colours are
4369 shown in the 'Borders' worksheet created by formats.pl.
4370 set_diag_type()
4372 Default state: Diagonal border is off.
4373 Default action: None.
4374 Valid args: 1-3, See below.
4375 Set the diagonal border type for the cell. Three types of diagonal
4377 borders are available in Excel:
4378 1: From bottom left to top right.
4380 2: From top left to bottom right.
4381 3: Same as 1 and 2 combined.
4382 For example:
4384 $format->set_diag_type( 3 );
4386 set_diag_border()
4388 Default state: Border is off
4389 Default action: Set border type 1
4390 Valid args: 0-13, See below.
4391 Set the diagonal border style. Same as the parameter to set_border()
4393 above.
4394 set_diag_color()
4396 Default state: Color is off
4397 Default action: Undefined
4398 Valid args: See set_color()
4399 Set the colour of the diagonal cell border:
4401 $format->set_diag_type( 3 );
4403 $format->set_diag_border( 7 );
4404 $format->set_diag_color( 'red' );
4405 set_quote_prefix()
4407 Default state: quote prefix is off
4408 Default action: Turn quote prefix on
4409 Valid args: 0, 1
4410 Set the quote prefix property of a format to ensure a string is treated
4412 as a string after editing. This is the same as prefixing the string
4413 with a single quote in Excel. You don't need to add the quote to the
4414 string but you do need to add the format.
4415 Set the quote prefix property of the format:
4417 $format->set_quote_prefix(); # Turn quote prefix on
4419 copy( $format )
4421 This method is used to copy all of the properties from one Format
4422 object to another:
4423 my $lorry1 = $workbook->add_format();
4425 $lorry1->set_bold();
4426 $lorry1->set_italic();
4427 $lorry1->set_color( 'red' ); # lorry1 is bold, italic and red
4428 my $lorry2 = $workbook->add_format();
4430 $lorry2->copy( $lorry1 );
4431 $lorry2->set_color( 'yellow' ); # lorry2 is bold, italic and yellow
4432 The copy() method is only useful if you are using the method interface
4434 to Format properties. It generally isn't required if you are setting
4435 Format properties directly using hashes.
4436 Note: this is not a copy constructor, both objects must exist prior to
4438 copying.
4439
4441 The following is a brief introduction to handling Unicode in
4442 "Excel::Writer::XLSX".
4443 For a more general introduction to Unicode handling in Perl see
4445 perlunitut and perluniintro.
4446 Excel::Writer::XLSX writer differs from Spreadsheet::WriteExcel in that
4448 it only handles Unicode data in "UTF-8" format and doesn't try to
4449 handle legacy UTF-16 Excel formats.
4450 If the data is in "UTF-8" format then Excel::Writer::XLSX will handle
4452 it automatically.
4453 If you are dealing with non-ASCII characters that aren't in "UTF-8"
4455 then perl provides useful tools in the guise of the "Encode" module to
4456 help you to convert to the required format. For example:
4457 use Encode 'decode';
4459 my $string = 'some string with koi8-r characters';
4461 $string = decode('koi8-r', $string); # koi8-r to utf8
4462 Alternatively you can read data from an encoded file and convert it to
4464 "UTF-8" as you read it in:
4465 my $file = 'unicode_koi8r.txt';
4467 open FH, '<:encoding(koi8-r)', $file or die "Couldn't open $file: $!\n";
4468 my $row = 0;
4470 while ( <FH> ) {
4471 # Data read in is now in utf8 format.
4472 chomp;
4473 $worksheet->write( $row++, 0, $_ );
4474 }
4475 These methodologies are explained in more detail in perlunitut,
4477 perluniintro and perlunicode.
4478 If the program contains UTF-8 text then you will also need to add "use
4480 utf8" to the includes:
4481 use utf8;
4483 ...
4485 $worksheet->write( 'A1', 'Some UTF-8 string' );
4487 See also the "unicode_*.pl" programs in the examples directory of the
4489 distro.
4490
4492 Throughout Excel::Writer::XLSX colours can be specified using a Html
4493 style "#RRGGBB" value. For example with a Format object:
4494 $format->set_color( '#FF0000' );
4496 For backward compatibility a limited number of color names are
4498 supported:
4499 $format->set_color( 'red' );
4501 The color names supported are:
4503 black
4505 blue
4506 brown
4507 cyan
4508 gray
4509 green
4510 lime
4511 magenta
4512 navy
4513 orange
4514 pink
4515 purple
4516 red
4517 silver
4518 white
4519 yellow
4520 See also "colors.pl" in the "examples" directory.
4522
4524 There are two important things to understand about dates and times in
4525 Excel:
4526 1 A date/time in Excel is a real number plus an Excel number format.
4528 2 Excel::Writer::XLSX doesn't automatically convert date/time strings
4529 in write() to an Excel date/time.
4530 These two points are explained in more detail below along with some
4532 suggestions on how to convert times and dates to the required format.
4533 An Excel date/time is a number plus a format
4535 If you write a date string with write() then all you will get is a
4536 string:
4537 $worksheet->write( 'A1', '02/03/04' ); # !! Writes a string not a date. !!
4539 Dates and times in Excel are represented by real numbers, for example
4541 "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
4542 The integer part of the number stores the number of days since the
4544 epoch and the fractional part stores the percentage of the day.
4545 A date or time in Excel is just like any other number. To have the
4547 number display as a date you must apply an Excel number format to it.
4548 Here are some examples.
4549 #!/usr/bin/perl -w
4551 use strict;
4553 use Excel::Writer::XLSX;
4554 my $workbook = Excel::Writer::XLSX->new( 'date_examples.xlsx' );
4556 my $worksheet = $workbook->add_worksheet();
4557 $worksheet->set_column( 'A:A', 30 ); # For extra visibility.
4559 my $number = 39506.5;
4561 $worksheet->write( 'A1', $number ); # 39506.5
4563 my $format2 = $workbook->add_format( num_format => 'dd/mm/yy' );
4565 $worksheet->write( 'A2', $number, $format2 ); # 28/02/08
4566 my $format3 = $workbook->add_format( num_format => 'mm/dd/yy' );
4568 $worksheet->write( 'A3', $number, $format3 ); # 02/28/08
4569 my $format4 = $workbook->add_format( num_format => 'd-m-yyyy' );
4571 $worksheet->write( 'A4', $number, $format4 ); # 28-2-2008
4572 my $format5 = $workbook->add_format( num_format => 'dd/mm/yy hh:mm' );
4574 $worksheet->write( 'A5', $number, $format5 ); # 28/02/08 12:00
4575 my $format6 = $workbook->add_format( num_format => 'd mmm yyyy' );
4577 $worksheet->write( 'A6', $number, $format6 ); # 28 Feb 2008
4578 my $format7 = $workbook->add_format( num_format => 'mmm d yyyy hh:mm AM/PM' );
4580 $worksheet->write('A7', $number , $format7); # Feb 28 2008 12:00 PM
4581 $workbook->close();
4583 Excel::Writer::XLSX doesn't automatically convert date/time strings
4585 Excel::Writer::XLSX doesn't automatically convert input date strings
4586 into Excel's formatted date numbers due to the large number of possible
4587 date formats and also due to the possibility of misinterpretation.
4588 For example, does "02/03/04" mean March 2 2004, February 3 2004 or even
4590 March 4 2002.
4591 Therefore, in order to handle dates you will have to convert them to
4593 numbers and apply an Excel format. Some methods for converting dates
4594 are listed in the next section.
4595 The most direct way is to convert your dates to the ISO8601
4597 "yyyy-mm-ddThh:mm:ss.sss" date format and use the write_date_time()
4598 worksheet method:
4599 $worksheet->write_date_time( 'A2', '2001-01-01T12:20', $format );
4601 See the write_date_time() section of the documentation for more
4603 details.
4604 A general methodology for handling date strings with write_date_time()
4606 is:
4607 1. Identify incoming date/time strings with a regex.
4609 2. Extract the component parts of the date/time using the same regex.
4610 3. Convert the date/time to the ISO8601 format.
4611 4. Write the date/time using write_date_time() and a number format.
4612 Here is an example:
4614 #!/usr/bin/perl -w
4616 use strict;
4618 use Excel::Writer::XLSX;
4619 my $workbook = Excel::Writer::XLSX->new( 'example.xlsx' );
4621 my $worksheet = $workbook->add_worksheet();
4622 # Set the default format for dates.
4624 my $date_format = $workbook->add_format( num_format => 'mmm d yyyy' );
4625 # Increase column width to improve visibility of data.
4627 $worksheet->set_column( 'A:C', 20 );
4628 # Simulate reading from a data source.
4630 my $row = 0;
4631 while ( <DATA> ) {
4633 chomp;
4634 my $col = 0;
4636 my @data = split ' ';
4637 for my $item ( @data ) {
4639 # Match dates in the following formats: d/m/yy, d/m/yyyy
4641 if ( $item =~ qr[^(\d{1,2})/(\d{1,2})/(\d{4})$] ) {
4642 # Change to the date format required by write_date_time().
4644 my $date = sprintf "%4d-%02d-%02dT", $3, $2, $1;
4645 $worksheet->write_date_time( $row, $col++, $date,
4647 $date_format );
4648 }
4649 else {
4650 # Just plain data
4652 $worksheet->write( $row, $col++, $item );
4653 }
4654 }
4655 $row++;
4656 }
4657 $workbook->close();
4659 __DATA__
4661 Item Cost Date
4662 Book 10 1/9/2007
4663 Beer 4 12/9/2007
4664 Bed 500 5/10/2007
4665 For a slightly more advanced solution you can modify the write() method
4667 to handle date formats of your choice via the add_write_handler()
4668 method. See the add_write_handler() section of the docs and the
4669 write_handler3.pl and write_handler4.pl programs in the examples
4670 directory of the distro.
4671 Converting dates and times to an Excel date or time
4673 The write_date_time() method above is just one way of handling dates
4674 and times.
4675 You can also use the convert_date_time() worksheet method to convert
4677 from an ISO8601 style date string to an Excel date and time number.
4678 The Excel::Writer::XLSX::Utility module which is included in the distro
4680 has date/time handling functions:
4681 use Excel::Writer::XLSX::Utility;
4683 $date = xl_date_list(2002, 1, 1); # 37257
4685 $date = xl_parse_date("11 July 1997"); # 35622
4686 $time = xl_parse_time('3:21:36 PM'); # 0.64
4687 $date = xl_decode_date_EU("13 May 2002"); # 37389
4688 Note: some of these functions require additional CPAN modules.
4690 For date conversions using the CPAN "DateTime" framework see
4692 DateTime::Format::Excel
4693 <http://search.cpan.org/search?dist=DateTime-Format-Excel>.
4694
4696 Excel allows you to group rows or columns so that they can be hidden or
4697 displayed with a single mouse click. This feature is referred to as
4698 outlines.
4699 Outlines can reduce complex data down to a few salient sub-totals or
4701 summaries.
4702 This feature is best viewed in Excel but the following is an ASCII
4704 representation of what a worksheet with three outlines might look like.
4705 Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at
4706 level 1. The lines at the left hand side are called outline level bars.
4707 ------------------------------------------
4709 1 2 3 | | A | B | C | D | ...
4710 ------------------------------------------
4711 _ | 1 | A | | | | ...
4712 | _ | 2 | B | | | | ...
4713 | | | 3 | (C) | | | | ...
4714 | | | 4 | (D) | | | | ...
4715 | - | 5 | E | | | | ...
4716 | _ | 6 | F | | | | ...
4717 | | | 7 | (G) | | | | ...
4718 | | | 8 | (H) | | | | ...
4719 | - | 9 | I | | | | ...
4720 - | . | ... | ... | ... | ... | ...
4721 Clicking the minus sign on each of the level 2 outlines will collapse
4723 and hide the data as shown in the next figure. The minus sign changes
4724 to a plus sign to indicate that the data in the outline is hidden.
4725 ------------------------------------------
4727 1 2 3 | | A | B | C | D | ...
4728 ------------------------------------------
4729 _ | 1 | A | | | | ...
4730 | | 2 | B | | | | ...
4731 | + | 5 | E | | | | ...
4732 | | 6 | F | | | | ...
4733 | + | 9 | I | | | | ...
4734 - | . | ... | ... | ... | ... | ...
4735 Clicking on the minus sign on the level 1 outline will collapse the
4737 remaining rows as follows:
4738 ------------------------------------------
4740 1 2 3 | | A | B | C | D | ...
4741 ------------------------------------------
4742 | 1 | A | | | | ...
4743 + | . | ... | ... | ... | ... | ...
4744 Grouping in "Excel::Writer::XLSX" is achieved by setting the outline
4746 level via the set_row() and set_column() worksheet methods:
4747 set_row( $row, $height, $format, $hidden, $level, $collapsed )
4749 set_column( $first_col, $last_col, $width, $format, $hidden, $level, $collapsed )
4750 The following example sets an outline level of 1 for rows 2 and 3
4752 (zero-indexed) and columns B to G. The parameters $height and $XF are
4753 assigned default values since they are undefined:
4754 $worksheet->set_row( 1, undef, undef, 0, 1 );
4756 $worksheet->set_row( 2, undef, undef, 0, 1 );
4757 $worksheet->set_column( 'B:G', undef, undef, 0, 1 );
4758 Excel allows up to 7 outline levels. Therefore the $level parameter
4760 should be in the range "0 <= $level <= 7".
4761 Rows and columns can be collapsed by setting the $hidden flag for the
4763 hidden rows/columns and setting the $collapsed flag for the row/column
4764 that has the collapsed "+" symbol:
4765 $worksheet->set_row( 1, undef, undef, 1, 1 );
4767 $worksheet->set_row( 2, undef, undef, 1, 1 );
4768 $worksheet->set_row( 3, undef, undef, 0, 0, 1 ); # Collapsed flag.
4769 $worksheet->set_column( 'B:G', undef, undef, 1, 1 );
4771 $worksheet->set_column( 'H:H', undef, undef, 0, 0, 1 ); # Collapsed flag.
4772 Note: Setting the $collapsed flag is particularly important for
4774 compatibility with OpenOffice.org and Gnumeric.
4775 For a more complete example see the "outline.pl" and
4777 "outline_collapsed.pl" programs in the examples directory of the
4778 distro.
4779 Some additional outline properties can be set via the
4781 outline_settings() worksheet method, see above.
4782
4784 Data validation is a feature of Excel which allows you to restrict the
4785 data that a users enters in a cell and to display help and warning
4786 messages. It also allows you to restrict input to values in a drop down
4787 list.
4788 A typical use case might be to restrict data in a cell to integer
4790 values in a certain range, to provide a help message to indicate the
4791 required value and to issue a warning if the input data doesn't meet
4792 the stated criteria. In Excel::Writer::XLSX we could do that as
4793 follows:
4794 $worksheet->data_validation('B3',
4796 {
4797 validate => 'integer',
4798 criteria => 'between',
4799 minimum => 1,
4800 maximum => 100,
4801 input_title => 'Input an integer:',
4802 input_message => 'Between 1 and 100',
4803 error_message => 'Sorry, try again.',
4804 });
4805 For more information on data validation see the following Microsoft
4807 support article "Description and examples of data validation in Excel":
4808 <http://support.microsoft.com/kb/211485>.
4809 The following sections describe how to use the data_validation() method
4811 and its various options.
4812 data_validation( $row, $col, { parameter => 'value', ... } )
4814 The data_validation() method is used to construct an Excel data
4815 validation.
4816 It can be applied to a single cell or a range of cells. You can pass 3
4818 parameters such as "($row, $col, {...})" or 5 parameters such as
4819 "($first_row, $first_col, $last_row, $last_col, {...})". You can also
4820 use "A1" style notation. For example:
4821 $worksheet->data_validation( 0, 0, {...} );
4823 $worksheet->data_validation( 0, 0, 4, 1, {...} );
4824 # Which are the same as:
4826 $worksheet->data_validation( 'A1', {...} );
4828 $worksheet->data_validation( 'A1:B5', {...} );
4829 See also the note about "Cell notation" for more information.
4831 The last parameter in data_validation() must be a hash ref containing
4833 the parameters that describe the type and style of the data validation.
4834 The allowable parameters are:
4835 validate
4837 criteria
4838 value | minimum | source
4839 maximum
4840 ignore_blank
4841 dropdown
4842 input_title
4844 input_message
4845 show_input
4846 error_title
4848 error_message
4849 error_type
4850 show_error
4851 These parameters are explained in the following sections. Most of the
4853 parameters are optional, however, you will generally require the three
4854 main options "validate", "criteria" and "value".
4855 $worksheet->data_validation('B3',
4857 {
4858 validate => 'integer',
4859 criteria => '>',
4860 value => 100,
4861 });
4862 The "data_validation" method returns:
4864 0 for success.
4866 -1 for insufficient number of arguments.
4867 -2 for row or column out of bounds.
4868 -3 for incorrect parameter or value.
4869 validate
4871 This parameter is passed in a hash ref to data_validation().
4872 The "validate" parameter is used to set the type of data that you wish
4874 to validate. It is always required and it has no default value.
4875 Allowable values are:
4876 any
4878 integer
4879 decimal
4880 list
4881 date
4882 time
4883 length
4884 custom
4885 • any is used to specify that the type of data is unrestricted. This
4887 is useful to display an input message without restricting the data
4888 that can be entered.
4889 • integer restricts the cell to integer values. Excel refers to this
4891 as 'whole number'.
4892 validate => 'integer',
4894 criteria => '>',
4895 value => 100,
4896 • decimal restricts the cell to decimal values.
4898 validate => 'decimal',
4900 criteria => '>',
4901 value => 38.6,
4902 • list restricts the cell to a set of user specified values. These
4904 can be passed in an array ref or as a cell range (named ranges
4905 aren't currently supported):
4906 validate => 'list',
4908 value => ['open', 'high', 'close'],
4909 # Or like this:
4910 value => 'B1:B3',
4911 Excel requires that range references are only to cells on the same
4913 worksheet.
4914 • date restricts the cell to date values. Dates in Excel are
4916 expressed as integer values but you can also pass an ISO8601 style
4917 string as used in write_date_time(). See also "DATES AND TIME IN
4918 EXCEL" for more information about working with Excel's dates.
4919 validate => 'date',
4921 criteria => '>',
4922 value => 39653, # 24 July 2008
4923 # Or like this:
4924 value => '2008-07-24T',
4925 • time restricts the cell to time values. Times in Excel are
4927 expressed as decimal values but you can also pass an ISO8601 style
4928 string as used in write_date_time(). See also "DATES AND TIME IN
4929 EXCEL" for more information about working with Excel's times.
4930 validate => 'time',
4932 criteria => '>',
4933 value => 0.5, # Noon
4934 # Or like this:
4935 value => 'T12:00:00',
4936 • length restricts the cell data based on an integer string length.
4938 Excel refers to this as 'Text length'.
4939 validate => 'length',
4941 criteria => '>',
4942 value => 10,
4943 • custom restricts the cell based on an external Excel formula that
4945 returns a "TRUE/FALSE" value.
4946 validate => 'custom',
4948 value => '=IF(A10>B10,TRUE,FALSE)',
4949 criteria
4951 This parameter is passed in a hash ref to data_validation().
4952 The "criteria" parameter is used to set the criteria by which the data
4954 in the cell is validated. It is almost always required except for the
4955 "list" and "custom" validate options. It has no default value.
4956 Allowable values are:
4957 'between'
4959 'not between'
4960 'equal to' | '==' | '='
4961 'not equal to' | '!=' | '<>'
4962 'greater than' | '>'
4963 'less than' | '<'
4964 'greater than or equal to' | '>='
4965 'less than or equal to' | '<='
4966 You can either use Excel's textual description strings, in the first
4968 column above, or the more common symbolic alternatives. The following
4969 are equivalent:
4970 validate => 'integer',
4972 criteria => 'greater than',
4973 value => 100,
4974 validate => 'integer',
4976 criteria => '>',
4977 value => 100,
4978 The "list" and "custom" validate options don't require a "criteria". If
4980 you specify one it will be ignored.
4981 validate => 'list',
4983 value => ['open', 'high', 'close'],
4984 validate => 'custom',
4986 value => '=IF(A10>B10,TRUE,FALSE)',
4987 value | minimum | source
4989 This parameter is passed in a hash ref to data_validation().
4990 The "value" parameter is used to set the limiting value to which the
4992 "criteria" is applied. It is always required and it has no default
4993 value. You can also use the synonyms "minimum" or "source" to make the
4994 validation a little clearer and closer to Excel's description of the
4995 parameter:
4996 # Use 'value'
4998 validate => 'integer',
4999 criteria => '>',
5000 value => 100,
5001 # Use 'minimum'
5003 validate => 'integer',
5004 criteria => 'between',
5005 minimum => 1,
5006 maximum => 100,
5007 # Use 'source'
5009 validate => 'list',
5010 source => '$B$1:$B$3',
5011 maximum
5013 This parameter is passed in a hash ref to data_validation().
5014 The "maximum" parameter is used to set the upper limiting value when
5016 the "criteria" is either 'between' or 'not between':
5017 validate => 'integer',
5019 criteria => 'between',
5020 minimum => 1,
5021 maximum => 100,
5022 ignore_blank
5024 This parameter is passed in a hash ref to data_validation().
5025 The "ignore_blank" parameter is used to toggle on and off the 'Ignore
5027 blank' option in the Excel data validation dialog. When the option is
5028 on the data validation is not applied to blank data in the cell. It is
5029 on by default.
5030 ignore_blank => 0, # Turn the option off
5032 dropdown
5034 This parameter is passed in a hash ref to data_validation().
5035 The "dropdown" parameter is used to toggle on and off the 'In-cell
5037 dropdown' option in the Excel data validation dialog. When the option
5038 is on a dropdown list will be shown for "list" validations. It is on by
5039 default.
5040 dropdown => 0, # Turn the option off
5042 input_title
5044 This parameter is passed in a hash ref to data_validation().
5045 The "input_title" parameter is used to set the title of the input
5047 message that is displayed when a cell is entered. It has no default
5048 value and is only displayed if the input message is displayed. See the
5049 "input_message" parameter below.
5050 input_title => 'This is the input title',
5052 The maximum title length is 32 characters.
5054 input_message
5056 This parameter is passed in a hash ref to data_validation().
5057 The "input_message" parameter is used to set the input message that is
5059 displayed when a cell is entered. It has no default value.
5060 validate => 'integer',
5062 criteria => 'between',
5063 minimum => 1,
5064 maximum => 100,
5065 input_title => 'Enter the applied discount:',
5066 input_message => 'between 1 and 100',
5067 The message can be split over several lines using newlines, "\n" in
5069 double quoted strings.
5070 input_message => "This is\na test.",
5072 The maximum message length is 255 characters.
5074 show_input
5076 This parameter is passed in a hash ref to data_validation().
5077 The "show_input" parameter is used to toggle on and off the 'Show input
5079 message when cell is selected' option in the Excel data validation
5080 dialog. When the option is off an input message is not displayed even
5081 if it has been set using "input_message". It is on by default.
5082 show_input => 0, # Turn the option off
5084 error_title
5086 This parameter is passed in a hash ref to data_validation().
5087 The "error_title" parameter is used to set the title of the error
5089 message that is displayed when the data validation criteria is not met.
5090 The default error title is 'Microsoft Excel'.
5091 error_title => 'Input value is not valid',
5093 The maximum title length is 32 characters.
5095 error_message
5097 This parameter is passed in a hash ref to data_validation().
5098 The "error_message" parameter is used to set the error message that is
5100 displayed when a cell is entered. The default error message is "The
5101 value you entered is not valid.\nA user has restricted values that can
5102 be entered into the cell.".
5103 validate => 'integer',
5105 criteria => 'between',
5106 minimum => 1,
5107 maximum => 100,
5108 error_title => 'Input value is not valid',
5109 error_message => 'It should be an integer between 1 and 100',
5110 The message can be split over several lines using newlines, "\n" in
5112 double quoted strings.
5113 input_message => "This is\na test.",
5115 The maximum message length is 255 characters.
5117 error_type
5119 This parameter is passed in a hash ref to data_validation().
5120 The "error_type" parameter is used to specify the type of error dialog
5122 that is displayed. There are 3 options:
5123 'stop'
5125 'warning'
5126 'information'
5127 The default is 'stop'.
5129 show_error
5131 This parameter is passed in a hash ref to data_validation().
5132 The "show_error" parameter is used to toggle on and off the 'Show error
5134 alert after invalid data is entered' option in the Excel data
5135 validation dialog. When the option is off an error message is not
5136 displayed even if it has been set using "error_message". It is on by
5137 default.
5138 show_error => 0, # Turn the option off
5140 Data Validation Examples
5142 Example 1. Limiting input to an integer greater than a fixed value.
5143 $worksheet->data_validation('A1',
5145 {
5146 validate => 'integer',
5147 criteria => '>',
5148 value => 0,
5149 });
5150 Example 2. Limiting input to an integer greater than a fixed value
5152 where the value is referenced from a cell.
5153 $worksheet->data_validation('A2',
5155 {
5156 validate => 'integer',
5157 criteria => '>',
5158 value => '=E3',
5159 });
5160 Example 3. Limiting input to a decimal in a fixed range.
5162 $worksheet->data_validation('A3',
5164 {
5165 validate => 'decimal',
5166 criteria => 'between',
5167 minimum => 0.1,
5168 maximum => 0.5,
5169 });
5170 Example 4. Limiting input to a value in a dropdown list.
5172 $worksheet->data_validation('A4',
5174 {
5175 validate => 'list',
5176 source => ['open', 'high', 'close'],
5177 });
5178 Example 5. Limiting input to a value in a dropdown list where the list
5180 is specified as a cell range.
5181 $worksheet->data_validation('A5',
5183 {
5184 validate => 'list',
5185 source => '=$E$4:$G$4',
5186 });
5187 Example 6. Limiting input to a date in a fixed range.
5189 $worksheet->data_validation('A6',
5191 {
5192 validate => 'date',
5193 criteria => 'between',
5194 minimum => '2008-01-01T',
5195 maximum => '2008-12-12T',
5196 });
5197 Example 7. Displaying a message when the cell is selected.
5199 $worksheet->data_validation('A7',
5201 {
5202 validate => 'integer',
5203 criteria => 'between',
5204 minimum => 1,
5205 maximum => 100,
5206 input_title => 'Enter an integer:',
5207 input_message => 'between 1 and 100',
5208 });
5209 See also the "data_validate.pl" program in the examples directory of
5211 the distro.
5212
5214 Conditional formatting is a feature of Excel which allows you to apply
5215 a format to a cell or a range of cells based on a certain criteria.
5216 For example the following criteria is used to highlight cells >= 50 in
5218 red in the "conditional_format.pl" example from the distro:
5219 # Write a conditional format over a range.
5221 $worksheet->conditional_formatting( 'B3:K12',
5222 {
5223 type => 'cell',
5224 criteria => '>=',
5225 value => 50,
5226 format => $format1,
5227 }
5228 );
5229 conditional_formatting( $row, $col, { parameter => 'value', ... } )
5231 The conditional_formatting() method is used to apply formatting based
5232 on user defined criteria to an Excel::Writer::XLSX file.
5233 It can be applied to a single cell or a range of cells. You can pass 3
5235 parameters such as "($row, $col, {...})" or 5 parameters such as
5236 "($first_row, $first_col, $last_row, $last_col, {...})". You can also
5237 use "A1" style notation. For example:
5238 $worksheet->conditional_formatting( 0, 0, {...} );
5240 $worksheet->conditional_formatting( 0, 0, 4, 1, {...} );
5241 # Which are the same as:
5243 $worksheet->conditional_formatting( 'A1', {...} );
5245 $worksheet->conditional_formatting( 'A1:B5', {...} );
5246 See also the note about "Cell notation" for more information.
5248 Using "A1" style notation is also possible to specify non-contiguous
5250 ranges, separated by a comma. For example:
5251 $worksheet->conditional_formatting( 'A1:D5,A8:D12', {...} );
5253 The last parameter in conditional_formatting() must be a hash ref
5255 containing the parameters that describe the type and style of the data
5256 validation. The main parameters are:
5257 type
5259 format
5260 criteria
5261 value
5262 minimum
5263 maximum
5264 Other, less commonly used parameters are:
5266 min_type
5268 mid_type
5269 max_type
5270 min_value
5271 mid_value
5272 max_value
5273 min_color
5274 mid_color
5275 max_color
5276 bar_color
5277 bar_only
5278 bar_solid
5279 bar_negative_color
5280 bar_border_color
5281 bar_negative_border_color
5282 bar_negative_color_same
5283 bar_negative_border_color_same
5284 bar_no_border
5285 bar_direction
5286 bar_axis_position
5287 bar_axis_color
5288 data_bar_2010
5289 icon_style
5290 icons
5291 reverse_icons
5292 icons_only
5293 stop_if_true
5294 multi_range
5295 Additional parameters which are used for specific conditional format
5297 types are shown in the relevant sections below.
5298 type
5300 This parameter is passed in a hash ref to conditional_formatting().
5301 The "type" parameter is used to set the type of conditional formatting
5303 that you wish to apply. It is always required and it has no default
5304 value. Allowable "type" values and their associated parameters are:
5305 Type Parameters
5307 ==== ==========
5308 cell criteria
5309 value
5310 minimum
5311 maximum
5312 format
5313 date criteria
5315 value
5316 minimum
5317 maximum
5318 format
5319 time_period criteria
5321 format
5322 text criteria
5324 value
5325 format
5326 average criteria
5328 format
5329 duplicate format
5331 unique format
5333 top criteria
5335 value
5336 format
5337 bottom criteria
5339 value
5340 format
5341 blanks format
5343 no_blanks format
5345 errors format
5347 no_errors format
5349 formula criteria
5351 format
5352 2_color_scale min_type
5354 max_type
5355 min_value
5356 max_value
5357 min_color
5358 max_color
5359 3_color_scale min_type
5361 mid_type
5362 max_type
5363 min_value
5364 mid_value
5365 max_value
5366 min_color
5367 mid_color
5368 max_color
5369 data_bar min_type
5371 max_type
5372 min_value
5373 max_value
5374 bar_only
5375 bar_color
5376 bar_solid*
5377 bar_negative_color*
5378 bar_border_color*
5379 bar_negative_border_color*
5380 bar_negative_color_same*
5381 bar_negative_border_color_same*
5382 bar_no_border*
5383 bar_direction*
5384 bar_axis_position*
5385 bar_axis_color*
5386 data_bar_2010*
5387 icon_set icon_style
5389 reverse_icons
5390 icons
5391 icons_only
5392 Data bar parameters marked with (*) are only available in Excel 2010
5394 and later. Files that use these properties can still be opened in Excel
5395 2007 but the data bars will be displayed without them.
5396 type => 'cell'
5398 This is the most common conditional formatting type. It is used when a
5399 format is applied to a cell based on a simple criterion. For example:
5400 $worksheet->conditional_formatting( 'A1',
5402 {
5403 type => 'cell',
5404 criteria => 'greater than',
5405 value => 5,
5406 format => $red_format,
5407 }
5408 );
5409 Or, using the "between" criteria:
5411 $worksheet->conditional_formatting( 'C1:C4',
5413 {
5414 type => 'cell',
5415 criteria => 'between',
5416 minimum => 20,
5417 maximum => 30,
5418 format => $green_format,
5419 }
5420 );
5421 criteria
5423 The "criteria" parameter is used to set the criteria by which the cell
5424 data will be evaluated. It has no default value. The most common
5425 criteria as applied to "{ type => 'cell' }" are:
5426 'between'
5428 'not between'
5429 'equal to' | '==' | '='
5430 'not equal to' | '!=' | '<>'
5431 'greater than' | '>'
5432 'less than' | '<'
5433 'greater than or equal to' | '>='
5434 'less than or equal to' | '<='
5435 You can either use Excel's textual description strings, in the first
5437 column above, or the more common symbolic alternatives.
5438 Additional criteria which are specific to other conditional format
5440 types are shown in the relevant sections below.
5441 value
5443 The "value" is generally used along with the "criteria" parameter to
5444 set the rule by which the cell data will be evaluated.
5445 type => 'cell',
5447 criteria => '>',
5448 value => 5
5449 format => $format,
5450 The "value" property can also be an cell reference.
5452 type => 'cell',
5454 criteria => '>',
5455 value => '$C$1',
5456 format => $format,
5457 format
5459 The "format" parameter is used to specify the format that will be
5460 applied to the cell when the conditional formatting criterion is met.
5461 The format is created using the add_format() method in the same way as
5462 cell formats:
5463 $format = $workbook->add_format( bold => 1, italic => 1 );
5465 $worksheet->conditional_formatting( 'A1',
5467 {
5468 type => 'cell',
5469 criteria => '>',
5470 value => 5
5471 format => $format,
5472 }
5473 );
5474 The conditional format follows the same rules as in Excel: it is
5476 superimposed over the existing cell format and not all font and border
5477 properties can be modified. Font properties that can't be modified are
5478 font name, font size, superscript and subscript. The border property
5479 that cannot be modified is diagonal borders.
5480 Excel specifies some default formats to be used with conditional
5482 formatting. You can replicate them using the following
5483 Excel::Writer::XLSX formats:
5484 # Light red fill with dark red text.
5486 my $format1 = $workbook->add_format(
5488 bg_color => '#FFC7CE',
5489 color => '#9C0006',
5490 );
5491 # Light yellow fill with dark yellow text.
5493 my $format2 = $workbook->add_format(
5495 bg_color => '#FFEB9C',
5496 color => '#9C6500',
5497 );
5498 # Green fill with dark green text.
5500 my $format3 = $workbook->add_format(
5502 bg_color => '#C6EFCE',
5503 color => '#006100',
5504 );
5505 minimum
5507 The "minimum" parameter is used to set the lower limiting value when
5508 the "criteria" is either 'between' or 'not between':
5509 validate => 'integer',
5511 criteria => 'between',
5512 minimum => 1,
5513 maximum => 100,
5514 maximum
5516 The "maximum" parameter is used to set the upper limiting value when
5517 the "criteria" is either 'between' or 'not between'. See the previous
5518 example.
5519 type => 'date'
5521 The "date" type is the same as the "cell" type and uses the same
5522 criteria and values. However it allows the "value", "minimum" and
5523 "maximum" properties to be specified in the ISO8601
5524 "yyyy-mm-ddThh:mm:ss.sss" date format which is detailed in the
5525 write_date_time() method.
5526 $worksheet->conditional_formatting( 'A1:A4',
5528 {
5529 type => 'date',
5530 criteria => 'greater than',
5531 value => '2011-01-01T',
5532 format => $format,
5533 }
5534 );
5535 type => 'time_period'
5537 The "time_period" type is used to specify Excel's "Dates Occurring"
5538 style conditional format.
5539 $worksheet->conditional_formatting( 'A1:A4',
5541 {
5542 type => 'time_period',
5543 criteria => 'yesterday',
5544 format => $format,
5545 }
5546 );
5547 The period is set in the "criteria" and can have one of the following
5549 values:
5550 criteria => 'yesterday',
5552 criteria => 'today',
5553 criteria => 'last 7 days',
5554 criteria => 'last week',
5555 criteria => 'this week',
5556 criteria => 'next week',
5557 criteria => 'last month',
5558 criteria => 'this month',
5559 criteria => 'next month'
5560 type => 'text'
5562 The "text" type is used to specify Excel's "Specific Text" style
5563 conditional format. It is used to do simple string matching using the
5564 "criteria" and "value" parameters:
5565 $worksheet->conditional_formatting( 'A1:A4',
5567 {
5568 type => 'text',
5569 criteria => 'containing',
5570 value => 'foo',
5571 format => $format,
5572 }
5573 );
5574 The "criteria" can have one of the following values:
5576 criteria => 'containing',
5578 criteria => 'not containing',
5579 criteria => 'begins with',
5580 criteria => 'ends with',
5581 The "value" parameter should be a string or single character.
5583 type => 'average'
5585 The "average" type is used to specify Excel's "Average" style
5586 conditional format.
5587 $worksheet->conditional_formatting( 'A1:A4',
5589 {
5590 type => 'average',
5591 criteria => 'above',
5592 format => $format,
5593 }
5594 );
5595 The type of average for the conditional format range is specified by
5597 the "criteria":
5598 criteria => 'above',
5600 criteria => 'below',
5601 criteria => 'equal or above',
5602 criteria => 'equal or below',
5603 criteria => '1 std dev above',
5604 criteria => '1 std dev below',
5605 criteria => '2 std dev above',
5606 criteria => '2 std dev below',
5607 criteria => '3 std dev above',
5608 criteria => '3 std dev below',
5609 type => 'duplicate'
5611 The "duplicate" type is used to highlight duplicate cells in a range:
5612 $worksheet->conditional_formatting( 'A1:A4',
5614 {
5615 type => 'duplicate',
5616 format => $format,
5617 }
5618 );
5619 type => 'unique'
5621 The "unique" type is used to highlight unique cells in a range:
5622 $worksheet->conditional_formatting( 'A1:A4',
5624 {
5625 type => 'unique',
5626 format => $format,
5627 }
5628 );
5629 type => 'top'
5631 The "top" type is used to specify the top "n" values by number or
5632 percentage in a range:
5633 $worksheet->conditional_formatting( 'A1:A4',
5635 {
5636 type => 'top',
5637 value => 10,
5638 format => $format,
5639 }
5640 );
5641 The "criteria" can be used to indicate that a percentage condition is
5643 required:
5644 $worksheet->conditional_formatting( 'A1:A4',
5646 {
5647 type => 'top',
5648 value => 10,
5649 criteria => '%',
5650 format => $format,
5651 }
5652 );
5653 type => 'bottom'
5655 The "bottom" type is used to specify the bottom "n" values by number or
5656 percentage in a range.
5657 It takes the same parameters as "top", see above.
5659 type => 'blanks'
5661 The "blanks" type is used to highlight blank cells in a range:
5662 $worksheet->conditional_formatting( 'A1:A4',
5664 {
5665 type => 'blanks',
5666 format => $format,
5667 }
5668 );
5669 type => 'no_blanks'
5671 The "no_blanks" type is used to highlight non blank cells in a range:
5672 $worksheet->conditional_formatting( 'A1:A4',
5674 {
5675 type => 'no_blanks',
5676 format => $format,
5677 }
5678 );
5679 type => 'errors'
5681 The "errors" type is used to highlight error cells in a range:
5682 $worksheet->conditional_formatting( 'A1:A4',
5684 {
5685 type => 'errors',
5686 format => $format,
5687 }
5688 );
5689 type => 'no_errors'
5691 The "no_errors" type is used to highlight non error cells in a range:
5692 $worksheet->conditional_formatting( 'A1:A4',
5694 {
5695 type => 'no_errors',
5696 format => $format,
5697 }
5698 );
5699 type => 'formula'
5701 The "formula" type is used to specify a conditional format based on a
5702 user defined formula:
5703 $worksheet->conditional_formatting( 'A1:A4',
5705 {
5706 type => 'formula',
5707 criteria => '=$A$1 > 5',
5708 format => $format,
5709 }
5710 );
5711 The formula is specified in the "criteria".
5713 type => '2_color_scale'
5715 The "2_color_scale" type is used to specify Excel's "2 Color Scale"
5716 style conditional format.
5717 $worksheet->conditional_formatting( 'A1:A12',
5719 {
5720 type => '2_color_scale',
5721 }
5722 );
5723 This conditional type can be modified with "min_type", "max_type",
5725 "min_value", "max_value", "min_color" and "max_color", see below.
5726 type => '3_color_scale'
5728 The "3_color_scale" type is used to specify Excel's "3 Color Scale"
5729 style conditional format.
5730 $worksheet->conditional_formatting( 'A1:A12',
5732 {
5733 type => '3_color_scale',
5734 }
5735 );
5736 This conditional type can be modified with "min_type", "mid_type",
5738 "max_type", "min_value", "mid_value", "max_value", "min_color",
5739 "mid_color" and "max_color", see below.
5740 type => 'data_bar'
5742 The "data_bar" type is used to specify Excel's "Data Bar" style
5743 conditional format.
5744 $worksheet->conditional_formatting( 'A1:A12',
5746 {
5747 type => 'data_bar',
5748 }
5749 );
5750 This data bar conditional type can be modified with the following
5752 parameters, which are explained in the sections below. These properties
5753 were available in the original xlsx file specification used in Excel
5754 2007::
5755 min_type
5757 max_type
5758 min_value
5759 max_value
5760 bar_color
5761 bar_only
5762 In Excel 2010 additional data bar properties were added such as solid
5764 (non-gradient) bars and control over how negative values are displayed.
5765 These properties can be set using the following parameters:
5766 bar_solid
5768 bar_negative_color
5769 bar_border_color
5770 bar_negative_border_color
5771 bar_negative_color_same
5772 bar_negative_border_color_same
5773 bar_no_border
5774 bar_direction
5775 bar_axis_position
5776 bar_axis_color
5777 data_bar_2010
5778 Files that use these Excel 2010 properties can still be opened in Excel
5780 2007 but the data bars will be displayed without them.
5781 type => 'icon_set'
5783 The "icon_set" type is used to specify a conditional format with a set
5784 of icons such as traffic lights or arrows:
5785 $worksheet->conditional_formatting( 'A1:C1',
5787 {
5788 type => 'icon_set',
5789 icon_style => '3_traffic_lights',
5790 }
5791 );
5792 The icon set style is specified by the "icon_style" parameter. Valid
5794 options are:
5795 3_arrows
5797 3_arrows_gray
5798 3_flags
5799 3_signs
5800 3_symbols
5801 3_symbols_circled
5802 3_traffic_lights
5803 3_traffic_lights_rimmed
5804 4_arrows
5806 4_arrows_gray
5807 4_ratings
5808 4_red_to_black
5809 4_traffic_lights
5810 5_arrows
5812 5_arrows_gray
5813 5_quarters
5814 5_ratings
5815 The criteria, type and value of each icon can be specified using the
5817 "icon" array of hash refs with optional "criteria", "type" and "value"
5818 parameters:
5819 $worksheet->conditional_formatting( 'A1:D1',
5821 {
5822 type => 'icon_set',
5823 icon_style => '4_red_to_black',
5824 icons => [ {criteria => '>', type => 'number', value => 90},
5825 {criteria => '>=', type => 'percentile', value => 50},
5826 {criteria => '>', type => 'percent', value => 25},
5827 ],
5828 }
5829 );
5830 The "icons criteria" parameter should be either ">=" or ">". The
5832 default "criteria" is ">=".
5833 The "icons type" parameter should be one of the following values:
5835 number
5837 percentile
5838 percent
5839 formula
5840 The default "type" is "percent".
5842 The "icons value" parameter can be a value or formula:
5844 $worksheet->conditional_formatting( 'A1:D1',
5846 {
5847 type => 'icon_set',
5848 icon_style => '4_red_to_black',
5849 icons => [ {value => 90},
5850 {value => 50},
5851 {value => 25},
5852 ],
5853 }
5854 );
5855 Note: The "icons" parameters should start with the highest value and
5857 with each subsequent one being lower. The default "value" is "(n * 100)
5858 / number_of_icons". The lowest number icon in an icon set has
5859 properties defined by Excel. Therefore in a "n" icon set, there is no
5860 "n-1" hash of parameters.
5861 The order of the icons can be reversed using the "reverse_icons"
5863 parameter:
5864 $worksheet->conditional_formatting( 'A1:C1',
5866 {
5867 type => 'icon_set',
5868 icon_style => '3_arrows',
5869 reverse_icons => 1,
5870 }
5871 );
5872 The icons can be displayed without the cell value using the
5874 "icons_only" parameter:
5875 $worksheet->conditional_formatting( 'A1:C1',
5877 {
5878 type => 'icon_set',
5879 icon_style => '3_flags',
5880 icons_only => 1,
5881 }
5882 );
5883 min_type, mid_type, max_type
5885 The "min_type" and "max_type" properties are available when the
5886 conditional formatting type is "2_color_scale", "3_color_scale" or
5887 "data_bar". The "mid_type" is available for "3_color_scale". The
5888 properties are used as follows:
5889 $worksheet->conditional_formatting( 'A1:A12',
5891 {
5892 type => '2_color_scale',
5893 min_type => 'percent',
5894 max_type => 'percent',
5895 }
5896 );
5897 The available min/mid/max types are:
5899 min (for min_type only)
5901 num
5902 percent
5903 percentile
5904 formula
5905 max (for max_type only)
5906 min_value, mid_value, max_value
5908 The "min_value" and "max_value" properties are available when the
5909 conditional formatting type is "2_color_scale", "3_color_scale" or
5910 "data_bar". The "mid_value" is available for "3_color_scale". The
5911 properties are used as follows:
5912 $worksheet->conditional_formatting( 'A1:A12',
5914 {
5915 type => '2_color_scale',
5916 min_value => 10,
5917 max_value => 90,
5918 }
5919 );
5920 min_color, mid_color, max_color, bar_color
5922 The "min_color" and "max_color" properties are available when the
5923 conditional formatting type is "2_color_scale", "3_color_scale" or
5924 "data_bar". The "mid_color" is available for "3_color_scale". The
5925 properties are used as follows:
5926 $worksheet->conditional_formatting( 'A1:A12',
5928 {
5929 type => '2_color_scale',
5930 min_color => "#C5D9F1",
5931 max_color => "#538ED5",
5932 }
5933 );
5934 The color can be specified as an Excel::Writer::XLSX color index or,
5936 more usefully, as a HTML style RGB hex number, as shown above.
5937 bar_only
5939 The "bar_only" parameter property displays a bar data but not the data
5940 in the cells:
5941 $worksheet->conditional_formatting( 'D3:D14',
5943 {
5944 type => 'data_bar',
5945 bar_only => 1
5946 }
5947 );
5948 bar_solid
5950 The "bar_solid" parameter turns on a solid (non-gradient) fill for data
5951 bars:
5952 $worksheet->conditional_formatting( 'H3:H14',
5954 {
5955 type => 'data_bar',
5956 bar_solid => 1
5957 }
5958 );
5959 Note, this property is only visible in Excel 2010 and later.
5961 bar_negative_color
5963 The "bar_negative_color" parameter is used to set the color fill for
5964 the negative portion of a data bar.
5965 The color can be specified as an Excel::Writer::XLSX color index or as
5967 a HTML style RGB hex number, as shown in the other examples.
5968 Note, this property is only visible in Excel 2010 and later.
5970 bar_border_color
5972 The "bar_border_color" parameter is used to set the border color of a
5973 data bar.
5974 The color can be specified as an Excel::Writer::XLSX color index or as
5976 a HTML style RGB hex number, as shown in the other examples.
5977 Note, this property is only visible in Excel 2010 and later.
5979 bar_negative_border_color
5981 The "bar_negative_border_color" parameter is used to set the border
5982 color of the negative portion of a data bar.
5983 The color can be specified as an Excel::Writer::XLSX color index or as
5985 a HTML style RGB hex number, as shown in the other examples.
5986 Note, this property is only visible in Excel 2010 and later.
5988 bar_negative_color_same
5990 The "bar_negative_color_same" parameter sets the fill color for the
5991 negative portion of a data bar to be the same as the fill color for the
5992 positive portion of the data bar:
5993 $worksheet->conditional_formatting( 'N3:N14',
5995 {
5996 type => 'data_bar',
5997 bar_negative_color_same => 1,
5998 bar_negative_border_color_same => 1
5999 }
6000 );
6001 Note, this property is only visible in Excel 2010 and later.
6003 bar_negative_border_color_same
6005 The "bar_negative_border_color_same" parameter sets the border color
6006 for the negative portion of a data bar to be the same as the border
6007 color for the positive portion of the data bar.
6008 Note, this property is only visible in Excel 2010 and later.
6010 bar_no_border
6012 The "bar_no_border" parameter turns off the border of a data bar.
6013 Note, this property is only visible in Excel 2010 and later, however
6015 the default in Excel 2007 is not to have a border.
6016 bar_direction
6018 The "bar_direction" parameter sets the direction for data bars. This
6019 property can be either "left" for left-to-right or "right" for right-
6020 to-left. If the property isn't set then Excel will adjust the position
6021 automatically based on the context:
6022 $worksheet->conditional_formatting( 'J3:J14',
6024 {
6025 type => 'data_bar',
6026 bar_direction => 'right'
6027 }
6028 );
6029 Note, this property is only visible in Excel 2010 and later.
6031 bar_axis_position
6033 The "bar_axis_position" parameter sets the position within the cells
6034 for the axis that is shown in data bars when there are negative values
6035 to display. The property can be either "middle" or "none". If the
6036 property isn't set then Excel will position the axis based on the range
6037 of positive and negative values.
6038 Note, this property is only visible in Excel 2010 and later.
6040 bar_axis_color
6042 The "bar_axis_color" parameter sets the color for the axis that is
6043 shown in data bars when there are negative values to display.
6044 The color can be specified as an Excel::Writer::XLSX color index or as
6046 a HTML style RGB hex number, as shown in the other examples.
6047 Note, this property is only visible in Excel 2010 and later.
6049 data_bar_2010
6051 The "data_bar_2010" parameter sets Excel 2010 style data bars even when
6052 Excel 2010 specific properties aren't used. This can be used to create
6053 consistency across all the data bar formatting in a worksheet:
6054 $worksheet->conditional_formatting( 'L3:L14',
6056 {
6057 type => 'data_bar',
6058 data_bar_2010 => 1
6059 }
6060 );
6061 Note, this property is only visible in Excel 2010 and later.
6063 stop_if_true
6065 The "stop_if_true" parameter, if set to a true value, will enable the
6066 "stop if true" feature on the conditional formatting rule, so that
6067 subsequent rules are not examined for any cell on which the conditions
6068 for this rule are met.
6069 Conditional Formatting Examples
6071 Example 1. Highlight cells greater than an integer value.
6072 $worksheet->conditional_formatting( 'A1:F10',
6074 {
6075 type => 'cell',
6076 criteria => 'greater than',
6077 value => 5,
6078 format => $format,
6079 }
6080 );
6081 Example 2. Highlight cells greater than a value in a reference cell.
6083 $worksheet->conditional_formatting( 'A1:F10',
6085 {
6086 type => 'cell',
6087 criteria => 'greater than',
6088 value => '$H$1',
6089 format => $format,
6090 }
6091 );
6092 Example 3. Highlight cells greater than a certain date:
6094 $worksheet->conditional_formatting( 'A1:F10',
6096 {
6097 type => 'date',
6098 criteria => 'greater than',
6099 value => '2011-01-01T',
6100 format => $format,
6101 }
6102 );
6103 Example 4. Highlight cells with a date in the last seven days:
6105 $worksheet->conditional_formatting( 'A1:F10',
6107 {
6108 type => 'time_period',
6109 criteria => 'last 7 days',
6110 format => $format,
6111 }
6112 );
6113 Example 5. Highlight cells with strings starting with the letter "b":
6115 $worksheet->conditional_formatting( 'A1:F10',
6117 {
6118 type => 'text',
6119 criteria => 'begins with',
6120 value => 'b',
6121 format => $format,
6122 }
6123 );
6124 Example 6. Highlight cells that are 1 std deviation above the average
6126 for the range:
6127 $worksheet->conditional_formatting( 'A1:F10',
6129 {
6130 type => 'average',
6131 format => $format,
6132 }
6133 );
6134 Example 7. Highlight duplicate cells in a range:
6136 $worksheet->conditional_formatting( 'A1:F10',
6138 {
6139 type => 'duplicate',
6140 format => $format,
6141 }
6142 );
6143 Example 8. Highlight unique cells in a range.
6145 $worksheet->conditional_formatting( 'A1:F10',
6147 {
6148 type => 'unique',
6149 format => $format,
6150 }
6151 );
6152 Example 9. Highlight the top 10 cells.
6154 $worksheet->conditional_formatting( 'A1:F10',
6156 {
6157 type => 'top',
6158 value => 10,
6159 format => $format,
6160 }
6161 );
6162 Example 10. Highlight blank cells.
6164 $worksheet->conditional_formatting( 'A1:F10',
6166 {
6167 type => 'blanks',
6168 format => $format,
6169 }
6170 );
6171 Example 11. Set traffic light icons in 3 cells:
6173 $worksheet->conditional_formatting( 'A1:C1',
6175 {
6176 type => 'icon_set',
6177 icon_style => '3_traffic_lights',
6178 }
6179 );
6180 See also the "conditional_format.pl" example program in "EXAMPLES".
6182
6184 Sparklines are a feature of Excel 2010+ which allows you to add small
6185 charts to worksheet cells. These are useful for showing visual trends
6186 in data in a compact format.
6187 In Excel::Writer::XLSX Sparklines can be added to cells using the
6189 add_sparkline() worksheet method:
6190 $worksheet->add_sparkline(
6192 {
6193 location => 'F2',
6194 range => 'Sheet1!A2:E2',
6195 type => 'column',
6196 style => 12,
6197 }
6198 );
6199 Note: Sparklines are a feature of Excel 2010+ only. You can write them
6201 to an XLSX file that can be read by Excel 2007 but they won't be
6202 displayed.
6203 add_sparkline( { parameter => 'value', ... } )
6205 The add_sparkline() worksheet method is used to add sparklines to a
6206 cell or a range of cells.
6207 The parameters to add_sparkline() must be passed in a hash ref. The
6209 main sparkline parameters are:
6210 location (required)
6212 range (required)
6213 type
6214 style
6215 markers
6217 negative_points
6218 axis
6219 reverse
6220 Other, less commonly used parameters are:
6222 high_point
6224 low_point
6225 first_point
6226 last_point
6227 max
6228 min
6229 empty_cells
6230 show_hidden
6231 date_axis
6232 weight
6233 series_color
6235 negative_color
6236 markers_color
6237 first_color
6238 last_color
6239 high_color
6240 low_color
6241 These parameters are explained in the sections below:
6243 location
6245 This is the cell where the sparkline will be displayed:
6246 location => 'F1'
6248 The "location" should be a single cell. (For multiple cells see
6250 "Grouped Sparklines" below).
6251 To specify the location in row-column notation use the
6253 xl_rowcol_to_cell() function from the Excel::Writer::XLSX::Utility
6254 module.
6255 use Excel::Writer::XLSX::Utility ':rowcol';
6257 ...
6258 location => xl_rowcol_to_cell( 0, 5 ), # F1
6259 range
6261 This specifies the cell data range that the sparkline will plot:
6262 $worksheet->add_sparkline(
6264 {
6265 location => 'F1',
6266 range => 'A1:E1',
6267 }
6268 );
6269 The "range" should be a 2D array. (For 3D arrays of cells see "Grouped
6271 Sparklines" below).
6272 If "range" is not on the same worksheet you can specify its location
6274 using the usual Excel notation:
6275 range => 'Sheet1!A1:E1',
6277 If the worksheet contains spaces or special characters you should quote
6279 the worksheet name in the same way that Excel does:
6280 range => q('Monthly Data'!A1:E1),
6282 To specify the location in row-column notation use the xl_range() or
6284 xl_range_formula() functions from the Excel::Writer::XLSX::Utility
6285 module.
6286 use Excel::Writer::XLSX::Utility ':rowcol';
6288 ...
6289 range => xl_range( 1, 1, 0, 4 ), # 'A1:E1'
6290 range => xl_range_formula( 'Sheet1', 0, 0, 0, 4 ), # 'Sheet1!A2:E2'
6291 type
6293 Specifies the type of sparkline. There are 3 available sparkline types:
6294 line (default)
6296 column
6297 win_loss
6298 For example:
6300 {
6302 location => 'F1',
6303 range => 'A1:E1',
6304 type => 'column',
6305 }
6306 style
6308 Excel provides 36 built-in Sparkline styles in 6 groups of 6. The
6309 "style" parameter can be used to replicate these and should be a
6310 corresponding number from 1 .. 36.
6311 {
6313 location => 'A14',
6314 range => 'Sheet2!A2:J2',
6315 style => 3,
6316 }
6317 The style number starts in the top left of the style grid and runs left
6319 to right. The default style is 1. It is possible to override colour
6320 elements of the sparklines using the *_color parameters below.
6321 markers
6323 Turn on the markers for "line" style sparklines.
6324 {
6326 location => 'A6',
6327 range => 'Sheet2!A1:J1',
6328 markers => 1,
6329 }
6330 Markers aren't shown in Excel for "column" and "win_loss" sparklines.
6332 negative_points
6334 Highlight negative values in a sparkline range. This is usually
6335 required with "win_loss" sparklines.
6336 {
6338 location => 'A21',
6339 range => 'Sheet2!A3:J3',
6340 type => 'win_loss',
6341 negative_points => 1,
6342 }
6343 axis
6345 Display a horizontal axis in the sparkline:
6346 {
6348 location => 'A10',
6349 range => 'Sheet2!A1:J1',
6350 axis => 1,
6351 }
6352 reverse
6354 Plot the data from right-to-left instead of the default left-to-right:
6355 {
6357 location => 'A24',
6358 range => 'Sheet2!A4:J4',
6359 type => 'column',
6360 reverse => 1,
6361 }
6362 weight
6364 Adjust the default line weight (thickness) for "line" style sparklines.
6365 weight => 0.25,
6367 The weight value should be one of the following values allowed by
6369 Excel:
6370 0.25 0.5 0.75
6372 1 1.25
6373 2.25
6374 3
6375 4.25
6376 6
6377 high_point, low_point, first_point, last_point
6379 Highlight points in a sparkline range.
6380 high_point => 1,
6382 low_point => 1,
6383 first_point => 1,
6384 last_point => 1,
6385 max, min
6387 Specify the maximum and minimum vertical axis values:
6388 max => 0.5,
6390 min => -0.5,
6391 As a special case you can set the maximum and minimum to be for a group
6393 of sparklines rather than one:
6394 max => 'group',
6396 See "Grouped Sparklines" below.
6398 empty_cells
6400 Define how empty cells are handled in a sparkline.
6401 empty_cells => 'zero',
6403 The available options are:
6405 gaps : show empty cells as gaps (the default).
6407 zero : plot empty cells as 0.
6408 connect: Connect points with a line ("line" type sparklines only).
6409 show_hidden
6411 Plot data in hidden rows and columns:
6412 show_hidden => 1,
6414 Note, this option is off by default.
6416 date_axis
6418 Specify an alternative date axis for the sparkline. This is useful if
6419 the data being plotted isn't at fixed width intervals:
6420 {
6422 location => 'F3',
6423 range => 'A3:E3',
6424 date_axis => 'A4:E4',
6425 }
6426 The number of cells in the date range should correspond to the number
6428 of cells in the data range.
6429 series_color
6431 It is possible to override the colour of a sparkline style using the
6432 following parameters:
6433 series_color
6435 negative_color
6436 markers_color
6437 first_color
6438 last_color
6439 high_color
6440 low_color
6441 The color should be specified as a HTML style "#rrggbb" hex value:
6443 {
6445 location => 'A18',
6446 range => 'Sheet2!A2:J2',
6447 type => 'column',
6448 series_color => '#E965E0',
6449 }
6450 Grouped Sparklines
6452 The add_sparkline() worksheet method can be used multiple times to
6453 write as many sparklines as are required in a worksheet.
6454 However, it is sometimes necessary to group contiguous sparklines so
6456 that changes that are applied to one are applied to all. In Excel this
6457 is achieved by selecting a 3D range of cells for the data "range" and a
6458 2D range of cells for the "location".
6459 In Excel::Writer::XLSX, you can simulate this by passing an array refs
6461 of values to "location" and "range":
6462 {
6464 location => [ 'A27', 'A28', 'A29' ],
6465 range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ],
6466 markers => 1,
6467 }
6468 Sparkline examples
6470 See the "sparklines1.pl" and "sparklines2.pl" example programs in the
6471 "examples" directory of the distro.
6472
6474 Tables in Excel are a way of grouping a range of cells into a single
6475 entity that has common formatting or that can be referenced from
6476 formulas. Tables can have column headers, autofilters, total rows,
6477 column formulas and default formatting.
6478 For more information see "An Overview of Excel Tables"
6480 <http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx>.
6481 Note, tables don't work in Excel::Writer::XLSX when set_optimization()
6483 mode in on.
6484 add_table( $row1, $col1, $row2, $col2, { parameter => 'value', ... })
6486 Tables are added to a worksheet using the add_table() method:
6487 $worksheet->add_table( 'B3:F7', { %parameters } );
6489 The data range can be specified in 'A1' or 'row/col' notation (see also
6491 the note about "Cell notation" for more information):
6492 $worksheet->add_table( 'B3:F7' );
6494 # Same as:
6495 $worksheet->add_table( 2, 1, 6, 5 );
6496 The last parameter in add_table() should be a hash ref containing the
6498 parameters that describe the table options and data. The available
6499 parameters are:
6500 data
6502 autofilter
6503 header_row
6504 banded_columns
6505 banded_rows
6506 first_column
6507 last_column
6508 style
6509 total_row
6510 columns
6511 name
6512 The table parameters are detailed below. There are no required
6514 parameters and the hash ref isn't required if no options are specified.
6515 data
6517 The "data" parameter can be used to specify the data in the cells of
6518 the table.
6519 my $data = [
6521 [ 'Apples', 10000, 5000, 8000, 6000 ],
6522 [ 'Pears', 2000, 3000, 4000, 5000 ],
6523 [ 'Bananas', 6000, 6000, 6500, 6000 ],
6524 [ 'Oranges', 500, 300, 200, 700 ],
6525 ];
6527 $worksheet->add_table( 'B3:F7', { data => $data } );
6529 Table data can also be written separately, as an array or individual
6531 cells.
6532 # These two statements are the same as the single statement above.
6534 $worksheet->add_table( 'B3:F7' );
6535 $worksheet->write_col( 'B4', $data );
6536 Writing the cell data separately is occasionally required when you need
6538 to control the "write_*()" method used to populate the cells or if you
6539 wish to tweak the cell formatting.
6540 The "data" structure should be an array ref of array refs holding row
6542 data as shown above.
6543 header_row
6545 The "header_row" parameter can be used to turn on or off the header row
6546 in the table. It is on by default.
6547 $worksheet->add_table( 'B4:F7', { header_row => 0 } ); # Turn header off.
6549 The header row will contain default captions such as "Column 1",
6551 "Column 2", etc. These captions can be overridden using the "columns"
6552 parameter below.
6553 autofilter
6555 The "autofilter" parameter can be used to turn on or off the autofilter
6556 in the header row. It is on by default.
6557 $worksheet->add_table( 'B3:F7', { autofilter => 0 } ); # Turn autofilter off.
6559 The "autofilter" is only shown if the "header_row" is on. Filters
6561 within the table are not supported.
6562 banded_rows
6564 The "banded_rows" parameter can be used to used to create rows of
6565 alternating colour in the table. It is on by default.
6566 $worksheet->add_table( 'B3:F7', { banded_rows => 0 } );
6568 banded_columns
6570 The "banded_columns" parameter can be used to used to create columns of
6571 alternating colour in the table. It is off by default.
6572 $worksheet->add_table( 'B3:F7', { banded_columns => 1 } );
6574 first_column
6576 The "first_column" parameter can be used to highlight the first column
6577 of the table. The type of highlighting will depend on the "style" of
6578 the table. It may be bold text or a different colour. It is off by
6579 default.
6580 $worksheet->add_table( 'B3:F7', { first_column => 1 } );
6582 last_column
6584 The "last_column" parameter can be used to highlight the last column of
6585 the table. The type of highlighting will depend on the "style" of the
6586 table. It may be bold text or a different colour. It is off by default.
6587 $worksheet->add_table( 'B3:F7', { last_column => 1 } );
6589 style
6591 The "style" parameter can be used to set the style of the table.
6592 Standard Excel table format names should be used (with matching
6593 capitalisation):
6594 $worksheet11->add_table(
6596 'B3:F7',
6597 {
6598 data => $data,
6599 style => 'Table Style Light 11',
6600 }
6601 );
6602 The default table style is 'Table Style Medium 9'.
6604 You can also turn the table style off by setting it to 'None':
6606 $worksheet11->add_table( 'B3:F7', { style => 'None' } );
6608 name
6610 By default tables are named "Table1", "Table2", etc. The "name"
6611 parameter can be used to set the name of the table:
6612 $worksheet->add_table( 'B3:F7', { name => 'SalesData' } );
6614 If you override the table name you must ensure that it doesn't clash
6616 with an existing table name and that it follows Excel's requirements
6617 for table names
6618 <http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names>.
6619 If you need to know the name of the table, for example to use it in a
6621 formula, you can get it as follows:
6622 my $table = $worksheet2->add_table( 'B3:F7' );
6624 my $table_name = $table->{_name};
6625 total_row
6627 The "total_row" parameter can be used to turn on the total row in the
6628 last row of a table. It is distinguished from the other rows by a
6629 different formatting and also with dropdown "SUBTOTAL" functions.
6630 $worksheet->add_table( 'B3:F7', { total_row => 1 } );
6632 The default total row doesn't have any captions or functions. These
6634 must by specified via the "columns" parameter below.
6635 columns
6637 The "columns" parameter can be used to set properties for columns
6638 within the table.
6639 The sub-properties that can be set are:
6641 header
6643 formula
6644 total_string
6645 total_function
6646 total_value
6647 format
6648 header_format
6649 The column data must be specified as an array ref of hash refs. For
6651 example to override the default 'Column n' style table headers:
6652 $worksheet->add_table(
6654 'B3:F7',
6655 {
6656 data => $data,
6657 columns => [
6658 { header => 'Product' },
6659 { header => 'Quarter 1' },
6660 { header => 'Quarter 2' },
6661 { header => 'Quarter 3' },
6662 { header => 'Quarter 4' },
6663 ]
6664 }
6665 );
6666 If you don't wish to specify properties for a specific column you pass
6668 an empty hash ref and the defaults will be applied:
6669 ...
6671 columns => [
6672 { header => 'Product' },
6673 { header => 'Quarter 1' },
6674 { }, # Defaults to 'Column 3'.
6675 { header => 'Quarter 3' },
6676 { header => 'Quarter 4' },
6677 ]
6678 ...
6679 Column formulas can by applied using the "formula" column property:
6681 $worksheet8->add_table(
6683 'B3:G7',
6684 {
6685 data => $data,
6686 columns => [
6687 { header => 'Product' },
6688 { header => 'Quarter 1' },
6689 { header => 'Quarter 2' },
6690 { header => 'Quarter 3' },
6691 { header => 'Quarter 4' },
6692 {
6693 header => 'Year',
6694 formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
6695 },
6696 ]
6697 }
6698 );
6699 The Excel 2007 "[#This Row]" and Excel 2010 "@" structural references
6701 are supported within the formula.
6702 As stated above the "total_row" table parameter turns on the "Total"
6704 row in the table but it doesn't populate it with any defaults. Total
6705 captions and functions must be specified via the "columns" property and
6706 the "total_string", "total_function" and "total_value" sub properties:
6707 $worksheet10->add_table(
6709 'B3:F8',
6710 {
6711 data => $data,
6712 total_row => 1,
6713 columns => [
6714 { header => 'Product', total_string => 'Totals' },
6715 { header => 'Quarter 1', total_function => 'sum' },
6716 { header => 'Quarter 2', total_function => 'sum' },
6717 { header => 'Quarter 3', total_function => 'sum' },
6718 { header => 'Quarter 4', total_function => 'sum' },
6719 ]
6720 }
6721 );
6722 The supported totals row "SUBTOTAL" functions are:
6724 average
6726 count_nums
6727 count
6728 max
6729 min
6730 std_dev
6731 sum
6732 var
6733 User defined functions or formulas aren't supported.
6735 It is also possible to set a calculated value for the "total_function"
6737 using the "total_value" sub property. This is only necessary when
6738 creating workbooks for applications that cannot calculate the value of
6739 formulas automatically. This is similar to setting the "value" optional
6740 property in write_formula():
6741 $worksheet10->add_table(
6743 'B3:F8',
6744 {
6745 data => $data,
6746 total_row => 1,
6747 columns => [
6748 { total_string => 'Totals' },
6749 { total_function => 'sum', total_value => 100 },
6750 { total_function => 'sum', total_value => 200 },
6751 { total_function => 'sum', total_value => 100 },
6752 { total_function => 'sum', total_value => 400 },
6753 ]
6754 }
6755 );
6756 Formatting can also be applied to columns, to the column data using
6758 "format" and to the header using "header_format":
6759 my $currency_format = $workbook->add_format( num_format => '$#,##0' );
6761 $worksheet->add_table(
6763 'B3:D8',
6764 {
6765 data => $data,
6766 total_row => 1,
6767 columns => [
6768 { header => 'Product', total_string => 'Totals' },
6769 {
6770 header => 'Quarter 1',
6771 total_function => 'sum',
6772 format => $currency_format,
6773 },
6774 {
6775 header => 'Quarter 2',
6776 header_format => $bold,
6777 total_function => 'sum',
6778 format => $currency_format,
6779 },
6780 ]
6781 }
6782 );
6783 Standard Excel::Writer::XLSX format objects can be used. However, they
6785 should be limited to numerical formats for the columns and simple
6786 formatting like text wrap for the headers. Overriding other table
6787 formatting may produce inconsistent results.
6788
6790 Introduction
6791 The following is a brief introduction to formulas and functions in
6792 Excel and Excel::Writer::XLSX.
6793 A formula is a string that begins with an equals sign:
6795 '=A1+B1'
6797 '=AVERAGE(1, 2, 3)'
6798 The formula can contain numbers, strings, boolean values, cell
6800 references, cell ranges and functions. Named ranges are not supported.
6801 Formulas should be written as they appear in Excel, that is cells and
6802 functions must be in uppercase.
6803 Cells in Excel are referenced using the A1 notation system where the
6805 column is designated by a letter and the row by a number. Columns range
6806 from A to XFD i.e. 0 to 16384, rows range from 1 to 1048576. The
6807 "Excel::Writer::XLSX::Utility" module that is included in the distro
6808 contains helper functions for dealing with A1 notation, for example:
6809 use Excel::Writer::XLSX::Utility;
6811 ( $row, $col ) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
6813 $str = xl_rowcol_to_cell( 1, 2 ); # C2
6814 The Excel "$" notation in cell references is also supported. This
6816 allows you to specify whether a row or column is relative or absolute.
6817 This only has an effect if the cell is copied. The following examples
6818 show relative and absolute values.
6819 '=A1' # Column and row are relative
6821 '=$A1' # Column is absolute and row is relative
6822 '=A$1' # Column is relative and row is absolute
6823 '=$A$1' # Column and row are absolute
6824 Formulas can also refer to cells in other worksheets of the current
6826 workbook. For example:
6827 '=Sheet2!A1'
6829 '=Sheet2!A1:A5'
6830 '=Sheet2:Sheet3!A1'
6831 '=Sheet2:Sheet3!A1:A5'
6832 q{='Test Data'!A1}
6833 q{='Test Data1:Test Data2'!A1}
6834 The sheet reference and the cell reference are separated by "!" the
6836 exclamation mark symbol. If worksheet names contain spaces, commas or
6837 parentheses then Excel requires that the name is enclosed in single
6838 quotes as shown in the last two examples above. In order to avoid using
6839 a lot of escape characters you can use the quote operator "q{}" to
6840 protect the quotes. See "perlop" in the main Perl documentation. Only
6841 valid sheet names that have been added using the add_worksheet() method
6842 can be used in formulas. You cannot reference external workbooks.
6843 The following table lists the operators that are available in Excel's
6845 formulas. The majority of the operators are the same as Perl's,
6846 differences are indicated:
6847 Arithmetic operators:
6849 =====================
6850 Operator Meaning Example
6851 + Addition 1+2
6852 - Subtraction 2-1
6853 * Multiplication 2*3
6854 / Division 1/4
6855 ^ Exponentiation 2^3 # Equivalent to **
6856 - Unary minus -(1+2)
6857 % Percent (Not modulus) 13%
6858 Comparison operators:
6861 =====================
6862 Operator Meaning Example
6863 = Equal to A1 = B1 # Equivalent to ==
6864 <> Not equal to A1 <> B1 # Equivalent to !=
6865 > Greater than A1 > B1
6866 < Less than A1 < B1
6867 >= Greater than or equal to A1 >= B1
6868 <= Less than or equal to A1 <= B1
6869 String operator:
6872 ================
6873 Operator Meaning Example
6874 & Concatenation "Hello " & "World!" # [1]
6875 Reference operators:
6878 ====================
6879 Operator Meaning Example
6880 : Range operator A1:A4 # [2]
6881 , Union operator SUM(1, 2+2, B3) # [3]
6882 Notes:
6885 [1]: Equivalent to "Hello " . "World!" in Perl.
6886 [2]: This range is equivalent to cells A1, A2, A3 and A4.
6887 [3]: The comma behaves like the list separator in Perl.
6888 The range and comma operators can have different symbols in non-English
6890 versions of Excel, see below.
6891 For a general introduction to Excel's formulas and an explanation of
6893 the syntax of the function refer to the Excel help files or the
6894 following:
6895 <http://office.microsoft.com/en-us/assistance/CH062528031033.aspx>.
6896 In most cases a formula in Excel can be used directly in the
6898 "write_formula" method. However, there are a few potential issues and
6899 differences that the user should be aware of. These are explained in
6900 the following sections.
6901 Non US Excel functions and syntax
6903 Excel stores formulas in the format of the US English version,
6904 regardless of the language or locale of the end-user's version of
6905 Excel. Therefore all formula function names written using
6906 Excel::Writer::XLSX must be in English:
6907 worksheet->write_formula('A1', '=SUM(1, 2, 3)'); # OK
6909 worksheet->write_formula('A2', '=SOMME(1, 2, 3)'); # French. Error on load.
6910 Also, formulas must be written with the US style separator/range
6912 operator which is a comma (not semi-colon). Therefore a formula with
6913 multiple values should be written as follows:
6914 worksheet->write_formula('A1', '=SUM(1, 2, 3)'); # OK
6916 worksheet->write_formula('A2', '=SUM(1; 2; 3)'); # Semi-colon. Error on load.
6917 If you have a non-English version of Excel you can use the following
6919 multi-lingual Formula Translator
6920 (<http://en.excel-translator.de/language/>) to help you convert the
6921 formula. It can also replace semi-colons with commas.
6922 Formulas added in Excel 2010 and later
6924 Excel 2010 and later added functions which weren't defined in the
6925 original file specification. These functions are referred to by
6926 Microsoft as future functions. Examples of these functions are "ACOT",
6927 "CHISQ.DIST.RT" , "CONFIDENCE.NORM", "STDEV.P", "STDEV.S" and
6928 "WORKDAY.INTL".
6929 When written using write_formula() these functions need to be fully
6931 qualified with a "_xlfn." (or other) prefix as they are shown the list
6932 below. For example:
6933 worksheet->write_formula('A1', '=_xlfn.STDEV.S(B1:B10)')
6935 They will appear without the prefix in Excel.
6937 The following list is taken from the MS XLSX extensions documentation
6939 on future functions:
6940 <http://msdn.microsoft.com/en-us/library/dd907480%28v=office.12%29.aspx>:
6941 _xlfn.ACOT
6943 _xlfn.ACOTH
6944 _xlfn.AGGREGATE
6945 _xlfn.ARABIC
6946 _xlfn.BASE
6947 _xlfn.BETA.DIST
6948 _xlfn.BETA.INV
6949 _xlfn.BINOM.DIST
6950 _xlfn.BINOM.DIST.RANGE
6951 _xlfn.BINOM.INV
6952 _xlfn.BITAND
6953 _xlfn.BITLSHIFT
6954 _xlfn.BITOR
6955 _xlfn.BITRSHIFT
6956 _xlfn.BITXOR
6957 _xlfn.CEILING.MATH
6958 _xlfn.CEILING.PRECISE
6959 _xlfn.CHISQ.DIST
6960 _xlfn.CHISQ.DIST.RT
6961 _xlfn.CHISQ.INV
6962 _xlfn.CHISQ.INV.RT
6963 _xlfn.CHISQ.TEST
6964 _xlfn.COMBINA
6965 _xlfn.CONFIDENCE.NORM
6966 _xlfn.CONFIDENCE.T
6967 _xlfn.COT
6968 _xlfn.COTH
6969 _xlfn.COVARIANCE.P
6970 _xlfn.COVARIANCE.S
6971 _xlfn.CSC
6972 _xlfn.CSCH
6973 _xlfn.DAYS
6974 _xlfn.DECIMAL
6975 ECMA.CEILING
6976 _xlfn.ERF.PRECISE
6977 _xlfn.ERFC.PRECISE
6978 _xlfn.EXPON.DIST
6979 _xlfn.F.DIST
6980 _xlfn.F.DIST.RT
6981 _xlfn.F.INV
6982 _xlfn.F.INV.RT
6983 _xlfn.F.TEST
6984 _xlfn.FILTERXML
6985 _xlfn.FLOOR.MATH
6986 _xlfn.FLOOR.PRECISE
6987 _xlfn.FORECAST.ETS
6988 _xlfn.FORECAST.ETS.CONFINT
6989 _xlfn.FORECAST.ETS.SEASONALITY
6990 _xlfn.FORECAST.ETS.STAT
6991 _xlfn.FORECAST.LINEAR
6992 _xlfn.FORMULATEXT
6993 _xlfn.GAMMA
6994 _xlfn.GAMMA.DIST
6995 _xlfn.GAMMA.INV
6996 _xlfn.GAMMALN.PRECISE
6997 _xlfn.GAUSS
6998 _xlfn.HYPGEOM.DIST
6999 _xlfn.IFNA
7000 _xlfn.IMCOSH
7001 _xlfn.IMCOT
7002 _xlfn.IMCSC
7003 _xlfn.IMCSCH
7004 _xlfn.IMSEC
7005 _xlfn.IMSECH
7006 _xlfn.IMSINH
7007 _xlfn.IMTAN
7008 _xlfn.ISFORMULA
7009 ISO.CEILING
7010 _xlfn.ISOWEEKNUM
7011 _xlfn.LOGNORM.DIST
7012 _xlfn.LOGNORM.INV
7013 _xlfn.MODE.MULT
7014 _xlfn.MODE.SNGL
7015 _xlfn.MUNIT
7016 _xlfn.NEGBINOM.DIST
7017 NETWORKDAYS.INTL
7018 _xlfn.NORM.DIST
7019 _xlfn.NORM.INV
7020 _xlfn.NORM.S.DIST
7021 _xlfn.NORM.S.INV
7022 _xlfn.NUMBERVALUE
7023 _xlfn.PDURATION
7024 _xlfn.PERCENTILE.EXC
7025 _xlfn.PERCENTILE.INC
7026 _xlfn.PERCENTRANK.EXC
7027 _xlfn.PERCENTRANK.INC
7028 _xlfn.PERMUTATIONA
7029 _xlfn.PHI
7030 _xlfn.POISSON.DIST
7031 _xlfn.QUARTILE.EXC
7032 _xlfn.QUARTILE.INC
7033 _xlfn.QUERYSTRING
7034 _xlfn.RANK.AVG
7035 _xlfn.RANK.EQ
7036 _xlfn.RRI
7037 _xlfn.SEC
7038 _xlfn.SECH
7039 _xlfn.SHEET
7040 _xlfn.SHEETS
7041 _xlfn.SKEW.P
7042 _xlfn.STDEV.P
7043 _xlfn.STDEV.S
7044 _xlfn.T.DIST
7045 _xlfn.T.DIST.2T
7046 _xlfn.T.DIST.RT
7047 _xlfn.T.INV
7048 _xlfn.T.INV.2T
7049 _xlfn.T.TEST
7050 _xlfn.UNICHAR
7051 _xlfn.UNICODE
7052 _xlfn.VAR.P
7053 _xlfn.VAR.S
7054 _xlfn.WEBSERVICE
7055 _xlfn.WEIBULL.DIST
7056 WORKDAY.INTL
7057 _xlfn.XOR
7058 _xlfn.Z.TEST
7059 Using Tables in Formulas
7061 Worksheet tables can be added with Excel::Writer::XLSX using the
7062 add_table() method:
7063 worksheet->add_table('B3:F7', {options});
7065 By default tables are named "Table1", "Table2", etc., in the order that
7067 they are added. However it can also be set by the user using the "name"
7068 parameter:
7069 worksheet->add_table('B3:F7', {'name': 'SalesData'});
7071 If you need to know the name of the table, for example to use it in a
7073 formula, you can get it as follows:
7074 table = worksheet->add_table('B3:F7');
7076 table_name = table->{_name};
7077 When used in a formula a table name such as "TableX" should be referred
7079 to as "TableX[]" (like a Perl array):
7080 worksheet->write_formula('A5', '=VLOOKUP("Sales", Table1[], 2, FALSE');
7082 Dealing with #NAME? errors
7084 If there is an error in the syntax of a formula it is usually displayed
7085 in Excel as "#NAME?". If you encounter an error like this you can debug
7086 it as follows:
7087 1. Ensure the formula is valid in Excel by copying and pasting it into
7089 a cell. Note, this should be done in Excel and not other applications
7090 such as OpenOffice or LibreOffice since they may have slightly
7091 different syntax.
7092 2. Ensure the formula is using comma separators instead of semi-colons,
7093 see "Non US Excel functions and syntax" above.
7094 3. Ensure the formula is in English, see "Non US Excel functions and
7095 syntax" above.
7096 4. Ensure that the formula doesn't contain an Excel 2010+ future
7097 function as listed in "Formulas added in Excel 2010 and later" above.
7098 If it does then ensure that the correct prefix is used.
7099 Finally if you have completed all the previous steps and still get a
7101 "#NAME?" error you can examine a valid Excel file to see what the
7102 correct syntax should be. To do this you should create a valid formula
7103 in Excel and save the file. You can then examine the XML in the
7104 unzipped file.
7105 The following shows how to do that using Linux "unzip" and libxml's
7107 xmllint <http://xmlsoft.org/xmllint.html> to format the XML for
7108 clarity:
7109 $ unzip myfile.xlsx -d myfile
7111 $ xmllint --format myfile/xl/worksheets/sheet1.xml | grep '<f>'
7112 <f>SUM(1, 2, 3)</f>
7114 Formula Results
7116 Excel::Writer::XLSX doesn't calculate the result of a formula and
7117 instead stores the value 0 as the formula result. It then sets a global
7118 flag in the XLSX file to say that all formulas and functions should be
7119 recalculated when the file is opened.
7120 This is the method recommended in the Excel documentation and in
7122 general it works fine with spreadsheet applications. However,
7123 applications that don't have a facility to calculate formulas will only
7124 display the 0 results. Examples of such applications are Excel Viewer,
7125 PDF Converters, and some mobile device applications.
7126 If required, it is also possible to specify the calculated result of
7128 the formula using the optional last "value" parameter in
7129 "write_formula":
7130 worksheet->write_formula('A1', '=2+2', num_format, 4);
7132 The "value" parameter can be a number, a string, a boolean sting
7134 ('TRUE' or 'FALSE') or one of the following Excel error codes:
7135 #DIV/0!
7137 #N/A
7138 #NAME?
7139 #NULL!
7140 #NUM!
7141 #REF!
7142 #VALUE!
7143 It is also possible to specify the calculated result of an array
7145 formula created with "write_array_formula":
7146 # Specify the result for a single cell range.
7148 worksheet->write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}', format, 2005);
7149 However, using this parameter only writes a single value to the upper
7151 left cell in the result array. For a multi-cell array formula where the
7152 results are required, the other result values can be specified by using
7153 write_number() to write to the appropriate cell:
7154 # Specify the results for a multi cell range.
7156 worksheet->write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}', format, 15);
7157 worksheet->write_number('A2', 12, format);
7158 worksheet->write_number('A3', 14, format);
7159
7161 An Excel "xlsm" file is exactly the same as a "xlsx" file except that
7162 is includes an additional "vbaProject.bin" file which contains
7163 functions and/or macros. Excel uses a different extension to
7164 differentiate between the two file formats since files containing
7165 macros are usually subject to additional security checks.
7166 The "vbaProject.bin" file is a binary OLE COM container. This was the
7168 format used in older "xls" versions of Excel prior to Excel 2007.
7169 Unlike all of the other components of an xlsx/xlsm file the data isn't
7170 stored in XML format. Instead the functions and macros as stored as
7171 pre-parsed binary format. As such it wouldn't be feasible to define
7172 macros and create a "vbaProject.bin" file from scratch (at least not in
7173 the remaining lifespan and interest levels of the author).
7174 Instead a workaround is used to extract "vbaProject.bin" files from
7176 existing xlsm files and then add these to Excel::Writer::XLSX files.
7177 The extract_vba utility
7179 The "extract_vba" utility is used to extract the "vbaProject.bin"
7180 binary from an Excel 2007+ xlsm file. The utility is included in the
7181 Excel::Writer::XLSX bin directory and is also installed as a standalone
7182 executable file:
7183 $ extract_vba macro_file.xlsm
7185 Extracted: vbaProject.bin
7186 Adding the VBA macros to a Excel::Writer::XLSX file
7188 Once the "vbaProject.bin" file has been extracted it can be added to
7189 the Excel::Writer::XLSX workbook using the add_vba_project() method:
7190 $workbook->add_vba_project( './vbaProject.bin' );
7192 If the VBA file contains functions you can then refer to them in
7194 calculations using "write_formula":
7195 $worksheet->write_formula( 'A1', '=MyMortgageCalc(200000, 25)' );
7197 Excel files that contain functions and macros should use an "xlsm"
7199 extension or else Excel will complain and possibly not open the file:
7200 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
7202 It is also possible to assign a macro to a button that is inserted into
7204 a worksheet using the insert_button() method:
7205 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
7207 ...
7208 $workbook->add_vba_project( './vbaProject.bin' );
7209 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
7211 It may be necessary to specify a more explicit macro name prefixed by
7213 the workbook VBA name as follows:
7214 $worksheet->insert_button( 'C2', { macro => 'ThisWorkbook.my_macro' } );
7216 See the "macros.pl" from the examples directory for a working example.
7218 Note: Button is the only VBA Control supported by Excel::Writer::XLSX.
7220 Due to the large effort in implementation (1+ man months) it is
7221 unlikely that any other form elements will be added in the future.
7222 Setting the VBA codenames
7224 VBA macros generally refer to workbook and worksheet objects. If the
7225 VBA codenames aren't specified then Excel::Writer::XLSX will use the
7226 Excel defaults of "ThisWorkbook" and "Sheet1", "Sheet2" etc.
7227 If the macro uses other codenames you can set them using the workbook
7229 and worksheet set_vba_name() methods as follows:
7230 $workbook->set_vba_name( 'MyWorkbook' );
7232 $worksheet->set_vba_name( 'MySheet' );
7233 You can find the names that are used in the VBA editor or by unzipping
7235 the "xlsm" file and grepping the files. The following shows how to do
7236 that using libxml's xmllint <http://xmlsoft.org/xmllint.html> to format
7237 the XML for clarity:
7238 $ unzip myfile.xlsm -d myfile
7240 $ xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"
7241 <workbookPr codeName="MyWorkbook" defaultThemeVersion="124226"/>
7243 <sheetPr codeName="MySheet"/>
7244 Note: This step is particularly important for macros created with non-
7246 English versions of Excel.
7247 What to do if it doesn't work
7249 This feature should be considered experimental and there is no
7250 guarantee that it will work in all cases. Some effort may be required
7251 and some knowledge of VBA will certainly help. If things don't work out
7252 here are some things to try:
7253 • Start with a simple macro file, ensure that it works and then add
7255 complexity.
7256 • Try to extract the macros from an Excel 2007 file. The method
7258 should work with macros from later versions (it was also tested
7259 with Excel 2010 macros). However there may be features in the macro
7260 files of more recent version of Excel that aren't backward
7261 compatible.
7262 • Check the code names that macros use to refer to the workbook and
7264 worksheets (see the previous section above). In general VBA uses a
7265 code name of "ThisWorkbook" to refer to the current workbook and
7266 the sheet name (such as "Sheet1") to refer to the worksheets. These
7267 are the defaults used by Excel::Writer::XLSX. If the macro uses
7268 other names then you can specify these using the workbook and
7269 worksheet set_vba_name() methods:
7270 $workbook>set_vba_name( 'MyWorkbook' );
7272 $worksheet->set_vba_name( 'MySheet' );
7273
7275 See Excel::Writer::XLSX::Examples for a full list of examples.
7276 Example 1
7278 The following example shows some of the basic features of
7279 Excel::Writer::XLSX.
7280 #!/usr/bin/perl -w
7282 use strict;
7284 use Excel::Writer::XLSX;
7285 # Create a new workbook called simple.xlsx and add a worksheet
7287 my $workbook = Excel::Writer::XLSX->new( 'simple.xlsx' );
7288 my $worksheet = $workbook->add_worksheet();
7289 # The general syntax is write($row, $column, $token). Note that row and
7291 # column are zero indexed
7292 # Write some text
7294 $worksheet->write( 0, 0, 'Hi Excel!' );
7295 # Write some numbers
7298 $worksheet->write( 2, 0, 3 );
7299 $worksheet->write( 3, 0, 3.00000 );
7300 $worksheet->write( 4, 0, 3.00001 );
7301 $worksheet->write( 5, 0, 3.14159 );
7302 # Write some formulas
7305 $worksheet->write( 7, 0, '=A3 + A6' );
7306 $worksheet->write( 8, 0, '=IF(A5>3,"Yes", "No")' );
7307 # Write a hyperlink
7310 my $hyperlink_format = $workbook->add_format(
7311 color => 'blue',
7312 underline => 1,
7313 );
7314 $worksheet->write( 10, 0, 'http://www.perl.com/', $hyperlink_format );
7316 $workbook->close();
7318 Example 2
7320 The following is a general example which demonstrates some features of
7321 working with multiple worksheets.
7322 #!/usr/bin/perl -w
7324 use strict;
7326 use Excel::Writer::XLSX;
7327 # Create a new Excel workbook
7329 my $workbook = Excel::Writer::XLSX->new( 'regions.xlsx' );
7330 # Add some worksheets
7332 my $north = $workbook->add_worksheet( 'North' );
7333 my $south = $workbook->add_worksheet( 'South' );
7334 my $east = $workbook->add_worksheet( 'East' );
7335 my $west = $workbook->add_worksheet( 'West' );
7336 # Add a Format
7338 my $format = $workbook->add_format();
7339 $format->set_bold();
7340 $format->set_color( 'blue' );
7341 # Add a caption to each worksheet
7343 for my $worksheet ( $workbook->sheets() ) {
7344 $worksheet->write( 0, 0, 'Sales', $format );
7345 }
7346 # Write some data
7348 $north->write( 0, 1, 200000 );
7349 $south->write( 0, 1, 100000 );
7350 $east->write( 0, 1, 150000 );
7351 $west->write( 0, 1, 100000 );
7352 # Set the active worksheet
7354 $south->activate();
7355 # Set the width of the first column
7357 $south->set_column( 0, 0, 20 );
7358 # Set the active cell
7360 $south->set_selection( 0, 1 );
7361 $workbook->close();
7363 Example 3
7365 Example of how to add conditional formatting to an Excel::Writer::XLSX
7366 file. The example below highlights cells that have a value greater than
7367 or equal to 50 in red and cells below that value in green.
7368 #!/usr/bin/perl
7370 use strict;
7372 use warnings;
7373 use Excel::Writer::XLSX;
7374 my $workbook = Excel::Writer::XLSX->new( 'conditional_format.xlsx' );
7376 my $worksheet = $workbook->add_worksheet();
7377 # This example below highlights cells that have a value greater than or
7380 # equal to 50 in red and cells below that value in green.
7381 # Light red fill with dark red text.
7383 my $format1 = $workbook->add_format(
7384 bg_color => '#FFC7CE',
7385 color => '#9C0006',
7386 );
7388 # Green fill with dark green text.
7390 my $format2 = $workbook->add_format(
7391 bg_color => '#C6EFCE',
7392 color => '#006100',
7393 );
7395 # Some sample data to run the conditional formatting against.
7397 my $data = [
7398 [ 34, 72, 38, 30, 75, 48, 75, 66, 84, 86 ],
7399 [ 6, 24, 1, 84, 54, 62, 60, 3, 26, 59 ],
7400 [ 28, 79, 97, 13, 85, 93, 93, 22, 5, 14 ],
7401 [ 27, 71, 40, 17, 18, 79, 90, 93, 29, 47 ],
7402 [ 88, 25, 33, 23, 67, 1, 59, 79, 47, 36 ],
7403 [ 24, 100, 20, 88, 29, 33, 38, 54, 54, 88 ],
7404 [ 6, 57, 88, 28, 10, 26, 37, 7, 41, 48 ],
7405 [ 52, 78, 1, 96, 26, 45, 47, 33, 96, 36 ],
7406 [ 60, 54, 81, 66, 81, 90, 80, 93, 12, 55 ],
7407 [ 70, 5, 46, 14, 71, 19, 66, 36, 41, 21 ],
7408 ];
7409 my $caption = 'Cells with values >= 50 are in light red. '
7411 . 'Values < 50 are in light green';
7412 # Write the data.
7414 $worksheet->write( 'A1', $caption );
7415 $worksheet->write_col( 'B3', $data );
7416 # Write a conditional format over a range.
7418 $worksheet->conditional_formatting( 'B3:K12',
7419 {
7420 type => 'cell',
7421 criteria => '>=',
7422 value => 50,
7423 format => $format1,
7424 }
7425 );
7426 # Write another conditional format over the same range.
7428 $worksheet->conditional_formatting( 'B3:K12',
7429 {
7430 type => 'cell',
7431 criteria => '<',
7432 value => 50,
7433 format => $format2,
7434 }
7435 );
7436 $workbook->close();
7438 Example 4
7440 The following is a simple example of using functions.
7441 #!/usr/bin/perl -w
7443 use strict;
7445 use Excel::Writer::XLSX;
7446 # Create a new workbook and add a worksheet
7448 my $workbook = Excel::Writer::XLSX->new( 'stats.xlsx' );
7449 my $worksheet = $workbook->add_worksheet( 'Test data' );
7450 # Set the column width for columns 1
7452 $worksheet->set_column( 0, 0, 20 );
7453 # Create a format for the headings
7456 my $format = $workbook->add_format();
7457 $format->set_bold();
7458 # Write the sample data
7461 $worksheet->write( 0, 0, 'Sample', $format );
7462 $worksheet->write( 0, 1, 1 );
7463 $worksheet->write( 0, 2, 2 );
7464 $worksheet->write( 0, 3, 3 );
7465 $worksheet->write( 0, 4, 4 );
7466 $worksheet->write( 0, 5, 5 );
7467 $worksheet->write( 0, 6, 6 );
7468 $worksheet->write( 0, 7, 7 );
7469 $worksheet->write( 0, 8, 8 );
7470 $worksheet->write( 1, 0, 'Length', $format );
7472 $worksheet->write( 1, 1, 25.4 );
7473 $worksheet->write( 1, 2, 25.4 );
7474 $worksheet->write( 1, 3, 24.8 );
7475 $worksheet->write( 1, 4, 25.0 );
7476 $worksheet->write( 1, 5, 25.3 );
7477 $worksheet->write( 1, 6, 24.9 );
7478 $worksheet->write( 1, 7, 25.2 );
7479 $worksheet->write( 1, 8, 24.8 );
7480 # Write some statistical functions
7482 $worksheet->write( 4, 0, 'Count', $format );
7483 $worksheet->write( 4, 1, '=COUNT(B1:I1)' );
7484 $worksheet->write( 5, 0, 'Sum', $format );
7486 $worksheet->write( 5, 1, '=SUM(B2:I2)' );
7487 $worksheet->write( 6, 0, 'Average', $format );
7489 $worksheet->write( 6, 1, '=AVERAGE(B2:I2)' );
7490 $worksheet->write( 7, 0, 'Min', $format );
7492 $worksheet->write( 7, 1, '=MIN(B2:I2)' );
7493 $worksheet->write( 8, 0, 'Max', $format );
7495 $worksheet->write( 8, 1, '=MAX(B2:I2)' );
7496 $worksheet->write( 9, 0, 'Standard Deviation', $format );
7498 $worksheet->write( 9, 1, '=STDEV(B2:I2)' );
7499 $worksheet->write( 10, 0, 'Kurtosis', $format );
7501 $worksheet->write( 10, 1, '=KURT(B2:I2)' );
7502 $workbook->close();
7504 Example 5
7506 The following example converts a tab separated file called "tab.txt"
7507 into an Excel file called "tab.xlsx".
7508 #!/usr/bin/perl -w
7510 use strict;
7512 use Excel::Writer::XLSX;
7513 open( TABFILE, 'tab.txt' ) or die "tab.txt: $!";
7515 my $workbook = Excel::Writer::XLSX->new( 'tab.xlsx' );
7517 my $worksheet = $workbook->add_worksheet();
7518 # Row and column are zero indexed
7520 my $row = 0;
7521 while ( <TABFILE> ) {
7523 chomp;
7524 # Split on single tab
7526 my @fields = split( '\t', $_ );
7527 my $col = 0;
7529 for my $token ( @fields ) {
7530 $worksheet->write( $row, $col, $token );
7531 $col++;
7532 }
7533 $row++;
7534 }
7535 $workbook->close();
7537 NOTE: This is a simple conversion program for illustrative purposes
7539 only. For converting a CSV or Tab separated or any other type of
7540 delimited text file to Excel I recommend the more rigorous csv2xls
7541 program that is part of H.Merijn Brand's Text::CSV_XS module distro.
7542 See the examples/csv2xls link here:
7544 <http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>.
7545 Additional Examples
7547 The following is a description of the example files that are provided
7548 in the standard Excel::Writer::XLSX distribution. They demonstrate the
7549 different features and options of the module. See
7550 Excel::Writer::XLSX::Examples for more details.
7551 Getting started
7553 ===============
7554 a_simple.pl A simple demo of some of the features.
7555 bug_report.pl A template for submitting bug reports.
7556 demo.pl A demo of some of the available features.
7557 formats.pl All the available formatting on several worksheets.
7558 regions.pl A simple example of multiple worksheets.
7559 stats.pl Basic formulas and functions.
7560 Intermediate
7563 ============
7564 autofit.pl Examples of simulated worksheet autofit.
7565 autofilter.pl Examples of worksheet autofilters.
7566 array_formula.pl Examples of how to write array formulas.
7567 cgi.pl A simple CGI program.
7568 chart_area.pl A demo of area style charts.
7569 chart_bar.pl A demo of bar (vertical histogram) style charts.
7570 chart_column.pl A demo of column (histogram) style charts.
7571 chart_line.pl A demo of line style charts.
7572 chart_pie.pl A demo of pie style charts.
7573 chart_doughnut.pl A demo of doughnut style charts.
7574 chart_radar.pl A demo of radar style charts.
7575 chart_scatter.pl A demo of scatter style charts.
7576 chart_secondary_axis.pl A demo of a line chart with a secondary axis.
7577 chart_combined.pl A demo of a combined column and line chart.
7578 chart_pareto.pl A demo of a combined Pareto chart.
7579 chart_stock.pl A demo of stock style charts.
7580 chart_data_table.pl A demo of a chart with a data table on the axis.
7581 chart_data_tools.pl A demo of charts with data highlighting options.
7582 chart_data_labels.pl A demo of standard and custom chart data labels.
7583 chart_clustered.pl A demo of a chart with a clustered axis.
7584 chart_styles.pl A demo of the available chart styles.
7585 chart_gauge.pl A demo of a gauge style chart.
7586 colors.pl A demo of the colour palette and named colours.
7587 comments1.pl Add comments to worksheet cells.
7588 comments2.pl Add comments with advanced options.
7589 conditional_format.pl Add conditional formats to a range of cells.
7590 data_validate.pl An example of data validation and dropdown lists.
7591 date_time.pl Write dates and times with write_date_time().
7592 defined_name.pl Example of how to create defined names.
7593 diag_border.pl A simple example of diagonal cell borders.
7594 dynamic_arrays.pl Example of using new Excel 365 dynamic functions.
7595 filehandle.pl Examples of working with filehandles.
7596 headers.pl Examples of worksheet headers and footers.
7597 hide_row_col.pl Example of hiding rows and columns.
7598 hide_sheet.pl Simple example of hiding a worksheet.
7599 hyperlink1.pl Shows how to create web hyperlinks.
7600 hyperlink2.pl Examples of internal and external hyperlinks.
7601 indent.pl An example of cell indentation.
7602 ignore_errors.pl An example of turning off worksheet cells errors/warnings.
7603 lambda.pl Example of using the Excel 365 LAMBDA() function.
7604 macros.pl An example of adding macros from an existing file.
7605 merge1.pl A simple example of cell merging.
7606 merge2.pl A simple example of cell merging with formatting.
7607 merge3.pl Add hyperlinks to merged cells.
7608 merge4.pl An advanced example of merging with formatting.
7609 merge5.pl An advanced example of merging with formatting.
7610 merge6.pl An example of merging with Unicode strings.
7611 mod_perl1.pl A simple mod_perl 1 program.
7612 mod_perl2.pl A simple mod_perl 2 program.
7613 outline.pl An example of outlines and grouping.
7614 outline_collapsed.pl An example of collapsed outlines.
7615 panes.pl An example of how to create panes.
7616 properties.pl Add document properties to a workbook.
7617 protection.pl Example of cell locking and formula hiding.
7618 rich_strings.pl Example of strings with multiple formats.
7619 right_to_left.pl Change default sheet direction to right to left.
7620 sales.pl An example of a simple sales spreadsheet.
7621 shape1.pl Insert shapes in worksheet.
7622 shape2.pl Insert shapes in worksheet. With properties.
7623 shape3.pl Insert shapes in worksheet. Scaled.
7624 shape4.pl Insert shapes in worksheet. With modification.
7625 shape5.pl Insert shapes in worksheet. With connections.
7626 shape6.pl Insert shapes in worksheet. With connections.
7627 shape7.pl Insert shapes in worksheet. One to many connections.
7628 shape8.pl Insert shapes in worksheet. One to many connections.
7629 shape_all.pl Demo of all the available shape and connector types.
7630 sparklines1.pl Simple sparklines demo.
7631 sparklines2.pl Sparklines demo showing formatting options.
7632 stats_ext.pl Same as stats.pl with external references.
7633 stocks.pl Demonstrates conditional formatting.
7634 watermark.pl Example of how to set a watermark image for a worksheet.
7635 background.pl Example of how to set the background image for a worksheet.
7636 tab_colors.pl Example of how to set worksheet tab colours.
7637 tables.pl Add Excel tables to a worksheet.
7638 write_handler1.pl Example of extending the write() method. Step 1.
7639 write_handler2.pl Example of extending the write() method. Step 2.
7640 write_handler3.pl Example of extending the write() method. Step 3.
7641 write_handler4.pl Example of extending the write() method. Step 4.
7642 write_to_scalar.pl Example of writing an Excel file to a Perl scalar.
7643 Unicode
7645 =======
7646 unicode_2022_jp.pl Japanese: ISO-2022-JP.
7647 unicode_8859_11.pl Thai: ISO-8859_11.
7648 unicode_8859_7.pl Greek: ISO-8859_7.
7649 unicode_big5.pl Chinese: BIG5.
7650 unicode_cp1251.pl Russian: CP1251.
7651 unicode_cp1256.pl Arabic: CP1256.
7652 unicode_cyrillic.pl Russian: Cyrillic.
7653 unicode_koi8r.pl Russian: KOI8-R.
7654 unicode_polish_utf8.pl Polish : UTF8.
7655 unicode_shift_jis.pl Japanese: Shift JIS.
7656
7658 The following limits are imposed by Excel 2007+:
7659 Description Limit
7661 -------------------------------------- ------
7662 Maximum number of chars in a string 32,767
7663 Maximum number of columns 16,384
7664 Maximum number of rows 1,048,576
7665 Maximum chars in a sheet name 31
7666 Maximum chars in a header/footer 254
7667 Maximum characters in hyperlink url (1) 2079
7669 Maximum number of unique hyperlinks (2) 65,530
7670 (1) Versions of Excel prior to Excel 2015 limited hyperlink links and
7672 anchor/locations to 255 characters each. Versions after that support
7673 urls up to 2079 characters. Excel::Writer::XLSX versions >= 1.0.2
7674 support the new longer limit by default.
7675 (2) Per worksheet. Excel allows a greater number of non-unique
7677 hyperlinks if they are contiguous and can be grouped into a single
7678 range. This isn't supported by Excel::Writer::XLSX.
7679
7681 <http://search.cpan.org/search?dist=Archive-Zip/>.
7682 Perl 5.8.2.
7684
7686 "Spreadsheet::WriteExcel" was written to optimise speed and reduce
7687 memory usage. However, these design goals meant that it wasn't easy to
7688 implement features that many users requested such as writing formatting
7689 and data separately.
7690 As a result "Excel::Writer::XLSX" takes a different design approach and
7692 holds a lot more data in memory so that it is functionally more
7693 flexible.
7694 The effect of this is that Excel::Writer::XLSX is about 30% slower than
7696 Spreadsheet::WriteExcel and uses 5 times more memory.
7697 In addition the extended row and column ranges in Excel 2007+ mean that
7699 it is possible to run out of memory creating large files. This was
7700 almost never an issue with Spreadsheet::WriteExcel.
7701 This memory usage can be reduced almost completely by using the
7703 Workbook set_optimization() method:
7704 $workbook->set_optimization();
7706 The trade-off is that you won't be able to take advantage of features
7708 that manipulate cell data after it is written. One such feature is
7709 Tables.
7710
7712 The latest version of this module is always available at:
7713 <http://search.cpan.org/search?dist=Excel-Writer-XLSX/>.
7714
7716 The module can be installed using the standard Perl procedure:
7717 perl Makefile.PL
7719 make
7720 make test
7721 make install # You may need to be sudo/root
7722
7724 Filename required by Excel::Writer::XLSX->new()
7725 A filename must be given in the constructor.
7726 Can't open filename. It may be in use or protected.
7728 The file cannot be opened for writing. The directory that you are
7729 writing to may be protected or the file may be in use by another
7730 program.
7731 Can't call method "XXX" on an undefined value at someprogram.pl.
7733 On Windows this is usually caused by the file that you are trying
7734 to create clashing with a version that is already open and locked
7735 by Excel.
7736 The file you are trying to open 'file.xls' is in a different format
7738 than specified by the file extension.
7739 This warning occurs when you create an XLSX file but give it an xls
7740 extension.
7741
7743 Depending on your requirements, background and general sensibilities
7744 you may prefer one of the following methods of getting data into Excel:
7745 • Spreadsheet::WriteExcel
7747 This module is the precursor to Excel::Writer::XLSX and uses the
7749 same interface. It produces files in the Excel Biff xls format that
7750 was used in Excel versions 97-2003. These files can still be read
7751 by Excel 2007 but have some limitations in relation to the number
7752 of rows and columns that the format supports.
7753 Spreadsheet::WriteExcel.
7755 • Win32::OLE module and office automation
7757 This requires a Windows platform and an installed copy of Excel.
7759 This is the most powerful and complete method for interfacing with
7760 Excel.
7761 Win32::OLE
7763 • CSV, comma separated variables or text
7765 Excel will open and automatically convert files with a "csv"
7767 extension.
7768 To create CSV files refer to the Text::CSV_XS module.
7770 • DBI with DBD::ADO or DBD::ODBC
7772 Excel files contain an internal index table that allows them to act
7774 like a database file. Using one of the standard Perl database
7775 modules you can connect to an Excel file as a database.
7776 For other Perl-Excel modules try the following search:
7778 <http://search.cpan.org/search?mode=module&query=excel>.
7779
7781 To read data from Excel files try:
7782 • Spreadsheet::ParseXLSX
7784 A module for reading data from XLSX files. It also imports most, if
7786 not all, of the metadata to be found in Excel XLSX files. As its
7787 author describes it: "This module is an adaptor for
7788 Spreadsheet::ParseExcel that reads XLSX files. For documentation
7789 about the various data that you can retrieve from these classes,
7790 please see Spreadsheet::ParseExcel,
7791 Spreadsheet::ParseExcel::Workbook,
7792 Spreadsheet::ParseExcel::Worksheet, and
7793 Spreadsheet::ParseExcel::Cell."
7794 • Spreadsheet::XLSX
7796 A module for reading formatted or unformatted data from XLSX files.
7798 Spreadsheet::XLSX
7800 • SimpleXlsx
7802 A lightweight module for reading data from XLSX files.
7804 SimpleXlsx
7806 • Spreadsheet::ParseExcel
7808 This module can read data from an Excel XLS file but it doesn't
7810 support the XLSX format.
7811 Spreadsheet::ParseExcel
7813 • Win32::OLE module and office automation (reading)
7815 See above.
7817 • DBI with DBD::ADO or DBD::ODBC.
7819 See above.
7821 For other Perl-Excel modules try the following search:
7823 <http://search.cpan.org/search?mode=module&query=excel>.
7824
7826 • Memory usage is very high for large worksheets.
7827 If you run out of memory creating large worksheets use the
7829 set_optimization() method. See "SPEED AND MEMORY USAGE" for more
7830 information.
7831 • Perl packaging programs can't find chart modules.
7833 When using Excel::Writer::XLSX charts with Perl packagers such as
7835 PAR or Cava you should explicitly include the chart that you are
7836 trying to create in your "use" statements. This isn't a bug as such
7837 but it might help someone from banging their head off a wall:
7838 ...
7840 use Excel::Writer::XLSX;
7841 use Excel::Writer::XLSX::Chart::Column;
7842 ...
7843 If you wish to submit a bug report run the "bug_report.pl" program in
7845 the "examples" directory of the distro.
7846 The bug tracker is on Github:
7848 <https://github.com/jmcnamara/excel-writer-xlsx/issues>.
7849
7851 The Excel::Writer::XLSX source code in host on github:
7852 <http://github.com/jmcnamara/excel-writer-xlsx>.
7853
7855 If you'd care to donate to the Excel::Writer::XLSX project or sponsor a
7856 new feature, you can do so via PayPal: <http://tinyurl.com/7ayes>.
7857
7859 Spreadsheet::WriteExcel:
7860 <http://search.cpan.org/dist/Spreadsheet-WriteExcel>.
7861 Spreadsheet::ParseExcel:
7863 <http://search.cpan.org/dist/Spreadsheet-ParseExcel>.
7864 Spreadsheet::XLSX: <http://search.cpan.org/dist/Spreadsheet-XLSX>.
7866
7868 The following people contributed to the debugging, testing or
7869 enhancement of Excel::Writer::XLSX:
7870 Rob Messer of IntelliSurvey gave me the initial prompt to port
7872 Spreadsheet::WriteExcel to the XLSX format. IntelliSurvey
7873 (<http://www.intellisurvey.com>) also sponsored large files
7874 optimisations and the charting feature.
7875 Bariatric Advantage (<http://www.bariatricadvantage.com>) sponsored
7877 work on chart formatting.
7878 Eric Johnson provided the ability to use secondary axes with charts.
7880 Thanks to Foxtons (<http://foxtons.co.uk>) for sponsoring this work.
7881 BuildFax (<http://www.buildfax.com>) sponsored the Tables feature and
7883 the Chart point formatting feature.
7884
7886 Because this software is licensed free of charge, there is no warranty
7887 for the software, to the extent permitted by applicable law. Except
7888 when otherwise stated in writing the copyright holders and/or other
7889 parties provide the software "as is" without warranty of any kind,
7890 either expressed or implied, including, but not limited to, the implied
7891 warranties of merchantability and fitness for a particular purpose. The
7892 entire risk as to the quality and performance of the software is with
7893 you. Should the software prove defective, you assume the cost of all
7894 necessary servicing, repair, or correction.
7895 In no event unless required by applicable law or agreed to in writing
7897 will any copyright holder, or any other party who may modify and/or
7898 redistribute the software as permitted by the above licence, be liable
7899 to you for damages, including any general, special, incidental, or
7900 consequential damages arising out of the use or inability to use the
7901 software (including but not limited to loss of data or data being
7902 rendered inaccurate or losses sustained by you or third parties or a
7903 failure of the software to operate with any other software), even if
7904 such holder or other party has been advised of the possibility of such
7905 damages.
7906
7908 The Perl Artistic Licence <http://dev.perl.org/licenses/artistic.html>.
7909
7911 John McNamara jmcnamara@cpan.org
7912
7914 Copyright MM-MMXXIII, John McNamara.
7915 All Rights Reserved. This module is free software. It may be used,
7917 redistributed and/or modified under the same terms as Perl itself.
7918perl v5.38.0 2023-07-20 Excel::Writer::XLSX(3)