1Spreadsheet::WriteExcelU(s3e)r Contributed Perl DocumentaStpiroenadsheet::WriteExcel(3)
2
3
4

NAME

6       Spreadsheet::WriteExcel - Write to a cross-platform Excel binary file.
7

VERSION

9       This document refers to version 2.40 of Spreadsheet::WriteExcel,
10       released November 6, 2013.
11

SYNOPSIS

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
16           use Spreadsheet::WriteExcel;
17
18           # Create a new Excel workbook
19           my $workbook = Spreadsheet::WriteExcel->new('perl.xls');
20
21           # Add a worksheet
22           $worksheet = $workbook->add_worksheet();
23
24           #  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
30           # 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
35           # Write a number and a formula using A1 notation
36           $worksheet->write('A3', 1.2345);
37           $worksheet->write('A4', '=SIN(PI()/4)');
38

DESCRIPTION

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
45       The file produced by this module is compatible with Excel 97, 2000,
46       2002, 2003 and 2007.
47
48       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
52       This module cannot be used to write to an existing Excel file (See
53       "MODIFYING AND REWRITING EXCEL FILES").
54
55       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

QUICK START

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
68       1. Create a new Excel workbook (i.e. file) using "new()".
69
70       2. Add a worksheet to the new workbook using "add_worksheet()".
71
72       3. Write to the worksheet using "write()".
73
74       Like this:
75
76           use Spreadsheet::WriteExcel;                             # Step 0
77
78           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
82       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
88       Those of you who read the instructions first and assemble the furniture
89       afterwards will know how to proceed. ;-)
90

WORKBOOK METHODS

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
96           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
111       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
115   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
120           my $workbook  = Spreadsheet::WriteExcel->new('filename.xls');
121           my $worksheet = $workbook->add_worksheet();
122           $worksheet->write(0, 0, 'Hi Excel!');
123
124       Here are some other examples of using "new()" with filenames:
125
126           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
131       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
137       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
144       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
150           my $workbook  = Spreadsheet::WriteExcel->new('protected.xls');
151           die "Problems creating new Excel file: $!" unless defined $workbook;
152
153       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
156           binmode(STDOUT);
157           my $workbook  = Spreadsheet::WriteExcel->new(\*STDOUT);
158
159       The requirement for "binmode()" is explained below.
160
161       See also, the "cgi.pl" program in the "examples" directory of the
162       distro.
163
164       However, this special case will not work in "mod_perl" programs where
165       you will have to do something like the following:
166
167           # mod_perl 1
168           ...
169           tie *XLS, 'Apache';
170           binmode(XLS);
171           my $workbook  = Spreadsheet::WriteExcel->new(\*XLS);
172           ...
173
174           # 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
181       See also, the "mod_perl1.pl" and "mod_perl2.pl" programs in the
182       "examples" directory of the distro.
183
184       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
187       For example here is a way to write an Excel file to a scalar with "perl
188       5.8":
189
190           #!/usr/bin/perl -w
191
192           use strict;
193           use Spreadsheet::WriteExcel;
194
195           # Requires perl 5.8 or later
196           open my $fh, '>', \my $str or die "Failed to open filehandle: $!";
197
198           my $workbook  = Spreadsheet::WriteExcel->new($fh);
199           my $worksheet = $workbook->add_worksheet();
200
201           $worksheet->write(0, 0,  'Hi Excel!');
202
203           $workbook->close();
204
205           # The Excel file in now in $str. Remember to binmode() the output
206           # filehandle before printing it.
207           binmode STDOUT;
208           print $str;
209
210       See also the "write_to_scalar.pl" and "filehandle.pl" programs in the
211       "examples" directory of the distro.
212
213       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
223       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
229   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
233           $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
238       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
242       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
247       On systems with "perl 5.8" and later the "add_worksheet()" method will
248       also handle strings in "UTF-8" format.
249
250           $worksheet = $workbook->add_worksheet("\x{263a}"); # Smiley
251
252       On earlier Perl systems your can specify "UTF-16BE" worksheet names
253       using an additional optional parameter:
254
255           my $name = pack 'n', 0x263a;
256           $worksheet = $workbook->add_worksheet($name, 1);   # Smiley
257
258   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
264           $format1 = $workbook->add_format(%props); # Set properties at creation
265           $format2 = $workbook->add_format();       # Set properties later
266
267       See the "CELL FORMATTING" section for more details about Format
268       properties and how to set them.
269
270   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
275           my $chart = $workbook->add_chart( type => 'column' );
276
277       The properties that can be set are:
278
279           type     (required)
280           name     (optional)
281           embedded (optional)
282
283       ·   "type"
284
285           This is a required parameter. It defines the type of chart that
286           will be created.
287
288               my $chart = $workbook->add_chart( type => 'line' );
289
290           The available types are:
291
292               area
293               bar
294               column
295               line
296               pie
297               scatter
298               stock
299
300       ·   "name"
301
302           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
308               my $chart = $workbook->add_chart( type => 'line', name => 'Results Chart' );
309
310       ·   "embedded"
311
312           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
316               my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
317
318               # Configure the chart.
319               ...
320
321               # Insert the chart into the a worksheet.
322               $worksheet->insert_chart( 'E2', $chart );
323
324       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
328   add_chart_ext($chart_data, $chartname)
329       This method is use to include externally generated charts in a
330       Spreadsheet::WriteExcel file.
331
332           my $chart = $workbook->add_chart_ext('chart01.bin', 'Chart1');
333
334       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
338   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
343           $workbook->close();
344
345       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
349       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
353       ·   If "my()" was not used to declare the scope of a workbook variable
354           created using "new()".
355
356       ·   If the "new()", "add_worksheet()" or "add_format()" methods are
357           called in subroutines.
358
359       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
365       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
368       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
372           $workbook->close() or die "Error closing file: $!";
373
374   compatibility_mode()
375       This method is used to improve compatibility with third party
376       applications that read Excel files.
377
378           $workbook->compatibility_mode();
379
380       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
386       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
394       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
399       If you encounter other situations that require "compatibility_mode()",
400       please let me know.
401
402       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
406       You must call "compatibility_mode()" before calling "add_worksheet()".
407
408   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
415       The properties should be passed as a hash of values as follows:
416
417           $workbook->set_properties(
418               title    => 'This is an example spreadsheet',
419               author   => 'John McNamara',
420               comments => 'Created with Perl and Spreadsheet::WriteExcel',
421           );
422
423       The properties that can be set are:
424
425           title
426           subject
427           author
428           manager
429           company
430           category
431           keywords
432           comments
433
434       User defined properties are not supported due to effort required.
435
436       In perl 5.8+ you can also pass UTF-8 strings as properties. See
437       "UNICODE IN EXCEL".
438
439           my $smiley = chr 0x263A;
440
441           $workbook->set_properties(
442               subject => "Happy now? $smiley",
443           );
444
445       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
449           my $smiley = pack 'H*', 'E298BA';
450
451           $workbook->set_properties(
452               subject => "Happy now? $smiley",
453               utf8    => 1,
454           );
455
456       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
460       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
464           $workbook->set_properties(
465               ...,
466               comments => 'Created with Perl and Spreadsheet::WriteExcel',
467               ...,
468           );
469
470       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
479       For convenience it is possible to pass either a hash or hash ref of
480       arguments to this method.
481
482       See also the "properties.pl" program in the examples directory of the
483       distro.
484
485   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
489           $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
493       See the defined_name.pl program in the examples dir of the distro.
494
495       Note: This currently a beta feature. More documentation and examples
496       will be added.
497
498   set_tempdir()
499       For speed and efficiency "Spreadsheet::WriteExcel" stores worksheet
500       data in temporary files prior to assembling the final workbook.
501
502       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
506       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
511       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
515           #!/usr/bin/perl -w
516
517           use Spreadsheet::WriteExcel;
518
519           my $workbook  = Spreadsheet::WriteExcel->new('test.xls');
520           my $worksheet = $workbook->add_worksheet();
521
522       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
525       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
530           perl -MFile::Spec -le "print File::Spec->tmpdir"
531
532       Even if the default temporary file directory is accessible you may wish
533       to specify an alternative location for security or maintenance reasons:
534
535           $workbook->set_tempdir('/tmp/writeexcel');
536           $workbook->set_tempdir('c:\windows\temp\writeexcel');
537
538       The directory for the temporary file must exist, "set_tempdir()" will
539       not create a new directory.
540
541       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
548   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
552       The value for $index should be in the range 8..63, see "COLOURS IN
553       EXCEL".
554
555       The default named colours use the following indices:
556
557            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
574       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
579       The "set_custom_color()" workbook method can also be used with a HTML
580       style "#rrggbb" hex value:
581
582           $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
586           my $font = $workbook->add_format(color => 40); # Use the modified colour
587
588       The return value from "set_custom_color()" is the index of the colour
589       that was changed:
590
591           my $ferrari = $workbook->set_custom_color(40, 216, 12, 12);
592
593           my $format  = $workbook->add_format(
594                                               bg_color => $ferrari,
595                                               pattern  => 1,
596                                               border   => 1
597                                             );
598
599   sheets(0, 1, ...)
600       The "sheets()" method returns a list, or a sliced list, of the
601       worksheets in a workbook.
602
603       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
607           foreach $worksheet ($workbook->sheets()) {
608              print $worksheet->get_name();
609           }
610
611       You can also specify a slice list to return one or more worksheet
612       objects:
613
614           $worksheet = $workbook->sheets(0);
615           $worksheet->write('A1', 'Hello');
616
617       Or since return value from "sheets()" is a reference to a worksheet
618       object you can write the above example as:
619
620           $workbook->sheets(0)->write('A1', 'Hello');
621
622       The following example returns the first and last worksheet in a
623       workbook:
624
625           foreach $worksheet ($workbook->sheets(0, -1)) {
626              # Do something
627           }
628
629       Array slices are explained in the perldata manpage.
630
631   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
639       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
644       See also "DATES AND TIME IN EXCEL" for more information about working
645       with Excel's date system.
646
647       In general you probably won't need to use "set_1904()".
648
649   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
655       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
659           $workbook->set_codepage(1); # ANSI, MS Windows
660           $workbook->set_codepage(2); # Apple Macintosh
661
662       The "set_codepage()" method is rarely required.
663

WORKSHEET METHODS

