1Spreadsheet::WriteExcelU(s3e)r Contributed Perl DocumentaStpiroenadsheet::WriteExcel(3)
2
6 Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file.
7
9 This document refers to version 2.40 of Spreadsheet::WriteExcel,
10 released November 6, 2013.
11
13 To write a string, a formatted string, a number and a formula to the
14 first worksheet in an Excel workbook called perl.xls:
15 use Spreadsheet::WriteExcel;
17 # Create a new Excel workbook
19 my $workbook = Spreadsheet::WriteExcel->new('perl.xls');
20 # Add a worksheet
22 $worksheet = $workbook->add_worksheet();
23 # Add and define a format
25 $format = $workbook->add_format(); # Add a format
26 $format->set_bold();
27 $format->set_color('red');
28 $format->set_align('center');
29 # Write a formatted and unformatted string, row and column notation.
31 $col = $row = 0;
32 $worksheet->write($row, $col, 'Hi Excel!', $format);
33 $worksheet->write(1, $col, 'Hi Excel!');
34 # Write a number and a formula using A1 notation
36 $worksheet->write('A3', 1.2345);
37 $worksheet->write('A4', '=SIN(PI()/4)');
38
40 The Spreadsheet::WriteExcel Perl module can be used to create a cross-
41 platform Excel binary file. Multiple worksheets can be added to a
42 workbook and formatting can be applied to cells. Text, numbers,
43 formulas, hyperlinks, images and charts can be written to the cells.
44 The file produced by this module is compatible with Excel 97, 2000,
46 2002, 2003 and 2007.
47 The module will work on the majority of Windows, UNIX and Mac
49 platforms. Generated files are also compatible with the Linux/UNIX
50 spreadsheet applications Gnumeric and OpenOffice.org.
51 This module cannot be used to write to an existing Excel file (See
53 "MODIFYING AND REWRITING EXCEL FILES").
54 Note: This module is in maintenance only mode and in future will only
56 be updated with bug fixes. The newer, more feature rich and API
57 compatible Excel::Writer::XLSX module is recommended instead. See,
58 "Migrating to Excel::Writer::XLSX".
59
61 Spreadsheet::WriteExcel tries to provide an interface to as many of
62 Excel's features as possible. As a result there is a lot of
63 documentation to accompany the interface and it can be difficult at
64 first glance to see what it important and what is not. So for those of
65 you who prefer to assemble Ikea furniture first and then read the
66 instructions, here are three easy steps:
67 1. Create a new Excel workbook (i.e. file) using "new()".
69 2. Add a worksheet to the new workbook using "add_worksheet()".
71 3. Write to the worksheet using "write()".
73 Like this:
75 use Spreadsheet::WriteExcel; # Step 0
77 my $workbook = Spreadsheet::WriteExcel->new('perl.xls'); # Step 1
79 $worksheet = $workbook->add_worksheet(); # Step 2
80 $worksheet->write('A1', 'Hi Excel!'); # Step 3
81 This will create an Excel file called "perl.xls" with a single
83 worksheet and the text 'Hi Excel!' in the relevant cell. And that's it.
84 Okay, so there is actually a zeroth step as well, but "use module" goes
85 without saying. There are also more than 80 examples that come with the
86 distribution and which you can use to get you started. See "EXAMPLES".
87 Those of you who read the instructions first and assemble the furniture
89 afterwards will know how to proceed. ;-)
90
92 The Spreadsheet::WriteExcel module provides an object oriented
93 interface to a new Excel workbook. The following methods are available
94 through a new workbook.
95 new()
97 add_worksheet()
98 add_format()
99 add_chart()
100 add_chart_ext()
101 close()
102 compatibility_mode()
103 set_properties()
104 define_name()
105 set_tempdir()
106 set_custom_color()
107 sheets()
108 set_1904()
109 set_codepage()
110 If you are unfamiliar with object oriented interfaces or the way that
112 they are implemented in Perl have a look at "perlobj" and "perltoot" in
113 the main Perl documentation.
114 new()
116 A new Excel workbook is created using the "new()" constructor which
117 accepts either a filename or a filehandle as a parameter. The following
118 example creates a new Excel file based on a filename:
119 my $workbook = Spreadsheet::WriteExcel->new('filename.xls');
121 my $worksheet = $workbook->add_worksheet();
122 $worksheet->write(0, 0, 'Hi Excel!');
123 Here are some other examples of using "new()" with filenames:
125 my $workbook1 = Spreadsheet::WriteExcel->new($filename);
127 my $workbook2 = Spreadsheet::WriteExcel->new('/tmp/filename.xls');
128 my $workbook3 = Spreadsheet::WriteExcel->new("c:\\tmp\\filename.xls");
129 my $workbook4 = Spreadsheet::WriteExcel->new('c:\tmp\filename.xls');
130 The last two examples demonstrates how to create a file on DOS or
132 Windows where it is necessary to either escape the directory separator
133 "\" or to use single quotes to ensure that it isn't interpolated. For
134 more information see "perlfaq5: Why can't I use "C:\temp\foo" in DOS
135 paths?".
136 The "new()" constructor returns a Spreadsheet::WriteExcel object that
138 you can use to add worksheets and store data. It should be noted that
139 although "my" is not specifically required it defines the scope of the
140 new workbook variable and, in the majority of cases, ensures that the
141 workbook is closed properly without explicitly calling the "close()"
142 method.
143 If the file cannot be created, due to file permissions or some other
145 reason, "new" will return "undef". Therefore, it is good practice to
146 check the return value of "new" before proceeding. As usual the Perl
147 variable $! will be set if there is a file creation error. You will
148 also see one of the warning messages detailed in "DIAGNOSTICS":
149 my $workbook = Spreadsheet::WriteExcel->new('protected.xls');
151 die "Problems creating new Excel file: $!" unless defined $workbook;
152 You can also pass a valid filehandle to the "new()" constructor. For
154 example in a CGI program you could do something like this:
155 binmode(STDOUT);
157 my $workbook = Spreadsheet::WriteExcel->new(\*STDOUT);
158 The requirement for "binmode()" is explained below.
160 See also, the "cgi.pl" program in the "examples" directory of the
162 distro.
163 However, this special case will not work in "mod_perl" programs where
165 you will have to do something like the following:
166 # mod_perl 1
168 ...
169 tie *XLS, 'Apache';
170 binmode(XLS);
171 my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
172 ...
173 # mod_perl 2
175 ...
176 tie *XLS => $r; # Tie to the Apache::RequestRec object
177 binmode(*XLS);
178 my $workbook = Spreadsheet::WriteExcel->new(\*XLS);
179 ...
180 See also, the "mod_perl1.pl" and "mod_perl2.pl" programs in the
182 "examples" directory of the distro.
183 Filehandles can also be useful if you want to stream an Excel file over
185 a socket or if you want to store an Excel file in a scalar.
186 For example here is a way to write an Excel file to a scalar with "perl
188 5.8":
189 #!/usr/bin/perl -w
191 use strict;
193 use Spreadsheet::WriteExcel;
194 # Requires perl 5.8 or later
196 open my $fh, '>', \my $str or die "Failed to open filehandle: $!";
197 my $workbook = Spreadsheet::WriteExcel->new($fh);
199 my $worksheet = $workbook->add_worksheet();
200 $worksheet->write(0, 0, 'Hi Excel!');
202 $workbook->close();
204 # The Excel file in now in $str. Remember to binmode() the output
206 # filehandle before printing it.
207 binmode STDOUT;
208 print $str;
209 See also the "write_to_scalar.pl" and "filehandle.pl" programs in the
211 "examples" directory of the distro.
212 Note about the requirement for "binmode()". An Excel file is comprised
214 of binary data. Therefore, if you are using a filehandle you should
215 ensure that you "binmode()" it prior to passing it to "new()".You
216 should do this regardless of whether you are on a Windows platform or
217 not. This applies especially to users of perl 5.8 on systems where
218 "UTF-8" is likely to be in operation such as RedHat Linux 9. If your
219 program, either intentionally or not, writes "UTF-8" data to a
220 filehandle that is passed to "new()" it will corrupt the Excel file
221 that is created.
222 You don't have to worry about "binmode()" if you are using filenames
224 instead of filehandles. Spreadsheet::WriteExcel performs the
225 "binmode()" internally when it converts the filename to a filehandle.
226 For more information about "binmode()" see "perlfunc" and "perlopentut"
227 in the main Perl documentation.
228 add_worksheet($sheetname, $utf_16_be)
230 At least one worksheet should be added to a new workbook. A worksheet
231 is used to write data into cells:
232 $worksheet1 = $workbook->add_worksheet(); # Sheet1
234 $worksheet2 = $workbook->add_worksheet('Foglio2'); # Foglio2
235 $worksheet3 = $workbook->add_worksheet('Data'); # Data
236 $worksheet4 = $workbook->add_worksheet(); # Sheet4
237 If $sheetname is not specified the default Excel convention will be
239 followed, i.e. Sheet1, Sheet2, etc. The $utf_16_be parameter is
240 optional, see below.
241 The worksheet name must be a valid Excel worksheet name, i.e. it cannot
243 contain any of the following characters, "[ ] : * ? / \" and it must be
244 less than 32 characters. In addition, you cannot use the same, case
245 insensitive, $sheetname for more than one worksheet.
246 On systems with "perl 5.8" and later the "add_worksheet()" method will
248 also handle strings in "UTF-8" format.
249 $worksheet = $workbook->add_worksheet("\x{263a}"); # Smiley
251 On earlier Perl systems your can specify "UTF-16BE" worksheet names
253 using an additional optional parameter:
254 my $name = pack 'n', 0x263a;
256 $worksheet = $workbook->add_worksheet($name, 1); # Smiley
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 name (optional)
281 embedded (optional)
282 • "type"
284 This is a required parameter. It defines the type of chart that
286 will be created.
287 my $chart = $workbook->add_chart( type => 'line' );
289 The available types are:
291 area
293 bar
294 column
295 line
296 pie
297 scatter
298 stock
299 • "name"
301 Set the name for the chart sheet. The name property is optional and
303 if it isn't supplied will default to "Chart1 .. n". The name must
304 be a valid Excel worksheet name. See "add_worksheet()" for more
305 details on valid sheet names. The "name" property can be omitted
306 for embedded charts.
307 my $chart = $workbook->add_chart( type => 'line', name => 'Results Chart' );
309 • "embedded"
311 Specifies that the Chart object will be inserted in a worksheet via
313 the "insert_chart()" Worksheet method. It is an error to try insert
314 a Chart that doesn't have this flag set.
315 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
317 # Configure the chart.
319 ...
320 # Insert the chart into the a worksheet.
322 $worksheet->insert_chart( 'E2', $chart );
323 See Spreadsheet::WriteExcel::Chart for details on how to configure the
325 chart object once it is created. See also the "chart_*.pl" programs in
326 the examples directory of the distro.
327 add_chart_ext($chart_data, $chartname)
329 This method is use to include externally generated charts in a
330 Spreadsheet::WriteExcel file.
331 my $chart = $workbook->add_chart_ext('chart01.bin', 'Chart1');
333 This feature is semi-deprecated in favour of the "native" charts
335 created using "add_chart()". Read "external_charts.txt" (or ".pod") in
336 the external_charts directory of the distro for a full explanation.
337 close()
339 In general your Excel file will be closed automatically when your
340 program ends or when the Workbook object goes out of scope, however the
341 "close()" method can be used to explicitly close an Excel file.
342 $workbook->close();
344 An explicit "close()" is required if the file must be closed prior to
346 performing some external action on it such as copying it, reading its
347 size or attaching it to an email.
348 In addition, "close()" may be required to prevent perl's garbage
350 collector from disposing of the Workbook, Worksheet and Format objects
351 in the wrong order. Situations where this can occur are:
352 • If "my()" was not used to declare the scope of a workbook variable
354 created using "new()".
355 • If the "new()", "add_worksheet()" or "add_format()" methods are
357 called in subroutines.
358 The reason for this is that Spreadsheet::WriteExcel relies on Perl's
360 "DESTROY" mechanism to trigger destructor methods in a specific
361 sequence. This may not happen in cases where the Workbook, Worksheet
362 and Format variables are not lexically scoped or where they have
363 different lexical scopes.
364 In general, if you create a file with a size of 0 bytes or you fail to
366 create a file you need to call "close()".
367 The return value of "close()" is the same as that returned by perl when
369 it closes the file created by "new()". This allows you to handle error
370 conditions in the usual way:
371 $workbook->close() or die "Error closing file: $!";
373 compatibility_mode()
375 This method is used to improve compatibility with third party
376 applications that read Excel files.
377 $workbook->compatibility_mode();
379 An Excel file is comprised of binary records that describe properties
381 of a spreadsheet. Excel is reasonably liberal about this and, outside
382 of a core subset, it doesn't require every possible record to be
383 present when it reads a file. This is also true of Gnumeric and
384 OpenOffice.Org Calc.
385 Spreadsheet::WriteExcel takes advantage of this fact to omit some
387 records in order to minimise the amount of data stored in memory and to
388 simplify and speed up the writing of files. However, some third party
389 applications that read Excel files often expect certain records to be
390 present. In "compatibility mode" Spreadsheet::WriteExcel writes these
391 records and tries to be as close to an Excel generated file as
392 possible.
393 Applications that require "compatibility_mode()" are Apache POI, Apple
395 Numbers, and Quickoffice on Nokia, Palm and other devices. You should
396 also use "compatibility_mode()" if your Excel file will be used as an
397 external data source by another Excel file.
398 If you encounter other situations that require "compatibility_mode()",
400 please let me know.
401 It should be noted that "compatibility_mode()" requires additional data
403 to be stored in memory and additional processing. This incurs a memory
404 and speed penalty and may not be suitable for very large files (>20MB).
405 You must call "compatibility_mode()" before calling "add_worksheet()".
407 set_properties()
409 The "set_properties" method can be used to set the document properties
410 of the Excel file created by "Spreadsheet::WriteExcel". These
411 properties are visible when you use the "File->Properties" menu option
412 in Excel and are also available to external applications that read or
413 index windows files.
414 The properties should be passed as a hash of values as follows:
416 $workbook->set_properties(
418 title => 'This is an example spreadsheet',
419 author => 'John McNamara',
420 comments => 'Created with Perl and Spreadsheet::WriteExcel',
421 );
422 The properties that can be set are:
424 title
426 subject
427 author
428 manager
429 company
430 category
431 keywords
432 comments
433 User defined properties are not supported due to effort required.
435 In perl 5.8+ you can also pass UTF-8 strings as properties. See
437 "UNICODE IN EXCEL".
438 my $smiley = chr 0x263A;
440 $workbook->set_properties(
442 subject => "Happy now? $smiley",
443 );
444 With older versions of perl you can use a module to convert a non-ASCII
446 string to a binary representation of UTF-8 and then pass an additional
447 "utf8" flag to "set_properties()":
448 my $smiley = pack 'H*', 'E298BA';
450 $workbook->set_properties(
452 subject => "Happy now? $smiley",
453 utf8 => 1,
454 );
455 Usually Spreadsheet::WriteExcel allows you to use UTF-16 with pre 5.8
457 versions of perl. However, document properties don't support UTF-16 for
458 these type of strings.
459 In order to promote the usefulness of Perl and the
461 Spreadsheet::WriteExcel module consider adding a comment such as the
462 following when using document properties:
463 $workbook->set_properties(
465 ...,
466 comments => 'Created with Perl and Spreadsheet::WriteExcel',
467 ...,
468 );
469 This feature requires that the "OLE::Storage_Lite" module is installed
471 (which is usually the case for a standard Spreadsheet::WriteExcel
472 installation). However, this also means that the resulting OLE document
473 may possibly be buggy for files less than 7MB since it hasn't been as
474 rigorously tested in that domain. As a result of this "set_properties"
475 is currently incompatible with Gnumeric for files less than 7MB. This
476 is being investigated. If you encounter any problems with this features
477 let me know.
478 For convenience it is possible to pass either a hash or hash ref of
480 arguments to this method.
481 See also the "properties.pl" program in the examples directory of the
483 distro.
484 define_name()
486 This method is used to defined a name that can be used to represent a
487 value, a single cell or a range of cells in a workbook.
488 $workbook->define_name('Exchange_rate', '=0.96');
490 $workbook->define_name('Sales', '=Sheet1!$G$1:$H$10');
491 $workbook->define_name('Sheet2!Sales', '=Sheet2!$G$1:$G$10');
492 See the defined_name.pl program in the examples dir of the distro.
494 Note: This currently a beta feature. More documentation and examples
496 will be added.
497 set_tempdir()
499 For speed and efficiency "Spreadsheet::WriteExcel" stores worksheet
500 data in temporary files prior to assembling the final workbook.
501 If Spreadsheet::WriteExcel is unable to create these temporary files it
503 will store the required data in memory. This can be slow for large
504 files.
505 The problem occurs mainly with IIS on Windows although it could
507 feasibly occur on Unix systems as well. The problem generally occurs
508 because the default temp file directory is defined as "C:/" or some
509 other directory that IIS doesn't provide write access to.
510 To check if this might be a problem on a particular system you can run
512 a simple test program with "-w" or "use warnings". This will generate a
513 warning if the module cannot create the required temporary files:
514 #!/usr/bin/perl -w
516 use Spreadsheet::WriteExcel;
518 my $workbook = Spreadsheet::WriteExcel->new('test.xls');
520 my $worksheet = $workbook->add_worksheet();
521 To avoid this problem the "set_tempdir()" method can be used to specify
523 a directory that is accessible for the creation of temporary files.
524 The "File::Temp" module is used to create the temporary files.
526 File::Temp uses "File::Spec" to determine an appropriate location for
527 these files such as "/tmp" or "c:\windows\temp". You can find out which
528 directory is used on your system as follows:
529 perl -MFile::Spec -le "print File::Spec->tmpdir"
531 Even if the default temporary file directory is accessible you may wish
533 to specify an alternative location for security or maintenance reasons:
534 $workbook->set_tempdir('/tmp/writeexcel');
536 $workbook->set_tempdir('c:\windows\temp\writeexcel');
537 The directory for the temporary file must exist, "set_tempdir()" will
539 not create a new directory.
540 One disadvantage of using the "set_tempdir()" method is that on some
542 Windows systems it will limit you to approximately 800 concurrent
543 tempfiles. This means that a single program running on one of these
544 systems will be limited to creating a total of 800 workbook and
545 worksheet objects. You can run multiple, non-concurrent programs to
546 work around this if necessary.
547 set_custom_color($index, $red, $green, $blue)
549 The "set_custom_color()" method can be used to override one of the
550 built-in palette values with a more suitable colour.
551 The value for $index should be in the range 8..63, see "COLOURS IN
553 EXCEL".
554 The default named colours use the following indices:
556 8 => black
558 9 => white
559 10 => red
560 11 => lime
561 12 => blue
562 13 => yellow
563 14 => magenta
564 15 => cyan
565 16 => brown
566 17 => green
567 18 => navy
568 20 => purple
569 22 => silver
570 23 => gray
571 33 => pink
572 53 => orange
573 A new colour is set using its RGB (red green blue) components. The
575 $red, $green and $blue values must be in the range 0..255. You can
576 determine the required values in Excel using the
577 "Tools->Options->Colors->Modify" dialog.
578 The "set_custom_color()" workbook method can also be used with a HTML
580 style "#rrggbb" hex value:
581 $workbook->set_custom_color(40, 255, 102, 0 ); # Orange
583 $workbook->set_custom_color(40, 0xFF, 0x66, 0x00); # Same thing
584 $workbook->set_custom_color(40, '#FF6600' ); # Same thing
585 my $font = $workbook->add_format(color => 40); # Use the modified colour
587 The return value from "set_custom_color()" is the index of the colour
589 that was changed:
590 my $ferrari = $workbook->set_custom_color(40, 216, 12, 12);
592 my $format = $workbook->add_format(
594 bg_color => $ferrari,
595 pattern => 1,
596 border => 1
597 );
598 sheets(0, 1, ...)
600 The "sheets()" method returns a list, or a sliced list, of the
601 worksheets in a workbook.
602 If no arguments are passed the method returns a list of all the
604 worksheets in the workbook. This is useful if you want to repeat an
605 operation on each worksheet:
606 foreach $worksheet ($workbook->sheets()) {
608 print $worksheet->get_name();
609 }
610 You can also specify a slice list to return one or more worksheet
612 objects:
613 $worksheet = $workbook->sheets(0);
615 $worksheet->write('A1', 'Hello');
616 Or since return value from "sheets()" is a reference to a worksheet
618 object you can write the above example as:
619 $workbook->sheets(0)->write('A1', 'Hello');
621 The following example returns the first and last worksheet in a
623 workbook:
624 foreach $worksheet ($workbook->sheets(0, -1)) {
626 # Do something
627 }
628 Array slices are explained in the perldata manpage.
630 set_1904()
632 Excel stores dates as real numbers where the integer part stores the
633 number of days since the epoch and the fractional part stores the
634 percentage of the day. The epoch can be either 1900 or 1904. Excel for
635 Windows uses 1900 and Excel for Macintosh uses 1904. However, Excel on
636 either platform will convert automatically between one system and the
637 other.
638 Spreadsheet::WriteExcel stores dates in the 1900 format by default. If
640 you wish to change this you can call the "set_1904()" workbook method.
641 You can query the current value by calling the "get_1904()" workbook
642 method. This returns 0 for 1900 and 1 for 1904.
643 See also "DATES AND TIME IN EXCEL" for more information about working
645 with Excel's date system.
646 In general you probably won't need to use "set_1904()".
648 set_codepage($codepage)
650 The default code page or character set used by Spreadsheet::WriteExcel
651 is ANSI. This is also the default used by Excel for Windows.
652 Occasionally however it may be necessary to change the code page via
653 the "set_codepage()" method.
654 Changing the code page may be required if your are using
656 Spreadsheet::WriteExcel on the Macintosh and you are using characters
657 outside the ASCII 128 character set:
658 $workbook->set_codepage(1); # ANSI, MS Windows
660 $workbook->set_codepage(2); # Apple Macintosh
661 The "set_codepage()" method is rarely required.
663
665 A new worksheet is created by calling the "add_worksheet()" method from
666 a workbook object:
667 $worksheet1 = $workbook->add_worksheet();
669 $worksheet2 = $workbook->add_worksheet();
670 The following methods are available through a new worksheet:
672 write()
674 write_number()
675 write_string()
676 write_utf16be_string()
677 write_utf16le_string()
678 keep_leading_zeros()
679 write_blank()
680 write_row()
681 write_col()
682 write_date_time()
683 write_url()
684 write_url_range()
685 write_formula()
686 store_formula()
687 repeat_formula()
688 write_comment()
689 show_comments()
690 add_write_handler()
691 insert_image()
692 insert_chart()
693 data_validation()
694 get_name()
695 activate()
696 select()
697 hide()
698 set_first_sheet()
699 protect()
700 set_selection()
701 set_row()
702 set_column()
703 outline_settings()
704 freeze_panes()
705 split_panes()
706 merge_range()
707 set_zoom()
708 right_to_left()
709 hide_zero()
710 set_tab_color()
711 autofilter()
712 Cell notation
714 Spreadsheet::WriteExcel supports two forms of notation to designate the
715 position of cells: Row-column notation and A1 notation.
716 Row-column notation uses a zero based index for both row and column
718 while A1 notation uses the standard Excel alphanumeric sequence of
719 column letter and 1-based row. For example:
720 (0, 0) # The top left cell in row-column notation.
722 ('A1') # The top left cell in A1 notation.
723 (1999, 29) # Row-column notation.
725 ('AD2000') # The same cell in A1 notation.
726 Row-column notation is useful if you are referring to cells
728 programmatically:
729 for my $i (0 .. 9) {
731 $worksheet->write($i, 0, 'Hello'); # Cells A1 to A10
732 }
733 A1 notation is useful for setting up a worksheet manually and for
735 working with formulas:
736 $worksheet->write('H1', 200);
738 $worksheet->write('H2', '=H1+1');
739 In formulas and applicable methods you can also use the "A:A" column
741 notation:
742 $worksheet->write('A1', '=SUM(B:B)');
744 The "Spreadsheet::WriteExcel::Utility" module that is included in the
746 distro contains helper functions for dealing with A1 notation, for
747 example:
748 use Spreadsheet::WriteExcel::Utility;
750 ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
752 $str = xl_rowcol_to_cell(1, 2); # C2
753 For simplicity, the parameter lists for the worksheet method calls in
755 the following sections are given in terms of row-column notation. In
756 all cases it is also possible to use A1 notation.
757 Note: in Excel it is also possible to use a R1C1 notation. This is not
759 supported by Spreadsheet::WriteExcel.
760 write($row, $column, $token, $format)
762 Excel makes a distinction between data types such as strings, numbers,
763 blanks, formulas and hyperlinks. To simplify the process of writing
764 data the "write()" method acts as a general alias for several more
765 specific methods:
766 write_string()
768 write_number()
769 write_blank()
770 write_formula()
771 write_url()
772 write_row()
773 write_col()
774 The general rule is that if the data looks like a something then a
776 something is written. Here are some examples in both row-column and A1
777 notation:
778 # Same as:
780 $worksheet->write(0, 0, 'Hello' ); # write_string()
781 $worksheet->write(1, 0, 'One' ); # write_string()
782 $worksheet->write(2, 0, 2 ); # write_number()
783 $worksheet->write(3, 0, 3.00001 ); # write_number()
784 $worksheet->write(4, 0, "" ); # write_blank()
785 $worksheet->write(5, 0, '' ); # write_blank()
786 $worksheet->write(6, 0, undef ); # write_blank()
787 $worksheet->write(7, 0 ); # write_blank()
788 $worksheet->write(8, 0, 'http://www.perl.com/'); # write_url()
789 $worksheet->write('A9', 'ftp://ftp.cpan.org/' ); # write_url()
790 $worksheet->write('A10', 'internal:Sheet1!A1' ); # write_url()
791 $worksheet->write('A11', 'external:c:\foo.xls' ); # write_url()
792 $worksheet->write('A12', '=A3 + 3*A4' ); # write_formula()
793 $worksheet->write('A13', '=SIN(PI()/4)' ); # write_formula()
794 $worksheet->write('A14', \@array ); # write_row()
795 $worksheet->write('A15', [\@array] ); # write_col()
796 # And if the keep_leading_zeros property is set:
798 $worksheet->write('A16', '2' ); # write_number()
799 $worksheet->write('A17', '02' ); # write_string()
800 $worksheet->write('A18', '00002' ); # write_string()
801 The "looks like" rule is defined by regular expressions:
803 "write_number()" if $token is a number based on the following regex:
805 "$token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/".
806 "write_string()" if "keep_leading_zeros()" is set and $token is an
808 integer with leading zeros based on the following regex: "$token =~
809 /^0\d+$/".
810 "write_blank()" if $token is undef or a blank string: "undef", "" or
812 ''.
813 "write_url()" if $token is a http, https, ftp or mailto URL based on
815 the following regexes: "$token =~ m|^[fh]tt?ps?://|" or "$token =~
816 m|^mailto:|".
817 "write_url()" if $token is an internal or external sheet reference
819 based on the following regex: "$token =~ m[^(in|ex)ternal:]".
820 "write_formula()" if the first character of $token is "=".
822 "write_row()" if $token is an array ref.
824 "write_col()" if $token is an array ref of array refs.
826 "write_string()" if none of the previous conditions apply.
828 The $format parameter is optional. It should be a valid Format object,
830 see "CELL FORMATTING":
831 my $format = $workbook->add_format();
833 $format->set_bold();
834 $format->set_color('red');
835 $format->set_align('center');
836 $worksheet->write(4, 0, 'Hello', $format); # Formatted string
838 The write() method will ignore empty strings or "undef" tokens unless a
840 format is also supplied. As such you needn't worry about special
841 handling for empty or "undef" values in your data. See also the
842 "write_blank()" method.
843 One problem with the "write()" method is that occasionally data looks
845 like a number but you don't want it treated as a number. For example,
846 zip codes or ID numbers often start with a leading zero. If you write
847 this data as a number then the leading zero(s) will be stripped. You
848 can change this default behaviour by using the "keep_leading_zeros()"
849 method. While this property is in place any integers with leading zeros
850 will be treated as strings and the zeros will be preserved. See the
851 "keep_leading_zeros()" section for a full discussion of this issue.
852 You can also add your own data handlers to the "write()" method using
854 "add_write_handler()".
855 On systems with "perl 5.8" and later the "write()" method will also
857 handle Unicode strings in "UTF-8" format.
858 The "write" methods return:
860 0 for success.
862 -1 for insufficient number of arguments.
863 -2 for row or column out of bounds.
864 -3 for string too long.
865 write_number($row, $column, $number, $format)
867 Write an integer or a float to the cell specified by $row and $column:
868 $worksheet->write_number(0, 0, 123456);
870 $worksheet->write_number('A2', 2.3451);
871 See the note about "Cell notation". The $format parameter is optional.
873 In general it is sufficient to use the "write()" method.
875 write_string($row, $column, $string, $format)
877 Write a string to the cell specified by $row and $column:
878 $worksheet->write_string(0, 0, 'Your text here' );
880 $worksheet->write_string('A2', 'or here' );
881 The maximum string size is 32767 characters. However the maximum string
883 segment that Excel can display in a cell is 1000. All 32767 characters
884 can be displayed in the formula bar.
885 The $format parameter is optional.
887 On systems with "perl 5.8" and later the "write()" method will also
889 handle strings in "UTF-8" format. With older perls you can also write
890 Unicode in "UTF16" format via the "write_utf16be_string()" method. See
891 also the "unicode_*.pl" programs in the examples directory of the
892 distro.
893 In general it is sufficient to use the "write()" method. However, you
895 may sometimes wish to use the "write_string()" method to write data
896 that looks like a number but that you don't want treated as a number.
897 For example, zip codes or phone numbers:
898 # Write as a plain string
900 $worksheet->write_string('A1', '01209');
901 However, if the user edits this string Excel may convert it back to a
903 number. To get around this you can use the Excel text format "@":
904 # Format as a string. Doesn't change to a number when edited
906 my $format1 = $workbook->add_format(num_format => '@');
907 $worksheet->write_string('A2', '01209', $format1);
908 See also the note about "Cell notation".
910 write_utf16be_string($row, $column, $string, $format)
912 This method is used to write "UTF-16BE" strings to a cell in Excel. It
913 is functionally the same as the "write_string()" method except that the
914 string should be in "UTF-16BE" Unicode format. It is generally easier,
915 when using Spreadsheet::WriteExcel, to write unicode strings in "UTF-8"
916 format, see "UNICODE IN EXCEL". The "write_utf16be_string()" method is
917 mainly of use in versions of perl prior to 5.8.
918 The following is a simple example showing how to write some Unicode
920 strings in "UTF-16BE" format:
921 #!/usr/bin/perl -w
923 use strict;
926 use Spreadsheet::WriteExcel;
927 use Unicode::Map();
928 my $workbook = Spreadsheet::WriteExcel->new('utf_16_be.xls');
930 my $worksheet = $workbook->add_worksheet();
931 # Increase the column width for clarity
933 $worksheet->set_column('A:A', 25);
934 # Write a Unicode character
937 #
938 my $smiley = pack 'n', 0x263a;
939 # Increase the font size for legibility.
941 my $big_font = $workbook->add_format(size => 72);
942 $worksheet->write_utf16be_string('A3', $smiley, $big_font);
944 # Write a phrase in Cyrillic using a hex-encoded string
948 #
949 my $str = pack 'H*', '042d0442043e0020044404400430043704300020043d' .
950 '043000200440044304410441043a043e043c0021';
951 $worksheet->write_utf16be_string('A5', $str);
953 # Map a string to UTF-16BE using an external module.
957 #
958 my $map = Unicode::Map->new('ISO-8859-1');
959 my $utf16 = $map->to_unicode('Hello world!');
960 $worksheet->write_utf16be_string('A7', $utf16);
962 You can convert ASCII encodings to the required "UTF-16BE" format using
964 one of the many Unicode modules on CPAN. For example "Unicode::Map" and
965 "Unicode::String":
966 <http://search.cpan.org/author/MSCHWARTZ/Unicode-Map/Map.pm> and
967 <http://search.cpan.org/author/GAAS/Unicode-String/String.pm>.
968 For a full list of the Perl Unicode modules see:
970 <http://search.cpan.org/search?query=unicode&mode=all>.
971 "UTF-16BE" is the format most often returned by "Perl" modules that
973 generate "UTF-16". To write "UTF-16" strings in little-endian format
974 use the "write_utf16be_string_le()" method below.
975 The "write_utf16be_string()" method was previously called
977 "write_unicode()". That, overly general, name is still supported but
978 deprecated.
979 See also the "unicode_*.pl" programs in the examples directory of the
981 distro.
982 write_utf16le_string($row, $column, $string, $format)
984 This method is the same as "write_utf16be()" except that the string
985 should be 16-bit characters in little-endian format. This is generally
986 referred to as "UTF-16LE". See "UNICODE IN EXCEL".
987 "UTF-16" data can be changed from little-endian to big-endian format
989 (and vice-versa) as follows:
990 $utf16be = pack 'n*', unpack 'v*', $utf16le;
992 keep_leading_zeros()
994 This method changes the default handling of integers with leading zeros
995 when using the "write()" method.
996 The "write()" method uses regular expressions to determine what type of
998 data to write to an Excel worksheet. If the data looks like a number it
999 writes a number using "write_number()". One problem with this approach
1000 is that occasionally data looks like a number but you don't want it
1001 treated as a number.
1002 Zip codes and ID numbers, for example, often start with a leading zero.
1004 If you write this data as a number then the leading zero(s) will be
1005 stripped. This is the also the default behaviour when you enter data
1006 manually in Excel.
1007 To get around this you can use one of three options. Write a formatted
1009 number, write the number as a string or use the "keep_leading_zeros()"
1010 method to change the default behaviour of "write()":
1011 # Implicitly write a number, the leading zero is removed: 1209
1013 $worksheet->write('A1', '01209');
1014 # Write a zero padded number using a format: 01209
1016 my $format1 = $workbook->add_format(num_format => '00000');
1017 $worksheet->write('A2', '01209', $format1);
1018 # Write explicitly as a string: 01209
1020 $worksheet->write_string('A3', '01209');
1021 # Write implicitly as a string: 01209
1023 $worksheet->keep_leading_zeros();
1024 $worksheet->write('A4', '01209');
1025 The above code would generate a worksheet that looked like the
1027 following:
1028 -----------------------------------------------------------
1030 | | A | B | C | D | ...
1031 -----------------------------------------------------------
1032 | 1 | 1209 | | | | ...
1033 | 2 | 01209 | | | | ...
1034 | 3 | 01209 | | | | ...
1035 | 4 | 01209 | | | | ...
1036 The examples are on different sides of the cells due to the fact that
1038 Excel displays strings with a left justification and numbers with a
1039 right justification by default. You can change this by using a format
1040 to justify the data, see "CELL FORMATTING".
1041 It should be noted that if the user edits the data in examples "A3" and
1043 "A4" the strings will revert back to numbers. Again this is Excel's
1044 default behaviour. To avoid this you can use the text format "@":
1045 # Format as a string (01209)
1047 my $format2 = $workbook->add_format(num_format => '@');
1048 $worksheet->write_string('A5', '01209', $format2);
1049 The "keep_leading_zeros()" property is off by default. The
1051 "keep_leading_zeros()" method takes 0 or 1 as an argument. It defaults
1052 to 1 if an argument isn't specified:
1053 $worksheet->keep_leading_zeros(); # Set on
1055 $worksheet->keep_leading_zeros(1); # Set on
1056 $worksheet->keep_leading_zeros(0); # Set off
1057 See also the "add_write_handler()" method.
1059 write_blank($row, $column, $format)
1061 Write a blank cell specified by $row and $column:
1062 $worksheet->write_blank(0, 0, $format);
1064 This method is used to add formatting to a cell which doesn't contain a
1066 string or number value.
1067 Excel differentiates between an "Empty" cell and a "Blank" cell. An
1069 "Empty" cell is a cell which doesn't contain data whilst a "Blank" cell
1070 is a cell which doesn't contain data but does contain formatting. Excel
1071 stores "Blank" cells but ignores "Empty" cells.
1072 As such, if you write an empty cell without formatting it is ignored:
1074 $worksheet->write('A1', undef, $format); # write_blank()
1076 $worksheet->write('A2', undef ); # Ignored
1077 This seemingly uninteresting fact means that you can write arrays of
1079 data without special treatment for undef or empty string values.
1080 See the note about "Cell notation".
1082 write_row($row, $column, $array_ref, $format)
1084 The "write_row()" method can be used to write a 1D or 2D array of data
1085 in one go. This is useful for converting the results of a database
1086 query into an Excel worksheet. You must pass a reference to the array
1087 of data rather than the array itself. The "write()" method is then
1088 called for each element of the data. For example:
1089 @array = ('awk', 'gawk', 'mawk');
1091 $array_ref = \@array;
1092 $worksheet->write_row(0, 0, $array_ref);
1094 # The above example is equivalent to:
1096 $worksheet->write(0, 0, $array[0]);
1097 $worksheet->write(0, 1, $array[1]);
1098 $worksheet->write(0, 2, $array[2]);
1099 Note: For convenience the "write()" method behaves in the same way as
1101 "write_row()" if it is passed an array reference. Therefore the
1102 following two method calls are equivalent:
1103 $worksheet->write_row('A1', $array_ref); # Write a row of data
1105 $worksheet->write( 'A1', $array_ref); # Same thing
1106 As with all of the write methods the $format parameter is optional. If
1108 a format is specified it is applied to all the elements of the data
1109 array.
1110 Array references within the data will be treated as columns. This
1112 allows you to write 2D arrays of data in one go. For example:
1113 @eec = (
1115 ['maggie', 'milly', 'molly', 'may' ],
1116 [13, 14, 15, 16 ],
1117 ['shell', 'star', 'crab', 'stone']
1118 );
1119 $worksheet->write_row('A1', \@eec);
1121 Would produce a worksheet as follows:
1123 -----------------------------------------------------------
1125 | | A | B | C | D | E | ...
1126 -----------------------------------------------------------
1127 | 1 | maggie | 13 | shell | ... | ... | ...
1128 | 2 | milly | 14 | star | ... | ... | ...
1129 | 3 | molly | 15 | crab | ... | ... | ...
1130 | 4 | may | 16 | stone | ... | ... | ...
1131 | 5 | ... | ... | ... | ... | ... | ...
1132 | 6 | ... | ... | ... | ... | ... | ...
1133 To write the data in a row-column order refer to the "write_col()"
1135 method below.
1136 Any "undef" values in the data will be ignored unless a format is
1138 applied to the data, in which case a formatted blank cell will be
1139 written. In either case the appropriate row or column value will still
1140 be incremented.
1141 To find out more about array references refer to "perlref" and
1143 "perlreftut" in the main Perl documentation. To find out more about 2D
1144 arrays or "lists of lists" refer to "perllol".
1145 The "write_row()" method returns the first error encountered when
1147 writing the elements of the data or zero if no errors were encountered.
1148 See the return values described for the "write()" method above.
1149 See also the "write_arrays.pl" program in the "examples" directory of
1151 the distro.
1152 The "write_row()" method allows the following idiomatic conversion of a
1154 text file to an Excel file:
1155 #!/usr/bin/perl -w
1157 use strict;
1159 use Spreadsheet::WriteExcel;
1160 my $workbook = Spreadsheet::WriteExcel->new('file.xls');
1162 my $worksheet = $workbook->add_worksheet();
1163 open INPUT, 'file.txt' or die "Couldn't open file: $!";
1165 $worksheet->write($.-1, 0, [split]) while <INPUT>;
1167 write_col($row, $column, $array_ref, $format)
1169 The "write_col()" method can be used to write a 1D or 2D array of data
1170 in one go. This is useful for converting the results of a database
1171 query into an Excel worksheet. You must pass a reference to the array
1172 of data rather than the array itself. The "write()" method is then
1173 called for each element of the data. For example:
1174 @array = ('awk', 'gawk', 'mawk');
1176 $array_ref = \@array;
1177 $worksheet->write_col(0, 0, $array_ref);
1179 # The above example is equivalent to:
1181 $worksheet->write(0, 0, $array[0]);
1182 $worksheet->write(1, 0, $array[1]);
1183 $worksheet->write(2, 0, $array[2]);
1184 As with all of the write methods the $format parameter is optional. If
1186 a format is specified it is applied to all the elements of the data
1187 array.
1188 Array references within the data will be treated as rows. This allows
1190 you to write 2D arrays of data in one go. For example:
1191 @eec = (
1193 ['maggie', 'milly', 'molly', 'may' ],
1194 [13, 14, 15, 16 ],
1195 ['shell', 'star', 'crab', 'stone']
1196 );
1197 $worksheet->write_col('A1', \@eec);
1199 Would produce a worksheet as follows:
1201 -----------------------------------------------------------
1203 | | A | B | C | D | E | ...
1204 -----------------------------------------------------------
1205 | 1 | maggie | milly | molly | may | ... | ...
1206 | 2 | 13 | 14 | 15 | 16 | ... | ...
1207 | 3 | shell | star | crab | stone | ... | ...
1208 | 4 | ... | ... | ... | ... | ... | ...
1209 | 5 | ... | ... | ... | ... | ... | ...
1210 | 6 | ... | ... | ... | ... | ... | ...
1211 To write the data in a column-row order refer to the "write_row()"
1213 method above.
1214 Any "undef" values in the data will be ignored unless a format is
1216 applied to the data, in which case a formatted blank cell will be
1217 written. In either case the appropriate row or column value will still
1218 be incremented.
1219 As noted above the "write()" method can be used as a synonym for
1221 "write_row()" and "write_row()" handles nested array refs as columns.
1222 Therefore, the following two method calls are equivalent although the
1223 more explicit call to "write_col()" would be preferable for
1224 maintainability:
1225 $worksheet->write_col('A1', $array_ref ); # Write a column of data
1227 $worksheet->write( 'A1', [ $array_ref ]); # Same thing
1228 To find out more about array references refer to "perlref" and
1230 "perlreftut" in the main Perl documentation. To find out more about 2D
1231 arrays or "lists of lists" refer to "perllol".
1232 The "write_col()" method returns the first error encountered when
1234 writing the elements of the data or zero if no errors were encountered.
1235 See the return values described for the "write()" method above.
1236 See also the "write_arrays.pl" program in the "examples" directory of
1238 the distro.
1239 write_date_time($row, $col, $date_string, $format)
1241 The "write_date_time()" method can be used to write a date or time to
1242 the cell specified by $row and $column:
1243 $worksheet->write_date_time('A1', '2004-05-13T23:20', $date_format);
1245 The $date_string should be in the following format:
1247 yyyy-mm-ddThh:mm:ss.sss
1249 This conforms to an ISO8601 date but it should be noted that the full
1251 range of ISO8601 formats are not supported.
1252 The following variations on the $date_string parameter are permitted:
1254 yyyy-mm-ddThh:mm:ss.sss # Standard format
1256 yyyy-mm-ddT # No time
1257 Thh:mm:ss.sss # No date
1258 yyyy-mm-ddThh:mm:ss.sssZ # Additional Z (but not time zones)
1259 yyyy-mm-ddThh:mm:ss # No fractional seconds
1260 yyyy-mm-ddThh:mm # No seconds
1261 Note that the "T" is required in all cases.
1263 A date should always have a $format, otherwise it will appear as a
1265 number, see "DATES AND TIME IN EXCEL" and "CELL FORMATTING". Here is a
1266 typical example:
1267 my $date_format = $workbook->add_format(num_format => 'mm/dd/yy');
1269 $worksheet->write_date_time('A1', '2004-05-13T23:20', $date_format);
1270 Valid dates should be in the range 1900-01-01 to 9999-12-31, for the
1272 1900 epoch and 1904-01-01 to 9999-12-31, for the 1904 epoch. As with
1273 Excel, dates outside these ranges will be written as a string.
1274 See also the date_time.pl program in the "examples" directory of the
1276 distro.
1277 write_url($row, $col, $url, $label, $format)
1279 Write a hyperlink to a URL in the cell specified by $row and $column.
1280 The hyperlink is comprised of two elements: the visible label and the
1281 invisible link. The visible label is the same as the link unless an
1282 alternative label is specified. The parameters $label and the $format
1283 are optional and their position is interchangeable.
1284 The label is written using the "write()" method. Therefore it is
1286 possible to write strings, numbers or formulas as labels.
1287 There are four web style URI's supported: "http://", "https://",
1289 "ftp://" and "mailto:":
1290 $worksheet->write_url(0, 0, 'ftp://www.perl.org/' );
1292 $worksheet->write_url(1, 0, 'http://www.perl.com/', 'Perl home' );
1293 $worksheet->write_url('A3', 'http://www.perl.com/', $format );
1294 $worksheet->write_url('A4', 'http://www.perl.com/', 'Perl', $format);
1295 $worksheet->write_url('A5', 'mailto:jmcnamara@cpan.org' );
1296 There are two local URIs supported: "internal:" and "external:". These
1298 are used for hyperlinks to internal worksheet references or external
1299 workbook and worksheet references:
1300 $worksheet->write_url('A6', 'internal:Sheet2!A1' );
1302 $worksheet->write_url('A7', 'internal:Sheet2!A1', $format );
1303 $worksheet->write_url('A8', 'internal:Sheet2!A1:B2' );
1304 $worksheet->write_url('A9', q{internal:'Sales Data'!A1} );
1305 $worksheet->write_url('A10', 'external:c:\temp\foo.xls' );
1306 $worksheet->write_url('A11', 'external:c:\temp\foo.xls#Sheet2!A1' );
1307 $worksheet->write_url('A12', 'external:..\..\..\foo.xls' );
1308 $worksheet->write_url('A13', 'external:..\..\..\foo.xls#Sheet2!A1' );
1309 $worksheet->write_url('A13', 'external:\\\\NETWORK\share\foo.xls' );
1310 All of the these URI types are recognised by the "write()" method, see
1312 above.
1313 Worksheet references are typically of the form "Sheet1!A1". You can
1315 also refer to a worksheet range using the standard Excel notation:
1316 "Sheet1!A1:B2".
1317 In external links the workbook and worksheet name must be separated by
1319 the "#" character: "external:Workbook.xls#Sheet1!A1'".
1320 You can also link to a named range in the target worksheet. For example
1322 say you have a named range called "my_name" in the workbook
1323 "c:\temp\foo.xls" you could link to it as follows:
1324 $worksheet->write_url('A14', 'external:c:\temp\foo.xls#my_name');
1326 Note, you cannot currently create named ranges with
1328 "Spreadsheet::WriteExcel".
1329 Excel requires that worksheet names containing spaces or non
1331 alphanumeric characters are single quoted as follows "'Sales Data'!A1".
1332 If you need to do this in a single quoted string then you can either
1333 escape the single quotes "\'" or use the quote operator "q{}" as
1334 described in "perlop" in the main Perl documentation.
1335 Links to network files are also supported. MS/Novell Network files
1337 normally begin with two back slashes as follows "\\NETWORK\etc". In
1338 order to generate this in a single or double quoted string you will
1339 have to escape the backslashes, '\\\\NETWORK\etc'.
1340 If you are using double quote strings then you should be careful to
1342 escape anything that looks like a metacharacter. For more information
1343 see "perlfaq5: Why can't I use "C:\temp\foo" in DOS paths?".
1344 Finally, you can avoid most of these quoting problems by using forward
1346 slashes. These are translated internally to backslashes:
1347 $worksheet->write_url('A14', "external:c:/temp/foo.xls" );
1349 $worksheet->write_url('A15', 'external://NETWORK/share/foo.xls' );
1350 See also, the note about "Cell notation".
1352 write_url_range($row1, $col1, $row2, $col2, $url, $string, $format)
1354 This method is essentially the same as the "write_url()" method
1355 described above. The main difference is that you can specify a link for
1356 a range of cells:
1357 $worksheet->write_url(0, 0, 0, 3, 'ftp://www.perl.org/' );
1359 $worksheet->write_url(1, 0, 0, 3, 'http://www.perl.com/', 'Perl home');
1360 $worksheet->write_url('A3:D3', 'internal:Sheet2!A1' );
1361 $worksheet->write_url('A4:D4', 'external:c:\temp\foo.xls' );
1362 This method is generally only required when used in conjunction with
1364 merged cells. See the "merge_range()" method and the "merge" property
1365 of a Format object, "CELL FORMATTING".
1366 There is no way to force this behaviour through the "write()" method.
1368 The parameters $string and the $format are optional and their position
1370 is interchangeable. However, they are applied only to the first cell in
1371 the range.
1372 See also, the note about "Cell notation".
1374 write_formula($row, $column, $formula, $format, $value)
1376 Write a formula or function to the cell specified by $row and $column:
1377 $worksheet->write_formula(0, 0, '=$B$3 + B4' );
1379 $worksheet->write_formula(1, 0, '=SIN(PI()/4)');
1380 $worksheet->write_formula(2, 0, '=SUM(B1:B5)' );
1381 $worksheet->write_formula('A4', '=IF(A3>1,"Yes", "No")' );
1382 $worksheet->write_formula('A5', '=AVERAGE(1, 2, 3, 4)' );
1383 $worksheet->write_formula('A6', '=DATEVALUE("1-Jan-2001")');
1384 See the note about "Cell notation". For more information about writing
1386 Excel formulas see "FORMULAS AND FUNCTIONS IN EXCEL"
1387 See also the section "Improving performance when working with formulas"
1389 and the "store_formula()" and "repeat_formula()" methods.
1390 If required, it is also possible to specify the calculated value of the
1392 formula. This is occasionally necessary when working with non-Excel
1393 applications that don't calculate the value of the formula. The
1394 calculated $value is added at the end of the argument list:
1395 $worksheet->write('A1', '=2+2', $format, 4);
1397 However, this probably isn't something that will ever need to do. If
1399 you do use this feature then do so with care.
1400 store_formula($formula)
1402 The "store_formula()" method is used in conjunction with
1403 "repeat_formula()" to speed up the generation of repeated formulas. See
1404 "Improving performance when working with formulas" in "FORMULAS AND
1405 FUNCTIONS IN EXCEL".
1406 The "store_formula()" method pre-parses a textual representation of a
1408 formula and stores it for use at a later stage by the
1409 "repeat_formula()" method.
1410 "store_formula()" carries the same speed penalty as "write_formula()".
1412 However, in practice it will be used less frequently.
1413 The return value of this method is a scalar that can be thought of as a
1415 reference to a formula.
1416 my $sin = $worksheet->store_formula('=SIN(A1)');
1418 my $cos = $worksheet->store_formula('=COS(A1)');
1419 $worksheet->repeat_formula('B1', $sin, $format, 'A1', 'A2');
1421 $worksheet->repeat_formula('C1', $cos, $format, 'A1', 'A2');
1422 Although "store_formula()" is a worksheet method the return value can
1424 be used in any worksheet:
1425 my $now = $worksheet->store_formula('=NOW()');
1427 $worksheet1->repeat_formula('B1', $now);
1429 $worksheet2->repeat_formula('B1', $now);
1430 $worksheet3->repeat_formula('B1', $now);
1431 repeat_formula($row, $col, $formula, $format, ($pattern => $replace, ...))
1433 The "repeat_formula()" method is used in conjunction with
1434 "store_formula()" to speed up the generation of repeated formulas. See
1435 "Improving performance when working with formulas" in "FORMULAS AND
1436 FUNCTIONS IN EXCEL".
1437 In many respects "repeat_formula()" behaves like "write_formula()"
1439 except that it is significantly faster.
1440 The "repeat_formula()" method creates a new formula based on the pre-
1442 parsed tokens returned by "store_formula()". The new formula is
1443 generated by substituting $pattern, $replace pairs in the stored
1444 formula:
1445 my $formula = $worksheet->store_formula('=A1 * 3 + 50');
1447 for my $row (0..99) {
1449 $worksheet->repeat_formula($row, 1, $formula, $format, 'A1', 'A'.($row +1));
1450 }
1451 It should be noted that "repeat_formula()" doesn't modify the tokens.
1453 In the above example the substitution is always made against the
1454 original token, "A1", which doesn't change.
1455 As usual, you can use "undef" if you don't wish to specify a $format:
1457 $worksheet->repeat_formula('B2', $formula, $format, 'A1', 'A2');
1459 $worksheet->repeat_formula('B3', $formula, undef, 'A1', 'A3');
1460 The substitutions are made from left to right and you can use as many
1462 $pattern, $replace pairs as you need. However, each substitution is
1463 made only once:
1464 my $formula = $worksheet->store_formula('=A1 + A1');
1466 # Gives '=B1 + A1'
1468 $worksheet->repeat_formula('B1', $formula, undef, 'A1', 'B1');
1469 # Gives '=B1 + B1'
1471 $worksheet->repeat_formula('B2', $formula, undef, ('A1', 'B1') x 2);
1472 Since the $pattern is interpolated each time that it is used it is
1474 worth using the "qr" operator to quote the pattern. The "qr" operator
1475 is explained in the "perlop" man page.
1476 $worksheet->repeat_formula('B1', $formula, $format, qr/A1/, 'A2');
1478 Care should be taken with the values that are substituted. The formula
1480 returned by "repeat_formula()" contains several other tokens in
1481 addition to those in the formula and these might also match the
1482 pattern that you are trying to replace. In particular you should avoid
1483 substituting a single 0, 1, 2 or 3.
1484 You should also be careful to avoid false matches. For example the
1486 following snippet is meant to change the stored formula in steps from
1487 "=A1 + SIN(A1)" to "=A10 + SIN(A10)".
1488 my $formula = $worksheet->store_formula('=A1 + SIN(A1)');
1490 for my $row (1 .. 10) {
1492 $worksheet->repeat_formula($row -1, 1, $formula, undef,
1493 qw/A1/, 'A' . $row, #! Bad.
1494 qw/A1/, 'A' . $row #! Bad.
1495 );
1496 }
1497 However it contains a bug. In the last iteration of the loop when $row
1499 is 10 the following substitutions will occur:
1500 s/A1/A10/; changes =A1 + SIN(A1) to =A10 + SIN(A1)
1502 s/A1/A10/; changes =A10 + SIN(A1) to =A100 + SIN(A1) # !!
1503 The solution in this case is to use a more explicit match such as
1505 "qw/^A1$/":
1506 $worksheet->repeat_formula($row -1, 1, $formula, undef,
1508 qw/^A1$/, 'A' . $row,
1509 qw/^A1$/, 'A' . $row
1510 );
1511 Another similar problem occurs due to the fact that substitutions are
1513 made in order. For example the following snippet is meant to change the
1514 stored formula from "=A10 + A11" to "=A11 + A12":
1515 my $formula = $worksheet->store_formula('=A10 + A11');
1517 $worksheet->repeat_formula('A1', $formula, undef,
1519 qw/A10/, 'A11', #! Bad.
1520 qw/A11/, 'A12' #! Bad.
1521 );
1522 However, the actual substitution yields "=A12 + A11":
1524 s/A10/A11/; changes =A10 + A11 to =A11 + A11
1526 s/A11/A12/; changes =A11 + A11 to =A12 + A11 # !!
1527 The solution here would be to reverse the order of the substitutions or
1529 to start with a stored formula that won't yield a false match such as
1530 "=X10 + Y11":
1531 my $formula = $worksheet->store_formula('=X10 + Y11');
1533 $worksheet->repeat_formula('A1', $formula, undef,
1535 qw/X10/, 'A11',
1536 qw/Y11/, 'A12'
1537 );
1538 If you think that you have a problem related to a false match you can
1540 check the tokens that you are substituting against as follows.
1541 my $formula = $worksheet->store_formula('=A1*5+4');
1543 print "@$formula\n";
1544 See also the "repeat.pl" program in the "examples" directory of the
1546 distro.
1547 write_comment($row, $column, $string, ...)
1549 The "write_comment()" method is used to add a comment to a cell. A cell
1550 comment is indicated in Excel by a small red triangle in the upper
1551 right-hand corner of the cell. Moving the cursor over the red triangle
1552 will reveal the comment.
1553 The following example shows how to add a comment to a cell:
1555 $worksheet->write (2, 2, 'Hello');
1557 $worksheet->write_comment(2, 2, 'This is a comment.');
1558 As usual you can replace the $row and $column parameters with an "A1"
1560 cell reference. See the note about "Cell notation".
1561 $worksheet->write ('C3', 'Hello');
1563 $worksheet->write_comment('C3', 'This is a comment.');
1564 On systems with "perl 5.8" and later the "write_comment()" method will
1566 also handle strings in "UTF-8" format.
1567 $worksheet->write_comment('C3', "\x{263a}"); # Smiley
1569 $worksheet->write_comment('C4', 'Comment ca va?');
1570 In addition to the basic 3 argument form of "write_comment()" you can
1572 pass in several optional key/value pairs to control the format of the
1573 comment. For example:
1574 $worksheet->write_comment('C3', 'Hello', visible => 1, author => 'Perl');
1576 Most of these options are quite specific and in general the default
1578 comment behaviour will be all that you need. However, should you need
1579 greater control over the format of the cell comment the following
1580 options are available:
1581 encoding
1583 author
1584 author_encoding
1585 visible
1586 x_scale
1587 width
1588 y_scale
1589 height
1590 color
1591 start_cell
1592 start_row
1593 start_col
1594 x_offset
1595 y_offset
1596 Option: encoding
1598 This option is used to indicate that the comment string is encoded
1599 as "UTF-16BE".
1600 my $comment = pack 'n', 0x263a; # UTF-16BE Smiley symbol
1602 $worksheet->write_comment('C3', $comment, encoding => 1);
1604 If you wish to use Unicode characters in the comment string then
1606 the preferred method is to use perl 5.8 and "UTF-8" strings, see
1607 "UNICODE IN EXCEL".
1608 Option: author
1610 This option is used to indicate who the author of the comment is.
1611 Excel displays the author of the comment in the status bar at the
1612 bottom of the worksheet. This is usually of interest in corporate
1613 environments where several people might review and provide comments
1614 to a workbook.
1615 $worksheet->write_comment('C3', 'Atonement', author => 'Ian McEwan');
1617 Option: author_encoding
1619 This option is used to indicate that the author string is encoded
1620 as "UTF-16BE".
1621 Option: visible
1623 This option is used to make a cell comment visible when the
1624 worksheet is opened. The default behaviour in Excel is that
1625 comments are initially hidden. However, it is also possible in
1626 Excel to make individual or all comments visible. In
1627 Spreadsheet::WriteExcel individual comments can be made visible as
1628 follows:
1629 $worksheet->write_comment('C3', 'Hello', visible => 1);
1631 It is possible to make all comments in a worksheet visible using
1633 the "show_comments()" worksheet method (see below). Alternatively,
1634 if all of the cell comments have been made visible you can hide
1635 individual comments:
1636 $worksheet->write_comment('C3', 'Hello', visible => 0);
1638 Option: x_scale
1640 This option is used to set the width of the cell comment box as a
1641 factor of the default width.
1642 $worksheet->write_comment('C3', 'Hello', x_scale => 2);
1644 $worksheet->write_comment('C4', 'Hello', x_scale => 4.2);
1645 Option: width
1647 This option is used to set the width of the cell comment box
1648 explicitly in pixels.
1649 $worksheet->write_comment('C3', 'Hello', width => 200);
1651 Option: y_scale
1653 This option is used to set the height of the cell comment box as a
1654 factor of the default height.
1655 $worksheet->write_comment('C3', 'Hello', y_scale => 2);
1657 $worksheet->write_comment('C4', 'Hello', y_scale => 4.2);
1658 Option: height
1660 This option is used to set the height of the cell comment box
1661 explicitly in pixels.
1662 $worksheet->write_comment('C3', 'Hello', height => 200);
1664 Option: color
1666 This option is used to set the background colour of cell comment
1667 box. You can use one of the named colours recognised by
1668 Spreadsheet::WriteExcel or a colour index. See "COLOURS IN EXCEL".
1669 $worksheet->write_comment('C3', 'Hello', color => 'green');
1671 $worksheet->write_comment('C4', 'Hello', color => 0x35); # Orange
1672 Option: start_cell
1674 This option is used to set the cell in which the comment will
1675 appear. By default Excel displays comments one cell to the right
1676 and one cell above the cell to which the comment relates. However,
1677 you can change this behaviour if you wish. In the following example
1678 the comment which would appear by default in cell "D2" is moved to
1679 "E2".
1680 $worksheet->write_comment('C3', 'Hello', start_cell => 'E2');
1682 Option: start_row
1684 This option is used to set the row in which the comment will
1685 appear. See the "start_cell" option above. The row is zero indexed.
1686 $worksheet->write_comment('C3', 'Hello', start_row => 0);
1688 Option: start_col
1690 This option is used to set the column in which the comment will
1691 appear. See the "start_cell" option above. The column is zero
1692 indexed.
1693 $worksheet->write_comment('C3', 'Hello', start_col => 4);
1695 Option: x_offset
1697 This option is used to change the x offset, in pixels, of a comment
1698 within a cell:
1699 $worksheet->write_comment('C3', $comment, x_offset => 30);
1701 Option: y_offset
1703 This option is used to change the y offset, in pixels, of a comment
1704 within a cell:
1705 $worksheet->write_comment('C3', $comment, x_offset => 30);
1707 You can apply as many of these options as you require.
1709 See also "ROW HEIGHTS AND WORKSHEET OBJECTS".
1711 show_comments()
1713 This method is used to make all cell comments visible when a worksheet
1714 is opened.
1715 Individual comments can be made visible using the "visible" parameter
1717 of the "write_comment" method (see above):
1718 $worksheet->write_comment('C3', 'Hello', visible => 1);
1720 If all of the cell comments have been made visible you can hide
1722 individual comments as follows:
1723 $worksheet->write_comment('C3', 'Hello', visible => 0);
1725 add_write_handler($re, $code_ref)
1727 This method is used to extend the Spreadsheet::WriteExcel write()
1728 method to handle user defined data.
1729 If you refer to the section on "write()" above you will see that it
1731 acts as an alias for several more specific "write_*" methods. However,
1732 it doesn't always act in exactly the way that you would like it to.
1733 One solution is to filter the input data yourself and call the
1735 appropriate "write_*" method. Another approach is to use the
1736 "add_write_handler()" method to add your own automated behaviour to
1737 "write()".
1738 The "add_write_handler()" method take two arguments, $re, a regular
1740 expression to match incoming data and $code_ref a callback function to
1741 handle the matched data:
1742 $worksheet->add_write_handler(qr/^\d\d\d\d$/, \&my_write);
1744 (In the these examples the "qr" operator is used to quote the regular
1746 expression strings, see perlop for more details).
1747 The method is used as follows. say you wished to write 7 digit ID
1749 numbers as a string so that any leading zeros were preserved*, you
1750 could do something like the following:
1751 $worksheet->add_write_handler(qr/^\d{7}$/, \&write_my_id);
1753 sub write_my_id {
1756 my $worksheet = shift;
1757 return $worksheet->write_string(@_);
1758 }
1759 * You could also use the "keep_leading_zeros()" method for this.
1761 Then if you call "write()" with an appropriate string it will be
1763 handled automatically:
1764 # Writes 0000000. It would normally be written as a number; 0.
1766 $worksheet->write('A1', '0000000');
1767 The callback function will receive a reference to the calling worksheet
1769 and all of the other arguments that were passed to "write()". The
1770 callback will see an @_ argument list that looks like the following:
1771 $_[0] A ref to the calling worksheet. *
1773 $_[1] Zero based row number.
1774 $_[2] Zero based column number.
1775 $_[3] A number or string or token.
1776 $_[4] A format ref if any.
1777 $_[5] Any other arguments.
1778 ...
1779 * It is good style to shift this off the list so the @_ is the same
1781 as the argument list seen by write().
1782 Your callback should "return()" the return value of the "write_*"
1784 method that was called or "undef" to indicate that you rejected the
1785 match and want "write()" to continue as normal.
1786 So for example if you wished to apply the previous filter only to ID
1788 values that occur in the first column you could modify your callback
1789 function as follows:
1790 sub write_my_id {
1792 my $worksheet = shift;
1793 my $col = $_[1];
1794 if ($col == 0) {
1796 return $worksheet->write_string(@_);
1797 }
1798 else {
1799 # Reject the match and return control to write()
1800 return undef;
1801 }
1802 }
1803 Now, you will get different behaviour for the first column and other
1805 columns:
1806 $worksheet->write('A1', '0000000'); # Writes 0000000
1808 $worksheet->write('B1', '0000000'); # Writes 0
1809 You may add more than one handler in which case they will be called in
1811 the order that they were added.
1812 Note, the "add_write_handler()" method is particularly suited for
1814 handling dates.
1815 See the "write_handler 1-4" programs in the "examples" directory for
1817 further examples.
1818 insert_image($row, $col, $filename, $x, $y, $scale_x, $scale_y)
1820 This method can be used to insert a image into a worksheet. The image
1821 can be in PNG, JPEG or BMP format. The $x, $y, $scale_x and $scale_y
1822 parameters are optional.
1823 $worksheet1->insert_image('A1', 'perl.bmp');
1825 $worksheet2->insert_image('A1', '../images/perl.bmp');
1826 $worksheet3->insert_image('A1', '.c:\images\perl.bmp');
1827 The parameters $x and $y can be used to specify an offset from the top
1829 left hand corner of the cell specified by $row and $col. The offset
1830 values are in pixels.
1831 $worksheet1->insert_image('A1', 'perl.bmp', 32, 10);
1833 The default width of a cell is 63 pixels. The default height of a cell
1835 is 17 pixels. The pixels offsets can be calculated using the following
1836 relationships:
1837 Wp = int(12We) if We < 1
1839 Wp = int(7We +5) if We >= 1
1840 Hp = int(4/3He)
1841 where:
1843 We is the cell width in Excels units
1844 Wp is width in pixels
1845 He is the cell height in Excels units
1846 Hp is height in pixels
1847 The offsets can be greater than the width or height of the underlying
1849 cell. This can be occasionally useful if you wish to align two or more
1850 images relative to the same cell.
1851 The parameters $scale_x and $scale_y can be used to scale the inserted
1853 image horizontally and vertically:
1854 # Scale the inserted image: width x 2.0, height x 0.8
1856 $worksheet->insert_image('A1', 'perl.bmp', 0, 0, 2, 0.8);
1857 See also the "images.pl" program in the "examples" directory of the
1859 distro.
1860 BMP images must be 24 bit, true colour, bitmaps. In general it is best
1862 to avoid BMP images since they aren't compressed. The older
1863 "insert_bitmap()" method is still supported but deprecated.
1864 See also "ROW HEIGHTS AND WORKSHEET OBJECTS".
1866 insert_chart($row, $col, $chart, $x, $y, $scale_x, $scale_y)
1868 This method can be used to insert a Chart object into a worksheet. The
1869 Chart must be created by the "add_chart()" Workbook method and it must
1870 have the "embedded" option set.
1871 my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
1873 # Configure the chart.
1875 ...
1876 # Insert the chart into the a worksheet.
1878 $worksheet->insert_chart('E2', $chart);
1879 See "add_chart()" for details on how to create the Chart object and
1881 Spreadsheet::WriteExcel::Chart for details on how to configure it. See
1882 also the "chart_*.pl" programs in the examples directory of the distro.
1883 The $x, $y, $scale_x and $scale_y parameters are optional.
1885 The parameters $x and $y can be used to specify an offset from the top
1887 left hand corner of the cell specified by $row and $col. The offset
1888 values are in pixels. See the "insert_image" method above for more
1889 information on sizes.
1890 $worksheet1->insert_chart('E2', $chart, 3, 3);
1892 The parameters $scale_x and $scale_y can be used to scale the inserted
1894 image horizontally and vertically:
1895 # Scale the width by 120% and the height by 150%
1897 $worksheet->insert_chart('E2', $chart, 0, 0, 1.2, 1.5);
1898 The easiest way to calculate the required scaling is to create a test
1900 chart worksheet with Spreadsheet::WriteExcel. Then open the file,
1901 select the chart and drag the corner to get the required size. While
1902 holding down the mouse the scale of the resized chart is shown to the
1903 left of the formula bar.
1904 See also "ROW HEIGHTS AND WORKSHEET OBJECTS".
1906 embed_chart($row, $col, $filename, $x, $y, $scale_x, $scale_y)
1908 This method can be used to insert a externally generated chart into a
1909 worksheet. The chart must first be extracted from an existing Excel
1910 file. This feature is semi-deprecated in favour of the "native" charts
1911 created using "add_chart()". Read "external_charts.txt" (or ".pod") in
1912 the external_charts directory of the distro for a full explanation.
1913 Here is an example:
1915 $worksheet->embed_chart('B2', 'sales_chart.bin');
1917 The $x, $y, $scale_x and $scale_y parameters are optional. See
1919 "insert_chart()" above for details.
1920 data_validation()
1922 The "data_validation()" method is used to construct an Excel data
1923 validation or to limit the user input to a dropdown list of values.
1924 $worksheet->data_validation('B3',
1926 {
1927 validate => 'integer',
1928 criteria => '>',
1929 value => 100,
1930 });
1931 $worksheet->data_validation('B5:B9',
1933 {
1934 validate => 'list',
1935 value => ['open', 'high', 'close'],
1936 });
1937 This method contains a lot of parameters and is described in detail in
1939 a separate section "DATA VALIDATION IN EXCEL".
1940 See also the "data_validate.pl" program in the examples directory of
1942 the distro
1943 get_name()
1945 The "get_name()" method is used to retrieve the name of a worksheet.
1946 For example:
1947 foreach my $sheet ($workbook->sheets()) {
1949 print $sheet->get_name();
1950 }
1951 For reasons related to the design of Spreadsheet::WriteExcel and to the
1953 internals of Excel there is no "set_name()" method. The only way to set
1954 the worksheet name is via the "add_worksheet()" method.
1955 activate()
1957 The "activate()" method is used to specify which worksheet is initially
1958 visible in a multi-sheet workbook:
1959 $worksheet1 = $workbook->add_worksheet('To');
1961 $worksheet2 = $workbook->add_worksheet('the');
1962 $worksheet3 = $workbook->add_worksheet('wind');
1963 $worksheet3->activate();
1965 This is similar to the Excel VBA activate method. More than one
1967 worksheet can be selected via the "select()" method, see below, however
1968 only one worksheet can be active.
1969 The default active worksheet is the first worksheet.
1971 select()
1973 The "select()" method is used to indicate that a worksheet is selected
1974 in a multi-sheet workbook:
1975 $worksheet1->activate();
1977 $worksheet2->select();
1978 $worksheet3->select();
1979 A selected worksheet has its tab highlighted. Selecting worksheets is a
1981 way of grouping them together so that, for example, several worksheets
1982 could be printed in one go. A worksheet that has been activated via the
1983 "activate()" method will also appear as selected.
1984 hide()
1986 The "hide()" method is used to hide a worksheet:
1987 $worksheet2->hide();
1989 You may wish to hide a worksheet in order to avoid confusing a user
1991 with intermediate data or calculations.
1992 A hidden worksheet can not be activated or selected so this method is
1994 mutually exclusive with the "activate()" and "select()" methods. In
1995 addition, since the first worksheet will default to being the active
1996 worksheet, you cannot hide the first worksheet without activating
1997 another sheet:
1998 $worksheet2->activate();
2000 $worksheet1->hide();
2001 set_first_sheet()
2003 The "activate()" method determines which worksheet is initially
2004 selected. However, if there are a large number of worksheets the
2005 selected worksheet may not appear on the screen. To avoid this you can
2006 select which is the leftmost visible worksheet using
2007 "set_first_sheet()":
2008 for (1..20) {
2010 $workbook->add_worksheet;
2011 }
2012 $worksheet21 = $workbook->add_worksheet();
2014 $worksheet22 = $workbook->add_worksheet();
2015 $worksheet21->set_first_sheet();
2017 $worksheet22->activate();
2018 This method is not required very often. The default value is the first
2020 worksheet.
2021 protect($password)
2023 The "protect()" method is used to protect a worksheet from
2024 modification:
2025 $worksheet->protect();
2027 It can be turned off in Excel via the "Tools->Protection->Unprotect
2029 Sheet" menu command.
2030 The "protect()" method also has the effect of enabling a cell's
2032 "locked" and "hidden" properties if they have been set. A "locked" cell
2033 cannot be edited. A "hidden" cell will display the results of a formula
2034 but not the formula itself. In Excel a cell's locked property is on by
2035 default.
2036 # Set some format properties
2038 my $unlocked = $workbook->add_format(locked => 0);
2039 my $hidden = $workbook->add_format(hidden => 1);
2040 # Enable worksheet protection
2042 $worksheet->protect();
2043 # This cell cannot be edited, it is locked by default
2045 $worksheet->write('A1', '=1+2');
2046 # This cell can be edited
2048 $worksheet->write('A2', '=1+2', $unlocked);
2049 # The formula in this cell isn't visible
2051 $worksheet->write('A3', '=1+2', $hidden);
2052 See also the "set_locked" and "set_hidden" format methods in "CELL
2054 FORMATTING".
2055 You can optionally add a password to the worksheet protection:
2057 $worksheet->protect('drowssap');
2059 Note, the worksheet level password in Excel provides very weak
2061 protection. It does not encrypt your data in any way and it is very
2062 easy to deactivate. Therefore, do not use the above method if you wish
2063 to protect sensitive data or calculations. However, before you get
2064 worried, Excel's own workbook level password protection does provide
2065 strong encryption in Excel 97+. For technical reasons this will never
2066 be supported by "Spreadsheet::WriteExcel".
2067 set_selection($first_row, $first_col, $last_row, $last_col)
2069 This method can be used to specify which cell or cells are selected in
2070 a worksheet. The most common requirement is to select a single cell, in
2071 which case $last_row and $last_col can be omitted. The active cell
2072 within a selected range is determined by the order in which $first and
2073 $last are specified. It is also possible to specify a cell or a range
2074 using A1 notation. See the note about "Cell notation".
2075 Examples:
2077 $worksheet1->set_selection(3, 3); # 1. Cell D4.
2079 $worksheet2->set_selection(3, 3, 6, 6); # 2. Cells D4 to G7.
2080 $worksheet3->set_selection(6, 6, 3, 3); # 3. Cells G7 to D4.
2081 $worksheet4->set_selection('D4'); # Same as 1.
2082 $worksheet5->set_selection('D4:G7'); # Same as 2.
2083 $worksheet6->set_selection('G7:D4'); # Same as 3.
2084 The default cell selections is (0, 0), 'A1'.
2086 set_row($row, $height, $format, $hidden, $level, $collapsed)
2088 This method can be used to change the default properties of a row. All
2089 parameters apart from $row are optional.
2090 The most common use for this method is to change the height of a row:
2092 $worksheet->set_row(0, 20); # Row 1 height set to 20
2094 If you wish to set the format without changing the height you can pass
2096 "undef" as the height parameter:
2097 $worksheet->set_row(0, undef, $format);
2099 The $format parameter will be applied to any cells in the row that
2101 don't have a format. For example
2102 $worksheet->set_row(0, undef, $format1); # Set the format for row 1
2104 $worksheet->write('A1', 'Hello'); # Defaults to $format1
2105 $worksheet->write('B1', 'Hello', $format2); # Keeps $format2
2106 If you wish to define a row format in this way you should call the
2108 method before any calls to "write()". Calling it afterwards will
2109 overwrite any format that was previously specified.
2110 The $hidden parameter should be set to 1 if you wish to hide a row.
2112 This can be used, for example, to hide intermediary steps in a
2113 complicated calculation:
2114 $worksheet->set_row(0, 20, $format, 1);
2116 $worksheet->set_row(1, undef, undef, 1);
2117 The $level parameter is used to set the outline level of the row.
2119 Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent
2120 rows with the same outline level are grouped together into a single
2121 outline.
2122 The following example sets an outline level of 1 for rows 1 and 2
2124 (zero-indexed):
2125 $worksheet->set_row(1, undef, undef, 0, 1);
2127 $worksheet->set_row(2, undef, undef, 0, 1);
2128 The $hidden parameter can also be used to hide collapsed outlined rows
2130 when used in conjunction with the $level parameter.
2131 $worksheet->set_row(1, undef, undef, 1, 1);
2133 $worksheet->set_row(2, undef, undef, 1, 1);
2134 For collapsed outlines you should also indicate which row has the
2136 collapsed "+" symbol using the optional $collapsed parameter.
2137 $worksheet->set_row(3, undef, undef, 0, 0, 1);
2139 For a more complete example see the "outline.pl" and
2141 "outline_collapsed.pl" programs in the examples directory of the
2142 distro.
2143 Excel allows up to 7 outline levels. Therefore the $level parameter
2145 should be in the range "0 <= $level <= 7".
2146 set_column($first_col, $last_col, $width, $format, $hidden, $level,
2148 $collapsed)
2149 This method can be used to change the default properties of a single
2150 column or a range of columns. All parameters apart from $first_col and
2151 $last_col are optional.
2152 If "set_column()" is applied to a single column the value of $first_col
2154 and $last_col should be the same. In the case where $last_col is zero
2155 it is set to the same value as $first_col.
2156 It is also possible, and generally clearer, to specify a column range
2158 using the form of A1 notation used for columns. See the note about
2159 "Cell notation".
2160 Examples:
2162 $worksheet->set_column(0, 0, 20); # Column A width set to 20
2164 $worksheet->set_column(1, 3, 30); # Columns B-D width set to 30
2165 $worksheet->set_column('E:E', 20); # Column E width set to 20
2166 $worksheet->set_column('F:H', 30); # Columns F-H width set to 30
2167 The width corresponds to the column width value that is specified in
2169 Excel. It is approximately equal to the length of a string in the
2170 default font of Arial 10. Unfortunately, there is no way to specify
2171 "AutoFit" for a column in the Excel file format. This feature is only
2172 available at runtime from within Excel.
2173 As usual the $format parameter is optional, for additional information,
2175 see "CELL FORMATTING". If you wish to set the format without changing
2176 the width you can pass "undef" as the width parameter:
2177 $worksheet->set_column(0, 0, undef, $format);
2179 The $format parameter will be applied to any cells in the column that
2181 don't have a format. For example
2182 $worksheet->set_column('A:A', undef, $format1); # Set format for col 1
2184 $worksheet->write('A1', 'Hello'); # Defaults to $format1
2185 $worksheet->write('A2', 'Hello', $format2); # Keeps $format2
2186 If you wish to define a column format in this way you should call the
2188 method before any calls to "write()". If you call it afterwards it
2189 won't have any effect.
2190 A default row format takes precedence over a default column format
2192 $worksheet->set_row(0, undef, $format1); # Set format for row 1
2194 $worksheet->set_column('A:A', undef, $format2); # Set format for col 1
2195 $worksheet->write('A1', 'Hello'); # Defaults to $format1
2196 $worksheet->write('A2', 'Hello'); # Defaults to $format2
2197 The $hidden parameter should be set to 1 if you wish to hide a column.
2199 This can be used, for example, to hide intermediary steps in a
2200 complicated calculation:
2201 $worksheet->set_column('D:D', 20, $format, 1);
2203 $worksheet->set_column('E:E', undef, undef, 1);
2204 The $level parameter is used to set the outline level of the column.
2206 Outlines are described in "OUTLINES AND GROUPING IN EXCEL". Adjacent
2207 columns with the same outline level are grouped together into a single
2208 outline.
2209 The following example sets an outline level of 1 for columns B to G:
2211 $worksheet->set_column('B:G', undef, undef, 0, 1);
2213 The $hidden parameter can also be used to hide collapsed outlined
2215 columns when used in conjunction with the $level parameter.
2216 $worksheet->set_column('B:G', undef, undef, 1, 1);
2218 For collapsed outlines you should also indicate which row has the
2220 collapsed "+" symbol using the optional $collapsed parameter.
2221 $worksheet->set_column('H:H', undef, undef, 0, 0, 1);
2223 For a more complete example see the "outline.pl" and
2225 "outline_collapsed.pl" programs in the examples directory of the
2226 distro.
2227 Excel allows up to 7 outline levels. Therefore the $level parameter
2229 should be in the range "0 <= $level <= 7".
2230 outline_settings($visible, $symbols_below, $symbols_right, $auto_style)
2232 The "outline_settings()" method is used to control the appearance of
2233 outlines in Excel. Outlines are described in "OUTLINES AND GROUPING IN
2234 EXCEL".
2235 The $visible parameter is used to control whether or not outlines are
2237 visible. Setting this parameter to 0 will cause all outlines on the
2238 worksheet to be hidden. They can be unhidden in Excel by means of the
2239 "Show Outline Symbols" command button. The default setting is 1 for
2240 visible outlines.
2241 $worksheet->outline_settings(0);
2243 The $symbols_below parameter is used to control whether the row outline
2245 symbol will appear above or below the outline level bar. The default
2246 setting is 1 for symbols to appear below the outline level bar.
2247 The "symbols_right" parameter is used to control whether the column
2249 outline symbol will appear to the left or the right of the outline
2250 level bar. The default setting is 1 for symbols to appear to the right
2251 of the outline level bar.
2252 The $auto_style parameter is used to control whether the automatic
2254 outline generator in Excel uses automatic styles when creating an
2255 outline. This has no effect on a file generated by
2256 "Spreadsheet::WriteExcel" but it does have an effect on how the
2257 worksheet behaves after it is created. The default setting is 0 for
2258 "Automatic Styles" to be turned off.
2259 The default settings for all of these parameters correspond to Excel's
2261 default parameters.
2262 The worksheet parameters controlled by "outline_settings()" are rarely
2264 used.
2265 freeze_panes($row, $col, $top_row, $left_col)
2267 This method can be used to divide a worksheet into horizontal or
2268 vertical regions known as panes and to also "freeze" these panes so
2269 that the splitter bars are not visible. This is the same as the
2270 "Window->Freeze Panes" menu command in Excel
2271 The parameters $row and $col are used to specify the location of the
2273 split. It should be noted that the split is specified at the top or
2274 left of a cell and that the method uses zero based indexing. Therefore
2275 to freeze the first row of a worksheet it is necessary to specify the
2276 split at row 2 (which is 1 as the zero-based index). This might lead
2277 you to think that you are using a 1 based index but this is not the
2278 case.
2279 You can set one of the $row and $col parameters as zero if you do not
2281 want either a vertical or horizontal split.
2282 Examples:
2284 $worksheet->freeze_panes(1, 0); # Freeze the first row
2286 $worksheet->freeze_panes('A2'); # Same using A1 notation
2287 $worksheet->freeze_panes(0, 1); # Freeze the first column
2288 $worksheet->freeze_panes('B1'); # Same using A1 notation
2289 $worksheet->freeze_panes(1, 2); # Freeze first row and first 2 columns
2290 $worksheet->freeze_panes('C2'); # Same using A1 notation
2291 The parameters $top_row and $left_col are optional. They are used to
2293 specify the top-most or left-most visible row or column in the
2294 scrolling region of the panes. For example to freeze the first row and
2295 to have the scrolling region begin at row twenty:
2296 $worksheet->freeze_panes(1, 0, 20, 0);
2298 You cannot use A1 notation for the $top_row and $left_col parameters.
2300 See also the "panes.pl" program in the "examples" directory of the
2302 distribution.
2303 split_panes($y, $x, $top_row, $left_col)
2305 This method can be used to divide a worksheet into horizontal or
2306 vertical regions known as panes. This method is different from the
2307 "freeze_panes()" method in that the splits between the panes will be
2308 visible to the user and each pane will have its own scroll bars.
2309 The parameters $y and $x are used to specify the vertical and
2311 horizontal position of the split. The units for $y and $x are the same
2312 as those used by Excel to specify row height and column width. However,
2313 the vertical and horizontal units are different from each other.
2314 Therefore you must specify the $y and $x parameters in terms of the row
2315 heights and column widths that you have set or the default values which
2316 are 12.75 for a row and 8.43 for a column.
2317 You can set one of the $y and $x parameters as zero if you do not want
2319 either a vertical or horizontal split. The parameters $top_row and
2320 $left_col are optional. They are used to specify the top-most or left-
2321 most visible row or column in the bottom-right pane.
2322 Example:
2324 $worksheet->split_panes(12.75, 0, 1, 0); # First row
2326 $worksheet->split_panes(0, 8.43, 0, 1); # First column
2327 $worksheet->split_panes(12.75, 8.43, 1, 1); # First row and column
2328 You cannot use A1 notation with this method.
2330 See also the "freeze_panes()" method and the "panes.pl" program in the
2332 "examples" directory of the distribution.
2333 Note: This "split_panes()" method was called "thaw_panes()" in older
2335 versions. The older name is still available for backwards
2336 compatibility.
2337 merge_range($first_row, $first_col, $last_row, $last_col, $token, $format,
2339 $utf_16_be)
2340 Merging cells can be achieved by setting the "merge" property of a
2341 Format object, see "CELL FORMATTING". However, this only allows simple
2342 Excel5 style horizontal merging which Excel refers to as "center across
2343 selection".
2344 The "merge_range()" method allows you to do Excel97+ style formatting
2346 where the cells can contain other types of alignment in addition to the
2347 merging:
2348 my $format = $workbook->add_format(
2350 border => 6,
2351 valign => 'vcenter',
2352 align => 'center',
2353 );
2354 $worksheet->merge_range('B3:D4', 'Vertical and horizontal', $format);
2356 WARNING. The format object that is used with a "merge_range()" method
2358 call is marked internally as being associated with a merged range. It
2359 is a fatal error to use a merged format in a non-merged cell. Instead
2360 you should use separate formats for merged and non-merged cells. This
2361 restriction will be removed in a future release.
2362 The $utf_16_be parameter is optional, see below.
2364 "merge_range()" writes its $token argument using the worksheet
2366 "write()" method. Therefore it will handle numbers, strings, formulas
2367 or urls as required.
2368 Setting the "merge" property of the format isn't required when you are
2370 using "merge_range()". In fact using it will exclude the use of any
2371 other horizontal alignment option.
2372 On systems with "perl 5.8" and later the "merge_range()" method will
2374 also handle strings in "UTF-8" format.
2375 $worksheet->merge_range('B3:D4', "\x{263a}", $format); # Smiley
2377 On earlier Perl systems your can specify "UTF-16BE" worksheet names
2379 using an additional optional parameter:
2380 my $str = pack 'n', 0x263a;
2382 $worksheet->merge_range('B3:D4', $str, $format, 1); # Smiley
2383 The full possibilities of this method are shown in the "merge3.pl" to
2385 "merge6.pl" programs in the "examples" directory of the distribution.
2386 set_zoom($scale)
2388 Set the worksheet zoom factor in the range "10 <= $scale <= 400":
2389 $worksheet1->set_zoom(50);
2391 $worksheet2->set_zoom(75);
2392 $worksheet3->set_zoom(300);
2393 $worksheet4->set_zoom(400);
2394 The default zoom factor is 100. You cannot zoom to "Selection" because
2396 it is calculated by Excel at run-time.
2397 Note, "set_zoom()" does not affect the scale of the printed page. For
2399 that you should use "set_print_scale()".
2400 right_to_left()
2402 The "right_to_left()" method is used to change the default direction of
2403 the worksheet from left-to-right, with the A1 cell in the top left, to
2404 right-to-left, with the he A1 cell in the top right.
2405 $worksheet->right_to_left();
2407 This is useful when creating Arabic, Hebrew or other near or far
2409 eastern worksheets that use right-to-left as the default direction.
2410 hide_zero()
2412 The "hide_zero()" method is used to hide any zero values that appear in
2413 cells.
2414 $worksheet->hide_zero();
2416 In Excel this option is found under Tools->Options->View.
2418 set_tab_color()
2420 The "set_tab_color()" method is used to change the colour of the
2421 worksheet tab. This feature is only available in Excel 2002 and later.
2422 You can use one of the standard colour names provided by the Format
2423 object or a colour index. See "COLOURS IN EXCEL" and the
2424 "set_custom_color()" method.
2425 $worksheet1->set_tab_color('red');
2427 $worksheet2->set_tab_color(0x0C);
2428 See the "tab_colors.pl" program in the examples directory of the
2430 distro.
2431 autofilter($first_row, $first_col, $last_row, $last_col)
2433 This method allows an autofilter to be added to a worksheet. An
2434 autofilter is a way of adding drop down lists to the headers of a 2D
2435 range of worksheet data. This in turn allow users to filter the data
2436 based on simple criteria so that some data is shown and some is hidden.
2437 To add an autofilter to a worksheet:
2439 $worksheet->autofilter(0, 0, 10, 3);
2441 $worksheet->autofilter('A1:D11'); # Same as above in A1 notation.
2442 Filter conditions can be applied using the "filter_column()" method.
2444 See the "autofilter.pl" program in the examples directory of the distro
2446 for a more detailed example.
2447 filter_column($column, $expression)
2449 The "filter_column" method can be used to filter columns in a
2450 autofilter range based on simple conditions.
2451 NOTE: It isn't sufficient to just specify the filter condition. You
2453 must also hide any rows that don't match the filter condition. Rows are
2454 hidden using the "set_row()" "visible" parameter.
2455 "Spreadsheet::WriteExcel" cannot do this automatically since it isn't
2456 part of the file format. See the "autofilter.pl" program in the
2457 examples directory of the distro for an example.
2458 The conditions for the filter are specified using simple expressions:
2460 $worksheet->filter_column('A', 'x > 2000');
2462 $worksheet->filter_column('B', 'x > 2000 and x < 5000');
2463 The $column parameter can either be a zero indexed column number or a
2465 string column name.
2466 The following operators are available:
2468 Operator Synonyms
2470 == = eq =~
2471 != <> ne !=
2472 >
2473 <
2474 >=
2475 <=
2476 and &&
2478 or ||
2479 The operator synonyms are just syntactic sugar to make you more
2481 comfortable using the expressions. It is important to remember that the
2482 expressions will be interpreted by Excel and not by perl.
2483 An expression can comprise a single statement or two statements
2485 separated by the "and" and "or" operators. For example:
2486 'x < 2000'
2488 'x > 2000'
2489 'x == 2000'
2490 'x > 2000 and x < 5000'
2491 'x == 2000 or x == 5000'
2492 Filtering of blank or non-blank data can be achieved by using a value
2494 of "Blanks" or "NonBlanks" in the expression:
2495 'x == Blanks'
2497 'x == NonBlanks'
2498 Top 10 style filters can be specified using a expression like the
2500 following:
2501 Top|Bottom 1-500 Items|%
2503 For example:
2505 'Top 10 Items'
2507 'Bottom 5 Items'
2508 'Top 25 %'
2509 'Bottom 50 %'
2510 Excel also allows some simple string matching operations:
2512 'x =~ b*' # begins with b
2514 'x !~ b*' # doesn't begin with b
2515 'x =~ *b' # ends with b
2516 'x !~ *b' # doesn't end with b
2517 'x =~ *b*' # contains b
2518 'x !~ *b*' # doesn't contains b
2519 You can also use "*" to match any character or number and "?" to match
2521 any single character or number. No other regular expression quantifier
2522 is supported by Excel's filters. Excel's regular expression characters
2523 can be escaped using "~".
2524 The placeholder variable "x" in the above examples can be replaced by
2526 any simple string. The actual placeholder name is ignored internally so
2527 the following are all equivalent:
2528 'x < 2000'
2530 'col < 2000'
2531 'Price < 2000'
2532 Also, note that a filter condition can only be applied to a column in a
2534 range specified by the "autofilter()" Worksheet method.
2535 See the "autofilter.pl" program in the examples directory of the distro
2537 for a more detailed example.
2538
2540 Page set-up methods affect the way that a worksheet looks when it is
2541 printed. They control features such as page headers and footers and
2542 margins. These methods are really just standard worksheet methods. They
2543 are documented here in a separate section for the sake of clarity.
2544 The following methods are available for page set-up:
2546 set_landscape()
2548 set_portrait()
2549 set_page_view()
2550 set_paper()
2551 center_horizontally()
2552 center_vertically()
2553 set_margins()
2554 set_header()
2555 set_footer()
2556 repeat_rows()
2557 repeat_columns()
2558 hide_gridlines()
2559 print_row_col_headers()
2560 print_area()
2561 print_across()
2562 fit_to_pages()
2563 set_start_page()
2564 set_print_scale()
2565 set_h_pagebreaks()
2566 set_v_pagebreaks()
2567 A common requirement when working with Spreadsheet::WriteExcel is to
2569 apply the same page set-up features to all of the worksheets in a
2570 workbook. To do this you can use the "sheets()" method of the
2571 "workbook" class to access the array of worksheets in a workbook:
2572 foreach $worksheet ($workbook->sheets()) {
2574 $worksheet->set_landscape();
2575 }
2576 set_landscape()
2578 This method is used to set the orientation of a worksheet's printed
2579 page to landscape:
2580 $worksheet->set_landscape(); # Landscape mode
2582 set_portrait()
2584 This method is used to set the orientation of a worksheet's printed
2585 page to portrait. The default worksheet orientation is portrait, so you
2586 won't generally need to call this method.
2587 $worksheet->set_portrait(); # Portrait mode
2589 set_page_view()
2591 This method is used to display the worksheet in "Page View" mode. This
2592 is currently only supported by Mac Excel, where it is the default.
2593 $worksheet->set_page_view();
2595 set_paper($index)
2597 This method is used to set the paper format for the printed output of a
2598 worksheet. The following paper styles are available:
2599 Index Paper format Paper size
2601 ===== ============ ==========
2602 0 Printer default -
2603 1 Letter 8 1/2 x 11 in
2604 2 Letter Small 8 1/2 x 11 in
2605 3 Tabloid 11 x 17 in
2606 4 Ledger 17 x 11 in
2607 5 Legal 8 1/2 x 14 in
2608 6 Statement 5 1/2 x 8 1/2 in
2609 7 Executive 7 1/4 x 10 1/2 in
2610 8 A3 297 x 420 mm
2611 9 A4 210 x 297 mm
2612 10 A4 Small 210 x 297 mm
2613 11 A5 148 x 210 mm
2614 12 B4 250 x 354 mm
2615 13 B5 182 x 257 mm
2616 14 Folio 8 1/2 x 13 in
2617 15 Quarto 215 x 275 mm
2618 16 - 10x14 in
2619 17 - 11x17 in
2620 18 Note 8 1/2 x 11 in
2621 19 Envelope 9 3 7/8 x 8 7/8
2622 20 Envelope 10 4 1/8 x 9 1/2
2623 21 Envelope 11 4 1/2 x 10 3/8
2624 22 Envelope 12 4 3/4 x 11
2625 23 Envelope 14 5 x 11 1/2
2626 24 C size sheet -
2627 25 D size sheet -
2628 26 E size sheet -
2629 27 Envelope DL 110 x 220 mm
2630 28 Envelope C3 324 x 458 mm
2631 29 Envelope C4 229 x 324 mm
2632 30 Envelope C5 162 x 229 mm
2633 31 Envelope C6 114 x 162 mm
2634 32 Envelope C65 114 x 229 mm
2635 33 Envelope B4 250 x 353 mm
2636 34 Envelope B5 176 x 250 mm
2637 35 Envelope B6 176 x 125 mm
2638 36 Envelope 110 x 230 mm
2639 37 Monarch 3.875 x 7.5 in
2640 38 Envelope 3 5/8 x 6 1/2 in
2641 39 Fanfold 14 7/8 x 11 in
2642 40 German Std Fanfold 8 1/2 x 12 in
2643 41 German Legal Fanfold 8 1/2 x 13 in
2644 Note, it is likely that not all of these paper types will be available
2646 to the end user since it will depend on the paper formats that the
2647 user's printer supports. Therefore, it is best to stick to standard
2648 paper types.
2649 $worksheet->set_paper(1); # US Letter
2651 $worksheet->set_paper(9); # A4
2652 If you do not specify a paper type the worksheet will print using the
2654 printer's default paper.
2655 center_horizontally()
2657 Center the worksheet data horizontally between the margins on the
2658 printed page:
2659 $worksheet->center_horizontally();
2661 center_vertically()
2663 Center the worksheet data vertically between the margins on the printed
2664 page:
2665 $worksheet->center_vertically();
2667 set_margins($inches)
2669 There are several methods available for setting the worksheet margins
2670 on the printed page:
2671 set_margins() # Set all margins to the same value
2673 set_margins_LR() # Set left and right margins to the same value
2674 set_margins_TB() # Set top and bottom margins to the same value
2675 set_margin_left(); # Set left margin
2676 set_margin_right(); # Set right margin
2677 set_margin_top(); # Set top margin
2678 set_margin_bottom(); # Set bottom margin
2679 All of these methods take a distance in inches as a parameter. Note: 1
2681 inch = 25.4mm. ;-) The default left and right margin is 0.75 inch. The
2682 default top and bottom margin is 1.00 inch.
2683 set_header($string, $margin)
2685 Headers and footers are generated using a $string which is a
2686 combination of plain text and control characters. The $margin parameter
2687 is optional.
2688 The available control character are:
2690 Control Category Description
2692 ======= ======== ===========
2693 &L Justification Left
2694 &C Center
2695 &R Right
2696 &P Information Page number
2698 &N Total number of pages
2699 &D Date
2700 &T Time
2701 &F File name
2702 &A Worksheet name
2703 &Z Workbook path
2704 &fontsize Font Font size
2706 &"font,style" Font name and style
2707 &U Single underline
2708 &E Double underline
2709 &S Strikethrough
2710 &X Superscript
2711 &Y Subscript
2712 && Miscellaneous Literal ampersand &
2714 Text in headers and footers can be justified (aligned) to the left,
2716 center and right by prefixing the text with the control characters &L,
2717 &C and &R.
2718 For example (with ASCII art representation of the results):
2720 $worksheet->set_header('&LHello');
2722 ---------------------------------------------------------------
2724 | |
2725 | Hello |
2726 | |
2727 $worksheet->set_header('&CHello');
2730 ---------------------------------------------------------------
2732 | |
2733 | Hello |
2734 | |
2735 $worksheet->set_header('&RHello');
2738 ---------------------------------------------------------------
2740 | |
2741 | Hello |
2742 | |
2743 For simple text, if you do not specify any justification the text will
2745 be centred. However, you must prefix the text with &C if you specify a
2746 font name or any other formatting:
2747 $worksheet->set_header('Hello');
2749 ---------------------------------------------------------------
2751 | |
2752 | Hello |
2753 | |
2754 You can have text in each of the justification regions:
2756 $worksheet->set_header('&LCiao&CBello&RCielo');
2758 ---------------------------------------------------------------
2760 | |
2761 | Ciao Bello Cielo |
2762 | |
2763 The information control characters act as variables that Excel will
2765 update as the workbook or worksheet changes. Times and dates are in the
2766 users default format:
2767 $worksheet->set_header('&CPage &P of &N');
2769 ---------------------------------------------------------------
2771 | |
2772 | Page 1 of 6 |
2773 | |
2774 $worksheet->set_header('&CUpdated at &T');
2777 ---------------------------------------------------------------
2779 | |
2780 | Updated at 12:30 PM |
2781 | |
2782 You can specify the font size of a section of the text by prefixing it
2784 with the control character &n where "n" is the font size:
2785 $worksheet1->set_header('&C&30Hello Big' );
2787 $worksheet2->set_header('&C&10Hello Small');
2788 You can specify the font of a section of the text by prefixing it with
2790 the control sequence "&"font,style"" where "fontname" is a font name
2791 such as "Courier New" or "Times New Roman" and "style" is one of the
2792 standard Windows font descriptions: "Regular", "Italic", "Bold" or
2793 "Bold Italic":
2794 $worksheet1->set_header('&C&"Courier New,Italic"Hello');
2796 $worksheet2->set_header('&C&"Courier New,Bold Italic"Hello');
2797 $worksheet3->set_header('&C&"Times New Roman,Regular"Hello');
2798 It is possible to combine all of these features together to create
2800 sophisticated headers and footers. As an aid to setting up complicated
2801 headers and footers you can record a page set-up as a macro in Excel
2802 and look at the format strings that VBA produces. Remember however that
2803 VBA uses two double quotes "" to indicate a single double quote. For
2804 the last example above the equivalent VBA code looks like this:
2805 .LeftHeader = ""
2807 .CenterHeader = "&""Times New Roman,Regular""Hello"
2808 .RightHeader = ""
2809 To include a single literal ampersand "&" in a header or footer you
2811 should use a double ampersand "&&":
2812 $worksheet1->set_header('&CCuriouser && Curiouser - Attorneys at Law');
2814 As stated above the margin parameter is optional. As with the other
2816 margins the value should be in inches. The default header and footer
2817 margin is 0.50 inch. The header and footer margin size can be set as
2818 follows:
2819 $worksheet->set_header('&CHello', 0.75);
2821 The header and footer margins are independent of the top and bottom
2823 margins.
2824 Note, the header or footer string must be less than 255 characters.
2826 Strings longer than this will not be written and a warning will be
2827 generated.
2828 On systems with "perl 5.8" and later the "set_header()" method can also
2830 handle Unicode strings in "UTF-8" format.
2831 $worksheet->set_header("&C\x{263a}")
2833 See, also the "headers.pl" program in the "examples" directory of the
2835 distribution.
2836 set_footer()
2838 The syntax of the "set_footer()" method is the same as "set_header()",
2839 see above.
2840 repeat_rows($first_row, $last_row)
2842 Set the number of rows to repeat at the top of each printed page.
2843 For large Excel documents it is often desirable to have the first row
2845 or rows of the worksheet print out at the top of each page. This can be
2846 achieved by using the "repeat_rows()" method. The parameters $first_row
2847 and $last_row are zero based. The $last_row parameter is optional if
2848 you only wish to specify one row:
2849 $worksheet1->repeat_rows(0); # Repeat the first row
2851 $worksheet2->repeat_rows(0, 1); # Repeat the first two rows
2852 repeat_columns($first_col, $last_col)
2854 Set the columns to repeat at the left hand side of each printed page.
2855 For large Excel documents it is often desirable to have the first
2857 column or columns of the worksheet print out at the left hand side of
2858 each page. This can be achieved by using the "repeat_columns()" method.
2859 The parameters $first_column and $last_column are zero based. The
2860 $last_column parameter is optional if you only wish to specify one
2861 column. You can also specify the columns using A1 column notation, see
2862 the note about "Cell notation".
2863 $worksheet1->repeat_columns(0); # Repeat the first column
2865 $worksheet2->repeat_columns(0, 1); # Repeat the first two columns
2866 $worksheet3->repeat_columns('A:A'); # Repeat the first column
2867 $worksheet4->repeat_columns('A:B'); # Repeat the first two columns
2868 hide_gridlines($option)
2870 This method is used to hide the gridlines on the screen and printed
2871 page. Gridlines are the lines that divide the cells on a worksheet.
2872 Screen and printed gridlines are turned on by default in an Excel
2873 worksheet. If you have defined your own cell borders you may wish to
2874 hide the default gridlines.
2875 $worksheet->hide_gridlines();
2877 The following values of $option are valid:
2879 0 : Don't hide gridlines
2881 1 : Hide printed gridlines only
2882 2 : Hide screen and printed gridlines
2883 If you don't supply an argument or use "undef" the default option is 1,
2885 i.e. only the printed gridlines are hidden.
2886 print_row_col_headers()
2888 Set the option to print the row and column headers on the printed page.
2889 An Excel worksheet looks something like the following;
2891 ------------------------------------------
2893 | | A | B | C | D | ...
2894 ------------------------------------------
2895 | 1 | | | | | ...
2896 | 2 | | | | | ...
2897 | 3 | | | | | ...
2898 | 4 | | | | | ...
2899 |...| ... | ... | ... | ... | ...
2900 The headers are the letters and numbers at the top and the left of the
2902 worksheet. Since these headers serve mainly as a indication of position
2903 on the worksheet they generally do not appear on the printed page. If
2904 you wish to have them printed you can use the "print_row_col_headers()"
2905 method :
2906 $worksheet->print_row_col_headers();
2908 Do not confuse these headers with page headers as described in the
2910 "set_header()" section above.
2911 print_area($first_row, $first_col, $last_row, $last_col)
2913 This method is used to specify the area of the worksheet that will be
2914 printed. All four parameters must be specified. You can also use A1
2915 notation, see the note about "Cell notation".
2916 $worksheet1->print_area('A1:H20'); # Cells A1 to H20
2918 $worksheet2->print_area(0, 0, 19, 7); # The same
2919 $worksheet2->print_area('A:H'); # Columns A to H if rows have data
2920 print_across()
2922 The "print_across" method is used to change the default print
2923 direction. This is referred to by Excel as the sheet "page order".
2924 $worksheet->print_across();
2926 The default page order is shown below for a worksheet that extends over
2928 4 pages. The order is called "down then across":
2929 [1] [3]
2931 [2] [4]
2932 However, by using the "print_across" method the print order will be
2934 changed to "across then down":
2935 [1] [2]
2937 [3] [4]
2938 fit_to_pages($width, $height)
2940 The "fit_to_pages()" method is used to fit the printed area to a
2941 specific number of pages both vertically and horizontally. If the
2942 printed area exceeds the specified number of pages it will be scaled
2943 down to fit. This guarantees that the printed area will always appear
2944 on the specified number of pages even if the page size or margins
2945 change.
2946 $worksheet1->fit_to_pages(1, 1); # Fit to 1x1 pages
2948 $worksheet2->fit_to_pages(2, 1); # Fit to 2x1 pages
2949 $worksheet3->fit_to_pages(1, 2); # Fit to 1x2 pages
2950 The print area can be defined using the "print_area()" method as
2952 described above.
2953 A common requirement is to fit the printed output to n pages wide but
2955 have the height be as long as necessary. To achieve this set the
2956 $height to zero or leave it blank:
2957 $worksheet1->fit_to_pages(1, 0); # 1 page wide and as long as necessary
2959 $worksheet2->fit_to_pages(1); # The same
2960 Note that although it is valid to use both "fit_to_pages()" and
2962 "set_print_scale()" on the same worksheet only one of these options can
2963 be active at a time. The last method call made will set the active
2964 option.
2965 Note that "fit_to_pages()" will override any manual page breaks that
2967 are defined in the worksheet.
2968 set_start_page($start_page)
2970 The "set_start_page()" method is used to set the number of the starting
2971 page when the worksheet is printed out. The default value is 1.
2972 $worksheet->set_start_page(2);
2974 set_print_scale($scale)
2976 Set the scale factor of the printed page. Scale factors in the range
2977 "10 <= $scale <= 400" are valid:
2978 $worksheet1->set_print_scale(50);
2980 $worksheet2->set_print_scale(75);
2981 $worksheet3->set_print_scale(300);
2982 $worksheet4->set_print_scale(400);
2983 The default scale factor is 100. Note, "set_print_scale()" does not
2985 affect the scale of the visible page in Excel. For that you should use
2986 "set_zoom()".
2987 Note also that although it is valid to use both "fit_to_pages()" and
2989 "set_print_scale()" on the same worksheet only one of these options can
2990 be active at a time. The last method call made will set the active
2991 option.
2992 set_h_pagebreaks(@breaks)
2994 Add horizontal page breaks to a worksheet. A page break causes all the
2995 data that follows it to be printed on the next page. Horizontal page
2996 breaks act between rows. To create a page break between rows 20 and 21
2997 you must specify the break at row 21. However in zero index notation
2998 this is actually row 20. So you can pretend for a small while that you
2999 are using 1 index notation:
3000 $worksheet1->set_h_pagebreaks(20); # Break between row 20 and 21
3002 The "set_h_pagebreaks()" method will accept a list of page breaks and
3004 you can call it more than once:
3005 $worksheet2->set_h_pagebreaks( 20, 40, 60, 80, 100); # Add breaks
3007 $worksheet2->set_h_pagebreaks(120, 140, 160, 180, 200); # Add some more
3008 Note: If you specify the "fit to page" option via the "fit_to_pages()"
3010 method it will override all manual page breaks.
3011 There is a silent limitation of about 1000 horizontal page breaks per
3013 worksheet in line with an Excel internal limitation.
3014 set_v_pagebreaks(@breaks)
3016 Add vertical page breaks to a worksheet. A page break causes all the
3017 data that follows it to be printed on the next page. Vertical page
3018 breaks act between columns. To create a page break between columns 20
3019 and 21 you must specify the break at column 21. However in zero index
3020 notation this is actually column 20. So you can pretend for a small
3021 while that you are using 1 index notation:
3022 $worksheet1->set_v_pagebreaks(20); # Break between column 20 and 21
3024 The "set_v_pagebreaks()" method will accept a list of page breaks and
3026 you can call it more than once:
3027 $worksheet2->set_v_pagebreaks( 20, 40, 60, 80, 100); # Add breaks
3029 $worksheet2->set_v_pagebreaks(120, 140, 160, 180, 200); # Add some more
3030 Note: If you specify the "fit to page" option via the "fit_to_pages()"
3032 method it will override all manual page breaks.
3033
3035 This section describes the methods and properties that are available
3036 for formatting cells in Excel. The properties of a cell that can be
3037 formatted include: fonts, colours, patterns, borders, alignment and
3038 number formatting.
3039 Creating and using a Format object
3041 Cell formatting is defined through a Format object. Format objects are
3042 created by calling the workbook "add_format()" method as follows:
3043 my $format1 = $workbook->add_format(); # Set properties later
3045 my $format2 = $workbook->add_format(%props); # Set at creation
3046 The format object holds all the formatting properties that can be
3048 applied to a cell, a row or a column. The process of setting these
3049 properties is discussed in the next section.
3050 Once a Format object has been constructed and its properties have been
3052 set it can be passed as an argument to the worksheet "write" methods as
3053 follows:
3054 $worksheet->write(0, 0, 'One', $format);
3056 $worksheet->write_string(1, 0, 'Two', $format);
3057 $worksheet->write_number(2, 0, 3, $format);
3058 $worksheet->write_blank(3, 0, $format);
3059 Formats can also be passed to the worksheet "set_row()" and
3061 "set_column()" methods to define the default property for a row or
3062 column.
3063 $worksheet->set_row(0, 15, $format);
3065 $worksheet->set_column(0, 0, 15, $format);
3066 Format methods and Format properties
3068 The following table shows the Excel format categories, the formatting
3069 properties that can be applied and the equivalent object method:
3070 Category Description Property Method Name
3072 -------- ----------- -------- -----------
3073 Font Font type font set_font()
3074 Font size size set_size()
3075 Font color color set_color()
3076 Bold bold set_bold()
3077 Italic italic set_italic()
3078 Underline underline set_underline()
3079 Strikeout font_strikeout set_font_strikeout()
3080 Super/Subscript font_script set_font_script()
3081 Outline font_outline set_font_outline()
3082 Shadow font_shadow set_font_shadow()
3083 Number Numeric format num_format set_num_format()
3085 Protection Lock cells locked set_locked()
3087 Hide formulas hidden set_hidden()
3088 Alignment Horizontal align align set_align()
3090 Vertical align valign set_align()
3091 Rotation rotation set_rotation()
3092 Text wrap text_wrap set_text_wrap()
3093 Justify last text_justlast set_text_justlast()
3094 Center across center_across set_center_across()
3095 Indentation indent set_indent()
3096 Shrink to fit shrink set_shrink()
3097 Pattern Cell pattern pattern set_pattern()
3099 Background color bg_color set_bg_color()
3100 Foreground color fg_color set_fg_color()
3101 Border Cell border border set_border()
3103 Bottom border bottom set_bottom()
3104 Top border top set_top()
3105 Left border left set_left()
3106 Right border right set_right()
3107 Border color border_color set_border_color()
3108 Bottom color bottom_color set_bottom_color()
3109 Top color top_color set_top_color()
3110 Left color left_color set_left_color()
3111 Right color right_color set_right_color()
3112 There are two ways of setting Format properties: by using the object
3114 method interface or by setting the property directly. For example, a
3115 typical use of the method interface would be as follows:
3116 my $format = $workbook->add_format();
3118 $format->set_bold();
3119 $format->set_color('red');
3120 By comparison the properties can be set directly by passing a hash of
3122 properties to the Format constructor:
3123 my $format = $workbook->add_format(bold => 1, color => 'red');
3125 or after the Format has been constructed by means of the
3127 "set_format_properties()" method as follows:
3128 my $format = $workbook->add_format();
3130 $format->set_format_properties(bold => 1, color => 'red');
3131 You can also store the properties in one or more named hashes and pass
3133 them to the required method:
3134 my %font = (
3136 font => 'Arial',
3137 size => 12,
3138 color => 'blue',
3139 bold => 1,
3140 );
3141 my %shading = (
3143 bg_color => 'green',
3144 pattern => 1,
3145 );
3146 my $format1 = $workbook->add_format(%font); # Font only
3149 my $format2 = $workbook->add_format(%font, %shading); # Font and shading
3150 The provision of two ways of setting properties might lead you to
3152 wonder which is the best way. The method mechanism may be better is you
3153 prefer setting properties via method calls (which the author did when
3154 the code was first written) otherwise passing properties to the
3155 constructor has proved to be a little more flexible and self
3156 documenting in practice. An additional advantage of working with
3157 property hashes is that it allows you to share formatting between
3158 workbook objects as shown in the example above.
3159 The Perl/Tk style of adding properties is also supported:
3161 my %font = (
3163 -font => 'Arial',
3164 -size => 12,
3165 -color => 'blue',
3166 -bold => 1,
3167 );
3168 Working with formats
3170 The default format is Arial 10 with all other properties off.
3171 Each unique format in Spreadsheet::WriteExcel must have a corresponding
3173 Format object. It isn't possible to use a Format with a write() method
3174 and then redefine the Format for use at a later stage. This is because
3175 a Format is applied to a cell not in its current state but in its final
3176 state. Consider the following example:
3177 my $format = $workbook->add_format();
3179 $format->set_bold();
3180 $format->set_color('red');
3181 $worksheet->write('A1', 'Cell A1', $format);
3182 $format->set_color('green');
3183 $worksheet->write('B1', 'Cell B1', $format);
3184 Cell A1 is assigned the Format $format which is initially set to the
3186 colour red. However, the colour is subsequently set to green. When
3187 Excel displays Cell A1 it will display the final state of the Format
3188 which in this case will be the colour green.
3189 In general a method call without an argument will turn a property on,
3191 for example:
3192 my $format1 = $workbook->add_format();
3194 $format1->set_bold(); # Turns bold on
3195 $format1->set_bold(1); # Also turns bold on
3196 $format1->set_bold(0); # Turns bold off
3197
3199 The Format object methods are described in more detail in the following
3200 sections. In addition, there is a Perl program called "formats.pl" in
3201 the "examples" directory of the WriteExcel distribution. This program
3202 creates an Excel workbook called "formats.xls" which contains examples
3203 of almost all the format types.
3204 The following Format methods are available:
3206 set_font()
3208 set_size()
3209 set_color()
3210 set_bold()
3211 set_italic()
3212 set_underline()
3213 set_font_strikeout()
3214 set_font_script()
3215 set_font_outline()
3216 set_font_shadow()
3217 set_num_format()
3218 set_locked()
3219 set_hidden()
3220 set_align()
3221 set_rotation()
3222 set_text_wrap()
3223 set_text_justlast()
3224 set_center_across()
3225 set_indent()
3226 set_shrink()
3227 set_pattern()
3228 set_bg_color()
3229 set_fg_color()
3230 set_border()
3231 set_bottom()
3232 set_top()
3233 set_left()
3234 set_right()
3235 set_border_color()
3236 set_bottom_color()
3237 set_top_color()
3238 set_left_color()
3239 set_right_color()
3240 The above methods can also be applied directly as properties. For
3242 example "$format->set_bold()" is equivalent to
3243 "$workbook->add_format(bold => 1)".
3244 set_format_properties(%properties)
3246 The properties of an existing Format object can be also be set by means
3247 of "set_format_properties()":
3248 my $format = $workbook->add_format();
3250 $format->set_format_properties(bold => 1, color => 'red');
3251 However, this method is here mainly for legacy reasons. It is
3253 preferable to set the properties in the format constructor:
3254 my $format = $workbook->add_format(bold => 1, color => 'red');
3256 set_font($fontname)
3258 Default state: Font is Arial
3259 Default action: None
3260 Valid args: Any valid font name
3261 Specify the font used:
3263 $format->set_font('Times New Roman');
3265 Excel can only display fonts that are installed on the system that it
3267 is running on. Therefore it is best to use the fonts that come as
3268 standard such as 'Arial', 'Times New Roman' and 'Courier New'. See also
3269 the Fonts worksheet created by formats.pl
3270 set_size()
3272 Default state: Font size is 10
3273 Default action: Set font size to 1
3274 Valid args: Integer values from 1 to as big as your screen.
3275 Set the font size. Excel adjusts the height of a row to accommodate the
3277 largest font size in the row. You can also explicitly specify the
3278 height of a row using the set_row() worksheet method.
3279 my $format = $workbook->add_format();
3281 $format->set_size(30);
3282 set_color()
3284 Default state: Excels default color, usually black
3285 Default action: Set the default color
3286 Valid args: Integers from 8..63 or the following strings:
3287 'black'
3288 'blue'
3289 'brown'
3290 'cyan'
3291 'gray'
3292 'green'
3293 'lime'
3294 'magenta'
3295 'navy'
3296 'orange'
3297 'pink'
3298 'purple'
3299 'red'
3300 'silver'
3301 'white'
3302 'yellow'
3303 Set the font colour. The "set_color()" method is used as follows:
3305 my $format = $workbook->add_format();
3307 $format->set_color('red');
3308 $worksheet->write(0, 0, 'wheelbarrow', $format);
3309 Note: The "set_color()" method is used to set the colour of the font in
3311 a cell. To set the colour of a cell use the "set_bg_color()" and
3312 "set_pattern()" methods.
3313 For additional examples see the 'Named colors' and 'Standard colors'
3315 worksheets created by formats.pl in the examples directory.
3316 See also "COLOURS IN EXCEL".
3318 set_bold()
3320 Default state: bold is off
3321 Default action: Turn bold on
3322 Valid args: 0, 1 [1]
3323 Set the bold property of the font:
3325 $format->set_bold(); # Turn bold on
3327 [1] Actually, values in the range 100..1000 are also valid. 400 is
3329 normal, 700 is bold and 1000 is very bold indeed. It is probably best
3330 to set the value to 1 and use normal bold.
3331 set_italic()
3333 Default state: Italic is off
3334 Default action: Turn italic on
3335 Valid args: 0, 1
3336 Set the italic property of the font:
3338 $format->set_italic(); # Turn italic on
3340 set_underline()
3342 Default state: Underline is off
3343 Default action: Turn on single underline
3344 Valid args: 0 = No underline
3345 1 = Single underline
3346 2 = Double underline
3347 33 = Single accounting underline
3348 34 = Double accounting underline
3349 Set the underline property of the font.
3351 $format->set_underline(); # Single underline
3353 set_font_strikeout()
3355 Default state: Strikeout is off
3356 Default action: Turn strikeout on
3357 Valid args: 0, 1
3358 Set the strikeout property of the font.
3360 set_font_script()
3362 Default state: Super/Subscript is off
3363 Default action: Turn Superscript on
3364 Valid args: 0 = Normal
3365 1 = Superscript
3366 2 = Subscript
3367 Set the superscript/subscript property of the font. This format is
3369 currently not very useful.
3370 set_font_outline()
3372 Default state: Outline is off
3373 Default action: Turn outline on
3374 Valid args: 0, 1
3375 Macintosh only.
3377 set_font_shadow()
3379 Default state: Shadow is off
3380 Default action: Turn shadow on
3381 Valid args: 0, 1
3382 Macintosh only.
3384 set_num_format()
3386 Default state: General format
3387 Default action: Format index 1
3388 Valid args: See the following table
3389 This method is used to define the numerical format of a number in
3391 Excel. It controls whether a number is displayed as an integer, a
3392 floating point number, a date, a currency value or some other user
3393 defined format.
3394 The numerical format of a cell can be specified by using a format
3396 string or an index to one of Excel's built-in formats:
3397 my $format1 = $workbook->add_format();
3399 my $format2 = $workbook->add_format();
3400 $format1->set_num_format('d mmm yyyy'); # Format string
3401 $format2->set_num_format(0x0f); # Format index
3402 $worksheet->write(0, 0, 36892.521, $format1); # 1 Jan 2001
3404 $worksheet->write(0, 0, 36892.521, $format2); # 1-Jan-01
3405 Using format strings you can define very sophisticated formatting of
3407 numbers.
3408 $format01->set_num_format('0.000');
3410 $worksheet->write(0, 0, 3.1415926, $format01); # 3.142
3411 $format02->set_num_format('#,##0');
3413 $worksheet->write(1, 0, 1234.56, $format02); # 1,235
3414 $format03->set_num_format('#,##0.00');
3416 $worksheet->write(2, 0, 1234.56, $format03); # 1,234.56
3417 $format04->set_num_format('$0.00');
3419 $worksheet->write(3, 0, 49.99, $format04); # $49.99
3420 # Note you can use other currency symbols such as the pound or yen as well.
3422 # Other currencies may require the use of Unicode.
3423 $format07->set_num_format('mm/dd/yy');
3425 $worksheet->write(6, 0, 36892.521, $format07); # 01/01/01
3426 $format08->set_num_format('mmm d yyyy');
3428 $worksheet->write(7, 0, 36892.521, $format08); # Jan 1 2001
3429 $format09->set_num_format('d mmmm yyyy');
3431 $worksheet->write(8, 0, 36892.521, $format09); # 1 January 2001
3432 $format10->set_num_format('dd/mm/yyyy hh:mm AM/PM');
3434 $worksheet->write(9, 0, 36892.521, $format10); # 01/01/2001 12:30 AM
3435 $format11->set_num_format('0 "dollar and" .00 "cents"');
3437 $worksheet->write(10, 0, 1.87, $format11); # 1 dollar and .87 cents
3438 # Conditional formatting
3440 $format12->set_num_format('[Green]General;[Red]-General;General');
3441 $worksheet->write(11, 0, 123, $format12); # > 0 Green
3442 $worksheet->write(12, 0, -45, $format12); # < 0 Red
3443 $worksheet->write(13, 0, 0, $format12); # = 0 Default colour
3444 # Zip code
3446 $format13->set_num_format('00000');
3447 $worksheet->write(14, 0, '01209', $format13);
3448 The number system used for dates is described in "DATES AND TIME IN
3450 EXCEL".
3451 The colour format should have one of the following values:
3453 [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
3455 Alternatively you can specify the colour based on a colour index as
3457 follows: "[Color n]", where n is a standard Excel colour index - 7. See
3458 the 'Standard colors' worksheet created by formats.pl.
3459 For more information refer to the documentation on formatting in the
3461 "docs" directory of the Spreadsheet::WriteExcel distro, the Excel on-
3462 line help or
3463 <http://office.microsoft.com/en-gb/assistance/HP051995001033.aspx>.
3464 You should ensure that the format string is valid in Excel prior to
3466 using it in WriteExcel.
3467 Excel's built-in formats are shown in the following table:
3469 Index Index Format String
3471 0 0x00 General
3472 1 0x01 0
3473 2 0x02 0.00
3474 3 0x03 #,##0
3475 4 0x04 #,##0.00
3476 5 0x05 ($#,##0_);($#,##0)
3477 6 0x06 ($#,##0_);[Red]($#,##0)
3478 7 0x07 ($#,##0.00_);($#,##0.00)
3479 8 0x08 ($#,##0.00_);[Red]($#,##0.00)
3480 9 0x09 0%
3481 10 0x0a 0.00%
3482 11 0x0b 0.00E+00
3483 12 0x0c # ?/?
3484 13 0x0d # ??/??
3485 14 0x0e m/d/yy
3486 15 0x0f d-mmm-yy
3487 16 0x10 d-mmm
3488 17 0x11 mmm-yy
3489 18 0x12 h:mm AM/PM
3490 19 0x13 h:mm:ss AM/PM
3491 20 0x14 h:mm
3492 21 0x15 h:mm:ss
3493 22 0x16 m/d/yy h:mm
3494 .. .... ...........
3495 37 0x25 (#,##0_);(#,##0)
3496 38 0x26 (#,##0_);[Red](#,##0)
3497 39 0x27 (#,##0.00_);(#,##0.00)
3498 40 0x28 (#,##0.00_);[Red](#,##0.00)
3499 41 0x29 _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
3500 42 0x2a _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
3501 43 0x2b _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
3502 44 0x2c _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
3503 45 0x2d mm:ss
3504 46 0x2e [h]:mm:ss
3505 47 0x2f mm:ss.0
3506 48 0x30 ##0.0E+0
3507 49 0x31 @
3508 For examples of these formatting codes see the 'Numerical formats'
3510 worksheet created by formats.pl. See also the number_formats1.html and
3511 the number_formats2.html documents in the "docs" directory of the
3512 distro.
3513 Note 1. Numeric formats 23 to 36 are not documented by Microsoft and
3515 may differ in international versions.
3516 Note 2. In Excel 5 the dollar sign appears as a dollar sign. In Excel
3518 97-2000 it appears as the defined local currency symbol.
3519 Note 3. The red negative numeric formats display slightly differently
3521 in Excel 5 and Excel 97-2000.
3522 set_locked()
3524 Default state: Cell locking is on
3525 Default action: Turn locking on
3526 Valid args: 0, 1
3527 This property can be used to prevent modification of a cells contents.
3529 Following Excel's convention, cell locking is turned on by default.
3530 However, it only has an effect if the worksheet has been protected, see
3531 the worksheet "protect()" method.
3532 my $locked = $workbook->add_format();
3534 $locked->set_locked(1); # A non-op
3535 my $unlocked = $workbook->add_format();
3537 $locked->set_locked(0);
3538 # Enable worksheet protection
3540 $worksheet->protect();
3541 # This cell cannot be edited.
3543 $worksheet->write('A1', '=1+2', $locked);
3544 # This cell can be edited.
3546 $worksheet->write('A2', '=1+2', $unlocked);
3547 Note: This offers weak protection even with a password, see the note in
3549 relation to the "protect()" method.
3550 set_hidden()
3552 Default state: Formula hiding is off
3553 Default action: Turn hiding on
3554 Valid args: 0, 1
3555 This property is used to hide a formula while still displaying its
3557 result. This is generally used to hide complex calculations from end
3558 users who are only interested in the result. It only has an effect if
3559 the worksheet has been protected, see the worksheet "protect()" method.
3560 my $hidden = $workbook->add_format();
3562 $hidden->set_hidden();
3563 # Enable worksheet protection
3565 $worksheet->protect();
3566 # The formula in this cell isn't visible
3568 $worksheet->write('A1', '=1+2', $hidden);
3569 Note: This offers weak protection even with a password, see the note in
3571 relation to the "protect()" method.
3572 set_align()
3574 Default state: Alignment is off
3575 Default action: Left alignment
3576 Valid args: 'left' Horizontal
3577 'center'
3578 'right'
3579 'fill'
3580 'justify'
3581 'center_across'
3582 'top' Vertical
3584 'vcenter'
3585 'bottom'
3586 'vjustify'
3587 This method is used to set the horizontal and vertical text alignment
3589 within a cell. Vertical and horizontal alignments can be combined. The
3590 method is used as follows:
3591 my $format = $workbook->add_format();
3593 $format->set_align('center');
3594 $format->set_align('vcenter');
3595 $worksheet->set_row(0, 30);
3596 $worksheet->write(0, 0, 'X', $format);
3597 Text can be aligned across two or more adjacent cells using the
3599 "center_across" property. However, for genuine merged cells it is
3600 better to use the "merge_range()" worksheet method.
3601 The "vjustify" (vertical justify) option can be used to provide
3603 automatic text wrapping in a cell. The height of the cell will be
3604 adjusted to accommodate the wrapped text. To specify where the text
3605 wraps use the "set_text_wrap()" method.
3606 For further examples see the 'Alignment' worksheet created by
3608 formats.pl.
3609 set_center_across()
3611 Default state: Center across selection is off
3612 Default action: Turn center across on
3613 Valid args: 1
3614 Text can be aligned across two or more adjacent cells using the
3616 "set_center_across()" method. This is an alias for the
3617 "set_align('center_across')" method call.
3618 Only one cell should contain the text, the other cells should be blank:
3620 my $format = $workbook->add_format();
3622 $format->set_center_across();
3623 $worksheet->write(1, 1, 'Center across selection', $format);
3625 $worksheet->write_blank(1, 2, $format);
3626 See also the "merge1.pl" to "merge6.pl" programs in the "examples"
3628 directory and the "merge_range()" method.
3629 set_text_wrap()
3631 Default state: Text wrap is off
3632 Default action: Turn text wrap on
3633 Valid args: 0, 1
3634 Here is an example using the text wrap property, the escape character
3636 "\n" is used to indicate the end of line:
3637 my $format = $workbook->add_format();
3639 $format->set_text_wrap();
3640 $worksheet->write(0, 0, "It's\na bum\nwrap", $format);
3641 Excel will adjust the height of the row to accommodate the wrapped
3643 text. A similar effect can be obtained without newlines using the
3644 "set_align('vjustify')" method. See the "textwrap.pl" program in the
3645 "examples" directory.
3646 set_rotation()
3648 Default state: Text rotation is off
3649 Default action: None
3650 Valid args: Integers in the range -90 to 90 and 270
3651 Set the rotation of the text in a cell. The rotation can be any angle
3653 in the range -90 to 90 degrees.
3654 my $format = $workbook->add_format();
3656 $format->set_rotation(30);
3657 $worksheet->write(0, 0, 'This text is rotated', $format);
3658 The angle 270 is also supported. This indicates text where the letters
3660 run from top to bottom.
3661 set_indent()
3663 Default state: Text indentation is off
3664 Default action: Indent text 1 level
3665 Valid args: Positive integers
3666 This method can be used to indent text. The argument, which should be
3668 an integer, is taken as the level of indentation:
3669 my $format = $workbook->add_format();
3671 $format->set_indent(2);
3672 $worksheet->write(0, 0, 'This text is indented', $format);
3673 Indentation is a horizontal alignment property. It will override any
3675 other horizontal properties but it can be used in conjunction with
3676 vertical properties.
3677 set_shrink()
3679 Default state: Text shrinking is off
3680 Default action: Turn "shrink to fit" on
3681 Valid args: 1
3682 This method can be used to shrink text so that it fits in a cell.
3684 my $format = $workbook->add_format();
3686 $format->set_shrink();
3687 $worksheet->write(0, 0, 'Honey, I shrunk the text!', $format);
3688 set_text_justlast()
3690 Default state: Justify last is off
3691 Default action: Turn justify last on
3692 Valid args: 0, 1
3693 Only applies to Far Eastern versions of Excel.
3695 set_pattern()
3697 Default state: Pattern is off
3698 Default action: Solid fill is on
3699 Valid args: 0 .. 18
3700 Set the background pattern of a cell.
3702 Examples of the available patterns are shown in the 'Patterns'
3704 worksheet created by formats.pl. However, it is unlikely that you will
3705 ever need anything other than Pattern 1 which is a solid fill of the
3706 background color.
3707 set_bg_color()
3709 Default state: Color is off
3710 Default action: Solid fill.
3711 Valid args: See set_color()
3712 The "set_bg_color()" method can be used to set the background colour of
3714 a pattern. Patterns are defined via the "set_pattern()" method. If a
3715 pattern hasn't been defined then a solid fill pattern is used as the
3716 default.
3717 Here is an example of how to set up a solid fill in a cell:
3719 my $format = $workbook->add_format();
3721 $format->set_pattern(); # This is optional when using a solid fill
3723 $format->set_bg_color('green');
3725 $worksheet->write('A1', 'Ray', $format);
3726 For further examples see the 'Patterns' worksheet created by
3728 formats.pl.
3729 set_fg_color()
3731 Default state: Color is off
3732 Default action: Solid fill.
3733 Valid args: See set_color()
3734 The "set_fg_color()" method can be used to set the foreground colour of
3736 a pattern.
3737 For further examples see the 'Patterns' worksheet created by
3739 formats.pl.
3740 set_border()
3742 Also applies to: set_bottom()
3743 set_top()
3744 set_left()
3745 set_right()
3746 Default state: Border is off
3748 Default action: Set border type 1
3749 Valid args: 0-13, See below.
3750 A cell border is comprised of a border on the bottom, top, left and
3752 right. These can be set to the same value using "set_border()" or
3753 individually using the relevant method calls shown above.
3754 The following shows the border styles sorted by Spreadsheet::WriteExcel
3756 index number:
3757 Index Name Weight Style
3759 ===== ============= ====== ===========
3760 0 None 0
3761 1 Continuous 1 -----------
3762 2 Continuous 2 -----------
3763 3 Dash 1 - - - - - -
3764 4 Dot 1 . . . . . .
3765 5 Continuous 3 -----------
3766 6 Double 3 ===========
3767 7 Continuous 0 -----------
3768 8 Dash 2 - - - - - -
3769 9 Dash Dot 1 - . - . - .
3770 10 Dash Dot 2 - . - . - .
3771 11 Dash Dot Dot 1 - . . - . .
3772 12 Dash Dot Dot 2 - . . - . .
3773 13 SlantDash Dot 2 / - . / - .
3774 The following shows the borders sorted by style:
3776 Name Weight Style Index
3778 ============= ====== =========== =====
3779 Continuous 0 ----------- 7
3780 Continuous 1 ----------- 1
3781 Continuous 2 ----------- 2
3782 Continuous 3 ----------- 5
3783 Dash 1 - - - - - - 3
3784 Dash 2 - - - - - - 8
3785 Dash Dot 1 - . - . - . 9
3786 Dash Dot 2 - . - . - . 10
3787 Dash Dot Dot 1 - . . - . . 11
3788 Dash Dot Dot 2 - . . - . . 12
3789 Dot 1 . . . . . . 4
3790 Double 3 =========== 6
3791 None 0 0
3792 SlantDash Dot 2 / - . / - . 13
3793 The following shows the borders in the order shown in the Excel Dialog.
3795 Index Style Index Style
3797 ===== ===== ===== =====
3798 0 None 12 - . . - . .
3799 7 ----------- 13 / - . / - .
3800 4 . . . . . . 10 - . - . - .
3801 11 - . . - . . 8 - - - - - -
3802 9 - . - . - . 2 -----------
3803 3 - - - - - - 5 -----------
3804 1 ----------- 6 ===========
3805 Examples of the available border styles are shown in the 'Borders'
3807 worksheet created by formats.pl.
3808 set_border_color()
3810 Also applies to: set_bottom_color()
3811 set_top_color()
3812 set_left_color()
3813 set_right_color()
3814 Default state: Color is off
3816 Default action: Undefined
3817 Valid args: See set_color()
3818 Set the colour of the cell borders. A cell border is comprised of a
3820 border on the bottom, top, left and right. These can be set to the same
3821 colour using "set_border_color()" or individually using the relevant
3822 method calls shown above. Examples of the border styles and colours are
3823 shown in the 'Borders' worksheet created by formats.pl.
3824 copy($format)
3826 This method is used to copy all of the properties from one Format
3827 object to another:
3828 my $lorry1 = $workbook->add_format();
3830 $lorry1->set_bold();
3831 $lorry1->set_italic();
3832 $lorry1->set_color('red'); # lorry1 is bold, italic and red
3833 my $lorry2 = $workbook->add_format();
3835 $lorry2->copy($lorry1);
3836 $lorry2->set_color('yellow'); # lorry2 is bold, italic and yellow
3837 The "copy()" method is only useful if you are using the method
3839 interface to Format properties. It generally isn't required if you are
3840 setting Format properties directly using hashes.
3841 Note: this is not a copy constructor, both objects must exist prior to
3843 copying.
3844
3846 The following is a brief introduction to handling Unicode in
3847 "Spreadsheet::WriteExcel".
3848 For a more general introduction to Unicode handling in Perl see
3850 perlunitut and perluniintro.
3851 When using Spreadsheet::WriteExcel the best and easiest way to write
3853 unicode strings to an Excel file is to use "UTF-8" encoded strings and
3854 perl 5.8 (or later). Spreadsheet::WriteExcel also allows you to write
3855 unicode strings using older perls but it generally requires more work,
3856 as explained below.
3857 Internally, Excel encodes unicode data as "UTF-16LE" (where LE means
3859 little-endian). If you are using perl 5.8+ then Spreadsheet::WriteExcel
3860 will convert "UTF-8" strings to "UTF-16LE" when required. No further
3861 intervention is required from the programmer, for example:
3862 # perl 5.8+ example:
3864 my $smiley = "\x{263A}";
3865 $worksheet->write('A1', 'Hello world'); # ASCII
3867 $worksheet->write('A2', $smiley); # UTF-8
3868 Spreadsheet::WriteExcel also lets you write unicode data as "UTF-16".
3870 Since the majority of CPAN modules default to "UTF-16BE" (big-endian)
3871 Spreadsheet::WriteExcel also uses "UTF-16BE" and converts it internally
3872 to "UTF-16LE":
3873 # perl 5.005 example:
3875 my $smiley = pack 'n', 0x263A;
3876 $worksheet->write ('A3', 'Hello world'); # ASCII
3878 $worksheet->write_utf16be_string('A4', $smiley); # UTF-16
3879 Although the above examples look similar there is an important
3881 difference. With "uft8" and perl 5.8+ Spreadsheet::WriteExcel treats
3882 "UTF-8" strings in exactly the same way as any other string. However,
3883 with "UTF16" data we need to distinguish it from other strings either
3884 by calling a separate function or by passing an additional flag to
3885 indicate the data type.
3886 If you are dealing with non-ASCII characters that aren't in "UTF-8"
3888 then perl 5.8+ provides useful tools in the guise of the "Encode"
3889 module to help you to convert to the required format. For example:
3890 use Encode 'decode';
3892 my $string = 'some string with koi8-r characters';
3894 $string = decode('koi8-r', $string); # koi8-r to utf8
3895 Alternatively you can read data from an encoded file and convert it to
3897 "UTF-8" as you read it in:
3898 my $file = 'unicode_koi8r.txt';
3900 open FH, '<:encoding(koi8-r)', $file or die "Couldn't open $file: $!\n";
3901 my $row = 0;
3903 while (<FH>) {
3904 # Data read in is now in utf8 format.
3905 chomp;
3906 $worksheet->write($row++, 0, $_);
3907 }
3908 These methodologies are explained in more detail in perlunitut,
3910 perluniintro and perlunicode.
3911 See also the "unicode_*.pl" programs in the examples directory of the
3913 distro.
3914
3916 Excel provides a colour palette of 56 colours. In
3917 Spreadsheet::WriteExcel these colours are accessed via their palette
3918 index in the range 8..63. This index is used to set the colour of
3919 fonts, cell patterns and cell borders. For example:
3920 my $format = $workbook->add_format(
3922 color => 12, # index for blue
3923 font => 'Arial',
3924 size => 12,
3925 bold => 1,
3926 );
3927 The most commonly used colours can also be accessed by name. The name
3929 acts as a simple alias for the colour index:
3930 black => 8
3932 blue => 12
3933 brown => 16
3934 cyan => 15
3935 gray => 23
3936 green => 17
3937 lime => 11
3938 magenta => 14
3939 navy => 18
3940 orange => 53
3941 pink => 33
3942 purple => 20
3943 red => 10
3944 silver => 22
3945 white => 9
3946 yellow => 13
3947 For example:
3949 my $font = $workbook->add_format(color => 'red');
3951 Users of VBA in Excel should note that the equivalent colour indices
3953 are in the range 1..56 instead of 8..63.
3954 If the default palette does not provide a required colour you can
3956 override one of the built-in values. This is achieved by using the
3957 "set_custom_color()" workbook method to adjust the RGB (red green blue)
3958 components of the colour:
3959 my $ferrari = $workbook->set_custom_color(40, 216, 12, 12);
3961 my $format = $workbook->add_format(
3963 bg_color => $ferrari,
3964 pattern => 1,
3965 border => 1
3966 );
3967 $worksheet->write_blank('A1', $format);
3969 The default Excel colour palette is shown in "palette.html" in the
3971 "docs" directory of the distro. You can generate an Excel version of
3972 the palette using "colors.pl" in the "examples" directory.
3973
3975 There are two important things to understand about dates and times in
3976 Excel:
3977 1 A date/time in Excel is a real number plus an Excel number format.
3979 2 Spreadsheet::WriteExcel doesn't automatically convert date/time
3980 strings in "write()" to an Excel date/time.
3981 These two points are explained in more detail below along with some
3983 suggestions on how to convert times and dates to the required format.
3984 An Excel date/time is a number plus a format
3986 If you write a date string with "write()" then all you will get is a
3987 string:
3988 $worksheet->write('A1', '02/03/04'); # !! Writes a string not a date. !!
3990 Dates and times in Excel are represented by real numbers, for example
3992 "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
3993 The integer part of the number stores the number of days since the
3995 epoch and the fractional part stores the percentage of the day.
3996 A date or time in Excel is just like any other number. To have the
3998 number display as a date you must apply an Excel number format to it.
3999 Here are some examples.
4000 #!/usr/bin/perl -w
4002 use strict;
4004 use Spreadsheet::WriteExcel;
4005 my $workbook = Spreadsheet::WriteExcel->new('date_examples.xls');
4007 my $worksheet = $workbook->add_worksheet();
4008 $worksheet->set_column('A:A', 30); # For extra visibility.
4010 my $number = 39506.5;
4012 $worksheet->write('A1', $number); # 39506.5
4014 my $format2 = $workbook->add_format(num_format => 'dd/mm/yy');
4016 $worksheet->write('A2', $number , $format2); # 28/02/08
4017 my $format3 = $workbook->add_format(num_format => 'mm/dd/yy');
4019 $worksheet->write('A3', $number , $format3); # 02/28/08
4020 my $format4 = $workbook->add_format(num_format => 'd-m-yyyy');
4022 $worksheet->write('A4', $number , $format4); # 28-2-2008
4023 my $format5 = $workbook->add_format(num_format => 'dd/mm/yy hh:mm');
4025 $worksheet->write('A5', $number , $format5); # 28/02/08 12:00
4026 my $format6 = $workbook->add_format(num_format => 'd mmm yyyy');
4028 $worksheet->write('A6', $number , $format6); # 28 Feb 2008
4029 my $format7 = $workbook->add_format(num_format => 'mmm d yyyy hh:mm AM/PM');
4031 $worksheet->write('A7', $number , $format7); # Feb 28 2008 12:00 PM
4032 Spreadsheet::WriteExcel doesn't automatically convert date/time strings
4034 Spreadsheet::WriteExcel doesn't automatically convert input date
4035 strings into Excel's formatted date numbers due to the large number of
4036 possible date formats and also due to the possibility of
4037 misinterpretation.
4038 For example, does "02/03/04" mean March 2 2004, February 3 2004 or even
4040 March 4 2002.
4041 Therefore, in order to handle dates you will have to convert them to
4043 numbers and apply an Excel format. Some methods for converting dates
4044 are listed in the next section.
4045 The most direct way is to convert your dates to the ISO8601
4047 "yyyy-mm-ddThh:mm:ss.sss" date format and use the "write_date_time()"
4048 worksheet method:
4049 $worksheet->write_date_time('A2', '2001-01-01T12:20', $format);
4051 See the "write_date_time()" section of the documentation for more
4053 details.
4054 A general methodology for handling date strings with
4056 "write_date_time()" is:
4057 1. Identify incoming date/time strings with a regex.
4059 2. Extract the component parts of the date/time using the same regex.
4060 3. Convert the date/time to the ISO8601 format.
4061 4. Write the date/time using write_date_time() and a number format.
4062 Here is an example:
4064 #!/usr/bin/perl -w
4066 use strict;
4068 use Spreadsheet::WriteExcel;
4069 my $workbook = Spreadsheet::WriteExcel->new('example.xls');
4071 my $worksheet = $workbook->add_worksheet();
4072 # Set the default format for dates.
4074 my $date_format = $workbook->add_format(num_format => 'mmm d yyyy');
4075 # Increase column width to improve visibility of data.
4077 $worksheet->set_column('A:C', 20);
4078 # Simulate reading from a data source.
4080 my $row = 0;
4081 while (<DATA>) {
4083 chomp;
4084 my $col = 0;
4086 my @data = split ' ';
4087 for my $item (@data) {
4089 # Match dates in the following formats: d/m/yy, d/m/yyyy
4091 if ($item =~ qr[^(\d{1,2})/(\d{1,2})/(\d{4})$]) {
4092 # Change to the date format required by write_date_time().
4094 my $date = sprintf "%4d-%02d-%02dT", $3, $2, $1;
4095 $worksheet->write_date_time($row, $col++, $date, $date_format);
4097 }
4098 else {
4099 # Just plain data
4100 $worksheet->write($row, $col++, $item);
4101 }
4102 }
4103 $row++;
4104 }
4105 __DATA__
4107 Item Cost Date
4108 Book 10 1/9/2007
4109 Beer 4 12/9/2007
4110 Bed 500 5/10/2007
4111 For a slightly more advanced solution you can modify the "write()"
4113 method to handle date formats of your choice via the
4114 "add_write_handler()" method. See the "add_write_handler()" section of
4115 the docs and the write_handler3.pl and write_handler4.pl programs in
4116 the examples directory of the distro.
4117 Converting dates and times to an Excel date or time
4119 The "write_date_time()" method above is just one way of handling dates
4120 and times.
4121 The Spreadsheet::WriteExcel::Utility module which is included in the
4123 distro has date/time handling functions:
4124 use Spreadsheet::WriteExcel::Utility;
4126 $date = xl_date_list(2002, 1, 1); # 37257
4128 $date = xl_parse_date("11 July 1997"); # 35622
4129 $time = xl_parse_time('3:21:36 PM'); # 0.64
4130 $date = xl_decode_date_EU("13 May 2002"); # 37389
4131 Note: some of these functions require additional CPAN modules.
4133 For date conversions using the CPAN "DateTime" framework see
4135 DateTime::Format::Excel
4136 <http://search.cpan.org/search?dist=DateTime-Format-Excel>.
4137
4139 Excel allows you to group rows or columns so that they can be hidden or
4140 displayed with a single mouse click. This feature is referred to as
4141 outlines.
4142 Outlines can reduce complex data down to a few salient sub-totals or
4144 summaries.
4145 This feature is best viewed in Excel but the following is an ASCII
4147 representation of what a worksheet with three outlines might look like.
4148 Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at
4149 level 1. The lines at the left hand side are called outline level bars.
4150 ------------------------------------------
4152 1 2 3 | | A | B | C | D | ...
4153 ------------------------------------------
4154 _ | 1 | A | | | | ...
4155 | _ | 2 | B | | | | ...
4156 | | | 3 | (C) | | | | ...
4157 | | | 4 | (D) | | | | ...
4158 | - | 5 | E | | | | ...
4159 | _ | 6 | F | | | | ...
4160 | | | 7 | (G) | | | | ...
4161 | | | 8 | (H) | | | | ...
4162 | - | 9 | I | | | | ...
4163 - | . | ... | ... | ... | ... | ...
4164 Clicking the minus sign on each of the level 2 outlines will collapse
4166 and hide the data as shown in the next figure. The minus sign changes
4167 to a plus sign to indicate that the data in the outline is hidden.
4168 ------------------------------------------
4170 1 2 3 | | A | B | C | D | ...
4171 ------------------------------------------
4172 _ | 1 | A | | | | ...
4173 | | 2 | B | | | | ...
4174 | + | 5 | E | | | | ...
4175 | | 6 | F | | | | ...
4176 | + | 9 | I | | | | ...
4177 - | . | ... | ... | ... | ... | ...
4178 Clicking on the minus sign on the level 1 outline will collapse the
4180 remaining rows as follows:
4181 ------------------------------------------
4183 1 2 3 | | A | B | C | D | ...
4184 ------------------------------------------
4185 | 1 | A | | | | ...
4186 + | . | ... | ... | ... | ... | ...
4187 Grouping in "Spreadsheet::WriteExcel" is achieved by setting the
4189 outline level via the "set_row()" and "set_column()" worksheet methods:
4190 set_row($row, $height, $format, $hidden, $level, $collapsed)
4192 set_column($first_col, $last_col, $width, $format, $hidden, $level, $collapsed)
4193 The following example sets an outline level of 1 for rows 1 and 2
4195 (zero-indexed) and columns B to G. The parameters $height and $XF are
4196 assigned default values since they are undefined:
4197 $worksheet->set_row(1, undef, undef, 0, 1);
4199 $worksheet->set_row(2, undef, undef, 0, 1);
4200 $worksheet->set_column('B:G', undef, undef, 0, 1);
4201 Excel allows up to 7 outline levels. Therefore the $level parameter
4203 should be in the range "0 <= $level <= 7".
4204 Rows and columns can be collapsed by setting the $hidden flag for the
4206 hidden rows/columns and setting the $collapsed flag for the row/column
4207 that has the collapsed "+" symbol:
4208 $worksheet->set_row(1, undef, undef, 1, 1);
4210 $worksheet->set_row(2, undef, undef, 1, 1);
4211 $worksheet->set_row(3, undef, undef, 0, 0, 1); # Collapsed flag.
4212 $worksheet->set_column('B:G', undef, undef, 1, 1);
4214 $worksheet->set_column('H:H', undef, undef, 0, 0, 1); # Collapsed flag.
4215 Note: Setting the $collapsed flag is particularly important for
4217 compatibility with OpenOffice.org and Gnumeric.
4218 For a more complete example see the "outline.pl" and
4220 "outline_collapsed.pl" programs in the examples directory of the
4221 distro.
4222 Some additional outline properties can be set via the
4224 "outline_settings()" worksheet method, see above.
4225
4227 Data validation is a feature of Excel which allows you to restrict the
4228 data that a users enters in a cell and to display help and warning
4229 messages. It also allows you to restrict input to values in a drop down
4230 list.
4231 A typical use case might be to restrict data in a cell to integer
4233 values in a certain range, to provide a help message to indicate the
4234 required value and to issue a warning if the input data doesn't meet
4235 the stated criteria. In Spreadsheet::WriteExcel we could do that as
4236 follows:
4237 $worksheet->data_validation('B3',
4239 {
4240 validate => 'integer',
4241 criteria => 'between',
4242 minimum => 1,
4243 maximum => 100,
4244 input_title => 'Input an integer:',
4245 input_message => 'Between 1 and 100',
4246 error_message => 'Sorry, try again.',
4247 });
4248 The above example would look like this in Excel:
4250 <http://homepage.eircom.net/~jmcnamara/perl/data_validation.jpg>.
4251 For more information on data validation see the following Microsoft
4253 support article "Description and examples of data validation in Excel":
4254 <http://support.microsoft.com/kb/211485>.
4255 The following sections describe how to use the "data_validation()"
4257 method and its various options.
4258 data_validation($row, $col, { parameter => 'value', ... })
4260 The "data_validation()" method is used to construct an Excel data
4261 validation.
4262 It can be applied to a single cell or a range of cells. You can pass 3
4264 parameters such as "($row, $col, {...})" or 5 parameters such as
4265 "($first_row, $first_col, $last_row, $last_col, {...})". You can also
4266 use "A1" style notation. For example:
4267 $worksheet->data_validation(0, 0, {...});
4269 $worksheet->data_validation(0, 0, 4, 1, {...});
4270 # Which are the same as:
4272 $worksheet->data_validation('A1', {...});
4274 $worksheet->data_validation('A1:B5', {...});
4275 See also the note about "Cell notation" for more information.
4277 The last parameter in "data_validation()" must be a hash ref containing
4279 the parameters that describe the type and style of the data validation.
4280 The allowable parameters are:
4281 validate
4283 criteria
4284 value | minimum | source
4285 maximum
4286 ignore_blank
4287 dropdown
4288 input_title
4290 input_message
4291 show_input
4292 error_title
4294 error_message
4295 error_type
4296 show_error
4297 These parameters are explained in the following sections. Most of the
4299 parameters are optional, however, you will generally require the three
4300 main options "validate", "criteria" and "value".
4301 $worksheet->data_validation('B3',
4303 {
4304 validate => 'integer',
4305 criteria => '>',
4306 value => 100,
4307 });
4308 The "data_validation" method returns:
4310 0 for success.
4312 -1 for insufficient number of arguments.
4313 -2 for row or column out of bounds.
4314 -3 for incorrect parameter or value.
4315 validate
4317 This parameter is passed in a hash ref to "data_validation()".
4318 The "validate" parameter is used to set the type of data that you wish
4320 to validate. It is always required and it has no default value.
4321 Allowable values are:
4322 any
4324 integer
4325 decimal
4326 list
4327 date
4328 time
4329 length
4330 custom
4331 • any is used to specify that the type of data is unrestricted. This
4333 is the same as not applying a data validation. It is only provided
4334 for completeness and isn't used very often in the context of
4335 Spreadsheet::WriteExcel.
4336 • integer restricts the cell to integer values. Excel refers to this
4338 as 'whole number'.
4339 validate => 'integer',
4341 criteria => '>',
4342 value => 100,
4343 • decimal restricts the cell to decimal values.
4345 validate => 'decimal',
4347 criteria => '>',
4348 value => 38.6,
4349 • list restricts the cell to a set of user specified values. These
4351 can be passed in an array ref or as a cell range (named ranges
4352 aren't currently supported):
4353 validate => 'list',
4355 value => ['open', 'high', 'close'],
4356 # Or like this:
4357 value => 'B1:B3',
4358 Excel requires that range references are only to cells on the same
4360 worksheet.
4361 • date restricts the cell to date values. Dates in Excel are
4363 expressed as integer values but you can also pass an ISO860 style
4364 string as used in "write_date_time()". See also "DATES AND TIME IN
4365 EXCEL" for more information about working with Excel's dates.
4366 validate => 'date',
4368 criteria => '>',
4369 value => 39653, # 24 July 2008
4370 # Or like this:
4371 value => '2008-07-24T',
4372 • time restricts the cell to time values. Times in Excel are
4374 expressed as decimal values but you can also pass an ISO860 style
4375 string as used in "write_date_time()". See also "DATES AND TIME IN
4376 EXCEL" for more information about working with Excel's times.
4377 validate => 'time',
4379 criteria => '>',
4380 value => 0.5, # Noon
4381 # Or like this:
4382 value => 'T12:00:00',
4383 • length restricts the cell data based on an integer string length.
4385 Excel refers to this as 'Text length'.
4386 validate => 'length',
4388 criteria => '>',
4389 value => 10,
4390 • custom restricts the cell based on an external Excel formula that
4392 returns a "TRUE/FALSE" value.
4393 validate => 'custom',
4395 value => '=IF(A10>B10,TRUE,FALSE)',
4396 criteria
4398 This parameter is passed in a hash ref to "data_validation()".
4399 The "criteria" parameter is used to set the criteria by which the data
4401 in the cell is validated. It is almost always required except for the
4402 "list" and "custom" validate options. It has no default value.
4403 Allowable values are:
4404 'between'
4406 'not between'
4407 'equal to' | '==' | '='
4408 'not equal to' | '!=' | '<>'
4409 'greater than' | '>'
4410 'less than' | '<'
4411 'greater than or equal to' | '>='
4412 'less than or equal to' | '<='
4413 You can either use Excel's textual description strings, in the first
4415 column above, or the more common operator alternatives. The following
4416 are equivalent:
4417 validate => 'integer',
4419 criteria => 'greater than',
4420 value => 100,
4421 validate => 'integer',
4423 criteria => '>',
4424 value => 100,
4425 The "list" and "custom" validate options don't require a "criteria". If
4427 you specify one it will be ignored.
4428 validate => 'list',
4430 value => ['open', 'high', 'close'],
4431 validate => 'custom',
4433 value => '=IF(A10>B10,TRUE,FALSE)',
4434 value | minimum | source
4436 This parameter is passed in a hash ref to "data_validation()".
4437 The "value" parameter is used to set the limiting value to which the
4439 "criteria" is applied. It is always required and it has no default
4440 value. You can also use the synonyms "minimum" or "source" to make the
4441 validation a little clearer and closer to Excel's description of the
4442 parameter:
4443 # Use 'value'
4445 validate => 'integer',
4446 criteria => '>',
4447 value => 100,
4448 # Use 'minimum'
4450 validate => 'integer',
4451 criteria => 'between',
4452 minimum => 1,
4453 maximum => 100,
4454 # Use 'source'
4456 validate => 'list',
4457 source => '$B$1:$B$3',
4458 maximum
4460 This parameter is passed in a hash ref to "data_validation()".
4461 The "maximum" parameter is used to set the upper limiting value when
4463 the "criteria" is either 'between' or 'not between':
4464 validate => 'integer',
4466 criteria => 'between',
4467 minimum => 1,
4468 maximum => 100,
4469 ignore_blank
4471 This parameter is passed in a hash ref to "data_validation()".
4472 The "ignore_blank" parameter is used to toggle on and off the 'Ignore
4474 blank' option in the Excel data validation dialog. When the option is
4475 on the data validation is not applied to blank data in the cell. It is
4476 on by default.
4477 ignore_blank => 0, # Turn the option off
4479 dropdown
4481 This parameter is passed in a hash ref to "data_validation()".
4482 The "dropdown" parameter is used to toggle on and off the 'In-cell
4484 dropdown' option in the Excel data validation dialog. When the option
4485 is on a dropdown list will be shown for "list" validations. It is on by
4486 default.
4487 dropdown => 0, # Turn the option off
4489 input_title
4491 This parameter is passed in a hash ref to "data_validation()".
4492 The "input_title" parameter is used to set the title of the input
4494 message that is displayed when a cell is entered. It has no default
4495 value and is only displayed if the input message is displayed. See the
4496 "input_message" parameter below.
4497 input_title => 'This is the input title',
4499 The maximum title length is 32 characters. UTF8 strings are handled
4501 automatically in perl 5.8 and later.
4502 input_message
4504 This parameter is passed in a hash ref to "data_validation()".
4505 The "input_message" parameter is used to set the input message that is
4507 displayed when a cell is entered. It has no default value.
4508 validate => 'integer',
4510 criteria => 'between',
4511 minimum => 1,
4512 maximum => 100,
4513 input_title => 'Enter the applied discount:',
4514 input_message => 'between 1 and 100',
4515 The message can be split over several lines using newlines, "\n" in
4517 double quoted strings.
4518 input_message => "This is\na test.",
4520 The maximum message length is 255 characters. UTF8 strings are handled
4522 automatically in perl 5.8 and later.
4523 show_input
4525 This parameter is passed in a hash ref to "data_validation()".
4526 The "show_input" parameter is used to toggle on and off the 'Show input
4528 message when cell is selected' option in the Excel data validation
4529 dialog. When the option is off an input message is not displayed even
4530 if it has been set using "input_message". It is on by default.
4531 show_input => 0, # Turn the option off
4533 error_title
4535 This parameter is passed in a hash ref to "data_validation()".
4536 The "error_title" parameter is used to set the title of the error
4538 message that is displayed when the data validation criteria is not met.
4539 The default error title is 'Microsoft Excel'.
4540 error_title => 'Input value is not valid',
4542 The maximum title length is 32 characters. UTF8 strings are handled
4544 automatically in perl 5.8 and later.
4545 error_message
4547 This parameter is passed in a hash ref to "data_validation()".
4548 The "error_message" parameter is used to set the error message that is
4550 displayed when a cell is entered. The default error message is "The
4551 value you entered is not valid.\nA user has restricted values that can
4552 be entered into the cell.".
4553 validate => 'integer',
4555 criteria => 'between',
4556 minimum => 1,
4557 maximum => 100,
4558 error_title => 'Input value is not valid',
4559 error_message => 'It should be an integer between 1 and 100',
4560 The message can be split over several lines using newlines, "\n" in
4562 double quoted strings.
4563 input_message => "This is\na test.",
4565 The maximum message length is 255 characters. UTF8 strings are handled
4567 automatically in perl 5.8 and later.
4568 error_type
4570 This parameter is passed in a hash ref to "data_validation()".
4571 The "error_type" parameter is used to specify the type of error dialog
4573 that is displayed. There are 3 options:
4574 'stop'
4576 'warning'
4577 'information'
4578 The default is 'stop'.
4580 show_error
4582 This parameter is passed in a hash ref to "data_validation()".
4583 The "show_error" parameter is used to toggle on and off the 'Show error
4585 alert after invalid data is entered' option in the Excel data
4586 validation dialog. When the option is off an error message is not
4587 displayed even if it has been set using "error_message". It is on by
4588 default.
4589 show_error => 0, # Turn the option off
4591 Data Validation Examples
4593 Example 1. Limiting input to an integer greater than a fixed value.
4594 $worksheet->data_validation('A1',
4596 {
4597 validate => 'integer',
4598 criteria => '>',
4599 value => 0,
4600 });
4601 Example 2. Limiting input to an integer greater than a fixed value
4603 where the value is referenced from a cell.
4604 $worksheet->data_validation('A2',
4606 {
4607 validate => 'integer',
4608 criteria => '>',
4609 value => '=E3',
4610 });
4611 Example 3. Limiting input to a decimal in a fixed range.
4613 $worksheet->data_validation('A3',
4615 {
4616 validate => 'decimal',
4617 criteria => 'between',
4618 minimum => 0.1,
4619 maximum => 0.5,
4620 });
4621 Example 4. Limiting input to a value in a dropdown list.
4623 $worksheet->data_validation('A4',
4625 {
4626 validate => 'list',
4627 source => ['open', 'high', 'close'],
4628 });
4629 Example 5. Limiting input to a value in a dropdown list where the list
4631 is specified as a cell range.
4632 $worksheet->data_validation('A5',
4634 {
4635 validate => 'list',
4636 source => '=E4:G4',
4637 });
4638 Example 6. Limiting input to a date in a fixed range.
4640 $worksheet->data_validation('A6',
4642 {
4643 validate => 'date',
4644 criteria => 'between',
4645 minimum => '2008-01-01T',
4646 maximum => '2008-12-12T',
4647 });
4648 Example 7. Displaying a message when the cell is selected.
4650 $worksheet->data_validation('A7',
4652 {
4653 validate => 'integer',
4654 criteria => 'between',
4655 minimum => 1,
4656 maximum => 100,
4657 input_title => 'Enter an integer:',
4658 input_message => 'between 1 and 100',
4659 });
4660 See also the "data_validate.pl" program in the examples directory of
4662 the distro.
4663
4665 The following relates to worksheet objects such as images, comments and
4666 charts.
4667 If you specify the height of a row that contains a worksheet object
4669 then Spreadsheet::WriteExcel will adjust the height of the object to
4670 maintain its default or user specified dimensions. In this way the
4671 object won't appear stretched or compressed in Excel.
4672 However, Excel can also adjust the height of a row automatically if it
4674 contains cells that have the text wrap property set or contain large
4675 fonts. In these cases the height of the row is unknown to
4676 Spreadsheet::WriteExcel at execution time and the scaling calculations
4677 it performs are incorrect. The effect of this is that the object is
4678 stretched with the row when it is displayed in Excel.
4679 In order to avoid this issue you should use the "set_row()" method to
4681 explicitly specify the height of any row that may otherwise be changed
4682 automatically by Excel.
4683
4685 Caveats
4686 The first thing to note is that there are still some outstanding issues
4687 with the implementation of formulas and functions:
4688 1. Writing a formula is much slower than writing the equivalent string.
4690 2. You cannot use array constants, i.e. {1;2;3}, in functions.
4691 3. Unary minus isn't supported.
4692 4. Whitespace is not preserved around operators.
4693 5. Named ranges are not supported.
4694 6. Array formulas are not supported.
4695 However, these constraints will be removed in future versions. They are
4697 here because of a trade-off between features and time. Also, it is
4698 possible to work around issue 1 using the "store_formula()" and
4699 "repeat_formula()" methods as described later in this section.
4700 Introduction
4702 The following is a brief introduction to formulas and functions in
4703 Excel and Spreadsheet::WriteExcel.
4704 A formula is a string that begins with an equals sign:
4706 '=A1+B1'
4708 '=AVERAGE(1, 2, 3)'
4709 The formula can contain numbers, strings, boolean values, cell
4711 references, cell ranges and functions. Named ranges are not supported.
4712 Formulas should be written as they appear in Excel, that is cells and
4713 functions must be in uppercase.
4714 Cells in Excel are referenced using the A1 notation system where the
4716 column is designated by a letter and the row by a number. Columns range
4717 from A to IV i.e. 0 to 255, rows range from 1 to 65536. The
4718 "Spreadsheet::WriteExcel::Utility" module that is included in the
4719 distro contains helper functions for dealing with A1 notation, for
4720 example:
4721 use Spreadsheet::WriteExcel::Utility;
4723 ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
4725 $str = xl_rowcol_to_cell(1, 2); # C2
4726 The Excel "$" notation in cell references is also supported. This
4728 allows you to specify whether a row or column is relative or absolute.
4729 This only has an effect if the cell is copied. The following examples
4730 show relative and absolute values.
4731 '=A1' # Column and row are relative
4733 '=$A1' # Column is absolute and row is relative
4734 '=A$1' # Column is relative and row is absolute
4735 '=$A$1' # Column and row are absolute
4736 Formulas can also refer to cells in other worksheets of the current
4738 workbook. For example:
4739 '=Sheet2!A1'
4741 '=Sheet2!A1:A5'
4742 '=Sheet2:Sheet3!A1'
4743 '=Sheet2:Sheet3!A1:A5'
4744 q{='Test Data'!A1}
4745 q{='Test Data1:Test Data2'!A1}
4746 The sheet reference and the cell reference are separated by "!" the
4748 exclamation mark symbol. If worksheet names contain spaces, commas or
4749 parentheses then Excel requires that the name is enclosed in single
4750 quotes as shown in the last two examples above. In order to avoid using
4751 a lot of escape characters you can use the quote operator "q{}" to
4752 protect the quotes. See "perlop" in the main Perl documentation. Only
4753 valid sheet names that have been added using the "add_worksheet()"
4754 method can be used in formulas. You cannot reference external
4755 workbooks.
4756 The following table lists the operators that are available in Excel's
4758 formulas. The majority of the operators are the same as Perl's,
4759 differences are indicated:
4760 Arithmetic operators:
4762 =====================
4763 Operator Meaning Example
4764 + Addition 1+2
4765 - Subtraction 2-1
4766 * Multiplication 2*3
4767 / Division 1/4
4768 ^ Exponentiation 2^3 # Equivalent to **
4769 - Unary minus -(1+2) # Not yet supported
4770 % Percent (Not modulus) 13% # Not supported, [1]
4771 Comparison operators:
4774 =====================
4775 Operator Meaning Example
4776 = Equal to A1 = B1 # Equivalent to ==
4777 <> Not equal to A1 <> B1 # Equivalent to !=
4778 > Greater than A1 > B1
4779 < Less than A1 < B1
4780 >= Greater than or equal to A1 >= B1
4781 <= Less than or equal to A1 <= B1
4782 String operator:
4785 ================
4786 Operator Meaning Example
4787 & Concatenation "Hello " & "World!" # [2]
4788 Reference operators:
4791 ====================
4792 Operator Meaning Example
4793 : Range operator A1:A4 # [3]
4794 , Union operator SUM(1, 2+2, B3) # [4]
4795 Notes:
4798 [1]: You can get a percentage with formatting and modulus with MOD().
4799 [2]: Equivalent to ("Hello " . "World!") in Perl.
4800 [3]: This range is equivalent to cells A1, A2, A3 and A4.
4801 [4]: The comma behaves like the list separator in Perl.
4802 The range and comma operators can have different symbols in non-English
4804 versions of Excel. These will be supported in a later version of
4805 Spreadsheet::WriteExcel. European users of Excel take note:
4806 $worksheet->write('A1', '=SUM(1; 2; 3)'); # Wrong!!
4808 $worksheet->write('A1', '=SUM(1, 2, 3)'); # Okay
4809 The following table lists all of the core functions supported by Excel
4811 5 and Spreadsheet::WriteExcel. Any additional functions that are
4812 available through the "Analysis ToolPak" or other add-ins are not
4813 supported. These functions have all been tested to verify that they
4814 work.
4815 ABS DB INDIRECT NORMINV SLN
4817 ACOS DCOUNT INFO NORMSDIST SLOPE
4818 ACOSH DCOUNTA INT NORMSINV SMALL
4819 ADDRESS DDB INTERCEPT NOT SQRT
4820 AND DEGREES IPMT NOW STANDARDIZE
4821 AREAS DEVSQ IRR NPER STDEV
4822 ASIN DGET ISBLANK NPV STDEVP
4823 ASINH DMAX ISERR ODD STEYX
4824 ATAN DMIN ISERROR OFFSET SUBSTITUTE
4825 ATAN2 DOLLAR ISLOGICAL OR SUBTOTAL
4826 ATANH DPRODUCT ISNA PEARSON SUM
4827 AVEDEV DSTDEV ISNONTEXT PERCENTILE SUMIF
4828 AVERAGE DSTDEVP ISNUMBER PERCENTRANK SUMPRODUCT
4829 BETADIST DSUM ISREF PERMUT SUMSQ
4830 BETAINV DVAR ISTEXT PI SUMX2MY2
4831 BINOMDIST DVARP KURT PMT SUMX2PY2
4832 CALL ERROR.TYPE LARGE POISSON SUMXMY2
4833 CEILING EVEN LEFT POWER SYD
4834 CELL EXACT LEN PPMT T
4835 CHAR EXP LINEST PROB TAN
4836 CHIDIST EXPONDIST LN PRODUCT TANH
4837 CHIINV FACT LOG PROPER TDIST
4838 CHITEST FALSE LOG10 PV TEXT
4839 CHOOSE FDIST LOGEST QUARTILE TIME
4840 CLEAN FIND LOGINV RADIANS TIMEVALUE
4841 CODE FINV LOGNORMDIST RAND TINV
4842 COLUMN FISHER LOOKUP RANK TODAY
4843 COLUMNS FISHERINV LOWER RATE TRANSPOSE
4844 COMBIN FIXED MATCH REGISTER.ID TREND
4845 CONCATENATE FLOOR MAX REPLACE TRIM
4846 CONFIDENCE FORECAST MDETERM REPT TRIMMEAN
4847 CORREL FREQUENCY MEDIAN RIGHT TRUE
4848 COS FTEST MID ROMAN TRUNC
4849 COSH FV MIN ROUND TTEST
4850 COUNT GAMMADIST MINUTE ROUNDDOWN TYPE
4851 COUNTA GAMMAINV MINVERSE ROUNDUP UPPER
4852 COUNTBLANK GAMMALN MIRR ROW VALUE
4853 COUNTIF GEOMEAN MMULT ROWS VAR
4854 COVAR GROWTH MOD RSQ VARP
4855 CRITBINOM HARMEAN MODE SEARCH VDB
4856 DATE HLOOKUP MONTH SECOND VLOOKUP
4857 DATEVALUE HOUR N SIGN WEEKDAY
4858 DAVERAGE HYPGEOMDIST NA SIN WEIBULL
4859 DAY IF NEGBINOMDIST SINH YEAR
4860 DAYS360 INDEX NORMDIST SKEW ZTEST
4861 You can also modify the module to support function names in the
4863 following languages: German, French, Spanish, Portuguese, Dutch,
4864 Finnish, Italian and Swedish. See the "function_locale.pl" program in
4865 the "examples" directory of the distro.
4866 For a general introduction to Excel's formulas and an explanation of
4868 the syntax of the function refer to the Excel help files or the
4869 following:
4870 <http://office.microsoft.com/en-us/assistance/CH062528031033.aspx>.
4871 If your formula doesn't work in Spreadsheet::WriteExcel try the
4873 following:
4874 1. Verify that the formula works in Excel (or Gnumeric or OpenOffice.org).
4876 2. Ensure that it isn't on the Caveats list shown above.
4877 3. Ensure that cell references and formula names are in uppercase.
4878 4. Ensure that you are using ':' as the range operator, A1:A4.
4879 5. Ensure that you are using ',' as the union operator, SUM(1,2,3).
4880 6. Ensure that the function is in the above table.
4881 If you go through steps 1-6 and you still have a problem, mail me.
4883 Improving performance when working with formulas
4885 Writing a large number of formulas with Spreadsheet::WriteExcel can be
4886 slow. This is due to the fact that each formula has to be parsed and
4887 with the current implementation this is computationally expensive.
4888 However, in a lot of cases the formulas that you write will be quite
4890 similar, for example:
4891 $worksheet->write_formula('B1', '=A1 * 3 + 50', $format);
4893 $worksheet->write_formula('B2', '=A2 * 3 + 50', $format);
4894 ...
4895 ...
4896 $worksheet->write_formula('B99', '=A999 * 3 + 50', $format);
4897 $worksheet->write_formula('B1000', '=A1000 * 3 + 50', $format);
4898 In this example the cell reference changes in iterations from "A1" to
4900 "A1000". The parser treats this variable as a token and arranges it
4901 according to predefined rules. However, since the parser is oblivious
4902 to the value of the token, it is essentially performing the same
4903 calculation 1000 times. This is inefficient.
4904 The way to avoid this inefficiency and thereby speed up the writing of
4906 formulas is to parse the formula once and then repeatedly substitute
4907 similar tokens.
4908 A formula can be parsed and stored via the "store_formula()" worksheet
4910 method. You can then use the "repeat_formula()" method to substitute
4911 $pattern, $replace pairs in the stored formula:
4912 my $formula = $worksheet->store_formula('=A1 * 3 + 50');
4914 for my $row (0..999) {
4916 $worksheet->repeat_formula($row, 1, $formula, $format, 'A1', 'A'.($row +1));
4917 }
4918 On an arbitrary test machine this method was 10 times faster than the
4920 brute force method shown above.
4921 For more information about how Spreadsheet::WriteExcel parses and
4923 stores formulas see the "Spreadsheet::WriteExcel::Formula" man page.
4924 It should be noted however that the overall speed of direct formula
4926 parsing will be improved in a future version.
4927
4929 See Spreadsheet::WriteExcel::Examples for a full list of examples.
4930 Example 1
4932 The following example shows some of the basic features of
4933 Spreadsheet::WriteExcel.
4934 #!/usr/bin/perl -w
4936 use strict;
4938 use Spreadsheet::WriteExcel;
4939 # Create a new workbook called simple.xls and add a worksheet
4941 my $workbook = Spreadsheet::WriteExcel->new('simple.xls');
4942 my $worksheet = $workbook->add_worksheet();
4943 # The general syntax is write($row, $column, $token). Note that row and
4945 # column are zero indexed
4946 # Write some text
4948 $worksheet->write(0, 0, 'Hi Excel!');
4949 # Write some numbers
4952 $worksheet->write(2, 0, 3); # Writes 3
4953 $worksheet->write(3, 0, 3.00000); # Writes 3
4954 $worksheet->write(4, 0, 3.00001); # Writes 3.00001
4955 $worksheet->write(5, 0, 3.14159); # TeX revision no.?
4956 # Write some formulas
4959 $worksheet->write(7, 0, '=A3 + A6');
4960 $worksheet->write(8, 0, '=IF(A5>3,"Yes", "No")');
4961 # Write a hyperlink
4964 $worksheet->write(10, 0, 'http://www.perl.com/');
4965 Example 2
4967 The following is a general example which demonstrates some features of
4968 working with multiple worksheets.
4969 #!/usr/bin/perl -w
4971 use strict;
4973 use Spreadsheet::WriteExcel;
4974 # Create a new Excel workbook
4976 my $workbook = Spreadsheet::WriteExcel->new('regions.xls');
4977 # Add some worksheets
4979 my $north = $workbook->add_worksheet('North');
4980 my $south = $workbook->add_worksheet('South');
4981 my $east = $workbook->add_worksheet('East');
4982 my $west = $workbook->add_worksheet('West');
4983 # Add a Format
4985 my $format = $workbook->add_format();
4986 $format->set_bold();
4987 $format->set_color('blue');
4988 # Add a caption to each worksheet
4990 foreach my $worksheet ($workbook->sheets()) {
4991 $worksheet->write(0, 0, 'Sales', $format);
4992 }
4993 # Write some data
4995 $north->write(0, 1, 200000);
4996 $south->write(0, 1, 100000);
4997 $east->write (0, 1, 150000);
4998 $west->write (0, 1, 100000);
4999 # Set the active worksheet
5001 $south->activate();
5002 # Set the width of the first column
5004 $south->set_column(0, 0, 20);
5005 # Set the active cell
5007 $south->set_selection(0, 1);
5008 Example 3
5010 This example shows how to use a conditional numerical format with
5011 colours to indicate if a share price has gone up or down.
5012 use strict;
5014 use Spreadsheet::WriteExcel;
5015 # Create a new workbook and add a worksheet
5017 my $workbook = Spreadsheet::WriteExcel->new('stocks.xls');
5018 my $worksheet = $workbook->add_worksheet();
5019 # Set the column width for columns 1, 2, 3 and 4
5021 $worksheet->set_column(0, 3, 15);
5022 # Create a format for the column headings
5025 my $header = $workbook->add_format();
5026 $header->set_bold();
5027 $header->set_size(12);
5028 $header->set_color('blue');
5029 # Create a format for the stock price
5032 my $f_price = $workbook->add_format();
5033 $f_price->set_align('left');
5034 $f_price->set_num_format('$0.00');
5035 # Create a format for the stock volume
5038 my $f_volume = $workbook->add_format();
5039 $f_volume->set_align('left');
5040 $f_volume->set_num_format('#,##0');
5041 # Create a format for the price change. This is an example of a
5044 # conditional format. The number is formatted as a percentage. If it is
5045 # positive it is formatted in green, if it is negative it is formatted
5046 # in red and if it is zero it is formatted as the default font colour
5047 # (in this case black). Note: the [Green] format produces an unappealing
5048 # lime green. Try [Color 10] instead for a dark green.
5049 #
5050 my $f_change = $workbook->add_format();
5051 $f_change->set_align('left');
5052 $f_change->set_num_format('[Green]0.0%;[Red]-0.0%;0.0%');
5053 # Write out the data
5056 $worksheet->write(0, 0, 'Company',$header);
5057 $worksheet->write(0, 1, 'Price', $header);
5058 $worksheet->write(0, 2, 'Volume', $header);
5059 $worksheet->write(0, 3, 'Change', $header);
5060 $worksheet->write(1, 0, 'Damage Inc.' );
5062 $worksheet->write(1, 1, 30.25, $f_price ); # $30.25
5063 $worksheet->write(1, 2, 1234567, $f_volume); # 1,234,567
5064 $worksheet->write(1, 3, 0.085, $f_change); # 8.5% in green
5065 $worksheet->write(2, 0, 'Dump Corp.' );
5067 $worksheet->write(2, 1, 1.56, $f_price ); # $1.56
5068 $worksheet->write(2, 2, 7564, $f_volume); # 7,564
5069 $worksheet->write(2, 3, -0.015, $f_change); # -1.5% in red
5070 $worksheet->write(3, 0, 'Rev Ltd.' );
5072 $worksheet->write(3, 1, 0.13, $f_price ); # $0.13
5073 $worksheet->write(3, 2, 321, $f_volume); # 321
5074 $worksheet->write(3, 3, 0, $f_change); # 0 in the font color (black)
5075 Example 4
5077 The following is a simple example of using functions.
5078 #!/usr/bin/perl -w
5080 use strict;
5082 use Spreadsheet::WriteExcel;
5083 # Create a new workbook and add a worksheet
5085 my $workbook = Spreadsheet::WriteExcel->new('stats.xls');
5086 my $worksheet = $workbook->add_worksheet('Test data');
5087 # Set the column width for columns 1
5089 $worksheet->set_column(0, 0, 20);
5090 # Create a format for the headings
5093 my $format = $workbook->add_format();
5094 $format->set_bold();
5095 # Write the sample data
5098 $worksheet->write(0, 0, 'Sample', $format);
5099 $worksheet->write(0, 1, 1);
5100 $worksheet->write(0, 2, 2);
5101 $worksheet->write(0, 3, 3);
5102 $worksheet->write(0, 4, 4);
5103 $worksheet->write(0, 5, 5);
5104 $worksheet->write(0, 6, 6);
5105 $worksheet->write(0, 7, 7);
5106 $worksheet->write(0, 8, 8);
5107 $worksheet->write(1, 0, 'Length', $format);
5109 $worksheet->write(1, 1, 25.4);
5110 $worksheet->write(1, 2, 25.4);
5111 $worksheet->write(1, 3, 24.8);
5112 $worksheet->write(1, 4, 25.0);
5113 $worksheet->write(1, 5, 25.3);
5114 $worksheet->write(1, 6, 24.9);
5115 $worksheet->write(1, 7, 25.2);
5116 $worksheet->write(1, 8, 24.8);
5117 # Write some statistical functions
5119 $worksheet->write(4, 0, 'Count', $format);
5120 $worksheet->write(4, 1, '=COUNT(B1:I1)');
5121 $worksheet->write(5, 0, 'Sum', $format);
5123 $worksheet->write(5, 1, '=SUM(B2:I2)');
5124 $worksheet->write(6, 0, 'Average', $format);
5126 $worksheet->write(6, 1, '=AVERAGE(B2:I2)');
5127 $worksheet->write(7, 0, 'Min', $format);
5129 $worksheet->write(7, 1, '=MIN(B2:I2)');
5130 $worksheet->write(8, 0, 'Max', $format);
5132 $worksheet->write(8, 1, '=MAX(B2:I2)');
5133 $worksheet->write(9, 0, 'Standard Deviation', $format);
5135 $worksheet->write(9, 1, '=STDEV(B2:I2)');
5136 $worksheet->write(10, 0, 'Kurtosis', $format);
5138 $worksheet->write(10, 1, '=KURT(B2:I2)');
5139 Example 5
5141 The following example converts a tab separated file called "tab.txt"
5142 into an Excel file called "tab.xls".
5143 #!/usr/bin/perl -w
5145 use strict;
5147 use Spreadsheet::WriteExcel;
5148 open (TABFILE, 'tab.txt') or die "tab.txt: $!";
5150 my $workbook = Spreadsheet::WriteExcel->new('tab.xls');
5152 my $worksheet = $workbook->add_worksheet();
5153 # Row and column are zero indexed
5155 my $row = 0;
5156 while (<TABFILE>) {
5158 chomp;
5159 # Split on single tab
5160 my @Fld = split('\t', $_);
5161 my $col = 0;
5163 foreach my $token (@Fld) {
5164 $worksheet->write($row, $col, $token);
5165 $col++;
5166 }
5167 $row++;
5168 }
5169 NOTE: This is a simple conversion program for illustrative purposes
5171 only. For converting a CSV or Tab separated or any other type of
5172 delimited text file to Excel I recommend the more rigorous csv2xls
5173 program that is part of H.Merijn Brand's Text::CSV_XS module distro.
5174 See the examples/csv2xls link here:
5176 <http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>.
5177 Additional Examples
5179 The following is a description of the example files that are provided
5180 in the standard Spreadsheet::WriteExcel distribution. They demonstrate
5181 the different features and options of the module. See
5182 Spreadsheet::WriteExcel::Examples for more details.
5183 Getting started
5185 ===============
5186 a_simple.pl A get started example with some basic features.
5187 demo.pl A demo of some of the available features.
5188 regions.pl A simple example of multiple worksheets.
5189 stats.pl Basic formulas and functions.
5190 formats.pl All the available formatting on several worksheets.
5191 bug_report.pl A template for submitting bug reports.
5192 Advanced
5195 ========
5196 autofilter.pl Examples of worksheet autofilters.
5197 autofit.pl Simulate Excel's autofit for column widths.
5198 bigfile.pl Write past the 7MB limit with OLE::Storage_Lite.
5199 cgi.pl A simple CGI program.
5200 chart_area.pl A demo of area style charts.
5201 chart_bar.pl A demo of bar (vertical histogram) style charts.
5202 chart_column.pl A demo of column (histogram) style charts.
5203 chart_line.pl A demo of line style charts.
5204 chart_pie.pl A demo of pie style charts.
5205 chart_scatter.pl A demo of scatter style charts.
5206 chart_stock.pl A demo of stock style charts.
5207 chess.pl An example of reusing formatting via properties.
5208 colors.pl A demo of the colour palette and named colours.
5209 comments1.pl Add comments to worksheet cells.
5210 comments2.pl Add comments with advanced options.
5211 copyformat.pl Example of copying a cell format.
5212 data_validate.pl An example of data validation and dropdown lists.
5213 date_time.pl Write dates and times with write_date_time().
5214 defined_name.pl Example of how to create defined names.
5215 diag_border.pl A simple example of diagonal cell borders.
5216 easter_egg.pl Expose the Excel97 flight simulator.
5217 filehandle.pl Examples of working with filehandles.
5218 formula_result.pl Formulas with user specified results.
5219 headers.pl Examples of worksheet headers and footers.
5220 hide_sheet.pl Simple example of hiding a worksheet.
5221 hyperlink1.pl Shows how to create web hyperlinks.
5222 hyperlink2.pl Examples of internal and external hyperlinks.
5223 images.pl Adding images to worksheets.
5224 indent.pl An example of cell indentation.
5225 merge1.pl A simple example of cell merging.
5226 merge2.pl A simple example of cell merging with formatting.
5227 merge3.pl Add hyperlinks to merged cells.
5228 merge4.pl An advanced example of merging with formatting.
5229 merge5.pl An advanced example of merging with formatting.
5230 merge6.pl An example of merging with Unicode strings.
5231 mod_perl1.pl A simple mod_perl 1 program.
5232 mod_perl2.pl A simple mod_perl 2 program.
5233 outline.pl An example of outlines and grouping.
5234 outline_collapsed.pl An example of collapsed outlines.
5235 panes.pl An examples of how to create panes.
5236 properties.pl Add document properties to a workbook.
5237 protection.pl Example of cell locking and formula hiding.
5238 repeat.pl Example of writing repeated formulas.
5239 right_to_left.pl Change default sheet direction to right to left.
5240 row_wrap.pl How to wrap data from one worksheet onto another.
5241 sales.pl An example of a simple sales spreadsheet.
5242 sendmail.pl Send an Excel email attachment using Mail::Sender.
5243 stats_ext.pl Same as stats.pl with external references.
5244 stocks.pl Demonstrates conditional formatting.
5245 tab_colors.pl Example of how to set worksheet tab colours.
5246 textwrap.pl Demonstrates text wrapping options.
5247 win32ole.pl A sample Win32::OLE example for comparison.
5248 write_arrays.pl Example of writing 1D or 2D arrays of data.
5249 write_handler1.pl Example of extending the write() method. Step 1.
5250 write_handler2.pl Example of extending the write() method. Step 2.
5251 write_handler3.pl Example of extending the write() method. Step 3.
5252 write_handler4.pl Example of extending the write() method. Step 4.
5253 write_to_scalar.pl Example of writing an Excel file to a Perl scalar.
5254 Unicode
5257 =======
5258 unicode_utf16.pl Simple example of using Unicode UTF16 strings.
5259 unicode_utf16_japan.pl Write Japanese Unicode strings using UTF-16.
5260 unicode_cyrillic.pl Write Russian Cyrillic strings using UTF-8.
5261 unicode_list.pl List the chars in a Unicode font.
5262 unicode_2022_jp.pl Japanese: ISO-2022-JP to utf8 in perl 5.8.
5263 unicode_8859_11.pl Thai: ISO-8859_11 to utf8 in perl 5.8.
5264 unicode_8859_7.pl Greek: ISO-8859_7 to utf8 in perl 5.8.
5265 unicode_big5.pl Chinese: BIG5 to utf8 in perl 5.8.
5266 unicode_cp1251.pl Russian: CP1251 to utf8 in perl 5.8.
5267 unicode_cp1256.pl Arabic: CP1256 to utf8 in perl 5.8.
5268 unicode_koi8r.pl Russian: KOI8-R to utf8 in perl 5.8.
5269 unicode_polish_utf8.pl Polish : UTF8 to utf8 in perl 5.8.
5270 unicode_shift_jis.pl Japanese: Shift JIS to utf8 in perl 5.8.
5271 Utility
5274 =======
5275 csv2xls.pl Program to convert a CSV file to an Excel file.
5276 tab2xls.pl Program to convert a tab separated file to xls.
5277 datecalc1.pl Convert Unix/Perl time to Excel time.
5278 datecalc2.pl Calculate an Excel date using Date::Calc.
5279 lecxe.pl Convert Excel to WriteExcel using Win32::OLE.
5280 Developer
5283 =========
5284 convertA1.pl Helper functions for dealing with A1 notation.
5285 function_locale.pl Add non-English function names to Formula.pm.
5286 writeA1.pl Example of how to extend the module.
5287
5289 The following limits are imposed by Excel:
5290 Description Limit
5292 ----------------------------------- ------
5293 Maximum number of chars in a string 32767
5294 Maximum number of columns 256
5295 Maximum number of rows 65536
5296 Maximum chars in a sheet name 31
5297 Maximum chars in a header/footer 254
5298 For Excel 2007+ file limits see the Excel::Writer::XLSX module.
5300 The minimum file size is 6K due to the OLE overhead. The maximum file
5302 size is approximately 7MB (7087104 bytes) of BIFF data. This can be
5303 extended by installing Takanori Kawai's OLE::Storage_Lite module
5304 <http://search.cpan.org/search?dist=OLE-Storage_Lite> see the
5305 "bigfile.pl" example in the "examples" directory of the distro.
5306
5308 The latest version of this module is always available at:
5309 <http://search.cpan.org/search?dist=Spreadsheet-WriteExcel/>.
5310
5312 This module requires Perl >= 5.005, Parse::RecDescent, File::Temp and
5313 OLE::Storage_Lite:
5314 http://search.cpan.org/search?dist=Parse-RecDescent/ # For formulas.
5316 http://search.cpan.org/search?dist=File-Temp/ # For set_tempdir().
5317 http://search.cpan.org/search?dist=OLE-Storage_Lite/ # For files > 7MB.
5318 Note, these aren't strict requirements. Spreadsheet::WriteExcel will
5320 work without these modules if you don't use write_formula(),
5321 set_tempdir() or create files greater than 7MB. However, it is best to
5322 install them if possible and they will be installed automatically if
5323 you use a tool such as CPAN.pm or ppm.
5324
5326 See the INSTALL or install.html docs that come with the distribution
5327 or:
5328 <http://search.cpan.org/src/JMCNAMARA/Spreadsheet-WriteExcel-2.31/INSTALL>.
5329
5331 Spreadsheet::WriteExcel will work on the majority of Windows, UNIX and
5332 Macintosh platforms. Specifically, the module will work on any system
5333 where perl packs floats in the 64 bit IEEE format. The float must also
5334 be in little-endian format but it will be reversed if necessary. Thus:
5335 print join(' ', map { sprintf '%#02x', $_ } unpack('C*', pack 'd', 1.2345)), "\n";
5337 should give (or in reverse order):
5339 0x8d 0x97 0x6e 0x12 0x83 0xc0 0xf3 0x3f
5341 In general, if you don't know whether your system supports a 64 bit
5343 IEEE float or not, it probably does. If your system doesn't, WriteExcel
5344 will "croak()" with the message given in the "DIAGNOSTICS" section. You
5345 can check which platforms the module has been tested on at the CPAN
5346 testers site:
5347 <http://testers.cpan.org/search?request=dist&dist=Spreadsheet-WriteExcel>.
5348
5350 Filename required by Spreadsheet::WriteExcel->new()
5351 A filename must be given in the constructor.
5352 Can't open filename. It may be in use or protected.
5354 The file cannot be opened for writing. The directory that you are
5355 writing to may be protected or the file may be in use by another
5356 program.
5357 Unable to create tmp files via File::Temp::tempfile()...
5359 This is a "-w" warning. You will see it if you are using
5360 Spreadsheet::WriteExcel in an environment where temporary files
5361 cannot be created, in which case all data will be stored in memory.
5362 The warning is for information only: it does not affect creation
5363 but it will affect the speed of execution for large files. See the
5364 "set_tempdir" workbook method.
5365 Maximum file size, 7087104, exceeded.
5367 The current OLE implementation only supports a maximum BIFF file of
5368 this size. This limit can be extended, see the "LIMITATIONS"
5369 section.
5370 Can't locate Parse/RecDescent.pm in @INC ...
5372 Spreadsheet::WriteExcel requires the Parse::RecDescent module.
5373 Download it from CPAN:
5374 <http://search.cpan.org/search?dist=Parse-RecDescent>
5375 Couldn't parse formula ...
5377 There are a large number of warnings which relate to badly formed
5378 formulas and functions. See the "FORMULAS AND FUNCTIONS IN EXCEL"
5379 section for suggestions on how to avoid these errors. You should
5380 also check the formula in Excel to ensure that it is valid.
5381 Required floating point format not supported on this platform.
5383 Operating system doesn't support 64 bit IEEE float or it is byte-
5384 ordered in a way unknown to WriteExcel.
5385 'file.xls' cannot be accessed. The file may be read-only ...
5387 You may sometimes encounter the following error when trying to open
5388 a file in Excel: "file.xls cannot be accessed. The file may be
5389 read-only, or you may be trying to access a read-only location. Or,
5390 the server the document is stored on may not be responding."
5391 This error generally means that the Excel file has been corrupted.
5393 There are two likely causes of this: the file was FTPed in ASCII
5394 mode instead of binary mode or else the file was created with
5395 "UTF-8" data returned by an XML parser. See "Warning about
5396 XML::Parser and perl 5.6" for further details.
5397
5399 The following is some general information about the Excel binary format
5400 for anyone who may be interested.
5401 Excel data is stored in the "Binary Interchange File Format" (BIFF)
5403 file format. Details of this format are given in "Excel 97-2007 Binary
5404 File Format Specification"
5405 <http://www.microsoft.com/interop/docs/OfficeBinaryFormats.mspx>.
5406 Daniel Rentz of OpenOffice.org has also written a detailed description
5408 of the Excel workbook records, see
5409 <http://sc.openoffice.org/excelfileformat.pdf>.
5410 Charles Wybble has collected together additional information about the
5412 Excel file format. See "The Chicago Project" at
5413 <http://chicago.sourceforge.net/devel/>.
5414 The BIFF data is stored along with other data in an OLE Compound File.
5416 This is a structured storage which acts like a file system within a
5417 file. A Compound File is comprised of storages and streams which, to
5418 follow the file system analogy, are like directories and files.
5419 The OLE format is explained in the "Windows Compound Binary File Format
5421 Specification"
5422 <http://www.microsoft.com/interop/docs/supportingtechnologies.mspx>
5423 The Digital Imaging Group have also detailed the OLE format in the
5425 JPEG2000 specification: see Appendix A of
5426 <http://www.i3a.org/pdf/wg1n1017.pdf>.
5427 Please note that the provision of this information does not constitute
5429 an invitation to start hacking at the BIFF or OLE file formats. There
5430 are more interesting ways to waste your time. ;-)
5431
5433 Depending on your requirements, background and general sensibilities
5434 you may prefer one of the following methods of getting data into Excel:
5435 • Win32::OLE module and office automation
5437 This requires a Windows platform and an installed copy of Excel.
5439 This is the most powerful and complete method for interfacing with
5440 Excel. See
5441 <http://www.activestate.com/ASPN/Reference/Products/ActivePerl-5.6/faq/Windows/ActivePerl-Winfaq12.html>
5442 and
5443 <http://www.activestate.com/ASPN/Reference/Products/ActivePerl-5.6/site/lib/Win32/OLE.html>.
5444 If your main platform is UNIX but you have the resources to set up
5445 a separate Win32/MSOffice server, you can convert office documents
5446 to text, postscript or PDF using Win32::OLE. For a demonstration of
5447 how to do this using Perl see Docserver:
5448 <http://search.cpan.org/search?mode=module&query=docserver>.
5449 • CSV, comma separated variables or text
5451 If the file extension is "csv", Excel will open and convert this
5453 format automatically. Generating a valid CSV file isn't as easy as
5454 it seems. Have a look at the DBD::RAM, DBD::CSV, Text::xSV and
5455 Text::CSV_XS modules.
5456 • DBI with DBD::ADO or DBD::ODBC
5458 Excel files contain an internal index table that allows them to act
5460 like a database file. Using one of the standard Perl database
5461 modules you can connect to an Excel file as a database.
5462 • DBD::Excel
5464 You can also access Spreadsheet::WriteExcel using the standard DBI
5466 interface via Takanori Kawai's DBD::Excel module
5467 <http://search.cpan.org/dist/DBD-Excel>
5468 • Spreadsheet::WriteExcelXML
5470 This module allows you to create an Excel XML file using the same
5472 interface as Spreadsheet::WriteExcel. See:
5473 <http://search.cpan.org/dist/Spreadsheet-WriteExcelXML>
5474 • Excel::Template
5476 This module allows you to create an Excel file from an XML template
5478 in a manner similar to HTML::Template. See
5479 <http://search.cpan.org/dist/Excel-Template/>.
5480 • Spreadsheet::WriteExcel::FromXML
5482 This module allows you to turn a simple XML file into an Excel file
5484 using Spreadsheet::WriteExcel as a back-end. The format of the XML
5485 file is defined by a supplied DTD:
5486 <http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromXML>.
5487 • Spreadsheet::WriteExcel::Simple
5489 This provides an easier interface to Spreadsheet::WriteExcel:
5491 <http://search.cpan.org/dist/Spreadsheet-WriteExcel-Simple>.
5492 • Spreadsheet::WriteExcel::FromDB
5494 This is a useful module for creating Excel files directly from a DB
5496 table: <http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB>.
5497 • HTML tables
5499 This is an easy way of adding formatting via a text based format.
5501 • XML or HTML
5503 The Excel XML and HTML file specification are available from
5505 <http://msdn.microsoft.com/library/officedev/ofxml2k/ofxml2k.htm>.
5506 For other Perl-Excel modules try the following search:
5508 <http://search.cpan.org/search?mode=module&query=excel>.
5509
5511 To read data from Excel files try:
5512 • Spreadsheet::ParseExcel
5514 This uses the OLE::Storage-Lite module to extract data from an
5516 Excel file. <http://search.cpan.org/dist/Spreadsheet-ParseExcel>.
5517 • Spreadsheet::ParseExcel_XLHTML
5519 This module uses Spreadsheet::ParseExcel's interface but uses
5521 xlHtml (see below) to do the conversion:
5522 <http://search.cpan.org/dist/Spreadsheet-ParseExcel_XLHTML>
5523 Spreadsheet::ParseExcel_XLHTML
5524 • xlHtml
5526 This is an open source "Excel to HTML Converter" C/C++ project at
5528 <http://chicago.sourceforge.net/xlhtml/>.
5529 • DBD::Excel (reading)
5531 You can also access Spreadsheet::ParseExcel using the standard DBI
5533 interface via Takanori Kawai's DBD::Excel module
5534 <http://search.cpan.org/dist/DBD-Excel>.
5535 • Win32::OLE module and office automation (reading)
5537 See, the section "WRITING EXCEL FILES".
5539 • HTML tables (reading)
5541 If the files are saved from Excel in a HTML format the data can be
5543 accessed using HTML::TableExtract
5544 <http://search.cpan.org/dist/HTML-TableExtract>.
5545 • DBI with DBD::ADO or DBD::ODBC.
5547 See, the section "WRITING EXCEL FILES".
5549 • XML::Excel
5551 Converts Excel files to XML using Spreadsheet::ParseExcel
5553 <http://search.cpan.org/dist/XML-Excel>.
5554 • OLE::Storage, aka LAOLA
5556 This is a Perl interface to OLE file formats. In particular, the
5558 distro contains an Excel to HTML converter called Herbert,
5559 <http://user.cs.tu-berlin.de/~schwartz/pmh/>. This has been
5560 superseded by the Spreadsheet::ParseExcel module.
5561 For other Perl-Excel modules try the following search:
5563 <http://search.cpan.org/search?mode=module&query=excel>.
5564 If you wish to view Excel files on a UNIX/Linux platform check out the
5566 excellent Gnumeric spreadsheet application at
5567 <http://www.gnome.org/projects/gnumeric/> or OpenOffice.org at
5568 <http://www.openoffice.org/>.
5569 If you wish to view Excel files on a Windows platform which doesn't
5571 have Excel installed you can use the free Microsoft Excel Viewer
5572 <http://office.microsoft.com/downloads/2000/xlviewer.aspx>.
5573
5575 An Excel file is a binary file within a binary file. It contains
5576 several interlinked checksums and changing even one byte can cause it
5577 to become corrupted.
5578 As such you cannot simply append or update an Excel file. The only way
5580 to achieve this is to read the entire file into memory, make the
5581 required changes or additions and then write the file out again.
5582 You can read and rewrite an Excel file using the
5584 Spreadsheet::ParseExcel::SaveParser module which is a wrapper around
5585 Spreadsheet::ParseExcel and Spreadsheet::WriteExcel. It is part of the
5586 Spreadsheet::ParseExcel package:
5587 <http://search.cpan.org/search?dist=Spreadsheet-ParseExcel>.
5588 However, you can only rewrite the features that Spreadsheet::WriteExcel
5590 supports so macros, graphs and some other features in the original
5591 Excel file will be lost. Also, formulas aren't rewritten, only the
5592 result of a formula is written.
5593 Here is an example:
5595 #!/usr/bin/perl -w
5597 use strict;
5599 use Spreadsheet::ParseExcel;
5600 use Spreadsheet::ParseExcel::SaveParser;
5601 # Open the template with SaveParser
5603 my $parser = new Spreadsheet::ParseExcel::SaveParser;
5604 my $template = $parser->Parse('template.xls');
5605 my $sheet = 0;
5607 my $row = 0;
5608 my $col = 0;
5609 # Get the format from the cell
5611 my $format = $template->{Worksheet}[$sheet]
5612 ->{Cells}[$row][$col]
5613 ->{FormatNo};
5614 # Write data to some cells
5616 $template->AddCell(0, $row, $col, 1, $format);
5617 $template->AddCell(0, $row+1, $col, "Hello", $format);
5618 # Add a new worksheet
5620 $template->AddWorksheet('New Data');
5621 # The SaveParser SaveAs() method returns a reference to a
5623 # Spreadsheet::WriteExcel object. If you wish you can then
5624 # use this to access any of the methods that aren't
5625 # available from the SaveParser object. If you don't need
5626 # to do this just use SaveAs().
5627 #
5628 my $workbook;
5629 {
5631 # SaveAs generates a lot of harmless warnings about unset
5632 # Worksheet properties. You can ignore them if you wish.
5633 local $^W = 0;
5634 # Rewrite the file or save as a new file
5636 $workbook = $template->SaveAs('new.xls');
5637 }
5638 # Use Spreadsheet::WriteExcel methods
5640 my $worksheet = $workbook->sheets(0);
5641 $worksheet->write($row+2, $col, "World2");
5643 $workbook->close();
5645
5647 You must be careful when using Spreadsheet::WriteExcel in conjunction
5648 with perl 5.6 and XML::Parser (and other XML parsers) due to the fact
5649 that the data returned by the parser is generally in "UTF-8" format.
5650 When "UTF-8" strings are added to Spreadsheet::WriteExcel's internal
5652 data it causes the generated Excel file to become corrupt.
5653 Note, this doesn't affect perl 5.005 (which doesn't try to handle
5655 "UTF-8") or 5.8 (which handles it correctly).
5656 To avoid this problem you should upgrade to perl 5.8, if possible, or
5658 else you should convert the output data from XML::Parser to ASCII or
5659 ISO-8859-1 using one of the following methods:
5660 $new_str = pack 'C*', unpack 'U*', $utf8_str;
5662 use Unicode::MapUTF8 'from_utf8';
5665 $new_str = from_utf8({-str => $utf8_str, -charset => 'ISO-8859-1'});
5666
5668 If you have Office Service Pack 3 (SP3) installed you may see the
5669 following warning when you open a file created by
5670 Spreadsheet::WriteExcel:
5671 "File Error: data may have been lost".
5673 This is usually caused by multiple instances of data in a cell.
5675 SP3 changed Excel's default behaviour when it encounters multiple data
5677 in a cell so that it issues a warning when the file is opened and it
5678 displays the first data that was written. Prior to SP3 it didn't issue
5679 a warning and displayed the last data written.
5680 For a longer discussion and some workarounds see the following:
5682 <http://groups.google.com/group/spreadsheet-writeexcel/browse_thread/thread/3dcea40e6620af3a>.
5683
5685 Formulas are formulae.
5686 XML and "UTF-8" data on perl 5.6 can cause Excel files created by
5688 Spreadsheet::WriteExcel to become corrupt. See "Warning about
5689 XML::Parser and perl 5.6" for further details.
5690 The format object that is used with a "merge_range()" method call is
5692 marked internally as being associated with a merged range. It is a
5693 fatal error to use a merged format in a non-merged cell. The current
5694 workaround is to use separate formats for merged and non-merged cell.
5695 This restriction will be removed in a future release.
5696 Nested formulas sometimes aren't parsed correctly and give a result of
5698 "#VALUE". If you come across a formula that parses like this, let me
5699 know.
5700 Spreadsheet::ParseExcel: All formulas created by
5702 Spreadsheet::WriteExcel are read as having a value of zero. This is
5703 because Spreadsheet::WriteExcel only stores the formula and not the
5704 calculated result.
5705 OpenOffice.org: No known issues in this release.
5707 Gnumeric: No known issues in this release.
5709 If you wish to submit a bug report run the "bug_report.pl" program in
5711 the "examples" directory of the distro.
5712
5714 Spreadsheet::WriteExcel is in maintenance only mode and has effectively
5715 been superseded by Excel::Writer::XLSX.
5716 Excel::Writer::XLSX is an API compatible, drop-in replacement for
5718 Spreadsheet::WriteExcel. It also has many more features such as
5719 conditional formats, better charts, better formula handling, Excel
5720 tables and even sparklines.
5721 To convert your Spreadsheet::WriteExcel program to Excel::Writer::XLSX
5723 you only need do the following:
5724 • Substitute Excel::Writer::XLSX for Spreadsheet::WriteExcel in your
5726 program.
5727 • Change the file extension of the output file from ".xls" to
5729 ".xlsx".
5730 • Optionally replace "store_formula()" and "repeat_formula()" with
5732 "write_formula()" which is no longer an expensive operation in
5733 Excel::Writer::XLSX. However, you can leave them unchanged if
5734 required.
5735 There are some differences between the formats and the modules that are
5737 worth noting:
5738 • The default font in the XLSX format is Calibri 11 not Arial 10.
5740 • Default column widths and row heights are different between XLS and
5742 XLSX.
5743 • The Excel::Writer::XLSX module uses more memory by default but has
5745 a optimisation mode to reduce usage for large files.
5746 • The XLSX format doesn't have reading support that is as complete as
5748 Spreadsheet::ParseExcel.
5749
5751 The Spreadsheet::WriteExcel source code in host on github:
5752 <http://github.com/jmcnamara/spreadsheet-writeexcel>.
5753
5755 There is a Google group for discussing and asking questions about
5756 Spreadsheet::WriteExcel. This is a good place to search to see if your
5757 question has been asked before:
5758 <http://groups.google.com/group/spreadsheet-writeexcel>.
5759 Alternatively you can keep up to date with future releases by
5761 subscribing at: <http://freshmeat.net/projects/writeexcel/>.
5762
5764 If you'd care to donate to the Spreadsheet::WriteExcel project, you can
5765 do so via PayPal: <http://tinyurl.com/7ayes>.
5766
5768 Spreadsheet::ParseExcel:
5769 <http://search.cpan.org/dist/Spreadsheet-ParseExcel>.
5770 Spreadsheet-WriteExcel-FromXML:
5772 <http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromXML>.
5773 Spreadsheet::WriteExcel::FromDB:
5775 <http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB>.
5776 Excel::Template: <http://search.cpan.org/~rkinyon/Excel-Template/>.
5778 DateTime::Format::Excel:
5780 <http://search.cpan.org/dist/DateTime-Format-Excel>.
5781 "Reading and writing Excel files with Perl" by Teodor Zlatanov, at IBM
5783 developerWorks:
5784 <http://www-106.ibm.com/developerworks/library/l-pexcel/>.
5785 "Excel-Dateien mit Perl erstellen - Controller im Gluck" by Peter
5787 Dintelmann and Christian Kirsch in the German Unix/web journal iX:
5788 <http://www.heise.de/ix/artikel/2001/06/175/>.
5789 Spreadsheet::WriteExcel documentation in Japanese by Takanori Kawai.
5791 <http://member.nifty.ne.jp/hippo2000/perltips/Spreadsheet/WriteExcel.htm>.
5792 Oesterly user brushes with fame:
5794 <http://oesterly.com/releases/12102000.html>.
5795 The csv2xls program that is part of Text::CSV_XS:
5797 <http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>.
5798
5800 The following people contributed to the debugging and testing of
5801 Spreadsheet::WriteExcel:
5802 Alexander Farber, Andre de Bruin, Arthur@ais, Artur Silveira da Cunha,
5804 Bob Rose, Borgar Olsen, Brian Foley, Brian White, Bob Mackay, Cedric
5805 Bouvier, Chad Johnson, CPAN testers, Damyan Ivanov, Daniel Berger,
5806 Daniel Gardner, Dmitry Kochurov, Eric Frazier, Ernesto Baschny, Felipe
5807 Perez Galiana, Gordon Simpson, Hanc Pavel, Harold Bamford, James
5808 Holmes, James Wilkinson, Johan Ekenberg, Johann Hanne, Jonathan Scott
5809 Duff, J.C. Wren, Kenneth Stacey, Keith Miller, Kyle Krom, Marc
5810 Rosenthal, Markus Schmitz, Michael Braig, Michael Buschauer, Mike
5811 Blazer, Michael Erickson, Michael W J West, Ning Xie, Paul J. Falbe,
5812 Paul Medynski, Peter Dintelmann, Pierre Laplante, Praveen Kotha, Reto
5813 Badertscher, Rich Sorden, Shane Ashby, Sharron McKenzie, Shenyu Zheng,
5814 Stephan Loescher, Steve Sapovits, Sven Passig, Svetoslav Marinov, Tamas
5815 Gulacsi, Troy Daniels, Vahe Sarkissian.
5816 The following people contributed patches, examples or Excel
5818 information:
5819 Andrew Benham, Bill Young, Cedric Bouvier, Charles Wybble, Daniel
5821 Rentz, David Robins, Franco Venturi, Guy Albertelli, Ian Penman, John
5822 Heitmann, Jon Guy, Kyle R. Burton, Pierre-Jean Vouette, Rubio, Marco
5823 Geri, Mark Fowler, Matisse Enzer, Sam Kington, Takanori Kawai, Tom
5824 O'Sullivan.
5825 Many thanks to Ron McKelvey, Ronzo Consulting for Siemens, who
5827 sponsored the development of the formula caching routines.
5828 Many thanks to Cassens Transport who sponsored the development of the
5830 embedded charts and autofilters.
5831 Additional thanks to Takanori Kawai for translating the documentation
5833 into Japanese.
5834 Gunnar Wolf maintains the Debian distro.
5836 Thanks to Damian Conway for the excellent Parse::RecDescent.
5838 Thanks to Tim Jenness for File::Temp.
5840 Thanks to Michael Meeks and Jody Goldberg for their work on Gnumeric.
5842
5844 Because this software is licensed free of charge, there is no warranty
5845 for the software, to the extent permitted by applicable law. Except
5846 when otherwise stated in writing the copyright holders and/or other
5847 parties provide the software "as is" without warranty of any kind,
5848 either expressed or implied, including, but not limited to, the implied
5849 warranties of merchantability and fitness for a particular purpose. The
5850 entire risk as to the quality and performance of the software is with
5851 you. Should the software prove defective, you assume the cost of all
5852 necessary servicing, repair, or correction.
5853 In no event unless required by applicable law or agreed to in writing
5855 will any copyright holder, or any other party who may modify and/or
5856 redistribute the software as permitted by the above licence, be liable
5857 to you for damages, including any general, special, incidental, or
5858 consequential damages arising out of the use or inability to use the
5859 software (including but not limited to loss of data or data being
5860 rendered inaccurate or losses sustained by you or third parties or a
5861 failure of the software to operate with any other software), even if
5862 such holder or other party has been advised of the possibility of such
5863 damages.
5864
5866 Either the Perl Artistic Licence
5867 <http://dev.perl.org/licenses/artistic.html> or the GPL
5868 <http://www.opensource.org/licenses/gpl-license.php>.
5869
5871 John McNamara jmcnamara@cpan.org
5872 The ashtray says
5874 You were up all night.
5875 When you went to bed
5876 With your darkest mind.
5877 Your pillow wept
5878 And covered your eyes.
5879 And you finally slept
5880 While the sun caught fire.
5881 You've changed.
5883 -- Jeff Tweedy
5884
5886 Copyright MM-MMXII, John McNamara.
5887 All Rights Reserved. This module is free software. It may be used,
5889 redistributed and/or modified under the same terms as Perl itself.
5890perl v5.34.0 2022-01-21 Spreadsheet::WriteExcel(3)