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 The XLSX format is the Office Open XML (OOXML) format used by Excel
42 2007 and later.
43 Multiple worksheets can be added to a workbook and formatting can be
45 applied to cells. Text, numbers, and formulas can be written to the
46 cells.
47 This module cannot, as yet, be used to write to an existing Excel XLSX
49 file.
50
52 "Excel::Writer::XLSX" uses the same interface as the
53 Spreadsheet::WriteExcel module which produces an Excel file in binary
54 XLS format.
55 Excel::Writer::XLSX supports all of the features of
57 Spreadsheet::WriteExcel and in some cases has more functionality. For
58 more details see "Compatibility with Spreadsheet::WriteExcel".
59 The main advantage of the XLSX format over the XLS format is that it
61 allows a larger number of rows and columns in a worksheet. The XLSX
62 file format also produces much smaller files than the XLS file format.
63
65 Excel::Writer::XLSX tries to provide an interface to as many of Excel's
66 features as possible. As a result there is a lot of documentation to
67 accompany the interface and it can be difficult at first glance to see
68 what it important and what is not. So for those of you who prefer to
69 assemble Ikea furniture first and then read the instructions, here are
70 four easy steps:
71 1. Create a new Excel workbook (i.e. file) using "new()".
73 2. Add a worksheet to the new workbook using "add_worksheet()".
75 3. Write to the worksheet using "write()".
77 4. "close()" the file.
79 Like this:
81 use Excel::Writer::XLSX; # Step 0
83 my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' ); # Step 1
85 $worksheet = $workbook->add_worksheet(); # Step 2
86 $worksheet->write( 'A1', 'Hi Excel!' ); # Step 3
87 $workbook->close(); # Step 4
89 This will create an Excel file called "perl.xlsx" with a single
91 worksheet and the text 'Hi Excel!' in the relevant cell. And that's it.
92 Okay, so there is actually a zeroth step as well, but "use module" goes
93 without saying. There are many examples that come with the distribution
94 and which you can use to get you started. See "EXAMPLES".
95 Those of you who read the instructions first and assemble the furniture
97 afterwards will know how to proceed. ;-)
98
100 The Excel::Writer::XLSX module provides an object oriented interface to
101 a new Excel workbook. The following methods are available through a new
102 workbook.
103 new()
105 add_worksheet()
106 add_format()
107 add_chart()
108 add_shape()
109 add_vba_project()
110 set_vba_name()
111 close()
112 set_properties()
113 set_custom_property()
114 define_name()
115 set_tempdir()
116 set_custom_color()
117 sheets()
118 get_worksheet_by_name()
119 set_1904()
120 set_optimization()
121 set_calc_mode()
122 get_default_url_format()
123 If you are unfamiliar with object oriented interfaces or the way that
125 they are implemented in Perl have a look at "perlobj" and "perltoot" in
126 the main Perl documentation.
127 new()
129 A new Excel workbook is created using the "new()" constructor which
130 accepts either a filename or a filehandle as a parameter. The following
131 example creates a new Excel file based on a filename:
132 my $workbook = Excel::Writer::XLSX->new( 'filename.xlsx' );
134 my $worksheet = $workbook->add_worksheet();
135 $worksheet->write( 0, 0, 'Hi Excel!' );
136 $workbook->close();
137 Here are some other examples of using "new()" with filenames:
139 my $workbook1 = Excel::Writer::XLSX->new( $filename );
141 my $workbook2 = Excel::Writer::XLSX->new( '/tmp/filename.xlsx' );
142 my $workbook3 = Excel::Writer::XLSX->new( "c:\\tmp\\filename.xlsx" );
143 my $workbook4 = Excel::Writer::XLSX->new( 'c:\tmp\filename.xlsx' );
144 The last two examples demonstrates how to create a file on DOS or
146 Windows where it is necessary to either escape the directory separator
147 "\" or to use single quotes to ensure that it isn't interpolated. For
148 more information see "perlfaq5: Why can't I use "C:\temp\foo" in DOS
149 paths?".
150 It is recommended that the filename uses the extension ".xlsx" rather
152 than ".xls" since the latter causes an Excel warning when used with the
153 XLSX format.
154 The "new()" constructor returns a Excel::Writer::XLSX object that you
156 can use to add worksheets and store data. It should be noted that
157 although "my" is not specifically required it defines the scope of the
158 new workbook variable and, in the majority of cases, ensures that the
159 workbook is closed properly without explicitly calling the "close()"
160 method.
161 If the file cannot be created, due to file permissions or some other
163 reason, "new" will return "undef". Therefore, it is good practice to
164 check the return value of "new" before proceeding. As usual the Perl
165 variable $! will be set if there is a file creation error. You will
166 also see one of the warning messages detailed in "DIAGNOSTICS":
167 my $workbook = Excel::Writer::XLSX->new( 'protected.xlsx' );
169 die "Problems creating new Excel file: $!" unless defined $workbook;
170 You can also pass a valid filehandle to the "new()" constructor. For
172 example in a CGI program you could do something like this:
173 binmode( STDOUT );
175 my $workbook = Excel::Writer::XLSX->new( \*STDOUT );
176 The requirement for "binmode()" is explained below.
178 See also, the "cgi.pl" program in the "examples" directory of the
180 distro.
181 In "mod_perl" programs where you will have to do something like the
183 following:
184 # mod_perl 1
186 ...
187 tie *XLSX, 'Apache';
188 binmode( XLSX );
189 my $workbook = Excel::Writer::XLSX->new( \*XLSX );
190 ...
191 # mod_perl 2
193 ...
194 tie *XLSX => $r; # Tie to the Apache::RequestRec object
195 binmode( *XLSX );
196 my $workbook = Excel::Writer::XLSX->new( \*XLSX );
197 ...
198 See also, the "mod_perl1.pl" and "mod_perl2.pl" programs in the
200 "examples" directory of the distro.
201 Filehandles can also be useful if you want to stream an Excel file over
203 a socket or if you want to store an Excel file in a scalar.
204 For example here is a way to write an Excel file to a scalar:
206 #!/usr/bin/perl -w
208 use strict;
210 use Excel::Writer::XLSX;
211 open my $fh, '>', \my $str or die "Failed to open filehandle: $!";
213 my $workbook = Excel::Writer::XLSX->new( $fh );
215 my $worksheet = $workbook->add_worksheet();
216 $worksheet->write( 0, 0, 'Hi Excel!' );
218 $workbook->close();
220 # The Excel file in now in $str. Remember to binmode() the output
222 # filehandle before printing it.
223 binmode STDOUT;
224 print $str;
225 See also the "write_to_scalar.pl" and "filehandle.pl" programs in the
227 "examples" directory of the distro.
228 Note about the requirement for "binmode()". An Excel file is comprised
230 of binary data. Therefore, if you are using a filehandle you should
231 ensure that you "binmode()" it prior to passing it to "new()".You
232 should do this regardless of whether you are on a Windows platform or
233 not.
234 You don't have to worry about "binmode()" if you are using filenames
236 instead of filehandles. Excel::Writer::XLSX performs the "binmode()"
237 internally when it converts the filename to a filehandle. For more
238 information about "binmode()" see "perlfunc" and "perlopentut" in the
239 main Perl documentation.
240 add_worksheet( $sheetname )
242 At least one worksheet should be added to a new workbook. A worksheet
243 is used to write data into cells:
244 $worksheet1 = $workbook->add_worksheet(); # Sheet1
246 $worksheet2 = $workbook->add_worksheet( 'Foglio2' ); # Foglio2
247 $worksheet3 = $workbook->add_worksheet( 'Data' ); # Data
248 $worksheet4 = $workbook->add_worksheet(); # Sheet4
249 If $sheetname is not specified the default Excel convention will be
251 followed, i.e. Sheet1, Sheet2, etc.
252 The worksheet name must be a valid Excel worksheet name, i.e. it cannot
254 contain any of the following characters, "[ ] : * ? / \" and it must be
255 less than 32 characters. In addition, you cannot use the same, case
256 insensitive, $sheetname for more than one worksheet.
257 add_format( %properties )
259 The "add_format()" method can be used to create new Format objects
260 which are used to apply formatting to a cell. You can either define the
261 properties at creation time via a hash of property values or later via
262 method calls.
263 $format1 = $workbook->add_format( %props ); # Set properties at creation
265 $format2 = $workbook->add_format(); # Set properties later
266 See the "CELL FORMATTING" section for more details about Format
268 properties and how to set them.
269 add_chart( %properties )
271 This method is use to create a new chart either as a standalone
272 worksheet (the default) or as an embeddable object that can be inserted
273 into a worksheet via the "insert_chart()" Worksheet method.
274 my $chart = $workbook->add_chart( type => 'column' );
276 The properties that can be set are:
278 type (required)
280 subtype (optional)
281 name (optional)
282 embedded (optional)
283 · "type"
285 This is a required parameter. It defines the type of chart that
287 will be created.
288 my $chart = $workbook->add_chart( type => 'line' );
290 The available types are:
292 area
294 bar
295 column
296 line
297 pie
298 doughnut
299 scatter
300 stock
301 · "subtype"
303 Used to define a chart subtype where available.
305 my $chart = $workbook->add_chart( type => 'bar', subtype => 'stacked' );
307 See the Excel::Writer::XLSX::Chart documentation for a list of
309 available chart subtypes.
310 · "name"
312 Set the name for the chart sheet. The name property is optional and
314 if it isn't supplied will default to "Chart1 .. n". The name must
315 be a valid Excel worksheet name. See "add_worksheet()" for more
316 details on valid sheet names. The "name" property can be omitted
317 for embedded charts.
318 my $chart = $workbook->add_chart( type => 'line', name => 'Results Chart' );
320 · "embedded"
322 Specifies that the Chart object will be inserted in a worksheet via
324 the "insert_chart()" Worksheet method. It is an error to try insert
325 a Chart that doesn't have this flag set.
326 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
328 # Configure the chart.
330 ...
331 # Insert the chart into the a worksheet.
333 $worksheet->insert_chart( 'E2', $chart );
334 See Excel::Writer::XLSX::Chart for details on how to configure the
336 chart object once it is created. See also the "chart_*.pl" programs in
337 the examples directory of the distro.
338 add_shape( %properties )
340 The "add_shape()" method can be used to create new shapes that may be
341 inserted into a worksheet.
342 You can either define the properties at creation time via a hash of
344 property values or later via method calls.
345 # Set properties at creation.
347 $plus = $workbook->add_shape(
348 type => 'plus',
349 id => 3,
350 width => $pw,
351 height => $ph
352 );
353 # Default rectangle shape. Set properties later.
356 $rect = $workbook->add_shape();
357 See Excel::Writer::XLSX::Shape for details on how to configure the
359 shape object once it is created.
360 See also the "shape*.pl" programs in the examples directory of the
362 distro.
363 add_vba_project( 'vbaProject.bin' )
365 The "add_vba_project()" method can be used to add macros or functions
366 to an Excel::Writer::XLSX file using a binary VBA project file that has
367 been extracted from an existing Excel "xlsm" file.
368 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
370 $workbook->add_vba_project( './vbaProject.bin' );
372 The supplied "extract_vba" utility can be used to extract the required
374 "vbaProject.bin" file from an existing Excel file:
375 $ extract_vba file.xlsm
377 Extracted 'vbaProject.bin' successfully
378 Macros can be tied to buttons using the worksheet "insert_button()"
380 method (see the "WORKSHEET METHODS" section for details):
381 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
383 Note, Excel uses the file extension "xlsm" instead of "xlsx" for files
385 that contain macros. It is advisable to follow the same convention.
386 See also the "macros.pl" example file and the "WORKING WITH VBA
388 MACROS".
389 set_vba_name()
391 The "set_vba_name()" method can be used to set the VBA codename for the
392 workbook. This is sometimes required when a "vbaProject macro" included
393 via "add_vba_project()" refers to the workbook. The default Excel VBA
394 name of "ThisWorkbook" is used if a user defined name isn't specified.
395 See also "WORKING WITH VBA MACROS".
396 close()
398 In general your Excel file will be closed automatically when your
399 program ends or when the Workbook object goes out of scope. However it
400 is recommended to explicitly call the "close()" method close the Excel
401 file and avoid the potential issues outlined below. The "close()"
402 method is called like this:
403 $workbook->close();
405 The return value of "close()" is the same as that returned by perl when
407 it closes the file created by "new()". This allows you to handle error
408 conditions in the usual way:
409 $workbook->close() or die "Error closing file: $!";
411 An explicit "close()" is required if the file must be closed prior to
413 performing some external action on it such as copying it, reading its
414 size or attaching it to an email.
415 In addition, "close()" may be required to prevent perl's garbage
417 collector from disposing of the Workbook, Worksheet and Format objects
418 in the wrong order. Situations where this can occur are:
419 · If "my()" was not used to declare the scope of a workbook variable
421 created using "new()".
422 · If the "new()", "add_worksheet()" or "add_format()" methods are
424 called in subroutines.
425 The reason for this is that Excel::Writer::XLSX relies on Perl's
427 "DESTROY" mechanism to trigger destructor methods in a specific
428 sequence. This may not happen in cases where the Workbook, Worksheet
429 and Format variables are not lexically scoped or where they have
430 different lexical scopes.
431 To avoid these issues it is recommended that you always close the
433 Excel::Writer::XLSX filehandle using "close()".
434 set_size( $width, $height )
436 The "set_size()" method can be used to set the size of a workbook
437 window.
438 $workbook->set_size(1200, 800);
440 The Excel window size was used in Excel 2007 to define the width and
442 height of a workbook window within the Multiple Document Interface
443 (MDI). In later versions of Excel for Windows this interface was
444 dropped. This method is currently only useful when setting the window
445 size in Excel for Mac 2011. The units are pixels and the default size
446 is 1073 x 644.
447 Note, this doesn't equate exactly to the Excel for Mac pixel size since
449 it is based on the original Excel 2007 for Windows sizing.
450 set_tab_ratio( $tab_ratio )
452 The "set_tab_ratio()" method can be used to set the ratio between
453 worksheet tabs and the horizontal slider at the bottom of a workbook.
454 This can be increased to give more room to the tabs or reduced to
455 increase the size of the horizontal slider:
456 $workbook->set_tab_ratio(75);
458 The default value in Excel is 60.
460 set_properties()
462 The "set_properties" method can be used to set the document properties
463 of the Excel file created by "Excel::Writer::XLSX". These properties
464 are visible when you use the "Office Button -> Prepare -> Properties"
465 option in Excel and are also available to external applications that
466 read or index Windows files.
467 The properties should be passed in hash format as follows:
469 $workbook->set_properties(
471 title => 'This is an example spreadsheet',
472 author => 'John McNamara',
473 comments => 'Created with Perl and Excel::Writer::XLSX',
474 );
475 The properties that can be set are:
477 title
479 subject
480 author
481 manager
482 company
483 category
484 keywords
485 comments
486 status
487 hyperlink_base
488 created - File create date. Such be an aref of gmtime() values.
489 See also the "properties.pl" program in the examples directory of the
491 distro.
492 set_custom_property( $name, $value, $type)
494 The "set_custom_property" method can be used to set one of more custom
495 document properties not covered by the "set_properties()" method above.
496 These properties are visible when you use the "Office Button -> Prepare
497 -> Properties -> Advanced Properties -> Custom" option in Excel and are
498 also available to external applications that read or index Windows
499 files.
500 The "set_custom_property" method takes 3 parameters:
502 $workbook-> set_custom_property( $name, $value, $type);
504 Where the available types are:
506 text
508 date
509 number
510 bool
511 For example:
513 $workbook->set_custom_property( 'Checked by', 'Eve', 'text' );
515 $workbook->set_custom_property( 'Date completed', '2016-12-12T23:00:00Z', 'date' );
516 $workbook->set_custom_property( 'Document number', '12345' , 'number' );
517 $workbook->set_custom_property( 'Reference', '1.2345', 'number' );
518 $workbook->set_custom_property( 'Has review', 1, 'bool' );
519 $workbook->set_custom_property( 'Signed off', 0, 'bool' );
520 $workbook->set_custom_property( 'Department', $some_string, 'text' );
521 $workbook->set_custom_property( 'Scale', '1.2345678901234', 'number' );
522 Dates should by in ISO8601 "yyyy-mm-ddThh:mm:ss.sssZ" date format in
524 Zulu time, as shown above.
525 The "text" and "number" types are optional since they can usually be
527 inferred from the data:
528 $workbook->set_custom_property( 'Checked by', 'Eve' );
530 $workbook->set_custom_property( 'Reference', '1.2345' );
531 The $name and $value parameters are limited to 255 characters by Excel.
533 define_name()
535 This method is used to defined a name that can be used to represent a
536 value, a single cell or a range of cells in a workbook.
537 For example to set a global/workbook name:
539 # Global/workbook names.
541 $workbook->define_name( 'Exchange_rate', '=0.96' );
542 $workbook->define_name( 'Sales', '=Sheet1!$G$1:$H$10' );
543 It is also possible to define a local/worksheet name by prefixing the
545 name with the sheet name using the syntax "sheetname!definedname":
546 # Local/worksheet name.
548 $workbook->define_name( 'Sheet2!Sales', '=Sheet2!$G$1:$G$10' );
549 If the sheet name contains spaces or special characters you must
551 enclose it in single quotes like in Excel:
552 $workbook->define_name( "'New Data'!Sales", '=Sheet2!$G$1:$G$10' );
554 See the defined_name.pl program in the examples dir of the distro.
556 Refer to the following to see Excel's syntax rules for defined names:
558 <http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names>
559 set_tempdir()
561 "Excel::Writer::XLSX" stores worksheet data in temporary files prior to
562 assembling the final workbook.
563 The "File::Temp" module is used to create these temporary files.
565 File::Temp uses "File::Spec" to determine an appropriate location for
566 these files such as "/tmp" or "c:\windows\temp". You can find out which
567 directory is used on your system as follows:
568 perl -MFile::Spec -le "print File::Spec->tmpdir()"
570 If the default temporary file directory isn't accessible to your
572 application, or doesn't contain enough space, you can specify an
573 alternative location using the "set_tempdir()" method:
574 $workbook->set_tempdir( '/tmp/writeexcel' );
576 $workbook->set_tempdir( 'c:\windows\temp\writeexcel' );
577 The directory for the temporary file must exist, "set_tempdir()" will
579 not create a new directory.
580 set_custom_color( $index, $red, $green, $blue )
582 The method is maintained for backward compatibility with
583 Spreadsheet::WriteExcel. Excel::Writer::XLSX programs don't require
584 this method and colours can be specified using a Html style "#RRGGBB"
585 value, see "WORKING WITH COLOURS".
586 sheets( 0, 1, ... )
588 The "sheets()" method returns a list, or a sliced list, of the
589 worksheets in a workbook.
590 If no arguments are passed the method returns a list of all the
592 worksheets in the workbook. This is useful if you want to repeat an
593 operation on each worksheet:
594 for $worksheet ( $workbook->sheets() ) {
596 print $worksheet->get_name();
597 }
598 You can also specify a slice list to return one or more worksheet
600 objects:
601 $worksheet = $workbook->sheets( 0 );
603 $worksheet->write( 'A1', 'Hello' );
604 Or since the return value from "sheets()" is a reference to a worksheet
606 object you can write the above example as:
607 $workbook->sheets( 0 )->write( 'A1', 'Hello' );
609 The following example returns the first and last worksheet in a
611 workbook:
612 for $worksheet ( $workbook->sheets( 0, -1 ) ) {
614 # Do something
615 }
616 Array slices are explained in the "perldata" manpage.
618 get_worksheet_by_name()
620 The "get_worksheet_by_name()" function return a worksheet or chartsheet
621 object in the workbook using the sheetname:
622 $worksheet = $workbook->get_worksheet_by_name('Sheet1');
624 set_1904()
626 Excel stores dates as real numbers where the integer part stores the
627 number of days since the epoch and the fractional part stores the
628 percentage of the day. The epoch can be either 1900 or 1904. Excel for
629 Windows uses 1900 and Excel for Macintosh uses 1904. However, Excel on
630 either platform will convert automatically between one system and the
631 other.
632 Excel::Writer::XLSX stores dates in the 1900 format by default. If you
634 wish to change this you can call the "set_1904()" workbook method. You
635 can query the current value by calling the "get_1904()" workbook
636 method. This returns 0 for 1900 and 1 for 1904.
637 See also "DATES AND TIME IN EXCEL" for more information about working
639 with Excel's date system.
640 In general you probably won't need to use "set_1904()".
642 set_optimization()
644 The "set_optimization()" method is used to turn on optimizations in the
645 Excel::Writer::XLSX module. Currently there is only one optimization
646 available and that is to reduce memory usage.
647 $workbook->set_optimization();
649 See "SPEED AND MEMORY USAGE" for more background information.
651 Note, that with this optimization turned on a row of data is written
653 and then discarded when a cell in a new row is added via one of the
654 Worksheet "write_*()" methods. As such data should be written in
655 sequential row order once the optimization is turned on.
656 This method must be called before any calls to "add_worksheet()".
658 set_calc_mode( $mode )
660 Set the calculation mode for formulas in the workbook. This is mainly
661 of use for workbooks with slow formulas where you want to allow the
662 user to calculate them manually.
663 The mode parameter can be one of the following strings:
665 "auto"
667 The default. Excel will re-calculate formulas when a formula or a
668 value affecting the formula changes.
669 "manual"
671 Only re-calculate formulas when the user requires it. Generally by
672 pressing F9.
673 "auto_except_tables"
675 Excel will automatically re-calculate formulas except for tables.
676 get_default_url_format()
678 The "get_default_url_format()" method gets a copy of the default url
679 format used when a user defined format isn't specified with the
680 worksheet "write_url()" method. The format is the hyperlink style
681 defined by Excel for the default theme:
682 my $url_format = $workbook->get_default_url_format();
684
686 A new worksheet is created by calling the "add_worksheet()" method from
687 a workbook object:
688 $worksheet1 = $workbook->add_worksheet();
690 $worksheet2 = $workbook->add_worksheet();
691 The following methods are available through a new worksheet:
693 write()
695 write_number()
696 write_string()
697 write_rich_string()
698 keep_leading_zeros()
699 write_blank()
700 write_row()
701 write_col()
702 write_date_time()
703 write_url()
704 write_url_range()
705 write_formula()
706 write_boolean()
707 write_comment()
708 show_comments()
709 set_comments_author()
710 add_write_handler()
711 insert_image()
712 insert_chart()
713 insert_shape()
714 insert_button()
715 data_validation()
716 conditional_formatting()
717 add_sparkline()
718 add_table()
719 get_name()
720 activate()
721 select()
722 hide()
723 set_first_sheet()
724 protect()
725 set_selection()
726 set_row()
727 set_default_row()
728 set_column()
729 outline_settings()
730 freeze_panes()
731 split_panes()
732 merge_range()
733 merge_range_type()
734 set_zoom()
735 right_to_left()
736 hide_zero()
737 set_tab_color()
738 autofilter()
739 filter_column()
740 filter_column_list()
741 set_vba_name()
742 Cell notation
744 Excel::Writer::XLSX supports two forms of notation to designate the
745 position of cells: Row-column notation and A1 notation.
746 Row-column notation uses a zero based index for both row and column
748 while A1 notation uses the standard Excel alphanumeric sequence of
749 column letter and 1-based row. For example:
750 (0, 0) # The top left cell in row-column notation.
752 ('A1') # The top left cell in A1 notation.
753 (1999, 29) # Row-column notation.
755 ('AD2000') # The same cell in A1 notation.
756 Row-column notation is useful if you are referring to cells
758 programmatically:
759 for my $i ( 0 .. 9 ) {
761 $worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
762 }
763 A1 notation is useful for setting up a worksheet manually and for
765 working with formulas:
766 $worksheet->write( 'H1', 200 );
768 $worksheet->write( 'H2', '=H1+1' );
769 In formulas and applicable methods you can also use the "A:A" column
771 notation:
772 $worksheet->write( 'A1', '=SUM(B:B)' );
774 The "Excel::Writer::XLSX::Utility" module that is included in the
776 distro contains helper functions for dealing with A1 notation, for
777 example:
778 use Excel::Writer::XLSX::Utility;
780 ( $row, $col ) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
782 $str = xl_rowcol_to_cell( 1, 2 ); # C2
783 For simplicity, the parameter lists for the worksheet method calls in
785 the following sections are given in terms of row-column notation. In
786 all cases it is also possible to use A1 notation.
787 Note: in Excel it is also possible to use a R1C1 notation. This is not
789 supported by Excel::Writer::XLSX.
790 write( $row, $column, $token, $format )
792 Excel makes a distinction between data types such as strings, numbers,
793 blanks, formulas and hyperlinks. To simplify the process of writing
794 data the "write()" method acts as a general alias for several more
795 specific methods:
796 write_string()
798 write_number()
799 write_blank()
800 write_formula()
801 write_url()
802 write_row()
803 write_col()
804 The general rule is that if the data looks like a something then a
806 something is written. Here are some examples in both row-column and A1
807 notation:
808 # Same as:
810 $worksheet->write( 0, 0, 'Hello' ); # write_string()
811 $worksheet->write( 1, 0, 'One' ); # write_string()
812 $worksheet->write( 2, 0, 2 ); # write_number()
813 $worksheet->write( 3, 0, 3.00001 ); # write_number()
814 $worksheet->write( 4, 0, "" ); # write_blank()
815 $worksheet->write( 5, 0, '' ); # write_blank()
816 $worksheet->write( 6, 0, undef ); # write_blank()
817 $worksheet->write( 7, 0 ); # write_blank()
818 $worksheet->write( 8, 0, 'http://www.perl.com/' ); # write_url()
819 $worksheet->write( 'A9', 'ftp://ftp.cpan.org/' ); # write_url()
820 $worksheet->write( 'A10', 'internal:Sheet1!A1' ); # write_url()
821 $worksheet->write( 'A11', 'external:c:\foo.xlsx' ); # write_url()
822 $worksheet->write( 'A12', '=A3 + 3*A4' ); # write_formula()
823 $worksheet->write( 'A13', '=SIN(PI()/4)' ); # write_formula()
824 $worksheet->write( 'A14', \@array ); # write_row()
825 $worksheet->write( 'A15', [\@array] ); # write_col()
826 # And if the keep_leading_zeros property is set:
828 $worksheet->write( 'A16', '2' ); # write_number()
829 $worksheet->write( 'A17', '02' ); # write_string()
830 $worksheet->write( 'A18', '00002' ); # write_string()
831 # Write an array formula. Not available in Spreadsheet::WriteExcel.
833 $worksheet->write( 'A19', '{=SUM(A1:B1*A2:B2)}' ); # write_formula()
834 The "looks like" rule is defined by regular expressions:
836 "write_number()" if $token is a number based on the following regex:
838 "$token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/".
839 "write_string()" if "keep_leading_zeros()" is set and $token is an
841 integer with leading zeros based on the following regex: "$token =~
842 /^0\d+$/".
843 "write_blank()" if $token is undef or a blank string: "undef", "" or
845 ''.
846 "write_url()" if $token is a http, https, ftp or mailto URL based on
848 the following regexes: "$token =~ m|^[fh]tt?ps?://|" or "$token =~
849 m|^mailto:|".
850 "write_url()" if $token is an internal or external sheet reference
852 based on the following regex: "$token =~ m[^(in|ex)ternal:]".
853 "write_formula()" if the first character of $token is "=".
855 "write_array_formula()" if the $token matches "/^{=.*}$/".
857 "write_row()" if $token is an array ref.
859 "write_col()" if $token is an array ref of array refs.
861 "write_string()" if none of the previous conditions apply.
863 The $format parameter is optional. It should be a valid Format object,
865 see "CELL FORMATTING":
866 my $format = $workbook->add_format();
868 $format->set_bold();
869 $format->set_color( 'red' );
870 $format->set_align( 'center' );
871 $worksheet->write( 4, 0, 'Hello', $format ); # Formatted string
873 The write() method will ignore empty strings or "undef" tokens unless a
875 format is also supplied. As such you needn't worry about special
876 handling for empty or "undef" values in your data. See also the
877 "write_blank()" method.
878 One problem with the "write()" method is that occasionally data looks
880 like a number but you don't want it treated as a number. For example,
881 zip codes or ID numbers often start with a leading zero. If you write
882 this data as a number then the leading zero(s) will be stripped. You
883 can change this default behaviour by using the "keep_leading_zeros()"
884 method. While this property is in place any integers with leading zeros
885 will be treated as strings and the zeros will be preserved. See the
886 "keep_leading_zeros()" section for a full discussion of this issue.
887 You can also add your own data handlers to the "write()" method using
889 "add_write_handler()".
890 The "write()" method will also handle Unicode strings in "UTF-8"
892 format.
893 The "write" methods return:
895 0 for success.
897 -1 for insufficient number of arguments.
898 -2 for row or column out of bounds.
899 -3 for string too long.
900 write_number( $row, $column, $number, $format )
902 Write an integer or a float to the cell specified by $row and $column:
903 $worksheet->write_number( 0, 0, 123456 );
905 $worksheet->write_number( 'A2', 2.3451 );
906 See the note about "Cell notation". The $format parameter is optional.
908 In general it is sufficient to use the "write()" method.
910 Note: some versions of Excel 2007 do not display the calculated values
912 of formulas written by Excel::Writer::XLSX. Applying all available
913 Service Packs to Excel should fix this.
914 write_string( $row, $column, $string, $format )
916 Write a string to the cell specified by $row and $column:
917 $worksheet->write_string( 0, 0, 'Your text here' );
919 $worksheet->write_string( 'A2', 'or here' );
920 The maximum string size is 32767 characters. However the maximum string
922 segment that Excel can display in a cell is 1000. All 32767 characters
923 can be displayed in the formula bar.
924 The $format parameter is optional.
926 The "write()" method will also handle strings in "UTF-8" format. See
928 also the "unicode_*.pl" programs in the examples directory of the
929 distro.
930 In general it is sufficient to use the "write()" method. However, you
932 may sometimes wish to use the "write_string()" method to write data
933 that looks like a number but that you don't want treated as a number.
934 For example, zip codes or phone numbers:
935 # Write as a plain string
937 $worksheet->write_string( 'A1', '01209' );
938 However, if the user edits this string Excel may convert it back to a
940 number. To get around this you can use the Excel text format "@":
941 # Format as a string. Doesn't change to a number when edited
943 my $format1 = $workbook->add_format( num_format => '@' );
944 $worksheet->write_string( 'A2', '01209', $format1 );
945 See also the note about "Cell notation".
947 write_rich_string( $row, $column, $format, $string, ..., $cell_format )
949 The "write_rich_string()" method is used to write strings with multiple
950 formats. For example to write the string "This is bold and this is
951 italic" you would use the following:
952 my $bold = $workbook->add_format( bold => 1 );
954 my $italic = $workbook->add_format( italic => 1 );
955 $worksheet->write_rich_string( 'A1',
957 'This is ', $bold, 'bold', ' and this is ', $italic, 'italic' );
958 The basic rule is to break the string into fragments and put a $format
960 object before the fragment that you want to format. For example:
961 # Unformatted string.
963 'This is an example string'
964 # Break it into fragments.
966 'This is an ', 'example', ' string'
967 # Add formatting before the fragments you want formatted.
969 'This is an ', $format, 'example', ' string'
970 # In Excel::Writer::XLSX.
972 $worksheet->write_rich_string( 'A1',
973 'This is an ', $format, 'example', ' string' );
974 String fragments that don't have a format are given a default format.
976 So for example when writing the string "Some bold text" you would use
977 the first example below but it would be equivalent to the second:
978 # With default formatting:
980 my $bold = $workbook->add_format( bold => 1 );
981 $worksheet->write_rich_string( 'A1',
983 'Some ', $bold, 'bold', ' text' );
984 # Or more explicitly:
986 my $bold = $workbook->add_format( bold => 1 );
987 my $default = $workbook->add_format();
988 $worksheet->write_rich_string( 'A1',
990 $default, 'Some ', $bold, 'bold', $default, ' text' );
991 As with Excel, only the font properties of the format such as font
993 name, style, size, underline, color and effects are applied to the
994 string fragments. Other features such as border, background, text wrap
995 and alignment must be applied to the cell.
996 The "write_rich_string()" method allows you to do this by using the
998 last argument as a cell format (if it is a format object). The
999 following example centers a rich string in the cell:
1000 my $bold = $workbook->add_format( bold => 1 );
1002 my $center = $workbook->add_format( align => 'center' );
1003 $worksheet->write_rich_string( 'A5',
1005 'Some ', $bold, 'bold text', ' centered', $center );
1006 See the "rich_strings.pl" example in the distro for more examples.
1008 my $bold = $workbook->add_format( bold => 1 );
1010 my $italic = $workbook->add_format( italic => 1 );
1011 my $red = $workbook->add_format( color => 'red' );
1012 my $blue = $workbook->add_format( color => 'blue' );
1013 my $center = $workbook->add_format( align => 'center' );
1014 my $super = $workbook->add_format( font_script => 1 );
1015 # Write some strings with multiple formats.
1018 $worksheet->write_rich_string( 'A1',
1019 'This is ', $bold, 'bold', ' and this is ', $italic, 'italic' );
1020 $worksheet->write_rich_string( 'A3',
1022 'This is ', $red, 'red', ' and this is ', $blue, 'blue' );
1023 $worksheet->write_rich_string( 'A5',
1025 'Some ', $bold, 'bold text', ' centered', $center );
1026 $worksheet->write_rich_string( 'A7',
1028 $italic, 'j = k', $super, '(n-1)', $center );
1029 As with "write_sting()" the maximum string size is 32767 characters.
1031 See also the note about "Cell notation".
1032 keep_leading_zeros()
1034 This method changes the default handling of integers with leading zeros
1035 when using the "write()" method.
1036 The "write()" method uses regular expressions to determine what type of
1038 data to write to an Excel worksheet. If the data looks like a number it
1039 writes a number using "write_number()". One problem with this approach
1040 is that occasionally data looks like a number but you don't want it
1041 treated as a number.
1042 Zip codes and ID numbers, for example, often start with a leading zero.
1044 If you write this data as a number then the leading zero(s) will be
1045 stripped. This is the also the default behaviour when you enter data
1046 manually in Excel.
1047 To get around this you can use one of three options. Write a formatted
1049 number, write the number as a string or use the "keep_leading_zeros()"
1050 method to change the default behaviour of "write()":
1051 # Implicitly write a number, the leading zero is removed: 1209
1053 $worksheet->write( 'A1', '01209' );
1054 # Write a zero padded number using a format: 01209
1056 my $format1 = $workbook->add_format( num_format => '00000' );
1057 $worksheet->write( 'A2', '01209', $format1 );
1058 # Write explicitly as a string: 01209
1060 $worksheet->write_string( 'A3', '01209' );
1061 # Write implicitly as a string: 01209
1063 $worksheet->keep_leading_zeros();
1064 $worksheet->write( 'A4', '01209' );
1065 The above code would generate a worksheet that looked like the
1067 following:
1068 -----------------------------------------------------------
1070 | | A | B | C | D | ...
1071 -----------------------------------------------------------
1072 | 1 | 1209 | | | | ...
1073 | 2 | 01209 | | | | ...
1074 | 3 | 01209 | | | | ...
1075 | 4 | 01209 | | | | ...
1076 The examples are on different sides of the cells due to the fact that
1078 Excel displays strings with a left justification and numbers with a
1079 right justification by default. You can change this by using a format
1080 to justify the data, see "CELL FORMATTING".
1081 It should be noted that if the user edits the data in examples "A3" and
1083 "A4" the strings will revert back to numbers. Again this is Excel's
1084 default behaviour. To avoid this you can use the text format "@":
1085 # Format as a string (01209)
1087 my $format2 = $workbook->add_format( num_format => '@' );
1088 $worksheet->write_string( 'A5', '01209', $format2 );
1089 The "keep_leading_zeros()" property is off by default. The
1091 "keep_leading_zeros()" method takes 0 or 1 as an argument. It defaults
1092 to 1 if an argument isn't specified:
1093 $worksheet->keep_leading_zeros(); # Set on
1095 $worksheet->keep_leading_zeros( 1 ); # Set on
1096 $worksheet->keep_leading_zeros( 0 ); # Set off
1097 See also the "add_write_handler()" method.
1099 write_blank( $row, $column, $format )
1101 Write a blank cell specified by $row and $column:
1102 $worksheet->write_blank( 0, 0, $format );
1104 This method is used to add formatting to a cell which doesn't contain a
1106 string or number value.
1107 Excel differentiates between an "Empty" cell and a "Blank" cell. An
1109 "Empty" cell is a cell which doesn't contain data whilst a "Blank" cell
1110 is a cell which doesn't contain data but does contain formatting. Excel
1111 stores "Blank" cells but ignores "Empty" cells.
1112 As such, if you write an empty cell without formatting it is ignored:
1114 $worksheet->write( 'A1', undef, $format ); # write_blank()
1116 $worksheet->write( 'A2', undef ); # Ignored
1117 This seemingly uninteresting fact means that you can write arrays of
1119 data without special treatment for "undef" or empty string values.
1120 See the note about "Cell notation".
1122 write_row( $row, $column, $array_ref, $format )
1124 The "write_row()" method can be used to write a 1D or 2D array of data
1125 in one go. This is useful for converting the results of a database
1126 query into an Excel worksheet. You must pass a reference to the array
1127 of data rather than the array itself. The "write()" method is then
1128 called for each element of the data. For example:
1129 @array = ( 'awk', 'gawk', 'mawk' );
1131 $array_ref = \@array;
1132 $worksheet->write_row( 0, 0, $array_ref );
1134 # The above example is equivalent to:
1136 $worksheet->write( 0, 0, $array[0] );
1137 $worksheet->write( 0, 1, $array[1] );
1138 $worksheet->write( 0, 2, $array[2] );
1139 Note: For convenience the "write()" method behaves in the same way as
1141 "write_row()" if it is passed an array reference. Therefore the
1142 following two method calls are equivalent:
1143 $worksheet->write_row( 'A1', $array_ref ); # Write a row of data
1145 $worksheet->write( 'A1', $array_ref ); # Same thing
1146 As with all of the write methods the $format parameter is optional. If
1148 a format is specified it is applied to all the elements of the data
1149 array.
1150 Array references within the data will be treated as columns. This
1152 allows you to write 2D arrays of data in one go. For example:
1153 @eec = (
1155 ['maggie', 'milly', 'molly', 'may' ],
1156 [13, 14, 15, 16 ],
1157 ['shell', 'star', 'crab', 'stone']
1158 );
1159 $worksheet->write_row( 'A1', \@eec );
1161 Would produce a worksheet as follows:
1163 -----------------------------------------------------------
1165 | | A | B | C | D | E | ...
1166 -----------------------------------------------------------
1167 | 1 | maggie | 13 | shell | ... | ... | ...
1168 | 2 | milly | 14 | star | ... | ... | ...
1169 | 3 | molly | 15 | crab | ... | ... | ...
1170 | 4 | may | 16 | stone | ... | ... | ...
1171 | 5 | ... | ... | ... | ... | ... | ...
1172 | 6 | ... | ... | ... | ... | ... | ...
1173 To write the data in a row-column order refer to the "write_col()"
1175 method below.
1176 Any "undef" values in the data will be ignored unless a format is
1178 applied to the data, in which case a formatted blank cell will be
1179 written. In either case the appropriate row or column value will still
1180 be incremented.
1181 To find out more about array references refer to "perlref" and
1183 "perlreftut" in the main Perl documentation. To find out more about 2D
1184 arrays or "lists of lists" refer to "perllol".
1185 The "write_row()" method returns the first error encountered when
1187 writing the elements of the data or zero if no errors were encountered.
1188 See the return values described for the "write()" method above.
1189 See also the "write_arrays.pl" program in the "examples" directory of
1191 the distro.
1192 The "write_row()" method allows the following idiomatic conversion of a
1194 text file to an Excel file:
1195 #!/usr/bin/perl -w
1197 use strict;
1199 use Excel::Writer::XLSX;
1200 my $workbook = Excel::Writer::XLSX->new( 'file.xlsx' );
1202 my $worksheet = $workbook->add_worksheet();
1203 open INPUT, 'file.txt' or die "Couldn't open file: $!";
1205 $worksheet->write( $. -1, 0, [split] ) while <INPUT>;
1207 $workbook->close();
1209 write_col( $row, $column, $array_ref, $format )
1211 The "write_col()" method can be used to write a 1D or 2D array of data
1212 in one go. This is useful for converting the results of a database
1213 query into an Excel worksheet. You must pass a reference to the array
1214 of data rather than the array itself. The "write()" method is then
1215 called for each element of the data. For example:
1216 @array = ( 'awk', 'gawk', 'mawk' );
1218 $array_ref = \@array;
1219 $worksheet->write_col( 0, 0, $array_ref );
1221 # The above example is equivalent to:
1223 $worksheet->write( 0, 0, $array[0] );
1224 $worksheet->write( 1, 0, $array[1] );
1225 $worksheet->write( 2, 0, $array[2] );
1226 As with all of the write methods the $format parameter is optional. If
1228 a format is specified it is applied to all the elements of the data
1229 array.
1230 Array references within the data will be treated as rows. This allows
1232 you to write 2D arrays of data in one go. For example:
1233 @eec = (
1235 ['maggie', 'milly', 'molly', 'may' ],
1236 [13, 14, 15, 16 ],
1237 ['shell', 'star', 'crab', 'stone']
1238 );
1239 $worksheet->write_col( 'A1', \@eec );
1241 Would produce a worksheet as follows:
1243 -----------------------------------------------------------
1245 | | A | B | C | D | E | ...
1246 -----------------------------------------------------------
1247 | 1 | maggie | milly | molly | may | ... | ...
1248 | 2 | 13 | 14 | 15 | 16 | ... | ...
1249 | 3 | shell | star | crab | stone | ... | ...
1250 | 4 | ... | ... | ... | ... | ... | ...
1251 | 5 | ... | ... | ... | ... | ... | ...
1252 | 6 | ... | ... | ... | ... | ... | ...
1253 To write the data in a column-row order refer to the "write_row()"
1255 method above.
1256 Any "undef" values in the data will be ignored unless a format is
1258 applied to the data, in which case a formatted blank cell will be
1259 written. In either case the appropriate row or column value will still
1260 be incremented.
1261 As noted above the "write()" method can be used as a synonym for
1263 "write_row()" and "write_row()" handles nested array refs as columns.
1264 Therefore, the following two method calls are equivalent although the
1265 more explicit call to "write_col()" would be preferable for
1266 maintainability:
1267 $worksheet->write_col( 'A1', $array_ref ); # Write a column of data
1269 $worksheet->write( 'A1', [ $array_ref ] ); # Same thing
1270 To find out more about array references refer to "perlref" and
1272 "perlreftut" in the main Perl documentation. To find out more about 2D
1273 arrays or "lists of lists" refer to "perllol".
1274 The "write_col()" method returns the first error encountered when
1276 writing the elements of the data or zero if no errors were encountered.
1277 See the return values described for the "write()" method above.
1278 See also the "write_arrays.pl" program in the "examples" directory of
1280 the distro.
1281 write_date_time( $row, $col, $date_string, $format )
1283 The "write_date_time()" method can be used to write a date or time to
1284 the cell specified by $row and $column:
1285 $worksheet->write_date_time( 'A1', '2004-05-13T23:20', $date_format );
1287 The $date_string should be in the following format:
1289 yyyy-mm-ddThh:mm:ss.sss
1291 This conforms to an ISO8601 date but it should be noted that the full
1293 range of ISO8601 formats are not supported.
1294 The following variations on the $date_string parameter are permitted:
1296 yyyy-mm-ddThh:mm:ss.sss # Standard format
1298 yyyy-mm-ddT # No time
1299 Thh:mm:ss.sss # No date
1300 yyyy-mm-ddThh:mm:ss.sssZ # Additional Z (but not time zones)
1301 yyyy-mm-ddThh:mm:ss # No fractional seconds
1302 yyyy-mm-ddThh:mm # No seconds
1303 Note that the "T" is required in all cases.
1305 A date should always have a $format, otherwise it will appear as a
1307 number, see "DATES AND TIME IN EXCEL" and "CELL FORMATTING". Here is a
1308 typical example:
1309 my $date_format = $workbook->add_format( num_format => 'mm/dd/yy' );
1311 $worksheet->write_date_time( 'A1', '2004-05-13T23:20', $date_format );
1312 Valid dates should be in the range 1900-01-01 to 9999-12-31, for the
1314 1900 epoch and 1904-01-01 to 9999-12-31, for the 1904 epoch. As with
1315 Excel, dates outside these ranges will be written as a string.
1316 See also the date_time.pl program in the "examples" directory of the
1318 distro.
1319 write_url( $row, $col, $url, $format, $label )
1321 Write a hyperlink to a URL in the cell specified by $row and $column.
1322 The hyperlink is comprised of two elements: the visible label and the
1323 invisible link. The visible label is the same as the link unless an
1324 alternative label is specified. The $label parameter is optional. The
1325 label is written using the "write()" method. Therefore it is possible
1326 to write strings, numbers or formulas as labels.
1327 The $format parameter is also optional and the default Excel hyperlink
1329 style will be used if it isn't specified. If required you can access
1330 the default url format using the Workbook "get_default_url_format"
1331 method:
1332 my $url_format = $workbook->get_default_url_format();
1334 There are four web style URI's supported: "http://", "https://",
1336 "ftp://" and "mailto:":
1337 $worksheet->write_url( 0, 0, 'ftp://www.perl.org/' );
1339 $worksheet->write_url( 'A3', 'http://www.perl.com/' );
1340 $worksheet->write_url( 'A4', 'mailto:jmcnamara@cpan.org' );
1341 You can display an alternative string using the $label parameter:
1343 $worksheet->write_url( 1, 0, 'http://www.perl.com/', undef, 'Perl' );
1345 If you wish to have some other cell data such as a number or a formula
1347 you can overwrite the cell using another call to "write_*()":
1348 $worksheet->write_url( 'A1', 'http://www.perl.com/' );
1350 # Overwrite the URL string with a formula. The cell is still a link.
1352 # Note the use of the default url format for consistency with other links.
1353 my $url_format = $workbook->get_default_url_format();
1354 $worksheet->write_formula( 'A1', '=1+1', $url_format );
1355 There are two local URIs supported: "internal:" and "external:". These
1357 are used for hyperlinks to internal worksheet references or external
1358 workbook and worksheet references:
1359 $worksheet->write_url( 'A6', 'internal:Sheet2!A1' );
1361 $worksheet->write_url( 'A7', 'internal:Sheet2!A1' );
1362 $worksheet->write_url( 'A8', 'internal:Sheet2!A1:B2' );
1363 $worksheet->write_url( 'A9', q{internal:'Sales Data'!A1} );
1364 $worksheet->write_url( 'A10', 'external:c:\temp\foo.xlsx' );
1365 $worksheet->write_url( 'A11', 'external:c:\foo.xlsx#Sheet2!A1' );
1366 $worksheet->write_url( 'A12', 'external:..\foo.xlsx' );
1367 $worksheet->write_url( 'A13', 'external:..\foo.xlsx#Sheet2!A1' );
1368 $worksheet->write_url( 'A13', 'external:\\\\NET\share\foo.xlsx' );
1369 All of the these URI types are recognised by the "write()" method, see
1371 above.
1372 Worksheet references are typically of the form "Sheet1!A1". You can
1374 also refer to a worksheet range using the standard Excel notation:
1375 "Sheet1!A1:B2".
1376 In external links the workbook and worksheet name must be separated by
1378 the "#" character: "external:Workbook.xlsx#Sheet1!A1'".
1379 You can also link to a named range in the target worksheet. For example
1381 say you have a named range called "my_name" in the workbook
1382 "c:\temp\foo.xlsx" you could link to it as follows:
1383 $worksheet->write_url( 'A14', 'external:c:\temp\foo.xlsx#my_name' );
1385 Excel requires that worksheet names containing spaces or non
1387 alphanumeric characters are single quoted as follows "'Sales Data'!A1".
1388 If you need to do this in a single quoted string then you can either
1389 escape the single quotes "\'" or use the quote operator "q{}" as
1390 described in "perlop" in the main Perl documentation.
1391 Links to network files are also supported. MS/Novell Network files
1393 normally begin with two back slashes as follows "\\NETWORK\etc". In
1394 order to generate this in a single or double quoted string you will
1395 have to escape the backslashes, '\\\\NETWORK\etc'.
1396 If you are using double quote strings then you should be careful to
1398 escape anything that looks like a metacharacter. For more information
1399 see "perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?".
1400 Finally, you can avoid most of these quoting problems by using forward
1402 slashes. These are translated internally to backslashes:
1403 $worksheet->write_url( 'A14', "external:c:/temp/foo.xlsx" );
1405 $worksheet->write_url( 'A15', 'external://NETWORK/share/foo.xlsx' );
1406 Note: Excel::Writer::XLSX will escape the following characters in URLs
1408 as required by Excel: "\s " < > \ [ ] ` ^ { }" unless the URL already
1409 contains %xx style escapes. In which case it is assumed that the URL
1410 was escaped correctly by the user and will by passed directly to Excel.
1411 Excel limits hyperlink links and anchor/locations to 255 characters
1413 each.
1414 See also, the note about "Cell notation".
1416 write_formula( $row, $column, $formula, $format, $value )
1418 Write a formula or function to the cell specified by $row and $column:
1419 $worksheet->write_formula( 0, 0, '=$B$3 + B4' );
1421 $worksheet->write_formula( 1, 0, '=SIN(PI()/4)' );
1422 $worksheet->write_formula( 2, 0, '=SUM(B1:B5)' );
1423 $worksheet->write_formula( 'A4', '=IF(A3>1,"Yes", "No")' );
1424 $worksheet->write_formula( 'A5', '=AVERAGE(1, 2, 3, 4)' );
1425 $worksheet->write_formula( 'A6', '=DATEVALUE("1-Jan-2001")' );
1426 Array formulas are also supported:
1428 $worksheet->write_formula( 'A7', '{=SUM(A1:B1*A2:B2)}' );
1430 See also the "write_array_formula()" method below.
1432 See the note about "Cell notation". For more information about writing
1434 Excel formulas see "FORMULAS AND FUNCTIONS IN EXCEL"
1435 If required, it is also possible to specify the calculated value of the
1437 formula. This is occasionally necessary when working with non-Excel
1438 applications that don't calculate the value of the formula. The
1439 calculated $value is added at the end of the argument list:
1440 $worksheet->write( 'A1', '=2+2', $format, 4 );
1442 However, this probably isn't something that you will ever need to do.
1444 If you do use this feature then do so with care.
1445 write_array_formula($first_row, $first_col, $last_row, $last_col, $formula,
1447 $format, $value)
1448 Write an array formula to a cell range. In Excel an array formula is a
1449 formula that performs a calculation on a set of values. It can return a
1450 single value or a range of values.
1451 An array formula is indicated by a pair of braces around the formula:
1453 "{=SUM(A1:B1*A2:B2)}". If the array formula returns a single value
1454 then the $first_ and $last_ parameters should be the same:
1455 $worksheet->write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}');
1457 It this case however it is easier to just use the "write_formula()" or
1459 "write()" methods:
1460 # Same as above but more concise.
1462 $worksheet->write( 'A1', '{=SUM(B1:C1*B2:C2)}' );
1463 $worksheet->write_formula( 'A1', '{=SUM(B1:C1*B2:C2)}' );
1464 For array formulas that return a range of values you must specify the
1466 range that the return values will be written to:
1467 $worksheet->write_array_formula( 'A1:A3', '{=TREND(C1:C3,B1:B3)}' );
1469 $worksheet->write_array_formula( 0, 0, 2, 0, '{=TREND(C1:C3,B1:B3)}' );
1470 If required, it is also possible to specify the calculated value of the
1472 formula. This is occasionally necessary when working with non-Excel
1473 applications that don't calculate the value of the formula. However,
1474 using this parameter only writes a single value to the upper left cell
1475 in the result array. For a multi-cell array formula where the results
1476 are required, the other result values can be specified by using
1477 "write_number()" to write to the appropriate cell:
1478 # Specify the result for a single cell range.
1480 $worksheet->write_array_formula( 'A1:A3', '{=SUM(B1:C1*B2:C2)}, $format, 2005 );
1481 # Specify the results for a multi cell range.
1483 $worksheet->write_array_formula( 'A1:A3', '{=TREND(C1:C3,B1:B3)}', $format, 105 );
1484 $worksheet->write_number( 'A2', 12, format );
1485 $worksheet->write_number( 'A3', 14, format );
1486 In addition, some early versions of Excel 2007 don't calculate the
1488 values of array formulas when they aren't supplied. Installing the
1489 latest Office Service Pack should fix this issue.
1490 See also the "array_formula.pl" program in the "examples" directory of
1492 the distro.
1493 Note: Array formulas are not supported by Spreadsheet::WriteExcel.
1495 write_boolean( $row, $column, $value, $format )
1497 Write an Excel boolean value to the cell specified by $row and $column:
1498 $worksheet->write_boolean( 'A1', 1 ); # TRUE
1500 $worksheet->write_boolean( 'A2', 0 ); # FALSE
1501 $worksheet->write_boolean( 'A3', undef ); # FALSE
1502 $worksheet->write_boolean( 'A3', 0, $format ); # FALSE, with format.
1503 A $value that is true or false using Perl's rules will be written as an
1505 Excel boolean "TRUE" or "FALSE" value.
1506 See the note about "Cell notation".
1508 store_formula( $formula )
1510 Deprecated. This is a Spreadsheet::WriteExcel method that is no longer
1511 required by Excel::Writer::XLSX. See below.
1512 repeat_formula( $row, $col, $formula, $format )
1514 Deprecated. This is a Spreadsheet::WriteExcel method that is no longer
1515 required by Excel::Writer::XLSX.
1516 In Spreadsheet::WriteExcel it was computationally expensive to write
1518 formulas since they were parsed by a recursive descent parser. The
1519 "store_formula()" and "repeat_formula()" methods were used as a way of
1520 avoiding the overhead of repeated formulas by reusing a pre-parsed
1521 formula.
1522 In Excel::Writer::XLSX this is no longer necessary since it is just as
1524 quick to write a formula as it is to write a string or a number.
1525 The methods remain for backward compatibility but new
1527 Excel::Writer::XLSX programs shouldn't use them.
1528 write_comment( $row, $column, $string, ... )
1530 The "write_comment()" method is used to add a comment to a cell. A cell
1531 comment is indicated in Excel by a small red triangle in the upper
1532 right-hand corner of the cell. Moving the cursor over the red triangle
1533 will reveal the comment.
1534 The following example shows how to add a comment to a cell:
1536 $worksheet->write ( 2, 2, 'Hello' );
1538 $worksheet->write_comment( 2, 2, 'This is a comment.' );
1539 As usual you can replace the $row and $column parameters with an "A1"
1541 cell reference. See the note about "Cell notation".
1542 $worksheet->write ( 'C3', 'Hello');
1544 $worksheet->write_comment( 'C3', 'This is a comment.' );
1545 The "write_comment()" method will also handle strings in "UTF-8"
1547 format.
1548 $worksheet->write_comment( 'C3', "\x{263a}" ); # Smiley
1550 $worksheet->write_comment( 'C4', 'Comment ca va?' );
1551 In addition to the basic 3 argument form of "write_comment()" you can
1553 pass in several optional key/value pairs to control the format of the
1554 comment. For example:
1555 $worksheet->write_comment( 'C3', 'Hello', visible => 1, author => 'Perl' );
1557 Most of these options are quite specific and in general the default
1559 comment behaves will be all that you need. However, should you need
1560 greater control over the format of the cell comment the following
1561 options are available:
1562 author
1564 visible
1565 x_scale
1566 width
1567 y_scale
1568 height
1569 color
1570 start_cell
1571 start_row
1572 start_col
1573 x_offset
1574 y_offset
1575 font
1576 font_size
1577 Option: author
1579 This option is used to indicate who is the author of the cell
1580 comment. Excel displays the author of the comment in the status bar
1581 at the bottom of the worksheet. This is usually of interest in
1582 corporate environments where several people might review and
1583 provide comments to a workbook.
1584 $worksheet->write_comment( 'C3', 'Atonement', author => 'Ian McEwan' );
1586 The default author for all cell comments can be set using the
1588 "set_comments_author()" method (see below).
1589 $worksheet->set_comments_author( 'Perl' );
1591 Option: visible
1593 This option is used to make a cell comment visible when the
1594 worksheet is opened. The default behaviour in Excel is that
1595 comments are initially hidden. However, it is also possible in
1596 Excel to make individual or all comments visible. In
1597 Excel::Writer::XLSX individual comments can be made visible as
1598 follows:
1599 $worksheet->write_comment( 'C3', 'Hello', visible => 1 );
1601 It is possible to make all comments in a worksheet visible using
1603 the "show_comments()" worksheet method (see below). Alternatively,
1604 if all of the cell comments have been made visible you can hide
1605 individual comments:
1606 $worksheet->write_comment( 'C3', 'Hello', visible => 0 );
1608 Option: x_scale
1610 This option is used to set the width of the cell comment box as a
1611 factor of the default width.
1612 $worksheet->write_comment( 'C3', 'Hello', x_scale => 2 );
1614 $worksheet->write_comment( 'C4', 'Hello', x_scale => 4.2 );
1615 Option: width
1617 This option is used to set the width of the cell comment box
1618 explicitly in pixels.
1619 $worksheet->write_comment( 'C3', 'Hello', width => 200 );
1621 Option: y_scale
1623 This option is used to set the height of the cell comment box as a
1624 factor of the default height.
1625 $worksheet->write_comment( 'C3', 'Hello', y_scale => 2 );
1627 $worksheet->write_comment( 'C4', 'Hello', y_scale => 4.2 );
1628 Option: height
1630 This option is used to set the height of the cell comment box
1631 explicitly in pixels.
1632 $worksheet->write_comment( 'C3', 'Hello', height => 200 );
1634 Option: color
1636 This option is used to set the background colour of cell comment
1637 box. You can use one of the named colours recognised by
1638 Excel::Writer::XLSX or a Html style "#RRGGBB" colour. See "WORKING
1639 WITH COLOURS".
1640 $worksheet->write_comment( 'C3', 'Hello', color => 'green' );
1642 $worksheet->write_comment( 'C4', 'Hello', color => '#FF6600' ); # Orange
1643 Option: start_cell
1645 This option is used to set the cell in which the comment will
1646 appear. By default Excel displays comments one cell to the right
1647 and one cell above the cell to which the comment relates. However,
1648 you can change this behaviour if you wish. In the following example
1649 the comment which would appear by default in cell "D2" is moved to
1650 "E2".
1651 $worksheet->write_comment( 'C3', 'Hello', start_cell => 'E2' );
1653 Option: start_row
1655 This option is used to set the row in which the comment will
1656 appear. See the "start_cell" option above. The row is zero indexed.
1657 $worksheet->write_comment( 'C3', 'Hello', start_row => 0 );
1659 Option: start_col
1661 This option is used to set the column in which the comment will
1662 appear. See the "start_cell" option above. The column is zero
1663 indexed.
1664 $worksheet->write_comment( 'C3', 'Hello', start_col => 4 );
1666 Option: x_offset
1668 This option is used to change the x offset, in pixels, of a comment
1669 within a cell:
1670 $worksheet->write_comment( 'C3', $comment, x_offset => 30 );
1672 Option: y_offset
1674 This option is used to change the y offset, in pixels, of a comment
1675 within a cell:
1676 $worksheet->write_comment('C3', $comment, x_offset => 30);
1678 Option: font
1680 This option is used to change the font used in the comment from
1681 'Tahoma' which is the default.
1682 $worksheet->write_comment('C3', $comment, font => 'Calibri');
1684 Option: font_size
1686 This option is used to change the font size used in the comment
1687 from 8 which is the default.
1688 $worksheet->write_comment('C3', $comment, font_size => 20);
1690 You can apply as many of these options as you require.
1692 Note about using options that adjust the position of the cell comment
1694 such as start_cell, start_row, start_col, x_offset and y_offset: Excel
1695 only displays offset cell comments when they are displayed as
1696 "visible". Excel does not display hidden cells as moved when you mouse
1697 over them.
1698 Note about row height and comments. If you specify the height of a row
1700 that contains a comment then Excel::Writer::XLSX will adjust the height
1701 of the comment to maintain the default or user specified dimensions.
1702 However, the height of a row can also be adjusted automatically by
1703 Excel if the text wrap property is set or large fonts are used in the
1704 cell. This means that the height of the row is unknown to the module at
1705 run time and thus the comment box is stretched with the row. Use the
1706 "set_row()" method to specify the row height explicitly and avoid this
1707 problem.
1708 show_comments()
1710 This method is used to make all cell comments visible when a worksheet
1711 is opened.
1712 $worksheet->show_comments();
1714 Individual comments can be made visible using the "visible" parameter
1716 of the "write_comment" method (see above):
1717 $worksheet->write_comment( 'C3', 'Hello', visible => 1 );
1719 If all of the cell comments have been made visible you can hide
1721 individual comments as follows:
1722 $worksheet->show_comments();
1724 $worksheet->write_comment( 'C3', 'Hello', visible => 0 );
1725 set_comments_author()
1727 This method is used to set the default author of all cell comments.
1728 $worksheet->set_comments_author( 'Perl' );
1730 Individual comment authors can be set using the "author" parameter of
1732 the "write_comment" method (see above).
1733 The default comment author is an empty string, '', if no author is
1735 specified.
1736 add_write_handler( $re, $code_ref )
1738 This method is used to extend the Excel::Writer::XLSX write() method to
1739 handle user defined data.
1740 If you refer to the section on "write()" above you will see that it
1742 acts as an alias for several more specific "write_*" methods. However,
1743 it doesn't always act in exactly the way that you would like it to.
1744 One solution is to filter the input data yourself and call the
1746 appropriate "write_*" method. Another approach is to use the
1747 "add_write_handler()" method to add your own automated behaviour to
1748 "write()".
1749 The "add_write_handler()" method take two arguments, $re, a regular
1751 expression to match incoming data and $code_ref a callback function to
1752 handle the matched data:
1753 $worksheet->add_write_handler( qr/^\d\d\d\d$/, \&my_write );
1755 (In the these examples the "qr" operator is used to quote the regular
1757 expression strings, see perlop for more details).
1758 The method is used as follows. say you wished to write 7 digit ID
1760 numbers as a string so that any leading zeros were preserved*, you
1761 could do something like the following:
1762 $worksheet->add_write_handler( qr/^\d{7}$/, \&write_my_id );
1764 sub write_my_id {
1767 my $worksheet = shift;
1768 return $worksheet->write_string( @_ );
1769 }
1770 * You could also use the "keep_leading_zeros()" method for this.
1772 Then if you call "write()" with an appropriate string it will be
1774 handled automatically:
1775 # Writes 0000000. It would normally be written as a number; 0.
1777 $worksheet->write( 'A1', '0000000' );
1778 The callback function will receive a reference to the calling worksheet
1780 and all of the other arguments that were passed to "write()". The
1781 callback will see an @_ argument list that looks like the following:
1782 $_[0] A ref to the calling worksheet. *
1784 $_[1] Zero based row number.
1785 $_[2] Zero based column number.
1786 $_[3] A number or string or token.
1787 $_[4] A format ref if any.
1788 $_[5] Any other arguments.
1789 ...
1790 * It is good style to shift this off the list so the @_ is the same
1792 as the argument list seen by write().
1793 Your callback should "return()" the return value of the "write_*"
1795 method that was called or "undef" to indicate that you rejected the
1796 match and want "write()" to continue as normal.
1797 So for example if you wished to apply the previous filter only to ID
1799 values that occur in the first column you could modify your callback
1800 function as follows:
1801 sub write_my_id {
1803 my $worksheet = shift;
1804 my $col = $_[1];
1805 if ( $col == 0 ) {
1807 return $worksheet->write_string( @_ );
1808 }
1809 else {
1810 # Reject the match and return control to write()
1811 return undef;
1812 }
1813 }
1814 Now, you will get different behaviour for the first column and other
1816 columns:
1817 $worksheet->write( 'A1', '0000000' ); # Writes 0000000
1819 $worksheet->write( 'B1', '0000000' ); # Writes 0
1820 You may add more than one handler in which case they will be called in
1822 the order that they were added.
1823 Note, the "add_write_handler()" method is particularly suited for
1825 handling dates.
1826 See the "write_handler 1-4" programs in the "examples" directory for
1828 further examples.
1829 insert_image( $row, $col, $filename, $x, $y, $x_scale, $y_scale )
1831 This method can be used to insert a image into a worksheet. The image
1832 can be in PNG, JPEG or BMP format. The $x, $y, $x_scale and $y_scale
1833 parameters are optional.
1834 $worksheet1->insert_image( 'A1', 'perl.bmp' );
1836 $worksheet2->insert_image( 'A1', '../images/perl.bmp' );
1837 $worksheet3->insert_image( 'A1', '.c:\images\perl.bmp' );
1838 The parameters $x and $y can be used to specify an offset from the top
1840 left hand corner of the cell specified by $row and $col. The offset
1841 values are in pixels.
1842 $worksheet1->insert_image('A1', 'perl.bmp', 32, 10);
1844 The offsets can be greater than the width or height of the underlying
1846 cell. This can be occasionally useful if you wish to align two or more
1847 images relative to the same cell.
1848 The parameters $x_scale and $y_scale can be used to scale the inserted
1850 image horizontally and vertically:
1851 # Scale the inserted image: width x 2.0, height x 0.8
1853 $worksheet->insert_image( 'A1', 'perl.bmp', 0, 0, 2, 0.8 );
1854 Note: you must call "set_row()" or "set_column()" before
1856 "insert_image()" if you wish to change the default dimensions of any of
1857 the rows or columns that the image occupies. The height of a row can
1858 also change if you use a font that is larger than the default. This in
1859 turn will affect the scaling of your image. To avoid this you should
1860 explicitly set the height of the row using "set_row()" if it contains a
1861 font size that will change the row height.
1862 BMP images must be 24 bit, true colour, bitmaps. In general it is best
1864 to avoid BMP images since they aren't compressed.
1865 insert_chart( $row, $col, $chart, $x, $y, $x_scale, $y_scale )
1867 This method can be used to insert a Chart object into a worksheet. The
1868 Chart must be created by the "add_chart()" Workbook method and it must
1869 have the "embedded" option set.
1870 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
1872 # Configure the chart.
1874 ...
1875 # Insert the chart into the a worksheet.
1877 $worksheet->insert_chart( 'E2', $chart );
1878 See "add_chart()" for details on how to create the Chart object and
1880 Excel::Writer::XLSX::Chart for details on how to configure it. See also
1881 the "chart_*.pl" programs in the examples directory of the distro.
1882 The $x, $y, $x_scale and $y_scale parameters are optional.
1884 The parameters $x and $y can be used to specify an offset from the top
1886 left hand corner of the cell specified by $row and $col. The offset
1887 values are in pixels.
1888 $worksheet1->insert_chart( 'E2', $chart, 3, 3 );
1890 The parameters $x_scale and $y_scale can be used to scale the inserted
1892 chart horizontally and vertically:
1893 # Scale the width by 120% and the height by 150%
1895 $worksheet->insert_chart( 'E2', $chart, 0, 0, 1.2, 1.5 );
1896 insert_shape( $row, $col, $shape, $x, $y, $x_scale, $y_scale )
1898 This method can be used to insert a Shape object into a worksheet. The
1899 Shape must be created by the "add_shape()" Workbook method.
1900 my $shape = $workbook->add_shape( name => 'My Shape', type => 'plus' );
1902 # Configure the shape.
1904 $shape->set_text('foo');
1905 ...
1906 # Insert the shape into the a worksheet.
1908 $worksheet->insert_shape( 'E2', $shape );
1909 See "add_shape()" for details on how to create the Shape object and
1911 Excel::Writer::XLSX::Shape for details on how to configure it.
1912 The $x, $y, $x_scale and $y_scale parameters are optional.
1914 The parameters $x and $y can be used to specify an offset from the top
1916 left hand corner of the cell specified by $row and $col. The offset
1917 values are in pixels.
1918 $worksheet1->insert_shape( 'E2', $chart, 3, 3 );
1920 The parameters $x_scale and $y_scale can be used to scale the inserted
1922 shape horizontally and vertically:
1923 # Scale the width by 120% and the height by 150%
1925 $worksheet->insert_shape( 'E2', $shape, 0, 0, 1.2, 1.5 );
1926 See also the "shape*.pl" programs in the examples directory of the
1928 distro.
1929 insert_button( $row, $col, { %properties })
1931 The "insert_button()" method can be used to insert an Excel form button
1932 into a worksheet.
1933 This method is generally only useful when used in conjunction with the
1935 Workbook "add_vba_project()" method to tie the button to a macro from
1936 an embedded VBA project:
1937 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
1939 ...
1940 $workbook->add_vba_project( './vbaProject.bin' );
1941 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
1943 The properties of the button that can be set are:
1945 macro
1947 caption
1948 width
1949 height
1950 x_scale
1951 y_scale
1952 x_offset
1953 y_offset
1954 Option: macro
1956 This option is used to set the macro that the button will invoke
1957 when the user clicks on it. The macro should be included using the
1958 Workbook "add_vba_project()" method shown above.
1959 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
1961 The default macro is "ButtonX_Click" where X is the button number.
1963 Option: caption
1965 This option is used to set the caption on the button. The default
1966 is "Button X" where X is the button number.
1967 $worksheet->insert_button( 'C2', { macro => 'my_macro', caption => 'Hello' } );
1969 Option: width
1971 This option is used to set the width of the button in pixels.
1972 $worksheet->insert_button( 'C2', { macro => 'my_macro', width => 128 } );
1974 The default button width is 64 pixels which is the width of a
1976 default cell.
1977 Option: height
1979 This option is used to set the height of the button in pixels.
1980 $worksheet->insert_button( 'C2', { macro => 'my_macro', height => 40 } );
1982 The default button height is 20 pixels which is the height of a
1984 default cell.
1985 Option: x_scale
1987 This option is used to set the width of the button as a factor of
1988 the default width.
1989 $worksheet->insert_button( 'C2', { macro => 'my_macro', x_scale => 2.0 );
1991 Option: y_scale
1993 This option is used to set the height of the button as a factor of
1994 the default height.
1995 $worksheet->insert_button( 'C2', { macro => 'my_macro', y_scale => 2.0 );
1997 Option: x_offset
1999 This option is used to change the x offset, in pixels, of a button
2000 within a cell:
2001 $worksheet->insert_button( 'C2', { macro => 'my_macro', x_offset => 2 );
2003 Option: y_offset
2005 This option is used to change the y offset, in pixels, of a comment
2006 within a cell.
2007 Note: Button is the only Excel form element that is available in
2009 Excel::Writer::XLSX. Form elements represent a lot of work to implement
2010 and the underlying VML syntax isn't very much fun.
2011 data_validation()
2013 The "data_validation()" method is used to construct an Excel data
2014 validation or to limit the user input to a dropdown list of values.
2015 $worksheet->data_validation('B3',
2017 {
2018 validate => 'integer',
2019 criteria => '>',
2020 value => 100,
2021 });
2022 $worksheet->data_validation('B5:B9',
2024 {
2025 validate => 'list',
2026 value => ['open', 'high', 'close'],
2027 });
2028 This method contains a lot of parameters and is described in detail in
2030 a separate section "DATA VALIDATION IN EXCEL".
2031 See also the "data_validate.pl" program in the examples directory of
2033 the distro
2034 conditional_formatting()
2036 The "conditional_formatting()" method is used to add formatting to a
2037 cell or range of cells based on user defined criteria.
2038 $worksheet->conditional_formatting( 'A1:J10',
2040 {
2041 type => 'cell',
2042 criteria => '>=',
2043 value => 50,
2044 format => $format1,
2045 }
2046 );
2047 This method contains a lot of parameters and is described in detail in
2049 a separate section "CONDITIONAL FORMATTING IN EXCEL".
2050 See also the "conditional_format.pl" program in the examples directory
2052 of the distro
2053 add_sparkline()
2055 The "add_sparkline()" worksheet method is used to add sparklines to a
2056 cell or a range of cells.
2057 $worksheet->add_sparkline(
2059 {
2060 location => 'F2',
2061 range => 'Sheet1!A2:E2',
2062 type => 'column',
2063 style => 12,
2064 }
2065 );
2066 This method contains a lot of parameters and is described in detail in
2068 a separate section "SPARKLINES IN EXCEL".
2069 See also the "sparklines1.pl" and "sparklines2.pl" example programs in
2071 the "examples" directory of the distro.
2072 Note: Sparklines are a feature of Excel 2010+ only. You can write them
2074 to an XLSX file that can be read by Excel 2007 but they won't be
2075 displayed.
2076 add_table()
2078 The "add_table()" method is used to group a range of cells into an
2079 Excel Table.
2080 $worksheet->add_table( 'B3:F7', { ... } );
2082 This method contains a lot of parameters and is described in detail in
2084 a separate section "TABLES IN EXCEL".
2085 See also the "tables.pl" program in the examples directory of the
2087 distro
2088 get_name()
2090 The "get_name()" method is used to retrieve the name of a worksheet.
2091 For example:
2092 for my $sheet ( $workbook->sheets() ) {
2094 print $sheet->get_name();
2095 }
2096 For reasons related to the design of Excel::Writer::XLSX and to the
2098 internals of Excel there is no "set_name()" method. The only way to set
2099 the worksheet name is via the "add_worksheet()" method.
2100 activate()
2102 The "activate()" method is used to specify which worksheet is initially
2103 visible in a multi-sheet workbook:
2104 $worksheet1 = $workbook->add_worksheet( 'To' );
2106 $worksheet2 = $workbook->add_worksheet( 'the' );
2107 $worksheet3 = $workbook->add_worksheet( 'wind' );
2108 $worksheet3->activate();
2110 This is similar to the Excel VBA activate method. More than one
2112 worksheet can be selected via the "select()" method, see below, however
2113 only one worksheet can be active.
2114 The default active worksheet is the first worksheet.
2116 select()
2118 The "select()" method is used to indicate that a worksheet is selected
2119 in a multi-sheet workbook:
2120 $worksheet1->activate();
2122 $worksheet2->select();
2123 $worksheet3->select();
2124 A selected worksheet has its tab highlighted. Selecting worksheets is a
2126 way of grouping them together so that, for example, several worksheets
2127 could be printed in one go. A worksheet that has been activated via the
2128 "activate()" method will also appear as selected.
2129 hide()
2131 The "hide()" method is used to hide a worksheet:
2132 $worksheet2->hide();
2134 You may wish to hide a worksheet in order to avoid confusing a user
2136 with intermediate data or calculations.
2137 A hidden worksheet can not be activated or selected so this method is
2139 mutually exclusive with the "activate()" and "select()" methods. In
2140 addition, since the first worksheet will default to being the active
2141 worksheet, you cannot hide the first worksheet without activating
2142 another sheet:
2143 $worksheet2->activate();
2145 $worksheet1->hide();
2146 set_first_sheet()
2148 The "activate()" method determines which worksheet is initially
2149 selected. However, if there are a large number of worksheets the
2150 selected worksheet may not appear on the screen. To avoid this you can
2151 select which is the leftmost visible worksheet using
2152 "set_first_sheet()":
2153 for ( 1 .. 20 ) {
2155 $workbook->add_worksheet;
2156 }
2157 $worksheet21 = $workbook->add_worksheet();
2159 $worksheet22 = $workbook->add_worksheet();
2160 $worksheet21->set_first_sheet();
2162 $worksheet22->activate();
2163 This method is not required very often. The default value is the first
2165 worksheet.
2166 protect( $password, \%options )
2168 The "protect()" method is used to protect a worksheet from
2169 modification:
2170 $worksheet->protect();
2172 The "protect()" method also has the effect of enabling a cell's
2174 "locked" and "hidden" properties if they have been set. A locked cell
2175 cannot be edited and this property is on by default for all cells. A
2176 hidden cell will display the results of a formula but not the formula
2177 itself.
2178 See the "protection.pl" program in the examples directory of the distro
2180 for an illustrative example and the "set_locked" and "set_hidden"
2181 format methods in "CELL FORMATTING".
2182 You can optionally add a password to the worksheet protection:
2184 $worksheet->protect( 'drowssap' );
2186 Passing the empty string '' is the same as turning on protection
2188 without a password.
2189 Note, the worksheet level password in Excel provides very weak
2191 protection. It does not encrypt your data and is very easy to
2192 deactivate. Full workbook encryption is not supported by
2193 "Excel::Writer::XLSX" since it requires a completely different file
2194 format and would take several man months to implement.
2195 You can specify which worksheet elements you wish to protect by passing
2197 a hash_ref with any or all of the following keys:
2198 # Default shown.
2200 %options = (
2201 objects => 0,
2202 scenarios => 0,
2203 format_cells => 0,
2204 format_columns => 0,
2205 format_rows => 0,
2206 insert_columns => 0,
2207 insert_rows => 0,
2208 insert_hyperlinks => 0,
2209 delete_columns => 0,
2210 delete_rows => 0,
2211 select_locked_cells => 1,
2212 sort => 0,
2213 autofilter => 0,
2214 pivot_tables => 0,
2215 select_unlocked_cells => 1,
2216 );
2217 The default boolean values are shown above. Individual elements can be
2219 protected as follows:
2220 $worksheet->protect( 'drowssap', { insert_rows => 1 } );
2222 For chartsheets the allowable options and default values are:
2224 %options = (
2226 objects => 1,
2227 content => 1,
2228 );
2229 set_selection( $first_row, $first_col, $last_row, $last_col )
2231 This method can be used to specify which cell or cells are selected in
2232 a worksheet. The most common requirement is to select a single cell, in
2233 which case $last_row and $last_col can be omitted. The active cell
2234 within a selected range is determined by the order in which $first and
2235 $last are specified. It is also possible to specify a cell or a range
2236 using A1 notation. See the note about "Cell notation".
2237 Examples:
2239 $worksheet1->set_selection( 3, 3 ); # 1. Cell D4.
2241 $worksheet2->set_selection( 3, 3, 6, 6 ); # 2. Cells D4 to G7.
2242 $worksheet3->set_selection( 6, 6, 3, 3 ); # 3. Cells G7 to D4.
2243 $worksheet4->set_selection( 'D4' ); # Same as 1.
2244 $worksheet5->set_selection( 'D4:G7' ); # Same as 2.
2245 $worksheet6->set_selection( 'G7:D4' ); # Same as 3.
2246 The default cell selections is (0, 0), 'A1'.
2248 set_row( $row, $height, $format, $hidden, $level, $collapsed )
2250 This method can be used to change the default properties of a row. All
2251 parameters apart from $row are optional.
2252 The most common use for this method is to change the height of a row:
2254 $worksheet->set_row( 0, 20 ); # Row 1 height set to 20
2256 If you wish to set the format without changing the height you can pass
2258 "undef" as the height parameter:
2259 $worksheet->set_row( 0, undef, $format );
2261 The $format parameter will be applied to any cells in the row that
2263 don't have a format. For example
2264 $worksheet->set_row( 0, undef, $format1 ); # Set the format for row 1
2266 $worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
2267 $worksheet->write( 'B1', 'Hello', $format2 ); # Keeps $format2
2268 If you wish to define a row format in this way you should call the
2270 method before any calls to "write()". Calling it afterwards will
2271 overwrite any format that was previously specified.
2272 The $hidden parameter should be set to 1 if you wish to hide a row.
2274 This can be used, for example, to hide intermediary steps in a
2275 complicated calculation:
2276 $worksheet->set_row( 0, 20, $format, 1 );
2278 $worksheet->set_row( 1, undef, undef, 1 );
2279 The $level parameter is used to set the outline level of the row.
2281 Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent
2282 rows with the same outline level are grouped together into a single
2283 outline.
2284 The following example sets an outline level of 1 for rows 2 and 3
2286 (zero-indexed):
2287 $worksheet->set_row( 1, undef, undef, 0, 1 );
2289 $worksheet->set_row( 2, undef, undef, 0, 1 );
2290 The $hidden parameter can also be used to hide collapsed outlined rows
2292 when used in conjunction with the $level parameter.
2293 $worksheet->set_row( 1, undef, undef, 1, 1 );
2295 $worksheet->set_row( 2, undef, undef, 1, 1 );
2296 For collapsed outlines you should also indicate which row has the
2298 collapsed "+" symbol using the optional $collapsed parameter.
2299 $worksheet->set_row( 3, undef, undef, 0, 0, 1 );
2301 For a more complete example see the "outline.pl" and
2303 "outline_collapsed.pl" programs in the examples directory of the
2304 distro.
2305 Excel allows up to 7 outline levels. Therefore the $level parameter
2307 should be in the range "0 <= $level <= 7".
2308 set_column( $first_col, $last_col, $width, $format, $hidden, $level,
2310 $collapsed )
2311 This method can be used to change the default properties of a single
2312 column or a range of columns. All parameters apart from $first_col and
2313 $last_col are optional.
2314 If "set_column()" is applied to a single column the value of $first_col
2316 and $last_col should be the same. In the case where $last_col is zero
2317 it is set to the same value as $first_col.
2318 It is also possible, and generally clearer, to specify a column range
2320 using the form of A1 notation used for columns. See the note about
2321 "Cell notation".
2322 Examples:
2324 $worksheet->set_column( 0, 0, 20 ); # Column A width set to 20
2326 $worksheet->set_column( 1, 3, 30 ); # Columns B-D width set to 30
2327 $worksheet->set_column( 'E:E', 20 ); # Column E width set to 20
2328 $worksheet->set_column( 'F:H', 30 ); # Columns F-H width set to 30
2329 The width corresponds to the column width value that is specified in
2331 Excel. It is approximately equal to the length of a string in the
2332 default font of Calibri 11. Unfortunately, there is no way to specify
2333 "AutoFit" for a column in the Excel file format. This feature is only
2334 available at runtime from within Excel.
2335 As usual the $format parameter is optional, for additional information,
2337 see "CELL FORMATTING". If you wish to set the format without changing
2338 the width you can pass "undef" as the width parameter:
2339 $worksheet->set_column( 0, 0, undef, $format );
2341 The $format parameter will be applied to any cells in the column that
2343 don't have a format. For example
2344 $worksheet->set_column( 'A:A', undef, $format1 ); # Set format for col 1
2346 $worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
2347 $worksheet->write( 'A2', 'Hello', $format2 ); # Keeps $format2
2348 If you wish to define a column format in this way you should call the
2350 method before any calls to "write()". If you call it afterwards it
2351 won't have any effect.
2352 A default row format takes precedence over a default column format
2354 $worksheet->set_row( 0, undef, $format1 ); # Set format for row 1
2356 $worksheet->set_column( 'A:A', undef, $format2 ); # Set format for col 1
2357 $worksheet->write( 'A1', 'Hello' ); # Defaults to $format1
2358 $worksheet->write( 'A2', 'Hello' ); # Defaults to $format2
2359 The $hidden parameter should be set to 1 if you wish to hide a column.
2361 This can be used, for example, to hide intermediary steps in a
2362 complicated calculation:
2363 $worksheet->set_column( 'D:D', 20, $format, 1 );
2365 $worksheet->set_column( 'E:E', undef, undef, 1 );
2366 The $level parameter is used to set the outline level of the column.
2368 Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent
2369 columns with the same outline level are grouped together into a single
2370 outline.
2371 The following example sets an outline level of 1 for columns B to G:
2373 $worksheet->set_column( 'B:G', undef, undef, 0, 1 );
2375 The $hidden parameter can also be used to hide collapsed outlined
2377 columns when used in conjunction with the $level parameter.
2378 $worksheet->set_column( 'B:G', undef, undef, 1, 1 );
2380 For collapsed outlines you should also indicate which row has the
2382 collapsed "+" symbol using the optional $collapsed parameter.
2383 $worksheet->set_column( 'H:H', undef, undef, 0, 0, 1 );
2385 For a more complete example see the "outline.pl" and
2387 "outline_collapsed.pl" programs in the examples directory of the
2388 distro.
2389 Excel allows up to 7 outline levels. Therefore the $level parameter
2391 should be in the range "0 <= $level <= 7".
2392 set_default_row( $height, $hide_unused_rows )
2394 The "set_default_row()" method is used to set the limited number of
2395 default row properties allowed by Excel. These are the default height
2396 and the option to hide unused rows.
2397 $worksheet->set_default_row( 24 ); # Set the default row height to 24.
2399 The option to hide unused rows is used by Excel as an optimisation so
2401 that the user can hide a large number of rows without generating a very
2402 large file with an entry for each hidden row.
2403 $worksheet->set_default_row( undef, 1 );
2405 See the "hide_row_col.pl" example program.
2407 outline_settings( $visible, $symbols_below, $symbols_right, $auto_style )
2409 The "outline_settings()" method is used to control the appearance of
2410 outlines in Excel. Outlines are described in "OUTLINES AND GROUPING IN
2411 EXCEL".
2412 The $visible parameter is used to control whether or not outlines are
2414 visible. Setting this parameter to 0 will cause all outlines on the
2415 worksheet to be hidden. They can be unhidden in Excel by means of the
2416 "Show Outline Symbols" command button. The default setting is 1 for
2417 visible outlines.
2418 $worksheet->outline_settings( 0 );
2420 The $symbols_below parameter is used to control whether the row outline
2422 symbol will appear above or below the outline level bar. The default
2423 setting is 1 for symbols to appear below the outline level bar.
2424 The $symbols_right parameter is used to control whether the column
2426 outline symbol will appear to the left or the right of the outline
2427 level bar. The default setting is 1 for symbols to appear to the right
2428 of the outline level bar.
2429 The $auto_style parameter is used to control whether the automatic
2431 outline generator in Excel uses automatic styles when creating an
2432 outline. This has no effect on a file generated by
2433 "Excel::Writer::XLSX" but it does have an effect on how the worksheet
2434 behaves after it is created. The default setting is 0 for "Automatic
2435 Styles" to be turned off.
2436 The default settings for all of these parameters correspond to Excel's
2438 default parameters.
2439 The worksheet parameters controlled by "outline_settings()" are rarely
2441 used.
2442 freeze_panes( $row, $col, $top_row, $left_col )
2444 This method can be used to divide a worksheet into horizontal or
2445 vertical regions known as panes and to also "freeze" these panes so
2446 that the splitter bars are not visible. This is the same as the
2447 "Window->Freeze Panes" menu command in Excel
2448 The parameters $row and $col are used to specify the location of the
2450 split. It should be noted that the split is specified at the top or
2451 left of a cell and that the method uses zero based indexing. Therefore
2452 to freeze the first row of a worksheet it is necessary to specify the
2453 split at row 2 (which is 1 as the zero-based index). This might lead
2454 you to think that you are using a 1 based index but this is not the
2455 case.
2456 You can set one of the $row and $col parameters as zero if you do not
2458 want either a vertical or horizontal split.
2459 Examples:
2461 $worksheet->freeze_panes( 1, 0 ); # Freeze the first row
2463 $worksheet->freeze_panes( 'A2' ); # Same using A1 notation
2464 $worksheet->freeze_panes( 0, 1 ); # Freeze the first column
2465 $worksheet->freeze_panes( 'B1' ); # Same using A1 notation
2466 $worksheet->freeze_panes( 1, 2 ); # Freeze first row and first 2 columns
2467 $worksheet->freeze_panes( 'C2' ); # Same using A1 notation
2468 The parameters $top_row and $left_col are optional. They are used to
2470 specify the top-most or left-most visible row or column in the
2471 scrolling region of the panes. For example to freeze the first row and
2472 to have the scrolling region begin at row twenty:
2473 $worksheet->freeze_panes( 1, 0, 20, 0 );
2475 You cannot use A1 notation for the $top_row and $left_col parameters.
2477 See also the "panes.pl" program in the "examples" directory of the
2479 distribution.
2480 split_panes( $y, $x, $top_row, $left_col )
2482 This method can be used to divide a worksheet into horizontal or
2483 vertical regions known as panes. This method is different from the
2484 "freeze_panes()" method in that the splits between the panes will be
2485 visible to the user and each pane will have its own scroll bars.
2486 The parameters $y and $x are used to specify the vertical and
2488 horizontal position of the split. The units for $y and $x are the same
2489 as those used by Excel to specify row height and column width. However,
2490 the vertical and horizontal units are different from each other.
2491 Therefore you must specify the $y and $x parameters in terms of the row
2492 heights and column widths that you have set or the default values which
2493 are 15 for a row and 8.43 for a column.
2494 You can set one of the $y and $x parameters as zero if you do not want
2496 either a vertical or horizontal split. The parameters $top_row and
2497 $left_col are optional. They are used to specify the top-most or left-
2498 most visible row or column in the bottom-right pane.
2499 Example:
2501 $worksheet->split_panes( 15, 0, ); # First row
2503 $worksheet->split_panes( 0, 8.43 ); # First column
2504 $worksheet->split_panes( 15, 8.43 ); # First row and column
2505 You cannot use A1 notation with this method.
2507 See also the "freeze_panes()" method and the "panes.pl" program in the
2509 "examples" directory of the distribution.
2510 merge_range( $first_row, $first_col, $last_row, $last_col, $token, $format
2512 )
2513 The "merge_range()" method allows you to merge cells that contain other
2514 types of alignment in addition to the merging:
2515 my $format = $workbook->add_format(
2517 border => 6,
2518 valign => 'vcenter',
2519 align => 'center',
2520 );
2521 $worksheet->merge_range( 'B3:D4', 'Vertical and horizontal', $format );
2523 "merge_range()" writes its $token argument using the worksheet
2525 "write()" method. Therefore it will handle numbers, strings, formulas
2526 or urls as required. If you need to specify the required "write_*()"
2527 method use the "merge_range_type()" method, see below.
2528 The full possibilities of this method are shown in the "merge3.pl" to
2530 "merge6.pl" programs in the "examples" directory of the distribution.
2531 merge_range_type( $type, $first_row, $first_col, $last_row, $last_col, ...
2533 )
2534 The "merge_range()" method, see above, uses "write()" to insert the
2535 required data into to a merged range. However, there may be times where
2536 this isn't what you require so as an alternative the "merge_range_type
2537 ()" method allows you to specify the type of data you wish to write.
2538 For example:
2539 $worksheet->merge_range_type( 'number', 'B2:C2', 123, $format1 );
2541 $worksheet->merge_range_type( 'string', 'B4:C4', 'foo', $format2 );
2542 $worksheet->merge_range_type( 'formula', 'B6:C6', '=1+2', $format3 );
2543 The $type must be one of the following, which corresponds to a
2545 "write_*()" method:
2546 'number'
2548 'string'
2549 'formula'
2550 'array_formula'
2551 'blank'
2552 'rich_string'
2553 'date_time'
2554 'url'
2555 Any arguments after the range should be whatever the appropriate method
2557 accepts:
2558 $worksheet->merge_range_type( 'rich_string', 'B8:C8',
2560 'This is ', $bold, 'bold', $format4 );
2561 Note, you must always pass a $format object as an argument, even if it
2563 is a default format.
2564 set_zoom( $scale )
2566 Set the worksheet zoom factor in the range "10 <= $scale <= 400":
2567 $worksheet1->set_zoom( 50 );
2569 $worksheet2->set_zoom( 75 );
2570 $worksheet3->set_zoom( 300 );
2571 $worksheet4->set_zoom( 400 );
2572 The default zoom factor is 100. You cannot zoom to "Selection" because
2574 it is calculated by Excel at run-time.
2575 Note, "set_zoom()" does not affect the scale of the printed page. For
2577 that you should use "set_print_scale()".
2578 right_to_left()
2580 The "right_to_left()" method is used to change the default direction of
2581 the worksheet from left-to-right, with the A1 cell in the top left, to
2582 right-to-left, with the A1 cell in the top right.
2583 $worksheet->right_to_left();
2585 This is useful when creating Arabic, Hebrew or other near or far
2587 eastern worksheets that use right-to-left as the default direction.
2588 hide_zero()
2590 The "hide_zero()" method is used to hide any zero values that appear in
2591 cells.
2592 $worksheet->hide_zero();
2594 In Excel this option is found under Tools->Options->View.
2596 set_tab_color()
2598 The "set_tab_color()" method is used to change the colour of the
2599 worksheet tab. You can use one of the standard colour names provided by
2600 the Format object or a Html style "#RRGGBB" colour. See "WORKING WITH
2601 COLOURS".
2602 $worksheet1->set_tab_color( 'red' );
2604 $worksheet2->set_tab_color( '#FF6600' );
2605 See the "tab_colors.pl" program in the examples directory of the
2607 distro.
2608 autofilter( $first_row, $first_col, $last_row, $last_col )
2610 This method allows an autofilter to be added to a worksheet. An
2611 autofilter is a way of adding drop down lists to the headers of a 2D
2612 range of worksheet data. This allows users to filter the data based on
2613 simple criteria so that some data is shown and some is hidden.
2614 To add an autofilter to a worksheet:
2616 $worksheet->autofilter( 0, 0, 10, 3 );
2618 $worksheet->autofilter( 'A1:D11' ); # Same as above in A1 notation.
2619 Filter conditions can be applied using the "filter_column()" or
2621 "filter_column_list()" method.
2622 See the "autofilter.pl" program in the examples directory of the distro
2624 for a more detailed example.
2625 filter_column( $column, $expression )
2627 The "filter_column" method can be used to filter columns in a
2628 autofilter range based on simple conditions.
2629 NOTE: It isn't sufficient to just specify the filter condition. You
2631 must also hide any rows that don't match the filter condition. Rows are
2632 hidden using the "set_row()" "visible" parameter. "Excel::Writer::XLSX"
2633 cannot do this automatically since it isn't part of the file format.
2634 See the "autofilter.pl" program in the examples directory of the distro
2635 for an example.
2636 The conditions for the filter are specified using simple expressions:
2638 $worksheet->filter_column( 'A', 'x > 2000' );
2640 $worksheet->filter_column( 'B', 'x > 2000 and x < 5000' );
2641 The $column parameter can either be a zero indexed column number or a
2643 string column name.
2644 The following operators are available:
2646 Operator Synonyms
2648 == = eq =~
2649 != <> ne !=
2650 >
2651 <
2652 >=
2653 <=
2654 and &&
2656 or ||
2657 The operator synonyms are just syntactic sugar to make you more
2659 comfortable using the expressions. It is important to remember that the
2660 expressions will be interpreted by Excel and not by perl.
2661 An expression can comprise a single statement or two statements
2663 separated by the "and" and "or" operators. For example:
2664 'x < 2000'
2666 'x > 2000'
2667 'x == 2000'
2668 'x > 2000 and x < 5000'
2669 'x == 2000 or x == 5000'
2670 Filtering of blank or non-blank data can be achieved by using a value
2672 of "Blanks" or "NonBlanks" in the expression:
2673 'x == Blanks'
2675 'x == NonBlanks'
2676 Excel also allows some simple string matching operations:
2678 'x =~ b*' # begins with b
2680 'x !~ b*' # doesn't begin with b
2681 'x =~ *b' # ends with b
2682 'x !~ *b' # doesn't end with b
2683 'x =~ *b*' # contains b
2684 'x !~ *b*' # doesn't contains b
2685 You can also use "*" to match any character or number and "?" to match
2687 any single character or number. No other regular expression quantifier
2688 is supported by Excel's filters. Excel's regular expression characters
2689 can be escaped using "~".
2690 The placeholder variable "x" in the above examples can be replaced by
2692 any simple string. The actual placeholder name is ignored internally so
2693 the following are all equivalent:
2694 'x < 2000'
2696 'col < 2000'
2697 'Price < 2000'
2698 Also, note that a filter condition can only be applied to a column in a
2700 range specified by the "autofilter()" Worksheet method.
2701 See the "autofilter.pl" program in the examples directory of the distro
2703 for a more detailed example.
2704 Note Spreadsheet::WriteExcel supports Top 10 style filters. These
2706 aren't currently supported by Excel::Writer::XLSX but may be added
2707 later.
2708 filter_column_list( $column, @matches )
2710 Prior to Excel 2007 it was only possible to have either 1 or 2 filter
2711 conditions such as the ones shown above in the "filter_column" method.
2712 Excel 2007 introduced a new list style filter where it is possible to
2714 specify 1 or more 'or' style criteria. For example if your column
2715 contained data for the first six months the initial data would be
2716 displayed as all selected as shown on the left. Then if you selected
2717 'March', 'April' and 'May' they would be displayed as shown on the
2718 right.
2719 No criteria selected Some criteria selected.
2721 [/] (Select all) [X] (Select all)
2723 [/] January [ ] January
2724 [/] February [ ] February
2725 [/] March [/] March
2726 [/] April [/] April
2727 [/] May [/] May
2728 [/] June [ ] June
2729 The "filter_column_list()" method can be used to represent these types
2731 of filters:
2732 $worksheet->filter_column_list( 'A', 'March', 'April', 'May' );
2734 The $column parameter can either be a zero indexed column number or a
2736 string column name.
2737 One or more criteria can be selected:
2739 $worksheet->filter_column_list( 0, 'March' );
2741 $worksheet->filter_column_list( 1, 100, 110, 120, 130 );
2742 NOTE: It isn't sufficient to just specify the filter condition. You
2744 must also hide any rows that don't match the filter condition. Rows are
2745 hidden using the "set_row()" "visible" parameter. "Excel::Writer::XLSX"
2746 cannot do this automatically since it isn't part of the file format.
2747 See the "autofilter.pl" program in the examples directory of the distro
2748 for an example.
2749 convert_date_time( $date_string )
2751 The "convert_date_time()" method is used internally by the
2752 "write_date_time()" method to convert date strings to a number that
2753 represents an Excel date and time.
2754 It is exposed as a public method for utility purposes.
2756 The $date_string format is detailed in the "write_date_time()" method.
2758 Worksheet set_vba_name()
2760 The Worksheet "set_vba_name()" method can be used to set the VBA
2761 codename for the worksheet (there is a similar method for the workbook
2762 VBA name). This is sometimes required when a "vbaProject" macro
2763 included via "add_vba_project()" refers to the worksheet. The default
2764 Excel VBA name of "Sheet1", etc., is used if a user defined name isn't
2765 specified.
2766 See also "WORKING WITH VBA MACROS".
2768
2770 Page set-up methods affect the way that a worksheet looks when it is
2771 printed. They control features such as page headers and footers and
2772 margins. These methods are really just standard worksheet methods. They
2773 are documented here in a separate section for the sake of clarity.
2774 The following methods are available for page set-up:
2776 set_landscape()
2778 set_portrait()
2779 set_page_view()
2780 set_paper()
2781 center_horizontally()
2782 center_vertically()
2783 set_margins()
2784 set_header()
2785 set_footer()
2786 repeat_rows()
2787 repeat_columns()
2788 hide_gridlines()
2789 print_row_col_headers()
2790 print_area()
2791 print_across()
2792 fit_to_pages()
2793 set_start_page()
2794 set_print_scale()
2795 print_black_and_white()
2796 set_h_pagebreaks()
2797 set_v_pagebreaks()
2798 A common requirement when working with Excel::Writer::XLSX is to apply
2800 the same page set-up features to all of the worksheets in a workbook.
2801 To do this you can use the "sheets()" method of the "workbook" class to
2802 access the array of worksheets in a workbook:
2803 for $worksheet ( $workbook->sheets() ) {
2805 $worksheet->set_landscape();
2806 }
2807 set_landscape()
2809 This method is used to set the orientation of a worksheet's printed
2810 page to landscape:
2811 $worksheet->set_landscape(); # Landscape mode
2813 set_portrait()
2815 This method is used to set the orientation of a worksheet's printed
2816 page to portrait. The default worksheet orientation is portrait, so you
2817 won't generally need to call this method.
2818 $worksheet->set_portrait(); # Portrait mode
2820 set_page_view()
2822 This method is used to display the worksheet in "Page View/Layout"
2823 mode.
2824 $worksheet->set_page_view();
2826 set_paper( $index )
2828 This method is used to set the paper format for the printed output of a
2829 worksheet. The following paper styles are available:
2830 Index Paper format Paper size
2832 ===== ============ ==========
2833 0 Printer default -
2834 1 Letter 8 1/2 x 11 in
2835 2 Letter Small 8 1/2 x 11 in
2836 3 Tabloid 11 x 17 in
2837 4 Ledger 17 x 11 in
2838 5 Legal 8 1/2 x 14 in
2839 6 Statement 5 1/2 x 8 1/2 in
2840 7 Executive 7 1/4 x 10 1/2 in
2841 8 A3 297 x 420 mm
2842 9 A4 210 x 297 mm
2843 10 A4 Small 210 x 297 mm
2844 11 A5 148 x 210 mm
2845 12 B4 250 x 354 mm
2846 13 B5 182 x 257 mm
2847 14 Folio 8 1/2 x 13 in
2848 15 Quarto 215 x 275 mm
2849 16 - 10x14 in
2850 17 - 11x17 in
2851 18 Note 8 1/2 x 11 in
2852 19 Envelope 9 3 7/8 x 8 7/8
2853 20 Envelope 10 4 1/8 x 9 1/2
2854 21 Envelope 11 4 1/2 x 10 3/8
2855 22 Envelope 12 4 3/4 x 11
2856 23 Envelope 14 5 x 11 1/2
2857 24 C size sheet -
2858 25 D size sheet -
2859 26 E size sheet -
2860 27 Envelope DL 110 x 220 mm
2861 28 Envelope C3 324 x 458 mm
2862 29 Envelope C4 229 x 324 mm
2863 30 Envelope C5 162 x 229 mm
2864 31 Envelope C6 114 x 162 mm
2865 32 Envelope C65 114 x 229 mm
2866 33 Envelope B4 250 x 353 mm
2867 34 Envelope B5 176 x 250 mm
2868 35 Envelope B6 176 x 125 mm
2869 36 Envelope 110 x 230 mm
2870 37 Monarch 3.875 x 7.5 in
2871 38 Envelope 3 5/8 x 6 1/2 in
2872 39 Fanfold 14 7/8 x 11 in
2873 40 German Std Fanfold 8 1/2 x 12 in
2874 41 German Legal Fanfold 8 1/2 x 13 in
2875 Note, it is likely that not all of these paper types will be available
2877 to the end user since it will depend on the paper formats that the
2878 user's printer supports. Therefore, it is best to stick to standard
2879 paper types.
2880 $worksheet->set_paper( 1 ); # US Letter
2882 $worksheet->set_paper( 9 ); # A4
2883 If you do not specify a paper type the worksheet will print using the
2885 printer's default paper.
2886 center_horizontally()
2888 Center the worksheet data horizontally between the margins on the
2889 printed page:
2890 $worksheet->center_horizontally();
2892 center_vertically()
2894 Center the worksheet data vertically between the margins on the printed
2895 page:
2896 $worksheet->center_vertically();
2898 set_margins( $inches )
2900 There are several methods available for setting the worksheet margins
2901 on the printed page:
2902 set_margins() # Set all margins to the same value
2904 set_margins_LR() # Set left and right margins to the same value
2905 set_margins_TB() # Set top and bottom margins to the same value
2906 set_margin_left(); # Set left margin
2907 set_margin_right(); # Set right margin
2908 set_margin_top(); # Set top margin
2909 set_margin_bottom(); # Set bottom margin
2910 All of these methods take a distance in inches as a parameter. Note: 1
2912 inch = 25.4mm. ";-)" The default left and right margin is 0.7 inch. The
2913 default top and bottom margin is 0.75 inch. Note, these defaults are
2914 different from the defaults used in the binary file format by
2915 Spreadsheet::WriteExcel.
2916 set_header( $string, $margin )
2918 Headers and footers are generated using a $string which is a
2919 combination of plain text and control characters. The $margin parameter
2920 is optional.
2921 The available control character are:
2923 Control Category Description
2925 ======= ======== ===========
2926 &L Justification Left
2927 &C Center
2928 &R Right
2929 &P Information Page number
2931 &N Total number of pages
2932 &D Date
2933 &T Time
2934 &F File name
2935 &A Worksheet name
2936 &Z Workbook path
2937 &fontsize Font Font size
2939 &"font,style" Font name and style
2940 &U Single underline
2941 &E Double underline
2942 &S Strikethrough
2943 &X Superscript
2944 &Y Subscript
2945 &[Picture] Images Image placeholder
2947 &G Same as &[Picture]
2948 && Miscellaneous Literal ampersand &
2950 Text in headers and footers can be justified (aligned) to the left,
2952 center and right by prefixing the text with the control characters &L,
2953 &C and &R.
2954 For example (with ASCII art representation of the results):
2956 $worksheet->set_header('&LHello');
2958 ---------------------------------------------------------------
2960 | |
2961 | Hello |
2962 | |
2963 $worksheet->set_header('&CHello');
2966 ---------------------------------------------------------------
2968 | |
2969 | Hello |
2970 | |
2971 $worksheet->set_header('&RHello');
2974 ---------------------------------------------------------------
2976 | |
2977 | Hello |
2978 | |
2979 For simple text, if you do not specify any justification the text will
2981 be centred. However, you must prefix the text with &C if you specify a
2982 font name or any other formatting:
2983 $worksheet->set_header('Hello');
2985 ---------------------------------------------------------------
2987 | |
2988 | Hello |
2989 | |
2990 You can have text in each of the justification regions:
2992 $worksheet->set_header('&LCiao&CBello&RCielo');
2994 ---------------------------------------------------------------
2996 | |
2997 | Ciao Bello Cielo |
2998 | |
2999 The information control characters act as variables that Excel will
3001 update as the workbook or worksheet changes. Times and dates are in the
3002 users default format:
3003 $worksheet->set_header('&CPage &P of &N');
3005 ---------------------------------------------------------------
3007 | |
3008 | Page 1 of 6 |
3009 | |
3010 $worksheet->set_header('&CUpdated at &T');
3013 ---------------------------------------------------------------
3015 | |
3016 | Updated at 12:30 PM |
3017 | |
3018 Images can be inserted using the options shown below. Each image must
3020 have a placeholder in header string using the "&[Picture]" or &G
3021 control characters:
3022 $worksheet->set_header( '&L&G', 0.3, { image_left => 'logo.jpg' });
3024 You can specify the font size of a section of the text by prefixing it
3026 with the control character &n where "n" is the font size:
3027 $worksheet1->set_header( '&C&30Hello Big' );
3029 $worksheet2->set_header( '&C&10Hello Small' );
3030 You can specify the font of a section of the text by prefixing it with
3032 the control sequence "&"font,style"" where "fontname" is a font name
3033 such as "Courier New" or "Times New Roman" and "style" is one of the
3034 standard Windows font descriptions: "Regular", "Italic", "Bold" or
3035 "Bold Italic":
3036 $worksheet1->set_header( '&C&"Courier New,Italic"Hello' );
3038 $worksheet2->set_header( '&C&"Courier New,Bold Italic"Hello' );
3039 $worksheet3->set_header( '&C&"Times New Roman,Regular"Hello' );
3040 It is possible to combine all of these features together to create
3042 sophisticated headers and footers. As an aid to setting up complicated
3043 headers and footers you can record a page set-up as a macro in Excel
3044 and look at the format strings that VBA produces. Remember however that
3045 VBA uses two double quotes "" to indicate a single double quote. For
3046 the last example above the equivalent VBA code looks like this:
3047 .LeftHeader = ""
3049 .CenterHeader = "&""Times New Roman,Regular""Hello"
3050 .RightHeader = ""
3051 To include a single literal ampersand "&" in a header or footer you
3053 should use a double ampersand "&&":
3054 $worksheet1->set_header('&CCuriouser && Curiouser - Attorneys at Law');
3056 As stated above the margin parameter is optional. As with the other
3058 margins the value should be in inches. The default header and footer
3059 margin is 0.3 inch. Note, the default margin is different from the
3060 default used in the binary file format by Spreadsheet::WriteExcel. The
3061 header and footer margin size can be set as follows:
3062 $worksheet->set_header( '&CHello', 0.75 );
3064 The header and footer margins are independent of the top and bottom
3066 margins.
3067 The available options are:
3069 · "image_left" The path to the image. Requires a &G or "&[Picture]"
3071 placeholder.
3072 · "image_center" Same as above.
3074 · "image_right" Same as above.
3076 · "scale_with_doc" Scale header with document. Defaults to true.
3078 · "align_with_margins" Align header to margins. Defaults to true.
3080 The image options must have an accompanying "&[Picture]" or &G control
3082 character in the header string:
3083 $worksheet->set_header(
3085 '&L&[Picture]&C&[Picture]&R&[Picture]',
3086 undef, # If you don't want to change the margin.
3087 {
3088 image_left => 'red.jpg',
3089 image_center => 'blue.jpg',
3090 image_right => 'yellow.jpg'
3091 }
3092 );
3093 Note, the header or footer string must be less than 255 characters.
3095 Strings longer than this will not be written and a warning will be
3096 generated.
3097 The "set_header()" method can also handle Unicode strings in "UTF-8"
3099 format.
3100 $worksheet->set_header( "&C\x{263a}" )
3102 See, also the "headers.pl" program in the "examples" directory of the
3104 distribution.
3105 set_footer( $string, $margin )
3107 The syntax of the "set_footer()" method is the same as "set_header()",
3108 see above.
3109 repeat_rows( $first_row, $last_row )
3111 Set the number of rows to repeat at the top of each printed page.
3112 For large Excel documents it is often desirable to have the first row
3114 or rows of the worksheet print out at the top of each page. This can be
3115 achieved by using the "repeat_rows()" method. The parameters $first_row
3116 and $last_row are zero based. The $last_row parameter is optional if
3117 you only wish to specify one row:
3118 $worksheet1->repeat_rows( 0 ); # Repeat the first row
3120 $worksheet2->repeat_rows( 0, 1 ); # Repeat the first two rows
3121 repeat_columns( $first_col, $last_col )
3123 Set the columns to repeat at the left hand side of each printed page.
3124 For large Excel documents it is often desirable to have the first
3126 column or columns of the worksheet print out at the left hand side of
3127 each page. This can be achieved by using the "repeat_columns()" method.
3128 The parameters $first_column and $last_column are zero based. The
3129 $last_column parameter is optional if you only wish to specify one
3130 column. You can also specify the columns using A1 column notation, see
3131 the note about "Cell notation".
3132 $worksheet1->repeat_columns( 0 ); # Repeat the first column
3134 $worksheet2->repeat_columns( 0, 1 ); # Repeat the first two columns
3135 $worksheet3->repeat_columns( 'A:A' ); # Repeat the first column
3136 $worksheet4->repeat_columns( 'A:B' ); # Repeat the first two columns
3137 hide_gridlines( $option )
3139 This method is used to hide the gridlines on the screen and printed
3140 page. Gridlines are the lines that divide the cells on a worksheet.
3141 Screen and printed gridlines are turned on by default in an Excel
3142 worksheet. If you have defined your own cell borders you may wish to
3143 hide the default gridlines.
3144 $worksheet->hide_gridlines();
3146 The following values of $option are valid:
3148 0 : Don't hide gridlines
3150 1 : Hide printed gridlines only
3151 2 : Hide screen and printed gridlines
3152 If you don't supply an argument or use "undef" the default option is 1,
3154 i.e. only the printed gridlines are hidden.
3155 print_row_col_headers()
3157 Set the option to print the row and column headers on the printed page.
3158 An Excel worksheet looks something like the following;
3160 ------------------------------------------
3162 | | A | B | C | D | ...
3163 ------------------------------------------
3164 | 1 | | | | | ...
3165 | 2 | | | | | ...
3166 | 3 | | | | | ...
3167 | 4 | | | | | ...
3168 |...| ... | ... | ... | ... | ...
3169 The headers are the letters and numbers at the top and the left of the
3171 worksheet. Since these headers serve mainly as a indication of position
3172 on the worksheet they generally do not appear on the printed page. If
3173 you wish to have them printed you can use the "print_row_col_headers()"
3174 method:
3175 $worksheet->print_row_col_headers();
3177 Do not confuse these headers with page headers as described in the
3179 "set_header()" section above.
3180 hide_row_col_headers()
3182 Similar to "print_row_col_headers()" above but set the option to hide
3183 the row and column headers within Excel so that they aren't visible to
3184 the user:
3185 $worksheet->hide_row_col_headers();
3187 print_area( $first_row, $first_col, $last_row, $last_col )
3189 This method is used to specify the area of the worksheet that will be
3190 printed. All four parameters must be specified. You can also use A1
3191 notation, see the note about "Cell notation".
3192 $worksheet1->print_area( 'A1:H20' ); # Cells A1 to H20
3194 $worksheet2->print_area( 0, 0, 19, 7 ); # The same
3195 $worksheet2->print_area( 'A:H' ); # Columns A to H if rows have data
3196 print_across()
3198 The "print_across" method is used to change the default print
3199 direction. This is referred to by Excel as the sheet "page order".
3200 $worksheet->print_across();
3202 The default page order is shown below for a worksheet that extends over
3204 4 pages. The order is called "down then across":
3205 [1] [3]
3207 [2] [4]
3208 However, by using the "print_across" method the print order will be
3210 changed to "across then down":
3211 [1] [2]
3213 [3] [4]
3214 fit_to_pages( $width, $height )
3216 The "fit_to_pages()" method is used to fit the printed area to a
3217 specific number of pages both vertically and horizontally. If the
3218 printed area exceeds the specified number of pages it will be scaled
3219 down to fit. This guarantees that the printed area will always appear
3220 on the specified number of pages even if the page size or margins
3221 change.
3222 $worksheet1->fit_to_pages( 1, 1 ); # Fit to 1x1 pages
3224 $worksheet2->fit_to_pages( 2, 1 ); # Fit to 2x1 pages
3225 $worksheet3->fit_to_pages( 1, 2 ); # Fit to 1x2 pages
3226 The print area can be defined using the "print_area()" method as
3228 described above.
3229 A common requirement is to fit the printed output to n pages wide but
3231 have the height be as long as necessary. To achieve this set the
3232 $height to zero:
3233 $worksheet1->fit_to_pages( 1, 0 ); # 1 page wide and as long as necessary
3235 Note that although it is valid to use both "fit_to_pages()" and
3237 "set_print_scale()" on the same worksheet only one of these options can
3238 be active at a time. The last method call made will set the active
3239 option.
3240 Note that "fit_to_pages()" will override any manual page breaks that
3242 are defined in the worksheet.
3243 Note: When using "fit_to_pages()" it may also be required to set the
3245 printer paper size using "set_paper()" or else Excel will default to
3246 "US Letter".
3247 set_start_page( $start_page )
3249 The "set_start_page()" method is used to set the number of the starting
3250 page when the worksheet is printed out. The default value is 1.
3251 $worksheet->set_start_page( 2 );
3253 set_print_scale( $scale )
3255 Set the scale factor of the printed page. Scale factors in the range
3256 "10 <= $scale <= 400" are valid:
3257 $worksheet1->set_print_scale( 50 );
3259 $worksheet2->set_print_scale( 75 );
3260 $worksheet3->set_print_scale( 300 );
3261 $worksheet4->set_print_scale( 400 );
3262 The default scale factor is 100. Note, "set_print_scale()" does not
3264 affect the scale of the visible page in Excel. For that you should use
3265 "set_zoom()".
3266 Note also that although it is valid to use both "fit_to_pages()" and
3268 "set_print_scale()" on the same worksheet only one of these options can
3269 be active at a time. The last method call made will set the active
3270 option.
3271 print_black_and_white()
3273 Set the option to print the worksheet in black and white:
3274 $worksheet->print_black_and_white();
3276 set_h_pagebreaks( @breaks )
3278 Add horizontal page breaks to a worksheet. A page break causes all the
3279 data that follows it to be printed on the next page. Horizontal page
3280 breaks act between rows. To create a page break between rows 20 and 21
3281 you must specify the break at row 21. However in zero index notation
3282 this is actually row 20. So you can pretend for a small while that you
3283 are using 1 index notation:
3284 $worksheet1->set_h_pagebreaks( 20 ); # Break between row 20 and 21
3286 The "set_h_pagebreaks()" method will accept a list of page breaks and
3288 you can call it more than once:
3289 $worksheet2->set_h_pagebreaks( 20, 40, 60, 80, 100 ); # Add breaks
3291 $worksheet2->set_h_pagebreaks( 120, 140, 160, 180, 200 ); # Add some more
3292 Note: If you specify the "fit to page" option via the "fit_to_pages()"
3294 method it will override all manual page breaks.
3295 There is a silent limitation of about 1000 horizontal page breaks per
3297 worksheet in line with an Excel internal limitation.
3298 set_v_pagebreaks( @breaks )
3300 Add vertical page breaks to a worksheet. A page break causes all the
3301 data that follows it to be printed on the next page. Vertical page
3302 breaks act between columns. To create a page break between columns 20
3303 and 21 you must specify the break at column 21. However in zero index
3304 notation this is actually column 20. So you can pretend for a small
3305 while that you are using 1 index notation:
3306 $worksheet1->set_v_pagebreaks(20); # Break between column 20 and 21
3308 The "set_v_pagebreaks()" method will accept a list of page breaks and
3310 you can call it more than once:
3311 $worksheet2->set_v_pagebreaks( 20, 40, 60, 80, 100 ); # Add breaks
3313 $worksheet2->set_v_pagebreaks( 120, 140, 160, 180, 200 ); # Add some more
3314 Note: If you specify the "fit to page" option via the "fit_to_pages()"
3316 method it will override all manual page breaks.
3317
3319 This section describes the methods and properties that are available
3320 for formatting cells in Excel. The properties of a cell that can be
3321 formatted include: fonts, colours, patterns, borders, alignment and
3322 number formatting.
3323 Creating and using a Format object
3325 Cell formatting is defined through a Format object. Format objects are
3326 created by calling the workbook "add_format()" method as follows:
3327 my $format1 = $workbook->add_format(); # Set properties later
3329 my $format2 = $workbook->add_format( %props ); # Set at creation
3330 The format object holds all the formatting properties that can be
3332 applied to a cell, a row or a column. The process of setting these
3333 properties is discussed in the next section.
3334 Once a Format object has been constructed and its properties have been
3336 set it can be passed as an argument to the worksheet "write" methods as
3337 follows:
3338 $worksheet->write( 0, 0, 'One', $format );
3340 $worksheet->write_string( 1, 0, 'Two', $format );
3341 $worksheet->write_number( 2, 0, 3, $format );
3342 $worksheet->write_blank( 3, 0, $format );
3343 Formats can also be passed to the worksheet "set_row()" and
3345 "set_column()" methods to define the default property for a row or
3346 column.
3347 $worksheet->set_row( 0, 15, $format );
3349 $worksheet->set_column( 0, 0, 15, $format );
3350 Format methods and Format properties
3352 The following table shows the Excel format categories, the formatting
3353 properties that can be applied and the equivalent object method:
3354 Category Description Property Method Name
3356 -------- ----------- -------- -----------
3357 Font Font type font set_font()
3358 Font size size set_size()
3359 Font color color set_color()
3360 Bold bold set_bold()
3361 Italic italic set_italic()
3362 Underline underline set_underline()
3363 Strikeout font_strikeout set_font_strikeout()
3364 Super/Subscript font_script set_font_script()
3365 Outline font_outline set_font_outline()
3366 Shadow font_shadow set_font_shadow()
3367 Number Numeric format num_format set_num_format()
3369 Protection Lock cells locked set_locked()
3371 Hide formulas hidden set_hidden()
3372 Alignment Horizontal align align set_align()
3374 Vertical align valign set_align()
3375 Rotation rotation set_rotation()
3376 Text wrap text_wrap set_text_wrap()
3377 Justify last text_justlast set_text_justlast()
3378 Center across center_across set_center_across()
3379 Indentation indent set_indent()
3380 Shrink to fit shrink set_shrink()
3381 Pattern Cell pattern pattern set_pattern()
3383 Background color bg_color set_bg_color()
3384 Foreground color fg_color set_fg_color()
3385 Border Cell border border set_border()
3387 Bottom border bottom set_bottom()
3388 Top border top set_top()
3389 Left border left set_left()
3390 Right border right set_right()
3391 Border color border_color set_border_color()
3392 Bottom color bottom_color set_bottom_color()
3393 Top color top_color set_top_color()
3394 Left color left_color set_left_color()
3395 Right color right_color set_right_color()
3396 Diagonal type diag_type set_diag_type()
3397 Diagonal border diag_border set_diag_border()
3398 Diagonal color diag_color set_diag_color()
3399 There are two ways of setting Format properties: by using the object
3401 method interface or by setting the property directly. For example, a
3402 typical use of the method interface would be as follows:
3403 my $format = $workbook->add_format();
3405 $format->set_bold();
3406 $format->set_color( 'red' );
3407 By comparison the properties can be set directly by passing a hash of
3409 properties to the Format constructor:
3410 my $format = $workbook->add_format( bold => 1, color => 'red' );
3412 or after the Format has been constructed by means of the
3414 "set_format_properties()" method as follows:
3415 my $format = $workbook->add_format();
3417 $format->set_format_properties( bold => 1, color => 'red' );
3418 You can also store the properties in one or more named hashes and pass
3420 them to the required method:
3421 my %font = (
3423 font => 'Calibri',
3424 size => 12,
3425 color => 'blue',
3426 bold => 1,
3427 );
3428 my %shading = (
3430 bg_color => 'green',
3431 pattern => 1,
3432 );
3433 my $format1 = $workbook->add_format( %font ); # Font only
3436 my $format2 = $workbook->add_format( %font, %shading ); # Font and shading
3437 The provision of two ways of setting properties might lead you to
3439 wonder which is the best way. The method mechanism may be better if you
3440 prefer setting properties via method calls (which the author did when
3441 the code was first written) otherwise passing properties to the
3442 constructor has proved to be a little more flexible and self
3443 documenting in practice. An additional advantage of working with
3444 property hashes is that it allows you to share formatting between
3445 workbook objects as shown in the example above.
3446 The Perl/Tk style of adding properties is also supported:
3448 my %font = (
3450 -font => 'Calibri',
3451 -size => 12,
3452 -color => 'blue',
3453 -bold => 1,
3454 );
3455 Working with formats
3457 The default format is Calibri 11 with all other properties off.
3458 Each unique format in Excel::Writer::XLSX must have a corresponding
3460 Format object. It isn't possible to use a Format with a write() method
3461 and then redefine the Format for use at a later stage. This is because
3462 a Format is applied to a cell not in its current state but in its final
3463 state. Consider the following example:
3464 my $format = $workbook->add_format();
3466 $format->set_bold();
3467 $format->set_color( 'red' );
3468 $worksheet->write( 'A1', 'Cell A1', $format );
3469 $format->set_color( 'green' );
3470 $worksheet->write( 'B1', 'Cell B1', $format );
3471 Cell A1 is assigned the Format $format which is initially set to the
3473 colour red. However, the colour is subsequently set to green. When
3474 Excel displays Cell A1 it will display the final state of the Format
3475 which in this case will be the colour green.
3476 In general a method call without an argument will turn a property on,
3478 for example:
3479 my $format1 = $workbook->add_format();
3481 $format1->set_bold(); # Turns bold on
3482 $format1->set_bold( 1 ); # Also turns bold on
3483 $format1->set_bold( 0 ); # Turns bold off
3484
3486 The Format object methods are described in more detail in the following
3487 sections. In addition, there is a Perl program called "formats.pl" in
3488 the "examples" directory of the WriteExcel distribution. This program
3489 creates an Excel workbook called "formats.xlsx" which contains examples
3490 of almost all the format types.
3491 The following Format methods are available:
3493 set_font()
3495 set_size()
3496 set_color()
3497 set_bold()
3498 set_italic()
3499 set_underline()
3500 set_font_strikeout()
3501 set_font_script()
3502 set_font_outline()
3503 set_font_shadow()
3504 set_num_format()
3505 set_locked()
3506 set_hidden()
3507 set_align()
3508 set_rotation()
3509 set_text_wrap()
3510 set_text_justlast()
3511 set_center_across()
3512 set_indent()
3513 set_shrink()
3514 set_pattern()
3515 set_bg_color()
3516 set_fg_color()
3517 set_border()
3518 set_bottom()
3519 set_top()
3520 set_left()
3521 set_right()
3522 set_border_color()
3523 set_bottom_color()
3524 set_top_color()
3525 set_left_color()
3526 set_right_color()
3527 set_diag_type()
3528 set_diag_border()
3529 set_diag_color()
3530 The above methods can also be applied directly as properties. For
3532 example "$format->set_bold()" is equivalent to
3533 "$workbook->add_format(bold => 1)".
3534 set_format_properties( %properties )
3536 The properties of an existing Format object can be also be set by means
3537 of "set_format_properties()":
3538 my $format = $workbook->add_format();
3540 $format->set_format_properties( bold => 1, color => 'red' );
3541 However, this method is here mainly for legacy reasons. It is
3543 preferable to set the properties in the format constructor:
3544 my $format = $workbook->add_format( bold => 1, color => 'red' );
3546 set_font( $fontname )
3548 Default state: Font is Calibri
3549 Default action: None
3550 Valid args: Any valid font name
3551 Specify the font used:
3553 $format->set_font('Times New Roman');
3555 Excel can only display fonts that are installed on the system that it
3557 is running on. Therefore it is best to use the fonts that come as
3558 standard such as 'Calibri', 'Times New Roman' and 'Courier New'. See
3559 also the Fonts worksheet created by formats.pl
3560 set_size()
3562 Default state: Font size is 10
3563 Default action: Set font size to 1
3564 Valid args: Integer values from 1 to as big as your screen.
3565 Set the font size. Excel adjusts the height of a row to accommodate the
3567 largest font size in the row. You can also explicitly specify the
3568 height of a row using the set_row() worksheet method.
3569 my $format = $workbook->add_format();
3571 $format->set_size( 30 );
3572 set_color()
3574 Default state: Excels default color, usually black
3575 Default action: Set the default color
3576 Valid args: Integers from 8..63 or the following strings:
3577 'black'
3578 'blue'
3579 'brown'
3580 'cyan'
3581 'gray'
3582 'green'
3583 'lime'
3584 'magenta'
3585 'navy'
3586 'orange'
3587 'pink'
3588 'purple'
3589 'red'
3590 'silver'
3591 'white'
3592 'yellow'
3593 Set the font colour. The "set_color()" method is used as follows:
3595 my $format = $workbook->add_format();
3597 $format->set_color( 'red' );
3598 $worksheet->write( 0, 0, 'wheelbarrow', $format );
3599 Note: The "set_color()" method is used to set the colour of the font in
3601 a cell. To set the colour of a cell use the "set_bg_color()" and
3602 "set_pattern()" methods.
3603 For additional examples see the 'Named colors' and 'Standard colors'
3605 worksheets created by formats.pl in the examples directory.
3606 See also "WORKING WITH COLOURS".
3608 set_bold()
3610 Default state: bold is off
3611 Default action: Turn bold on
3612 Valid args: 0, 1
3613 Set the bold property of the font:
3615 $format->set_bold(); # Turn bold on
3617 set_italic()
3619 Default state: Italic is off
3620 Default action: Turn italic on
3621 Valid args: 0, 1
3622 Set the italic property of the font:
3624 $format->set_italic(); # Turn italic on
3626 set_underline()
3628 Default state: Underline is off
3629 Default action: Turn on single underline
3630 Valid args: 0 = No underline
3631 1 = Single underline
3632 2 = Double underline
3633 33 = Single accounting underline
3634 34 = Double accounting underline
3635 Set the underline property of the font.
3637 $format->set_underline(); # Single underline
3639 set_font_strikeout()
3641 Default state: Strikeout is off
3642 Default action: Turn strikeout on
3643 Valid args: 0, 1
3644 Set the strikeout property of the font.
3646 set_font_script()
3648 Default state: Super/Subscript is off
3649 Default action: Turn Superscript on
3650 Valid args: 0 = Normal
3651 1 = Superscript
3652 2 = Subscript
3653 Set the superscript/subscript property of the font.
3655 set_font_outline()
3657 Default state: Outline is off
3658 Default action: Turn outline on
3659 Valid args: 0, 1
3660 Macintosh only.
3662 set_font_shadow()
3664 Default state: Shadow is off
3665 Default action: Turn shadow on
3666 Valid args: 0, 1
3667 Macintosh only.
3669 set_num_format()
3671 Default state: General format
3672 Default action: Format index 1
3673 Valid args: See the following table
3674 This method is used to define the numerical format of a number in
3676 Excel. It controls whether a number is displayed as an integer, a
3677 floating point number, a date, a currency value or some other user
3678 defined format.
3679 The numerical format of a cell can be specified by using a format
3681 string or an index to one of Excel's built-in formats:
3682 my $format1 = $workbook->add_format();
3684 my $format2 = $workbook->add_format();
3685 $format1->set_num_format( 'd mmm yyyy' ); # Format string
3686 $format2->set_num_format( 0x0f ); # Format index
3687 $worksheet->write( 0, 0, 36892.521, $format1 ); # 1 Jan 2001
3689 $worksheet->write( 0, 0, 36892.521, $format2 ); # 1-Jan-01
3690 Using format strings you can define very sophisticated formatting of
3692 numbers.
3693 $format01->set_num_format( '0.000' );
3695 $worksheet->write( 0, 0, 3.1415926, $format01 ); # 3.142
3696 $format02->set_num_format( '#,##0' );
3698 $worksheet->write( 1, 0, 1234.56, $format02 ); # 1,235
3699 $format03->set_num_format( '#,##0.00' );
3701 $worksheet->write( 2, 0, 1234.56, $format03 ); # 1,234.56
3702 $format04->set_num_format( '$0.00' );
3704 $worksheet->write( 3, 0, 49.99, $format04 ); # $49.99
3705 # Note you can use other currency symbols such as the pound or yen as well.
3707 # Other currencies may require the use of Unicode.
3708 $format07->set_num_format( 'mm/dd/yy' );
3710 $worksheet->write( 6, 0, 36892.521, $format07 ); # 01/01/01
3711 $format08->set_num_format( 'mmm d yyyy' );
3713 $worksheet->write( 7, 0, 36892.521, $format08 ); # Jan 1 2001
3714 $format09->set_num_format( 'd mmmm yyyy' );
3716 $worksheet->write( 8, 0, 36892.521, $format09 ); # 1 January 2001
3717 $format10->set_num_format( 'dd/mm/yyyy hh:mm AM/PM' );
3719 $worksheet->write( 9, 0, 36892.521, $format10 ); # 01/01/2001 12:30 AM
3720 $format11->set_num_format( '0 "dollar and" .00 "cents"' );
3722 $worksheet->write( 10, 0, 1.87, $format11 ); # 1 dollar and .87 cents
3723 # Conditional numerical formatting.
3725 $format12->set_num_format( '[Green]General;[Red]-General;General' );
3726 $worksheet->write( 11, 0, 123, $format12 ); # > 0 Green
3727 $worksheet->write( 12, 0, -45, $format12 ); # < 0 Red
3728 $worksheet->write( 13, 0, 0, $format12 ); # = 0 Default colour
3729 # Zip code
3731 $format13->set_num_format( '00000' );
3732 $worksheet->write( 14, 0, '01209', $format13 );
3733 The number system used for dates is described in "DATES AND TIME IN
3735 EXCEL".
3736 The colour format should have one of the following values:
3738 [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
3740 Alternatively you can specify the colour based on a colour index as
3742 follows: "[Color n]", where n is a standard Excel colour index - 7. See
3743 the 'Standard colors' worksheet created by formats.pl.
3744 For more information refer to the documentation on formatting in the
3746 "docs" directory of the Excel::Writer::XLSX distro, the Excel on-line
3747 help or
3748 <http://office.microsoft.com/en-gb/assistance/HP051995001033.aspx>.
3749 You should ensure that the format string is valid in Excel prior to
3751 using it in WriteExcel.
3752 Excel's built-in formats are shown in the following table:
3754 Index Index Format String
3756 0 0x00 General
3757 1 0x01 0
3758 2 0x02 0.00
3759 3 0x03 #,##0
3760 4 0x04 #,##0.00
3761 5 0x05 ($#,##0_);($#,##0)
3762 6 0x06 ($#,##0_);[Red]($#,##0)
3763 7 0x07 ($#,##0.00_);($#,##0.00)
3764 8 0x08 ($#,##0.00_);[Red]($#,##0.00)
3765 9 0x09 0%
3766 10 0x0a 0.00%
3767 11 0x0b 0.00E+00
3768 12 0x0c # ?/?
3769 13 0x0d # ??/??
3770 14 0x0e m/d/yy
3771 15 0x0f d-mmm-yy
3772 16 0x10 d-mmm
3773 17 0x11 mmm-yy
3774 18 0x12 h:mm AM/PM
3775 19 0x13 h:mm:ss AM/PM
3776 20 0x14 h:mm
3777 21 0x15 h:mm:ss
3778 22 0x16 m/d/yy h:mm
3779 .. .... ...........
3780 37 0x25 (#,##0_);(#,##0)
3781 38 0x26 (#,##0_);[Red](#,##0)
3782 39 0x27 (#,##0.00_);(#,##0.00)
3783 40 0x28 (#,##0.00_);[Red](#,##0.00)
3784 41 0x29 _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
3785 42 0x2a _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
3786 43 0x2b _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
3787 44 0x2c _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
3788 45 0x2d mm:ss
3789 46 0x2e [h]:mm:ss
3790 47 0x2f mm:ss.0
3791 48 0x30 ##0.0E+0
3792 49 0x31 @
3793 For examples of these formatting codes see the 'Numerical formats'
3795 worksheet created by formats.pl. See also the number_formats1.html and
3796 the number_formats2.html documents in the "docs" directory of the
3797 distro.
3798 Note 1. Numeric formats 23 to 36 are not documented by Microsoft and
3800 may differ in international versions.
3801 Note 2. The dollar sign appears as the defined local currency symbol.
3803 set_locked()
3805 Default state: Cell locking is on
3806 Default action: Turn locking on
3807 Valid args: 0, 1
3808 This property can be used to prevent modification of a cells contents.
3810 Following Excel's convention, cell locking is turned on by default.
3811 However, it only has an effect if the worksheet has been protected, see
3812 the worksheet "protect()" method.
3813 my $locked = $workbook->add_format();
3815 $locked->set_locked( 1 ); # A non-op
3816 my $unlocked = $workbook->add_format();
3818 $locked->set_locked( 0 );
3819 # Enable worksheet protection
3821 $worksheet->protect();
3822 # This cell cannot be edited.
3824 $worksheet->write( 'A1', '=1+2', $locked );
3825 # This cell can be edited.
3827 $worksheet->write( 'A2', '=1+2', $unlocked );
3828 Note: This offers weak protection even with a password, see the note in
3830 relation to the "protect()" method.
3831 set_hidden()
3833 Default state: Formula hiding is off
3834 Default action: Turn hiding on
3835 Valid args: 0, 1
3836 This property is used to hide a formula while still displaying its
3838 result. This is generally used to hide complex calculations from end
3839 users who are only interested in the result. It only has an effect if
3840 the worksheet has been protected, see the worksheet "protect()" method.
3841 my $hidden = $workbook->add_format();
3843 $hidden->set_hidden();
3844 # Enable worksheet protection
3846 $worksheet->protect();
3847 # The formula in this cell isn't visible
3849 $worksheet->write( 'A1', '=1+2', $hidden );
3850 Note: This offers weak protection even with a password, see the note in
3852 relation to the "protect()" method.
3853 set_align()
3855 Default state: Alignment is off
3856 Default action: Left alignment
3857 Valid args: 'left' Horizontal
3858 'center'
3859 'right'
3860 'fill'
3861 'justify'
3862 'center_across'
3863 'top' Vertical
3865 'vcenter'
3866 'bottom'
3867 'vjustify'
3868 This method is used to set the horizontal and vertical text alignment
3870 within a cell. Vertical and horizontal alignments can be combined. The
3871 method is used as follows:
3872 my $format = $workbook->add_format();
3874 $format->set_align( 'center' );
3875 $format->set_align( 'vcenter' );
3876 $worksheet->set_row( 0, 30 );
3877 $worksheet->write( 0, 0, 'X', $format );
3878 Text can be aligned across two or more adjacent cells using the
3880 "center_across" property. However, for genuine merged cells it is
3881 better to use the "merge_range()" worksheet method.
3882 The "vjustify" (vertical justify) option can be used to provide
3884 automatic text wrapping in a cell. The height of the cell will be
3885 adjusted to accommodate the wrapped text. To specify where the text
3886 wraps use the "set_text_wrap()" method.
3887 For further examples see the 'Alignment' worksheet created by
3889 formats.pl.
3890 set_center_across()
3892 Default state: Center across selection is off
3893 Default action: Turn center across on
3894 Valid args: 1
3895 Text can be aligned across two or more adjacent cells using the
3897 "set_center_across()" method. This is an alias for the
3898 "set_align('center_across')" method call.
3899 Only one cell should contain the text, the other cells should be blank:
3901 my $format = $workbook->add_format();
3903 $format->set_center_across();
3904 $worksheet->write( 1, 1, 'Center across selection', $format );
3906 $worksheet->write_blank( 1, 2, $format );
3907 See also the "merge1.pl" to "merge6.pl" programs in the "examples"
3909 directory and the "merge_range()" method.
3910 set_text_wrap()
3912 Default state: Text wrap is off
3913 Default action: Turn text wrap on
3914 Valid args: 0, 1
3915 Here is an example using the text wrap property, the escape character
3917 "\n" is used to indicate the end of line:
3918 my $format = $workbook->add_format();
3920 $format->set_text_wrap();
3921 $worksheet->write( 0, 0, "It's\na bum\nwrap", $format );
3922 Excel will adjust the height of the row to accommodate the wrapped
3924 text. A similar effect can be obtained without newlines using the
3925 "set_align('vjustify')" method. See the "textwrap.pl" program in the
3926 "examples" directory.
3927 set_rotation()
3929 Default state: Text rotation is off
3930 Default action: None
3931 Valid args: Integers in the range -90 to 90 and 270
3932 Set the rotation of the text in a cell. The rotation can be any angle
3934 in the range -90 to 90 degrees.
3935 my $format = $workbook->add_format();
3937 $format->set_rotation( 30 );
3938 $worksheet->write( 0, 0, 'This text is rotated', $format );
3939 The angle 270 is also supported. This indicates text where the letters
3941 run from top to bottom.
3942 set_indent()
3944 Default state: Text indentation is off
3945 Default action: Indent text 1 level
3946 Valid args: Positive integers
3947 This method can be used to indent text. The argument, which should be
3949 an integer, is taken as the level of indentation:
3950 my $format = $workbook->add_format();
3952 $format->set_indent( 2 );
3953 $worksheet->write( 0, 0, 'This text is indented', $format );
3954 Indentation is a horizontal alignment property. It will override any
3956 other horizontal properties but it can be used in conjunction with
3957 vertical properties.
3958 set_shrink()
3960 Default state: Text shrinking is off
3961 Default action: Turn "shrink to fit" on
3962 Valid args: 1
3963 This method can be used to shrink text so that it fits in a cell.
3965 my $format = $workbook->add_format();
3967 $format->set_shrink();
3968 $worksheet->write( 0, 0, 'Honey, I shrunk the text!', $format );
3969 set_text_justlast()
3971 Default state: Justify last is off
3972 Default action: Turn justify last on
3973 Valid args: 0, 1
3974 Only applies to Far Eastern versions of Excel.
3976 set_pattern()
3978 Default state: Pattern is off
3979 Default action: Solid fill is on
3980 Valid args: 0 .. 18
3981 Set the background pattern of a cell.
3983 Examples of the available patterns are shown in the 'Patterns'
3985 worksheet created by formats.pl. However, it is unlikely that you will
3986 ever need anything other than Pattern 1 which is a solid fill of the
3987 background color.
3988 set_bg_color()
3990 Default state: Color is off
3991 Default action: Solid fill.
3992 Valid args: See set_color()
3993 The "set_bg_color()" method can be used to set the background colour of
3995 a pattern. Patterns are defined via the "set_pattern()" method. If a
3996 pattern hasn't been defined then a solid fill pattern is used as the
3997 default.
3998 Here is an example of how to set up a solid fill in a cell:
4000 my $format = $workbook->add_format();
4002 $format->set_pattern(); # This is optional when using a solid fill
4004 $format->set_bg_color( 'green' );
4006 $worksheet->write( 'A1', 'Ray', $format );
4007 For further examples see the 'Patterns' worksheet created by
4009 formats.pl.
4010 set_fg_color()
4012 Default state: Color is off
4013 Default action: Solid fill.
4014 Valid args: See set_color()
4015 The "set_fg_color()" method can be used to set the foreground colour of
4017 a pattern.
4018 For further examples see the 'Patterns' worksheet created by
4020 formats.pl.
4021 set_border()
4023 Also applies to: set_bottom()
4024 set_top()
4025 set_left()
4026 set_right()
4027 Default state: Border is off
4029 Default action: Set border type 1
4030 Valid args: 0-13, See below.
4031 A cell border is comprised of a border on the bottom, top, left and
4033 right. These can be set to the same value using "set_border()" or
4034 individually using the relevant method calls shown above.
4035 The following shows the border styles sorted by Excel::Writer::XLSX
4037 index number:
4038 Index Name Weight Style
4040 ===== ============= ====== ===========
4041 0 None 0
4042 1 Continuous 1 -----------
4043 2 Continuous 2 -----------
4044 3 Dash 1 - - - - - -
4045 4 Dot 1 . . . . . .
4046 5 Continuous 3 -----------
4047 6 Double 3 ===========
4048 7 Continuous 0 -----------
4049 8 Dash 2 - - - - - -
4050 9 Dash Dot 1 - . - . - .
4051 10 Dash Dot 2 - . - . - .
4052 11 Dash Dot Dot 1 - . . - . .
4053 12 Dash Dot Dot 2 - . . - . .
4054 13 SlantDash Dot 2 / - . / - .
4055 The following shows the borders sorted by style:
4057 Name Weight Style Index
4059 ============= ====== =========== =====
4060 Continuous 0 ----------- 7
4061 Continuous 1 ----------- 1
4062 Continuous 2 ----------- 2
4063 Continuous 3 ----------- 5
4064 Dash 1 - - - - - - 3
4065 Dash 2 - - - - - - 8
4066 Dash Dot 1 - . - . - . 9
4067 Dash Dot 2 - . - . - . 10
4068 Dash Dot Dot 1 - . . - . . 11
4069 Dash Dot Dot 2 - . . - . . 12
4070 Dot 1 . . . . . . 4
4071 Double 3 =========== 6
4072 None 0 0
4073 SlantDash Dot 2 / - . / - . 13
4074 The following shows the borders in the order shown in the Excel Dialog.
4076 Index Style Index Style
4078 ===== ===== ===== =====
4079 0 None 12 - . . - . .
4080 7 ----------- 13 / - . / - .
4081 4 . . . . . . 10 - . - . - .
4082 11 - . . - . . 8 - - - - - -
4083 9 - . - . - . 2 -----------
4084 3 - - - - - - 5 -----------
4085 1 ----------- 6 ===========
4086 Examples of the available border styles are shown in the 'Borders'
4088 worksheet created by formats.pl.
4089 set_border_color()
4091 Also applies to: set_bottom_color()
4092 set_top_color()
4093 set_left_color()
4094 set_right_color()
4095 Default state: Color is off
4097 Default action: Undefined
4098 Valid args: See set_color()
4099 Set the colour of the cell borders. A cell border is comprised of a
4101 border on the bottom, top, left and right. These can be set to the same
4102 colour using "set_border_color()" or individually using the relevant
4103 method calls shown above. Examples of the border styles and colours are
4104 shown in the 'Borders' worksheet created by formats.pl.
4105 set_diag_type()
4107 Default state: Diagonal border is off.
4108 Default action: None.
4109 Valid args: 1-3, See below.
4110 Set the diagonal border type for the cell. Three types of diagonal
4112 borders are available in Excel:
4113 1: From bottom left to top right.
4115 2: From top left to bottom right.
4116 3: Same as 1 and 2 combined.
4117 For example:
4119 $format->set_diag_type( 3 );
4121 set_diag_border()
4123 Default state: Border is off
4124 Default action: Set border type 1
4125 Valid args: 0-13, See below.
4126 Set the diagonal border style. Same as the parameter to "set_border()"
4128 above.
4129 set_diag_color()
4131 Default state: Color is off
4132 Default action: Undefined
4133 Valid args: See set_color()
4134 Set the colour of the diagonal cell border:
4136 $format->set_diag_type( 3 );
4138 $format->set_diag_border( 7 );
4139 $format->set_diag_color( 'red' );
4140 copy( $format )
4142 This method is used to copy all of the properties from one Format
4143 object to another:
4144 my $lorry1 = $workbook->add_format();
4146 $lorry1->set_bold();
4147 $lorry1->set_italic();
4148 $lorry1->set_color( 'red' ); # lorry1 is bold, italic and red
4149 my $lorry2 = $workbook->add_format();
4151 $lorry2->copy( $lorry1 );
4152 $lorry2->set_color( 'yellow' ); # lorry2 is bold, italic and yellow
4153 The "copy()" method is only useful if you are using the method
4155 interface to Format properties. It generally isn't required if you are
4156 setting Format properties directly using hashes.
4157 Note: this is not a copy constructor, both objects must exist prior to
4159 copying.
4160
4162 The following is a brief introduction to handling Unicode in
4163 "Excel::Writer::XLSX".
4164 For a more general introduction to Unicode handling in Perl see
4166 perlunitut and perluniintro.
4167 Excel::Writer::XLSX writer differs from Spreadsheet::WriteExcel in that
4169 it only handles Unicode data in "UTF-8" format and doesn't try to
4170 handle legacy UTF-16 Excel formats.
4171 If the data is in "UTF-8" format then Excel::Writer::XLSX will handle
4173 it automatically.
4174 If you are dealing with non-ASCII characters that aren't in "UTF-8"
4176 then perl provides useful tools in the guise of the "Encode" module to
4177 help you to convert to the required format. For example:
4178 use Encode 'decode';
4180 my $string = 'some string with koi8-r characters';
4182 $string = decode('koi8-r', $string); # koi8-r to utf8
4183 Alternatively you can read data from an encoded file and convert it to
4185 "UTF-8" as you read it in:
4186 my $file = 'unicode_koi8r.txt';
4188 open FH, '<:encoding(koi8-r)', $file or die "Couldn't open $file: $!\n";
4189 my $row = 0;
4191 while ( <FH> ) {
4192 # Data read in is now in utf8 format.
4193 chomp;
4194 $worksheet->write( $row++, 0, $_ );
4195 }
4196 These methodologies are explained in more detail in perlunitut,
4198 perluniintro and perlunicode.
4199 If the program contains UTF-8 text then you will also need to add "use
4201 utf8" to the includes:
4202 use utf8;
4204 ...
4206 $worksheet->write( 'A1', 'Some UTF-8 string' );
4208 See also the "unicode_*.pl" programs in the examples directory of the
4210 distro.
4211
4213 Throughout Excel::Writer::XLSX colours can be specified using a Html
4214 style "#RRGGBB" value. For example with a Format object:
4215 $format->set_font_color( '#FF0000' );
4217 For backward compatibility a limited number of color names are
4219 supported:
4220 $format->set_font_color( 'red' );
4222 The color names supported are:
4224 black
4226 blue
4227 brown
4228 cyan
4229 gray
4230 green
4231 lime
4232 magenta
4233 navy
4234 orange
4235 pink
4236 purple
4237 red
4238 silver
4239 white
4240 yellow
4241 See also "colors.pl" in the "examples" directory.
4243
4245 There are two important things to understand about dates and times in
4246 Excel:
4247 1 A date/time in Excel is a real number plus an Excel number format.
4249 2 Excel::Writer::XLSX doesn't automatically convert date/time strings
4250 in "write()" to an Excel date/time.
4251 These two points are explained in more detail below along with some
4253 suggestions on how to convert times and dates to the required format.
4254 An Excel date/time is a number plus a format
4256 If you write a date string with "write()" then all you will get is a
4257 string:
4258 $worksheet->write( 'A1', '02/03/04' ); # !! Writes a string not a date. !!
4260 Dates and times in Excel are represented by real numbers, for example
4262 "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
4263 The integer part of the number stores the number of days since the
4265 epoch and the fractional part stores the percentage of the day.
4266 A date or time in Excel is just like any other number. To have the
4268 number display as a date you must apply an Excel number format to it.
4269 Here are some examples.
4270 #!/usr/bin/perl -w
4272 use strict;
4274 use Excel::Writer::XLSX;
4275 my $workbook = Excel::Writer::XLSX->new( 'date_examples.xlsx' );
4277 my $worksheet = $workbook->add_worksheet();
4278 $worksheet->set_column( 'A:A', 30 ); # For extra visibility.
4280 my $number = 39506.5;
4282 $worksheet->write( 'A1', $number ); # 39506.5
4284 my $format2 = $workbook->add_format( num_format => 'dd/mm/yy' );
4286 $worksheet->write( 'A2', $number, $format2 ); # 28/02/08
4287 my $format3 = $workbook->add_format( num_format => 'mm/dd/yy' );
4289 $worksheet->write( 'A3', $number, $format3 ); # 02/28/08
4290 my $format4 = $workbook->add_format( num_format => 'd-m-yyyy' );
4292 $worksheet->write( 'A4', $number, $format4 ); # 28-2-2008
4293 my $format5 = $workbook->add_format( num_format => 'dd/mm/yy hh:mm' );
4295 $worksheet->write( 'A5', $number, $format5 ); # 28/02/08 12:00
4296 my $format6 = $workbook->add_format( num_format => 'd mmm yyyy' );
4298 $worksheet->write( 'A6', $number, $format6 ); # 28 Feb 2008
4299 my $format7 = $workbook->add_format( num_format => 'mmm d yyyy hh:mm AM/PM' );
4301 $worksheet->write('A7', $number , $format7); # Feb 28 2008 12:00 PM
4302 $workbook->close();
4304 Excel::Writer::XLSX doesn't automatically convert date/time strings
4306 Excel::Writer::XLSX doesn't automatically convert input date strings
4307 into Excel's formatted date numbers due to the large number of possible
4308 date formats and also due to the possibility of misinterpretation.
4309 For example, does "02/03/04" mean March 2 2004, February 3 2004 or even
4311 March 4 2002.
4312 Therefore, in order to handle dates you will have to convert them to
4314 numbers and apply an Excel format. Some methods for converting dates
4315 are listed in the next section.
4316 The most direct way is to convert your dates to the ISO8601
4318 "yyyy-mm-ddThh:mm:ss.sss" date format and use the "write_date_time()"
4319 worksheet method:
4320 $worksheet->write_date_time( 'A2', '2001-01-01T12:20', $format );
4322 See the "write_date_time()" section of the documentation for more
4324 details.
4325 A general methodology for handling date strings with
4327 "write_date_time()" is:
4328 1. Identify incoming date/time strings with a regex.
4330 2. Extract the component parts of the date/time using the same regex.
4331 3. Convert the date/time to the ISO8601 format.
4332 4. Write the date/time using write_date_time() and a number format.
4333 Here is an example:
4335 #!/usr/bin/perl -w
4337 use strict;
4339 use Excel::Writer::XLSX;
4340 my $workbook = Excel::Writer::XLSX->new( 'example.xlsx' );
4342 my $worksheet = $workbook->add_worksheet();
4343 # Set the default format for dates.
4345 my $date_format = $workbook->add_format( num_format => 'mmm d yyyy' );
4346 # Increase column width to improve visibility of data.
4348 $worksheet->set_column( 'A:C', 20 );
4349 # Simulate reading from a data source.
4351 my $row = 0;
4352 while ( <DATA> ) {
4354 chomp;
4355 my $col = 0;
4357 my @data = split ' ';
4358 for my $item ( @data ) {
4360 # Match dates in the following formats: d/m/yy, d/m/yyyy
4362 if ( $item =~ qr[^(\d{1,2})/(\d{1,2})/(\d{4})$] ) {
4363 # Change to the date format required by write_date_time().
4365 my $date = sprintf "%4d-%02d-%02dT", $3, $2, $1;
4366 $worksheet->write_date_time( $row, $col++, $date,
4368 $date_format );
4369 }
4370 else {
4371 # Just plain data
4373 $worksheet->write( $row, $col++, $item );
4374 }
4375 }
4376 $row++;
4377 }
4378 $workbook->close();
4380 __DATA__
4382 Item Cost Date
4383 Book 10 1/9/2007
4384 Beer 4 12/9/2007
4385 Bed 500 5/10/2007
4386 For a slightly more advanced solution you can modify the "write()"
4388 method to handle date formats of your choice via the
4389 "add_write_handler()" method. See the "add_write_handler()" section of
4390 the docs and the write_handler3.pl and write_handler4.pl programs in
4391 the examples directory of the distro.
4392 Converting dates and times to an Excel date or time
4394 The "write_date_time()" method above is just one way of handling dates
4395 and times.
4396 You can also use the "convert_date_time()" worksheet method to convert
4398 from an ISO8601 style date string to an Excel date and time number.
4399 The Excel::Writer::XLSX::Utility module which is included in the distro
4401 has date/time handling functions:
4402 use Excel::Writer::XLSX::Utility;
4404 $date = xl_date_list(2002, 1, 1); # 37257
4406 $date = xl_parse_date("11 July 1997"); # 35622
4407 $time = xl_parse_time('3:21:36 PM'); # 0.64
4408 $date = xl_decode_date_EU("13 May 2002"); # 37389
4409 Note: some of these functions require additional CPAN modules.
4411 For date conversions using the CPAN "DateTime" framework see
4413 DateTime::Format::Excel
4414 <http://search.cpan.org/search?dist=DateTime-Format-Excel>.
4415
4417 Excel allows you to group rows or columns so that they can be hidden or
4418 displayed with a single mouse click. This feature is referred to as
4419 outlines.
4420 Outlines can reduce complex data down to a few salient sub-totals or
4422 summaries.
4423 This feature is best viewed in Excel but the following is an ASCII
4425 representation of what a worksheet with three outlines might look like.
4426 Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at
4427 level 1. The lines at the left hand side are called outline level bars.
4428 ------------------------------------------
4430 1 2 3 | | A | B | C | D | ...
4431 ------------------------------------------
4432 _ | 1 | A | | | | ...
4433 | _ | 2 | B | | | | ...
4434 | | | 3 | (C) | | | | ...
4435 | | | 4 | (D) | | | | ...
4436 | - | 5 | E | | | | ...
4437 | _ | 6 | F | | | | ...
4438 | | | 7 | (G) | | | | ...
4439 | | | 8 | (H) | | | | ...
4440 | - | 9 | I | | | | ...
4441 - | . | ... | ... | ... | ... | ...
4442 Clicking the minus sign on each of the level 2 outlines will collapse
4444 and hide the data as shown in the next figure. The minus sign changes
4445 to a plus sign to indicate that the data in the outline is hidden.
4446 ------------------------------------------
4448 1 2 3 | | A | B | C | D | ...
4449 ------------------------------------------
4450 _ | 1 | A | | | | ...
4451 | | 2 | B | | | | ...
4452 | + | 5 | E | | | | ...
4453 | | 6 | F | | | | ...
4454 | + | 9 | I | | | | ...
4455 - | . | ... | ... | ... | ... | ...
4456 Clicking on the minus sign on the level 1 outline will collapse the
4458 remaining rows as follows:
4459 ------------------------------------------
4461 1 2 3 | | A | B | C | D | ...
4462 ------------------------------------------
4463 | 1 | A | | | | ...
4464 + | . | ... | ... | ... | ... | ...
4465 Grouping in "Excel::Writer::XLSX" is achieved by setting the outline
4467 level via the "set_row()" and "set_column()" worksheet methods:
4468 set_row( $row, $height, $format, $hidden, $level, $collapsed )
4470 set_column( $first_col, $last_col, $width, $format, $hidden, $level, $collapsed )
4471 The following example sets an outline level of 1 for rows 2 and 3
4473 (zero-indexed) and columns B to G. The parameters $height and $XF are
4474 assigned default values since they are undefined:
4475 $worksheet->set_row( 1, undef, undef, 0, 1 );
4477 $worksheet->set_row( 2, undef, undef, 0, 1 );
4478 $worksheet->set_column( 'B:G', undef, undef, 0, 1 );
4479 Excel allows up to 7 outline levels. Therefore the $level parameter
4481 should be in the range "0 <= $level <= 7".
4482 Rows and columns can be collapsed by setting the $hidden flag for the
4484 hidden rows/columns and setting the $collapsed flag for the row/column
4485 that has the collapsed "+" symbol:
4486 $worksheet->set_row( 1, undef, undef, 1, 1 );
4488 $worksheet->set_row( 2, undef, undef, 1, 1 );
4489 $worksheet->set_row( 3, undef, undef, 0, 0, 1 ); # Collapsed flag.
4490 $worksheet->set_column( 'B:G', undef, undef, 1, 1 );
4492 $worksheet->set_column( 'H:H', undef, undef, 0, 0, 1 ); # Collapsed flag.
4493 Note: Setting the $collapsed flag is particularly important for
4495 compatibility with OpenOffice.org and Gnumeric.
4496 For a more complete example see the "outline.pl" and
4498 "outline_collapsed.pl" programs in the examples directory of the
4499 distro.
4500 Some additional outline properties can be set via the
4502 "outline_settings()" worksheet method, see above.
4503
4505 Data validation is a feature of Excel which allows you to restrict the
4506 data that a users enters in a cell and to display help and warning
4507 messages. It also allows you to restrict input to values in a drop down
4508 list.
4509 A typical use case might be to restrict data in a cell to integer
4511 values in a certain range, to provide a help message to indicate the
4512 required value and to issue a warning if the input data doesn't meet
4513 the stated criteria. In Excel::Writer::XLSX we could do that as
4514 follows:
4515 $worksheet->data_validation('B3',
4517 {
4518 validate => 'integer',
4519 criteria => 'between',
4520 minimum => 1,
4521 maximum => 100,
4522 input_title => 'Input an integer:',
4523 input_message => 'Between 1 and 100',
4524 error_message => 'Sorry, try again.',
4525 });
4526 For more information on data validation see the following Microsoft
4528 support article "Description and examples of data validation in Excel":
4529 <http://support.microsoft.com/kb/211485>.
4530 The following sections describe how to use the "data_validation()"
4532 method and its various options.
4533 data_validation( $row, $col, { parameter => 'value', ... } )
4535 The "data_validation()" method is used to construct an Excel data
4536 validation.
4537 It can be applied to a single cell or a range of cells. You can pass 3
4539 parameters such as "($row, $col, {...})" or 5 parameters such as
4540 "($first_row, $first_col, $last_row, $last_col, {...})". You can also
4541 use "A1" style notation. For example:
4542 $worksheet->data_validation( 0, 0, {...} );
4544 $worksheet->data_validation( 0, 0, 4, 1, {...} );
4545 # Which are the same as:
4547 $worksheet->data_validation( 'A1', {...} );
4549 $worksheet->data_validation( 'A1:B5', {...} );
4550 See also the note about "Cell notation" for more information.
4552 The last parameter in "data_validation()" must be a hash ref containing
4554 the parameters that describe the type and style of the data validation.
4555 The allowable parameters are:
4556 validate
4558 criteria
4559 value | minimum | source
4560 maximum
4561 ignore_blank
4562 dropdown
4563 input_title
4565 input_message
4566 show_input
4567 error_title
4569 error_message
4570 error_type
4571 show_error
4572 These parameters are explained in the following sections. Most of the
4574 parameters are optional, however, you will generally require the three
4575 main options "validate", "criteria" and "value".
4576 $worksheet->data_validation('B3',
4578 {
4579 validate => 'integer',
4580 criteria => '>',
4581 value => 100,
4582 });
4583 The "data_validation" method returns:
4585 0 for success.
4587 -1 for insufficient number of arguments.
4588 -2 for row or column out of bounds.
4589 -3 for incorrect parameter or value.
4590 validate
4592 This parameter is passed in a hash ref to "data_validation()".
4593 The "validate" parameter is used to set the type of data that you wish
4595 to validate. It is always required and it has no default value.
4596 Allowable values are:
4597 any
4599 integer
4600 decimal
4601 list
4602 date
4603 time
4604 length
4605 custom
4606 · any is used to specify that the type of data is unrestricted. This
4608 is useful to display an input message without restricting the data
4609 that can be entered.
4610 · integer restricts the cell to integer values. Excel refers to this
4612 as 'whole number'.
4613 validate => 'integer',
4615 criteria => '>',
4616 value => 100,
4617 · decimal restricts the cell to decimal values.
4619 validate => 'decimal',
4621 criteria => '>',
4622 value => 38.6,
4623 · list restricts the cell to a set of user specified values. These
4625 can be passed in an array ref or as a cell range (named ranges
4626 aren't currently supported):
4627 validate => 'list',
4629 value => ['open', 'high', 'close'],
4630 # Or like this:
4631 value => 'B1:B3',
4632 Excel requires that range references are only to cells on the same
4634 worksheet.
4635 · date restricts the cell to date values. Dates in Excel are
4637 expressed as integer values but you can also pass an ISO8601 style
4638 string as used in "write_date_time()". See also "DATES AND TIME IN
4639 EXCEL" for more information about working with Excel's dates.
4640 validate => 'date',
4642 criteria => '>',
4643 value => 39653, # 24 July 2008
4644 # Or like this:
4645 value => '2008-07-24T',
4646 · time restricts the cell to time values. Times in Excel are
4648 expressed as decimal values but you can also pass an ISO8601 style
4649 string as used in "write_date_time()". See also "DATES AND TIME IN
4650 EXCEL" for more information about working with Excel's times.
4651 validate => 'time',
4653 criteria => '>',
4654 value => 0.5, # Noon
4655 # Or like this:
4656 value => 'T12:00:00',
4657 · length restricts the cell data based on an integer string length.
4659 Excel refers to this as 'Text length'.
4660 validate => 'length',
4662 criteria => '>',
4663 value => 10,
4664 · custom restricts the cell based on an external Excel formula that
4666 returns a "TRUE/FALSE" value.
4667 validate => 'custom',
4669 value => '=IF(A10>B10,TRUE,FALSE)',
4670 criteria
4672 This parameter is passed in a hash ref to "data_validation()".
4673 The "criteria" parameter is used to set the criteria by which the data
4675 in the cell is validated. It is almost always required except for the
4676 "list" and "custom" validate options. It has no default value.
4677 Allowable values are:
4678 'between'
4680 'not between'
4681 'equal to' | '==' | '='
4682 'not equal to' | '!=' | '<>'
4683 'greater than' | '>'
4684 'less than' | '<'
4685 'greater than or equal to' | '>='
4686 'less than or equal to' | '<='
4687 You can either use Excel's textual description strings, in the first
4689 column above, or the more common symbolic alternatives. The following
4690 are equivalent:
4691 validate => 'integer',
4693 criteria => 'greater than',
4694 value => 100,
4695 validate => 'integer',
4697 criteria => '>',
4698 value => 100,
4699 The "list" and "custom" validate options don't require a "criteria". If
4701 you specify one it will be ignored.
4702 validate => 'list',
4704 value => ['open', 'high', 'close'],
4705 validate => 'custom',
4707 value => '=IF(A10>B10,TRUE,FALSE)',
4708 value | minimum | source
4710 This parameter is passed in a hash ref to "data_validation()".
4711 The "value" parameter is used to set the limiting value to which the
4713 "criteria" is applied. It is always required and it has no default
4714 value. You can also use the synonyms "minimum" or "source" to make the
4715 validation a little clearer and closer to Excel's description of the
4716 parameter:
4717 # Use 'value'
4719 validate => 'integer',
4720 criteria => '>',
4721 value => 100,
4722 # Use 'minimum'
4724 validate => 'integer',
4725 criteria => 'between',
4726 minimum => 1,
4727 maximum => 100,
4728 # Use 'source'
4730 validate => 'list',
4731 source => '$B$1:$B$3',
4732 maximum
4734 This parameter is passed in a hash ref to "data_validation()".
4735 The "maximum" parameter is used to set the upper limiting value when
4737 the "criteria" is either 'between' or 'not between':
4738 validate => 'integer',
4740 criteria => 'between',
4741 minimum => 1,
4742 maximum => 100,
4743 ignore_blank
4745 This parameter is passed in a hash ref to "data_validation()".
4746 The "ignore_blank" parameter is used to toggle on and off the 'Ignore
4748 blank' option in the Excel data validation dialog. When the option is
4749 on the data validation is not applied to blank data in the cell. It is
4750 on by default.
4751 ignore_blank => 0, # Turn the option off
4753 dropdown
4755 This parameter is passed in a hash ref to "data_validation()".
4756 The "dropdown" parameter is used to toggle on and off the 'In-cell
4758 dropdown' option in the Excel data validation dialog. When the option
4759 is on a dropdown list will be shown for "list" validations. It is on by
4760 default.
4761 dropdown => 0, # Turn the option off
4763 input_title
4765 This parameter is passed in a hash ref to "data_validation()".
4766 The "input_title" parameter is used to set the title of the input
4768 message that is displayed when a cell is entered. It has no default
4769 value and is only displayed if the input message is displayed. See the
4770 "input_message" parameter below.
4771 input_title => 'This is the input title',
4773 The maximum title length is 32 characters.
4775 input_message
4777 This parameter is passed in a hash ref to "data_validation()".
4778 The "input_message" parameter is used to set the input message that is
4780 displayed when a cell is entered. It has no default value.
4781 validate => 'integer',
4783 criteria => 'between',
4784 minimum => 1,
4785 maximum => 100,
4786 input_title => 'Enter the applied discount:',
4787 input_message => 'between 1 and 100',
4788 The message can be split over several lines using newlines, "\n" in
4790 double quoted strings.
4791 input_message => "This is\na test.",
4793 The maximum message length is 255 characters.
4795 show_input
4797 This parameter is passed in a hash ref to "data_validation()".
4798 The "show_input" parameter is used to toggle on and off the 'Show input
4800 message when cell is selected' option in the Excel data validation
4801 dialog. When the option is off an input message is not displayed even
4802 if it has been set using "input_message". It is on by default.
4803 show_input => 0, # Turn the option off
4805 error_title
4807 This parameter is passed in a hash ref to "data_validation()".
4808 The "error_title" parameter is used to set the title of the error
4810 message that is displayed when the data validation criteria is not met.
4811 The default error title is 'Microsoft Excel'.
4812 error_title => 'Input value is not valid',
4814 The maximum title length is 32 characters.
4816 error_message
4818 This parameter is passed in a hash ref to "data_validation()".
4819 The "error_message" parameter is used to set the error message that is
4821 displayed when a cell is entered. The default error message is "The
4822 value you entered is not valid.\nA user has restricted values that can
4823 be entered into the cell.".
4824 validate => 'integer',
4826 criteria => 'between',
4827 minimum => 1,
4828 maximum => 100,
4829 error_title => 'Input value is not valid',
4830 error_message => 'It should be an integer between 1 and 100',
4831 The message can be split over several lines using newlines, "\n" in
4833 double quoted strings.
4834 input_message => "This is\na test.",
4836 The maximum message length is 255 characters.
4838 error_type
4840 This parameter is passed in a hash ref to "data_validation()".
4841 The "error_type" parameter is used to specify the type of error dialog
4843 that is displayed. There are 3 options:
4844 'stop'
4846 'warning'
4847 'information'
4848 The default is 'stop'.
4850 show_error
4852 This parameter is passed in a hash ref to "data_validation()".
4853 The "show_error" parameter is used to toggle on and off the 'Show error
4855 alert after invalid data is entered' option in the Excel data
4856 validation dialog. When the option is off an error message is not
4857 displayed even if it has been set using "error_message". It is on by
4858 default.
4859 show_error => 0, # Turn the option off
4861 Data Validation Examples
4863 Example 1. Limiting input to an integer greater than a fixed value.
4864 $worksheet->data_validation('A1',
4866 {
4867 validate => 'integer',
4868 criteria => '>',
4869 value => 0,
4870 });
4871 Example 2. Limiting input to an integer greater than a fixed value
4873 where the value is referenced from a cell.
4874 $worksheet->data_validation('A2',
4876 {
4877 validate => 'integer',
4878 criteria => '>',
4879 value => '=E3',
4880 });
4881 Example 3. Limiting input to a decimal in a fixed range.
4883 $worksheet->data_validation('A3',
4885 {
4886 validate => 'decimal',
4887 criteria => 'between',
4888 minimum => 0.1,
4889 maximum => 0.5,
4890 });
4891 Example 4. Limiting input to a value in a dropdown list.
4893 $worksheet->data_validation('A4',
4895 {
4896 validate => 'list',
4897 source => ['open', 'high', 'close'],
4898 });
4899 Example 5. Limiting input to a value in a dropdown list where the list
4901 is specified as a cell range.
4902 $worksheet->data_validation('A5',
4904 {
4905 validate => 'list',
4906 source => '=$E$4:$G$4',
4907 });
4908 Example 6. Limiting input to a date in a fixed range.
4910 $worksheet->data_validation('A6',
4912 {
4913 validate => 'date',
4914 criteria => 'between',
4915 minimum => '2008-01-01T',
4916 maximum => '2008-12-12T',
4917 });
4918 Example 7. Displaying a message when the cell is selected.
4920 $worksheet->data_validation('A7',
4922 {
4923 validate => 'integer',
4924 criteria => 'between',
4925 minimum => 1,
4926 maximum => 100,
4927 input_title => 'Enter an integer:',
4928 input_message => 'between 1 and 100',
4929 });
4930 See also the "data_validate.pl" program in the examples directory of
4932 the distro.
4933
4935 Conditional formatting is a feature of Excel which allows you to apply
4936 a format to a cell or a range of cells based on a certain criteria.
4937 For example the following criteria is used to highlight cells >= 50 in
4939 red in the "conditional_format.pl" example from the distro:
4940 # Write a conditional format over a range.
4942 $worksheet->conditional_formatting( 'B3:K12',
4943 {
4944 type => 'cell',
4945 criteria => '>=',
4946 value => 50,
4947 format => $format1,
4948 }
4949 );
4950 conditional_formatting( $row, $col, { parameter => 'value', ... } )
4952 The "conditional_formatting()" method is used to apply formatting
4953 based on user defined criteria to an Excel::Writer::XLSX file.
4954 It can be applied to a single cell or a range of cells. You can pass 3
4956 parameters such as "($row, $col, {...})" or 5 parameters such as
4957 "($first_row, $first_col, $last_row, $last_col, {...})". You can also
4958 use "A1" style notation. For example:
4959 $worksheet->conditional_formatting( 0, 0, {...} );
4961 $worksheet->conditional_formatting( 0, 0, 4, 1, {...} );
4962 # Which are the same as:
4964 $worksheet->conditional_formatting( 'A1', {...} );
4966 $worksheet->conditional_formatting( 'A1:B5', {...} );
4967 See also the note about "Cell notation" for more information.
4969 Using "A1" style notation is also possible to specify non-contiguous
4971 ranges, separated by a comma. For example:
4972 $worksheet->conditional_formatting( 'A1:D5,A8:D12', {...} );
4974 The last parameter in "conditional_formatting()" must be a hash ref
4976 containing the parameters that describe the type and style of the data
4977 validation. The main parameters are:
4978 type
4980 format
4981 criteria
4982 value
4983 minimum
4984 maximum
4985 Other, less commonly used parameters are:
4987 min_type
4989 mid_type
4990 max_type
4991 min_value
4992 mid_value
4993 max_value
4994 min_color
4995 mid_color
4996 max_color
4997 bar_color
4998 bar_only
4999 bar_solid
5000 bar_negative_color
5001 bar_border_color
5002 bar_negative_border_color
5003 bar_negative_color_same
5004 bar_negative_border_color_same
5005 bar_no_border
5006 bar_direction
5007 bar_axis_position
5008 bar_axis_color
5009 data_bar_2010
5010 icon_style
5011 icons
5012 reverse_icons
5013 icons_only
5014 stop_if_true
5015 multi_range
5016 Additional parameters which are used for specific conditional format
5018 types are shown in the relevant sections below.
5019 type
5021 This parameter is passed in a hash ref to "conditional_formatting()".
5022 The "type" parameter is used to set the type of conditional formatting
5024 that you wish to apply. It is always required and it has no default
5025 value. Allowable "type" values and their associated parameters are:
5026 Type Parameters
5028 ==== ==========
5029 cell criteria
5030 value
5031 minimum
5032 maximum
5033 format
5034 date criteria
5036 value
5037 minimum
5038 maximum
5039 format
5040 time_period criteria
5042 format
5043 text criteria
5045 value
5046 format
5047 average criteria
5049 format
5050 duplicate format
5052 unique format
5054 top criteria
5056 value
5057 format
5058 bottom criteria
5060 value
5061 format
5062 blanks format
5064 no_blanks format
5066 errors format
5068 no_errors format
5070 formula criteria
5072 format
5073 2_color_scale min_type
5075 max_type
5076 min_value
5077 max_value
5078 min_color
5079 max_color
5080 3_color_scale min_type
5082 mid_type
5083 max_type
5084 min_value
5085 mid_value
5086 max_value
5087 min_color
5088 mid_color
5089 max_color
5090 data_bar min_type
5092 max_type
5093 min_value
5094 max_value
5095 bar_only
5096 bar_color
5097 bar_solid*
5098 bar_negative_color*
5099 bar_border_color*
5100 bar_negative_border_color*
5101 bar_negative_color_same*
5102 bar_negative_border_color_same*
5103 bar_no_border*
5104 bar_direction*
5105 bar_axis_position*
5106 bar_axis_color*
5107 data_bar_2010*
5108 icon_set icon_style
5110 reverse_icons
5111 icons
5112 icons_only
5113 Data bar parameters marked with (*) are only available in Excel 2010
5115 and later. Files that use these properties can still be opened in Excel
5116 2007 but the data bars will be displayed without them.
5117 type => 'cell'
5119 This is the most common conditional formatting type. It is used when a
5120 format is applied to a cell based on a simple criterion. For example:
5121 $worksheet->conditional_formatting( 'A1',
5123 {
5124 type => 'cell',
5125 criteria => 'greater than',
5126 value => 5,
5127 format => $red_format,
5128 }
5129 );
5130 Or, using the "between" criteria:
5132 $worksheet->conditional_formatting( 'C1:C4',
5134 {
5135 type => 'cell',
5136 criteria => 'between',
5137 minimum => 20,
5138 maximum => 30,
5139 format => $green_format,
5140 }
5141 );
5142 criteria
5144 The "criteria" parameter is used to set the criteria by which the cell
5145 data will be evaluated. It has no default value. The most common
5146 criteria as applied to "{ type => 'cell' }" are:
5147 'between'
5149 'not between'
5150 'equal to' | '==' | '='
5151 'not equal to' | '!=' | '<>'
5152 'greater than' | '>'
5153 'less than' | '<'
5154 'greater than or equal to' | '>='
5155 'less than or equal to' | '<='
5156 You can either use Excel's textual description strings, in the first
5158 column above, or the more common symbolic alternatives.
5159 Additional criteria which are specific to other conditional format
5161 types are shown in the relevant sections below.
5162 value
5164 The "value" is generally used along with the "criteria" parameter to
5165 set the rule by which the cell data will be evaluated.
5166 type => 'cell',
5168 criteria => '>',
5169 value => 5
5170 format => $format,
5171 The "value" property can also be an cell reference.
5173 type => 'cell',
5175 criteria => '>',
5176 value => '$C$1',
5177 format => $format,
5178 format
5180 The "format" parameter is used to specify the format that will be
5181 applied to the cell when the conditional formatting criterion is met.
5182 The format is created using the "add_format()" method in the same way
5183 as cell formats:
5184 $format = $workbook->add_format( bold => 1, italic => 1 );
5186 $worksheet->conditional_formatting( 'A1',
5188 {
5189 type => 'cell',
5190 criteria => '>',
5191 value => 5
5192 format => $format,
5193 }
5194 );
5195 The conditional format follows the same rules as in Excel: it is
5197 superimposed over the existing cell format and not all font and border
5198 properties can be modified. Font properties that can't be modified are
5199 font name, font size, superscript and subscript. The border property
5200 that cannot be modified is diagonal borders.
5201 Excel specifies some default formats to be used with conditional
5203 formatting. You can replicate them using the following
5204 Excel::Writer::XLSX formats:
5205 # Light red fill with dark red text.
5207 my $format1 = $workbook->add_format(
5209 bg_color => '#FFC7CE',
5210 color => '#9C0006',
5211 );
5212 # Light yellow fill with dark yellow text.
5214 my $format2 = $workbook->add_format(
5216 bg_color => '#FFEB9C',
5217 color => '#9C6500',
5218 );
5219 # Green fill with dark green text.
5221 my $format3 = $workbook->add_format(
5223 bg_color => '#C6EFCE',
5224 color => '#006100',
5225 );
5226 minimum
5228 The "minimum" parameter is used to set the lower limiting value when
5229 the "criteria" is either 'between' or 'not between':
5230 validate => 'integer',
5232 criteria => 'between',
5233 minimum => 1,
5234 maximum => 100,
5235 maximum
5237 The "maximum" parameter is used to set the upper limiting value when
5238 the "criteria" is either 'between' or 'not between'. See the previous
5239 example.
5240 type => 'date'
5242 The "date" type is the same as the "cell" type and uses the same
5243 criteria and values. However it allows the "value", "minimum" and
5244 "maximum" properties to be specified in the ISO8601
5245 "yyyy-mm-ddThh:mm:ss.sss" date format which is detailed in the
5246 "write_date_time()" method.
5247 $worksheet->conditional_formatting( 'A1:A4',
5249 {
5250 type => 'date',
5251 criteria => 'greater than',
5252 value => '2011-01-01T',
5253 format => $format,
5254 }
5255 );
5256 type => 'time_period'
5258 The "time_period" type is used to specify Excel's "Dates Occurring"
5259 style conditional format.
5260 $worksheet->conditional_formatting( 'A1:A4',
5262 {
5263 type => 'time_period',
5264 criteria => 'yesterday',
5265 format => $format,
5266 }
5267 );
5268 The period is set in the "criteria" and can have one of the following
5270 values:
5271 criteria => 'yesterday',
5273 criteria => 'today',
5274 criteria => 'last 7 days',
5275 criteria => 'last week',
5276 criteria => 'this week',
5277 criteria => 'next week',
5278 criteria => 'last month',
5279 criteria => 'this month',
5280 criteria => 'next month'
5281 type => 'text'
5283 The "text" type is used to specify Excel's "Specific Text" style
5284 conditional format. It is used to do simple string matching using the
5285 "criteria" and "value" parameters:
5286 $worksheet->conditional_formatting( 'A1:A4',
5288 {
5289 type => 'text',
5290 criteria => 'containing',
5291 value => 'foo',
5292 format => $format,
5293 }
5294 );
5295 The "criteria" can have one of the following values:
5297 criteria => 'containing',
5299 criteria => 'not containing',
5300 criteria => 'begins with',
5301 criteria => 'ends with',
5302 The "value" parameter should be a string or single character.
5304 type => 'average'
5306 The "average" type is used to specify Excel's "Average" style
5307 conditional format.
5308 $worksheet->conditional_formatting( 'A1:A4',
5310 {
5311 type => 'average',
5312 criteria => 'above',
5313 format => $format,
5314 }
5315 );
5316 The type of average for the conditional format range is specified by
5318 the "criteria":
5319 criteria => 'above',
5321 criteria => 'below',
5322 criteria => 'equal or above',
5323 criteria => 'equal or below',
5324 criteria => '1 std dev above',
5325 criteria => '1 std dev below',
5326 criteria => '2 std dev above',
5327 criteria => '2 std dev below',
5328 criteria => '3 std dev above',
5329 criteria => '3 std dev below',
5330 type => 'duplicate'
5332 The "duplicate" type is used to highlight duplicate cells in a range:
5333 $worksheet->conditional_formatting( 'A1:A4',
5335 {
5336 type => 'duplicate',
5337 format => $format,
5338 }
5339 );
5340 type => 'unique'
5342 The "unique" type is used to highlight unique cells in a range:
5343 $worksheet->conditional_formatting( 'A1:A4',
5345 {
5346 type => 'unique',
5347 format => $format,
5348 }
5349 );
5350 type => 'top'
5352 The "top" type is used to specify the top "n" values by number or
5353 percentage in a range:
5354 $worksheet->conditional_formatting( 'A1:A4',
5356 {
5357 type => 'top',
5358 value => 10,
5359 format => $format,
5360 }
5361 );
5362 The "criteria" can be used to indicate that a percentage condition is
5364 required:
5365 $worksheet->conditional_formatting( 'A1:A4',
5367 {
5368 type => 'top',
5369 value => 10,
5370 criteria => '%',
5371 format => $format,
5372 }
5373 );
5374 type => 'bottom'
5376 The "bottom" type is used to specify the bottom "n" values by number or
5377 percentage in a range.
5378 It takes the same parameters as "top", see above.
5380 type => 'blanks'
5382 The "blanks" type is used to highlight blank cells in a range:
5383 $worksheet->conditional_formatting( 'A1:A4',
5385 {
5386 type => 'blanks',
5387 format => $format,
5388 }
5389 );
5390 type => 'no_blanks'
5392 The "no_blanks" type is used to highlight non blank cells in a range:
5393 $worksheet->conditional_formatting( 'A1:A4',
5395 {
5396 type => 'no_blanks',
5397 format => $format,
5398 }
5399 );
5400 type => 'errors'
5402 The "errors" type is used to highlight error cells in a range:
5403 $worksheet->conditional_formatting( 'A1:A4',
5405 {
5406 type => 'errors',
5407 format => $format,
5408 }
5409 );
5410 type => 'no_errors'
5412 The "no_errors" type is used to highlight non error cells in a range:
5413 $worksheet->conditional_formatting( 'A1:A4',
5415 {
5416 type => 'no_errors',
5417 format => $format,
5418 }
5419 );
5420 type => 'formula'
5422 The "formula" type is used to specify a conditional format based on a
5423 user defined formula:
5424 $worksheet->conditional_formatting( 'A1:A4',
5426 {
5427 type => 'formula',
5428 criteria => '=$A$1 > 5',
5429 format => $format,
5430 }
5431 );
5432 The formula is specified in the "criteria".
5434 type => '2_color_scale'
5436 The "2_color_scale" type is used to specify Excel's "2 Color Scale"
5437 style conditional format.
5438 $worksheet->conditional_formatting( 'A1:A12',
5440 {
5441 type => '2_color_scale',
5442 }
5443 );
5444 This conditional type can be modified with "min_type", "max_type",
5446 "min_value", "max_value", "min_color" and "max_color", see below.
5447 type => '3_color_scale'
5449 The "3_color_scale" type is used to specify Excel's "3 Color Scale"
5450 style conditional format.
5451 $worksheet->conditional_formatting( 'A1:A12',
5453 {
5454 type => '3_color_scale',
5455 }
5456 );
5457 This conditional type can be modified with "min_type", "mid_type",
5459 "max_type", "min_value", "mid_value", "max_value", "min_color",
5460 "mid_color" and "max_color", see below.
5461 type => 'data_bar'
5463 The "data_bar" type is used to specify Excel's "Data Bar" style
5464 conditional format.
5465 $worksheet->conditional_formatting( 'A1:A12',
5467 {
5468 type => 'data_bar',
5469 }
5470 );
5471 This data bar conditional type can be modified with the following
5473 parameters, which are explained in the sections below. These properties
5474 were available in the original xlsx file specification used in Excel
5475 2007::
5476 min_type
5478 max_type
5479 min_value
5480 max_value
5481 bar_color
5482 bar_only
5483 In Excel 2010 additional data bar properties were added such as solid
5485 (non-gradient) bars and control over how negative values are displayed.
5486 These properties can be set using the following parameters:
5487 bar_solid
5489 bar_negative_color
5490 bar_border_color
5491 bar_negative_border_color
5492 bar_negative_color_same
5493 bar_negative_border_color_same
5494 bar_no_border
5495 bar_direction
5496 bar_axis_position
5497 bar_axis_color
5498 data_bar_2010
5499 Files that use these Excel 2010 properties can still be opened in Excel
5501 2007 but the data bars will be displayed without them.
5502 type => 'icon_set'
5504 The "icon_set" type is used to specify a conditional format with a set
5505 of icons such as traffic lights or arrows:
5506 $worksheet->conditional_formatting( 'A1:C1',
5508 {
5509 type => 'icon_set',
5510 icon_style => '3_traffic_lights',
5511 }
5512 );
5513 The icon set style is specified by the "icon_style" parameter. Valid
5515 options are:
5516 3_arrows
5518 3_arrows_gray
5519 3_flags
5520 3_signs
5521 3_symbols
5522 3_symbols_circled
5523 3_traffic_lights
5524 3_traffic_lights_rimmed
5525 4_arrows
5527 4_arrows_gray
5528 4_ratings
5529 4_red_to_black
5530 4_traffic_lights
5531 5_arrows
5533 5_arrows_gray
5534 5_quarters
5535 5_ratings
5536 The criteria, type and value of each icon can be specified using the
5538 "icon" array of hash refs with optional "criteria", "type" and "value"
5539 parameters:
5540 $worksheet->conditional_formatting( 'A1:D1',
5542 {
5543 type => 'icon_set',
5544 icon_style => '4_red_to_black',
5545 icons => [ {criteria => '>', type => 'number', value => 90},
5546 {criteria => '>=', type => 'percentile', value => 50},
5547 {criteria => '>', type => 'percent', value => 25},
5548 ],
5549 }
5550 );
5551 The "icons criteria" parameter should be either ">=" or ">". The
5553 default "criteria" is ">=".
5554 The "icons type" parameter should be one of the following values:
5556 number
5558 percentile
5559 percent
5560 formula
5561 The default "type" is "percent".
5563 The "icons value" parameter can be a value or formula:
5565 $worksheet->conditional_formatting( 'A1:D1',
5567 {
5568 type => 'icon_set',
5569 icon_style => '4_red_to_black',
5570 icons => [ {value => 90},
5571 {value => 50},
5572 {value => 25},
5573 ],
5574 }
5575 );
5576 Note: The "icons" parameters should start with the highest value and
5578 with each subsequent one being lower. The default "value" is "(n * 100)
5579 / number_of_icons". The lowest number icon in an icon set has
5580 properties defined by Excel. Therefore in a "n" icon set, there is no
5581 "n-1" hash of parameters.
5582 The order of the icons can be reversed using the "reverse_icons"
5584 parameter:
5585 $worksheet->conditional_formatting( 'A1:C1',
5587 {
5588 type => 'icon_set',
5589 icon_style => '3_arrows',
5590 reverse_icons => 1,
5591 }
5592 );
5593 The icons can be displayed without the cell value using the
5595 "icons_only" parameter:
5596 $worksheet->conditional_formatting( 'A1:C1',
5598 {
5599 type => 'icon_set',
5600 icon_style => '3_flags',
5601 icons_only => 1,
5602 }
5603 );
5604 min_type, mid_type, max_type
5606 The "min_type" and "max_type" properties are available when the
5607 conditional formatting type is "2_color_scale", "3_color_scale" or
5608 "data_bar". The "mid_type" is available for "3_color_scale". The
5609 properties are used as follows:
5610 $worksheet->conditional_formatting( 'A1:A12',
5612 {
5613 type => '2_color_scale',
5614 min_type => 'percent',
5615 max_type => 'percent',
5616 }
5617 );
5618 The available min/mid/max types are:
5620 min (for min_type only)
5622 num
5623 percent
5624 percentile
5625 formula
5626 max (for max_type only)
5627 min_value, mid_value, max_value
5629 The "min_value" and "max_value" properties are available when the
5630 conditional formatting type is "2_color_scale", "3_color_scale" or
5631 "data_bar". The "mid_value" is available for "3_color_scale". The
5632 properties are used as follows:
5633 $worksheet->conditional_formatting( 'A1:A12',
5635 {
5636 type => '2_color_scale',
5637 min_value => 10,
5638 max_value => 90,
5639 }
5640 );
5641 min_color, mid_color, max_color, bar_color
5643 The "min_color" and "max_color" properties are available when the
5644 conditional formatting type is "2_color_scale", "3_color_scale" or
5645 "data_bar". The "mid_color" is available for "3_color_scale". The
5646 properties are used as follows:
5647 $worksheet->conditional_formatting( 'A1:A12',
5649 {
5650 type => '2_color_scale',
5651 min_color => "#C5D9F1",
5652 max_color => "#538ED5",
5653 }
5654 );
5655 The color can be specified as an Excel::Writer::XLSX color index or,
5657 more usefully, as a HTML style RGB hex number, as shown above.
5658 bar_only
5660 The "bar_only" parameter property displays a bar data but not the data
5661 in the cells:
5662 $worksheet->conditional_formatting( 'D3:D14',
5664 {
5665 type => 'data_bar',
5666 bar_only => 1
5667 }
5668 );
5669 bar_solid
5671 The "bar_solid" parameter turns on a solid (non-gradient) fill for data
5672 bars:
5673 $worksheet->conditional_formatting( 'H3:H14',
5675 {
5676 type => 'data_bar',
5677 bar_solid => 1
5678 }
5679 );
5680 Note, this property is only visible in Excel 2010 and later.
5682 bar_negative_color
5684 The "bar_negative_color" parameter is used to set the color fill for
5685 the negative portion of a data bar.
5686 The color can be specified as an Excel::Writer::XLSX color index or as
5688 a HTML style RGB hex number, as shown in the other examples.
5689 Note, this property is only visible in Excel 2010 and later.
5691 bar_border_color
5693 The "bar_border_color" parameter is used to set the border color of a
5694 data bar.
5695 The color can be specified as an Excel::Writer::XLSX color index or as
5697 a HTML style RGB hex number, as shown in the other examples.
5698 Note, this property is only visible in Excel 2010 and later.
5700 bar_negative_border_color
5702 The "bar_negative_border_color" parameter is used to set the border
5703 color of the negative portion of a data bar.
5704 The color can be specified as an Excel::Writer::XLSX color index or as
5706 a HTML style RGB hex number, as shown in the other examples.
5707 Note, this property is only visible in Excel 2010 and later.
5709 bar_negative_color_same
5711 The "bar_negative_color_same" parameter sets the fill color for the
5712 negative portion of a data bar to be the same as the fill color for the
5713 positive portion of the data bar:
5714 $worksheet->conditional_formatting( 'N3:N14',
5716 {
5717 type => 'data_bar',
5718 bar_negative_color_same => 1,
5719 bar_negative_border_color_same => 1
5720 }
5721 );
5722 Note, this property is only visible in Excel 2010 and later.
5724 bar_negative_border_color_same
5726 The "bar_negative_border_color_same" parameter sets the border color
5727 for the negative portion of a data bar to be the same as the border
5728 color for the positive portion of the data bar.
5729 Note, this property is only visible in Excel 2010 and later.
5731 bar_no_border
5733 The "bar_no_border" parameter turns off the border of a data bar.
5734 Note, this property is only visible in Excel 2010 and later, however
5736 the default in Excel 2007 is not to have a border.
5737 bar_direction
5739 The "bar_direction" parameter sets the direction for data bars. This
5740 property can be either "left" for left-to-right or "right" for right-
5741 to-left. If the property isn't set then Excel will adjust the position
5742 automatically based on the context:
5743 $worksheet->conditional_formatting( 'J3:J14',
5745 {
5746 type => 'data_bar',
5747 bar_direction => 'right'
5748 }
5749 );
5750 Note, this property is only visible in Excel 2010 and later.
5752 bar_axis_position
5754 The "bar_axis_position" parameter sets the position within the cells
5755 for the axis that is shown in data bars when there are negative values
5756 to display. The property can be either "middle" or "none". If the
5757 property isn't set then Excel will position the axis based on the range
5758 of positive and negative values.
5759 Note, this property is only visible in Excel 2010 and later.
5761 bar_axis_color
5763 The "bar_axis_color" parameter sets the color for the axis that is
5764 shown in data bars when there are negative values to display.
5765 The color can be specified as an Excel::Writer::XLSX color index or as
5767 a HTML style RGB hex number, as shown in the other examples.
5768 Note, this property is only visible in Excel 2010 and later.
5770 data_bar_2010
5772 The "data_bar_2010" parameter sets Excel 2010 style data bars even when
5773 Excel 2010 specific properties aren't used. This can be used to create
5774 consistency across all the data bar formatting in a worksheet:
5775 $worksheet->conditional_formatting( 'L3:L14',
5777 {
5778 type => 'data_bar',
5779 data_bar_2010 => 1
5780 }
5781 );
5782 Note, this property is only visible in Excel 2010 and later.
5784 stop_if_true
5786 The "stop_if_true" parameter, if set to a true value, will enable the
5787 "stop if true" feature on the conditional formatting rule, so that
5788 subsequent rules are not examined for any cell on which the conditions
5789 for this rule are met.
5790 Conditional Formatting Examples
5792 Example 1. Highlight cells greater than an integer value.
5793 $worksheet->conditional_formatting( 'A1:F10',
5795 {
5796 type => 'cell',
5797 criteria => 'greater than',
5798 value => 5,
5799 format => $format,
5800 }
5801 );
5802 Example 2. Highlight cells greater than a value in a reference cell.
5804 $worksheet->conditional_formatting( 'A1:F10',
5806 {
5807 type => 'cell',
5808 criteria => 'greater than',
5809 value => '$H$1',
5810 format => $format,
5811 }
5812 );
5813 Example 3. Highlight cells greater than a certain date:
5815 $worksheet->conditional_formatting( 'A1:F10',
5817 {
5818 type => 'date',
5819 criteria => 'greater than',
5820 value => '2011-01-01T',
5821 format => $format,
5822 }
5823 );
5824 Example 4. Highlight cells with a date in the last seven days:
5826 $worksheet->conditional_formatting( 'A1:F10',
5828 {
5829 type => 'time_period',
5830 criteria => 'last 7 days',
5831 format => $format,
5832 }
5833 );
5834 Example 5. Highlight cells with strings starting with the letter "b":
5836 $worksheet->conditional_formatting( 'A1:F10',
5838 {
5839 type => 'text',
5840 criteria => 'begins with',
5841 value => 'b',
5842 format => $format,
5843 }
5844 );
5845 Example 6. Highlight cells that are 1 std deviation above the average
5847 for the range:
5848 $worksheet->conditional_formatting( 'A1:F10',
5850 {
5851 type => 'average',
5852 format => $format,
5853 }
5854 );
5855 Example 7. Highlight duplicate cells in a range:
5857 $worksheet->conditional_formatting( 'A1:F10',
5859 {
5860 type => 'duplicate',
5861 format => $format,
5862 }
5863 );
5864 Example 8. Highlight unique cells in a range.
5866 $worksheet->conditional_formatting( 'A1:F10',
5868 {
5869 type => 'unique',
5870 format => $format,
5871 }
5872 );
5873 Example 9. Highlight the top 10 cells.
5875 $worksheet->conditional_formatting( 'A1:F10',
5877 {
5878 type => 'top',
5879 value => 10,
5880 format => $format,
5881 }
5882 );
5883 Example 10. Highlight blank cells.
5885 $worksheet->conditional_formatting( 'A1:F10',
5887 {
5888 type => 'blanks',
5889 format => $format,
5890 }
5891 );
5892 Example 11. Set traffic light icons in 3 cells:
5894 $worksheet->conditional_formatting( 'A1:C1',
5896 {
5897 type => 'icon_set',
5898 icon_style => '3_traffic_lights',
5899 }
5900 );
5901 See also the "conditional_format.pl" example program in "EXAMPLES".
5903
5905 Sparklines are a feature of Excel 2010+ which allows you to add small
5906 charts to worksheet cells. These are useful for showing visual trends
5907 in data in a compact format.
5908 In Excel::Writer::XLSX Sparklines can be added to cells using the
5910 "add_sparkline()" worksheet method:
5911 $worksheet->add_sparkline(
5913 {
5914 location => 'F2',
5915 range => 'Sheet1!A2:E2',
5916 type => 'column',
5917 style => 12,
5918 }
5919 );
5920 Note: Sparklines are a feature of Excel 2010+ only. You can write them
5922 to an XLSX file that can be read by Excel 2007 but they won't be
5923 displayed.
5924 add_sparkline( { parameter => 'value', ... } )
5926 The "add_sparkline()" worksheet method is used to add sparklines to a
5927 cell or a range of cells.
5928 The parameters to "add_sparkline()" must be passed in a hash ref. The
5930 main sparkline parameters are:
5931 location (required)
5933 range (required)
5934 type
5935 style
5936 markers
5938 negative_points
5939 axis
5940 reverse
5941 Other, less commonly used parameters are:
5943 high_point
5945 low_point
5946 first_point
5947 last_point
5948 max
5949 min
5950 empty_cells
5951 show_hidden
5952 date_axis
5953 weight
5954 series_color
5956 negative_color
5957 markers_color
5958 first_color
5959 last_color
5960 high_color
5961 low_color
5962 These parameters are explained in the sections below:
5964 location
5966 This is the cell where the sparkline will be displayed:
5967 location => 'F1'
5969 The "location" should be a single cell. (For multiple cells see
5971 "Grouped Sparklines" below).
5972 To specify the location in row-column notation use the
5974 "xl_rowcol_to_cell()" function from the Excel::Writer::XLSX::Utility
5975 module.
5976 use Excel::Writer::XLSX::Utility ':rowcol';
5978 ...
5979 location => xl_rowcol_to_cell( 0, 5 ), # F1
5980 range
5982 This specifies the cell data range that the sparkline will plot:
5983 $worksheet->add_sparkline(
5985 {
5986 location => 'F1',
5987 range => 'A1:E1',
5988 }
5989 );
5990 The "range" should be a 2D array. (For 3D arrays of cells see "Grouped
5992 Sparklines" below).
5993 If "range" is not on the same worksheet you can specify its location
5995 using the usual Excel notation:
5996 range => 'Sheet1!A1:E1',
5998 If the worksheet contains spaces or special characters you should quote
6000 the worksheet name in the same way that Excel does:
6001 range => q('Monthly Data'!A1:E1),
6003 To specify the location in row-column notation use the "xl_range()" or
6005 "xl_range_formula()" functions from the Excel::Writer::XLSX::Utility
6006 module.
6007 use Excel::Writer::XLSX::Utility ':rowcol';
6009 ...
6010 range => xl_range( 1, 1, 0, 4 ), # 'A1:E1'
6011 range => xl_range_formula( 'Sheet1', 0, 0, 0, 4 ), # 'Sheet1!A2:E2'
6012 type
6014 Specifies the type of sparkline. There are 3 available sparkline types:
6015 line (default)
6017 column
6018 win_loss
6019 For example:
6021 {
6023 location => 'F1',
6024 range => 'A1:E1',
6025 type => 'column',
6026 }
6027 style
6029 Excel provides 36 built-in Sparkline styles in 6 groups of 6. The
6030 "style" parameter can be used to replicate these and should be a
6031 corresponding number from 1 .. 36.
6032 {
6034 location => 'A14',
6035 range => 'Sheet2!A2:J2',
6036 style => 3,
6037 }
6038 The style number starts in the top left of the style grid and runs left
6040 to right. The default style is 1. It is possible to override colour
6041 elements of the sparklines using the *_color parameters below.
6042 markers
6044 Turn on the markers for "line" style sparklines.
6045 {
6047 location => 'A6',
6048 range => 'Sheet2!A1:J1',
6049 markers => 1,
6050 }
6051 Markers aren't shown in Excel for "column" and "win_loss" sparklines.
6053 negative_points
6055 Highlight negative values in a sparkline range. This is usually
6056 required with "win_loss" sparklines.
6057 {
6059 location => 'A21',
6060 range => 'Sheet2!A3:J3',
6061 type => 'win_loss',
6062 negative_points => 1,
6063 }
6064 axis
6066 Display a horizontal axis in the sparkline:
6067 {
6069 location => 'A10',
6070 range => 'Sheet2!A1:J1',
6071 axis => 1,
6072 }
6073 reverse
6075 Plot the data from right-to-left instead of the default left-to-right:
6076 {
6078 location => 'A24',
6079 range => 'Sheet2!A4:J4',
6080 type => 'column',
6081 reverse => 1,
6082 }
6083 weight
6085 Adjust the default line weight (thickness) for "line" style sparklines.
6086 weight => 0.25,
6088 The weight value should be one of the following values allowed by
6090 Excel:
6091 0.25 0.5 0.75
6093 1 1.25
6094 2.25
6095 3
6096 4.25
6097 6
6098 high_point, low_point, first_point, last_point
6100 Highlight points in a sparkline range.
6101 high_point => 1,
6103 low_point => 1,
6104 first_point => 1,
6105 last_point => 1,
6106 max, min
6108 Specify the maximum and minimum vertical axis values:
6109 max => 0.5,
6111 min => -0.5,
6112 As a special case you can set the maximum and minimum to be for a group
6114 of sparklines rather than one:
6115 max => 'group',
6117 See "Grouped Sparklines" below.
6119 empty_cells
6121 Define how empty cells are handled in a sparkline.
6122 empty_cells => 'zero',
6124 The available options are:
6126 gaps : show empty cells as gaps (the default).
6128 zero : plot empty cells as 0.
6129 connect: Connect points with a line ("line" type sparklines only).
6130 show_hidden
6132 Plot data in hidden rows and columns:
6133 show_hidden => 1,
6135 Note, this option is off by default.
6137 date_axis
6139 Specify an alternative date axis for the sparkline. This is useful if
6140 the data being plotted isn't at fixed width intervals:
6141 {
6143 location => 'F3',
6144 range => 'A3:E3',
6145 date_axis => 'A4:E4',
6146 }
6147 The number of cells in the date range should correspond to the number
6149 of cells in the data range.
6150 series_color
6152 It is possible to override the colour of a sparkline style using the
6153 following parameters:
6154 series_color
6156 negative_color
6157 markers_color
6158 first_color
6159 last_color
6160 high_color
6161 low_color
6162 The color should be specified as a HTML style "#rrggbb" hex value:
6164 {
6166 location => 'A18',
6167 range => 'Sheet2!A2:J2',
6168 type => 'column',
6169 series_color => '#E965E0',
6170 }
6171 Grouped Sparklines
6173 The "add_sparkline()" worksheet method can be used multiple times to
6174 write as many sparklines as are required in a worksheet.
6175 However, it is sometimes necessary to group contiguous sparklines so
6177 that changes that are applied to one are applied to all. In Excel this
6178 is achieved by selecting a 3D range of cells for the data "range" and a
6179 2D range of cells for the "location".
6180 In Excel::Writer::XLSX, you can simulate this by passing an array refs
6182 of values to "location" and "range":
6183 {
6185 location => [ 'A27', 'A28', 'A29' ],
6186 range => [ 'Sheet2!A5:J5', 'Sheet2!A6:J6', 'Sheet2!A7:J7' ],
6187 markers => 1,
6188 }
6189 Sparkline examples
6191 See the "sparklines1.pl" and "sparklines2.pl" example programs in the
6192 "examples" directory of the distro.
6193
6195 Tables in Excel are a way of grouping a range of cells into a single
6196 entity that has common formatting or that can be referenced from
6197 formulas. Tables can have column headers, autofilters, total rows,
6198 column formulas and default formatting.
6199 For more information see "An Overview of Excel Tables"
6201 <http://office.microsoft.com/en-us/excel-help/overview-of-excel-tables-HA010048546.aspx>.
6202 Note, tables don't work in Excel::Writer::XLSX when
6204 "set_optimization()" mode in on.
6205 add_table( $row1, $col1, $row2, $col2, { parameter => 'value', ... })
6207 Tables are added to a worksheet using the "add_table()" method:
6208 $worksheet->add_table( 'B3:F7', { %parameters } );
6210 The data range can be specified in 'A1' or 'row/col' notation (see also
6212 the note about "Cell notation" for more information):
6213 $worksheet->add_table( 'B3:F7' );
6215 # Same as:
6216 $worksheet->add_table( 2, 1, 6, 5 );
6217 The last parameter in "add_table()" should be a hash ref containing the
6219 parameters that describe the table options and data. The available
6220 parameters are:
6221 data
6223 autofilter
6224 header_row
6225 banded_columns
6226 banded_rows
6227 first_column
6228 last_column
6229 style
6230 total_row
6231 columns
6232 name
6233 The table parameters are detailed below. There are no required
6235 parameters and the hash ref isn't required if no options are specified.
6236 data
6238 The "data" parameter can be used to specify the data in the cells of
6239 the table.
6240 my $data = [
6242 [ 'Apples', 10000, 5000, 8000, 6000 ],
6243 [ 'Pears', 2000, 3000, 4000, 5000 ],
6244 [ 'Bananas', 6000, 6000, 6500, 6000 ],
6245 [ 'Oranges', 500, 300, 200, 700 ],
6246 ];
6248 $worksheet->add_table( 'B3:F7', { data => $data } );
6250 Table data can also be written separately, as an array or individual
6252 cells.
6253 # These two statements are the same as the single statement above.
6255 $worksheet->add_table( 'B3:F7' );
6256 $worksheet->write_col( 'B4', $data );
6257 Writing the cell data separately is occasionally required when you need
6259 to control the "write_*()" method used to populate the cells or if you
6260 wish to tweak the cell formatting.
6261 The "data" structure should be an array ref of array refs holding row
6263 data as shown above.
6264 header_row
6266 The "header_row" parameter can be used to turn on or off the header row
6267 in the table. It is on by default.
6268 $worksheet->add_table( 'B4:F7', { header_row => 0 } ); # Turn header off.
6270 The header row will contain default captions such as "Column 1",
6272 "Column 2", etc. These captions can be overridden using the "columns"
6273 parameter below.
6274 autofilter
6276 The "autofilter" parameter can be used to turn on or off the autofilter
6277 in the header row. It is on by default.
6278 $worksheet->add_table( 'B3:F7', { autofilter => 0 } ); # Turn autofilter off.
6280 The "autofilter" is only shown if the "header_row" is on. Filters
6282 within the table are not supported.
6283 banded_rows
6285 The "banded_rows" parameter can be used to used to create rows of
6286 alternating colour in the table. It is on by default.
6287 $worksheet->add_table( 'B3:F7', { banded_rows => 0 } );
6289 banded_columns
6291 The "banded_columns" parameter can be used to used to create columns of
6292 alternating colour in the table. It is off by default.
6293 $worksheet->add_table( 'B3:F7', { banded_columns => 1 } );
6295 first_column
6297 The "first_column" parameter can be used to highlight the first column
6298 of the table. The type of highlighting will depend on the "style" of
6299 the table. It may be bold text or a different colour. It is off by
6300 default.
6301 $worksheet->add_table( 'B3:F7', { first_column => 1 } );
6303 last_column
6305 The "last_column" parameter can be used to highlight the last column of
6306 the table. The type of highlighting will depend on the "style" of the
6307 table. It may be bold text or a different colour. It is off by default.
6308 $worksheet->add_table( 'B3:F7', { last_column => 1 } );
6310 style
6312 The "style" parameter can be used to set the style of the table.
6313 Standard Excel table format names should be used (with matching
6314 capitalisation):
6315 $worksheet11->add_table(
6317 'B3:F7',
6318 {
6319 data => $data,
6320 style => 'Table Style Light 11',
6321 }
6322 );
6323 The default table style is 'Table Style Medium 9'.
6325 name
6327 By default tables are named "Table1", "Table2", etc. The "name"
6328 parameter can be used to set the name of the table:
6329 $worksheet->add_table( 'B3:F7', { name => 'SalesData' } );
6331 If you override the table name you must ensure that it doesn't clash
6333 with an existing table name and that it follows Excel's requirements
6334 for table names
6335 <http://office.microsoft.com/en-001/excel-help/define-and-use-names-in-formulas-HA010147120.aspx#BMsyntax_rules_for_names>.
6336 If you need to know the name of the table, for example to use it in a
6338 formula, you can get it as follows:
6339 my $table = $worksheet2->add_table( 'B3:F7' );
6341 my $table_name = $table->{_name};
6342 total_row
6344 The "total_row" parameter can be used to turn on the total row in the
6345 last row of a table. It is distinguished from the other rows by a
6346 different formatting and also with dropdown "SUBTOTAL" functions.
6347 $worksheet->add_table( 'B3:F7', { total_row => 1 } );
6349 The default total row doesn't have any captions or functions. These
6351 must by specified via the "columns" parameter below.
6352 columns
6354 The "columns" parameter can be used to set properties for columns
6355 within the table.
6356 The sub-properties that can be set are:
6358 header
6360 formula
6361 total_string
6362 total_function
6363 total_value
6364 format
6365 header_format
6366 The column data must be specified as an array ref of hash refs. For
6368 example to override the default 'Column n' style table headers:
6369 $worksheet->add_table(
6371 'B3:F7',
6372 {
6373 data => $data,
6374 columns => [
6375 { header => 'Product' },
6376 { header => 'Quarter 1' },
6377 { header => 'Quarter 2' },
6378 { header => 'Quarter 3' },
6379 { header => 'Quarter 4' },
6380 ]
6381 }
6382 );
6383 If you don't wish to specify properties for a specific column you pass
6385 an empty hash ref and the defaults will be applied:
6386 ...
6388 columns => [
6389 { header => 'Product' },
6390 { header => 'Quarter 1' },
6391 { }, # Defaults to 'Column 3'.
6392 { header => 'Quarter 3' },
6393 { header => 'Quarter 4' },
6394 ]
6395 ...
6396 Column formulas can by applied using the "formula" column property:
6398 $worksheet8->add_table(
6400 'B3:G7',
6401 {
6402 data => $data,
6403 columns => [
6404 { header => 'Product' },
6405 { header => 'Quarter 1' },
6406 { header => 'Quarter 2' },
6407 { header => 'Quarter 3' },
6408 { header => 'Quarter 4' },
6409 {
6410 header => 'Year',
6411 formula => '=SUM(Table8[@[Quarter 1]:[Quarter 4]])'
6412 },
6413 ]
6414 }
6415 );
6416 The Excel 2007 "[#This Row]" and Excel 2010 "@" structural references
6418 are supported within the formula.
6419 As stated above the "total_row" table parameter turns on the "Total"
6421 row in the table but it doesn't populate it with any defaults. Total
6422 captions and functions must be specified via the "columns" property and
6423 the "total_string", "total_function" and "total_value" sub properties:
6424 $worksheet10->add_table(
6426 'B3:F8',
6427 {
6428 data => $data,
6429 total_row => 1,
6430 columns => [
6431 { header => 'Product', total_string => 'Totals' },
6432 { header => 'Quarter 1', total_function => 'sum' },
6433 { header => 'Quarter 2', total_function => 'sum' },
6434 { header => 'Quarter 3', total_function => 'sum' },
6435 { header => 'Quarter 4', total_function => 'sum' },
6436 ]
6437 }
6438 );
6439 The supported totals row "SUBTOTAL" functions are:
6441 average
6443 count_nums
6444 count
6445 max
6446 min
6447 std_dev
6448 sum
6449 var
6450 User defined functions or formulas aren't supported.
6452 It is also possible to set a calculated value for the "total_function"
6454 using the "total_value" sub property. This is only necessary when
6455 creating workbooks for applications that cannot calculate the value of
6456 formulas automatically. This is similar to setting the "value" optional
6457 property in "write_formula()":
6458 $worksheet10->add_table(
6460 'B3:F8',
6461 {
6462 data => $data,
6463 total_row => 1,
6464 columns => [
6465 { total_string => 'Totals' },
6466 { total_function => 'sum', total_value => 100 },
6467 { total_function => 'sum', total_value => 200 },
6468 { total_function => 'sum', total_value => 100 },
6469 { total_function => 'sum', total_value => 400 },
6470 ]
6471 }
6472 );
6473 Formatting can also be applied to columns, to the column data using
6475 "format" and to the header using "header_format":
6476 my $currency_format = $workbook->add_format( num_format => '$#,##0' );
6478 $worksheet->add_table(
6480 'B3:D8',
6481 {
6482 data => $data,
6483 total_row => 1,
6484 columns => [
6485 { header => 'Product', total_string => 'Totals' },
6486 {
6487 header => 'Quarter 1',
6488 total_function => 'sum',
6489 format => $currency_format,
6490 },
6491 {
6492 header => 'Quarter 2',
6493 header_format => $bold,
6494 total_function => 'sum',
6495 format => $currency_format,
6496 },
6497 ]
6498 }
6499 );
6500 Standard Excel::Writer::XLSX format objects can be used. However, they
6502 should be limited to numerical formats for the columns and simple
6503 formatting like text wrap for the headers. Overriding other table
6504 formatting may produce inconsistent results.
6505
6507 Introduction
6508 The following is a brief introduction to formulas and functions in
6509 Excel and Excel::Writer::XLSX.
6510 A formula is a string that begins with an equals sign:
6512 '=A1+B1'
6514 '=AVERAGE(1, 2, 3)'
6515 The formula can contain numbers, strings, boolean values, cell
6517 references, cell ranges and functions. Named ranges are not supported.
6518 Formulas should be written as they appear in Excel, that is cells and
6519 functions must be in uppercase.
6520 Cells in Excel are referenced using the A1 notation system where the
6522 column is designated by a letter and the row by a number. Columns range
6523 from A to XFD i.e. 0 to 16384, rows range from 1 to 1048576. The
6524 "Excel::Writer::XLSX::Utility" module that is included in the distro
6525 contains helper functions for dealing with A1 notation, for example:
6526 use Excel::Writer::XLSX::Utility;
6528 ( $row, $col ) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
6530 $str = xl_rowcol_to_cell( 1, 2 ); # C2
6531 The Excel "$" notation in cell references is also supported. This
6533 allows you to specify whether a row or column is relative or absolute.
6534 This only has an effect if the cell is copied. The following examples
6535 show relative and absolute values.
6536 '=A1' # Column and row are relative
6538 '=$A1' # Column is absolute and row is relative
6539 '=A$1' # Column is relative and row is absolute
6540 '=$A$1' # Column and row are absolute
6541 Formulas can also refer to cells in other worksheets of the current
6543 workbook. For example:
6544 '=Sheet2!A1'
6546 '=Sheet2!A1:A5'
6547 '=Sheet2:Sheet3!A1'
6548 '=Sheet2:Sheet3!A1:A5'
6549 q{='Test Data'!A1}
6550 q{='Test Data1:Test Data2'!A1}
6551 The sheet reference and the cell reference are separated by "!" the
6553 exclamation mark symbol. If worksheet names contain spaces, commas or
6554 parentheses then Excel requires that the name is enclosed in single
6555 quotes as shown in the last two examples above. In order to avoid using
6556 a lot of escape characters you can use the quote operator "q{}" to
6557 protect the quotes. See "perlop" in the main Perl documentation. Only
6558 valid sheet names that have been added using the "add_worksheet()"
6559 method can be used in formulas. You cannot reference external
6560 workbooks.
6561 The following table lists the operators that are available in Excel's
6563 formulas. The majority of the operators are the same as Perl's,
6564 differences are indicated:
6565 Arithmetic operators:
6567 =====================
6568 Operator Meaning Example
6569 + Addition 1+2
6570 - Subtraction 2-1
6571 * Multiplication 2*3
6572 / Division 1/4
6573 ^ Exponentiation 2^3 # Equivalent to **
6574 - Unary minus -(1+2)
6575 % Percent (Not modulus) 13%
6576 Comparison operators:
6579 =====================
6580 Operator Meaning Example
6581 = Equal to A1 = B1 # Equivalent to ==
6582 <> Not equal to A1 <> B1 # Equivalent to !=
6583 > Greater than A1 > B1
6584 < Less than A1 < B1
6585 >= Greater than or equal to A1 >= B1
6586 <= Less than or equal to A1 <= B1
6587 String operator:
6590 ================
6591 Operator Meaning Example
6592 & Concatenation "Hello " & "World!" # [1]
6593 Reference operators:
6596 ====================
6597 Operator Meaning Example
6598 : Range operator A1:A4 # [2]
6599 , Union operator SUM(1, 2+2, B3) # [3]
6600 Notes:
6603 [1]: Equivalent to "Hello " . "World!" in Perl.
6604 [2]: This range is equivalent to cells A1, A2, A3 and A4.
6605 [3]: The comma behaves like the list separator in Perl.
6606 The range and comma operators can have different symbols in non-English
6608 versions of Excel, see below.
6609 For a general introduction to Excel's formulas and an explanation of
6611 the syntax of the function refer to the Excel help files or the
6612 following:
6613 <http://office.microsoft.com/en-us/assistance/CH062528031033.aspx>.
6614 In most cases a formula in Excel can be used directly in the
6616 "write_formula" method. However, there are a few potential issues and
6617 differences that the user should be aware of. These are explained in
6618 the following sections.
6619 Non US Excel functions and syntax
6621 Excel stores formulas in the format of the US English version,
6622 regardless of the language or locale of the end-user's version of
6623 Excel. Therefore all formula function names written using
6624 Excel::Writer::XLSX must be in English:
6625 worksheet->write_formula('A1', '=SUM(1, 2, 3)'); # OK
6627 worksheet->write_formula('A2', '=SOMME(1, 2, 3)'); # French. Error on load.
6628 Also, formulas must be written with the US style separator/range
6630 operator which is a comma (not semi-colon). Therefore a formula with
6631 multiple values should be written as follows:
6632 worksheet->write_formula('A1', '=SUM(1, 2, 3)'); # OK
6634 worksheet->write_formula('A2', '=SUM(1; 2; 3)'); # Semi-colon. Error on load.
6635 If you have a non-English version of Excel you can use the following
6637 multi-lingual Formula Translator
6638 (<http://en.excel-translator.de/language/>) to help you convert the
6639 formula. It can also replace semi-colons with commas.
6640 Formulas added in Excel 2010 and later
6642 Excel 2010 and later added functions which weren't defined in the
6643 original file specification. These functions are referred to by
6644 Microsoft as future functions. Examples of these functions are "ACOT",
6645 "CHISQ.DIST.RT" , "CONFIDENCE.NORM", "STDEV.P", "STDEV.S" and
6646 "WORKDAY.INTL".
6647 When written using "write_formula()" these functions need to be fully
6649 qualified with a "_xlfn." (or other) prefix as they are shown the list
6650 below. For example:
6651 worksheet->write_formula('A1', '=_xlfn.STDEV.S(B1:B10)')
6653 They will appear without the prefix in Excel.
6655 The following list is taken from the MS XLSX extensions documentation
6657 on future functions:
6658 <http://msdn.microsoft.com/en-us/library/dd907480%28v=office.12%29.aspx>:
6659 _xlfn.ACOT
6661 _xlfn.ACOTH
6662 _xlfn.AGGREGATE
6663 _xlfn.ARABIC
6664 _xlfn.BASE
6665 _xlfn.BETA.DIST
6666 _xlfn.BETA.INV
6667 _xlfn.BINOM.DIST
6668 _xlfn.BINOM.DIST.RANGE
6669 _xlfn.BINOM.INV
6670 _xlfn.BITAND
6671 _xlfn.BITLSHIFT
6672 _xlfn.BITOR
6673 _xlfn.BITRSHIFT
6674 _xlfn.BITXOR
6675 _xlfn.CEILING.MATH
6676 _xlfn.CEILING.PRECISE
6677 _xlfn.CHISQ.DIST
6678 _xlfn.CHISQ.DIST.RT
6679 _xlfn.CHISQ.INV
6680 _xlfn.CHISQ.INV.RT
6681 _xlfn.CHISQ.TEST
6682 _xlfn.COMBINA
6683 _xlfn.CONFIDENCE.NORM
6684 _xlfn.CONFIDENCE.T
6685 _xlfn.COT
6686 _xlfn.COTH
6687 _xlfn.COVARIANCE.P
6688 _xlfn.COVARIANCE.S
6689 _xlfn.CSC
6690 _xlfn.CSCH
6691 _xlfn.DAYS
6692 _xlfn.DECIMAL
6693 ECMA.CEILING
6694 _xlfn.ERF.PRECISE
6695 _xlfn.ERFC.PRECISE
6696 _xlfn.EXPON.DIST
6697 _xlfn.F.DIST
6698 _xlfn.F.DIST.RT
6699 _xlfn.F.INV
6700 _xlfn.F.INV.RT
6701 _xlfn.F.TEST
6702 _xlfn.FILTERXML
6703 _xlfn.FLOOR.MATH
6704 _xlfn.FLOOR.PRECISE
6705 _xlfn.FORECAST.ETS
6706 _xlfn.FORECAST.ETS.CONFINT
6707 _xlfn.FORECAST.ETS.SEASONALITY
6708 _xlfn.FORECAST.ETS.STAT
6709 _xlfn.FORECAST.LINEAR
6710 _xlfn.FORMULATEXT
6711 _xlfn.GAMMA
6712 _xlfn.GAMMA.DIST
6713 _xlfn.GAMMA.INV
6714 _xlfn.GAMMALN.PRECISE
6715 _xlfn.GAUSS
6716 _xlfn.HYPGEOM.DIST
6717 _xlfn.IFNA
6718 _xlfn.IMCOSH
6719 _xlfn.IMCOT
6720 _xlfn.IMCSC
6721 _xlfn.IMCSCH
6722 _xlfn.IMSEC
6723 _xlfn.IMSECH
6724 _xlfn.IMSINH
6725 _xlfn.IMTAN
6726 _xlfn.ISFORMULA
6727 ISO.CEILING
6728 _xlfn.ISOWEEKNUM
6729 _xlfn.LOGNORM.DIST
6730 _xlfn.LOGNORM.INV
6731 _xlfn.MODE.MULT
6732 _xlfn.MODE.SNGL
6733 _xlfn.MUNIT
6734 _xlfn.NEGBINOM.DIST
6735 NETWORKDAYS.INTL
6736 _xlfn.NORM.DIST
6737 _xlfn.NORM.INV
6738 _xlfn.NORM.S.DIST
6739 _xlfn.NORM.S.INV
6740 _xlfn.NUMBERVALUE
6741 _xlfn.PDURATION
6742 _xlfn.PERCENTILE.EXC
6743 _xlfn.PERCENTILE.INC
6744 _xlfn.PERCENTRANK.EXC
6745 _xlfn.PERCENTRANK.INC
6746 _xlfn.PERMUTATIONA
6747 _xlfn.PHI
6748 _xlfn.POISSON.DIST
6749 _xlfn.QUARTILE.EXC
6750 _xlfn.QUARTILE.INC
6751 _xlfn.QUERYSTRING
6752 _xlfn.RANK.AVG
6753 _xlfn.RANK.EQ
6754 _xlfn.RRI
6755 _xlfn.SEC
6756 _xlfn.SECH
6757 _xlfn.SHEET
6758 _xlfn.SHEETS
6759 _xlfn.SKEW.P
6760 _xlfn.STDEV.P
6761 _xlfn.STDEV.S
6762 _xlfn.T.DIST
6763 _xlfn.T.DIST.2T
6764 _xlfn.T.DIST.RT
6765 _xlfn.T.INV
6766 _xlfn.T.INV.2T
6767 _xlfn.T.TEST
6768 _xlfn.UNICHAR
6769 _xlfn.UNICODE
6770 _xlfn.VAR.P
6771 _xlfn.VAR.S
6772 _xlfn.WEBSERVICE
6773 _xlfn.WEIBULL.DIST
6774 WORKDAY.INTL
6775 _xlfn.XOR
6776 _xlfn.Z.TEST
6777 Using Tables in Formulas
6779 Worksheet tables can be added with Excel::Writer::XLSX using the
6780 "add_table()" method:
6781 worksheet->add_table('B3:F7', {options});
6783 By default tables are named "Table1", "Table2", etc., in the order that
6785 they are added. However it can also be set by the user using the "name"
6786 parameter:
6787 worksheet->add_table('B3:F7', {'name': 'SalesData'});
6789 If you need to know the name of the table, for example to use it in a
6791 formula, you can get it as follows:
6792 table = worksheet->add_table('B3:F7');
6794 table_name = table->{_name};
6795 When used in a formula a table name such as "TableX" should be referred
6797 to as "TableX[]" (like a Perl array):
6798 worksheet->write_formula('A5', '=VLOOKUP("Sales", Table1[], 2, FALSE');
6800 Dealing with #NAME? errors
6802 If there is an error in the syntax of a formula it is usually displayed
6803 in Excel as "#NAME?". If you encounter an error like this you can debug
6804 it as follows:
6805 1. Ensure the formula is valid in Excel by copying and pasting it into
6807 a cell. Note, this should be done in Excel and not other applications
6808 such as OpenOffice or LibreOffice since they may have slightly
6809 different syntax.
6810 2. Ensure the formula is using comma separators instead of semi-colons,
6811 see "Non US Excel functions and syntax" above.
6812 3. Ensure the formula is in English, see "Non US Excel functions and
6813 syntax" above.
6814 4. Ensure that the formula doesn't contain an Excel 2010+ future
6815 function as listed in "Formulas added in Excel 2010 and later" above.
6816 If it does then ensure that the correct prefix is used.
6817 Finally if you have completed all the previous steps and still get a
6819 "#NAME?" error you can examine a valid Excel file to see what the
6820 correct syntax should be. To do this you should create a valid formula
6821 in Excel and save the file. You can then examine the XML in the
6822 unzipped file.
6823 The following shows how to do that using Linux "unzip" and libxml's
6825 xmllint <http://xmlsoft.org/xmllint.html> to format the XML for
6826 clarity:
6827 $ unzip myfile.xlsx -d myfile
6829 $ xmllint --format myfile/xl/worksheets/sheet1.xml | grep '<f>'
6830 <f>SUM(1, 2, 3)</f>
6832 Formula Results
6834 Excel::Writer::XLSX doesn't calculate the result of a formula and
6835 instead stores the value 0 as the formula result. It then sets a global
6836 flag in the XLSX file to say that all formulas and functions should be
6837 recalculated when the file is opened.
6838 This is the method recommended in the Excel documentation and in
6840 general it works fine with spreadsheet applications. However,
6841 applications that don't have a facility to calculate formulas will only
6842 display the 0 results. Examples of such applications are Excel Viewer,
6843 PDF Converters, and some mobile device applications.
6844 If required, it is also possible to specify the calculated result of
6846 the formula using the optional last "value" parameter in
6847 "write_formula":
6848 worksheet->write_formula('A1', '=2+2', num_format, 4);
6850 The "value" parameter can be a number, a string, a boolean sting
6852 ('TRUE' or 'FALSE') or one of the following Excel error codes:
6853 #DIV/0!
6855 #N/A
6856 #NAME?
6857 #NULL!
6858 #NUM!
6859 #REF!
6860 #VALUE!
6861 It is also possible to specify the calculated result of an array
6863 formula created with "write_array_formula":
6864 # Specify the result for a single cell range.
6866 worksheet->write_array_formula('A1:A1', '{=SUM(B1:C1*B2:C2)}', format, 2005);
6867 However, using this parameter only writes a single value to the upper
6869 left cell in the result array. For a multi-cell array formula where the
6870 results are required, the other result values can be specified by using
6871 "write_number()" to write to the appropriate cell:
6872 # Specify the results for a multi cell range.
6874 worksheet->write_array_formula('A1:A3', '{=TREND(C1:C3,B1:B3)}', format, 15);
6875 worksheet->write_number('A2', 12, format);
6876 worksheet->write_number('A3', 14, format);
6877
6879 An Excel "xlsm" file is exactly the same as a "xlsx" file except that
6880 is includes an additional "vbaProject.bin" file which contains
6881 functions and/or macros. Excel uses a different extension to
6882 differentiate between the two file formats since files containing
6883 macros are usually subject to additional security checks.
6884 The "vbaProject.bin" file is a binary OLE COM container. This was the
6886 format used in older "xls" versions of Excel prior to Excel 2007.
6887 Unlike all of the other components of an xlsx/xlsm file the data isn't
6888 stored in XML format. Instead the functions and macros as stored as
6889 pre-parsed binary format. As such it wouldn't be feasible to define
6890 macros and create a "vbaProject.bin" file from scratch (at least not in
6891 the remaining lifespan and interest levels of the author).
6892 Instead a workaround is used to extract "vbaProject.bin" files from
6894 existing xlsm files and then add these to Excel::Writer::XLSX files.
6895 The extract_vba utility
6897 The "extract_vba" utility is used to extract the "vbaProject.bin"
6898 binary from an Excel 2007+ xlsm file. The utility is included in the
6899 Excel::Writer::XLSX bin directory and is also installed as a standalone
6900 executable file:
6901 $ extract_vba macro_file.xlsm
6903 Extracted: vbaProject.bin
6904 Adding the VBA macros to a Excel::Writer::XLSX file
6906 Once the "vbaProject.bin" file has been extracted it can be added to
6907 the Excel::Writer::XLSX workbook using the "add_vba_project()" method:
6908 $workbook->add_vba_project( './vbaProject.bin' );
6910 If the VBA file contains functions you can then refer to them in
6912 calculations using "write_formula":
6913 $worksheet->write_formula( 'A1', '=MyMortgageCalc(200000, 25)' );
6915 Excel files that contain functions and macros should use an "xlsm"
6917 extension or else Excel will complain and possibly not open the file:
6918 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
6920 It is also possible to assign a macro to a button that is inserted into
6922 a worksheet using the "insert_button()" method:
6923 my $workbook = Excel::Writer::XLSX->new( 'file.xlsm' );
6925 ...
6926 $workbook->add_vba_project( './vbaProject.bin' );
6927 $worksheet->insert_button( 'C2', { macro => 'my_macro' } );
6929 It may be necessary to specify a more explicit macro name prefixed by
6931 the workbook VBA name as follows:
6932 $worksheet->insert_button( 'C2', { macro => 'ThisWorkbook.my_macro' } );
6934 See the "macros.pl" from the examples directory for a working example.
6936 Note: Button is the only VBA Control supported by Excel::Writer::XLSX.
6938 Due to the large effort in implementation (1+ man months) it is
6939 unlikely that any other form elements will be added in the future.
6940 Setting the VBA codenames
6942 VBA macros generally refer to workbook and worksheet objects. If the
6943 VBA codenames aren't specified then Excel::Writer::XLSX will use the
6944 Excel defaults of "ThisWorkbook" and "Sheet1", "Sheet2" etc.
6945 If the macro uses other codenames you can set them using the workbook
6947 and worksheet "set_vba_name()" methods as follows:
6948 $workbook->set_vba_name( 'MyWorkbook' );
6950 $worksheet->set_vba_name( 'MySheet' );
6951 You can find the names that are used in the VBA editor or by unzipping
6953 the "xlsm" file and grepping the files. The following shows how to do
6954 that using libxml's xmllint <http://xmlsoft.org/xmllint.html> to format
6955 the XML for clarity:
6956 $ unzip myfile.xlsm -d myfile
6958 $ xmllint --format `find myfile -name "*.xml" | xargs` | grep "Pr.*codeName"
6959 <workbookPr codeName="MyWorkbook" defaultThemeVersion="124226"/>
6961 <sheetPr codeName="MySheet"/>
6962 Note: This step is particularly important for macros created with non-
6964 English versions of Excel.
6965 What to do if it doesn't work
6967 This feature should be considered experimental and there is no
6968 guarantee that it will work in all cases. Some effort may be required
6969 and some knowledge of VBA will certainly help. If things don't work out
6970 here are some things to try:
6971 · Start with a simple macro file, ensure that it works and then add
6973 complexity.
6974 · Try to extract the macros from an Excel 2007 file. The method
6976 should work with macros from later versions (it was also tested
6977 with Excel 2010 macros). However there may be features in the macro
6978 files of more recent version of Excel that aren't backward
6979 compatible.
6980 · Check the code names that macros use to refer to the workbook and
6982 worksheets (see the previous section above). In general VBA uses a
6983 code name of "ThisWorkbook" to refer to the current workbook and
6984 the sheet name (such as "Sheet1") to refer to the worksheets. These
6985 are the defaults used by Excel::Writer::XLSX. If the macro uses
6986 other names then you can specify these using the workbook and
6987 worksheet "set_vba_name()" methods:
6988 $workbook>set_vba_name( 'MyWorkbook' );
6990 $worksheet->set_vba_name( 'MySheet' );
6991
6993 See Excel::Writer::XLSX::Examples for a full list of examples.
6994 Example 1
6996 The following example shows some of the basic features of
6997 Excel::Writer::XLSX.
6998 #!/usr/bin/perl -w
7000 use strict;
7002 use Excel::Writer::XLSX;
7003 # Create a new workbook called simple.xlsx and add a worksheet
7005 my $workbook = Excel::Writer::XLSX->new( 'simple.xlsx' );
7006 my $worksheet = $workbook->add_worksheet();
7007 # The general syntax is write($row, $column, $token). Note that row and
7009 # column are zero indexed
7010 # Write some text
7012 $worksheet->write( 0, 0, 'Hi Excel!' );
7013 # Write some numbers
7016 $worksheet->write( 2, 0, 3 );
7017 $worksheet->write( 3, 0, 3.00000 );
7018 $worksheet->write( 4, 0, 3.00001 );
7019 $worksheet->write( 5, 0, 3.14159 );
7020 # Write some formulas
7023 $worksheet->write( 7, 0, '=A3 + A6' );
7024 $worksheet->write( 8, 0, '=IF(A5>3,"Yes", "No")' );
7025 # Write a hyperlink
7028 my $hyperlink_format = $workbook->add_format(
7029 color => 'blue',
7030 underline => 1,
7031 );
7032 $worksheet->write( 10, 0, 'http://www.perl.com/', $hyperlink_format );
7034 $workbook->close();
7036 Example 2
7038 The following is a general example which demonstrates some features of
7039 working with multiple worksheets.
7040 #!/usr/bin/perl -w
7042 use strict;
7044 use Excel::Writer::XLSX;
7045 # Create a new Excel workbook
7047 my $workbook = Excel::Writer::XLSX->new( 'regions.xlsx' );
7048 # Add some worksheets
7050 my $north = $workbook->add_worksheet( 'North' );
7051 my $south = $workbook->add_worksheet( 'South' );
7052 my $east = $workbook->add_worksheet( 'East' );
7053 my $west = $workbook->add_worksheet( 'West' );
7054 # Add a Format
7056 my $format = $workbook->add_format();
7057 $format->set_bold();
7058 $format->set_color( 'blue' );
7059 # Add a caption to each worksheet
7061 for my $worksheet ( $workbook->sheets() ) {
7062 $worksheet->write( 0, 0, 'Sales', $format );
7063 }
7064 # Write some data
7066 $north->write( 0, 1, 200000 );
7067 $south->write( 0, 1, 100000 );
7068 $east->write( 0, 1, 150000 );
7069 $west->write( 0, 1, 100000 );
7070 # Set the active worksheet
7072 $south->activate();
7073 # Set the width of the first column
7075 $south->set_column( 0, 0, 20 );
7076 # Set the active cell
7078 $south->set_selection( 0, 1 );
7079 $workbook->close();
7081 Example 3
7083 Example of how to add conditional formatting to an Excel::Writer::XLSX
7084 file. The example below highlights cells that have a value greater than
7085 or equal to 50 in red and cells below that value in green.
7086 #!/usr/bin/perl
7088 use strict;
7090 use warnings;
7091 use Excel::Writer::XLSX;
7092 my $workbook = Excel::Writer::XLSX->new( 'conditional_format.xlsx' );
7094 my $worksheet = $workbook->add_worksheet();
7095 # This example below highlights cells that have a value greater than or
7098 # equal to 50 in red and cells below that value in green.
7099 # Light red fill with dark red text.
7101 my $format1 = $workbook->add_format(
7102 bg_color => '#FFC7CE',
7103 color => '#9C0006',
7104 );
7106 # Green fill with dark green text.
7108 my $format2 = $workbook->add_format(
7109 bg_color => '#C6EFCE',
7110 color => '#006100',
7111 );
7113 # Some sample data to run the conditional formatting against.
7115 my $data = [
7116 [ 34, 72, 38, 30, 75, 48, 75, 66, 84, 86 ],
7117 [ 6, 24, 1, 84, 54, 62, 60, 3, 26, 59 ],
7118 [ 28, 79, 97, 13, 85, 93, 93, 22, 5, 14 ],
7119 [ 27, 71, 40, 17, 18, 79, 90, 93, 29, 47 ],
7120 [ 88, 25, 33, 23, 67, 1, 59, 79, 47, 36 ],
7121 [ 24, 100, 20, 88, 29, 33, 38, 54, 54, 88 ],
7122 [ 6, 57, 88, 28, 10, 26, 37, 7, 41, 48 ],
7123 [ 52, 78, 1, 96, 26, 45, 47, 33, 96, 36 ],
7124 [ 60, 54, 81, 66, 81, 90, 80, 93, 12, 55 ],
7125 [ 70, 5, 46, 14, 71, 19, 66, 36, 41, 21 ],
7126 ];
7127 my $caption = 'Cells with values >= 50 are in light red. '
7129 . 'Values < 50 are in light green';
7130 # Write the data.
7132 $worksheet->write( 'A1', $caption );
7133 $worksheet->write_col( 'B3', $data );
7134 # Write a conditional format over a range.
7136 $worksheet->conditional_formatting( 'B3:K12',
7137 {
7138 type => 'cell',
7139 criteria => '>=',
7140 value => 50,
7141 format => $format1,
7142 }
7143 );
7144 # Write another conditional format over the same range.
7146 $worksheet->conditional_formatting( 'B3:K12',
7147 {
7148 type => 'cell',
7149 criteria => '<',
7150 value => 50,
7151 format => $format2,
7152 }
7153 );
7154 $workbook->close();
7156 Example 4
7158 The following is a simple example of using functions.
7159 #!/usr/bin/perl -w
7161 use strict;
7163 use Excel::Writer::XLSX;
7164 # Create a new workbook and add a worksheet
7166 my $workbook = Excel::Writer::XLSX->new( 'stats.xlsx' );
7167 my $worksheet = $workbook->add_worksheet( 'Test data' );
7168 # Set the column width for columns 1
7170 $worksheet->set_column( 0, 0, 20 );
7171 # Create a format for the headings
7174 my $format = $workbook->add_format();
7175 $format->set_bold();
7176 # Write the sample data
7179 $worksheet->write( 0, 0, 'Sample', $format );
7180 $worksheet->write( 0, 1, 1 );
7181 $worksheet->write( 0, 2, 2 );
7182 $worksheet->write( 0, 3, 3 );
7183 $worksheet->write( 0, 4, 4 );
7184 $worksheet->write( 0, 5, 5 );
7185 $worksheet->write( 0, 6, 6 );
7186 $worksheet->write( 0, 7, 7 );
7187 $worksheet->write( 0, 8, 8 );
7188 $worksheet->write( 1, 0, 'Length', $format );
7190 $worksheet->write( 1, 1, 25.4 );
7191 $worksheet->write( 1, 2, 25.4 );
7192 $worksheet->write( 1, 3, 24.8 );
7193 $worksheet->write( 1, 4, 25.0 );
7194 $worksheet->write( 1, 5, 25.3 );
7195 $worksheet->write( 1, 6, 24.9 );
7196 $worksheet->write( 1, 7, 25.2 );
7197 $worksheet->write( 1, 8, 24.8 );
7198 # Write some statistical functions
7200 $worksheet->write( 4, 0, 'Count', $format );
7201 $worksheet->write( 4, 1, '=COUNT(B1:I1)' );
7202 $worksheet->write( 5, 0, 'Sum', $format );
7204 $worksheet->write( 5, 1, '=SUM(B2:I2)' );
7205 $worksheet->write( 6, 0, 'Average', $format );
7207 $worksheet->write( 6, 1, '=AVERAGE(B2:I2)' );
7208 $worksheet->write( 7, 0, 'Min', $format );
7210 $worksheet->write( 7, 1, '=MIN(B2:I2)' );
7211 $worksheet->write( 8, 0, 'Max', $format );
7213 $worksheet->write( 8, 1, '=MAX(B2:I2)' );
7214 $worksheet->write( 9, 0, 'Standard Deviation', $format );
7216 $worksheet->write( 9, 1, '=STDEV(B2:I2)' );
7217 $worksheet->write( 10, 0, 'Kurtosis', $format );
7219 $worksheet->write( 10, 1, '=KURT(B2:I2)' );
7220 $workbook->close();
7222 Example 5
7224 The following example converts a tab separated file called "tab.txt"
7225 into an Excel file called "tab.xlsx".
7226 #!/usr/bin/perl -w
7228 use strict;
7230 use Excel::Writer::XLSX;
7231 open( TABFILE, 'tab.txt' ) or die "tab.txt: $!";
7233 my $workbook = Excel::Writer::XLSX->new( 'tab.xlsx' );
7235 my $worksheet = $workbook->add_worksheet();
7236 # Row and column are zero indexed
7238 my $row = 0;
7239 while ( <TABFILE> ) {
7241 chomp;
7242 # Split on single tab
7244 my @fields = split( '\t', $_ );
7245 my $col = 0;
7247 for my $token ( @fields ) {
7248 $worksheet->write( $row, $col, $token );
7249 $col++;
7250 }
7251 $row++;
7252 }
7253 $workbook->close();
7255 NOTE: This is a simple conversion program for illustrative purposes
7257 only. For converting a CSV or Tab separated or any other type of
7258 delimited text file to Excel I recommend the more rigorous csv2xls
7259 program that is part of H.Merijn Brand's Text::CSV_XS module distro.
7260 See the examples/csv2xls link here:
7262 <http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>.
7263 Additional Examples
7265 The following is a description of the example files that are provided
7266 in the standard Excel::Writer::XLSX distribution. They demonstrate the
7267 different features and options of the module. See
7268 Excel::Writer::XLSX::Examples for more details.
7269 Getting started
7271 ===============
7272 a_simple.pl A simple demo of some of the features.
7273 bug_report.pl A template for submitting bug reports.
7274 demo.pl A demo of some of the available features.
7275 formats.pl All the available formatting on several worksheets.
7276 regions.pl A simple example of multiple worksheets.
7277 stats.pl Basic formulas and functions.
7278 Intermediate
7281 ============
7282 autofilter.pl Examples of worksheet autofilters.
7283 array_formula.pl Examples of how to write array formulas.
7284 cgi.pl A simple CGI program.
7285 chart_area.pl A demo of area style charts.
7286 chart_bar.pl A demo of bar (vertical histogram) style charts.
7287 chart_column.pl A demo of column (histogram) style charts.
7288 chart_line.pl A demo of line style charts.
7289 chart_pie.pl A demo of pie style charts.
7290 chart_doughnut.pl A demo of doughnut style charts.
7291 chart_radar.pl A demo of radar style charts.
7292 chart_scatter.pl A demo of scatter style charts.
7293 chart_secondary_axis.pl A demo of a line chart with a secondary axis.
7294 chart_combined.pl A demo of a combined column and line chart.
7295 chart_pareto.pl A demo of a combined Pareto chart.
7296 chart_stock.pl A demo of stock style charts.
7297 chart_data_table.pl A demo of a chart with a data table on the axis.
7298 chart_data_tools.pl A demo of charts with data highlighting options.
7299 chart_clustered.pl A demo of a chart with a clustered axis.
7300 chart_styles.pl A demo of the available chart styles.
7301 colors.pl A demo of the colour palette and named colours.
7302 comments1.pl Add comments to worksheet cells.
7303 comments2.pl Add comments with advanced options.
7304 conditional_format.pl Add conditional formats to a range of cells.
7305 data_validate.pl An example of data validation and dropdown lists.
7306 date_time.pl Write dates and times with write_date_time().
7307 defined_name.pl Example of how to create defined names.
7308 diag_border.pl A simple example of diagonal cell borders.
7309 filehandle.pl Examples of working with filehandles.
7310 headers.pl Examples of worksheet headers and footers.
7311 hide_row_col.pl Example of hiding rows and columns.
7312 hide_sheet.pl Simple example of hiding a worksheet.
7313 hyperlink1.pl Shows how to create web hyperlinks.
7314 hyperlink2.pl Examples of internal and external hyperlinks.
7315 indent.pl An example of cell indentation.
7316 macros.pl An example of adding macros from an existing file.
7317 merge1.pl A simple example of cell merging.
7318 merge2.pl A simple example of cell merging with formatting.
7319 merge3.pl Add hyperlinks to merged cells.
7320 merge4.pl An advanced example of merging with formatting.
7321 merge5.pl An advanced example of merging with formatting.
7322 merge6.pl An example of merging with Unicode strings.
7323 mod_perl1.pl A simple mod_perl 1 program.
7324 mod_perl2.pl A simple mod_perl 2 program.
7325 panes.pl An examples of how to create panes.
7326 outline.pl An example of outlines and grouping.
7327 outline_collapsed.pl An example of collapsed outlines.
7328 protection.pl Example of cell locking and formula hiding.
7329 rich_strings.pl Example of strings with multiple formats.
7330 right_to_left.pl Change default sheet direction to right to left.
7331 sales.pl An example of a simple sales spreadsheet.
7332 shape1.pl Insert shapes in worksheet.
7333 shape2.pl Insert shapes in worksheet. With properties.
7334 shape3.pl Insert shapes in worksheet. Scaled.
7335 shape4.pl Insert shapes in worksheet. With modification.
7336 shape5.pl Insert shapes in worksheet. With connections.
7337 shape6.pl Insert shapes in worksheet. With connections.
7338 shape7.pl Insert shapes in worksheet. One to many connections.
7339 shape8.pl Insert shapes in worksheet. One to many connections.
7340 shape_all.pl Demo of all the available shape and connector types.
7341 sparklines1.pl Simple sparklines demo.
7342 sparklines2.pl Sparklines demo showing formatting options.
7343 stats_ext.pl Same as stats.pl with external references.
7344 stocks.pl Demonstrates conditional formatting.
7345 tab_colors.pl Example of how to set worksheet tab colours.
7346 tables.pl Add Excel tables to a worksheet.
7347 write_handler1.pl Example of extending the write() method. Step 1.
7348 write_handler2.pl Example of extending the write() method. Step 2.
7349 write_handler3.pl Example of extending the write() method. Step 3.
7350 write_handler4.pl Example of extending the write() method. Step 4.
7351 write_to_scalar.pl Example of writing an Excel file to a Perl scalar.
7352 Unicode
7354 =======
7355 unicode_2022_jp.pl Japanese: ISO-2022-JP.
7356 unicode_8859_11.pl Thai: ISO-8859_11.
7357 unicode_8859_7.pl Greek: ISO-8859_7.
7358 unicode_big5.pl Chinese: BIG5.
7359 unicode_cp1251.pl Russian: CP1251.
7360 unicode_cp1256.pl Arabic: CP1256.
7361 unicode_cyrillic.pl Russian: Cyrillic.
7362 unicode_koi8r.pl Russian: KOI8-R.
7363 unicode_polish_utf8.pl Polish : UTF8.
7364 unicode_shift_jis.pl Japanese: Shift JIS.
7365
7367 The following limits are imposed by Excel 2007+:
7368 Description Limit
7370 -------------------------------------- ------
7371 Maximum number of chars in a string 32,767
7372 Maximum number of columns 16,384
7373 Maximum number of rows 1,048,576
7374 Maximum chars in a sheet name 31
7375 Maximum chars in a header/footer 254
7376 Maximum characters in hyperlink url 255
7378 Maximum characters in hyperlink anchor 255
7379 Maximum number of unique hyperlinks* 65,530
7380 * Per worksheet. Excel allows a greater number of non-unique hyperlinks
7382 if they are contiguous and can be grouped into a single range. This
7383 will be supported in a later version of Excel::Writer::XLSX if
7384 possible.
7385
7387 The "Excel::Writer::XLSX" module is a drop-in replacement for
7388 "Spreadsheet::WriteExcel".
7389 It supports all of the features of Spreadsheet::WriteExcel with some
7391 minor differences noted below.
7392 Workbook Methods Support
7394 ================ ======
7395 new() Yes
7396 add_worksheet() Yes
7397 add_format() Yes
7398 add_chart() Yes
7399 add_shape() Yes. Not in Spreadsheet::WriteExcel.
7400 add_vba_project() Yes. Not in Spreadsheet::WriteExcel.
7401 close() Yes
7402 set_properties() Yes
7403 define_name() Yes
7404 set_tempdir() Yes
7405 set_custom_color() Yes
7406 sheets() Yes
7407 set_1904() Yes
7408 set_optimization() Yes. Not required in Spreadsheet::WriteExcel.
7409 add_chart_ext() Not supported. Not required in Excel::Writer::XLSX.
7410 compatibility_mode() Deprecated. Not required in Excel::Writer::XLSX.
7411 set_codepage() Deprecated. Not required in Excel::Writer::XLSX.
7412 Worksheet Methods Support
7415 ================= =======
7416 write() Yes
7417 write_number() Yes
7418 write_string() Yes
7419 write_rich_string() Yes. Not in Spreadsheet::WriteExcel.
7420 write_blank() Yes
7421 write_row() Yes
7422 write_col() Yes
7423 write_date_time() Yes
7424 write_url() Yes
7425 write_formula() Yes
7426 write_array_formula() Yes. Not in Spreadsheet::WriteExcel.
7427 keep_leading_zeros() Yes
7428 write_comment() Yes
7429 show_comments() Yes
7430 set_comments_author() Yes
7431 add_write_handler() Yes
7432 insert_image() Yes.
7433 insert_chart() Yes
7434 insert_shape() Yes. Not in Spreadsheet::WriteExcel.
7435 insert_button() Yes. Not in Spreadsheet::WriteExcel.
7436 data_validation() Yes
7437 conditional_formatting() Yes. Not in Spreadsheet::WriteExcel.
7438 add_sparkline() Yes. Not in Spreadsheet::WriteExcel.
7439 add_table() Yes. Not in Spreadsheet::WriteExcel.
7440 get_name() Yes
7441 activate() Yes
7442 select() Yes
7443 hide() Yes
7444 set_first_sheet() Yes
7445 protect() Yes
7446 set_selection() Yes
7447 set_row() Yes.
7448 set_column() Yes.
7449 set_default_row() Yes. Not in Spreadsheet::WriteExcel.
7450 outline_settings() Yes
7451 freeze_panes() Yes
7452 split_panes() Yes
7453 merge_range() Yes
7454 merge_range_type() Yes. Not in Spreadsheet::WriteExcel.
7455 set_zoom() Yes
7456 right_to_left() Yes
7457 hide_zero() Yes
7458 set_tab_color() Yes
7459 autofilter() Yes
7460 filter_column() Yes
7461 filter_column_list() Yes. Not in Spreadsheet::WriteExcel.
7462 write_utf16be_string() Deprecated. Use Perl utf8 strings instead.
7463 write_utf16le_string() Deprecated. Use Perl utf8 strings instead.
7464 store_formula() Deprecated. See docs.
7465 repeat_formula() Deprecated. See docs.
7466 write_url_range() Not supported. Not required in Excel::Writer::XLSX.
7467 Page Set-up Methods Support
7469 =================== =======
7470 set_landscape() Yes
7471 set_portrait() Yes
7472 set_page_view() Yes
7473 set_paper() Yes
7474 center_horizontally() Yes
7475 center_vertically() Yes
7476 set_margins() Yes
7477 set_header() Yes
7478 set_footer() Yes
7479 repeat_rows() Yes
7480 repeat_columns() Yes
7481 hide_gridlines() Yes
7482 print_row_col_headers() Yes
7483 print_area() Yes
7484 print_across() Yes
7485 fit_to_pages() Yes
7486 set_start_page() Yes
7487 set_print_scale() Yes
7488 set_h_pagebreaks() Yes
7489 set_v_pagebreaks() Yes
7490 Format Methods Support
7492 ============== =======
7493 set_font() Yes
7494 set_size() Yes
7495 set_color() Yes
7496 set_bold() Yes
7497 set_italic() Yes
7498 set_underline() Yes
7499 set_font_strikeout() Yes
7500 set_font_script() Yes
7501 set_font_outline() Yes
7502 set_font_shadow() Yes
7503 set_num_format() Yes
7504 set_locked() Yes
7505 set_hidden() Yes
7506 set_align() Yes
7507 set_rotation() Yes
7508 set_text_wrap() Yes
7509 set_text_justlast() Yes
7510 set_center_across() Yes
7511 set_indent() Yes
7512 set_shrink() Yes
7513 set_pattern() Yes
7514 set_bg_color() Yes
7515 set_fg_color() Yes
7516 set_border() Yes
7517 set_bottom() Yes
7518 set_top() Yes
7519 set_left() Yes
7520 set_right() Yes
7521 set_border_color() Yes
7522 set_bottom_color() Yes
7523 set_top_color() Yes
7524 set_left_color() Yes
7525 set_right_color() Yes
7526
7528 <http://search.cpan.org/search?dist=Archive-Zip/>.
7529 Perl 5.8.2.
7531
7533 "Spreadsheet::WriteExcel" was written to optimise speed and reduce
7534 memory usage. However, these design goals meant that it wasn't easy to
7535 implement features that many users requested such as writing formatting
7536 and data separately.
7537 As a result "Excel::Writer::XLSX" takes a different design approach and
7539 holds a lot more data in memory so that it is functionally more
7540 flexible.
7541 The effect of this is that Excel::Writer::XLSX is about 30% slower than
7543 Spreadsheet::WriteExcel and uses 5 times more memory.
7544 In addition the extended row and column ranges in Excel 2007+ mean that
7546 it is possible to run out of memory creating large files. This was
7547 almost never an issue with Spreadsheet::WriteExcel.
7548 This memory usage can be reduced almost completely by using the
7550 Workbook "set_optimization()" method:
7551 $workbook->set_optimization();
7553 This also gives an increase in performance to within 1-10% of
7555 Spreadsheet::WriteExcel, see below.
7556 The trade-off is that you won't be able to take advantage of any new
7558 features that manipulate cell data after it is written. One such
7559 feature is Tables.
7560 Performance figures
7562 The performance figures below show execution speed and memory usage for
7563 60 columns x N rows for a 50/50 mixture of strings and numbers.
7564 Percentage speeds are relative to Spreadsheet::WriteExcel.
7565 Excel::Writer::XLSX
7567 Rows Time (s) Memory (bytes) Rel. Time
7568 400 0.66 6,586,254 129%
7569 800 1.26 13,099,422 125%
7570 1600 2.55 26,126,361 123%
7571 3200 5.16 52,211,284 125%
7572 6400 10.47 104,401,428 128%
7573 12800 21.48 208,784,519 131%
7574 25600 43.90 417,700,746 126%
7575 51200 88.52 835,900,298 126%
7576 Excel::Writer::XLSX + set_optimisation()
7578 Rows Time (s) Memory (bytes) Rel. Time
7579 400 0.70 63,059 135%
7580 800 1.10 63,059 110%
7581 1600 2.30 63,062 111%
7582 3200 4.44 63,062 107%
7583 6400 8.91 63,062 109%
7584 12800 17.69 63,065 108%
7585 25600 35.15 63,065 101%
7586 51200 70.67 63,065 101%
7587 Spreadsheet::WriteExcel
7589 Rows Time (s) Memory (bytes)
7590 400 0.51 1,265,583
7591 800 1.01 2,424,855
7592 1600 2.07 4,743,400
7593 3200 4.14 9,411,139
7594 6400 8.20 18,766,915
7595 12800 16.39 37,478,468
7596 25600 34.72 75,044,423
7597 51200 70.21 150,543,431
7598
7600 The latest version of this module is always available at:
7601 <http://search.cpan.org/search?dist=Excel-Writer-XLSX/>.
7602
7604 The module can be installed using the standard Perl procedure:
7605 perl Makefile.PL
7607 make
7608 make test
7609 make install # You may need to be sudo/root
7610
7612 Filename required by Excel::Writer::XLSX->new()
7613 A filename must be given in the constructor.
7614 Can't open filename. It may be in use or protected.
7616 The file cannot be opened for writing. The directory that you are
7617 writing to may be protected or the file may be in use by another
7618 program.
7619 Can't call method "XXX" on an undefined value at someprogram.pl.
7621 On Windows this is usually caused by the file that you are trying
7622 to create clashing with a version that is already open and locked
7623 by Excel.
7624 The file you are trying to open 'file.xls' is in a different format
7626 than specified by the file extension.
7627 This warning occurs when you create an XLSX file but give it an xls
7628 extension.
7629
7631 Depending on your requirements, background and general sensibilities
7632 you may prefer one of the following methods of getting data into Excel:
7633 · Spreadsheet::WriteExcel
7635 This module is the precursor to Excel::Writer::XLSX and uses the
7637 same interface. It produces files in the Excel Biff xls format that
7638 was used in Excel versions 97-2003. These files can still be read
7639 by Excel 2007 but have some limitations in relation to the number
7640 of rows and columns that the format supports.
7641 Spreadsheet::WriteExcel.
7643 · Win32::OLE module and office automation
7645 This requires a Windows platform and an installed copy of Excel.
7647 This is the most powerful and complete method for interfacing with
7648 Excel.
7649 Win32::OLE
7651 · CSV, comma separated variables or text
7653 Excel will open and automatically convert files with a "csv"
7655 extension.
7656 To create CSV files refer to the Text::CSV_XS module.
7658 · DBI with DBD::ADO or DBD::ODBC
7660 Excel files contain an internal index table that allows them to act
7662 like a database file. Using one of the standard Perl database
7663 modules you can connect to an Excel file as a database.
7664 For other Perl-Excel modules try the following search:
7666 <http://search.cpan.org/search?mode=module&query=excel>.
7667
7669 To read data from Excel files try:
7670 · Spreadsheet::XLSX
7672 A module for reading formatted or unformatted data form XLSX files.
7674 Spreadsheet::XLSX
7676 · SimpleXlsx
7678 A lightweight module for reading data from XLSX files.
7680 SimpleXlsx
7682 · Spreadsheet::ParseExcel
7684 This module can read data from an Excel XLS file but it doesn't
7686 support the XLSX format.
7687 Spreadsheet::ParseExcel
7689 · Win32::OLE module and office automation (reading)
7691 See above.
7693 · DBI with DBD::ADO or DBD::ODBC.
7695 See above.
7697 For other Perl-Excel modules try the following search:
7699 <http://search.cpan.org/search?mode=module&query=excel>.
7700
7702 · Memory usage is very high for large worksheets.
7703 If you run out of memory creating large worksheets use the
7705 "set_optimization()" method. See "SPEED AND MEMORY USAGE" for more
7706 information.
7707 · Perl packaging programs can't find chart modules.
7709 When using Excel::Writer::XLSX charts with Perl packagers such as
7711 PAR or Cava you should explicitly include the chart that you are
7712 trying to create in your "use" statements. This isn't a bug as such
7713 but it might help someone from banging their head off a wall:
7714 ...
7716 use Excel::Writer::XLSX;
7717 use Excel::Writer::XLSX::Chart::Column;
7718 ...
7719 If you wish to submit a bug report run the "bug_report.pl" program in
7721 the "examples" directory of the distro.
7722 The bug tracker is on Github:
7724 <https://github.com/jmcnamara/excel-writer-xlsx/issues>.
7725
7727 The roadmap is as follows:
7728 · New separated data/formatting API to allow cells to be formatted
7730 after data is added.
7731 · More charting features.
7733
7735 The Excel::Writer::XLSX source code in host on github:
7736 <http://github.com/jmcnamara/excel-writer-xlsx>.
7737
7739 There is a Google group for discussing and asking questions about
7740 Excel::Writer::XLSX. This is a good place to search to see if your
7741 question has been asked before:
7742 <http://groups.google.com/group/spreadsheet-writeexcel>.
7743
7745 If you'd care to donate to the Excel::Writer::XLSX project or sponsor a
7746 new feature, you can do so via PayPal: <http://tinyurl.com/7ayes>.
7747
7749 Spreadsheet::WriteExcel:
7750 <http://search.cpan.org/dist/Spreadsheet-WriteExcel>.
7751 Spreadsheet::ParseExcel:
7753 <http://search.cpan.org/dist/Spreadsheet-ParseExcel>.
7754 Spreadsheet::XLSX: <http://search.cpan.org/dist/Spreadsheet-XLSX>.
7756
7758 The following people contributed to the debugging, testing or
7759 enhancement of Excel::Writer::XLSX:
7760 Rob Messer of IntelliSurvey gave me the initial prompt to port
7762 Spreadsheet::WriteExcel to the XLSX format. IntelliSurvey
7763 (<http://www.intellisurvey.com>) also sponsored large files
7764 optimisations and the charting feature.
7765 Bariatric Advantage (<http://www.bariatricadvantage.com>) sponsored
7767 work on chart formatting.
7768 Eric Johnson provided the ability to use secondary axes with charts.
7770 Thanks to Foxtons (<http://foxtons.co.uk>) for sponsoring this work.
7771 BuildFax (<http://www.buildfax.com>) sponsored the Tables feature and
7773 the Chart point formatting feature.
7774
7776 Because this software is licensed free of charge, there is no warranty
7777 for the software, to the extent permitted by applicable law. Except
7778 when otherwise stated in writing the copyright holders and/or other
7779 parties provide the software "as is" without warranty of any kind,
7780 either expressed or implied, including, but not limited to, the implied
7781 warranties of merchantability and fitness for a particular purpose. The
7782 entire risk as to the quality and performance of the software is with
7783 you. Should the software prove defective, you assume the cost of all
7784 necessary servicing, repair, or correction.
7785 In no event unless required by applicable law or agreed to in writing
7787 will any copyright holder, or any other party who may modify and/or
7788 redistribute the software as permitted by the above licence, be liable
7789 to you for damages, including any general, special, incidental, or
7790 consequential damages arising out of the use or inability to use the
7791 software (including but not limited to loss of data or data being
7792 rendered inaccurate or losses sustained by you or third parties or a
7793 failure of the software to operate with any other software), even if
7794 such holder or other party has been advised of the possibility of such
7795 damages.
7796
7798 Either the Perl Artistic Licence
7799 <http://dev.perl.org/licenses/artistic.html> or the GPL
7800 <http://www.opensource.org/licenses/gpl-license.php>.
7801
7803 John McNamara jmcnamara@cpan.org
7804 Wilderness for miles, eyes so mild and wise
7806 Oasis child, born and so wild
7807 Don't I know you better than the rest
7808 All deception, all deception from you
7809 Any way you run, you run before us
7811 Black and white horse arching among us
7812 Any way you run, you run before us
7813 Black and white horse arching among us
7814 -- Beach House
7816
7818 Copyright MM-MMXIX, John McNamara.
7819 All Rights Reserved. This module is free software. It may be used,
7821 redistributed and/or modified under the same terms as Perl itself.
7822perl v5.30.0 2019-07-26 Excel::Writer::XLSX(3)