665       A new worksheet is created by calling the "add_worksheet()" method from
666       a workbook object:
667
668           $worksheet1 = $workbook->add_worksheet();
669           $worksheet2 = $workbook->add_worksheet();
670
671       The following methods are available through a new worksheet:
672
673           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
713   Cell notation
714       Spreadsheet::WriteExcel supports two forms of notation to designate the
715       position of cells: Row-column notation and A1 notation.
716
717       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
721           (0, 0)      # The top left cell in row-column notation.
722           ('A1')      # The top left cell in A1 notation.
723
724           (1999, 29)  # Row-column notation.
725           ('AD2000')  # The same cell in A1 notation.
726
727       Row-column notation is useful if you are referring to cells
728       programmatically:
729
730           for my $i (0 .. 9) {
731               $worksheet->write($i, 0, 'Hello'); # Cells A1 to A10
732           }
733
734       A1 notation is useful for setting up a worksheet manually and for
735       working with formulas:
736
737           $worksheet->write('H1', 200);
738           $worksheet->write('H2', '=H1+1');
739
740       In formulas and applicable methods you can also use the "A:A" column
741       notation:
742
743           $worksheet->write('A1', '=SUM(B:B)');
744
745       The "Spreadsheet::WriteExcel::Utility" module that is included in the
746       distro contains helper functions for dealing with A1 notation, for
747       example:
748
749           use Spreadsheet::WriteExcel::Utility;
750
751           ($row, $col)    = xl_cell_to_rowcol('C2');  # (1, 2)
752           $str            = xl_rowcol_to_cell(1, 2);  # C2
753
754       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
758       Note: in Excel it is also possible to use a R1C1 notation. This is not
759       supported by Spreadsheet::WriteExcel.
760
761   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
767           write_string()
768           write_number()
769           write_blank()
770           write_formula()
771           write_url()
772           write_row()
773           write_col()
774
775       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
779                                                             # 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
797           # 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
802       The "looks like" rule is defined by regular expressions:
803
804       "write_number()" if $token is a number based on the following regex:
805       "$token =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?$/".
806
807       "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
811       "write_blank()" if $token is undef or a blank string: "undef", "" or
812       ''.
813
814       "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
818       "write_url()" if $token is an internal or external sheet reference
819       based on the following regex: "$token =~ m[^(in|ex)ternal:]".
820
821       "write_formula()" if the first character of $token is "=".
822
823       "write_row()" if $token is an array ref.
824
825       "write_col()" if $token is an array ref of array refs.
826
827       "write_string()" if none of the previous conditions apply.
828
829       The $format parameter is optional. It should be a valid Format object,
830       see "CELL FORMATTING":
831
832           my $format = $workbook->add_format();
833           $format->set_bold();
834           $format->set_color('red');
835           $format->set_align('center');
836
837           $worksheet->write(4, 0, 'Hello', $format); # Formatted string
838
839       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
844       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
853       You can also add your own data handlers to the "write()" method using
854       "add_write_handler()".
855
856       On systems with "perl 5.8" and later the "write()" method will also
857       handle Unicode strings in "UTF-8" format.
858
859       The "write" methods return:
860
861           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
866   write_number($row, $column, $number, $format)
867       Write an integer or a float to the cell specified by $row and $column:
868
869           $worksheet->write_number(0, 0,  123456);
870           $worksheet->write_number('A2',  2.3451);
871
872       See the note about "Cell notation". The $format parameter is optional.
873
874       In general it is sufficient to use the "write()" method.
875
876   write_string($row, $column, $string, $format)
877       Write a string to the cell specified by $row and $column:
878
879           $worksheet->write_string(0, 0, 'Your text here' );
880           $worksheet->write_string('A2', 'or here' );
881
882       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
886       The $format parameter is optional.
887
888       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
894       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
899           # Write as a plain string
900           $worksheet->write_string('A1', '01209');
901
902       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
905           # 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
909       See also the note about "Cell notation".
910
911   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
919       The following is a simple example showing how to write some Unicode
920       strings in "UTF-16BE" format:
921
922           #!/usr/bin/perl -w
923
924
925           use strict;
926           use Spreadsheet::WriteExcel;
927           use Unicode::Map();
928
929           my $workbook  = Spreadsheet::WriteExcel->new('utf_16_be.xls');
930           my $worksheet = $workbook->add_worksheet();
931
932           # Increase the column width for clarity
933           $worksheet->set_column('A:A', 25);
934
935
936           # Write a Unicode character
937           #
938           my $smiley = pack 'n', 0x263a;
939
940           # Increase the font size for legibility.
941           my $big_font = $workbook->add_format(size => 72);
942
943           $worksheet->write_utf16be_string('A3', $smiley, $big_font);
944
945
946
947           # Write a phrase in Cyrillic using a hex-encoded string
948           #
949           my $str = pack 'H*', '042d0442043e0020044404400430043704300020043d' .
950                                '043000200440044304410441043a043e043c0021';
951
952           $worksheet->write_utf16be_string('A5', $str);
953
954
955
956           # 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
961           $worksheet->write_utf16be_string('A7', $utf16);
962
963       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
969       For a full list of the Perl Unicode modules see:
970       <http://search.cpan.org/search?query=unicode&mode=all>.
971
972       "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
976       The "write_utf16be_string()" method was previously called
977       "write_unicode()". That, overly general, name is still supported but
978       deprecated.
979
980       See also the "unicode_*.pl" programs in the examples directory of the
981       distro.
982
983   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
988       "UTF-16" data can be changed from little-endian to big-endian format
989       (and vice-versa) as follows:
990
991           $utf16be = pack 'n*', unpack 'v*', $utf16le;
992
993   keep_leading_zeros()
994       This method changes the default handling of integers with leading zeros
995       when using the "write()" method.
996
997       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
1003       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
1008       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
1012           # Implicitly write a number, the leading zero is removed: 1209
1013           $worksheet->write('A1', '01209');
1014
1015           # 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
1019           # Write explicitly as a string: 01209
1020           $worksheet->write_string('A3', '01209');
1021
1022           # Write implicitly as a string: 01209
1023           $worksheet->keep_leading_zeros();
1024           $worksheet->write('A4', '01209');
1025
1026       The above code would generate a worksheet that looked like the
1027       following:
1028
1029            -----------------------------------------------------------
1030           |   |     A     |     B     |     C     |     D     | ...
1031            -----------------------------------------------------------
1032           | 1 |      1209 |           |           |           | ...
1033           | 2 |     01209 |           |           |           | ...
1034           | 3 | 01209     |           |           |           | ...
1035           | 4 | 01209     |           |           |           | ...
1036
1037       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
1042       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
1046           # Format as a string (01209)
1047           my $format2 = $workbook->add_format(num_format => '@');
1048           $worksheet->write_string('A5', '01209', $format2);
1049
1050       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
1054           $worksheet->keep_leading_zeros();  # Set on
1055           $worksheet->keep_leading_zeros(1); # Set on
1056           $worksheet->keep_leading_zeros(0); # Set off
1057
1058       See also the "add_write_handler()" method.
1059
1060   write_blank($row, $column, $format)
1061       Write a blank cell specified by $row and $column:
1062
1063           $worksheet->write_blank(0, 0, $format);
1064
1065       This method is used to add formatting to a cell which doesn't contain a
1066       string or number value.
1067
1068       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
1073       As such, if you write an empty cell without formatting it is ignored:
1074
1075           $worksheet->write('A1',  undef, $format); # write_blank()
1076           $worksheet->write('A2',  undef         ); # Ignored
1077
1078       This seemingly uninteresting fact means that you can write arrays of
1079       data without special treatment for undef or empty string values.
1080
1081       See the note about "Cell notation".
1082
1083   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
1090           @array      = ('awk', 'gawk', 'mawk');
1091           $array_ref  = \@array;
1092
1093           $worksheet->write_row(0, 0, $array_ref);
1094
1095           # 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
1100       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
1104           $worksheet->write_row('A1', $array_ref); # Write a row of data
1105           $worksheet->write(    'A1', $array_ref); # Same thing
1106
1107       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
1111       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
1114           @eec =  (
1115                       ['maggie', 'milly', 'molly', 'may'  ],
1116                       [13,       14,      15,      16     ],
1117                       ['shell',  'star',  'crab',  'stone']
1118                   );
1119
1120           $worksheet->write_row('A1', \@eec);
1121
1122       Would produce a worksheet as follows:
1123
1124            -----------------------------------------------------------
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
1134       To write the data in a row-column order refer to the "write_col()"
1135       method below.
1136
1137       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
1142       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
1146       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
1150       See also the "write_arrays.pl" program in the "examples" directory of
1151       the distro.
1152
1153       The "write_row()" method allows the following idiomatic conversion of a
1154       text file to an Excel file:
1155
1156           #!/usr/bin/perl -w
1157
1158           use strict;
1159           use Spreadsheet::WriteExcel;
1160
1161           my $workbook  = Spreadsheet::WriteExcel->new('file.xls');
1162           my $worksheet = $workbook->add_worksheet();
1163
1164           open INPUT, 'file.txt' or die "Couldn't open file: $!";
1165
1166           $worksheet->write($.-1, 0, [split]) while <INPUT>;
1167
1168   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
1175           @array      = ('awk', 'gawk', 'mawk');
1176           $array_ref  = \@array;
1177
1178           $worksheet->write_col(0, 0, $array_ref);
1179
1180           # 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
1185       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
1189       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
1192           @eec =  (
1193                       ['maggie', 'milly', 'molly', 'may'  ],
1194                       [13,       14,      15,      16     ],
1195                       ['shell',  'star',  'crab',  'stone']
1196                   );
1197
1198           $worksheet->write_col('A1', \@eec);
1199
1200       Would produce a worksheet as follows:
1201
1202            -----------------------------------------------------------
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
1212       To write the data in a column-row order refer to the "write_row()"
1213       method above.
1214
1215       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
1220       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
1226           $worksheet->write_col('A1', $array_ref    ); # Write a column of data
1227           $worksheet->write(    'A1', [ $array_ref ]); # Same thing
1228
1229       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
1233       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
1237       See also the "write_arrays.pl" program in the "examples" directory of
1238       the distro.
1239
1240   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
1244           $worksheet->write_date_time('A1', '2004-05-13T23:20', $date_format);
1245
1246       The $date_string should be in the following format:
1247
1248           yyyy-mm-ddThh:mm:ss.sss
1249
1250       This conforms to an ISO8601 date but it should be noted that the full
1251       range of ISO8601 formats are not supported.
1252
1253       The following variations on the $date_string parameter are permitted:
1254
1255           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
1262       Note that the "T" is required in all cases.
1263
1264       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
1268           my $date_format = $workbook->add_format(num_format => 'mm/dd/yy');
1269           $worksheet->write_date_time('A1', '2004-05-13T23:20', $date_format);
1270
1271       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
1275       See also the date_time.pl program in the "examples" directory of the
1276       distro.
1277
1278   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
1285       The label is written using the "write()" method. Therefore it is
1286       possible to write strings, numbers or formulas as labels.
1287
1288       There are four web style URI's supported: "http://", "https://",
1289       "ftp://" and  "mailto:":
1290
1291           $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
1297       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
1301           $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
1311       All of the these URI types are recognised by the "write()" method, see
1312       above.
1313
1314       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
1318       In external links the workbook and worksheet name must be separated by
1319       the "#" character: "external:Workbook.xls#Sheet1!A1'".
1320
1321       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
1325           $worksheet->write_url('A14', 'external:c:\temp\foo.xls#my_name');
1326
1327       Note, you cannot currently create named ranges with
1328       "Spreadsheet::WriteExcel".
1329
1330       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
1336       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
1341       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
1345       Finally, you can avoid most of these quoting problems by using forward
1346       slashes. These are translated internally to backslashes:
1347
1348           $worksheet->write_url('A14', "external:c:/temp/foo.xls"             );
1349           $worksheet->write_url('A15', 'external://NETWORK/share/foo.xls'     );
1350
1351       See also, the note about "Cell notation".
1352
1353   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
1358           $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
1363       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
1367       There is no way to force this behaviour through the "write()" method.
1368
1369       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
1373       See also, the note about "Cell notation".
1374
1375   write_formula($row, $column, $formula, $format, $value)
1376       Write a formula or function to the cell specified by $row and $column:
1377
1378           $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
1385       See the note about "Cell notation". For more information about writing
1386       Excel formulas see "FORMULAS AND FUNCTIONS IN EXCEL"
1387
1388       See also the section "Improving performance when working with formulas"
1389       and the "store_formula()" and "repeat_formula()" methods.
1390
1391       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
1396           $worksheet->write('A1', '=2+2', $format, 4);
1397
1398       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
1401   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
1407       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
1411       "store_formula()" carries the same speed penalty as "write_formula()".
1412       However, in practice it will be used less frequently.
1413
1414       The return value of this method is a scalar that can be thought of as a
1415       reference to a formula.
1416
1417           my $sin = $worksheet->store_formula('=SIN(A1)');
1418           my $cos = $worksheet->store_formula('=COS(A1)');
1419
1420           $worksheet->repeat_formula('B1', $sin, $format, 'A1', 'A2');
1421           $worksheet->repeat_formula('C1', $cos, $format, 'A1', 'A2');
1422
1423       Although "store_formula()" is a worksheet method the return value can
1424       be used in any worksheet:
1425
1426           my $now = $worksheet->store_formula('=NOW()');
1427
1428           $worksheet1->repeat_formula('B1', $now);
1429           $worksheet2->repeat_formula('B1', $now);
1430           $worksheet3->repeat_formula('B1', $now);
1431
1432   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
1438       In many respects "repeat_formula()" behaves like "write_formula()"
1439       except that it is significantly faster.
1440
1441       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
1446           my $formula = $worksheet->store_formula('=A1 * 3 + 50');
1447
1448           for my $row (0..99) {
1449               $worksheet->repeat_formula($row, 1, $formula, $format, 'A1', 'A'.($row +1));
1450           }
1451
1452       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
1456       As usual, you can use "undef" if you don't wish to specify a $format:
1457
1458           $worksheet->repeat_formula('B2', $formula, $format, 'A1', 'A2');
1459           $worksheet->repeat_formula('B3', $formula, undef,   'A1', 'A3');
1460
1461       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
1465           my $formula = $worksheet->store_formula('=A1 + A1');
1466
1467           # Gives '=B1 + A1'
1468           $worksheet->repeat_formula('B1', $formula, undef, 'A1', 'B1');
1469
1470           # Gives '=B1 + B1'
1471           $worksheet->repeat_formula('B2', $formula, undef, ('A1', 'B1') x 2);
1472
1473       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
1477           $worksheet->repeat_formula('B1', $formula, $format, qr/A1/, 'A2');
1478
1479       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
1485       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
1489           my $formula = $worksheet->store_formula('=A1 + SIN(A1)');
1490
1491           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
1498       However it contains a bug. In the last iteration of the loop when $row
1499       is 10 the following substitutions will occur:
1500
1501           s/A1/A10/;    changes    =A1 + SIN(A1)     to    =A10 + SIN(A1)
1502           s/A1/A10/;    changes    =A10 + SIN(A1)    to    =A100 + SIN(A1) # !!
1503
1504       The solution in this case is to use a more explicit match such as
1505       "qw/^A1$/":
1506
1507               $worksheet->repeat_formula($row -1, 1, $formula, undef,
1508                                           qw/^A1$/, 'A' . $row,
1509                                           qw/^A1$/, 'A' . $row
1510                                         );
1511
1512       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
1516           my $formula = $worksheet->store_formula('=A10 + A11');
1517
1518           $worksheet->repeat_formula('A1', $formula, undef,
1519                                       qw/A10/, 'A11',   #! Bad.
1520                                       qw/A11/, 'A12'    #! Bad.
1521                                     );
1522
1523       However, the actual substitution yields "=A12 + A11":
1524
1525           s/A10/A11/;    changes    =A10 + A11    to    =A11 + A11
1526           s/A11/A12/;    changes    =A11 + A11    to    =A12 + A11 # !!
1527
1528       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
1532           my $formula = $worksheet->store_formula('=X10 + Y11');
1533
1534           $worksheet->repeat_formula('A1', $formula, undef,
1535                                       qw/X10/, 'A11',
1536                                       qw/Y11/, 'A12'
1537                                     );
1538
1539       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
1542           my $formula = $worksheet->store_formula('=A1*5+4');
1543           print "@$formula\n";
1544
1545       See also the "repeat.pl" program in the "examples" directory of the
1546       distro.
1547
1548   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
1554       The following example shows how to add a comment to a cell:
1555
1556           $worksheet->write        (2, 2, 'Hello');
1557           $worksheet->write_comment(2, 2, 'This is a comment.');
1558
1559       As usual you can replace the $row and $column parameters with an "A1"
1560       cell reference. See the note about "Cell notation".
1561
1562           $worksheet->write        ('C3', 'Hello');
1563           $worksheet->write_comment('C3', 'This is a comment.');
1564
1565       On systems with "perl 5.8" and later the "write_comment()" method will
1566       also handle strings in "UTF-8" format.
1567
1568           $worksheet->write_comment('C3', "\x{263a}");       # Smiley
1569           $worksheet->write_comment('C4', 'Comment ca va?');
1570
1571       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
1575           $worksheet->write_comment('C3', 'Hello', visible => 1, author => 'Perl');
1576
1577       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
1582           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
1597       Option: encoding
1598           This option is used to indicate that the comment string is encoded
1599           as "UTF-16BE".
1600
1601               my $comment = pack 'n', 0x263a; # UTF-16BE Smiley symbol
1602
1603               $worksheet->write_comment('C3', $comment, encoding => 1);
1604
1605           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
1609       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
1616               $worksheet->write_comment('C3', 'Atonement', author => 'Ian McEwan');
1617
1618       Option: author_encoding
1619           This option is used to indicate that the author string is encoded
1620           as "UTF-16BE".
1621
1622       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
1630               $worksheet->write_comment('C3', 'Hello', visible => 1);
1631
1632           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
1637               $worksheet->write_comment('C3', 'Hello', visible => 0);
1638
1639       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
1643               $worksheet->write_comment('C3', 'Hello', x_scale => 2);
1644               $worksheet->write_comment('C4', 'Hello', x_scale => 4.2);
1645
1646       Option: width
1647           This option is used to set the width of the cell comment box
1648           explicitly in pixels.
1649
1650               $worksheet->write_comment('C3', 'Hello', width => 200);
1651
1652       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
1656               $worksheet->write_comment('C3', 'Hello', y_scale => 2);
1657               $worksheet->write_comment('C4', 'Hello', y_scale => 4.2);
1658
1659       Option: height
1660           This option is used to set the height of the cell comment box
1661           explicitly in pixels.
1662
1663               $worksheet->write_comment('C3', 'Hello', height => 200);
1664
1665       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
1670               $worksheet->write_comment('C3', 'Hello', color => 'green');
1671               $worksheet->write_comment('C4', 'Hello', color => 0x35);    # Orange
1672
1673       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
1681               $worksheet->write_comment('C3', 'Hello', start_cell => 'E2');
1682
1683       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
1687               $worksheet->write_comment('C3', 'Hello', start_row => 0);
1688
1689       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
1694               $worksheet->write_comment('C3', 'Hello', start_col => 4);
1695
1696       Option: x_offset
1697           This option is used to change the x offset, in pixels, of a comment
1698           within a cell:
1699
1700               $worksheet->write_comment('C3', $comment, x_offset => 30);
1701
1702       Option: y_offset
1703           This option is used to change the y offset, in pixels, of a comment
1704           within a cell:
1705
1706               $worksheet->write_comment('C3', $comment, x_offset => 30);
1707
1708       You can apply as many of these options as you require.
1709
1710       See also "ROW HEIGHTS AND WORKSHEET OBJECTS".
1711
1712   show_comments()
1713       This method is used to make all cell comments visible when a worksheet
1714       is opened.
1715
1716       Individual comments can be made visible using the "visible" parameter
1717       of the "write_comment" method (see above):
1718
1719           $worksheet->write_comment('C3', 'Hello', visible => 1);
1720
1721       If all of the cell comments have been made visible you can hide
1722       individual comments as follows:
1723
1724           $worksheet->write_comment('C3', 'Hello', visible => 0);
1725
1726   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
1730       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
1734       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
1739       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
1743           $worksheet->add_write_handler(qr/^\d\d\d\d$/, \&my_write);
1744
1745       (In the these examples the "qr" operator is used to quote the regular
1746       expression strings, see perlop for more details).
1747
1748       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
1752           $worksheet->add_write_handler(qr/^\d{7}$/, \&write_my_id);
1753
1754
1755           sub write_my_id {
1756               my $worksheet = shift;
1757               return $worksheet->write_string(@_);
1758           }
1759
1760       * You could also use the "keep_leading_zeros()" method for this.
1761
1762       Then if you call "write()" with an appropriate string it will be
1763       handled automatically:
1764
1765           # Writes 0000000. It would normally be written as a number; 0.
1766           $worksheet->write('A1', '0000000');
1767
1768       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
1772           $_[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
1780           *  It is good style to shift this off the list so the @_ is the same
1781              as the argument list seen by write().
1782
1783       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
1787       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
1791           sub write_my_id {
1792               my $worksheet = shift;
1793               my $col       = $_[1];
1794
1795               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
1804       Now, you will get different behaviour for the first column and other
1805       columns:
1806
1807           $worksheet->write('A1', '0000000'); # Writes 0000000
1808           $worksheet->write('B1', '0000000'); # Writes 0
1809
1810       You may add more than one handler in which case they will be called in
1811       the order that they were added.
1812
1813       Note, the "add_write_handler()" method is particularly suited for
1814       handling dates.
1815
1816       See the "write_handler 1-4" programs in the "examples" directory for
1817       further examples.
1818
1819   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
1824           $worksheet1->insert_image('A1', 'perl.bmp');
1825           $worksheet2->insert_image('A1', '../images/perl.bmp');
1826           $worksheet3->insert_image('A1', '.c:\images\perl.bmp');
1827
1828       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
1832           $worksheet1->insert_image('A1', 'perl.bmp', 32, 10);
1833
1834       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
1838           Wp = int(12We)   if We <  1
1839           Wp = int(7We +5) if We >= 1
1840           Hp = int(4/3He)
1841
1842           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
1848       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
1852       The parameters $scale_x and $scale_y can be used to scale the inserted
1853       image horizontally and vertically:
1854
1855           # 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
1858       See also the "images.pl" program in the "examples" directory of the
1859       distro.
1860
1861       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
1865       See also "ROW HEIGHTS AND WORKSHEET OBJECTS".
1866
1867   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
1872           my $chart = $workbook->add_chart( type => 'line', embedded => 1 );
1873
1874           # Configure the chart.
1875           ...
1876
1877           # Insert the chart into the a worksheet.
1878           $worksheet->insert_chart('E2', $chart);
1879
1880       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
1884       The $x, $y, $scale_x and $scale_y parameters are optional.
1885
1886       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
1891           $worksheet1->insert_chart('E2', $chart, 3, 3);
1892
1893       The parameters $scale_x and $scale_y can be used to scale the inserted
1894       image horizontally and vertically:
1895
1896           # Scale the width by 120% and the height by 150%
1897           $worksheet->insert_chart('E2', $chart, 0, 0, 1.2, 1.5);
1898
1899       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
1905       See also "ROW HEIGHTS AND WORKSHEET OBJECTS".
1906
1907   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
1914       Here is an example:
1915
1916           $worksheet->embed_chart('B2', 'sales_chart.bin');
1917
1918       The $x, $y, $scale_x and $scale_y parameters are optional. See
1919       "insert_chart()" above for details.
1920
1921   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
1925           $worksheet->data_validation('B3',
1926               {
1927                   validate => 'integer',
1928                   criteria => '>',
1929                   value    => 100,
1930               });
1931
1932           $worksheet->data_validation('B5:B9',
1933               {
1934                   validate => 'list',
1935                   value    => ['open', 'high', 'close'],
1936               });
1937
1938       This method contains a lot of parameters and is described in detail in
1939       a separate section "DATA VALIDATION IN EXCEL".
1940
1941       See also the "data_validate.pl" program in the examples directory of
1942       the distro
1943
1944   get_name()
1945       The "get_name()" method is used to retrieve the name of a worksheet.
1946       For example:
1947
1948           foreach my $sheet ($workbook->sheets()) {
1949               print $sheet->get_name();
1950           }
1951
1952       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
1956   activate()
1957       The "activate()" method is used to specify which worksheet is initially
1958       visible in a multi-sheet workbook:
1959
1960           $worksheet1 = $workbook->add_worksheet('To');
1961           $worksheet2 = $workbook->add_worksheet('the');
1962           $worksheet3 = $workbook->add_worksheet('wind');
1963
1964           $worksheet3->activate();
1965
1966       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
1970       The default active worksheet is the first worksheet.
1971
1972   select()
1973       The "select()" method is used to indicate that a worksheet is selected
1974       in a multi-sheet workbook:
1975
1976           $worksheet1->activate();
1977           $worksheet2->select();
1978           $worksheet3->select();
1979
1980       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
1985   hide()
1986       The "hide()" method is used to hide a worksheet:
1987
1988           $worksheet2->hide();
1989
1990       You may wish to hide a worksheet in order to avoid confusing a user
1991       with intermediate data or calculations.
1992
1993       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
1999           $worksheet2->activate();
2000           $worksheet1->hide();
2001
2002   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
2009           for (1..20) {
2010               $workbook->add_worksheet;
2011           }
2012
2013           $worksheet21 = $workbook->add_worksheet();
2014           $worksheet22 = $workbook->add_worksheet();
2015
2016           $worksheet21->set_first_sheet();
2017           $worksheet22->activate();
2018
2019       This method is not required very often. The default value is the first
2020       worksheet.
2021
2022   protect($password)
2023       The "protect()" method is used to protect a worksheet from
2024       modification:
2025
2026           $worksheet->protect();
2027
2028       It can be turned off in Excel via the "Tools->Protection->Unprotect
2029       Sheet" menu command.
2030
2031       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
2037           # Set some format properties
2038           my $unlocked  = $workbook->add_format(locked => 0);
2039           my $hidden    = $workbook->add_format(hidden => 1);
2040
2041           # Enable worksheet protection
2042           $worksheet->protect();
2043
2044           # This cell cannot be edited, it is locked by default
2045           $worksheet->write('A1', '=1+2');
2046
2047           # This cell can be edited
2048           $worksheet->write('A2', '=1+2', $unlocked);
2049
2050           # The formula in this cell isn't visible
2051           $worksheet->write('A3', '=1+2', $hidden);
2052
2053       See also the "set_locked" and "set_hidden" format methods in "CELL
2054       FORMATTING".
2055
2056       You can optionally add a password to the worksheet protection:
2057
2058           $worksheet->protect('drowssap');
2059
2060       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
2068   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
2076       Examples:
2077
2078           $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
2085       The default cell selections is (0, 0), 'A1'.
2086
2087   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
2091       The most common use for this method is to change the height of a row:
2092
2093           $worksheet->set_row(0, 20); # Row 1 height set to 20
2094
2095       If you wish to set the format without changing the height you can pass
2096       "undef" as the height parameter:
2097
2098           $worksheet->set_row(0, undef, $format);
2099
2100       The $format parameter will be applied to any cells in the row that
2101       don't  have a format. For example
2102
2103           $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
2107       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
2111       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
2115           $worksheet->set_row(0, 20,    $format, 1);
2116           $worksheet->set_row(1, undef, undef,   1);
2117
2118       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
2123       The following example sets an outline level of 1 for rows 1 and 2
2124       (zero-indexed):
2125
2126           $worksheet->set_row(1, undef, undef, 0, 1);
2127           $worksheet->set_row(2, undef, undef, 0, 1);
2128
2129       The $hidden parameter can also be used to hide collapsed outlined rows
2130       when used in conjunction with the $level parameter.
2131
2132           $worksheet->set_row(1, undef, undef, 1, 1);
2133           $worksheet->set_row(2, undef, undef, 1, 1);
2134
2135       For collapsed outlines you should also indicate which row has the
2136       collapsed "+" symbol using the optional $collapsed parameter.
2137
2138           $worksheet->set_row(3, undef, undef, 0, 0, 1);
2139
2140       For a more complete example see the "outline.pl" and
2141       "outline_collapsed.pl" programs in the examples directory of the
2142       distro.
2143
2144       Excel allows up to 7 outline levels. Therefore the $level parameter
2145       should be in the range "0 <= $level <= 7".
2146
2147   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
2153       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
2157       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
2161       Examples:
2162
2163           $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
2168       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
2174       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
2178           $worksheet->set_column(0, 0, undef, $format);
2179
2180       The $format parameter will be applied to any cells in the column that
2181       don't  have a format. For example
2182
2183           $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
2187       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
2191       A default row format takes precedence over a default column format
2192
2193           $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
2198       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
2202           $worksheet->set_column('D:D', 20,    $format, 1);
2203           $worksheet->set_column('E:E', undef, undef,   1);
2204
2205       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
2210       The following example sets an outline level of 1 for columns B to G:
2211
2212           $worksheet->set_column('B:G', undef, undef, 0, 1);
2213
2214       The $hidden parameter can also be used to hide collapsed outlined
2215       columns when used in conjunction with the $level parameter.
2216
2217           $worksheet->set_column('B:G', undef, undef, 1, 1);
2218
2219       For collapsed outlines you should also indicate which row has the
2220       collapsed "+" symbol using the optional $collapsed parameter.
2221
2222           $worksheet->set_column('H:H', undef, undef, 0, 0, 1);
2223
2224       For a more complete example see the "outline.pl" and
2225       "outline_collapsed.pl" programs in the examples directory of the
2226       distro.
2227
2228       Excel allows up to 7 outline levels. Therefore the $level parameter
2229       should be in the range "0 <= $level <= 7".
2230
2231   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
2236       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
2242           $worksheet->outline_settings(0);
2243
2244       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
2248       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
2253       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
2260       The default settings for all of these parameters correspond to Excel's
2261       default parameters.
2262
2263       The worksheet parameters controlled by "outline_settings()" are rarely
2264       used.
2265
2266   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
2272       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
2280       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
2283       Examples:
2284
2285           $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
2292       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
2297           $worksheet->freeze_panes(1, 0, 20, 0);
2298
2299       You cannot use A1 notation for the $top_row and $left_col parameters.
2300
2301       See also the "panes.pl" program in the "examples" directory of the
2302       distribution.
2303
2304   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
2310       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
2318       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
2323       Example:
2324
2325           $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
2329       You cannot use A1 notation with this method.
2330
2331       See also the "freeze_panes()" method and the "panes.pl" program in the
2332       "examples" directory of the distribution.
2333
2334       Note: This "split_panes()" method was called "thaw_panes()" in older
2335       versions. The older name is still available for backwards
2336       compatibility.
2337
2338   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
2345       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
2349           my $format = $workbook->add_format(
2350                                               border  => 6,
2351                                               valign  => 'vcenter',
2352                                               align   => 'center',
2353                                             );
2354
2355           $worksheet->merge_range('B3:D4', 'Vertical and horizontal', $format);
2356
2357       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
2363       The $utf_16_be parameter is optional, see below.
2364
2365       "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
2369       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
2373       On systems with "perl 5.8" and later the "merge_range()" method will
2374       also handle strings in "UTF-8" format.
2375
2376           $worksheet->merge_range('B3:D4', "\x{263a}", $format); # Smiley
2377
2378       On earlier Perl systems your can specify "UTF-16BE" worksheet names
2379       using an additional optional parameter:
2380
2381           my $str = pack 'n', 0x263a;
2382           $worksheet->merge_range('B3:D4', $str, $format, 1); # Smiley
2383
2384       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
2387   set_zoom($scale)
2388       Set the worksheet zoom factor in the range "10 <= $scale <= 400":
2389
2390           $worksheet1->set_zoom(50);
2391           $worksheet2->set_zoom(75);
2392           $worksheet3->set_zoom(300);
2393           $worksheet4->set_zoom(400);
2394
2395       The default zoom factor is 100. You cannot zoom to "Selection" because
2396       it is calculated by Excel at run-time.
2397
2398       Note, "set_zoom()" does not affect the scale of the printed page. For
2399       that you should use "set_print_scale()".
2400
2401   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
2406           $worksheet->right_to_left();
2407
2408       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
2411   hide_zero()
2412       The "hide_zero()" method is used to hide any zero values that appear in
2413       cells.
2414
2415           $worksheet->hide_zero();
2416
2417       In Excel this option is found under Tools->Options->View.
2418
2419   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
2426           $worksheet1->set_tab_color('red');
2427           $worksheet2->set_tab_color(0x0C);
2428
2429       See the "tab_colors.pl" program in the examples directory of the
2430       distro.
2431
2432   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
2438       To add an autofilter to a worksheet:
2439
2440           $worksheet->autofilter(0, 0, 10, 3);
2441           $worksheet->autofilter('A1:D11');    # Same as above in A1 notation.
2442
2443       Filter conditions can be applied using the "filter_column()" method.
2444
2445       See the "autofilter.pl" program in the examples directory of the distro
2446       for a more detailed example.
2447
2448   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
2452       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
2459       The conditions for the filter are specified using simple expressions:
2460
2461           $worksheet->filter_column('A', 'x > 2000');
2462           $worksheet->filter_column('B', 'x > 2000 and x < 5000');
2463
2464       The $column parameter can either be a zero indexed column number or a
2465       string column name.
2466
2467       The following operators are available:
2468
2469           Operator        Synonyms
2470              ==           =   eq  =~
2471              !=           <>  ne  !=
2472              >
2473              <
2474              >=
2475              <=
2476
2477              and          &&
2478              or           ||
2479
2480       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
2484       An expression can comprise a single statement or two statements
2485       separated by the "and" and "or" operators. For example:
2486
2487           'x <  2000'
2488           'x >  2000'
2489           'x == 2000'
2490           'x >  2000 and x <  5000'
2491           'x == 2000 or  x == 5000'
2492
2493       Filtering of blank or non-blank data can be achieved by using a value
2494       of "Blanks" or "NonBlanks" in the expression:
2495
2496           'x == Blanks'
2497           'x == NonBlanks'
2498
2499       Top 10 style filters can be specified using a expression like the
2500       following:
2501
2502           Top|Bottom 1-500 Items|%
2503
2504       For example:
2505
2506           'Top    10 Items'
2507           'Bottom  5 Items'
2508           'Top    25 %'
2509           'Bottom 50 %'
2510
2511       Excel also allows some simple string matching operations:
2512
2513           '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
2520       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
2525       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
2529           'x     < 2000'
2530           'col   < 2000'
2531           'Price < 2000'
2532
2533       Also, note that a filter condition can only be applied to a column in a
2534       range specified by the "autofilter()" Worksheet method.
2535
2536       See the "autofilter.pl" program in the examples directory of the distro
2537       for a more detailed example.
2538

PAGE SET-UP METHODS

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
2545       The following methods are available for page set-up:
2546
2547           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
2568       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
2573           foreach $worksheet ($workbook->sheets()) {
2574              $worksheet->set_landscape();
2575           }
2576
2577   set_landscape()
2578       This method is used to set the orientation of a worksheet's printed
2579       page to landscape:
2580
2581           $worksheet->set_landscape(); # Landscape mode
2582
2583   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
2588           $worksheet->set_portrait(); # Portrait mode
2589
2590   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
2594           $worksheet->set_page_view();
2595
2596   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
2600           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
2645       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
2650           $worksheet->set_paper(1); # US Letter
2651           $worksheet->set_paper(9); # A4
2652
2653       If you do not specify a paper type the worksheet will print using the
2654       printer's default paper.
2655
2656   center_horizontally()
2657       Center the worksheet data horizontally between the margins on the
2658       printed page:
2659
2660           $worksheet->center_horizontally();
2661
2662   center_vertically()
2663       Center the worksheet data vertically between the margins on the printed
2664       page:
2665
2666           $worksheet->center_vertically();
2667
2668   set_margins($inches)
2669       There are several methods available for setting the worksheet margins
2670       on the printed page:
2671
2672           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
2680       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
2684   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
2689       The available control character are:
2690
2691           Control             Category            Description
2692           =======             ========            ===========
2693           &L                  Justification       Left
2694           &C                                      Center
2695           &R                                      Right
2696
2697           &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
2705           &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
2713           &&                  Miscellaneous       Literal ampersand &
2714
2715       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
2719       For example (with ASCII art representation of the results):
2720
2721           $worksheet->set_header('&LHello');
2722
2723            ---------------------------------------------------------------
2724           |                                                               |
2725           | Hello                                                         |
2726           |                                                               |
2727
2728
2729           $worksheet->set_header('&CHello');
2730
2731            ---------------------------------------------------------------
2732           |                                                               |
2733           |                          Hello                                |
2734           |                                                               |
2735
2736
2737           $worksheet->set_header('&RHello');
2738
2739            ---------------------------------------------------------------
2740           |                                                               |
2741           |                                                         Hello |
2742           |                                                               |
2743
2744       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
2748           $worksheet->set_header('Hello');
2749
2750            ---------------------------------------------------------------
2751           |                                                               |
2752           |                          Hello                                |
2753           |                                                               |
2754
2755       You can have text in each of the justification regions:
2756
2757           $worksheet->set_header('&LCiao&CBello&RCielo');
2758
2759            ---------------------------------------------------------------
2760           |                                                               |
2761           | Ciao                     Bello                          Cielo |
2762           |                                                               |
2763
2764       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
2768           $worksheet->set_header('&CPage &P of &N');
2769
2770            ---------------------------------------------------------------
2771           |                                                               |
2772           |                        Page 1 of 6                            |
2773           |                                                               |
2774
2775
2776           $worksheet->set_header('&CUpdated at &T');
2777
2778            ---------------------------------------------------------------
2779           |                                                               |
2780           |                    Updated at 12:30 PM                        |
2781           |                                                               |
2782
2783       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
2786           $worksheet1->set_header('&C&30Hello Big'  );
2787           $worksheet2->set_header('&C&10Hello Small');
2788
2789       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
2795           $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
2799       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
2806           .LeftHeader   = ""
2807           .CenterHeader = "&""Times New Roman,Regular""Hello"
2808           .RightHeader  = ""
2809
2810       To include a single literal ampersand "&" in a header or footer you
2811       should use a double ampersand "&&":
2812
2813           $worksheet1->set_header('&CCuriouser && Curiouser - Attorneys at Law');
2814
2815       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
2820           $worksheet->set_header('&CHello', 0.75);
2821
2822       The header and footer margins are independent of the top and bottom
2823       margins.
2824
2825       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
2829       On systems with "perl 5.8" and later the "set_header()" method can also
2830       handle Unicode strings in "UTF-8" format.
2831
2832           $worksheet->set_header("&C\x{263a}")
2833
2834       See, also the "headers.pl" program in the "examples" directory of the
2835       distribution.
2836
2837   set_footer()
2838       The syntax of the "set_footer()" method is the same as "set_header()",
2839       see above.
2840
2841   repeat_rows($first_row, $last_row)
2842       Set the number of rows to repeat at the top of each printed page.
2843
2844       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
2850           $worksheet1->repeat_rows(0);    # Repeat the first row
2851           $worksheet2->repeat_rows(0, 1); # Repeat the first two rows
2852
2853   repeat_columns($first_col, $last_col)
2854       Set the columns to repeat at the left hand side of each printed page.
2855
2856       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
2864           $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
2869   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
2876           $worksheet->hide_gridlines();
2877
2878       The following values of $option are valid:
2879
2880           0 : Don't hide gridlines
2881           1 : Hide printed gridlines only
2882           2 : Hide screen and printed gridlines
2883
2884       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
2887   print_row_col_headers()
2888       Set the option to print the row and column headers on the printed page.
2889
2890       An Excel worksheet looks something like the following;
2891
2892            ------------------------------------------
2893           |   |   A   |   B   |   C   |   D   |  ...
2894            ------------------------------------------
2895           | 1 |       |       |       |       |  ...
2896           | 2 |       |       |       |       |  ...
2897           | 3 |       |       |       |       |  ...
2898           | 4 |       |       |       |       |  ...
2899           |...|  ...  |  ...  |  ...  |  ...  |  ...
2900
2901       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
2907           $worksheet->print_row_col_headers();
2908
2909       Do not confuse these headers with page headers as described in the
2910       "set_header()" section above.
2911
2912   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
2917           $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
2921   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
2925           $worksheet->print_across();
2926
2927       The default page order is shown below for a worksheet that extends over
2928       4 pages. The order is called "down then across":
2929
2930           [1] [3]
2931           [2] [4]
2932
2933       However, by using the "print_across" method the print order will be
2934       changed to "across then down":
2935
2936           [1] [2]
2937           [3] [4]
2938
2939   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
2947           $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
2951       The print area can be defined using the "print_area()" method as
2952       described above.
2953
2954       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
2958           $worksheet1->fit_to_pages(1, 0); # 1 page wide and as long as necessary
2959           $worksheet2->fit_to_pages(1);    # The same
2960
2961       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
2966       Note that "fit_to_pages()" will override any manual page breaks that
2967       are defined in the worksheet.
2968
2969   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
2973           $worksheet->set_start_page(2);
2974
2975   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
2979           $worksheet1->set_print_scale(50);
2980           $worksheet2->set_print_scale(75);
2981           $worksheet3->set_print_scale(300);
2982           $worksheet4->set_print_scale(400);
2983
2984       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
2988       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
2993   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
3001           $worksheet1->set_h_pagebreaks(20); # Break between row 20 and 21
3002
3003       The "set_h_pagebreaks()" method will accept a list of page breaks and
3004       you can call it more than once:
3005
3006           $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
3009       Note: If you specify the "fit to page" option via the "fit_to_pages()"
3010       method it will override all manual page breaks.
3011
3012       There is a silent limitation of about 1000 horizontal page breaks per
3013       worksheet in line with an Excel internal limitation.
3014
3015   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
3023           $worksheet1->set_v_pagebreaks(20); # Break between column 20 and 21
3024
3025       The "set_v_pagebreaks()" method will accept a list of page breaks and
3026       you can call it more than once:
3027
3028           $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
3031       Note: If you specify the "fit to page" option via the "fit_to_pages()"
3032       method it will override all manual page breaks.
3033

CELL FORMATTING

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
3040   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
3044           my $format1 = $workbook->add_format();       # Set properties later
3045           my $format2 = $workbook->add_format(%props); # Set at creation
3046
3047       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
3051       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
3055           $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
3060       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
3064           $worksheet->set_row(0, 15, $format);
3065           $worksheet->set_column(0, 0, 15, $format);
3066
3067   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
3071           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
3084           Number     Numeric format    num_format      set_num_format()
3085
3086           Protection Lock cells        locked          set_locked()
3087                      Hide formulas     hidden          set_hidden()
3088
3089           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
3098           Pattern    Cell pattern      pattern         set_pattern()
3099                      Background color  bg_color        set_bg_color()
3100                      Foreground color  fg_color        set_fg_color()
3101
3102           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
3113       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
3117           my $format = $workbook->add_format();
3118           $format->set_bold();
3119           $format->set_color('red');
3120
3121       By comparison the properties can be set directly by passing a hash of
3122       properties to the Format constructor:
3123
3124           my $format = $workbook->add_format(bold => 1, color => 'red');
3125
3126       or after the Format has been constructed by means of the
3127       "set_format_properties()" method as follows:
3128
3129           my $format = $workbook->add_format();
3130           $format->set_format_properties(bold => 1, color => 'red');
3131
3132       You can also store the properties in one or more named hashes and pass
3133       them to the required method:
3134
3135           my %font    = (
3136                           font  => 'Arial',
3137                           size  => 12,
3138                           color => 'blue',
3139                           bold  => 1,
3140                         );
3141
3142           my %shading = (
3143                           bg_color => 'green',
3144                           pattern  => 1,
3145                         );
3146
3147
3148           my $format1 = $workbook->add_format(%font);           # Font only
3149           my $format2 = $workbook->add_format(%font, %shading); # Font and shading
3150
3151       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
3160       The Perl/Tk style of adding properties is also supported:
3161
3162           my %font    = (
3163                           -font      => 'Arial',
3164                           -size      => 12,
3165                           -color     => 'blue',
3166                           -bold      => 1,
3167                         );
3168
3169   Working with formats
3170       The default format is Arial 10 with all other properties off.
3171
3172       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
3178           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
3185       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
3190       In general a method call without an argument will turn a property on,
3191       for example:
3192
3193           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

FORMAT METHODS

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
3205       The following Format methods are available:
3206
3207           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
3241       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
3245   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
3249           my $format = $workbook->add_format();
3250           $format->set_format_properties(bold => 1, color => 'red');
3251
3252       However, this method is here mainly for legacy reasons. It is
3253       preferable to set the properties in the format constructor:
3254
3255           my $format = $workbook->add_format(bold => 1, color => 'red');
3256
3257   set_font($fontname)
3258           Default state:      Font is Arial
3259           Default action:     None
3260           Valid args:         Any valid font name
3261
3262       Specify the font used:
3263
3264           $format->set_font('Times New Roman');
3265
3266       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
3271   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
3276       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
3280           my $format = $workbook->add_format();
3281           $format->set_size(30);
3282
3283   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
3304       Set the font colour. The "set_color()" method is used as follows:
3305
3306           my $format = $workbook->add_format();
3307           $format->set_color('red');
3308           $worksheet->write(0, 0, 'wheelbarrow', $format);
3309
3310       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
3314       For additional examples see the 'Named colors' and 'Standard colors'
3315       worksheets created by formats.pl in the examples directory.
3316
3317       See also "COLOURS IN EXCEL".
3318
3319   set_bold()
3320           Default state:      bold is off
3321           Default action:     Turn bold on
3322           Valid args:         0, 1 [1]
3323
3324       Set the bold property of the font:
3325
3326           $format->set_bold();  # Turn bold on
3327
3328       [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
3332   set_italic()
3333           Default state:      Italic is off
3334           Default action:     Turn italic on
3335           Valid args:         0, 1
3336
3337       Set the italic property of the font:
3338
3339           $format->set_italic();  # Turn italic on
3340
3341   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
3350       Set the underline property of the font.
3351
3352           $format->set_underline();   # Single underline
3353
3354   set_font_strikeout()
3355           Default state:      Strikeout is off
3356           Default action:     Turn strikeout on
3357           Valid args:         0, 1
3358
3359       Set the strikeout property of the font.
3360
3361   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
3368       Set the superscript/subscript property of the font. This format is
3369       currently not very useful.
3370
3371   set_font_outline()
3372           Default state:      Outline is off
3373           Default action:     Turn outline on
3374           Valid args:         0, 1
3375
3376       Macintosh only.
3377
3378   set_font_shadow()
3379           Default state:      Shadow is off
3380           Default action:     Turn shadow on
3381           Valid args:         0, 1
3382
3383       Macintosh only.
3384
3385   set_num_format()
3386           Default state:      General format
3387           Default action:     Format index 1
3388           Valid args:         See the following table
3389
3390       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
3395       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
3398           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
3403           $worksheet->write(0, 0, 36892.521, $format1);      # 1 Jan 2001
3404           $worksheet->write(0, 0, 36892.521, $format2);      # 1-Jan-01
3405
3406       Using format strings you can define very sophisticated formatting of
3407       numbers.
3408
3409           $format01->set_num_format('0.000');
3410           $worksheet->write(0,  0, 3.1415926, $format01);    # 3.142
3411
3412           $format02->set_num_format('#,##0');
3413           $worksheet->write(1,  0, 1234.56,   $format02);    # 1,235
3414
3415           $format03->set_num_format('#,##0.00');
3416           $worksheet->write(2,  0, 1234.56,   $format03);    # 1,234.56
3417
3418           $format04->set_num_format('$0.00');
3419           $worksheet->write(3,  0, 49.99,     $format04);    # $49.99
3420
3421           # 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
3424           $format07->set_num_format('mm/dd/yy');
3425           $worksheet->write(6,  0, 36892.521, $format07);    # 01/01/01
3426
3427           $format08->set_num_format('mmm d yyyy');
3428           $worksheet->write(7,  0, 36892.521, $format08);    # Jan 1 2001
3429
3430           $format09->set_num_format('d mmmm yyyy');
3431           $worksheet->write(8,  0, 36892.521, $format09);    # 1 January 2001
3432
3433           $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
3436           $format11->set_num_format('0 "dollar and" .00 "cents"');
3437           $worksheet->write(10, 0, 1.87,      $format11);    # 1 dollar and .87 cents
3438
3439           # 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
3445           # Zip code
3446           $format13->set_num_format('00000');
3447           $worksheet->write(14, 0, '01209',   $format13);
3448
3449       The number system used for dates is described in "DATES AND TIME IN
3450       EXCEL".
3451
3452       The colour format should have one of the following values:
3453
3454           [Black] [Blue] [Cyan] [Green] [Magenta] [Red] [White] [Yellow]
3455
3456       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
3460       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
3465       You should ensure that the format string is valid in Excel prior to
3466       using it in WriteExcel.
3467
3468       Excel's built-in formats are shown in the following table:
3469
3470           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
3509       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
3514       Note 1. Numeric formats 23 to 36 are not documented by Microsoft and
3515       may differ in international versions.
3516
3517       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
3520       Note 3. The red negative numeric formats display slightly differently
3521       in Excel 5 and Excel 97-2000.
3522
3523   set_locked()
3524           Default state:      Cell locking is on
3525           Default action:     Turn locking on
3526           Valid args:         0, 1
3527
3528       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
3533           my $locked  = $workbook->add_format();
3534           $locked->set_locked(1); # A non-op
3535
3536           my $unlocked = $workbook->add_format();
3537           $locked->set_locked(0);
3538
3539           # Enable worksheet protection
3540           $worksheet->protect();
3541
3542           # This cell cannot be edited.
3543           $worksheet->write('A1', '=1+2', $locked);
3544
3545           # This cell can be edited.
3546           $worksheet->write('A2', '=1+2', $unlocked);
3547
3548       Note: This offers weak protection even with a password, see the note in
3549       relation to the "protect()" method.
3550
3551   set_hidden()
3552           Default state:      Formula hiding is off
3553           Default action:     Turn hiding on
3554           Valid args:         0, 1
3555
3556       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
3561           my $hidden = $workbook->add_format();
3562           $hidden->set_hidden();
3563
3564           # Enable worksheet protection
3565           $worksheet->protect();
3566
3567           # The formula in this cell isn't visible
3568           $worksheet->write('A1', '=1+2', $hidden);
3569
3570       Note: This offers weak protection even with a password, see the note in
3571       relation to the "protect()" method.
3572
3573   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
3583                               'top'               Vertical
3584                               'vcenter'
3585                               'bottom'
3586                               'vjustify'
3587
3588       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
3592           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
3598       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
3602       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
3607       For further examples see the 'Alignment' worksheet created by
3608       formats.pl.
3609
3610   set_center_across()
3611           Default state:      Center across selection is off
3612           Default action:     Turn center across on
3613           Valid args:         1
3614
3615       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
3619       Only one cell should contain the text, the other cells should be blank:
3620
3621           my $format = $workbook->add_format();
3622           $format->set_center_across();
3623
3624           $worksheet->write(1, 1, 'Center across selection', $format);
3625           $worksheet->write_blank(1, 2, $format);
3626
3627       See also the "merge1.pl" to "merge6.pl" programs in the "examples"
3628       directory and the "merge_range()" method.
3629
3630   set_text_wrap()
3631           Default state:      Text wrap is off
3632           Default action:     Turn text wrap on
3633           Valid args:         0, 1
3634
3635       Here is an example using the text wrap property, the escape character
3636       "\n" is used to indicate the end of line:
3637
3638           my $format = $workbook->add_format();
3639           $format->set_text_wrap();
3640           $worksheet->write(0, 0, "It's\na bum\nwrap", $format);
3641
3642       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
3647   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
3652       Set the rotation of the text in a cell. The rotation can be any angle
3653       in the range -90 to 90 degrees.
3654
3655           my $format = $workbook->add_format();
3656           $format->set_rotation(30);
3657           $worksheet->write(0, 0, 'This text is rotated', $format);
3658
3659       The angle 270 is also supported. This indicates text where the letters
3660       run from top to bottom.
3661
3662   set_indent()
3663           Default state:      Text indentation is off
3664           Default action:     Indent text 1 level
3665           Valid args:         Positive integers
3666
3667       This method can be used to indent text. The argument, which should be
3668       an integer, is taken as the level of indentation:
3669
3670           my $format = $workbook->add_format();
3671           $format->set_indent(2);
3672           $worksheet->write(0, 0, 'This text is indented', $format);
3673
3674       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
3678   set_shrink()
3679           Default state:      Text shrinking is off
3680           Default action:     Turn "shrink to fit" on
3681           Valid args:         1
3682
3683       This method can be used to shrink text so that it fits in a cell.
3684
3685           my $format = $workbook->add_format();
3686           $format->set_shrink();
3687           $worksheet->write(0, 0, 'Honey, I shrunk the text!', $format);
3688
3689   set_text_justlast()
3690           Default state:      Justify last is off
3691           Default action:     Turn justify last on
3692           Valid args:         0, 1
3693
3694       Only applies to Far Eastern versions of Excel.
3695
3696   set_pattern()
3697           Default state:      Pattern is off
3698           Default action:     Solid fill is on
3699           Valid args:         0 .. 18
3700
3701       Set the background pattern of a cell.
3702
3703       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
3708   set_bg_color()
3709           Default state:      Color is off
3710           Default action:     Solid fill.
3711           Valid args:         See set_color()
3712
3713       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
3718       Here is an example of how to set up a solid fill in a cell:
3719
3720           my $format = $workbook->add_format();
3721
3722           $format->set_pattern(); # This is optional when using a solid fill
3723
3724           $format->set_bg_color('green');
3725           $worksheet->write('A1', 'Ray', $format);
3726
3727       For further examples see the 'Patterns' worksheet created by
3728       formats.pl.
3729
3730   set_fg_color()
3731           Default state:      Color is off
3732           Default action:     Solid fill.
3733           Valid args:         See set_color()
3734
3735       The "set_fg_color()" method can be used to set the foreground colour of
3736       a pattern.
3737
3738       For further examples see the 'Patterns' worksheet created by
3739       formats.pl.
3740
3741   set_border()
3742           Also applies to:    set_bottom()
3743                               set_top()
3744                               set_left()
3745                               set_right()
3746
3747           Default state:      Border is off
3748           Default action:     Set border type 1
3749           Valid args:         0-13, See below.
3750
3751       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
3755       The following shows the border styles sorted by Spreadsheet::WriteExcel
3756       index number:
3757
3758           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
3775       The following shows the borders sorted by style:
3776
3777           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
3794       The following shows the borders in the order shown in the Excel Dialog.
3795
3796           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
3806       Examples of the available border styles are shown in the 'Borders'
3807       worksheet created by formats.pl.
3808
3809   set_border_color()
3810           Also applies to:    set_bottom_color()
3811                               set_top_color()
3812                               set_left_color()
3813                               set_right_color()
3814
3815           Default state:      Color is off
3816           Default action:     Undefined
3817           Valid args:         See set_color()
3818
3819       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
3825   copy($format)
3826       This method is used to copy all of the properties from one Format
3827       object to another:
3828
3829           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
3834           my $lorry2 = $workbook->add_format();
3835           $lorry2->copy($lorry1);
3836           $lorry2->set_color('yellow'); # lorry2 is bold, italic and yellow
3837
3838       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
3842       Note: this is not a copy constructor, both objects must exist prior to
3843       copying.
3844

UNICODE IN EXCEL

3846       The following is a brief introduction to handling Unicode in
3847       "Spreadsheet::WriteExcel".
3848
3849       For a more general introduction to Unicode handling in Perl see
3850       perlunitut and perluniintro.
3851
3852       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
3858       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
3863           # perl 5.8+ example:
3864           my $smiley = "\x{263A}";
3865
3866           $worksheet->write('A1', 'Hello world'); # ASCII
3867           $worksheet->write('A2', $smiley);       # UTF-8
3868
3869       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
3874           # perl 5.005 example:
3875           my $smiley = pack 'n', 0x263A;
3876
3877           $worksheet->write               ('A3', 'Hello world'); # ASCII
3878           $worksheet->write_utf16be_string('A4', $smiley);       # UTF-16
3879
3880       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
3887       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
3891           use Encode 'decode';
3892
3893           my $string = 'some string with koi8-r characters';
3894              $string = decode('koi8-r', $string); # koi8-r to utf8
3895
3896       Alternatively you can read data from an encoded file and convert it to
3897       "UTF-8" as you read it in:
3898
3899           my $file = 'unicode_koi8r.txt';
3900           open FH, '<:encoding(koi8-r)', $file  or die "Couldn't open $file: $!\n";
3901
3902           my $row = 0;
3903           while (<FH>) {
3904               # Data read in is now in utf8 format.
3905               chomp;
3906               $worksheet->write($row++, 0,  $_);
3907           }
3908
3909       These methodologies are explained in more detail in perlunitut,
3910       perluniintro and perlunicode.
3911
3912       See also the "unicode_*.pl" programs in the examples directory of the
3913       distro.
3914

COLOURS IN EXCEL

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
3921           my $format = $workbook->add_format(
3922                                               color => 12, # index for blue
3923                                               font  => 'Arial',
3924                                               size  => 12,
3925                                               bold  => 1,
3926                                            );
3927
3928       The most commonly used colours can also be accessed by name. The name
3929       acts as a simple alias for the colour index:
3930
3931           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
3948       For example:
3949
3950           my $font = $workbook->add_format(color => 'red');
3951
3952       Users of VBA in Excel should note that the equivalent colour indices
3953       are in the range 1..56 instead of 8..63.
3954
3955       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
3960           my $ferrari = $workbook->set_custom_color(40, 216, 12, 12);
3961
3962           my $format  = $workbook->add_format(
3963                                               bg_color => $ferrari,
3964                                               pattern  => 1,
3965                                               border   => 1
3966                                             );
3967
3968           $worksheet->write_blank('A1', $format);
3969
3970       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

DATES AND TIME IN EXCEL

3975       There are two important things to understand about dates and times in
3976       Excel:
3977
3978       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
3982       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
3985   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
3989           $worksheet->write('A1', '02/03/04'); # !! Writes a string not a date. !!
3990
3991       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
3994       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
3997       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
4001           #!/usr/bin/perl -w
4002
4003           use strict;
4004           use Spreadsheet::WriteExcel;
4005
4006           my $workbook  = Spreadsheet::WriteExcel->new('date_examples.xls');
4007           my $worksheet = $workbook->add_worksheet();
4008
4009           $worksheet->set_column('A:A', 30); # For extra visibility.
4010
4011           my $number    = 39506.5;
4012
4013           $worksheet->write('A1', $number);            #     39506.5
4014
4015           my $format2 = $workbook->add_format(num_format => 'dd/mm/yy');
4016           $worksheet->write('A2', $number , $format2); #     28/02/08
4017
4018           my $format3 = $workbook->add_format(num_format => 'mm/dd/yy');
4019           $worksheet->write('A3', $number , $format3); #     02/28/08
4020
4021           my $format4 = $workbook->add_format(num_format => 'd-m-yyyy');
4022           $worksheet->write('A4', $number , $format4); #     28-2-2008
4023
4024           my $format5 = $workbook->add_format(num_format => 'dd/mm/yy hh:mm');
4025           $worksheet->write('A5', $number , $format5); #     28/02/08 12:00
4026
4027           my $format6 = $workbook->add_format(num_format => 'd mmm yyyy');
4028           $worksheet->write('A6', $number , $format6); #     28 Feb 2008
4029
4030           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
4033   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
4039       For example, does "02/03/04" mean March 2 2004, February 3 2004 or even
4040       March 4 2002.
4041
4042       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
4046       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
4050           $worksheet->write_date_time('A2', '2001-01-01T12:20', $format);
4051
4052       See the "write_date_time()" section of the documentation for more
4053       details.
4054
4055       A general methodology for handling date strings with
4056       "write_date_time()" is:
4057
4058           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
4063       Here is an example:
4064
4065           #!/usr/bin/perl -w
4066
4067           use strict;
4068           use Spreadsheet::WriteExcel;
4069
4070           my $workbook    = Spreadsheet::WriteExcel->new('example.xls');
4071           my $worksheet   = $workbook->add_worksheet();
4072
4073           # Set the default format for dates.
4074           my $date_format = $workbook->add_format(num_format => 'mmm d yyyy');
4075
4076           # Increase column width to improve visibility of data.
4077           $worksheet->set_column('A:C', 20);
4078
4079           # Simulate reading from a data source.
4080           my $row = 0;
4081
4082           while (<DATA>) {
4083               chomp;
4084
4085               my $col  = 0;
4086               my @data = split ' ';
4087
4088               for my $item (@data) {
4089
4090                   # 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
4093                       # Change to the date format required by write_date_time().
4094                       my $date = sprintf "%4d-%02d-%02dT", $3, $2, $1;
4095
4096                       $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
4106           __DATA__
4107           Item    Cost    Date
4108           Book    10      1/9/2007
4109           Beer    4       12/9/2007
4110           Bed     500     5/10/2007
4111
4112       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
4118   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
4122       The Spreadsheet::WriteExcel::Utility module which is included in the
4123       distro has date/time handling functions:
4124
4125           use Spreadsheet::WriteExcel::Utility;
4126
4127           $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
4132       Note: some of these functions require additional CPAN modules.
4133
4134       For date conversions using the CPAN "DateTime" framework see
4135       DateTime::Format::Excel
4136       <http://search.cpan.org/search?dist=DateTime-Format-Excel>.
4137

OUTLINES AND GROUPING IN EXCEL

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
4143       Outlines can reduce complex data down to a few salient sub-totals or
4144       summaries.
4145
4146       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
4151                   ------------------------------------------
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
4165       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
4169                   ------------------------------------------
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
4179       Clicking on the minus sign on the level 1 outline will collapse the
4180       remaining rows as follows:
4181
4182                   ------------------------------------------
4183            1 2 3 |   |   A   |   B   |   C   |   D   |  ...
4184                   ------------------------------------------
4185                  | 1 |   A   |       |       |       |  ...
4186            +     | . |  ...  |  ...  |  ...  |  ...  |  ...
4187
4188       Grouping in "Spreadsheet::WriteExcel" is achieved by setting the
4189       outline level via the "set_row()" and "set_column()" worksheet methods:
4190
4191           set_row($row, $height, $format, $hidden, $level, $collapsed)
4192           set_column($first_col, $last_col, $width, $format, $hidden, $level, $collapsed)
4193
4194       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
4198           $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
4202       Excel allows up to 7 outline levels. Therefore the $level parameter
4203       should be in the range "0 <= $level <= 7".
4204
4205       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
4209           $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
4213           $worksheet->set_column('B:G', undef, undef, 1, 1);
4214           $worksheet->set_column('H:H', undef, undef, 0, 0, 1); # Collapsed flag.
4215
4216       Note: Setting the $collapsed flag is particularly important for
4217       compatibility with OpenOffice.org and Gnumeric.
4218
4219       For a more complete example see the "outline.pl" and
4220       "outline_collapsed.pl" programs in the examples directory of the
4221       distro.
4222
4223       Some additional outline properties can be set via the
4224       "outline_settings()" worksheet method, see above.
4225

DATA VALIDATION IN EXCEL

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
4232       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
4238           $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
4249       The above example would look like this in Excel:
4250       <http://homepage.eircom.net/~jmcnamara/perl/data_validation.jpg>.
4251
4252       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
4256       The following sections describe how to use the "data_validation()"
4257       method and its various options.
4258
4259   data_validation($row, $col, { parameter => 'value', ... })
4260       The "data_validation()" method is used to construct an Excel data
4261       validation.
4262
4263       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
4268           $worksheet->data_validation(0, 0,       {...});
4269           $worksheet->data_validation(0, 0, 4, 1, {...});
4270
4271           # Which are the same as:
4272
4273           $worksheet->data_validation('A1',       {...});
4274           $worksheet->data_validation('A1:B5',    {...});
4275
4276       See also the note about "Cell notation" for more information.
4277
4278       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
4282           validate
4283           criteria
4284           value | minimum | source
4285           maximum
4286           ignore_blank
4287           dropdown
4288
4289           input_title
4290           input_message
4291           show_input
4292
4293           error_title
4294           error_message
4295           error_type
4296           show_error
4297
4298       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
4302           $worksheet->data_validation('B3',
4303               {
4304                   validate => 'integer',
4305                   criteria => '>',
4306                   value    => 100,
4307               });
4308
4309       The "data_validation" method returns:
4310
4311            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
4316   validate
4317       This parameter is passed in a hash ref to "data_validation()".
4318
4319       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
4323           any
4324           integer
4325           decimal
4326           list
4327           date
4328           time
4329           length
4330           custom
4331
4332       ·   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
4337       ·   integer restricts the cell to integer values. Excel refers to this
4338           as 'whole number'.
4339
4340               validate => 'integer',
4341               criteria => '>',
4342               value    => 100,
4343
4344       ·   decimal restricts the cell to decimal values.
4345
4346               validate => 'decimal',
4347               criteria => '>',
4348               value    => 38.6,
4349
4350       ·   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
4354               validate => 'list',
4355               value    => ['open', 'high', 'close'],
4356               # Or like this:
4357               value    => 'B1:B3',
4358
4359           Excel requires that range references are only to cells on the same
4360           worksheet.
4361
4362       ·   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
4367               validate => 'date',
4368               criteria => '>',
4369               value    => 39653, # 24 July 2008
4370               # Or like this:
4371               value    => '2008-07-24T',
4372
4373       ·   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
4378               validate => 'time',
4379               criteria => '>',
4380               value    => 0.5, # Noon
4381               # Or like this:
4382               value    => 'T12:00:00',
4383
4384       ·   length restricts the cell data based on an integer string length.
4385           Excel refers to this as 'Text length'.
4386
4387               validate => 'length',
4388               criteria => '>',
4389               value    => 10,
4390
4391       ·   custom restricts the cell based on an external Excel formula that
4392           returns a "TRUE/FALSE" value.
4393
4394               validate => 'custom',
4395               value    => '=IF(A10>B10,TRUE,FALSE)',
4396
4397   criteria
4398       This parameter is passed in a hash ref to "data_validation()".
4399
4400       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
4405           '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
4414       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
4418           validate => 'integer',
4419           criteria => 'greater than',
4420           value    => 100,
4421
4422           validate => 'integer',
4423           criteria => '>',
4424           value    => 100,
4425
4426       The "list" and "custom" validate options don't require a "criteria". If
4427       you specify one it will be ignored.
4428
4429           validate => 'list',
4430           value    => ['open', 'high', 'close'],
4431
4432           validate => 'custom',
4433           value    => '=IF(A10>B10,TRUE,FALSE)',
4434
4435   value | minimum | source
4436       This parameter is passed in a hash ref to "data_validation()".
4437
4438       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
4444           # Use 'value'
4445           validate => 'integer',
4446           criteria => '>',
4447           value    => 100,
4448
4449           # Use 'minimum'
4450           validate => 'integer',
4451           criteria => 'between',
4452           minimum  => 1,
4453           maximum  => 100,
4454
4455           # Use 'source'
4456           validate => 'list',
4457           source   => '$B$1:$B$3',
4458
4459   maximum
4460       This parameter is passed in a hash ref to "data_validation()".
4461
4462       The "maximum" parameter is used to set the upper limiting value when
4463       the "criteria" is either 'between' or 'not between':
4464
4465           validate => 'integer',
4466           criteria => 'between',
4467           minimum  => 1,
4468           maximum  => 100,
4469
4470   ignore_blank
4471       This parameter is passed in a hash ref to "data_validation()".
4472
4473       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
4478           ignore_blank => 0,  # Turn the option off
4479
4480   dropdown
4481       This parameter is passed in a hash ref to "data_validation()".
4482
4483       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
4488           dropdown => 0,      # Turn the option off
4489
4490   input_title
4491       This parameter is passed in a hash ref to "data_validation()".
4492
4493       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
4498           input_title   => 'This is the input title',
4499
4500       The maximum title length is 32 characters. UTF8 strings are handled
4501       automatically in perl 5.8 and later.
4502
4503   input_message
4504       This parameter is passed in a hash ref to "data_validation()".
4505
4506       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
4509           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
4516       The message can be split over several lines using newlines, "\n" in
4517       double quoted strings.
4518
4519           input_message => "This is\na test.",
4520
4521       The maximum message length is 255 characters. UTF8 strings are handled
4522       automatically in perl 5.8 and later.
4523
4524   show_input
4525       This parameter is passed in a hash ref to "data_validation()".
4526
4527       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
4532           show_input => 0,      # Turn the option off
4533
4534   error_title
4535       This parameter is passed in a hash ref to "data_validation()".
4536
4537       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
4541           error_title   => 'Input value is not valid',
4542
4543       The maximum title length is 32 characters. UTF8 strings are handled
4544       automatically in perl 5.8 and later.
4545
4546   error_message
4547       This parameter is passed in a hash ref to "data_validation()".
4548
4549       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
4554           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
4561       The message can be split over several lines using newlines, "\n" in
4562       double quoted strings.
4563
4564           input_message => "This is\na test.",
4565
4566       The maximum message length is 255 characters. UTF8 strings are handled
4567       automatically in perl 5.8 and later.
4568
4569   error_type
4570       This parameter is passed in a hash ref to "data_validation()".
4571
4572       The "error_type" parameter is used to specify the type of error dialog
4573       that is displayed. There are 3 options:
4574
4575           'stop'
4576           'warning'
4577           'information'
4578
4579       The default is 'stop'.
4580
4581   show_error
4582       This parameter is passed in a hash ref to "data_validation()".
4583
4584       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
4590           show_error => 0,      # Turn the option off
4591
4592   Data Validation Examples
4593       Example 1. Limiting input to an integer greater than a fixed value.
4594
4595           $worksheet->data_validation('A1',
4596               {
4597                   validate        => 'integer',
4598                   criteria        => '>',
4599                   value           => 0,
4600               });
4601
4602       Example 2. Limiting input to an integer greater than a fixed value
4603       where the value is referenced from a cell.
4604
4605           $worksheet->data_validation('A2',
4606               {
4607                   validate        => 'integer',
4608                   criteria        => '>',
4609                   value           => '=E3',
4610               });
4611
4612       Example 3. Limiting input to a decimal in a fixed range.
4613
4614           $worksheet->data_validation('A3',
4615               {
4616                   validate        => 'decimal',
4617                   criteria        => 'between',
4618                   minimum         => 0.1,
4619                   maximum         => 0.5,
4620               });
4621
4622       Example 4. Limiting input to a value in a dropdown list.
4623
4624           $worksheet->data_validation('A4',
4625               {
4626                   validate        => 'list',
4627                   source          => ['open', 'high', 'close'],
4628               });
4629
4630       Example 5. Limiting input to a value in a dropdown list where the list
4631       is specified as a cell range.
4632
4633           $worksheet->data_validation('A5',
4634               {
4635                   validate        => 'list',
4636                   source          => '=E4:G4',
4637               });
4638
4639       Example 6. Limiting input to a date in a fixed range.
4640
4641           $worksheet->data_validation('A6',
4642               {
4643                   validate        => 'date',
4644                   criteria        => 'between',
4645                   minimum         => '2008-01-01T',
4646                   maximum         => '2008-12-12T',
4647               });
4648
4649       Example 7. Displaying a message when the cell is selected.
4650
4651           $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
4661       See also the "data_validate.pl" program in the examples directory of
4662       the distro.
4663

ROW HEIGHTS AND WORKSHEET OBJECTS

4665       The following relates to worksheet objects such as images, comments and
4666       charts.
4667
4668       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
4673       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
4680       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

FORMULAS AND FUNCTIONS IN EXCEL

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
4689           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
4696       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
4701   Introduction
4702       The following is a brief introduction to formulas and functions in
4703       Excel and Spreadsheet::WriteExcel.
4704
4705       A formula is a string that begins with an equals sign:
4706
4707           '=A1+B1'
4708           '=AVERAGE(1, 2, 3)'
4709
4710       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
4715       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
4722           use Spreadsheet::WriteExcel::Utility;
4723
4724           ($row, $col) = xl_cell_to_rowcol('C2');  # (1, 2)
4725           $str         = xl_rowcol_to_cell(1, 2);  # C2
4726
4727       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
4732           '=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
4737       Formulas can also refer to cells in other worksheets of the current
4738       workbook. For example:
4739
4740           '=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
4747       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
4757       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
4761           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
4772
4773           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
4783
4784           String operator:
4785           ================
4786           Operator  Meaning                   Example
4787               &     Concatenation             "Hello " & "World!" # [2]
4788
4789
4790           Reference operators:
4791           ====================
4792           Operator  Meaning                   Example
4793               :     Range operator            A1:A4               # [3]
4794               ,     Union operator            SUM(1, 2+2, B3)     # [4]
4795
4796
4797           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
4803       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
4807           $worksheet->write('A1', '=SUM(1; 2; 3)'); # Wrong!!
4808           $worksheet->write('A1', '=SUM(1, 2, 3)'); # Okay
4809
4810       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
4816           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
4862       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
4867       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
4872       If your formula doesn't work in Spreadsheet::WriteExcel try the
4873       following:
4874
4875           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
4882       If you go through steps 1-6 and you still have a problem, mail me.
4883
4884   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
4889       However, in a lot of cases the formulas that you write will be quite
4890       similar, for example:
4891
4892           $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
4899       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
4905       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
4909       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
4913           my $formula = $worksheet->store_formula('=A1 * 3 + 50');
4914
4915           for my $row (0..999) {
4916               $worksheet->repeat_formula($row, 1, $formula, $format, 'A1', 'A'.($row +1));
4917           }
4918
4919       On an arbitrary test machine this method was 10 times faster than the
4920       brute force method shown above.
4921
4922       For more information about how Spreadsheet::WriteExcel parses and
4923       stores formulas see the "Spreadsheet::WriteExcel::Formula" man page.
4924
4925       It should be noted however that the overall speed of direct formula
4926       parsing will be improved in a future version.
4927

EXAMPLES

4929       See Spreadsheet::WriteExcel::Examples for a full list of examples.
4930
4931   Example 1
4932       The following example shows some of the basic features of
4933       Spreadsheet::WriteExcel.
4934
4935           #!/usr/bin/perl -w
4936
4937           use strict;
4938           use Spreadsheet::WriteExcel;
4939
4940           # 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
4944           # The general syntax is write($row, $column, $token). Note that row and
4945           # column are zero indexed
4946
4947           # Write some text
4948           $worksheet->write(0, 0,  'Hi Excel!');
4949
4950
4951           # 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
4957
4958           # Write some formulas
4959           $worksheet->write(7, 0,  '=A3 + A6');
4960           $worksheet->write(8, 0,  '=IF(A5>3,"Yes", "No")');
4961
4962
4963           # Write a hyperlink
4964           $worksheet->write(10, 0, 'http://www.perl.com/');
4965
4966   Example 2
4967       The following is a general example which demonstrates some features of
4968       working with multiple worksheets.
4969
4970           #!/usr/bin/perl -w
4971
4972           use strict;
4973           use Spreadsheet::WriteExcel;
4974
4975           # Create a new Excel workbook
4976           my $workbook = Spreadsheet::WriteExcel->new('regions.xls');
4977
4978           # 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
4984           # Add a Format
4985           my $format = $workbook->add_format();
4986           $format->set_bold();
4987           $format->set_color('blue');
4988
4989           # Add a caption to each worksheet
4990           foreach my $worksheet ($workbook->sheets()) {
4991               $worksheet->write(0, 0, 'Sales', $format);
4992           }
4993
4994           # 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
5000           # Set the active worksheet
5001           $south->activate();
5002
5003           # Set the width of the first column
5004           $south->set_column(0, 0, 20);
5005
5006           # Set the active cell
5007           $south->set_selection(0, 1);
5008
5009   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
5013           use strict;
5014           use Spreadsheet::WriteExcel;
5015
5016           # Create a new workbook and add a worksheet
5017           my $workbook  = Spreadsheet::WriteExcel->new('stocks.xls');
5018           my $worksheet = $workbook->add_worksheet();
5019
5020           # Set the column width for columns 1, 2, 3 and 4
5021           $worksheet->set_column(0, 3, 15);
5022
5023
5024           # 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
5030
5031           # 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
5036
5037           # 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
5042
5043           # 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
5054
5055           # 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
5061           $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
5066           $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
5071           $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
5076   Example 4
5077       The following is a simple example of using functions.
5078
5079           #!/usr/bin/perl -w
5080
5081           use strict;
5082           use Spreadsheet::WriteExcel;
5083
5084           # 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
5088           # Set the column width for columns 1
5089           $worksheet->set_column(0, 0, 20);
5090
5091
5092           # Create a format for the headings
5093           my $format = $workbook->add_format();
5094           $format->set_bold();
5095
5096
5097           # 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
5108           $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
5118           # Write some statistical functions
5119           $worksheet->write(4,  0, 'Count', $format);
5120           $worksheet->write(4,  1, '=COUNT(B1:I1)');
5121
5122           $worksheet->write(5,  0, 'Sum', $format);
5123           $worksheet->write(5,  1, '=SUM(B2:I2)');
5124
5125           $worksheet->write(6,  0, 'Average', $format);
5126           $worksheet->write(6,  1, '=AVERAGE(B2:I2)');
5127
5128           $worksheet->write(7,  0, 'Min', $format);
5129           $worksheet->write(7,  1, '=MIN(B2:I2)');
5130
5131           $worksheet->write(8,  0, 'Max', $format);
5132           $worksheet->write(8,  1, '=MAX(B2:I2)');
5133
5134           $worksheet->write(9,  0, 'Standard Deviation', $format);
5135           $worksheet->write(9,  1, '=STDEV(B2:I2)');
5136
5137           $worksheet->write(10, 0, 'Kurtosis', $format);
5138           $worksheet->write(10, 1, '=KURT(B2:I2)');
5139
5140   Example 5
5141       The following example converts a tab separated file called "tab.txt"
5142       into an Excel file called "tab.xls".
5143
5144           #!/usr/bin/perl -w
5145
5146           use strict;
5147           use Spreadsheet::WriteExcel;
5148
5149           open (TABFILE, 'tab.txt') or die "tab.txt: $!";
5150
5151           my $workbook  = Spreadsheet::WriteExcel->new('tab.xls');
5152           my $worksheet = $workbook->add_worksheet();
5153
5154           # Row and column are zero indexed
5155           my $row = 0;
5156
5157           while (<TABFILE>) {
5158               chomp;
5159               # Split on single tab
5160               my @Fld = split('\t', $_);
5161
5162               my $col = 0;
5163               foreach my $token (@Fld) {
5164                   $worksheet->write($row, $col, $token);
5165                   $col++;
5166               }
5167               $row++;
5168           }
5169
5170       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
5175       See the examples/csv2xls link here:
5176       <http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>.
5177
5178   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
5184           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
5193
5194           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
5255
5256           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
5272
5273           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
5281
5282           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

LIMITATIONS

5289       The following limits are imposed by Excel:
5290
5291           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
5299       For Excel 2007+ file limits see the Excel::Writer::XLSX module.
5300
5301       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

DOWNLOADING

5308       The latest version of this module is always available at:
5309       <http://search.cpan.org/search?dist=Spreadsheet-WriteExcel/>.
5310

REQUIREMENTS

5312       This module requires Perl >= 5.005, Parse::RecDescent, File::Temp and
5313       OLE::Storage_Lite:
5314
5315           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
5319       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

INSTALLATION

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

PORTABILITY

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
5336           print join(' ', map { sprintf '%#02x', $_ } unpack('C*', pack 'd', 1.2345)), "\n";
5337
5338       should give (or in reverse order):
5339
5340           0x8d 0x97 0x6e 0x12 0x83 0xc0 0xf3 0x3f
5341
5342       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

DIAGNOSTICS

5350       Filename required by Spreadsheet::WriteExcel->new()
5351           A filename must be given in the constructor.
5352
5353       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
5358       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
5366       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
5371       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
5376       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
5382       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
5386       '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
5392           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

THE EXCEL BINARY FORMAT

5399       The following is some general information about the Excel binary format
5400       for anyone who may be interested.
5401
5402       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
5407       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
5411       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
5415       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
5420       The OLE format is explained in the "Windows Compound Binary File Format
5421       Specification"
5422       <http://www.microsoft.com/interop/docs/supportingtechnologies.mspx>
5423
5424       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
5428       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

WRITING EXCEL FILES

5433       Depending on your requirements, background and general sensibilities
5434       you may prefer one of the following methods of getting data into Excel:
5435
5436       ·   Win32::OLE module and office automation
5437
5438           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
5450       ·   CSV, comma separated variables or text
5451
5452           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
5457       ·   DBI with DBD::ADO or DBD::ODBC
5458
5459           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
5463       ·   DBD::Excel
5464
5465           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
5469       ·   Spreadsheet::WriteExcelXML
5470
5471           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
5475       ·   Excel::Template
5476
5477           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
5481       ·   Spreadsheet::WriteExcel::FromXML
5482
5483           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
5488       ·   Spreadsheet::WriteExcel::Simple
5489
5490           This provides an easier interface to Spreadsheet::WriteExcel:
5491           <http://search.cpan.org/dist/Spreadsheet-WriteExcel-Simple>.
5492
5493       ·   Spreadsheet::WriteExcel::FromDB
5494
5495           This is a useful module for creating Excel files directly from a DB
5496           table: <http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB>.
5497
5498       ·   HTML tables
5499
5500           This is an easy way of adding formatting via a text based format.
5501
5502       ·   XML or HTML
5503
5504           The Excel XML and HTML file specification are available from
5505           <http://msdn.microsoft.com/library/officedev/ofxml2k/ofxml2k.htm>.
5506
5507       For other Perl-Excel modules try the following search:
5508       <http://search.cpan.org/search?mode=module&query=excel>.
5509

READING EXCEL FILES

5511       To read data from Excel files try:
5512
5513       ·   Spreadsheet::ParseExcel
5514
5515           This uses the OLE::Storage-Lite module to extract data from an
5516           Excel file. <http://search.cpan.org/dist/Spreadsheet-ParseExcel>.
5517
5518       ·   Spreadsheet::ParseExcel_XLHTML
5519
5520           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
5525       ·   xlHtml
5526
5527           This is an open source "Excel to HTML Converter" C/C++ project at
5528           <http://chicago.sourceforge.net/xlhtml/>.
5529
5530       ·   DBD::Excel (reading)
5531
5532           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
5536       ·   Win32::OLE module and office automation (reading)
5537
5538           See, the section "WRITING EXCEL FILES".
5539
5540       ·   HTML tables (reading)
5541
5542           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
5546       ·   DBI with DBD::ADO or DBD::ODBC.
5547
5548           See, the section "WRITING EXCEL FILES".
5549
5550       ·   XML::Excel
5551
5552           Converts Excel files to XML using Spreadsheet::ParseExcel
5553           <http://search.cpan.org/dist/XML-Excel>.
5554
5555       ·   OLE::Storage, aka LAOLA
5556
5557           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
5562       For other Perl-Excel modules try the following search:
5563       <http://search.cpan.org/search?mode=module&query=excel>.
5564
5565       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
5570       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

MODIFYING AND REWRITING EXCEL FILES

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
5579       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
5583       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
5589       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
5594       Here is an example:
5595
5596           #!/usr/bin/perl -w
5597
5598           use strict;
5599           use Spreadsheet::ParseExcel;
5600           use Spreadsheet::ParseExcel::SaveParser;
5601
5602           # Open the template with SaveParser
5603           my $parser   = new Spreadsheet::ParseExcel::SaveParser;
5604           my $template = $parser->Parse('template.xls');
5605
5606           my $sheet    = 0;
5607           my $row      = 0;
5608           my $col      = 0;
5609
5610           # Get the format from the cell
5611           my $format   = $template->{Worksheet}[$sheet]
5612                                   ->{Cells}[$row][$col]
5613                                   ->{FormatNo};
5614
5615           # Write data to some cells
5616           $template->AddCell(0, $row,   $col,   1,     $format);
5617           $template->AddCell(0, $row+1, $col, "Hello", $format);
5618
5619           # Add a new worksheet
5620           $template->AddWorksheet('New Data');
5621
5622           # 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
5630           {
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
5635               # Rewrite the file or save as a new file
5636               $workbook = $template->SaveAs('new.xls');
5637           }
5638
5639           # Use Spreadsheet::WriteExcel methods
5640           my $worksheet  = $workbook->sheets(0);
5641
5642           $worksheet->write($row+2, $col, "World2");
5643
5644           $workbook->close();
5645

Warning about XML::Parser and perl 5.6

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
5651       When "UTF-8" strings are added to Spreadsheet::WriteExcel's internal
5652       data it causes the generated Excel file to become corrupt.
5653
5654       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
5657       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
5661           $new_str = pack 'C*', unpack 'U*', $utf8_str;
5662
5663
5664           use Unicode::MapUTF8 'from_utf8';
5665           $new_str = from_utf8({-str => $utf8_str, -charset => 'ISO-8859-1'});
5666

Warning about Office Service Pack 3

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
5672           "File Error: data may have been lost".
5673
5674       This is usually caused by multiple instances of data in a cell.
5675
5676       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
5681       For a longer discussion and some workarounds see the following:
5682       <http://groups.google.com/group/spreadsheet-writeexcel/browse_thread/thread/3dcea40e6620af3a>.
5683

BUGS

5685       Formulas are formulae.
5686
5687       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
5691       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
5697       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
5701       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
5706       OpenOffice.org: No known issues in this release.
5707
5708       Gnumeric: No known issues in this release.
5709
5710       If you wish to submit a bug report run the "bug_report.pl" program in
5711       the "examples" directory of the distro.
5712

Migrating to Excel::Writer::XLSX

5714       Spreadsheet::WriteExcel is in maintenance only mode and has effectively
5715       been superseded by Excel::Writer::XLSX.
5716
5717       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
5722       To convert your Spreadsheet::WriteExcel program to Excel::Writer::XLSX
5723       you only need do the following:
5724
5725       ·   Substitute Excel::Writer::XLSX for Spreadsheet::WriteExcel in your
5726           program.
5727
5728       ·   Change the file extension of the output file from ".xls" to
5729           ".xlsx".
5730
5731       ·   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
5736       There are some differences between the formats and the modules that are
5737       worth noting:
5738
5739       ·   The default font in the XLSX format is Calibri 11 not Arial 10.
5740
5741       ·   Default column widths and row heights are different between XLS and
5742           XLSX.
5743
5744       ·   The Excel::Writer::XLSX module uses more memory by default but has
5745           a optimisation mode to reduce usage for large files.
5746
5747       ·   The XLSX format doesn't have reading support that is as complete as
5748           Spreadsheet::ParseExcel.
5749

REPOSITORY

5751       The Spreadsheet::WriteExcel source code in host on github:
5752       <http://github.com/jmcnamara/spreadsheet-writeexcel>.
5753

MAILING LIST

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
5760       Alternatively you can keep up to date with future releases by
5761       subscribing at: <http://freshmeat.net/projects/writeexcel/>.
5762

DONATIONS

5764       If you'd care to donate to the Spreadsheet::WriteExcel project, you can
5765       do so via PayPal: <http://tinyurl.com/7ayes>.
5766

SEE ALSO

5768       Spreadsheet::ParseExcel:
5769       <http://search.cpan.org/dist/Spreadsheet-ParseExcel>.
5770
5771       Spreadsheet-WriteExcel-FromXML:
5772       <http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromXML>.
5773
5774       Spreadsheet::WriteExcel::FromDB:
5775       <http://search.cpan.org/dist/Spreadsheet-WriteExcel-FromDB>.
5776
5777       Excel::Template: <http://search.cpan.org/~rkinyon/Excel-Template/>.
5778
5779       DateTime::Format::Excel:
5780       <http://search.cpan.org/dist/DateTime-Format-Excel>.
5781
5782       "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
5786       "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
5790       Spreadsheet::WriteExcel documentation in Japanese by Takanori Kawai.
5791       <http://member.nifty.ne.jp/hippo2000/perltips/Spreadsheet/WriteExcel.htm>.
5792
5793       Oesterly user brushes with fame:
5794       <http://oesterly.com/releases/12102000.html>.
5795
5796       The csv2xls program that is part of Text::CSV_XS:
5797       <http://search.cpan.org/~hmbrand/Text-CSV_XS/MANIFEST>.
5798

ACKNOWLEDGMENTS

5800       The following people contributed to the debugging and testing of
5801       Spreadsheet::WriteExcel:
5802
5803       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
5817       The following people contributed patches, examples or Excel
5818       information:
5819
5820       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
5826       Many thanks to Ron McKelvey, Ronzo Consulting for Siemens, who
5827       sponsored the development of the formula caching routines.
5828
5829       Many thanks to Cassens Transport who sponsored the development of the
5830       embedded charts and autofilters.
5831
5832       Additional thanks to Takanori Kawai for translating the documentation
5833       into Japanese.
5834
5835       Gunnar Wolf maintains the Debian distro.
5836
5837       Thanks to Damian Conway for the excellent Parse::RecDescent.
5838
5839       Thanks to Tim Jenness for File::Temp.
5840
5841       Thanks to Michael Meeks and Jody Goldberg for their work on Gnumeric.
5842

DISCLAIMER OF WARRANTY

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
5854       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

LICENSE

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

AUTHOR

5871       John McNamara jmcnamara@cpan.org
5872
5873           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
5882           You've changed.
5883             -- Jeff Tweedy
5884
5886       Copyright MM-MMXII, John McNamara.
5887
5888       All Rights Reserved. This module is free software. It may be used,
5889       redistributed and/or modified under the same terms as Perl itself.
5890
5891
5892
5893perl v5.30.0                      2019-07-26        Spreadsheet::WriteExcel(3)
Impressum