{ "mode": "perldoc", "parameter": "Spreadsheet::WriteExcel::Examples", "section": "", "url": "https://www.chedong.com/phpMan.php/perldoc/Spreadsheet%3A%3AWriteExcel%3A%3AExamples/json", "generated": "2026-07-05T16:13:00Z", "sections": { "NAME": { "content": "Examples - Spreadsheet::WriteExcel example programs.\n", "subsections": [] }, "DESCRIPTION": { "content": "This is a documentation only module showing the examples that are included in the\nSpreadsheet::WriteExcel distribution.\n\nThis file was auto-generated via the genexamplespod.pl program that is also included in the\nexamples directory.\n", "subsections": [] }, "Example programs": { "content": "The following is a list of the 85 example programs that are included in the\nSpreadsheet::WriteExcel distribution.\n\n* \"Example: asimple.pl\" A get started example with some basic features.\n\n* \"Example: demo.pl\" A demo of some of the available features.\n\n* \"Example: regions.pl\" A simple example of multiple worksheets.\n\n* \"Example: stats.pl\" Basic formulas and functions.\n\n* \"Example: formats.pl\" All the available formatting on several worksheets.\n\n* \"Example: bugreport.pl\" A template for submitting bug reports.\n\n* \"Example: autofilter.pl\" Examples of worksheet autofilters.\n\n* \"Example: autofit.pl\" Simulate Excel's autofit for column widths.\n\n* \"Example: bigfile.pl\" Write past the 7MB limit with OLE::StorageLite.\n\n* \"Example: cgi.pl\" A simple CGI program.\n\n* \"Example: chartarea.pl\" A demo of area style charts.\n\n* \"Example: chartbar.pl\" A demo of bar (vertical histogram) style charts.\n\n* \"Example: chartcolumn.pl\" A demo of column (histogram) style charts.\n\n* \"Example: chartline.pl\" A demo of line style charts.\n\n* \"Example: chartpie.pl\" A demo of pie style charts.\n\n* \"Example: chartscatter.pl\" A demo of scatter style charts.\n\n* \"Example: chartstock.pl\" A demo of stock style charts.\n\n* \"Example: chess.pl\" An example of reusing formatting via properties.\n\n* \"Example: colors.pl\" A demo of the colour palette and named colours.\n\n* \"Example: comments1.pl\" Add comments to worksheet cells.\n\n* \"Example: comments2.pl\" Add comments with advanced options.\n\n* \"Example: copyformat.pl\" Example of copying a cell format.\n\n* \"Example: datavalidate.pl\" An example of data validation and dropdown lists.\n\n* \"Example: datetime.pl\" Write dates and times with writedatetime().\n\n* \"Example: definedname.pl\" Example of how to create defined names.\n\n* \"Example: diagborder.pl\" A simple example of diagonal cell borders.\n\n* \"Example: easteregg.pl\" Expose the Excel97 flight simulator.\n\n* \"Example: filehandle.pl\" Examples of working with filehandles.\n\n* \"Example: formularesult.pl\" Formulas with user specified results.\n\n* \"Example: headers.pl\" Examples of worksheet headers and footers.\n\n* \"Example: hidesheet.pl\" Simple example of hiding a worksheet.\n\n* \"Example: hyperlink1.pl\" Shows how to create web hyperlinks.\n\n* \"Example: hyperlink2.pl\" Examples of internal and external hyperlinks.\n\n* \"Example: images.pl\" Adding images to worksheets.\n\n* \"Example: indent.pl\" An example of cell indentation.\n\n* \"Example: merge1.pl\" A simple example of cell merging.\n\n* \"Example: merge2.pl\" A simple example of cell merging with formatting.\n\n* \"Example: merge3.pl\" Add hyperlinks to merged cells.\n\n* \"Example: merge4.pl\" An advanced example of merging with formatting.\n\n* \"Example: merge5.pl\" An advanced example of merging with formatting.\n\n* \"Example: merge6.pl\" An example of merging with Unicode strings.\n\n* \"Example: modperl1.pl\" A simple modperl 1 program.\n\n* \"Example: modperl2.pl\" A simple modperl 2 program.\n\n* \"Example: outline.pl\" An example of outlines and grouping.\n\n* \"Example: outlinecollapsed.pl\" An example of collapsed outlines.\n\n* \"Example: panes.pl\" An examples of how to create panes.\n\n* \"Example: properties.pl\" Add document properties to a workbook.\n\n* \"Example: protection.pl\" Example of cell locking and formula hiding.\n\n* \"Example: repeat.pl\" Example of writing repeated formulas.\n\n* \"Example: righttoleft.pl\" Change default sheet direction to right to left.\n\n* \"Example: rowwrap.pl\" How to wrap data from one worksheet onto another.\n\n* \"Example: sales.pl\" An example of a simple sales spreadsheet.\n\n* \"Example: sendmail.pl\" Send an Excel email attachment using Mail::Sender.\n\n* \"Example: statsext.pl\" Same as stats.pl with external references.\n\n* \"Example: stocks.pl\" Demonstrates conditional formatting.\n\n* \"Example: tabcolors.pl\" Example of how to set worksheet tab colours.\n\n* \"Example: textwrap.pl\" Demonstrates text wrapping options.\n\n* \"Example: win32ole.pl\" A sample Win32::OLE example for comparison.\n\n* \"Example: writearrays.pl\" Example of writing 1D or 2D arrays of data.\n\n* \"Example: writehandler1.pl\" Example of extending the write() method. Step 1.\n\n* \"Example: writehandler2.pl\" Example of extending the write() method. Step 2.\n\n* \"Example: writehandler3.pl\" Example of extending the write() method. Step 3.\n\n* \"Example: writehandler4.pl\" Example of extending the write() method. Step 4.\n\n* \"Example: writetoscalar.pl\" Example of writing an Excel file to a Perl scalar.\n\n* \"Example: unicodeutf16.pl\" Simple example of using Unicode UTF16 strings.\n\n* \"Example: unicodeutf16japan.pl\" Write Japanese Unicode strings using UTF-16.\n\n* \"Example: unicodecyrillic.pl\" Write Russian Cyrillic strings using UTF-8.\n\n* \"Example: unicodelist.pl\" List the chars in a Unicode font.\n\n* \"Example: unicode2022jp.pl\" Japanese: ISO-2022-JP to utf8 in perl 5.8.\n\n* \"Example: unicode885911.pl\" Thai: ISO-885911 to utf8 in perl 5.8.\n\n* \"Example: unicode88597.pl\" Greek: ISO-88597 to utf8 in perl 5.8.\n\n* \"Example: unicodebig5.pl\" Chinese: BIG5 to utf8 in perl 5.8.\n\n* \"Example: unicodecp1251.pl\" Russian: CP1251 to utf8 in perl 5.8.\n\n* \"Example: unicodecp1256.pl\" Arabic: CP1256 to utf8 in perl 5.8.\n\n* \"Example: unicodekoi8r.pl\" Russian: KOI8-R to utf8 in perl 5.8.\n\n* \"Example: unicodepolishutf8.pl\" Polish : UTF8 to utf8 in perl 5.8.\n\n* \"Example: unicodeshiftjis.pl\" Japanese: Shift JIS to utf8 in perl 5.8.\n\n* \"Example: csv2xls.pl\" Program to convert a CSV file to an Excel file.\n\n* \"Example: tab2xls.pl\" Program to convert a tab separated file to xls.\n\n* \"Example: datecalc1.pl\" Convert Unix/Perl time to Excel time.\n\n* \"Example: datecalc2.pl\" Calculate an Excel date using Date::Calc.\n\n* \"Example: lecxe.pl\" Convert Excel to WriteExcel using Win32::OLE.\n\n* \"Example: convertA1.pl\" Helper functions for dealing with A1 notation.\n\n* \"Example: functionlocale.pl\" Add non-English function names to Formula.pm.\n\n* \"Example: writeA1.pl\" Example of how to extend the module.\n", "subsections": [ { "name": "Example: asimple.pl", "content": "A simple example of how to use the Spreadsheet::WriteExcel module to write some text and numbers\nto an Excel binary file.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple example of how to use the Spreadsheet::WriteExcel module to write\n# some text and numbers to an Excel binary file.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook called simple.xls and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new('asimple.xls');\nmy $worksheet = $workbook->addworksheet();\n\n# The general syntax is write($row, $column, $token). Note that row and\n# column are zero indexed\n#\n\n# Write some text\n$worksheet->write(0, 0, \"Hi Excel!\");\n\n\n# Write some numbers\n$worksheet->write(2, 0, 3); # Writes 3\n$worksheet->write(3, 0, 3.00000); # Writes 3\n$worksheet->write(4, 0, 3.00001); # Writes 3.00001\n$worksheet->write(5, 0, 3.14159); # TeX revision no.?\n\n\n# Write some formulas\n$worksheet->write(7, 0, '=A3 + A6');\n$worksheet->write(8, 0, '=IF(A5>3,\"Yes\", \"No\")');\n\n\n# Write a hyperlink\n$worksheet->write(10, 0, 'http://www.perl.com/');\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: demo.pl", "content": "A simple demo of some of the features of Spreadsheet::WriteExcel.\n\nThis program is used to create the project screenshot for Freshmeat:\n\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# A simple demo of some of the features of Spreadsheet::WriteExcel.\n#\n# This program is used to create the project screenshot for Freshmeat:\n# L\n#\n# reverse('(c)'), October 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"demo.xls\");\nmy $worksheet = $workbook->addworksheet('Demo');\nmy $worksheet2 = $workbook->addworksheet('Another sheet');\nmy $worksheet3 = $workbook->addworksheet('And another');\n\nmy $bold = $workbook->addformat(bold => 1);\n\n\n#######################################################################\n#\n# Write a general heading\n#\n$worksheet->setcolumn('A:A', 36, $bold);\n$worksheet->setcolumn('B:B', 20 );\n$worksheet->setrow (0, 40 );\n\nmy $heading = $workbook->addformat(\nbold => 1,\ncolor => 'blue',\nsize => 16,\nmerge => 1,\nalign => 'vcenter',\n);\n\nmy @headings = ('Features of Spreadsheet::WriteExcel', '');\n$worksheet->writerow('A1', \\@headings, $heading);\n\n\n#######################################################################\n#\n# Some text examples\n#\nmy $textformat = $workbook->addformat(\nbold => 1,\nitalic => 1,\ncolor => 'red',\nsize => 18,\nfont =>'Lucida Calligraphy'\n);\n\n# A phrase in Cyrillic\nmy $unicode = pack \"H*\", \"042d0442043e002004440440043004370430002004\".\n\"3d043000200440044304410441043a043e043c0021\";\n\n\n$worksheet->write('A2', \"Text\");\n$worksheet->write('B2', \"Hello Excel\");\n$worksheet->write('A3', \"Formatted text\");\n$worksheet->write('B3', \"Hello Excel\", $textformat);\n$worksheet->write('A4', \"Unicode text\");\n$worksheet->writeutf16bestring('B4', $unicode);\n\n#######################################################################\n#\n# Some numeric examples\n#\nmy $num1format = $workbook->addformat(numformat => '$#,##0.00');\nmy $num2format = $workbook->addformat(numformat => ' d mmmm yyy');\n\n\n$worksheet->write('A5', \"Numbers\");\n$worksheet->write('B5', 1234.56);\n$worksheet->write('A6', \"Formatted numbers\");\n$worksheet->write('B6', 1234.56, $num1format);\n$worksheet->write('A7', \"Formatted numbers\");\n$worksheet->write('B7', 37257, $num2format);\n\n\n#######################################################################\n#\n# Formulae\n#\n$worksheet->setselection('B8');\n$worksheet->write('A8', 'Formulas and functions, \"=SIN(PI()/4)\"');\n$worksheet->write('B8', '=SIN(PI()/4)');\n\n\n#######################################################################\n#\n# Hyperlinks\n#\n$worksheet->write('A9', \"Hyperlinks\");\n$worksheet->write('B9', 'http://www.perl.com/' );\n\n\n#######################################################################\n#\n# Images\n#\n$worksheet->write('A10', \"Images\");\n$worksheet->insertimage('B10', 'republic.png', 16, 8);\n\n\n#######################################################################\n#\n# Misc\n#\n$worksheet->write('A18', \"Page/printer setup\");\n$worksheet->write('A19', \"Multiple worksheets\");\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: regions.pl", "content": "An example of how to use the Spreadsheet:WriteExcel module to write a basic Excel workbook with\nmultiple worksheets.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# An example of how to use the Spreadsheet:WriteExcel module to write a basic\n# Excel workbook with multiple worksheets.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new Excel workbook\nmy $workbook = Spreadsheet::WriteExcel->new(\"regions.xls\");\n\n# Add some worksheets\nmy $north = $workbook->addworksheet(\"North\");\nmy $south = $workbook->addworksheet(\"South\");\nmy $east = $workbook->addworksheet(\"East\");\nmy $west = $workbook->addworksheet(\"West\");\n\n# Add a Format\nmy $format = $workbook->addformat();\n$format->setbold();\n$format->setcolor('blue');\n\n# Add a caption to each worksheet\nforeach my $worksheet ($workbook->sheets()) {\n$worksheet->write(0, 0, \"Sales\", $format);\n}\n\n# Write some data\n$north->write(0, 1, 200000);\n$south->write(0, 1, 100000);\n$east->write (0, 1, 150000);\n$west->write (0, 1, 100000);\n\n# Set the active worksheet\n$south->activate();\n\n# Set the width of the first column\n$south->setcolumn(0, 0, 20);\n\n# Set the active cell\n$south->setselection(0, 1);\n\nDownload this example:\n\n" }, { "name": "Example: stats.pl", "content": "A simple example of how to use functions with the Spreadsheet::WriteExcel module.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple example of how to use functions with the Spreadsheet::WriteExcel\n# module.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"stats.xls\");\nmy $worksheet = $workbook->addworksheet('Test data');\n\n# Set the column width for columns 1\n$worksheet->setcolumn(0, 0, 20);\n\n\n# Create a format for the headings\nmy $format = $workbook->addformat();\n$format->setbold();\n\n\n# Write the sample data\n$worksheet->write(0, 0, 'Sample', $format);\n$worksheet->write(0, 1, 1);\n$worksheet->write(0, 2, 2);\n$worksheet->write(0, 3, 3);\n$worksheet->write(0, 4, 4);\n$worksheet->write(0, 5, 5);\n$worksheet->write(0, 6, 6);\n$worksheet->write(0, 7, 7);\n$worksheet->write(0, 8, 8);\n\n$worksheet->write(1, 0, 'Length', $format);\n$worksheet->write(1, 1, 25.4);\n$worksheet->write(1, 2, 25.4);\n$worksheet->write(1, 3, 24.8);\n$worksheet->write(1, 4, 25.0);\n$worksheet->write(1, 5, 25.3);\n$worksheet->write(1, 6, 24.9);\n$worksheet->write(1, 7, 25.2);\n$worksheet->write(1, 8, 24.8);\n\n# Write some statistical functions\n$worksheet->write(4, 0, 'Count', $format);\n$worksheet->write(4, 1, '=COUNT(B1:I1)');\n\n$worksheet->write(5, 0, 'Sum', $format);\n$worksheet->write(5, 1, '=SUM(B2:I2)');\n\n$worksheet->write(6, 0, 'Average', $format);\n$worksheet->write(6, 1, '=AVERAGE(B2:I2)');\n\n$worksheet->write(7, 0, 'Min', $format);\n$worksheet->write(7, 1, '=MIN(B2:I2)');\n\n$worksheet->write(8, 0, 'Max', $format);\n$worksheet->write(8, 1, '=MAX(B2:I2)');\n\n$worksheet->write(9, 0, 'Standard Deviation', $format);\n$worksheet->write(9, 1, '=STDEV(B2:I2)');\n\n$worksheet->write(10, 0, 'Kurtosis', $format);\n$worksheet->write(10, 1, '=KURT(B2:I2)');\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: formats.pl", "content": "Examples of formatting using the Spreadsheet::WriteExcel module.\n\nThis program demonstrates almost all possible formatting options. It is worth running this\nprogram and viewing the output Excel file if you are interested in the various formatting\npossibilities.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Examples of formatting using the Spreadsheet::WriteExcel module.\n#\n# This program demonstrates almost all possible formatting options. It is worth\n# running this program and viewing the output Excel file if you are interested\n# in the various formatting possibilities.\n#\n# reverse('(c)'), September 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('formats.xls');\n\n# Some common formats\nmy $center = $workbook->addformat(align => 'center');\nmy $heading = $workbook->addformat(align => 'center', bold => 1);\n\n# The named colors\nmy %colors = (\n0x08, 'black',\n0x0C, 'blue',\n0x10, 'brown',\n0x0F, 'cyan',\n0x17, 'gray',\n0x11, 'green',\n0x0B, 'lime',\n0x0E, 'magenta',\n0x12, 'navy',\n0x35, 'orange',\n0x21, 'pink',\n0x14, 'purple',\n0x0A, 'red',\n0x16, 'silver',\n0x09, 'white',\n0x0D, 'yellow',\n);\n\n# Call these subroutines to demonstrate different formatting options\nintro();\nfonts();\nnamedcolors();\nstandardcolors();\nnumericformats();\nborders();\npatterns();\nalignment();\nmisc();\n\n# Note: this is required\n$workbook->close();\n\n\n######################################################################\n#\n# Intro.\n#\nsub intro {\n\nmy $worksheet = $workbook->addworksheet('Introduction');\n\n$worksheet->setcolumn(0, 0, 60);\n\nmy $format = $workbook->addformat();\n$format->setbold();\n$format->setsize(14);\n$format->setcolor('blue');\n$format->setalign('center');\n\nmy $format2 = $workbook->addformat();\n$format2->setbold();\n$format2->setcolor('blue');\n\n$worksheet->write(2, 0, 'This workbook demonstrates some of', $format);\n$worksheet->write(3, 0, 'the formatting options provided by', $format);\n$worksheet->write(4, 0, 'the Spreadsheet::WriteExcel module.', $format);\n\n$worksheet->write('A7', 'Sections:', $format2);\n$worksheet->write('A8', \"internal:Fonts!A1\", 'Fonts' );\n$worksheet->write('A9', \"internal:'Named colors'!A1\", 'Named colors' );\n$worksheet->write('A10', \"internal:'Standard colors'!A1\", 'Standard colors');\n$worksheet->write('A11', \"internal:'Numeric formats'!A1\", 'Numeric formats');\n$worksheet->write('A12', \"internal:Borders!A1\", 'Borders' );\n$worksheet->write('A13', \"internal:Patterns!A1\", 'Patterns' );\n$worksheet->write('A14', \"internal:Alignment!A1\", 'Alignment' );\n$worksheet->write('A15', \"internal:Miscellaneous!A1\", 'Miscellaneous' );\n\n}\n\n\n######################################################################\n#\n# Demonstrate the named colors.\n#\nsub namedcolors {\n\nmy $worksheet = $workbook->addworksheet('Named colors');\n\n$worksheet->setcolumn(0, 3, 15);\n\n$worksheet->write(0, 0, \"Index\", $heading);\n$worksheet->write(0, 1, \"Index\", $heading);\n$worksheet->write(0, 2, \"Name\", $heading);\n$worksheet->write(0, 3, \"Color\", $heading);\n\nmy $i = 1;\n\nwhile (my($index, $color) = each %colors) {\nmy $format = $workbook->addformat(\nbgcolor => $color,\npattern => 1,\nborder => 1\n);\n\n$worksheet->write($i+1, 0, $index, $center);\n$worksheet->write($i+1, 1, sprintf(\"0x%02X\", $index), $center);\n$worksheet->write($i+1, 2, $color, $center);\n$worksheet->write($i+1, 3, '', $format);\n$i++;\n}\n}\n\n\n######################################################################\n#\n# Demonstrate the standard Excel colors in the range 8..63.\n#\nsub standardcolors {\n\nmy $worksheet = $workbook->addworksheet('Standard colors');\n\n$worksheet->setcolumn(0, 3, 15);\n\n$worksheet->write(0, 0, \"Index\", $heading);\n$worksheet->write(0, 1, \"Index\", $heading);\n$worksheet->write(0, 2, \"Color\", $heading);\n$worksheet->write(0, 3, \"Name\", $heading);\n\nfor my $i (8..63) {\nmy $format = $workbook->addformat(\nbgcolor => $i,\npattern => 1,\nborder => 1\n);\n\n$worksheet->write(($i -7), 0, $i, $center);\n$worksheet->write(($i -7), 1, sprintf(\"0x%02X\", $i), $center);\n$worksheet->write(($i -7), 2, '', $format);\n\n# Add the color names\nif (exists $colors{$i}) {\n$worksheet->write(($i -7), 3, $colors{$i}, $center);\n\n}\n}\n}\n\n\n######################################################################\n#\n# Demonstrate the standard numeric formats.\n#\nsub numericformats {\n\nmy $worksheet = $workbook->addworksheet('Numeric formats');\n\n$worksheet->setcolumn(0, 4, 15);\n$worksheet->setcolumn(5, 5, 45);\n\n$worksheet->write(0, 0, \"Index\", $heading);\n$worksheet->write(0, 1, \"Index\", $heading);\n$worksheet->write(0, 2, \"Unformatted\", $heading);\n$worksheet->write(0, 3, \"Formatted\", $heading);\n$worksheet->write(0, 4, \"Negative\", $heading);\n$worksheet->write(0, 5, \"Format\", $heading);\n\nmy @formats;\npush @formats, [ 0x00, 1234.567, 0, 'General' ];\npush @formats, [ 0x01, 1234.567, 0, '0' ];\npush @formats, [ 0x02, 1234.567, 0, '0.00' ];\npush @formats, [ 0x03, 1234.567, 0, '#,##0' ];\npush @formats, [ 0x04, 1234.567, 0, '#,##0.00' ];\npush @formats, [ 0x05, 1234.567, -1234.567, '($#,##0);($#,##0)' ];\npush @formats, [ 0x06, 1234.567, -1234.567, '($#,##0);[Red]($#,##0)' ];\npush @formats, [ 0x07, 1234.567, -1234.567, '($#,##0.00);($#,##0.00)' ];\npush @formats, [ 0x08, 1234.567, -1234.567, '($#,##0.00);[Red]($#,##0.00)' ];\npush @formats, [ 0x09, 0.567, 0, '0%' ];\npush @formats, [ 0x0a, 0.567, 0, '0.00%' ];\npush @formats, [ 0x0b, 1234.567, 0, '0.00E+00' ];\npush @formats, [ 0x0c, 0.75, 0, '# ?/?' ];\npush @formats, [ 0x0d, 0.3125, 0, '# ??/??' ];\npush @formats, [ 0x0e, 36892.521, 0, 'm/d/yy' ];\npush @formats, [ 0x0f, 36892.521, 0, 'd-mmm-yy' ];\npush @formats, [ 0x10, 36892.521, 0, 'd-mmm' ];\npush @formats, [ 0x11, 36892.521, 0, 'mmm-yy' ];\npush @formats, [ 0x12, 36892.521, 0, 'h:mm AM/PM' ];\npush @formats, [ 0x13, 36892.521, 0, 'h:mm:ss AM/PM' ];\npush @formats, [ 0x14, 36892.521, 0, 'h:mm' ];\npush @formats, [ 0x15, 36892.521, 0, 'h:mm:ss' ];\npush @formats, [ 0x16, 36892.521, 0, 'm/d/yy h:mm' ];\npush @formats, [ 0x25, 1234.567, -1234.567, '(#,##0);(#,##0)' ];\npush @formats, [ 0x26, 1234.567, -1234.567, '(#,##0);[Red](#,##0)' ];\npush @formats, [ 0x27, 1234.567, -1234.567, '(#,##0.00);(#,##0.00)' ];\npush @formats, [ 0x28, 1234.567, -1234.567, '(#,##0.00);[Red](#,##0.00)' ];\npush @formats, [ 0x29, 1234.567, -1234.567, '(* #,##0);(* (#,##0);(* \"-\");(@)' ];\npush @formats, [ 0x2a, 1234.567, -1234.567, '($* #,##0);($* (#,##0);($* \"-\");(@)' ];\npush @formats, [ 0x2b, 1234.567, -1234.567, '(* #,##0.00);(* (#,##0.00);(* \"-\"??);(@)' ];\npush @formats, [ 0x2c, 1234.567, -1234.567, '($* #,##0.00);($* (#,##0.00);($* \"-\"??);(@)' ];\npush @formats, [ 0x2d, 36892.521, 0, 'mm:ss' ];\npush @formats, [ 0x2e, 3.0153, 0, '[h]:mm:ss' ];\npush @formats, [ 0x2f, 36892.521, 0, 'mm:ss.0' ];\npush @formats, [ 0x30, 1234.567, 0, '##0.0E+0' ];\npush @formats, [ 0x31, 1234.567, 0, '@' ];\n\nmy $i;\nforeach my $format (@formats){\nmy $style = $workbook->addformat();\n$style->setnumformat($format->[0]);\n\n$i++;\n$worksheet->write($i, 0, $format->[0], $center);\n$worksheet->write($i, 1, sprintf(\"0x%02X\", $format->[0]), $center);\n$worksheet->write($i, 2, $format->[1], $center);\n$worksheet->write($i, 3, $format->[1], $style);\n\nif ($format->[2]) {\n$worksheet->write($i, 4, $format->[2], $style);\n}\n\n$worksheet->writestring($i, 5, $format->[3]);\n}\n}\n\n\n######################################################################\n#\n# Demonstrate the font options.\n#\nsub fonts {\n\nmy $worksheet = $workbook->addworksheet('Fonts');\n\n$worksheet->setcolumn(0, 0, 30);\n$worksheet->setcolumn(1, 1, 10);\n\n$worksheet->write(0, 0, \"Font name\", $heading);\n$worksheet->write(0, 1, \"Font size\", $heading);\n\nmy @fonts;\npush @fonts, [ 10, 'Arial' ];\npush @fonts, [ 12, 'Arial' ];\npush @fonts, [ 14, 'Arial' ];\npush @fonts, [ 12, 'Arial Black' ];\npush @fonts, [ 12, 'Arial Narrow' ];\npush @fonts, [ 12, 'Century Schoolbook' ];\npush @fonts, [ 12, 'Courier' ];\npush @fonts, [ 12, 'Courier New' ];\npush @fonts, [ 12, 'Garamond' ];\npush @fonts, [ 12, 'Impact' ];\npush @fonts, [ 12, 'Lucida Handwriting'] ;\npush @fonts, [ 12, 'Times New Roman' ];\npush @fonts, [ 12, 'Symbol' ];\npush @fonts, [ 12, 'Wingdings' ];\npush @fonts, [ 12, 'A font that doesn\\'t exist' ];\n\nmy $i;\nforeach my $font (@fonts){\nmy $format = $workbook->addformat();\n\n$format->setsize($font->[0]);\n$format->setfont($font->[1]);\n\n$i++;\n$worksheet->write($i, 0, $font->[1], $format);\n$worksheet->write($i, 1, $font->[0], $format);\n}\n\n}\n\n\n######################################################################\n#\n# Demonstrate the standard Excel border styles.\n#\nsub borders {\n\nmy $worksheet = $workbook->addworksheet('Borders');\n\n$worksheet->setcolumn(0, 4, 10);\n$worksheet->setcolumn(5, 5, 40);\n\n$worksheet->write(0, 0, \"Index\", $heading);\n$worksheet->write(0, 1, \"Index\", $heading);\n$worksheet->write(0, 3, \"Style\", $heading);\n$worksheet->write(0, 5, \"The style is highlighted in red for \", $heading);\n$worksheet->write(1, 5, \"emphasis, the default color is black.\", $heading);\n\nfor my $i (0..13){\nmy $format = $workbook->addformat();\n$format->setborder($i);\n$format->setbordercolor('red');\n$format->setalign('center');\n\n$worksheet->write((2*($i+1)), 0, $i, $center);\n$worksheet->write((2*($i+1)), 1, sprintf(\"0x%02X\", $i), $center);\n\n$worksheet->write((2*($i+1)), 3, \"Border\", $format);\n}\n\n$worksheet->write(30, 0, \"Diag type\", $heading);\n$worksheet->write(30, 1, \"Index\", $heading);\n$worksheet->write(30, 3, \"Style\", $heading);\n$worksheet->write(30, 5, \"Diagonal Boder styles\", $heading);\n\nfor my $i (1..3){\nmy $format = $workbook->addformat();\n$format->setdiagtype($i);\n$format->setdiagborder(1);\n$format->setdiagcolor('red');\n$format->setalign('center');\n\n$worksheet->write((2*($i+15)), 0, $i, $center);\n$worksheet->write((2*($i+15)), 1, sprintf(\"0x%02X\", $i), $center);\n\n$worksheet->write((2*($i+15)), 3, \"Border\", $format);\n}\n}\n\n\n\n######################################################################\n#\n# Demonstrate the standard Excel cell patterns.\n#\nsub patterns {\n\nmy $worksheet = $workbook->addworksheet('Patterns');\n\n$worksheet->setcolumn(0, 4, 10);\n$worksheet->setcolumn(5, 5, 50);\n\n$worksheet->write(0, 0, \"Index\", $heading);\n$worksheet->write(0, 1, \"Index\", $heading);\n$worksheet->write(0, 3, \"Pattern\", $heading);\n\n$worksheet->write(0, 5, \"The background colour has been set to silver.\", $heading);\n$worksheet->write(1, 5, \"The foreground colour has been set to green.\", $heading);\n\nfor my $i (0..18){\nmy $format = $workbook->addformat();\n\n$format->setpattern($i);\n$format->setbgcolor('silver');\n$format->setfgcolor('green');\n$format->setalign('center');\n\n$worksheet->write((2*($i+1)), 0, $i, $center);\n$worksheet->write((2*($i+1)), 1, sprintf(\"0x%02X\", $i), $center);\n\n$worksheet->write((2*($i+1)), 3, \"Pattern\", $format);\n\nif ($i == 1) {\n$worksheet->write((2*($i+1)), 5, \"This is solid colour, the most useful pattern.\", $heading);\n}\n}\n}\n\n\n######################################################################\n#\n# Demonstrate the standard Excel cell alignments.\n#\nsub alignment {\n\nmy $worksheet = $workbook->addworksheet('Alignment');\n\n$worksheet->setcolumn(0, 7, 12);\n$worksheet->setrow(0, 40);\n$worksheet->setselection(7, 0);\n\nmy $format01 = $workbook->addformat();\nmy $format02 = $workbook->addformat();\nmy $format03 = $workbook->addformat();\nmy $format04 = $workbook->addformat();\nmy $format05 = $workbook->addformat();\nmy $format06 = $workbook->addformat();\nmy $format07 = $workbook->addformat();\nmy $format08 = $workbook->addformat();\nmy $format09 = $workbook->addformat();\nmy $format10 = $workbook->addformat();\nmy $format11 = $workbook->addformat();\nmy $format12 = $workbook->addformat();\nmy $format13 = $workbook->addformat();\nmy $format14 = $workbook->addformat();\nmy $format15 = $workbook->addformat();\nmy $format16 = $workbook->addformat();\nmy $format17 = $workbook->addformat();\n\n$format02->setalign('top');\n$format03->setalign('bottom');\n$format04->setalign('vcenter');\n$format05->setalign('vjustify');\n$format06->settextwrap();\n\n$format07->setalign('left');\n$format08->setalign('right');\n$format09->setalign('center');\n$format10->setalign('fill');\n$format11->setalign('justify');\n$format12->setmerge();\n\n$format13->setrotation(45);\n$format14->setrotation(-45);\n$format15->setrotation(270);\n\n$format16->setshrink();\n$format17->setindent(1);\n\n$worksheet->write(0, 0, 'Vertical', $heading);\n$worksheet->write(0, 1, 'top', $format02);\n$worksheet->write(0, 2, 'bottom', $format03);\n$worksheet->write(0, 3, 'vcenter', $format04);\n$worksheet->write(0, 4, 'vjustify', $format05);\n$worksheet->write(0, 5, \"text\\nwrap\", $format06);\n\n$worksheet->write(2, 0, 'Horizontal', $heading);\n$worksheet->write(2, 1, 'left', $format07);\n$worksheet->write(2, 2, 'right', $format08);\n$worksheet->write(2, 3, 'center', $format09);\n$worksheet->write(2, 4, 'fill', $format10);\n$worksheet->write(2, 5, 'justify', $format11);\n\n$worksheet->write(3, 1, 'merge', $format12);\n$worksheet->write(3, 2, '', $format12);\n\n$worksheet->write(3, 3, 'Shrink ' x 3, $format16);\n$worksheet->write(3, 4, 'Indent', $format17);\n\n\n$worksheet->write(5, 0, 'Rotation', $heading);\n$worksheet->write(5, 1, 'Rotate 45', $format13);\n$worksheet->write(6, 1, 'Rotate -45', $format14);\n$worksheet->write(7, 1, 'Rotate 270', $format15);\n}\n\n\n######################################################################\n#\n# Demonstrate other miscellaneous features.\n#\nsub misc {\n\nmy $worksheet = $workbook->addworksheet('Miscellaneous');\n\n$worksheet->setcolumn(2, 2, 25);\n\nmy $format01 = $workbook->addformat();\nmy $format02 = $workbook->addformat();\nmy $format03 = $workbook->addformat();\nmy $format04 = $workbook->addformat();\nmy $format05 = $workbook->addformat();\nmy $format06 = $workbook->addformat();\nmy $format07 = $workbook->addformat();\n\n$format01->setunderline(0x01);\n$format02->setunderline(0x02);\n$format03->setunderline(0x21);\n$format04->setunderline(0x22);\n$format05->setfontstrikeout();\n$format06->setfontoutline();\n$format07->setfontshadow();\n\n$worksheet->write(1, 2, 'Underline 0x01', $format01);\n$worksheet->write(3, 2, 'Underline 0x02', $format02);\n$worksheet->write(5, 2, 'Underline 0x21', $format03);\n$worksheet->write(7, 2, 'Underline 0x22', $format04);\n$worksheet->write(9, 2, 'Strikeout', $format05);\n$worksheet->write(11, 2, 'Outline (Macintosh only)', $format06);\n$worksheet->write(13, 2, 'Shadow (Macintosh only)', $format07);\n}\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: bugreport.pl", "content": "A template for submitting a bug report.\n\nRun this program and read the output from the command line.\n\n#!/usr/bin/perl -w\n\n\n###############################################################################\n#\n# A template for submitting a bug report.\n#\n# Run this program and read the output from the command line.\n#\n# reverse('(c)'), March 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\n\nprint << 'HINTS1';\n\nREPORTING A BUG OR ASKING A QUESTION\n\nFeel free to report bugs or ask questions. However, to save time\nconsider the following steps first:\n\nRead the documentation:\n\nThe Spreadsheet::WriteExcel documentation has been refined in\nresponse to user questions. Therefore, if you have a question it is\npossible that someone else has asked it before you and that it is\nalready addressed in the documentation. Since there is a lot of\ndocumentation to get through you should at least read the table of\ncontents and search for keywords that you are interested in.\n\nLook at the example programs:\n\nThere are over 70 example programs shipped with the standard\nSpreadsheet::WriteExcel distribution. Many of these were created in\nresponse to user questions. Try to identify an example program that\ncorresponds to your query and adapt it to your needs.\n\nHINTS1\nprint \"Press enter ...\"; ;\n\nprint << 'HINTS2';\n\nIf you submit a bug report here are some pointers.\n\n1. Put \"WriteExcel:\" at the beginning of the subject line. This helps\nto filter genuine messages from spam.\n\n2. Describe the problems as clearly and as concisely as possible.\n\n3. Send a sample program. It is often easier to describe a problem in\ncode than in written prose.\n\n4. The sample program should be as small as possible to demonstrate the\nproblem. Don't copy and past large sections of your program. The\nprogram should also be self contained and working.\n\nA sample bug report is generated below. If you use this format then it\nwill help to analyse your question and respond to it more quickly.\n\nPlease don't send patches without contacting the author first.\n\n\nHINTS2\nprint \"Press enter ...\"; ;\n\n\nprint << 'EMAIL';\n\n=======================================================================\n\nTo: John McNamara \nSubject: WriteExcel: Problem with something.\n\nHi John,\n\nI am using Spreadsheet::WriteExcel and I have encountered a problem. I\nwant it to do SOMETHING but the module appears to do SOMETHINGELSE.\n\nHere is some code that demonstrates the problem.\n\n#!/usr/bin/perl -w\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"reload.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n$worksheet->write(0, 0, \"Hi Excel!\");\n\nEND\n\n\nI tested using Excel XX (or Gnumeric or OpenOffice.org).\n\nMy automatically generated system details are as follows:\nEMAIL\n\n\nprint \"\\n Perl version : $]\";\nprint \"\\n OS name : $^O\";\nprint \"\\n Module versions: (not all are required)\\n\";\n\n\nmy @modules = qw(\nSpreadsheet::WriteExcel\nSpreadsheet::ParseExcel\nOLE::StorageLite\nParse::RecDescent\nFile::Temp\nDigest::MD4\nDigest::Perl::MD4\nDigest::MD5\n);\n\n\nfor my $module (@modules) {\nmy $version;\neval \"require $module\";\n\nif (not $@) {\n$version = $module->VERSION;\n$version = '(unknown)' if not defined $version;\n}\nelse {\n$version = '(not installed)';\n}\n\nprintf \"%21s%-24s\\t%s\\n\", \"\", $module, $version;\n}\n\n\nprint << \"BYE\";\nYours etc.,\n\nA. Person\n--\n\nBYE\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: autofilter.pl", "content": "An example of how to create autofilters with Spreadsheet::WriteExcel.\n\nAn autofilter is a way of adding drop down lists to the headers of a 2D range of worksheet data.\nThis is turn allow users to filter the data based on simple criteria so that some data is shown\nand some is hidden.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# An example of how to create autofilters with Spreadsheet::WriteExcel.\n#\n# An autofilter is a way of adding drop down lists to the headers of a 2D range\n# of worksheet data. This is turn allow users to filter the data based on\n# simple criteria so that some data is shown and some is hidden.\n#\n# reverse('(c)'), September 2007, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('autofilter.xls');\n\ndie \"Couldn't create new Excel file: $!.\\n\" unless defined $workbook;\n\nmy $worksheet1 = $workbook->addworksheet();\nmy $worksheet2 = $workbook->addworksheet();\nmy $worksheet3 = $workbook->addworksheet();\nmy $worksheet4 = $workbook->addworksheet();\nmy $worksheet5 = $workbook->addworksheet();\nmy $worksheet6 = $workbook->addworksheet();\n\nmy $bold = $workbook->addformat(bold => 1);\n\n\n# Extract the data embedded at the end of this file.\nmy @headings = split ' ', ;\nmy @data;\npush @data, [split] while ;\n\n\n# Set up several sheets with the same data.\nfor my $worksheet ($workbook->sheets()) {\n$worksheet->setcolumn('A:D', 12);\n$worksheet->setrow(0, 20, $bold);\n$worksheet->write('A1', \\@headings);\n}\n\n\n###############################################################################\n#\n# Example 1. Autofilter without conditions.\n#\n\n$worksheet1->autofilter('A1:D51');\n$worksheet1->write('A2', [[@data]]);\n\n\n###############################################################################\n#\n#\n# Example 2. Autofilter with a filter condition in the first column.\n#\n\n# The range in this example is the same as above but in row-column notation.\n$worksheet2->autofilter(0, 0, 50, 3);\n\n# The placeholder \"Region\" in the filter is ignored and can be any string\n# that adds clarity to the expression.\n#\n$worksheet2->filtercolumn(0, 'Region eq East');\n\n#\n# Hide the rows that don't match the filter criteria.\n#\nmy $row = 1;\n\nfor my $rowdata (@data) {\nmy $region = $rowdata->[0];\n\nif ($region eq 'East') {\n# Row is visible.\n}\nelse {\n# Hide row.\n$worksheet2->setrow($row, undef, undef, 1);\n}\n\n$worksheet2->write($row++, 0, $rowdata);\n}\n\n\n###############################################################################\n#\n#\n# Example 3. Autofilter with a dual filter condition in one of the columns.\n#\n\n$worksheet3->autofilter('A1:D51');\n\n$worksheet3->filtercolumn('A', 'x eq East or x eq South');\n\n#\n# Hide the rows that don't match the filter criteria.\n#\n$row = 1;\n\nfor my $rowdata (@data) {\nmy $region = $rowdata->[0];\n\nif ($region eq 'East' or $region eq 'South') {\n# Row is visible.\n}\nelse {\n# Hide row.\n$worksheet3->setrow($row, undef, undef, 1);\n}\n\n$worksheet3->write($row++, 0, $rowdata);\n}\n\n\n###############################################################################\n#\n#\n# Example 4. Autofilter with filter conditions in two columns.\n#\n\n$worksheet4->autofilter('A1:D51');\n\n$worksheet4->filtercolumn('A', 'x eq East');\n$worksheet4->filtercolumn('C', 'x > 3000 and x < 8000' );\n\n#\n# Hide the rows that don't match the filter criteria.\n#\n$row = 1;\n\nfor my $rowdata (@data) {\nmy $region = $rowdata->[0];\nmy $volume = $rowdata->[2];\n\nif ($region eq 'East' and\n$volume > 3000 and $volume < 8000\n)\n{\n# Row is visible.\n}\nelse {\n# Hide row.\n$worksheet4->setrow($row, undef, undef, 1);\n}\n\n$worksheet4->write($row++, 0, $rowdata);\n}\n\n\n###############################################################################\n#\n#\n# Example 5. Autofilter with filter for blanks.\n#\n\n# Create a blank cell in our test data.\n$data[5]->[0] = '';\n\n\n$worksheet5->autofilter('A1:D51');\n$worksheet5->filtercolumn('A', 'x == Blanks');\n\n#\n# Hide the rows that don't match the filter criteria.\n#\n$row = 1;\n\nfor my $rowdata (@data) {\nmy $region = $rowdata->[0];\n\nif ($region eq '')\n{\n# Row is visible.\n}\nelse {\n# Hide row.\n$worksheet5->setrow($row, undef, undef, 1);\n}\n\n$worksheet5->write($row++, 0, $rowdata);\n}\n\n\n###############################################################################\n#\n#\n# Example 6. Autofilter with filter for non-blanks.\n#\n\n\n$worksheet6->autofilter('A1:D51');\n$worksheet6->filtercolumn('A', 'x == NonBlanks');\n\n#\n# Hide the rows that don't match the filter criteria.\n#\n$row = 1;\n\nfor my $rowdata (@data) {\nmy $region = $rowdata->[0];\n\nif ($region ne '')\n{\n# Row is visible.\n}\nelse {\n# Hide row.\n$worksheet6->setrow($row, undef, undef, 1);\n}\n\n$worksheet6->write($row++, 0, $rowdata);\n}\n\n\n\nDATA\nRegion Item Volume Month\nEast Apple 9000 July\nEast Apple 5000 July\nSouth Orange 9000 September\nNorth Apple 2000 November\nWest Apple 9000 November\nSouth Pear 7000 October\nNorth Pear 9000 August\nWest Orange 1000 December\nWest Grape 1000 November\nSouth Pear 10000 April\nWest Grape 6000 January\nSouth Orange 3000 May\nNorth Apple 3000 December\nSouth Apple 7000 February\nWest Grape 1000 December\nEast Grape 8000 February\nSouth Grape 10000 June\nWest Pear 7000 December\nSouth Apple 2000 October\nEast Grape 7000 December\nNorth Grape 6000 April\nEast Pear 8000 February\nNorth Apple 7000 August\nNorth Orange 7000 July\nNorth Apple 6000 June\nSouth Grape 8000 September\nWest Apple 3000 October\nSouth Orange 10000 November\nWest Grape 4000 July\nNorth Orange 5000 August\nEast Orange 1000 November\nEast Orange 4000 October\nNorth Grape 5000 August\nEast Apple 1000 December\nSouth Apple 10000 March\nEast Grape 7000 October\nWest Grape 1000 September\nEast Grape 10000 October\nSouth Orange 8000 March\nNorth Apple 4000 July\nSouth Orange 5000 July\nWest Apple 4000 June\nEast Apple 5000 April\nNorth Pear 3000 August\nEast Grape 9000 November\nNorth Orange 8000 October\nEast Apple 10000 June\nSouth Pear 1000 December\nNorth Grape 10000 July\nEast Grape 6000 February\n\nDownload this example:\n\n" }, { "name": "Example: autofit.pl", "content": "Simulate Excel's autofit for column widths.\n\nExcel provides a function called Autofit (Format->Columns->Autofit) that adjusts column widths\nto match the length of the longest string in a column. Excel calculates these widths at run time\nwhen it has access to information about string lengths and font information. This function is\n*not* a feature of the file format and thus cannot be implemented by Spreadsheet::WriteExcel.\n\nHowever, we can make an attempt to simulate it by keeping track of the longest string written to\neach column and then adjusting the column widths prior to closing the file.\n\nWe keep track of the longest strings by adding a handler to the write() function. See" }, { "name": "add_handler", "content": "The main problem with trying to simulate Autofit lies in defining a relationship between a\nstring length and its width in a arbitrary font and size. We use two approaches below. The first\nis a simple direct relationship obtained by trial and error. The second is a slightly more\nsophisticated method using an external module. For more complicated applications you will\nprobably have to work out your own methods.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# Simulate Excel's autofit for column widths.\n#\n# Excel provides a function called Autofit (Format->Columns->Autofit) that\n# adjusts column widths to match the length of the longest string in a column.\n# Excel calculates these widths at run time when it has access to information\n# about string lengths and font information. This function is *not* a feature\n# of the file format and thus cannot be implemented by Spreadsheet::WriteExcel.\n#\n# However, we can make an attempt to simulate it by keeping track of the\n# longest string written to each column and then adjusting the column widths\n# prior to closing the file.\n#\n# We keep track of the longest strings by adding a handler to the write()\n# function. See addhandler() in the S::WE docs for more information.\n#\n# The main problem with trying to simulate Autofit lies in defining a\n# relationship between a string length and its width in a arbitrary font and\n# size. We use two approaches below. The first is a simple direct relationship\n# obtained by trial and error. The second is a slightly more sophisticated\n# method using an external module. For more complicated applications you will\n# probably have to work out your own methods.\n#\n# reverse('(c)'), May 2006, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('autofit.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n###############################################################################\n#\n# Add a handler to store the width of the longest string written to a column.\n# We use the stored width to simulate an autofit of the column widths.\n#\n# You should do this for every worksheet you want to autofit.\n#\n$worksheet->addwritehandler(qr[\\w], \\&storestringwidths);\n\n\n\n$worksheet->write('A1', 'Hello');\n$worksheet->write('B1', 'Hello World');\n$worksheet->write('D1', 'Hello');\n$worksheet->write('F1', 'This is a long string as an example.');\n\n# Run the autofit after you have finished writing strings to the workbook.\nautofitcolumns($worksheet);\n\n\n\n###############################################################################\n#\n# Functions used for Autofit.\n#\n###############################################################################\n\n###############################################################################\n#\n# Adjust the column widths to fit the longest string in the column.\n#\nsub autofitcolumns {\n\nmy $worksheet = shift;\nmy $col = 0;\n\nfor my $width (@{$worksheet->{colwidths}}) {\n\n$worksheet->setcolumn($col, $col, $width) if $width;\n$col++;\n}\n}\n\n\n###############################################################################\n#\n# The following function is a callback that was added via addwritehandler()\n# above. It modifies the write() function so that it stores the maximum\n# unwrapped width of a string in a column.\n#\nsub storestringwidths {\n\nmy $worksheet = shift;\nmy $col = $[1];\nmy $token = $[2];\n\n# Ignore some tokens that we aren't interested in.\nreturn if not defined $token; # Ignore undefs.\nreturn if $token eq ''; # Ignore blank cells.\nreturn if ref $token eq 'ARRAY'; # Ignore array refs.\nreturn if $token =~ /^=/; # Ignore formula\n\n# Ignore numbers\nreturn if $token =~ /^([+-]?)(?=\\d|\\.\\d)\\d*(\\.\\d*)?([Ee]([+-]?\\d+))?$/;\n\n# Ignore various internal and external hyperlinks. In a real scenario\n# you may wish to track the length of the optional strings used with\n# urls.\nreturn if $token =~ m{^[fh]tt?ps?://};\nreturn if $token =~ m{^mailto:};\nreturn if $token =~ m{^(?:in|ex)ternal:};\n\n\n# We store the string width as data in the Worksheet object. We use\n# a double underscore key name to avoid conflicts with future names.\n#\nmy $oldwidth = $worksheet->{colwidths}->[$col];\nmy $stringwidth = stringwidth($token);\n\nif (not defined $oldwidth or $stringwidth > $oldwidth) {\n# You may wish to set a minimum column width as follows.\n#return undef if $stringwidth < 10;\n\n$worksheet->{colwidths}->[$col] = $stringwidth;\n}\n\n\n# Return control to write();\nreturn undef;\n}\n\n\n###############################################################################\n#\n# Very simple conversion between string length and string width for Arial 10.\n# See below for a more sophisticated method.\n#\nsub stringwidth {\n\nreturn 0.9 * length $[0];\n}\n\nEND\n\n\n\n###############################################################################\n#\n# This function uses an external module to get a more accurate width for a\n# string. Note that in a real program you could \"use\" the module instead of\n# \"require\"-ing it and you could make the Font object global to avoid repeated\n# initialisation.\n#\n# Note also that the $pixelwidth to $cellwidth is specific to Arial. For\n# other fonts you should calculate appropriate relationships. A future version\n# of S::WE will provide a way of specifying column widths in pixels instead of\n# cell units in order to simplify this conversion.\n#\nsub stringwidth {\n\nrequire Font::TTFMetrics;\n\nmy $arial = Font::TTFMetrics->new('c:\\windows\\fonts\\arial.ttf');\n\nmy $fontsize = 10;\nmy $dpi = 96;\nmy $unitsperem = $arial->getunitsperem();\nmy $fontwidth = $arial->stringwidth($[0]);\n\n# Convert to pixels as per TTFMetrics docs.\nmy $pixelwidth = 6 + $fontwidth *$fontsize *$dpi /(72 *$unitsperem);\n\n# Add extra pixels for border around text.\n$pixelwidth += 6;\n\n# Convert to cell width (for Arial) and for cell widths > 1.\nmy $cellwidth = ($pixelwidth -5) /7;\n\nreturn $cellwidth;\n\n}\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: bigfile.pl", "content": "Example of creating a Spreadsheet::WriteExcel that is larger than the default 7MB limit.\n\nThis is exactly that same as any other Spreadsheet::WriteExcel program except that is requires\nthat the OLE::Storage module is installed.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of creating a Spreadsheet::WriteExcel that is larger than the\n# default 7MB limit.\n#\n# This is exactly that same as any other Spreadsheet::WriteExcel program except\n# that is requires that the OLE::Storage module is installed.\n#\n# reverse('(c)'), Jan 2007, John McNamara, jmcnamara@cpan.org\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new('bigfile.xls');\nmy $worksheet = $workbook->addworksheet();\n\n$worksheet->setcolumn(0, 50, 18);\n\nfor my $col (0 .. 50) {\nfor my $row (0 .. 6000) {\n$worksheet->write($row, $col, \"Row: $row Col: $col\");\n}\n}\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: cgi.pl", "content": "Example of how to use the Spreadsheet::WriteExcel module to send an Excel file to a browser in a\nCGI program.\n\nOn Windows the hash-bang line should be something like:\n\n#!C:\\Perl\\bin\\perl.exe\n\nThe \"Content-Disposition\" line will cause a prompt to be generated to save the file. If you want\nto stream the file to the browser instead, comment out that line as shown below.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the Spreadsheet::WriteExcel module to send an Excel\n# file to a browser in a CGI program.\n#\n# On Windows the hash-bang line should be something like:\n#\n# #!C:\\Perl\\bin\\perl.exe\n#\n# The \"Content-Disposition\" line will cause a prompt to be generated to save\n# the file. If you want to stream the file to the browser instead, comment out\n# that line as shown below.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Set the filename and send the content type\nmy $filename =\"cgitest.xls\";\n\nprint \"Content-type: application/vnd.ms-excel\\n\";\n# The Content-Disposition will generate a prompt to save the file. If you want\n# to stream the file to the browser, comment out the following line.\nprint \"Content-Disposition: attachment; filename=$filename\\n\";\nprint \"\\n\";\n\n# Create a new workbook and add a worksheet. The special Perl filehandle - will\n# redirect the output to STDOUT\n#\nmy $workbook = Spreadsheet::WriteExcel->new(\\*STDOUT);\nmy $worksheet = $workbook->addworksheet();\n\n\n# Set the column width for column 1\n$worksheet->setcolumn(0, 0, 20);\n\n\n# Create a format\nmy $format = $workbook->addformat();\n$format->setbold();\n$format->setsize(15);\n$format->setcolor('blue');\n\n\n# Write to the workbook\n$worksheet->write(0, 0, \"Hi Excel!\", $format);\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: chartarea.pl", "content": "A simple demo of Area charts in Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple demo of Area charts in Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), December 2009, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new( 'chartarea.xls' );\nmy $worksheet = $workbook->addworksheet();\nmy $bold = $workbook->addformat( bold => 1 );\n\n# Add the worksheet data that the charts will refer to.\nmy $headings = [ 'Category', 'Values 1', 'Values 2' ];\nmy $data = [\n[ 2, 3, 4, 5, 6, 7 ],\n[ 1, 4, 5, 2, 1, 5 ],\n[ 3, 6, 7, 5, 4, 3 ],\n];\n\n$worksheet->write( 'A1', $headings, $bold );\n$worksheet->write( 'A2', $data );\n\n\n###############################################################################\n#\n# Example 1. A minimal chart.\n#\nmy $chart1 = $workbook->addchart( type => 'area' );\n\n# Add values only. Use the default categories.\n$chart1->addseries( values => '=Sheet1!$B$2:$B$7' );\n\n\n###############################################################################\n#\n# Example 2. A minimal chart with user specified categories (X axis)\n# and a series name.\n#\nmy $chart2 = $workbook->addchart( type => 'area' );\n\n# Configure the series.\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n\n###############################################################################\n#\n# Example 3. Same as previous chart but with added title and axes labels.\n#\nmy $chart3 = $workbook->addchart( type => 'area' );\n\n# Configure the series.\n$chart3->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart3->settitle( name => 'Results of sample analysis' );\n$chart3->setxaxis( name => 'Sample number' );\n$chart3->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 4. Same as previous chart but with an added series and with a\n# user specified chart sheet name.\n#\nmy $chart4 = $workbook->addchart( name => 'Results Chart', type => 'area' );\n\n# Configure the series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add another series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$C$2:$C$7',\nname => 'Test data series 2',\n);\n\n# Add some labels.\n$chart4->settitle( name => 'Results of sample analysis' );\n$chart4->setxaxis( name => 'Sample number' );\n$chart4->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 5. Same as Example 3 but as an embedded chart.\n#\nmy $chart5 = $workbook->addchart( type => 'area', embedded => 1 );\n\n# Configure the series.\n$chart5->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart5->settitle( name => 'Results of sample analysis' );\n$chart5->setxaxis( name => 'Sample number' );\n$chart5->setyaxis( name => 'Sample length (cm)' );\n\n# Insert the chart into the main worksheet.\n$worksheet->insertchart( 'E2', $chart5 );\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: chartbar.pl", "content": "A simple demo of Bar charts in Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple demo of Bar charts in Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), December 2009, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new( 'chartbar.xls' );\nmy $worksheet = $workbook->addworksheet();\nmy $bold = $workbook->addformat( bold => 1 );\n\n# Add the worksheet data that the charts will refer to.\nmy $headings = [ 'Category', 'Values 1', 'Values 2' ];\nmy $data = [\n[ 2, 3, 4, 5, 6, 7 ],\n[ 1, 4, 5, 2, 1, 5 ],\n[ 3, 6, 7, 5, 4, 3 ],\n];\n\n$worksheet->write( 'A1', $headings, $bold );\n$worksheet->write( 'A2', $data );\n\n\n###############################################################################\n#\n# Example 1. A minimal chart.\n#\nmy $chart1 = $workbook->addchart( type => 'bar' );\n\n# Add values only. Use the default categories.\n$chart1->addseries( values => '=Sheet1!$B$2:$B$7' );\n\n\n###############################################################################\n#\n# Example 2. A minimal chart with user specified categories (X axis)\n# and a series name.\n#\nmy $chart2 = $workbook->addchart( type => 'bar' );\n\n# Configure the series.\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n\n###############################################################################\n#\n# Example 3. Same as previous chart but with added title and axes labels.\n#\nmy $chart3 = $workbook->addchart( type => 'bar' );\n\n# Configure the series.\n$chart3->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart3->settitle( name => 'Results of sample analysis' );\n$chart3->setxaxis( name => 'Sample number' );\n$chart3->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 4. Same as previous chart but with an added series and with a\n# user specified chart sheet name.\n#\nmy $chart4 = $workbook->addchart( name => 'Results Chart', type => 'bar' );\n\n# Configure the series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add another series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$C$2:$C$7',\nname => 'Test data series 2',\n);\n\n# Add some labels.\n$chart4->settitle( name => 'Results of sample analysis' );\n$chart4->setxaxis( name => 'Sample number' );\n$chart4->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 5. Same as Example 3 but as an embedded chart.\n#\nmy $chart5 = $workbook->addchart( type => 'bar', embedded => 1 );\n\n# Configure the series.\n$chart5->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart5->settitle( name => 'Results of sample analysis' );\n$chart5->setxaxis( name => 'Sample number' );\n$chart5->setyaxis( name => 'Sample length (cm)' );\n\n# Insert the chart into the main worksheet.\n$worksheet->insertchart( 'E2', $chart5 );\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: chartcolumn.pl", "content": "A simple demo of Column charts in Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple demo of Column charts in Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), December 2009, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new( 'chartcolumn.xls' );\nmy $worksheet = $workbook->addworksheet();\nmy $bold = $workbook->addformat( bold => 1 );\n\n# Add the worksheet data that the charts will refer to.\nmy $headings = [ 'Category', 'Values 1', 'Values 2' ];\nmy $data = [\n[ 2, 3, 4, 5, 6, 7 ],\n[ 1, 4, 5, 2, 1, 5 ],\n[ 3, 6, 7, 5, 4, 3 ],\n];\n\n$worksheet->write( 'A1', $headings, $bold );\n$worksheet->write( 'A2', $data );\n\n\n###############################################################################\n#\n# Example 1. A minimal chart.\n#\nmy $chart1 = $workbook->addchart( type => 'column' );\n\n# Add values only. Use the default categories.\n$chart1->addseries( values => '=Sheet1!$B$2:$B$7' );\n\n\n###############################################################################\n#\n# Example 2. A minimal chart with user specified categories (X axis)\n# and a series name.\n#\nmy $chart2 = $workbook->addchart( type => 'column' );\n\n# Configure the series.\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n\n###############################################################################\n#\n# Example 3. Same as previous chart but with added title and axes labels.\n#\nmy $chart3 = $workbook->addchart( type => 'column' );\n\n# Configure the series.\n$chart3->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart3->settitle( name => 'Results of sample analysis' );\n$chart3->setxaxis( name => 'Sample number' );\n$chart3->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 4. Same as previous chart but with an added series and with a\n# user specified chart sheet name.\n#\nmy $chart4 = $workbook->addchart( name => 'Results Chart', type => 'column' );\n\n# Configure the series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add another series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$C$2:$C$7',\nname => 'Test data series 2',\n);\n\n# Add some labels.\n$chart4->settitle( name => 'Results of sample analysis' );\n$chart4->setxaxis( name => 'Sample number' );\n$chart4->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 5. Same as Example 3 but as an embedded chart.\n#\nmy $chart5 = $workbook->addchart( type => 'column', embedded => 1 );\n\n# Configure the series.\n$chart5->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart5->settitle( name => 'Results of sample analysis' );\n$chart5->setxaxis( name => 'Sample number' );\n$chart5->setyaxis( name => 'Sample length (cm)' );\n\n# Insert the chart into the main worksheet.\n$worksheet->insertchart( 'E2', $chart5 );\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: chartline.pl", "content": "A simple demo of Line charts in Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple demo of Line charts in Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), December 2009, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new( 'chartline.xls' );\nmy $worksheet = $workbook->addworksheet();\nmy $bold = $workbook->addformat( bold => 1 );\n\n# Add the worksheet data that the charts will refer to.\nmy $headings = [ 'Category', 'Values 1', 'Values 2' ];\nmy $data = [\n[ 2, 3, 4, 5, 6, 7 ],\n[ 1, 4, 5, 2, 1, 5 ],\n[ 3, 6, 7, 5, 4, 3 ],\n];\n\n$worksheet->write( 'A1', $headings, $bold );\n$worksheet->write( 'A2', $data );\n\n\n###############################################################################\n#\n# Example 1. A minimal chart.\n#\nmy $chart1 = $workbook->addchart( type => 'line' );\n\n# Add values only. Use the default categories.\n$chart1->addseries( values => '=Sheet1!$B$2:$B$7' );\n\n\n###############################################################################\n#\n# Example 2. A minimal chart with user specified categories (X axis)\n# and a series name.\n#\nmy $chart2 = $workbook->addchart( type => 'line' );\n\n# Configure the series.\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n\n###############################################################################\n#\n# Example 3. Same as previous chart but with added title and axes labels.\n#\nmy $chart3 = $workbook->addchart( type => 'line' );\n\n# Configure the series.\n$chart3->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart3->settitle( name => 'Results of sample analysis' );\n$chart3->setxaxis( name => 'Sample number' );\n$chart3->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 4. Same as previous chart but with an added series and with a\n# user specified chart sheet name.\n#\nmy $chart4 = $workbook->addchart( name => 'Results Chart', type => 'line' );\n\n# Configure the series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add another series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$C$2:$C$7',\nname => 'Test data series 2',\n);\n\n# Add some labels.\n$chart4->settitle( name => 'Results of sample analysis' );\n$chart4->setxaxis( name => 'Sample number' );\n$chart4->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 5. Same as Example 3 but as an embedded chart.\n#\nmy $chart5 = $workbook->addchart( type => 'line', embedded => 1 );\n\n# Configure the series.\n$chart5->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart5->settitle( name => 'Results of sample analysis' );\n$chart5->setxaxis( name => 'Sample number' );\n$chart5->setyaxis( name => 'Sample length (cm)' );\n\n# Insert the chart into the main worksheet.\n$worksheet->insertchart( 'E2', $chart5 );\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: chartpie.pl", "content": "A simple demo of Pie charts in Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple demo of Pie charts in Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), December 2009, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new( 'chartpie.xls' );\nmy $worksheet = $workbook->addworksheet();\nmy $bold = $workbook->addformat( bold => 1 );\n\n# Add the worksheet data that the charts will refer to.\nmy $headings = [ 'Category', 'Values' ];\nmy $data = [\n[ 'Apple', 'Cherry', 'Pecan' ],\n[ 60, 30, 10 ],\n];\n\n$worksheet->write( 'A1', $headings, $bold );\n$worksheet->write( 'A2', $data );\n\n\n###############################################################################\n#\n# Example 1. A minimal chart.\n#\nmy $chart1 = $workbook->addchart( type => 'pie' );\n\n# Add values only. Use the default categories.\n$chart1->addseries( values => '=Sheet1!$B$2:$B$4' );\n\n\n###############################################################################\n#\n# Example 2. A minimal chart with user specified categories and a series name.\n#\nmy $chart2 = $workbook->addchart( type => 'pie' );\n\n# Configure the series.\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$4',\nvalues => '=Sheet1!$B$2:$B$4',\nname => 'Pie sales data',\n);\n\n\n###############################################################################\n#\n# Example 3. Same as previous chart but with an added title.\n#\nmy $chart3 = $workbook->addchart( type => 'pie' );\n\n# Configure the series.\n$chart3->addseries(\ncategories => '=Sheet1!$A$2:$A$4',\nvalues => '=Sheet1!$B$2:$B$4',\nname => 'Pie sales data',\n);\n\n# Add a title.\n$chart3->settitle( name => 'Popular Pie Types' );\n\n\n###############################################################################\n#\n# Example 4. Same as previous chart with a user specified chart sheet name.\n#\nmy $chart4 = $workbook->addchart( name => 'Results Chart', type => 'pie' );\n\n# Configure the series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$4',\nvalues => '=Sheet1!$B$2:$B$4',\nname => 'Pie sales data',\n);\n\n# The other chart*.pl examples add a second series in example 4 but additional\n# series aren't plotted in a pie chart.\n\n# Add a title.\n$chart4->settitle( name => 'Popular Pie Types' );\n\n\n###############################################################################\n#\n# Example 5. Same as Example 3 but as an embedded chart.\n#\nmy $chart5 = $workbook->addchart( type => 'pie', embedded => 1 );\n\n# Configure the series.\n$chart5->addseries(\ncategories => '=Sheet1!$A$2:$A$4',\nvalues => '=Sheet1!$B$2:$B$4',\nname => 'Pie sales data',\n);\n\n# Add a title.\n$chart5->settitle( name => 'Popular Pie Types' );\n\n# Insert the chart into the main worksheet.\n$worksheet->insertchart( 'D2', $chart5 );\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: chartscatter.pl", "content": "A simple demo of Scatter charts in Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple demo of Scatter charts in Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), December 2009, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new( 'chartscatter.xls' );\nmy $worksheet = $workbook->addworksheet();\nmy $bold = $workbook->addformat( bold => 1 );\n\n# Add the worksheet data that the charts will refer to.\nmy $headings = [ 'Category', 'Values 1', 'Values 2' ];\nmy $data = [\n[ 2, 3, 4, 5, 6, 7 ],\n[ 1, 4, 5, 2, 1, 5 ],\n[ 3, 6, 7, 5, 4, 3 ],\n];\n\n$worksheet->write( 'A1', $headings, $bold );\n$worksheet->write( 'A2', $data );\n\n\n###############################################################################\n#\n# Example 1. A minimal chart.\n#\nmy $chart1 = $workbook->addchart( type => 'scatter' );\n\n# Add values only. Use the default categories.\n$chart1->addseries( values => '=Sheet1!$B$2:$B$7' );\n\n\n###############################################################################\n#\n# Example 2. A minimal chart with user specified categories (X axis)\n# and a series name.\n#\nmy $chart2 = $workbook->addchart( type => 'scatter' );\n\n# Configure the series.\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n\n###############################################################################\n#\n# Example 3. Same as previous chart but with added title and axes labels.\n#\nmy $chart3 = $workbook->addchart( type => 'scatter' );\n\n# Configure the series.\n$chart3->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart3->settitle( name => 'Results of sample analysis' );\n$chart3->setxaxis( name => 'Sample number' );\n$chart3->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 4. Same as previous chart but with an added series and with a\n# user specified chart sheet name.\n#\nmy $chart4 = $workbook->addchart( name => 'Results Chart', type => 'scatter' );\n\n# Configure the series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add another series.\n$chart4->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$C$2:$C$7',\nname => 'Test data series 2',\n);\n\n# Add some labels.\n$chart4->settitle( name => 'Results of sample analysis' );\n$chart4->setxaxis( name => 'Sample number' );\n$chart4->setyaxis( name => 'Sample length (cm)' );\n\n\n###############################################################################\n#\n# Example 5. Same as Example 3 but as an embedded chart.\n#\nmy $chart5 = $workbook->addchart( type => 'scatter', embedded => 1 );\n\n# Configure the series.\n$chart5->addseries(\ncategories => '=Sheet1!$A$2:$A$7',\nvalues => '=Sheet1!$B$2:$B$7',\nname => 'Test data series 1',\n);\n\n# Add some labels.\n$chart5->settitle( name => 'Results of sample analysis' );\n$chart5->setxaxis( name => 'Sample number' );\n$chart5->setyaxis( name => 'Sample length (cm)' );\n\n# Insert the chart into the main worksheet.\n$worksheet->insertchart( 'E2', $chart5 );\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: chartstock.pl", "content": "A simple demo of Stock charts in Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# A simple demo of Stock charts in Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), January 2010, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new( 'chartstock.xls' );\nmy $worksheet = $workbook->addworksheet();\n\n\n###############################################################################\n#\n# Set up the data worksheet that the charts will refer to. We read the example\n# data from the DATA section at the end of the file. This simulates\n# reading the data from a database or other source.\n#\n# The default Excel Stock chart is an Open-High-Low-Close chart. Therefore\n# we will need data for each of those series.\n#\n# The layout of the DATA section is similar to the layout of the worksheet.\n#\n\n# Add some formats.\nmy $bold = $workbook->addformat( bold => 1 );\nmy $dateformat = $workbook->addformat( numformat => 'dd/mm/yyyy' );\n\n# Increase the width of the column used for date to make it clearer.\n$worksheet->setcolumn( 'A:A', 12 );\n\n# Read the data from the DATA section at the end. In a real example this\n# would probably be a database query.\nmy @stockdata;\n\nwhile ( ) {\nnext unless /\\S/; # Skip blank lines.\nnext if /^#/; # Skip comments.\n\npush @stockdata, [split];\n}\n\n# Write the data to the worksheet.\nmy $row = 0;\nmy $col = 0;\n\nmy $headers = shift @stockdata;\n$worksheet->write( $row++, $col, $headers, $bold );\n\nfor my $stockdata ( @stockdata ) {\n\nmy @data = @$stockdata;\nmy $date = shift @data;\n\n$worksheet->write( $row, $col, $date, $dateformat );\n$worksheet->write( $row, $col + 1, \\@data );\n\n$row++;\n}\n\n\n###############################################################################\n#\n# Example 1. A default Open-High-Low-Close chart with series names, axes labels\n# and a title.\n#\n\nmy $chart1 = $workbook->addchart( type => 'stock' );\n\n# Add a series for each of the Open-High-Low-Close columns. The categories are\n# the dates in the first column.\n\n$chart1->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$B$2:$B$10',\nname => 'Open',\n);\n\n$chart1->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$C$2:$C$10',\nname => 'High',\n);\n\n$chart1->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$D$2:$D$10',\nname => 'Low',\n);\n\n$chart1->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$E$2:$E$10',\nname => 'Close',\n);\n\n# Add a chart title and axes labels.\n$chart1->settitle( name => 'Open-High-Low-Close', );\n$chart1->setxaxis( name => 'Date', );\n$chart1->setyaxis( name => 'Share price', );\n\n###############################################################################\n#\n# Example 2. Same as the previous as an embedded chart.\n#\n\nmy $chart2 = $workbook->addchart( type => 'stock', embedded => 1 );\n\n# Add a series for each of the Open-High-Low-Close columns. The categories are\n# the dates in the first column.\n\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$B$2:$B$10',\nname => 'Open',\n);\n\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$C$2:$C$10',\nname => 'High',\n);\n\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$D$2:$D$10',\nname => 'Low',\n);\n\n$chart2->addseries(\ncategories => '=Sheet1!$A$2:$A$10',\nvalues => '=Sheet1!$E$2:$E$10',\nname => 'Close',\n);\n\n# Add a chart title and axes labels.\n$chart2->settitle( name => 'Open-High-Low-Close', );\n$chart2->setxaxis( name => 'Date', );\n$chart2->setyaxis( name => 'Share price', );\n\n# Insert the chart into the main worksheet.\n$worksheet->insertchart( 'G2', $chart2 );\n\n\nDATA\n# Some sample stock data used for charting.\nDate Open High Low Close\n2009-08-19 100.00 104.06 95.96 100.34\n2009-08-20 101.01 109.08 100.50 108.31\n2009-08-23 110.75 113.48 109.05 109.40\n2009-08-24 111.24 111.60 103.57 104.87\n2009-08-25 104.96 108.00 103.88 106.00\n2009-08-26 104.95 107.95 104.66 107.91\n2009-08-27 108.10 108.62 105.69 106.15\n2009-08-30 105.28 105.49 102.01 102.01\n2009-08-31 102.30 103.71 102.16 102.37\n\nDownload this example:\n\n" }, { "name": "Example: chess.pl", "content": "Example of formatting using the Spreadsheet::WriteExcel module via property hashes.\n\nSetting format properties via hashes of values is useful when you have to deal with a large\nnumber of similar formats. Consider for example a chess board pattern with black squares, white\nunformatted squares and a border.\n\nThis relatively simple example requires 14 separate Format objects although there are only 5\ndifferent properties: black background, top border, bottom border, left border and right border.\n\nUsing property hashes it is possible to define these 5 sets of properties and then add them\ntogether to create the 14 Format configurations.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n########################################################################\n#\n# Example of formatting using the Spreadsheet::WriteExcel module via\n# property hashes.\n#\n# Setting format properties via hashes of values is useful when you have\n# to deal with a large number of similar formats. Consider for example a\n# chess board pattern with black squares, white unformatted squares and\n# a border.\n#\n# This relatively simple example requires 14 separate Format\n# objects although there are only 5 different properties: black\n# background, top border, bottom border, left border and right border.\n#\n# Using property hashes it is possible to define these 5 sets of\n# properties and then add them together to create the 14 Format\n# configurations.\n#\n# reverse('(c)'), July 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"chess.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n\n# Some row and column formatting\n$worksheet->setcolumn('B:I', 10);\n\nfor my $i (1..8) {\n$worksheet->setrow($i, 50);\n}\n\n\n# Define the property hashes\n#\nmy %black = (\n'fgcolor' => 'black',\n'pattern' => 1,\n);\n\nmy %top = ( 'top' => 6 );\nmy %bottom = ( 'bottom' => 6 );\nmy %left = ( 'left' => 6 );\nmy %right = ( 'right' => 6 );\n\n\n# Define the formats\n#\nmy $format01 = $workbook->addformat(%top, %left );\nmy $format02 = $workbook->addformat(%top, %black );\nmy $format03 = $workbook->addformat(%top, );\nmy $format04 = $workbook->addformat(%top, %right, %black );\n\nmy $format05 = $workbook->addformat(%left );\nmy $format06 = $workbook->addformat(%black );\nmy $format07 = $workbook->addformat( );\nmy $format08 = $workbook->addformat(%right, %black );\nmy $format09 = $workbook->addformat(%right );\nmy $format10 = $workbook->addformat(%left, %black );\n\nmy $format11 = $workbook->addformat(%bottom, %left, %black );\nmy $format12 = $workbook->addformat(%bottom );\nmy $format13 = $workbook->addformat(%bottom, %black );\nmy $format14 = $workbook->addformat(%bottom, %right );\n\n\n# Draw the pattern\n$worksheet->write('B2', '', $format01);\n$worksheet->write('C2', '', $format02);\n$worksheet->write('D2', '', $format03);\n$worksheet->write('E2', '', $format02);\n$worksheet->write('F2', '', $format03);\n$worksheet->write('G2', '', $format02);\n$worksheet->write('H2', '', $format03);\n$worksheet->write('I2', '', $format04);\n\n$worksheet->write('B3', '', $format10);\n$worksheet->write('C3', '', $format07);\n$worksheet->write('D3', '', $format06);\n$worksheet->write('E3', '', $format07);\n$worksheet->write('F3', '', $format06);\n$worksheet->write('G3', '', $format07);\n$worksheet->write('H3', '', $format06);\n$worksheet->write('I3', '', $format09);\n\n$worksheet->write('B4', '', $format05);\n$worksheet->write('C4', '', $format06);\n$worksheet->write('D4', '', $format07);\n$worksheet->write('E4', '', $format06);\n$worksheet->write('F4', '', $format07);\n$worksheet->write('G4', '', $format06);\n$worksheet->write('H4', '', $format07);\n$worksheet->write('I4', '', $format08);\n\n$worksheet->write('B5', '', $format10);\n$worksheet->write('C5', '', $format07);\n$worksheet->write('D5', '', $format06);\n$worksheet->write('E5', '', $format07);\n$worksheet->write('F5', '', $format06);\n$worksheet->write('G5', '', $format07);\n$worksheet->write('H5', '', $format06);\n$worksheet->write('I5', '', $format09);\n\n$worksheet->write('B6', '', $format05);\n$worksheet->write('C6', '', $format06);\n$worksheet->write('D6', '', $format07);\n$worksheet->write('E6', '', $format06);\n$worksheet->write('F6', '', $format07);\n$worksheet->write('G6', '', $format06);\n$worksheet->write('H6', '', $format07);\n$worksheet->write('I6', '', $format08);\n\n$worksheet->write('B7', '', $format10);\n$worksheet->write('C7', '', $format07);\n$worksheet->write('D7', '', $format06);\n$worksheet->write('E7', '', $format07);\n$worksheet->write('F7', '', $format06);\n$worksheet->write('G7', '', $format07);\n$worksheet->write('H7', '', $format06);\n$worksheet->write('I7', '', $format09);\n\n$worksheet->write('B8', '', $format05);\n$worksheet->write('C8', '', $format06);\n$worksheet->write('D8', '', $format07);\n$worksheet->write('E8', '', $format06);\n$worksheet->write('F8', '', $format07);\n$worksheet->write('G8', '', $format06);\n$worksheet->write('H8', '', $format07);\n$worksheet->write('I8', '', $format08);\n\n$worksheet->write('B9', '', $format11);\n$worksheet->write('C9', '', $format12);\n$worksheet->write('D9', '', $format13);\n$worksheet->write('E9', '', $format12);\n$worksheet->write('F9', '', $format13);\n$worksheet->write('G9', '', $format12);\n$worksheet->write('H9', '', $format13);\n$worksheet->write('I9', '', $format14);\n\nDownload this example:\n\n" }, { "name": "Example: colors.pl", "content": "Demonstrates Spreadsheet::WriteExcel's named colors and the Excel color palette.\n\nThe setcustomcolor() Worksheet method can be used to override one of the built-in palette\nvalues with a more suitable colour. See the main docs.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n################################################################################\n#\n# Demonstrates Spreadsheet::WriteExcel's named colors and the Excel color\n# palette.\n#\n# The setcustomcolor() Worksheet method can be used to override one of the\n# built-in palette values with a more suitable colour. See the main docs.\n#\n# reverse('(c)'), March 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"colors.xls\");\n\n# Some common formats\nmy $center = $workbook->addformat(align => 'center');\nmy $heading = $workbook->addformat(align => 'center', bold => 1);\n\n\n######################################################################\n#\n# Demonstrate the named colors.\n#\n\nmy %colors = (\n0x08, 'black',\n0x0C, 'blue',\n0x10, 'brown',\n0x0F, 'cyan',\n0x17, 'gray',\n0x11, 'green',\n0x0B, 'lime',\n0x0E, 'magenta',\n0x12, 'navy',\n0x35, 'orange',\n0x21, 'pink',\n0x14, 'purple',\n0x0A, 'red',\n0x16, 'silver',\n0x09, 'white',\n0x0D, 'yellow',\n);\n\nmy $worksheet1 = $workbook->addworksheet('Named colors');\n\n$worksheet1->setcolumn(0, 3, 15);\n\n$worksheet1->write(0, 0, \"Index\", $heading);\n$worksheet1->write(0, 1, \"Index\", $heading);\n$worksheet1->write(0, 2, \"Name\", $heading);\n$worksheet1->write(0, 3, \"Color\", $heading);\n\nmy $i = 1;\n\nwhile (my($index, $color) = each %colors) {\nmy $format = $workbook->addformat(\nfgcolor => $color,\npattern => 1,\nborder => 1\n);\n\n$worksheet1->write($i+1, 0, $index, $center);\n$worksheet1->write($i+1, 1, sprintf(\"0x%02X\", $index), $center);\n$worksheet1->write($i+1, 2, $color, $center);\n$worksheet1->write($i+1, 3, '', $format);\n$i++;\n}\n\n\n######################################################################\n#\n# Demonstrate the standard Excel colors in the range 8..63.\n#\n\nmy $worksheet2 = $workbook->addworksheet('Standard colors');\n\n$worksheet2->setcolumn(0, 3, 15);\n\n$worksheet2->write(0, 0, \"Index\", $heading);\n$worksheet2->write(0, 1, \"Index\", $heading);\n$worksheet2->write(0, 2, \"Color\", $heading);\n$worksheet2->write(0, 3, \"Name\", $heading);\n\nfor my $i (8..63) {\nmy $format = $workbook->addformat(\nfgcolor => $i,\npattern => 1,\nborder => 1\n);\n\n$worksheet2->write(($i -7), 0, $i, $center);\n$worksheet2->write(($i -7), 1, sprintf(\"0x%02X\", $i), $center);\n$worksheet2->write(($i -7), 2, '', $format);\n\n# Add the color names\nif (exists $colors{$i}) {\n$worksheet2->write(($i -7), 3, $colors{$i}, $center);\n\n}\n}\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: comments1.pl", "content": "This example demonstrates writing cell comments.\n\nA cell comment is indicated in Excel by a small red triangle in the upper right-hand corner of\nthe cell.\n\nFor more advanced comment options see comments2.pl.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# This example demonstrates writing cell comments.\n#\n# A cell comment is indicated in Excel by a small red triangle in the upper\n# right-hand corner of the cell.\n#\n# For more advanced comment options see comments2.pl.\n#\n# reverse('(c)'), November 2005, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"comments1.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n\n\n$worksheet->write ('A1', 'Hello' );\n$worksheet->writecomment('A1', 'This is a comment');\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: comments2.pl", "content": "This example demonstrates writing cell comments.\n\nA cell comment is indicated in Excel by a small red triangle in the upper right-hand corner of\nthe cell.\n\nEach of the worksheets demonstrates different features of cell comments.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# This example demonstrates writing cell comments.\n#\n# A cell comment is indicated in Excel by a small red triangle in the upper\n# right-hand corner of the cell.\n#\n# Each of the worksheets demonstrates different features of cell comments.\n#\n# reverse('(c)'), November 2005, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"comments2.xls\");\nmy $textwrap = $workbook->addformat(textwrap => 1, valign => 'top');\nmy $worksheet1 = $workbook->addworksheet();\nmy $worksheet2 = $workbook->addworksheet();\nmy $worksheet3 = $workbook->addworksheet();\nmy $worksheet4 = $workbook->addworksheet();\nmy $worksheet5 = $workbook->addworksheet();\nmy $worksheet6 = $workbook->addworksheet();\nmy $worksheet7 = $workbook->addworksheet();\nmy $worksheet8 = $workbook->addworksheet();\n\n\n# Variables that we will use in each example.\nmy $celltext = '';\nmy $comment = '';\n\n\n\n\n###############################################################################\n#\n# Example 1. Demonstrates a simple cell comment without formatting and Unicode\n# comments encoded as UTF-16 and as UTF-8.\n#\n\n# Set up some formatting.\n$worksheet1->setcolumn('C:C', 25);\n$worksheet1->setrow(2, 50);\n$worksheet1->setrow(5, 50);\n\n\n# Simple ascii string.\n$celltext = 'Hold the mouse over this cell to see the comment.';\n\n$comment = 'This is a comment.';\n\n$worksheet1->write ('C3', $celltext, $textwrap);\n$worksheet1->writecomment('C3', $comment);\n\n\n# UTF-16 string.\n$celltext = 'This is a UTF-16 comment.';\n\n$comment = pack \"n\", 0x263a;\n\n$worksheet1->write ('C6', $celltext, $textwrap);\n$worksheet1->writecomment('C6', $comment, encoding => 1);\n\n\n# UTF-8 string in perl 5.8.\nif ($] >= 5.008) {\n\n$worksheet1->setrow(8, 50);\n$celltext = 'This is a UTF-8 string.';\n$comment = chr 0x263a;\n\n$worksheet1->write ('C9', $celltext, $textwrap);\n$worksheet1->writecomment('C9', $comment);\n}\n\n\n\n###############################################################################\n#\n# Example 2. Demonstrates visible and hidden comments.\n#\n\n# Set up some formatting.\n$worksheet2->setcolumn('C:C', 25);\n$worksheet2->setrow(2, 50);\n$worksheet2->setrow(5, 50);\n\n\n$celltext = 'This cell comment is visible.';\n\n$comment = 'Hello.';\n\n$worksheet2->write ('C3', $celltext, $textwrap);\n$worksheet2->writecomment('C3', $comment, visible => 1);\n\n\n$celltext = \"This cell comment isn't visible (the default).\";\n\n$comment = 'Hello.';\n\n$worksheet2->write ('C6', $celltext, $textwrap);\n$worksheet2->writecomment('C6', $comment);\n\n\n\n\n###############################################################################\n#\n# Example 3. Demonstrates visible and hidden comments set at the worksheet\n# level.\n#\n\n# Set up some formatting.\n$worksheet3->setcolumn('C:C', 25);\n$worksheet3->setrow(2, 50);\n$worksheet3->setrow(5, 50);\n$worksheet3->setrow(8, 50);\n\n# Make all comments on the worksheet visible.\n$worksheet3->showcomments();\n\n$celltext = 'This cell comment is visible, explicitly.';\n\n$comment = 'Hello.';\n\n$worksheet3->write ('C3', $celltext, $textwrap);\n$worksheet3->writecomment('C3', $comment, visible => 1);\n\n\n$celltext = 'This cell comment is also visible because '.\n'we used showcomments().';\n\n$comment = 'Hello.';\n\n$worksheet3->write ('C6', $celltext, $textwrap);\n$worksheet3->writecomment('C6', $comment);\n\n\n$celltext = 'However, we can still override it locally.';\n\n$comment = 'Hello.';\n\n$worksheet3->write ('C9', $celltext, $textwrap);\n$worksheet3->writecomment('C9', $comment, visible => 0);\n\n\n\n\n###############################################################################\n#\n# Example 4. Demonstrates changes to the comment box dimensions.\n#\n\n# Set up some formatting.\n$worksheet4->setcolumn('C:C', 25);\n$worksheet4->setrow(2, 50);\n$worksheet4->setrow(5, 50);\n$worksheet4->setrow(8, 50);\n$worksheet4->setrow(15, 50);\n\n$worksheet4->showcomments();\n\n$celltext = 'This cell comment is default size.';\n\n$comment = 'Hello.';\n\n$worksheet4->write ('C3', $celltext, $textwrap);\n$worksheet4->writecomment('C3', $comment);\n\n\n$celltext = 'This cell comment is twice as wide.';\n\n$comment = 'Hello.';\n\n$worksheet4->write ('C6', $celltext, $textwrap);\n$worksheet4->writecomment('C6', $comment, xscale => 2);\n\n\n$celltext = 'This cell comment is twice as high.';\n\n$comment = 'Hello.';\n\n$worksheet4->write ('C9', $celltext, $textwrap);\n$worksheet4->writecomment('C9', $comment, yscale => 2);\n\n\n$celltext = 'This cell comment is scaled in both directions.';\n\n$comment = 'Hello.';\n\n$worksheet4->write ('C16', $celltext, $textwrap);\n$worksheet4->writecomment('C16', $comment, xscale => 1.2, yscale => 0.8);\n\n\n$celltext = 'This cell comment has width and height specified in pixels.';\n\n$comment = 'Hello.';\n\n$worksheet4->write ('C19', $celltext, $textwrap);\n$worksheet4->writecomment('C19', $comment, width => 200, height => 20);\n\n\n\n###############################################################################\n#\n# Example 5. Demonstrates changes to the cell comment position.\n#\n\n$worksheet5->setcolumn('C:C', 25);\n$worksheet5->setrow(2, 50);\n$worksheet5->setrow(5, 50);\n$worksheet5->setrow(8, 50);\n$worksheet5->setrow(11, 50);\n\n$worksheet5->showcomments();\n\n$celltext = 'This cell comment is in the default position.';\n\n$comment = 'Hello.';\n\n$worksheet5->write ('C3', $celltext, $textwrap);\n$worksheet5->writecomment('C3', $comment);\n\n\n$celltext = 'This cell comment has been moved to another cell.';\n\n$comment = 'Hello.';\n\n$worksheet5->write ('C6', $celltext, $textwrap);\n$worksheet5->writecomment('C6', $comment, startcell => 'E4');\n\n\n$celltext = 'This cell comment has been moved to another cell.';\n\n$comment = 'Hello.';\n\n$worksheet5->write ('C9', $celltext, $textwrap);\n$worksheet5->writecomment('C9', $comment, startrow => 8, startcol => 4);\n\n\n$celltext = 'This cell comment has been shifted within its default cell.';\n\n$comment = 'Hello.';\n\n$worksheet5->write ('C12', $celltext, $textwrap);\n$worksheet5->writecomment('C12', $comment, xoffset => 30, yoffset => 12);\n\n\n\n###############################################################################\n#\n# Example 6. Demonstrates changes to the comment background colour.\n#\n\n$worksheet6->setcolumn('C:C', 25);\n$worksheet6->setrow(2, 50);\n$worksheet6->setrow(5, 50);\n$worksheet6->setrow(8, 50);\n\n$worksheet6->showcomments();\n\n$celltext = 'This cell comment has a different colour.';\n\n$comment = 'Hello.';\n\n$worksheet6->write ('C3', $celltext, $textwrap);\n$worksheet6->writecomment('C3', $comment, color => 'green');\n\n\n$celltext = 'This cell comment has the default colour.';\n\n$comment = 'Hello.';\n\n$worksheet6->write ('C6', $celltext, $textwrap);\n$worksheet6->writecomment('C6', $comment);\n\n\n$celltext = 'This cell comment has a different colour.';\n\n$comment = 'Hello.';\n\n$worksheet6->write ('C9', $celltext, $textwrap);\n$worksheet6->writecomment('C9', $comment, color => 0x35);\n\n\n\n\n###############################################################################\n#\n# Example 7. Demonstrates how to set the cell comment author.\n#\n\n$worksheet7->setcolumn('C:C', 30);\n$worksheet7->setrow(2, 50);\n$worksheet7->setrow(5, 50);\n$worksheet7->setrow(8, 50);\n$worksheet7->setrow(11, 50);\n\nmy $author = '';\nmy $cell = 'C3';\n\n$celltext = \"Move the mouse over this cell and you will see 'Cell commented \".\n\"by $author' (blank) in the status bar at the bottom\";\n\n$comment = 'Hello.';\n\n$worksheet7->write ($cell, $celltext, $textwrap);\n$worksheet7->writecomment($cell, $comment);\n\n\n$author = 'Perl';\n$cell = 'C6';\n$celltext = \"Move the mouse over this cell and you will see 'Cell commented \".\n\"by $author' in the status bar at the bottom\";\n\n$comment = 'Hello.';\n\n$worksheet7->write ($cell, $celltext, $textwrap);\n$worksheet7->writecomment($cell, $comment, author => $author);\n\n\n$author = pack \"n\", 0x20AC; # UTF-16 Euro\n$cell = 'C9';\n$celltext = \"Move the mouse over this cell and you will see 'Cell commented \".\n\"by Euro' in the status bar at the bottom\";\n\n$comment = 'Hello.';\n\n$worksheet7->write ($cell, $celltext, $textwrap);\n$worksheet7->writecomment($cell, $comment, author => $author,\nauthorencoding => 1 );\n\n# UTF-8 string in perl 5.8.\nif ($] >= 5.008) {\n$author = chr 0x20AC;\n$cell = 'C12';\n$celltext = \"Move the mouse over this cell and you will see 'Cell commented \".\n\"by $author' in the status bar at the bottom\";\n$comment = 'Hello.';\n\n$worksheet7->write ($cell, $celltext, $textwrap);\n$worksheet7->writecomment($cell, $comment, author => $author);\n\n}\n\n\n###############################################################################\n#\n# Example 8. Demonstrates the need to explicitly set the row height.\n#\n\n# Set up some formatting.\n$worksheet8->setcolumn('C:C', 25);\n$worksheet8->setrow(2, 80);\n\n$worksheet8->showcomments();\n\n\n$celltext = 'The height of this row has been adjusted explicitly using ' .\n'setrow(). The size of the comment box is adjusted ' .\n'accordingly by WriteExcel.';\n\n$comment = 'Hello.';\n\n$worksheet8->write ('C3', $celltext, $textwrap);\n$worksheet8->writecomment('C3', $comment);\n\n\n$celltext = 'The height of this row has been adjusted by Excel due to the ' .\n'text wrap property being set. Unfortunately this means that ' .\n'the height of the row is unknown to WriteExcel at run time ' .\n\"and thus the comment box is stretched as well.\\n\\n\" .\n'Use setrow() to specify the row height explicitly to avoid ' .\n'this problem.';\n\n$comment = 'Hello.';\n\n$worksheet8->write ('C6', $celltext, $textwrap);\n$worksheet8->writecomment('C6', $comment);\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: copyformat.pl", "content": "Example of how to use the format copying method with Spreadsheet::WriteExcel.\n\nThis feature isn't required very often.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the format copying method with Spreadsheet::WriteExcel.\n#\n# This feature isn't required very often.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create workbook1\nmy $workbook1 = Spreadsheet::WriteExcel->new(\"workbook1.xls\");\nmy $worksheet1 = $workbook1->addworksheet();\nmy $format1a = $workbook1->addformat();\nmy $format1b = $workbook1->addformat();\n\n# Create workbook2\nmy $workbook2 = Spreadsheet::WriteExcel->new(\"workbook2.xls\");\nmy $worksheet2 = $workbook2->addworksheet();\nmy $format2a = $workbook2->addformat();\nmy $format2b = $workbook2->addformat();\n\n\n# Create a global format object that isn't tied to a workbook\nmy $globalformat = Spreadsheet::WriteExcel::Format->new();\n\n# Set the formatting\n$globalformat->setcolor('blue');\n$globalformat->setbold();\n$globalformat->setitalic();\n\n# Create another example format\n$format1b->setcolor('red');\n\n# Copy the global format properties to the worksheet formats\n$format1a->copy($globalformat);\n$format2a->copy($globalformat);\n\n# Copy a format from worksheet1 to worksheet2\n$format2b->copy($format1b);\n\n# Write some output\n$worksheet1->write(0, 0, \"Ciao\", $format1a);\n$worksheet1->write(1, 0, \"Ciao\", $format1b);\n\n$worksheet2->write(0, 0, \"Hello\", $format2a);\n$worksheet2->write(1, 0, \"Hello\", $format2b);\n\nDownload this example:\n\n" }, { "name": "Example: datavalidate.pl", "content": "Example of how to add data validation and dropdown lists to a Spreadsheet::WriteExcel file.\n\nData validation is a feature of Excel which allows you to restrict the data that a users enters\nin a cell and to display help and warning messages. It also allows you to restrict input to\nvalues in a drop down list.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to add data validation and dropdown lists to a\n# Spreadsheet::WriteExcel file.\n#\n# Data validation is a feature of Excel which allows you to restrict the data\n# that a users enters in a cell and to display help and warning messages. It\n# also allows you to restrict input to values in a drop down list.\n#\n# reverse('(c)'), August 2008, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('datavalidate.xls');\nmy $worksheet = $workbook->addworksheet();\n\n# Add a format for the header cells.\nmy $headerformat = $workbook->addformat(\nborder => 1,\nbgcolor => 43,\nbold => 1,\ntextwrap => 1,\nvalign => 'vcenter',\nindent => 1,\n);\n\n# Set up layout of the worksheet.\n$worksheet->setcolumn('A:A', 64);\n$worksheet->setcolumn('B:B', 15);\n$worksheet->setcolumn('D:D', 15);\n$worksheet->setrow(0, 36);\n$worksheet->setselection('B3');\n\n\n# Write the header cells and some data that will be used in the examples.\nmy $row = 0;\nmy $txt;\nmy $heading1 = 'Some examples of data validation in Spreadsheet::WriteExcel';\nmy $heading2 = 'Enter values in this column';\nmy $heading3 = 'Sample Data';\n\n$worksheet->write('A1', $heading1, $headerformat);\n$worksheet->write('B1', $heading2, $headerformat);\n$worksheet->write('D1', $heading3, $headerformat);\n\n$worksheet->write('D3', ['Integers', 1, 10]);\n$worksheet->write('D4', ['List data', 'open', 'high', 'close']);\n$worksheet->write('D5', ['Formula', '=AND(F5=50,G5=60)', 50, 60]);\n\n\n#\n# Example 1. Limiting input to an integer in a fixed range.\n#\n$txt = 'Enter an integer between 1 and 10';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'integer',\ncriteria => 'between',\nminimum => 1,\nmaximum => 10,\n});\n\n\n#\n# Example 2. Limiting input to an integer outside a fixed range.\n#\n$txt = 'Enter an integer that is not between 1 and 10 (using cell references)';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'integer',\ncriteria => 'not between',\nminimum => '=E3',\nmaximum => '=F3',\n});\n\n\n#\n# Example 3. Limiting input to an integer greater than a fixed value.\n#\n$txt = 'Enter an integer greater than 0';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'integer',\ncriteria => '>',\nvalue => 0,\n});\n\n\n#\n# Example 4. Limiting input to an integer less than a fixed value.\n#\n$txt = 'Enter an integer less than 10';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'integer',\ncriteria => '<',\nvalue => 10,\n});\n\n\n#\n# Example 5. Limiting input to a decimal in a fixed range.\n#\n$txt = 'Enter a decimal between 0.1 and 0.5';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'decimal',\ncriteria => 'between',\nminimum => 0.1,\nmaximum => 0.5,\n});\n\n\n#\n# Example 6. Limiting input to a value in a dropdown list.\n#\n$txt = 'Select a value from a drop down list';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'list',\nsource => ['open', 'high', 'close'],\n});\n\n\n#\n# Example 6. Limiting input to a value in a dropdown list.\n#\n$txt = 'Select a value from a drop down list (using a cell range)';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'list',\nsource => '=E4:G4',\n});\n\n\n#\n# Example 7. Limiting input to a date in a fixed range.\n#\n$txt = 'Enter a date between 1/1/2008 and 12/12/2008';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'date',\ncriteria => 'between',\nminimum => '2008-01-01T',\nmaximum => '2008-12-12T',\n});\n\n\n#\n# Example 8. Limiting input to a time in a fixed range.\n#\n$txt = 'Enter a time between 6:00 and 12:00';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'time',\ncriteria => 'between',\nminimum => 'T06:00',\nmaximum => 'T12:00',\n});\n\n\n#\n# Example 9. Limiting input to a string greater than a fixed length.\n#\n$txt = 'Enter a string longer than 3 characters';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'length',\ncriteria => '>',\nvalue => 3,\n});\n\n\n#\n# Example 10. Limiting input based on a formula.\n#\n$txt = 'Enter a value if the following is true \"=AND(F5=50,G5=60)\"';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'custom',\nvalue => '=AND(F5=50,G5=60)',\n});\n\n\n#\n# Example 11. Displaying and modify data validation messages.\n#\n$txt = 'Displays a message when you select the cell';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'integer',\ncriteria => 'between',\nminimum => 1,\nmaximum => 100,\ninputtitle => 'Enter an integer:',\ninputmessage => 'between 1 and 100',\n});\n\n\n#\n# Example 12. Displaying and modify data validation messages.\n#\n$txt = 'Display a custom error message when integer isn\\'t between 1 and 100';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'integer',\ncriteria => 'between',\nminimum => 1,\nmaximum => 100,\ninputtitle => 'Enter an integer:',\ninputmessage => 'between 1 and 100',\nerrortitle => 'Input value is not valid!',\nerrormessage => 'It should be an integer between 1 and 100',\n});\n\n\n#\n# Example 13. Displaying and modify data validation messages.\n#\n$txt = 'Display a custom information message when integer isn\\'t between 1 and 100';\n$row += 2;\n\n$worksheet->write($row, 0, $txt);\n$worksheet->datavalidation($row, 1,\n{\nvalidate => 'integer',\ncriteria => 'between',\nminimum => 1,\nmaximum => 100,\ninputtitle => 'Enter an integer:',\ninputmessage => 'between 1 and 100',\nerrortitle => 'Input value is not valid!',\nerrormessage => 'It should be an integer between 1 and 100',\nerrortype => 'information',\n});\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: datetime.pl", "content": "Spreadsheet::WriteExcel example of writing dates and times using the writedatetime() Worksheet\nmethod.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Spreadsheet::WriteExcel example of writing dates and times using the\n# writedatetime() Worksheet method.\n#\n# reverse('(c)'), August 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"datetime.xls\");\nmy $worksheet = $workbook->addworksheet();\nmy $bold = $workbook->addformat(bold => 1);\nmy $row = 0;\n\n\n# Expand the first column so that the date is visible.\n$worksheet->setcolumn(\"A:B\", 30);\n\n\n# Write the column headers\n$worksheet->write('A1', 'Formatted date', $bold);\n$worksheet->write('B1', 'Format', $bold);\n\n\n# Examples date and time formats. In the output file compare how changing\n# the format codes change the appearance of the date.\n#\nmy @dateformats = (\n'dd/mm/yy',\n'mm/dd/yy',\n'',\n'd mm yy',\n'dd mm yy',\n'',\n'dd m yy',\n'dd mm yy',\n'dd mmm yy',\n'dd mmmm yy',\n'',\n'dd mm y',\n'dd mm yyy',\n'dd mm yyyy',\n'',\n'd mmmm yyyy',\n'',\n'dd/mm/yy',\n'dd/mm/yy hh:mm',\n'dd/mm/yy hh:mm:ss',\n'dd/mm/yy hh:mm:ss.000',\n'',\n'hh:mm',\n'hh:mm:ss',\n'hh:mm:ss.000',\n);\n\n\n# Write the same date and time using each of the above formats. The empty\n# string formats create a blank line to make the example clearer.\n#\nfor my $dateformat (@dateformats) {\n$row++;\nnext if $dateformat eq '';\n\n# Create a format for the date or time.\nmy $format = $workbook->addformat(\nnumformat => $dateformat,\nalign => 'left'\n);\n\n# Write the same date using different formats.\n$worksheet->writedatetime($row, 0, '2004-08-01T12:30:45.123', $format);\n$worksheet->write ($row, 1, $dateformat);\n}\n\n\n# The following is an example of an invalid date. It is written as a string\n# instead of a number. This is also Excel's default behaviour.\n#\n$row += 2;\n$worksheet->writedatetime($row, 0, '2004-13-01T12:30:45.123');\n$worksheet->write ($row, 1, 'Invalid date. Written as string.', $bold);\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: definedname.pl", "content": "Example of how to create defined names in a Spreadsheet::WriteExcel file.\n\nThis method is used to defined a name that can be used to represent a value, a single cell or a\nrange of cells in a workbook.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to create defined names in a Spreadsheet::WriteExcel file.\n#\n# This method is used to defined a name that can be used to represent a value,\n# a single cell or a range of cells in a workbook.\n#\n# reverse('(c)'), September 2008, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('definedname.xls');\nmy $worksheet1 = $workbook->addworksheet();\nmy $worksheet2 = $workbook->addworksheet();\n\n\n$workbook->definename('Exchangerate', '=0.96');\n$workbook->definename('Sales', '=Sheet1!$G$1:$H$10');\n$workbook->definename('Sheet2!Sales', '=Sheet2!$G$1:$G$10');\n\n\nfor my $worksheet ($workbook->sheets()) {\n$worksheet->setcolumn('A:A', 45);\n$worksheet->write('A2', 'This worksheet contains some defined names,');\n$worksheet->write('A3', 'See the Insert -> Name -> Define dialog.');\n\n}\n\n\n$worksheet1->write('A4', '=Exchangerate');\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: diagborder.pl", "content": "A simple formatting example that demonstrates how to add a diagonal cell border with\nSpreadsheet::WriteExcel\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple formatting example that demonstrates how to add a diagonal cell\n# border with Spreadsheet::WriteExcel\n#\n# reverse('(c)'), May 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new('diagborder.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\nmy $format1 = $workbook->addformat(diagtype => '1');\n\nmy $format2 = $workbook->addformat(diagtype => '2');\n\nmy $format3 = $workbook->addformat(diagtype => '3');\n\nmy $format4 = $workbook->addformat(\ndiagtype => '3',\ndiagborder => '7',\ndiagcolor => 'red',\n);\n\n\n$worksheet->write('B3', 'Text', $format1);\n$worksheet->write('B6', 'Text', $format2);\n$worksheet->write('B9', 'Text', $format3);\n$worksheet->write('B12', 'Text', $format4);\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: easteregg.pl", "content": "This uses the Win32::OLE module to expose the Flight Simulator easter egg in Excel 97 SR2.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# This uses the Win32::OLE module to expose the Flight Simulator easter egg\n# in Excel 97 SR2.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Win32::OLE;\n\nmy $application = Win32::OLE->new(\"Excel.Application\");\nmy $workbook = $application->Workbooks->Add;\nmy $worksheet = $workbook->Worksheets(1);\n\n$application->{Visible} = 1;\n\n$worksheet->Range(\"L97:X97\")->Select;\n$worksheet->Range(\"M97\")->Activate;\n\nmy $message = \"Hold down Shift and Ctrl and click the \".\n\"Chart Wizard icon on the toolbar.\\n\\n\".\n\"Use the mouse motion and buttons to control \".\n\"movement. Try to find the monolith. \".\n\"Close this dialog first.\";\n\n$application->InputBox($message);\n\nDownload this example:\n\n" }, { "name": "Example: filehandle.pl", "content": "Example of using Spreadsheet::WriteExcel to write Excel files to different filehandles.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of using Spreadsheet::WriteExcel to write Excel files to\n# different filehandles.\n#\n# reverse('(c)'), April 2003, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\nuse IO::Scalar;\n\n\n\n\n###############################################################################\n#\n# Example 1. This demonstrates the standard way of creating an Excel file by\n# specifying a file name.\n#\n\nmy $workbook1 = Spreadsheet::WriteExcel->new('fh01.xls');\nmy $worksheet1 = $workbook1->addworksheet();\n\n$worksheet1->write(0, 0, \"Hi Excel!\");\n\n\n\n\n###############################################################################\n#\n# Example 2. Write an Excel file to an existing filehandle.\n#\n\nopen TEST, \"> fh02.xls\" or die \"Couldn't open file: $!\";\nbinmode TEST; # Always do this regardless of whether the platform requires it.\n\nmy $workbook2 = Spreadsheet::WriteExcel->new(\\*TEST);\nmy $worksheet2 = $workbook2->addworksheet();\n\n$worksheet2->write(0, 0, \"Hi Excel!\");\n\n\n\n\n###############################################################################\n#\n# Example 3. Write an Excel file to an existing OO style filehandle.\n#\n\nmy $fh = FileHandle->new(\"> fh03.xls\")\nor die \"Couldn't open file: $!\";\n\nbinmode($fh);\n\nmy $workbook3 = Spreadsheet::WriteExcel->new($fh);\nmy $worksheet3 = $workbook3->addworksheet();\n\n$worksheet3->write(0, 0, \"Hi Excel!\");\n\n\n\n\n###############################################################################\n#\n# Example 4. Write an Excel file to a string via IO::Scalar. Please refer to\n# the IO::Scalar documentation for further details.\n#\n\nmy $xlsstr;\n\ntie *XLS, 'IO::Scalar', \\$xlsstr;\n\nmy $workbook4 = Spreadsheet::WriteExcel->new(\\*XLS);\nmy $worksheet4 = $workbook4->addworksheet();\n\n$worksheet4->write(0, 0, \"Hi Excel 4\");\n$workbook4->close(); # This is required before we use the scalar\n\n\n# The Excel file is now in $xlsstr. As a demonstration, print it to a file.\nopen TMP, \"> fh04.xls\" or die \"Couldn't open file: $!\";\nbinmode TMP;\nprint TMP $xlsstr;\nclose TMP;\n\n\n\n\n###############################################################################\n#\n# Example 5. Write an Excel file to a string via IO::Scalar's newer interface.\n# Please refer to the IO::Scalar documentation for further details.\n#\nmy $xlsstr2;\n\nmy $fh5 = IO::Scalar->new(\\$xlsstr2);\n\n\nmy $workbook5 = Spreadsheet::WriteExcel->new($fh5);\nmy $worksheet5 = $workbook5->addworksheet();\n\n$worksheet5->write(0, 0, \"Hi Excel 5\");\n$workbook5->close(); # This is required before we use the scalar\n\n# The Excel file is now in $xlsstr. As a demonstration, print it to a file.\nopen TMP, \"> fh05.xls\" or die \"Couldn't open file: $!\";\nbinmode TMP;\nprint TMP $xlsstr2;\nclose TMP;\n\nDownload this example:\n\n" }, { "name": "Example: formularesult.pl", "content": "Example of how to write Spreadsheet::WriteExcel formulas with a user specified result.\n\nThis is generally only required when writing a spreadsheet for an application other than Excel\nwhere the formula isn't evaluated.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# Example of how to write Spreadsheet::WriteExcel formulas with a user\n# specified result.\n#\n# This is generally only required when writing a spreadsheet for an\n# application other than Excel where the formula isn't evaluated.\n#\n# reverse('(c)'), August 2005, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('formularesult.xls');\nmy $worksheet = $workbook->addworksheet();\nmy $format = $workbook->addformat(color => 'blue');\n\n\n$worksheet->write('A1', '=1+2');\n$worksheet->write('A2', '=1+2', $format, 4);\n$worksheet->write('A3', '=\"ABC\"', undef, 'DEF');\n$worksheet->write('A4', '=IF(A1 > 1, TRUE, FALSE)', undef, 'TRUE');\n$worksheet->write('A5', '=1/0', undef, '#DIV/0!');\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: headers.pl", "content": "This program shows several examples of how to set up headers and footers with\nSpreadsheet::WriteExcel.\n\nThe control characters used in the header/footer strings are:\n\nControl Category Description\n======= ======== ===========\n&L Justification Left\n&C Center\n&R Right\n\n&P Information Page number\n&N Total number of pages\n&D Date\n&T Time\n&F File name\n&A Worksheet name\n\n&fontsize Font Font size\n&\"font,style\" Font name and style\n&U Single underline\n&E Double underline\n&S Strikethrough\n&X Superscript\n&Y Subscript\n\n&& Miscellaneous Literal ampersand &\n\nSee the main Spreadsheet::WriteExcel documentation for more information.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n######################################################################\n#\n# This program shows several examples of how to set up headers and\n# footers with Spreadsheet::WriteExcel.\n#\n# The control characters used in the header/footer strings are:\n#\n# Control Category Description\n# ======= ======== ===========\n# &L Justification Left\n# &C Center\n# &R Right\n#\n# &P Information Page number\n# &N Total number of pages\n# &D Date\n# &T Time\n# &F File name\n# &A Worksheet name\n#\n# &fontsize Font Font size\n# &\"font,style\" Font name and style\n# &U Single underline\n# &E Double underline\n# &S Strikethrough\n# &X Superscript\n# &Y Subscript\n#\n# && Miscellaneous Literal ampersand &\n#\n# See the main Spreadsheet::WriteExcel documentation for more information.\n#\n# reverse('(c)'), March 2002, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"headers.xls\");\nmy $preview = \"Select Print Preview to see the header and footer\";\n\n\n######################################################################\n#\n# A simple example to start\n#\nmy $worksheet1 = $workbook->addworksheet('Simple');\n\nmy $header1 = '&CHere is some centred text.';\n\nmy $footer1 = '&LHere is some left aligned text.';\n\n\n$worksheet1->setheader($header1);\n$worksheet1->setfooter($footer1);\n\n$worksheet1->setcolumn('A:A', 50);\n$worksheet1->write('A1', $preview);\n\n\n\n\n######################################################################\n#\n# This is an example of some of the header/footer variables.\n#\nmy $worksheet2 = $workbook->addworksheet('Variables');\n\nmy $header2 = '&LPage &P of &N'.\n'&CFilename: &F' .\n'&RSheetname: &A';\n\nmy $footer2 = '&LCurrent date: &D'.\n'&RCurrent time: &T';\n\n\n\n$worksheet2->setheader($header2);\n$worksheet2->setfooter($footer2);\n\n\n$worksheet2->setcolumn('A:A', 50);\n$worksheet2->write('A1', $preview);\n$worksheet2->write('A21', \"Next sheet\");\n$worksheet2->sethpagebreaks(20);\n\n\n\n######################################################################\n#\n# This example shows how to use more than one font\n#\nmy $worksheet3 = $workbook->addworksheet('Mixed fonts');\n\nmy $header3 = '&C' .\n'&\"Courier New,Bold\"Hello ' .\n'&\"Arial,Italic\"World';\n\nmy $footer3 = '&C' .\n'&\"Symbol\"e' .\n'&\"Arial\" = mc&X2';\n\n$worksheet3->setheader($header3);\n$worksheet3->setfooter($footer3);\n\n$worksheet3->setcolumn('A:A', 50);\n$worksheet3->write('A1', $preview);\n\n\n\n\n######################################################################\n#\n# Example of line wrapping\n#\nmy $worksheet4 = $workbook->addworksheet('Word wrap');\n\nmy $header4 = \"&CHeading 1\\nHeading 2\\nHeading 3\";\n\n$worksheet4->setheader($header4);\n\n$worksheet4->setcolumn('A:A', 50);\n$worksheet4->write('A1', $preview);\n\n\n\n\n######################################################################\n#\n# Example of inserting a literal ampersand &\n#\nmy $worksheet5 = $workbook->addworksheet('Ampersand');\n\nmy $header5 = \"&CCuriouser && Curiouser - Attorneys at Law\";\n\n$worksheet5->setheader($header5);\n\n$worksheet5->setcolumn('A:A', 50);\n$worksheet5->write('A1', $preview);\n\nDownload this example:\n\n" }, { "name": "Example: hidesheet.pl", "content": "Example of how to hide a worksheet with Spreadsheet::WriteExcel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# Example of how to hide a worksheet with Spreadsheet::WriteExcel.\n#\n# reverse('(c)'), April 2005, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('hidden.xls');\nmy $worksheet1 = $workbook->addworksheet();\nmy $worksheet2 = $workbook->addworksheet();\nmy $worksheet3 = $workbook->addworksheet();\n\n# Sheet2 won't be visible until it is unhidden in Excel.\n$worksheet2->hide();\n\n$worksheet1->write(0, 0, 'Sheet2 is hidden');\n$worksheet2->write(0, 0, 'How did you find me?');\n$worksheet3->write(0, 0, 'Sheet2 is hidden');\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: hyperlink1.pl", "content": "Example of how to use the WriteExcel module to write hyperlinks.\n\nSee also hyperlink2.pl for worksheet URL examples.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the WriteExcel module to write hyperlinks.\n#\n# See also hyperlink2.pl for worksheet URL examples.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"hyperlink.xls\");\nmy $worksheet = $workbook->addworksheet('Hyperlinks');\n\n# Format the first column\n$worksheet->setcolumn('A:A', 30);\n$worksheet->setselection('B1');\n\n\n# Add a sample format\nmy $format = $workbook->addformat();\n$format->setsize(12);\n$format->setbold();\n$format->setcolor('red');\n$format->setunderline();\n\n\n# Write some hyperlinks\n$worksheet->write('A1', 'http://www.perl.com/' );\n$worksheet->write('A3', 'http://www.perl.com/', 'Perl home' );\n$worksheet->write('A5', 'http://www.perl.com/', undef, $format);\n$worksheet->write('A7', 'mailto:jmcnamara@cpan.org', 'Mail me');\n\n# Write a URL that isn't a hyperlink\n$worksheet->writestring('A9', 'http://www.perl.com/');\n\nDownload this example:\n\n" }, { "name": "Example: hyperlink2.pl", "content": "Example of how to use the WriteExcel module to write internal and internal hyperlinks.\n\nIf you wish to run this program and follow the hyperlinks you should create the following\ndirectory structure:\n\nC:\\ -- Temp --+-- Europe\n|\n\\-- Asia\n\nSee also hyperlink1.pl for web URL examples.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the WriteExcel module to write internal and internal\n# hyperlinks.\n#\n# If you wish to run this program and follow the hyperlinks you should create\n# the following directory structure:\n#\n# C:\\ -- Temp --+-- Europe\n# |\n# \\-- Asia\n#\n#\n# See also hyperlink1.pl for web URL examples.\n#\n# reverse('(c)'), February 2002, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create three workbooks:\n# C:\\Temp\\Europe\\Ireland.xls\n# C:\\Temp\\Europe\\Italy.xls\n# C:\\Temp\\Asia\\China.xls\n#\nmy $ireland = Spreadsheet::WriteExcel->new('C:\\Temp\\Europe\\Ireland.xls');\nmy $irelinks = $ireland->addworksheet('Links');\nmy $iresales = $ireland->addworksheet('Sales');\nmy $iredata = $ireland->addworksheet('Product Data');\n\nmy $italy = Spreadsheet::WriteExcel->new('C:\\Temp\\Europe\\Italy.xls');\nmy $italinks = $italy->addworksheet('Links');\nmy $itasales = $italy->addworksheet('Sales');\nmy $itadata = $italy->addworksheet('Product Data');\n\nmy $china = Spreadsheet::WriteExcel->new('C:\\Temp\\Asia\\China.xls');\nmy $chalinks = $china->addworksheet('Links');\nmy $chasales = $china->addworksheet('Sales');\nmy $chadata = $china->addworksheet('Product Data');\n\n# Add a format\nmy $format = $ireland->addformat(color => 'green', bold => 1);\n$irelinks->setcolumn('A:B', 25);\n\n\n###############################################################################\n#\n# Examples of internal links\n#\n$irelinks->write('A1', 'Internal links', $format);\n\n# Internal link\n$irelinks->write('A2', 'internal:Sales!A2');\n\n# Internal link to a range\n$irelinks->write('A3', 'internal:Sales!A3:D3');\n\n# Internal link with an alternative string\n$irelinks->write('A4', 'internal:Sales!A4', 'Link');\n\n# Internal link with a format\n$irelinks->write('A5', 'internal:Sales!A5', $format);\n\n# Internal link with an alternative string and format\n$irelinks->write('A6', 'internal:Sales!A6', 'Link', $format);\n\n# Internal link (spaces in worksheet name)\n$irelinks->write('A7', q{internal:'Product Data'!A7});\n\n\n###############################################################################\n#\n# Examples of external links\n#\n$irelinks->write('B1', 'External links', $format);\n\n# External link to a local file\n$irelinks->write('B2', 'external:Italy.xls');\n\n# External link to a local file with worksheet\n$irelinks->write('B3', 'external:Italy.xls#Sales!B3');\n\n# External link to a local file with worksheet and alternative string\n$irelinks->write('B4', 'external:Italy.xls#Sales!B4', 'Link');\n\n# External link to a local file with worksheet and format\n$irelinks->write('B5', 'external:Italy.xls#Sales!B5', $format);\n\n# External link to a remote file, absolute path\n$irelinks->write('B6', 'external:c:/Temp/Asia/China.xls');\n\n# External link to a remote file, relative path\n$irelinks->write('B7', 'external:../Asia/China.xls');\n\n# External link to a remote file with worksheet\n$irelinks->write('B8', 'external:c:/Temp/Asia/China.xls#Sales!B8');\n\n# External link to a remote file with worksheet (with spaces in the name)\n$irelinks->write('B9', q{external:c:/Temp/Asia/China.xls#'Product Data'!B9});\n\n\n###############################################################################\n#\n# Some utility links to return to the main sheet\n#\n$iresales->write('A2', 'internal:Links!A2', 'Back');\n$iresales->write('A3', 'internal:Links!A3', 'Back');\n$iresales->write('A4', 'internal:Links!A4', 'Back');\n$iresales->write('A5', 'internal:Links!A5', 'Back');\n$iresales->write('A6', 'internal:Links!A6', 'Back');\n$iredata-> write('A7', 'internal:Links!A7', 'Back');\n\n$italinks->write('A1', 'external:Ireland.xls#Links!B2', 'Back');\n$itasales->write('B3', 'external:Ireland.xls#Links!B3', 'Back');\n$itasales->write('B4', 'external:Ireland.xls#Links!B4', 'Back');\n$itasales->write('B5', 'external:Ireland.xls#Links!B5', 'Back');\n$chalinks->write('A1', 'external:../Europe/Ireland.xls#Links!B6', 'Back');\n$chasales->write('B8', 'external:../Europe/Ireland.xls#Links!B8', 'Back');\n$chadata-> write('B9', 'external:../Europe/Ireland.xls#Links!B9', 'Back');\n\nDownload this example:\n\n" }, { "name": "Example: images.pl", "content": "Example of how to insert images into an Excel worksheet using the Spreadsheet::WriteExcel" }, { "name": "insert_image", "content": "Source code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# Example of how to insert images into an Excel worksheet using the\n# Spreadsheet::WriteExcel insertimage() method.\n#\n# reverse('(c)'), October 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook called simple.xls and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"images.xls\");\nmy $worksheet1 = $workbook->addworksheet('Image 1');\nmy $worksheet2 = $workbook->addworksheet('Image 2');\nmy $worksheet3 = $workbook->addworksheet('Image 3');\nmy $worksheet4 = $workbook->addworksheet('Image 4');\n\n# Insert a basic image\n$worksheet1->write('A10', \"Image inserted into worksheet.\");\n$worksheet1->insertimage('A1', 'republic.png');\n\n\n# Insert an image with an offset\n$worksheet2->write('A10', \"Image inserted with an offset.\");\n$worksheet2->insertimage('A1', 'republic.png', 32, 10);\n\n# Insert a scaled image\n$worksheet3->write('A10', \"Image scaled: width x 2, height x 0.8.\");\n$worksheet3->insertimage('A1', 'republic.png', 0, 0, 2, 0.8);\n\n# Insert an image over varied column and row sizes.\n$worksheet4->setcolumn('A:A', 5);\n$worksheet4->setcolumn('B:B', undef, undef, 1); # Hidden\n$worksheet4->setcolumn('C:D', 10);\n$worksheet4->setrow(0, 30);\n$worksheet4->setrow(3, 5);\n\n$worksheet4->write('A10', \"Image inserted over scaled rows and columns.\");\n$worksheet4->insertimage('A1', 'republic.png');\n\nDownload this example:\n\n" }, { "name": "Example: indent.pl", "content": "A simple formatting example using Spreadsheet::WriteExcel.\n\nThis program demonstrates the indentation cell format.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple formatting example using Spreadsheet::WriteExcel.\n#\n# This program demonstrates the indentation cell format.\n#\n# reverse('(c)'), May 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('indent.xls');\n\nmy $worksheet = $workbook->addworksheet();\nmy $indent1 = $workbook->addformat(indent => 1);\nmy $indent2 = $workbook->addformat(indent => 2);\n\n$worksheet->setcolumn('A:A', 40);\n\n\n$worksheet->write('A1', \"This text is indented 1 level\", $indent1);\n$worksheet->write('A2', \"This text is indented 2 levels\", $indent2);\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: merge1.pl", "content": "Simple example of merging cells using the Spreadsheet::WriteExcel module.\n\nThis example merges three cells using the \"Centre Across Selection\" alignment which was the\nExcel 5 method of achieving a merge. For a more modern approach use the mergerange() worksheet\nmethod instead. See the merge3.pl - merge6.pl programs.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Simple example of merging cells using the Spreadsheet::WriteExcel module.\n#\n# This example merges three cells using the \"Centre Across Selection\"\n# alignment which was the Excel 5 method of achieving a merge. For a more\n# modern approach use the mergerange() worksheet method instead.\n# See the merge3.pl - merge6.pl programs.\n#\n# reverse('(c)'), August 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"merge1.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n\n# Increase the cell size of the merged cells to highlight the formatting.\n$worksheet->setcolumn('B:D', 20);\n$worksheet->setrow(2, 30);\n\n\n# Create a merge format\nmy $format = $workbook->addformat(centeracross => 1);\n\n\n# Only one cell should contain text, the others should be blank.\n$worksheet->write (2, 1, \"Center across selection\", $format);\n$worksheet->writeblank(2, 2, $format);\n$worksheet->writeblank(2, 3, $format);\n\nDownload this example:\n\n" }, { "name": "Example: merge2.pl", "content": "Simple example of merging cells using the Spreadsheet::WriteExcel module\n\nThis example merges three cells using the \"Centre Across Selection\" alignment which was the\nExcel 5 method of achieving a merge. For a more modern approach use the mergerange() worksheet\nmethod instead. See the merge3.pl - merge6.pl programs.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Simple example of merging cells using the Spreadsheet::WriteExcel module\n#\n# This example merges three cells using the \"Centre Across Selection\"\n# alignment which was the Excel 5 method of achieving a merge. For a more\n# modern approach use the mergerange() worksheet method instead.\n# See the merge3.pl - merge6.pl programs.\n#\n# reverse('(c)'), August 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"merge2.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n\n# Increase the cell size of the merged cells to highlight the formatting.\n$worksheet->setcolumn(1, 2, 30);\n$worksheet->setrow(2, 40);\n\n\n# Create a merged format\nmy $format = $workbook->addformat(\ncenteracross => 1,\nbold => 1,\nsize => 15,\npattern => 1,\nborder => 6,\ncolor => 'white',\nfgcolor => 'green',\nbordercolor => 'yellow',\nalign => 'vcenter',\n);\n\n\n# Only one cell should contain text, the others should be blank.\n$worksheet->write (2, 1, \"Center across selection\", $format);\n$worksheet->writeblank(2, 2, $format);\n\nDownload this example:\n\n" }, { "name": "Example: merge3.pl", "content": "Example of how to use Spreadsheet::WriteExcel to write a hyperlink in a merged cell. There are\ntwo options writeurlrange() with a standard merge format or mergerange().\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use Spreadsheet::WriteExcel to write a hyperlink in a\n# merged cell. There are two options writeurlrange() with a standard merge\n# format or mergerange().\n#\n# reverse('(c)'), September 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new('merge3.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Increase the cell size of the merged cells to highlight the formatting.\n$worksheet->setrow($, 30) for (1, 3, 6, 7);\n$worksheet->setcolumn('B:D', 20);\n\n\n###############################################################################\n#\n# Example 1: Merge cells containing a hyperlink using writeurlrange()\n# and the standard Excel 5+ merge property.\n#\nmy $format1 = $workbook->addformat(\ncenteracross => 1,\nborder => 1,\nunderline => 1,\ncolor => 'blue',\n);\n\n# Write the cells to be merged\n$worksheet->writeurlrange('B2:D2', 'http://www.perl.com', $format1);\n$worksheet->writeblank('C2', $format1);\n$worksheet->writeblank('D2', $format1);\n\n\n\n###############################################################################\n#\n# Example 2: Merge cells containing a hyperlink using mergerange().\n#\nmy $format2 = $workbook->addformat(\nborder => 1,\nunderline => 1,\ncolor => 'blue',\nalign => 'center',\nvalign => 'vcenter',\n);\n\n# Merge 3 cells\n$worksheet->mergerange('B4:D4', 'http://www.perl.com', $format2);\n\n\n# Merge 3 cells over two rows\n$worksheet->mergerange('B7:D8', 'http://www.perl.com', $format2);\n\nDownload this example:\n\n" }, { "name": "Example: merge4.pl", "content": "Example of how to use the Spreadsheet::WriteExcel mergerange() workbook method with complex\nformatting.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the Spreadsheet::WriteExcel mergerange() workbook\n# method with complex formatting.\n#\n# reverse('(c)'), September 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new('merge4.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Increase the cell size of the merged cells to highlight the formatting.\n$worksheet->setrow($, 30) for (1..11);\n$worksheet->setcolumn('B:D', 20);\n\n\n###############################################################################\n#\n# Example 1: Text centered vertically and horizontally\n#\nmy $format1 = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nvalign => 'vcenter',\nalign => 'center',\n);\n\n\n\n$worksheet->mergerange('B2:D3', 'Vertical and horizontal', $format1);\n\n\n###############################################################################\n#\n# Example 2: Text aligned to the top and left\n#\nmy $format2 = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nvalign => 'top',\nalign => 'left',\n);\n\n\n\n$worksheet->mergerange('B5:D6', 'Aligned to the top and left', $format2);\n\n\n###############################################################################\n#\n# Example 3: Text aligned to the bottom and right\n#\nmy $format3 = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nvalign => 'bottom',\nalign => 'right',\n);\n\n\n\n$worksheet->mergerange('B8:D9', 'Aligned to the bottom and right', $format3);\n\n\n###############################################################################\n#\n# Example 4: Text justified (i.e. wrapped) in the cell\n#\nmy $format4 = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nvalign => 'top',\nalign => 'justify',\n);\n\n\n\n$worksheet->mergerange('B11:D12', 'Justified: '.'so on and ' x18, $format4);\n\nDownload this example:\n\n" }, { "name": "Example: merge5.pl", "content": "Example of how to use the Spreadsheet::WriteExcel mergecells() workbook method with complex\nformatting and rotation.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the Spreadsheet::WriteExcel mergecells() workbook\n# method with complex formatting and rotation.\n#\n#\n# reverse('(c)'), September 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new('merge5.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Increase the cell size of the merged cells to highlight the formatting.\n$worksheet->setrow($, 36) for (3..8);\n$worksheet->setcolumn($, $ , 15) for (1,3,5);\n\n\n###############################################################################\n#\n# Rotation 1, letters run from top to bottom\n#\nmy $format1 = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nvalign => 'vcentre',\nalign => 'centre',\nrotation => 270,\n);\n\n\n$worksheet->mergerange('B4:B9', 'Rotation 270', $format1);\n\n\n###############################################################################\n#\n# Rotation 2, 90 deg anticlockwise\n#\nmy $format2 = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nvalign => 'vcentre',\nalign => 'centre',\nrotation => 90,\n);\n\n\n$worksheet->mergerange('D4:D9', 'Rotation 90 deg', $format2);\n\n\n\n###############################################################################\n#\n# Rotation 3, 90 deg clockwise\n#\nmy $format3 = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nvalign => 'vcentre',\nalign => 'centre',\nrotation => -90,\n);\n\n\n$worksheet->mergerange('F4:F9', 'Rotation -90 deg', $format3);\n\nDownload this example:\n\n" }, { "name": "Example: merge6.pl", "content": "Example of how to use the Spreadsheet::WriteExcel mergecells() workbook method with Unicode\nstrings.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the Spreadsheet::WriteExcel mergecells() workbook\n# method with Unicode strings.\n#\n#\n# reverse('(c)'), December 2005, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new('merge6.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Increase the cell size of the merged cells to highlight the formatting.\n$worksheet->setrow($, 36) for 2..9;\n$worksheet->setcolumn('B:D', 25);\n\n\n# Format for the merged cells.\nmy $format = $workbook->addformat(\nborder => 6,\nbold => 1,\ncolor => 'red',\nsize => 20,\nvalign => 'vcentre',\nalign => 'left',\nindent => 1,\n);\n\n\n\n\n###############################################################################\n#\n# Write an Ascii string.\n#\n\n$worksheet->mergerange('B3:D4', 'ASCII: A simple string', $format);\n\n\n\n\n###############################################################################\n#\n# Write a UTF-16 Unicode string.\n#\n\n# A phrase in Cyrillic encoded as UTF-16BE.\nmy $utf16str = pack \"H*\", '005500540046002d00310036003a0020'.\n'042d0442043e002004440440043004370430002004'.\n'3d043000200440044304410441043a043e043c0021';\n\n# Note the extra parameter at the end to indicate UTF-16 encoding.\n$worksheet->mergerange('B6:D7', $utf16str, $format, 1);\n\n\n\n\n###############################################################################\n#\n# Write a UTF-8 Unicode string.\n#\n\nif ($] >= 5.008) {\nmy $smiley = chr 0x263a;\n$worksheet->mergerange('B9:D10', \"UTF-8: A Unicode smiley $smiley\",\n$format);\n}\nelse {\n$worksheet->mergerange('B9:D10', \"UTF-8: Requires Perl 5.8\", $format);\n}\n\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: modperl1.pl", "content": "Example of how to use the Spreadsheet::WriteExcel module to send an Excel file to a browser\nusing modperl 1 and Apache\n\nThis module ties *XLS directly to Apache, and with the correct content-disposition/types it will\nprompt the user to save the file, or open it at this location.\n\nThis script is a modification of the Spreadsheet::WriteExcel cgi.pl example.\n\nChange the name of this file to Cgi.pm. Change the package location to where ever you locate\nthis package. In the example below it is located in the WriteExcel directory.\n\nYour httpd.conf entry for this module, should you choose to use it as a stand alone app, should\nlook similar to the following:\n\n\nSetHandler perl-script\nPerlHandler Spreadsheet::WriteExcel::Cgi\nPerlSendHeader On\n\n\nThe PerlHandler name above and the package name below *have* to match.\n\n###############################################################################\n#\n# Example of how to use the Spreadsheet::WriteExcel module to send an Excel\n# file to a browser using modperl 1 and Apache\n#\n# This module ties *XLS directly to Apache, and with the correct\n# content-disposition/types it will prompt the user to save\n# the file, or open it at this location.\n#\n# This script is a modification of the Spreadsheet::WriteExcel cgi.pl example.\n#\n# Change the name of this file to Cgi.pm.\n# Change the package location to where ever you locate this package.\n# In the example below it is located in the WriteExcel directory.\n#\n# Your httpd.conf entry for this module, should you choose to use it\n# as a stand alone app, should look similar to the following:\n#\n# \n# SetHandler perl-script\n# PerlHandler Spreadsheet::WriteExcel::Cgi\n# PerlSendHeader On\n# \n#\n# The PerlHandler name above and the package name below *have* to match.\n\n# Apr 2001, Thomas Sullivan, webmaster@860.org\n# Feb 2001, John McNamara, jmcnamara@cpan.org\n\npackage Spreadsheet::WriteExcel::Cgi;\n\n##########################################\n# Pragma Definitions\n##########################################\nuse strict;\n\n##########################################\n# Required Modules\n##########################################\nuse Apache::Constants qw(:common);\nuse Apache::Request;\nuse Apache::URI; # This may not be needed\nuse Spreadsheet::WriteExcel;\n\n##########################################\n# Main App Body\n##########################################\nsub handler {\n# New apache object\n# Should you decide to use it.\nmy $r = Apache::Request->new(shift);\n\n# Set the filename and send the content type\n# This will appear when they save the spreadsheet\nmy $filename =\"cgitest.xls\";\n\n####################################################\n## Send the content type headers\n####################################################\nprint \"Content-disposition: attachment;filename=$filename\\n\";\nprint \"Content-type: application/vnd.ms-excel\\n\\n\";\n\n####################################################\n# Tie a filehandle to Apache's STDOUT.\n# Create a new workbook and add a worksheet.\n####################################################\ntie *XLS => 'Apache';\nbinmode(*XLS);\n\nmy $workbook = Spreadsheet::WriteExcel->new(\\*XLS);\nmy $worksheet = $workbook->addworksheet();\n\n\n# Set the column width for column 1\n$worksheet->setcolumn(0, 0, 20);\n\n\n# Create a format\nmy $format = $workbook->addformat();\n$format->setbold();\n$format->setsize(15);\n$format->setcolor('blue');\n\n\n# Write to the workbook\n$worksheet->write(0, 0, \"Hi Excel!\", $format);\n\n# You must close the workbook for Content-disposition\n$workbook->close();\n}\n\n1;\n\nDownload this example:\n\n" }, { "name": "Example: modperl2.pl", "content": "Example of how to use the Spreadsheet::WriteExcel module to send an Excel file to a browser\nusing modperl 2 and Apache.\n\nThis module ties *XLS directly to Apache, and with the correct content-disposition/types it will\nprompt the user to save the file, or open it at this location.\n\nThis script is a modification of the Spreadsheet::WriteExcel cgi.pl example.\n\nChange the name of this file to MP2Test.pm. Change the package location to where ever you locate\nthis package. In the example below it is located in the WriteExcel directory.\n\nYour httpd.conf entry for this module, should you choose to use it as a stand alone app, should\nlook similar to the following:\n\nPerlModule Apache2::RequestRec\nPerlModule APR::Table\nPerlModule Apache2::RequestIO\n\n\nSetHandler perl-script\nPerlResponseHandler Spreadsheet::WriteExcel::MP2Test\n\n\nThe PerlResponseHandler must match the package name below.\n\n###############################################################################\n#\n# Example of how to use the Spreadsheet::WriteExcel module to send an Excel\n# file to a browser using modperl 2 and Apache.\n#\n# This module ties *XLS directly to Apache, and with the correct\n# content-disposition/types it will prompt the user to save\n# the file, or open it at this location.\n#\n# This script is a modification of the Spreadsheet::WriteExcel cgi.pl example.\n#\n# Change the name of this file to MP2Test.pm.\n# Change the package location to where ever you locate this package.\n# In the example below it is located in the WriteExcel directory.\n#\n# Your httpd.conf entry for this module, should you choose to use it\n# as a stand alone app, should look similar to the following:\n#\n# PerlModule Apache2::RequestRec\n# PerlModule APR::Table\n# PerlModule Apache2::RequestIO\n#\n# \n# SetHandler perl-script\n# PerlResponseHandler Spreadsheet::WriteExcel::MP2Test\n# \n#\n# The PerlResponseHandler must match the package name below.\n\n# Jun 2004, Matisse Enzer, matisse@matisse.net (modperl 2 version)\n# Apr 2001, Thomas Sullivan, webmaster@860.org\n# Feb 2001, John McNamara, jmcnamara@cpan.org\n\npackage Spreadsheet::WriteExcel::MP2Test;\n\n##########################################\n# Pragma Definitions\n##########################################\nuse strict;\n\n##########################################\n# Required Modules\n##########################################\nuse Apache2::Const -compile => qw( :common );\nuse Spreadsheet::WriteExcel;\n\n##########################################\n# Main App Body\n##########################################\nsub handler {\nmy($r) = @; # Apache request object is passed to handler in modperl 2\n\n# Set the filename and send the content type\n# This will appear when they save the spreadsheet\nmy $filename =\"modperl2test.xls\";\n\n####################################################\n## Send the content type headers the modperl 2 way\n####################################################\n$r->headersout->{'Content-Disposition'} = \"attachment;filename=$filename\";\n$r->contenttype('application/vnd.ms-excel');\n\n####################################################\n# Tie a filehandle to Apache's STDOUT.\n# Create a new workbook and add a worksheet.\n####################################################\ntie *XLS => $r; # The modperl 2 way. Tie to the Apache::RequestRec object\nbinmode(*XLS);\n\nmy $workbook = Spreadsheet::WriteExcel->new(\\*XLS);\nmy $worksheet = $workbook->addworksheet();\n\n\n# Set the column width for column 1\n$worksheet->setcolumn(0, 0, 20);\n\n\n# Create a format\nmy $format = $workbook->addformat();\n$format->setbold();\n$format->setsize(15);\n$format->setcolor('blue');\n\n\n# Write to the workbook\n$worksheet->write(0, 0, 'Hi Excel! from ' . $r->hostname , $format);\n\n# You must close the workbook for Content-disposition\n$workbook->close();\nreturn Apache2::Const::OK;\n}\n\n1;\n\nDownload this example:\n\n" }, { "name": "Example: outline.pl", "content": "Example of how use Spreadsheet::WriteExcel to generate Excel outlines and grouping.\n\nExcel allows you to group rows or columns so that they can be hidden or displayed with a single\nmouse click. This feature is referred to as outlines.\n\nOutlines can reduce complex data down to a few salient sub-totals or summaries.\n\nThis feature is best viewed in Excel but the following is an ASCII representation of what a\nworksheet with three outlines might look like. Rows 3-4 and rows 7-8 are grouped at level 2.\nRows 2-9 are grouped at level 1. The lines at the left hand side are called outline level bars.\n\n------------------------------------------\n1 2 3 | | A | B | C | D | ...\n------------------------------------------\n| 1 | A | | | | ...\n| | 2 | B | | | | ...\n| | | 3 | (C) | | | | ...\n| | | 4 | (D) | | | | ...\n| - | 5 | E | | | | ...\n| | 6 | F | | | | ...\n| | | 7 | (G) | | | | ...\n| | | 8 | (H) | | | | ...\n| - | 9 | I | | | | ...\n- | . | ... | ... | ... | ... | ...\n\nClicking the minus sign on each of the level 2 outlines will collapse and hide the data as shown\nin the next figure. The minus sign changes to a plus sign to indicate that the data in the\noutline is hidden.\n\n------------------------------------------\n1 2 3 | | A | B | C | D | ...\n------------------------------------------\n| 1 | A | | | | ...\n| | 2 | B | | | | ...\n| + | 5 | E | | | | ...\n| | 6 | F | | | | ...\n| + | 9 | I | | | | ...\n- | . | ... | ... | ... | ... | ...\n\nClicking on the minus sign on the level 1 outline will collapse the remaining rows as follows:\n\n------------------------------------------\n1 2 3 | | A | B | C | D | ...\n------------------------------------------\n| 1 | A | | | | ...\n+ | . | ... | ... | ... | ... | ...\n\nSee the main Spreadsheet::WriteExcel documentation for more information.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how use Spreadsheet::WriteExcel to generate Excel outlines and\n# grouping.\n#\n#\n# Excel allows you to group rows or columns so that they can be hidden or\n# displayed with a single mouse click. This feature is referred to as outlines.\n#\n# Outlines can reduce complex data down to a few salient sub-totals or\n# summaries.\n#\n# This feature is best viewed in Excel but the following is an ASCII\n# representation of what a worksheet with three outlines might look like.\n# Rows 3-4 and rows 7-8 are grouped at level 2. Rows 2-9 are grouped at\n# level 1. The lines at the left hand side are called outline level bars.\n#\n#\n# ------------------------------------------\n# 1 2 3 | | A | B | C | D | ...\n# ------------------------------------------\n# | 1 | A | | | | ...\n# | | 2 | B | | | | ...\n# | | | 3 | (C) | | | | ...\n# | | | 4 | (D) | | | | ...\n# | - | 5 | E | | | | ...\n# | | 6 | F | | | | ...\n# | | | 7 | (G) | | | | ...\n# | | | 8 | (H) | | | | ...\n# | - | 9 | I | | | | ...\n# - | . | ... | ... | ... | ... | ...\n#\n#\n# Clicking the minus sign on each of the level 2 outlines will collapse and\n# hide the data as shown in the next figure. The minus sign changes to a plus\n# sign to indicate that the data in the outline is hidden.\n#\n# ------------------------------------------\n# 1 2 3 | | A | B | C | D | ...\n# ------------------------------------------\n# | 1 | A | | | | ...\n# | | 2 | B | | | | ...\n# | + | 5 | E | | | | ...\n# | | 6 | F | | | | ...\n# | + | 9 | I | | | | ...\n# - | . | ... | ... | ... | ... | ...\n#\n#\n# Clicking on the minus sign on the level 1 outline will collapse the remaining\n# rows as follows:\n#\n# ------------------------------------------\n# 1 2 3 | | A | B | C | D | ...\n# ------------------------------------------\n# | 1 | A | | | | ...\n# + | . | ... | ... | ... | ... | ...\n#\n# See the main Spreadsheet::WriteExcel documentation for more information.\n#\n# reverse('(c)'), April 2003, John McNamara, jmcnamara@cpan.org\n#\n\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add some worksheets\nmy $workbook = Spreadsheet::WriteExcel->new('outline.xls');\nmy $worksheet1 = $workbook->addworksheet('Outlined Rows');\nmy $worksheet2 = $workbook->addworksheet('Collapsed Rows');\nmy $worksheet3 = $workbook->addworksheet('Outline Columns');\nmy $worksheet4 = $workbook->addworksheet('Outline levels');\n\n# Add a general format\nmy $bold = $workbook->addformat(bold => 1);\n\n\n\n###############################################################################\n#\n# Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL()\n# functions so that it looks like the type of automatic outlines that are\n# generated when you use the Excel Data->SubTotals menu item.\n#\n\n\n# For outlines the important parameters are $hidden and $level. Rows with the\n# same $level are grouped together. The group will be collapsed if $hidden is\n# non-zero. $height and $XF are assigned default values if they are undef.\n#\n# The syntax is: setrow($row, $height, $XF, $hidden, $level, $collapsed)\n#\n$worksheet1->setrow(1, undef, undef, 0, 2);\n$worksheet1->setrow(2, undef, undef, 0, 2);\n$worksheet1->setrow(3, undef, undef, 0, 2);\n$worksheet1->setrow(4, undef, undef, 0, 2);\n$worksheet1->setrow(5, undef, undef, 0, 1);\n\n$worksheet1->setrow(6, undef, undef, 0, 2);\n$worksheet1->setrow(7, undef, undef, 0, 2);\n$worksheet1->setrow(8, undef, undef, 0, 2);\n$worksheet1->setrow(9, undef, undef, 0, 2);\n$worksheet1->setrow(10, undef, undef, 0, 1);\n\n\n# Add a column format for clarity\n$worksheet1->setcolumn('A:A', 20);\n\n# Add the data, labels and formulas\n$worksheet1->write('A1', 'Region', $bold);\n$worksheet1->write('A2', 'North');\n$worksheet1->write('A3', 'North');\n$worksheet1->write('A4', 'North');\n$worksheet1->write('A5', 'North');\n$worksheet1->write('A6', 'North Total', $bold);\n\n$worksheet1->write('B1', 'Sales', $bold);\n$worksheet1->write('B2', 1000);\n$worksheet1->write('B3', 1200);\n$worksheet1->write('B4', 900);\n$worksheet1->write('B5', 1200);\n$worksheet1->write('B6', '=SUBTOTAL(9,B2:B5)', $bold);\n\n$worksheet1->write('A7', 'South');\n$worksheet1->write('A8', 'South');\n$worksheet1->write('A9', 'South');\n$worksheet1->write('A10', 'South');\n$worksheet1->write('A11', 'South Total', $bold);\n\n$worksheet1->write('B7', 400);\n$worksheet1->write('B8', 600);\n$worksheet1->write('B9', 500);\n$worksheet1->write('B10', 600);\n$worksheet1->write('B11', '=SUBTOTAL(9,B7:B10)', $bold);\n\n$worksheet1->write('A12', 'Grand Total', $bold);\n$worksheet1->write('B12', '=SUBTOTAL(9,B2:B10)', $bold);\n\n\n###############################################################################\n#\n# Example 2: Create a worksheet with outlined rows. This is the same as the\n# previous example except that the rows are collapsed.\n# Note: We need to indicate the row that contains the collapsed symbol '+'\n# with the optional parameter, $collapsed.\n\n# The group will be collapsed if $hidden is non-zero.\n# The syntax is: setrow($row, $height, $XF, $hidden, $level, $collapsed)\n#\n$worksheet2->setrow(1, undef, undef, 1, 2);\n$worksheet2->setrow(2, undef, undef, 1, 2);\n$worksheet2->setrow(3, undef, undef, 1, 2);\n$worksheet2->setrow(4, undef, undef, 1, 2);\n$worksheet2->setrow(5, undef, undef, 1, 1);\n\n$worksheet2->setrow(6, undef, undef, 1, 2);\n$worksheet2->setrow(7, undef, undef, 1, 2);\n$worksheet2->setrow(8, undef, undef, 1, 2);\n$worksheet2->setrow(9, undef, undef, 1, 2);\n$worksheet2->setrow(10, undef, undef, 1, 1);\n$worksheet2->setrow(11, undef, undef, 0, 0, 1);\n\n\n# Add a column format for clarity\n$worksheet2->setcolumn('A:A', 20);\n\n# Add the data, labels and formulas\n$worksheet2->write('A1', 'Region', $bold);\n$worksheet2->write('A2', 'North');\n$worksheet2->write('A3', 'North');\n$worksheet2->write('A4', 'North');\n$worksheet2->write('A5', 'North');\n$worksheet2->write('A6', 'North Total', $bold);\n\n$worksheet2->write('B1', 'Sales', $bold);\n$worksheet2->write('B2', 1000);\n$worksheet2->write('B3', 1200);\n$worksheet2->write('B4', 900);\n$worksheet2->write('B5', 1200);\n$worksheet2->write('B6', '=SUBTOTAL(9,B2:B5)', $bold);\n\n$worksheet2->write('A7', 'South');\n$worksheet2->write('A8', 'South');\n$worksheet2->write('A9', 'South');\n$worksheet2->write('A10', 'South');\n$worksheet2->write('A11', 'South Total', $bold);\n\n$worksheet2->write('B7', 400);\n$worksheet2->write('B8', 600);\n$worksheet2->write('B9', 500);\n$worksheet2->write('B10', 600);\n$worksheet2->write('B11', '=SUBTOTAL(9,B7:B10)', $bold);\n\n$worksheet2->write('A12', 'Grand Total', $bold);\n$worksheet2->write('B12', '=SUBTOTAL(9,B2:B10)', $bold);\n\n\n\n###############################################################################\n#\n# Example 3: Create a worksheet with outlined columns.\n#\nmy $data = [\n['Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',' Total'],\n['North', 50, 20, 15, 25, 65, 80, ,'=SUM(B2:G2)'],\n['South', 10, 20, 30, 50, 50, 50, ,'=SUM(B3:G3)'],\n['East', 45, 75, 50, 15, 75, 100, ,'=SUM(B4:G4)'],\n['West', 15, 15, 55, 35, 20, 50, ,'=SUM(B5:G6)'],\n];\n\n# Add bold format to the first row\n$worksheet3->setrow(0, undef, $bold);\n\n# Syntax: setcolumn($col1, $col2, $width, $XF, $hidden, $level, $collapsed)\n$worksheet3->setcolumn('A:A', 10, $bold );\n$worksheet3->setcolumn('B:G', 5, undef, 0, 1);\n$worksheet3->setcolumn('H:H', 10);\n\n# Write the data and a formula\n$worksheet3->writecol('A1', $data);\n$worksheet3->write('H6', '=SUM(H2:H5)', $bold);\n\n\n\n###############################################################################\n#\n# Example 4: Show all possible outline levels.\n#\nmy $levels = [\"Level 1\", \"Level 2\", \"Level 3\", \"Level 4\",\n\"Level 5\", \"Level 6\", \"Level 7\", \"Level 6\",\n\"Level 5\", \"Level 4\", \"Level 3\", \"Level 2\", \"Level 1\"];\n\n\n$worksheet4->writecol('A1', $levels);\n\n$worksheet4->setrow(0, undef, undef, undef, 1);\n$worksheet4->setrow(1, undef, undef, undef, 2);\n$worksheet4->setrow(2, undef, undef, undef, 3);\n$worksheet4->setrow(3, undef, undef, undef, 4);\n$worksheet4->setrow(4, undef, undef, undef, 5);\n$worksheet4->setrow(5, undef, undef, undef, 6);\n$worksheet4->setrow(6, undef, undef, undef, 7);\n$worksheet4->setrow(7, undef, undef, undef, 6);\n$worksheet4->setrow(8, undef, undef, undef, 5);\n$worksheet4->setrow(9, undef, undef, undef, 4);\n$worksheet4->setrow(10, undef, undef, undef, 3);\n$worksheet4->setrow(11, undef, undef, undef, 2);\n$worksheet4->setrow(12, undef, undef, undef, 1);\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: outlinecollapsed.pl", "content": "Example of how use Spreadsheet::WriteExcel to generate Excel outlines and grouping.\n\nThese example focus mainly on collapsed outlines. See also the outlines.pl example program for\nmore general examples.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how use Spreadsheet::WriteExcel to generate Excel outlines and\n# grouping.\n#\n# These example focus mainly on collapsed outlines. See also the\n# outlines.pl example program for more general examples.\n#\n# reverse('(c)'), March 2008, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add some worksheets\nmy $workbook = Spreadsheet::WriteExcel->new('outlinecollapsed.xls');\nmy $worksheet1 = $workbook->addworksheet('Outlined Rows');\nmy $worksheet2 = $workbook->addworksheet('Collapsed Rows 1');\nmy $worksheet3 = $workbook->addworksheet('Collapsed Rows 2');\nmy $worksheet4 = $workbook->addworksheet('Collapsed Rows 3');\nmy $worksheet5 = $workbook->addworksheet('Outline Columns');\nmy $worksheet6 = $workbook->addworksheet('Collapsed Columns');\n\n\n# Add a general format\nmy $bold = $workbook->addformat(bold => 1);\n\n\n#\n# This function will generate the same data and sub-totals on each worksheet.\n#\nsub createsubtotals {\n\nmy $worksheet = $[0];\n\n# Add a column format for clarity\n$worksheet->setcolumn('A:A', 20);\n\n# Add the data, labels and formulas\n$worksheet->write('A1', 'Region', $bold);\n$worksheet->write('A2', 'North');\n$worksheet->write('A3', 'North');\n$worksheet->write('A4', 'North');\n$worksheet->write('A5', 'North');\n$worksheet->write('A6', 'North Total', $bold);\n\n$worksheet->write('B1', 'Sales', $bold);\n$worksheet->write('B2', 1000);\n$worksheet->write('B3', 1200);\n$worksheet->write('B4', 900);\n$worksheet->write('B5', 1200);\n$worksheet->write('B6', '=SUBTOTAL(9,B2:B5)', $bold);\n\n$worksheet->write('A7', 'South');\n$worksheet->write('A8', 'South');\n$worksheet->write('A9', 'South');\n$worksheet->write('A10', 'South');\n$worksheet->write('A11', 'South Total', $bold);\n\n$worksheet->write('B7', 400);\n$worksheet->write('B8', 600);\n$worksheet->write('B9', 500);\n$worksheet->write('B10', 600);\n$worksheet->write('B11', '=SUBTOTAL(9,B7:B10)', $bold);\n\n$worksheet->write('A12', 'Grand Total', $bold);\n$worksheet->write('B12', '=SUBTOTAL(9,B2:B10)', $bold);\n\n}\n\n\n###############################################################################\n#\n# Example 1: Create a worksheet with outlined rows. It also includes SUBTOTAL()\n# functions so that it looks like the type of automatic outlines that are\n# generated when you use the Excel Data->SubTotals menu item.\n#\n\n# The syntax is: setrow($row, $height, $XF, $hidden, $level, $collapsed)\n$worksheet1->setrow(1, undef, undef, 0, 2);\n$worksheet1->setrow(2, undef, undef, 0, 2);\n$worksheet1->setrow(3, undef, undef, 0, 2);\n$worksheet1->setrow(4, undef, undef, 0, 2);\n$worksheet1->setrow(5, undef, undef, 0, 1);\n\n$worksheet1->setrow(6, undef, undef, 0, 2);\n$worksheet1->setrow(7, undef, undef, 0, 2);\n$worksheet1->setrow(8, undef, undef, 0, 2);\n$worksheet1->setrow(9, undef, undef, 0, 2);\n$worksheet1->setrow(10, undef, undef, 0, 1);\n\n# Write the sub-total data that is common to the row examples.\ncreatesubtotals($worksheet1);\n\n\n###############################################################################\n#\n# Example 2: Create a worksheet with collapsed outlined rows.\n# This is the same as the example 1 except that the all rows are collapsed.\n# Note: We need to indicate the row that contains the collapsed symbol '+' with\n# the optional parameter, $collapsed.\n\n$worksheet2->setrow(1, undef, undef, 1, 2);\n$worksheet2->setrow(2, undef, undef, 1, 2);\n$worksheet2->setrow(3, undef, undef, 1, 2);\n$worksheet2->setrow(4, undef, undef, 1, 2);\n$worksheet2->setrow(5, undef, undef, 1, 1);\n\n$worksheet2->setrow(6, undef, undef, 1, 2);\n$worksheet2->setrow(7, undef, undef, 1, 2);\n$worksheet2->setrow(8, undef, undef, 1, 2);\n$worksheet2->setrow(9, undef, undef, 1, 2);\n$worksheet2->setrow(10, undef, undef, 1, 1);\n\n$worksheet2->setrow(11, undef, undef, 0, 0, 1);\n\n# Write the sub-total data that is common to the row examples.\ncreatesubtotals($worksheet2);\n\n\n###############################################################################\n#\n# Example 3: Create a worksheet with collapsed outlined rows.\n# Same as the example 1 except that the two sub-totals are collapsed.\n\n$worksheet3->setrow(1, undef, undef, 1, 2);\n$worksheet3->setrow(2, undef, undef, 1, 2);\n$worksheet3->setrow(3, undef, undef, 1, 2);\n$worksheet3->setrow(4, undef, undef, 1, 2);\n$worksheet3->setrow(5, undef, undef, 0, 1, 1);\n\n$worksheet3->setrow(6, undef, undef, 1, 2);\n$worksheet3->setrow(7, undef, undef, 1, 2);\n$worksheet3->setrow(8, undef, undef, 1, 2);\n$worksheet3->setrow(9, undef, undef, 1, 2);\n$worksheet3->setrow(10, undef, undef, 0, 1, 1);\n\n\n# Write the sub-total data that is common to the row examples.\ncreatesubtotals($worksheet3);\n\n\n###############################################################################\n#\n# Example 4: Create a worksheet with outlined rows.\n# Same as the example 1 except that the two sub-totals are collapsed.\n\n$worksheet4->setrow(1, undef, undef, 1, 2);\n$worksheet4->setrow(2, undef, undef, 1, 2);\n$worksheet4->setrow(3, undef, undef, 1, 2);\n$worksheet4->setrow(4, undef, undef, 1, 2);\n$worksheet4->setrow(5, undef, undef, 1, 1, 1);\n\n$worksheet4->setrow(6, undef, undef, 1, 2);\n$worksheet4->setrow(7, undef, undef, 1, 2);\n$worksheet4->setrow(8, undef, undef, 1, 2);\n$worksheet4->setrow(9, undef, undef, 1, 2);\n$worksheet4->setrow(10, undef, undef, 1, 1, 1);\n\n$worksheet4->setrow(11, undef, undef, 0, 0, 1);\n\n# Write the sub-total data that is common to the row examples.\ncreatesubtotals($worksheet4);\n\n\n\n###############################################################################\n#\n# Example 5: Create a worksheet with outlined columns.\n#\nmy $data = [\n['Month', 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',' Total'],\n['North', 50, 20, 15, 25, 65, 80, ,'=SUM(B2:G2)'],\n['South', 10, 20, 30, 50, 50, 50, ,'=SUM(B3:G3)'],\n['East', 45, 75, 50, 15, 75, 100, ,'=SUM(B4:G4)'],\n['West', 15, 15, 55, 35, 20, 50, ,'=SUM(B5:G6)'],\n];\n\n# Add bold format to the first row\n$worksheet5->setrow(0, undef, $bold);\n\n# Syntax: setcolumn($col1, $col2, $width, $XF, $hidden, $level, $collapsed)\n$worksheet5->setcolumn('A:A', 10, $bold );\n$worksheet5->setcolumn('B:G', 5, undef, 0, 1);\n$worksheet5->setcolumn('H:H', 10 );\n\n# Write the data and a formula\n$worksheet5->writecol('A1', $data);\n$worksheet5->write('H6', '=SUM(H2:H5)', $bold);\n\n\n###############################################################################\n#\n# Example 6: Create a worksheet with collapsed outlined columns.\n# This is the same as the previous example except collapsed columns.\n\n# Add bold format to the first row\n$worksheet6->setrow(0, undef, $bold);\n\n# Syntax: setcolumn($col1, $col2, $width, $XF, $hidden, $level, $collapsed)\n$worksheet6->setcolumn('A:A', 10, $bold );\n$worksheet6->setcolumn('B:G', 5, undef, 1, 1 );\n$worksheet6->setcolumn('H:H', 10, undef, 0, 0, 1);\n\n# Write the data and a formula\n$worksheet6->writecol('A1', $data);\n$worksheet6->write('H6', '=SUM(H2:H5)', $bold);\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: panes.pl", "content": "Example of using the WriteExcel module to create worksheet panes.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# Example of using the WriteExcel module to create worksheet panes.\n#\n# reverse('(c)'), May 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"panes.xls\");\n\nmy $worksheet1 = $workbook->addworksheet('Panes 1');\nmy $worksheet2 = $workbook->addworksheet('Panes 2');\nmy $worksheet3 = $workbook->addworksheet('Panes 3');\nmy $worksheet4 = $workbook->addworksheet('Panes 4');\n\n# Freeze panes\n$worksheet1->freezepanes(1, 0); # 1 row\n\n$worksheet2->freezepanes(0, 1); # 1 column\n$worksheet3->freezepanes(1, 1); # 1 row and column\n\n# Split panes.\n# The divisions must be specified in terms of row and column dimensions.\n# The default row height is 12.75 and the default column width is 8.43\n#\n$worksheet4->splitpanes(12.75, 8.43, 1, 1); # 1 row and column\n\n\n#######################################################################\n#\n# Set up some formatting and text to highlight the panes\n#\n\nmy $header = $workbook->addformat();\n$header->setcolor('white');\n$header->setalign('center');\n$header->setalign('vcenter');\n$header->setpattern();\n$header->setfgcolor('green');\n\nmy $center = $workbook->addformat();\n$center->setalign('center');\n\n\n#######################################################################\n#\n# Sheet 1\n#\n\n$worksheet1->setcolumn('A:I', 16);\n$worksheet1->setrow(0, 20);\n$worksheet1->setselection('C3');\n\nfor my $i (0..8){\n$worksheet1->write(0, $i, 'Scroll down', $header);\n}\n\nfor my $i (1..100){\nfor my $j (0..8){\n$worksheet1->write($i, $j, $i+1, $center);\n}\n}\n\n\n#######################################################################\n#\n# Sheet 2\n#\n\n$worksheet2->setcolumn('A:A', 16);\n$worksheet2->setselection('C3');\n\nfor my $i (0..49){\n$worksheet2->setrow($i, 15);\n$worksheet2->write($i, 0, 'Scroll right', $header);\n}\n\nfor my $i (0..49){\nfor my $j (1..25){\n$worksheet2->write($i, $j, $j, $center);\n}\n}\n\n\n#######################################################################\n#\n# Sheet 3\n#\n\n$worksheet3->setcolumn('A:Z', 16);\n$worksheet3->setselection('C3');\n\nfor my $i (1..25){\n$worksheet3->write(0, $i, 'Scroll down', $header);\n}\n\nfor my $i (1..49){\n$worksheet3->write($i, 0, 'Scroll right', $header);\n}\n\nfor my $i (1..49){\nfor my $j (1..25){\n$worksheet3->write($i, $j, $j, $center);\n}\n}\n\n\n#######################################################################\n#\n# Sheet 4\n#\n\n$worksheet4->setselection('C3');\n\nfor my $i (1..25){\n$worksheet4->write(0, $i, 'Scroll', $center);\n}\n\nfor my $i (1..49){\n$worksheet4->write($i, 0, 'Scroll', $center);\n}\n\nfor my $i (1..49){\nfor my $j (1..25){\n$worksheet4->write($i, $j, $j, $center);\n}\n}\n\nDownload this example:\n\n" }, { "name": "Example: properties.pl", "content": "An example of adding document properties to a Spreadsheet::WriteExcel file.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# An example of adding document properties to a Spreadsheet::WriteExcel file.\n#\n# reverse('(c)'), August 2008, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('properties.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n$workbook->setproperties(\ntitle => 'This is an example spreadsheet',\nsubject => 'With document properties',\nauthor => 'John McNamara',\nmanager => 'Dr. Heinz Doofenshmirtz ',\ncompany => 'of Wolves',\ncategory => 'Example spreadsheets',\nkeywords => 'Sample, Example, Properties',\ncomments => 'Created with Perl and Spreadsheet::WriteExcel',\n);\n\n\n$worksheet->setcolumn('A:A', 50);\n$worksheet->write('A1', 'Select File->Properties to see the file properties');\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: protection.pl", "content": "Example of cell locking and formula hiding in an Excel worksheet via the Spreadsheet::WriteExcel\nmodule.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n########################################################################\n#\n# Example of cell locking and formula hiding in an Excel worksheet via\n# the Spreadsheet::WriteExcel module.\n#\n# reverse('(c)'), August 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"protection.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n# Create some format objects\nmy $locked = $workbook->addformat(locked => 1);\nmy $unlocked = $workbook->addformat(locked => 0);\nmy $hidden = $workbook->addformat(hidden => 1);\n\n# Format the columns\n$worksheet->setcolumn('A:A', 42);\n$worksheet->setselection('B3:B3');\n\n# Protect the worksheet\n$worksheet->protect();\n\n# Examples of cell locking and hiding\n$worksheet->write('A1', 'Cell B1 is locked. It cannot be edited.');\n$worksheet->write('B1', '=1+2', $locked);\n\n$worksheet->write('A2', 'Cell B2 is unlocked. It can be edited.');\n$worksheet->write('B2', '=1+2', $unlocked);\n\n$worksheet->write('A3', \"Cell B3 is hidden. The formula isn't visible.\");\n$worksheet->write('B3', '=1+2', $hidden);\n\n$worksheet->write('A5', 'Use Menu->Tools->Protection->Unprotect Sheet');\n$worksheet->write('A6', 'to remove the worksheet protection. ');\n\nDownload this example:\n\n" }, { "name": "Example: repeat.pl", "content": "Example of writing repeated formulas.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n######################################################################\n#\n# Example of writing repeated formulas.\n#\n# reverse('(c)'), August 2002, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"repeat.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n\nmy $limit = 1000;\n\n# Write a column of numbers\nfor my $row (0..$limit) {\n$worksheet->write($row, 0, $row);\n}\n\n\n# Store a formula\nmy $formula = $worksheet->storeformula('=A1*5+4');\n\n\n# Write a column of formulas based on the stored formula\nfor my $row (0..$limit) {\n$worksheet->repeatformula($row, 1, $formula, undef,\nqr/^A1$/, 'A'.($row+1));\n}\n\n\n# Direct formula writing. As a speed comparison uncomment the\n# following and run the program again\n\n#for my $row (0..$limit) {\n# $worksheet->writeformula($row, 2, '=A'.($row+1).'*5+4');\n#}\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: righttoleft.pl", "content": "Example of how to change the default worksheet direction from left-to-right to right-to-left as\nrequired by some eastern verions of Excel.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# Example of how to change the default worksheet direction from\n# left-to-right to right-to-left as required by some eastern verions\n# of Excel.\n#\n# reverse('(c)'), January 2006, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"righttoleft.xls\");\nmy $worksheet1 = $workbook->addworksheet();\nmy $worksheet2 = $workbook->addworksheet();\n\n$worksheet2->righttoleft();\n\n$worksheet1->write(0, 0, 'Hello'); # A1, B1, C1, ...\n$worksheet2->write(0, 0, 'Hello'); # ..., C1, B1, A1\n\nDownload this example:\n\n" }, { "name": "Example: rowwrap.pl", "content": "Demonstrates how to wrap data from one worksheet onto another.\n\nExcel has a row limit of 65536 rows. Sometimes the amount of row data to be written to a file is\ngreater than this limit. In this case it is a useful technique to wrap the data from one\nworksheet onto the next so that we get something like the following:\n\nSheet1 Row 1 - 65536\nSheet2 Row 65537 - 131072\nSheet3 Row 131073 - ...\n\nIn order to achieve this we use a single worksheet reference and reinitialise it to point to a\nnew worksheet when required.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# Demonstrates how to wrap data from one worksheet onto another.\n#\n# Excel has a row limit of 65536 rows. Sometimes the amount of row data to be\n# written to a file is greater than this limit. In this case it is a useful\n# technique to wrap the data from one worksheet onto the next so that we get\n# something like the following:\n#\n# Sheet1 Row 1 - 65536\n# Sheet2 Row 65537 - 131072\n# Sheet3 Row 131073 - ...\n#\n# In order to achieve this we use a single worksheet reference and\n# reinitialise it to point to a new worksheet when required.\n#\n# reverse('(c)'), May 2006, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nmy $workbook = Spreadsheet::WriteExcel->new('rowwrap.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Worksheet formatting.\n$worksheet->setcolumn('A:A', 20);\n\n\n# For the sake of this example we will use a small row limit. In order to use\n# the entire row range set the $rowlimit to 65536.\nmy $rowlimit = 10;\nmy $row = 0;\n\nfor my $count (1 .. 2 * $rowlimit +10) {\n\n# When we hit the row limit we redirect the output\n# to a new worksheet and reset the row number.\nif ($row == $rowlimit) {\n$worksheet = $workbook->addworksheet();\n$row = 0;\n\n# Repeat any worksheet formatting.\n$worksheet->setcolumn('A:A', 20);\n}\n\n$worksheet->write($row, 0, \"This is row $count\");\n$row++;\n}\n\nDownload this example:\n\n" }, { "name": "Example: sales.pl", "content": "Example of a sales worksheet to demonstrate several different features. Also uses functions from\nthe Spreadsheet::WriteExcel::Utility module.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of a sales worksheet to demonstrate several different features.\n# Also uses functions from the L module.\n#\n# reverse('(c)'), October 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\nuse Spreadsheet::WriteExcel::Utility;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"sales.xls\");\nmy $worksheet = $workbook->addworksheet('May Sales');\n\n\n# Set up some formats\nmy %heading = (\nbold => 1,\npattern => 1,\nfgcolor => 19,\nborder => 1,\nalign => 'center',\n);\n\nmy %total = (\nbold => 1,\ntop => 1,\nnumformat => '$#,##0.00'\n);\n\nmy $heading = $workbook->addformat(%heading);\nmy $totalformat = $workbook->addformat(%total);\nmy $priceformat = $workbook->addformat(numformat => '$#,##0.00');\nmy $dateformat = $workbook->addformat(numformat => 'mmm d yyy');\n\n\n# Write the main headings\n$worksheet->freezepanes(1); # Freeze the first row\n$worksheet->write('A1', 'Item', $heading);\n$worksheet->write('B1', 'Quantity', $heading);\n$worksheet->write('C1', 'Price', $heading);\n$worksheet->write('D1', 'Total', $heading);\n$worksheet->write('E1', 'Date', $heading);\n\n# Set the column widths\n$worksheet->setcolumn('A:A', 25);\n$worksheet->setcolumn('B:B', 10);\n$worksheet->setcolumn('C:E', 16);\n\n\n# Extract the sales data from the DATA section at the end of the file.\n# In reality this information would probably come from a database\nmy @sales;\n\nforeach my $line () {\nchomp $line;\nnext if $line eq '';\n# Simple-minded processing of CSV data. Refer to the Text::CSVXS\n# and Text::xSV modules for a more complete CSV handling.\nmy @items = split /,/, $line;\npush @sales, \\@items;\n}\n\n\n# Write out the items from each row\nmy $row = 1;\nforeach my $sale (@sales) {\n\n$worksheet->write($row, 0, @$sale[0]);\n$worksheet->write($row, 1, @$sale[1]);\n$worksheet->write($row, 2, @$sale[2], $priceformat);\n\n# Create a formula like '=B2*C2'\nmy $formula = '='\n. xlrowcoltocell($row, 1)\n. \"*\"\n. xlrowcoltocell($row, 2);\n\n$worksheet->write($row, 3, $formula, $priceformat);\n\n# Parse the date\nmy $date = xldecodedateUS(@$sale[3]);\n$worksheet->write($row, 4, $date, $dateformat);\n$row++;\n}\n\n# Create a formula to sum the totals, like '=SUM(D2:D6)'\nmy $total = '=SUM(D2:'\n. xlrowcoltocell($row-1, 3)\n. \")\";\n\n$worksheet->write($row, 3, $total, $totalformat);\n\n\n\nDATA\n586 card,20,125.50,5/12/01\nFlat Screen Monitor,1,1300.00,5/12/01\n64 MB dimms,45,49.99,5/13/01\n15 GB HD,12,300.00,5/13/01\nSpeakers (pair),5,15.50,5/14/01\n\nDownload this example:\n\n" }, { "name": "Example: sendmail.pl", "content": "Example of how to use Mail::Sender to send a Spreadsheet::WriteExcel Excel file as an\nattachment.\n\nThe main thing is to ensure that you close() the Worbook before you send it.\n\nSee the Mail::Sender module for further details.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use Mail::Sender to send a Spreadsheet::WriteExcel Excel\n# file as an attachment.\n#\n# The main thing is to ensure that you close() the Worbook before you send it.\n#\n# See the L module for further details.\n#\n# reverse('(c)'), August 2002, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\nuse Mail::Sender;\n\n# Create an Excel file\nmy $workbook = Spreadsheet::WriteExcel->new(\"sendmail.xls\");\nmy $worksheet = $workbook->addworksheet;\n\n$worksheet->write('A1', \"Hello World!\");\n\n$workbook->close(); # Must close before sending\n\n\n\n# Send the file. Change all variables to suit\nmy $sender = new Mail::Sender\n{\nsmtp => '123.123.123.123',\nfrom => 'Someone'\n};\n\n$sender->MailFile(\n{\nto => 'another@mail.com',\nsubject => 'Excel file',\nmsg => \"Here is the data.\\n\",\nfile => 'mail.xls',\n});\n\nDownload this example:\n\n" }, { "name": "Example: statsext.pl", "content": "Example of formatting using the Spreadsheet::WriteExcel module\n\nThis is a simple example of how to use functions that reference cells in other worksheets within\nthe same workbook.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of formatting using the Spreadsheet::WriteExcel module\n#\n# This is a simple example of how to use functions that reference cells in\n# other worksheets within the same workbook.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"statsext.xls\");\nmy $worksheet1 = $workbook->addworksheet('Test results');\nmy $worksheet2 = $workbook->addworksheet('Data');\n\n# Set the column width for columns 1\n$worksheet1->setcolumn('A:A', 20);\n\n\n# Create a format for the headings\nmy $heading = $workbook->addformat();\n$heading->setbold();\n\n# Create a numerical format\nmy $numformat = $workbook->addformat();\n$numformat->setnumformat('0.00');\n\n\n\n\n# Write some statistical functions\n$worksheet1->write('A1', 'Count', $heading);\n$worksheet1->write('B1', '=COUNT(Data!B2:B9)');\n\n$worksheet1->write('A2', 'Sum', $heading);\n$worksheet1->write('B2', '=SUM(Data!B2:B9)');\n\n$worksheet1->write('A3', 'Average', $heading);\n$worksheet1->write('B3', '=AVERAGE(Data!B2:B9)');\n\n$worksheet1->write('A4', 'Min', $heading);\n$worksheet1->write('B4', '=MIN(Data!B2:B9)');\n\n$worksheet1->write('A5', 'Max', $heading);\n$worksheet1->write('B5', '=MAX(Data!B2:B9)');\n\n$worksheet1->write('A6', 'Standard Deviation', $heading);\n$worksheet1->write('B6', '=STDEV(Data!B2:B9)');\n\n$worksheet1->write('A7', 'Kurtosis', $heading);\n$worksheet1->write('B7', '=KURT(Data!B2:B9)');\n\n\n# Write the sample data\n$worksheet2->write('A1', 'Sample', $heading);\n$worksheet2->write('A2', 1);\n$worksheet2->write('A3', 2);\n$worksheet2->write('A4', 3);\n$worksheet2->write('A5', 4);\n$worksheet2->write('A6', 5);\n$worksheet2->write('A7', 6);\n$worksheet2->write('A8', 7);\n$worksheet2->write('A9', 8);\n\n$worksheet2->write('B1', 'Length', $heading);\n$worksheet2->write('B2', 25.4, $numformat);\n$worksheet2->write('B3', 25.4, $numformat);\n$worksheet2->write('B4', 24.8, $numformat);\n$worksheet2->write('B5', 25.0, $numformat);\n$worksheet2->write('B6', 25.3, $numformat);\n$worksheet2->write('B7', 24.9, $numformat);\n$worksheet2->write('B8', 25.2, $numformat);\n$worksheet2->write('B9', 24.8, $numformat);\n\nDownload this example:\n\n" }, { "name": "Example: stocks.pl", "content": "Example of formatting using the Spreadsheet::WriteExcel module\n\nThis example shows how to use a conditional numerical format with colours to indicate if a share\nprice has gone up or down.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of formatting using the Spreadsheet::WriteExcel module\n#\n# This example shows how to use a conditional numerical format\n# with colours to indicate if a share price has gone up or down.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"stocks.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n# Set the column width for columns 1, 2, 3 and 4\n$worksheet->setcolumn(0, 3, 15);\n\n\n# Create a format for the column headings\nmy $header = $workbook->addformat();\n$header->setbold();\n$header->setsize(12);\n$header->setcolor('blue');\n\n\n# Create a format for the stock price\nmy $fprice = $workbook->addformat();\n$fprice->setalign('left');\n$fprice->setnumformat('$0.00');\n\n\n# Create a format for the stock volume\nmy $fvolume = $workbook->addformat();\n$fvolume->setalign('left');\n$fvolume->setnumformat('#,##0');\n\n\n# Create a format for the price change. This is an example of a conditional\n# format. The number is formatted as a percentage. If it is positive it is\n# formatted in green, if it is negative it is formatted in red and if it is\n# zero it is formatted as the default font colour (in this case black).\n# Note: the [Green] format produces an unappealing lime green. Try\n# [Color 10] instead for a dark green.\n#\nmy $fchange = $workbook->addformat();\n$fchange->setalign('left');\n$fchange->setnumformat('[Green]0.0%;[Red]-0.0%;0.0%');\n\n\n# Write out the data\n$worksheet->write(0, 0, 'Company', $header);\n$worksheet->write(0, 1, 'Price', $header);\n$worksheet->write(0, 2, 'Volume', $header);\n$worksheet->write(0, 3, 'Change', $header);\n\n$worksheet->write(1, 0, 'Damage Inc.' );\n$worksheet->write(1, 1, 30.25, $fprice); # $30.25\n$worksheet->write(1, 2, 1234567, $fvolume); # 1,234,567\n$worksheet->write(1, 3, 0.085, $fchange); # 8.5% in green\n\n$worksheet->write(2, 0, 'Dump Corp.' );\n$worksheet->write(2, 1, 1.56, $fprice); # $1.56\n$worksheet->write(2, 2, 7564, $fvolume); # 7,564\n$worksheet->write(2, 3, -0.015, $fchange); # -1.5% in red\n\n$worksheet->write(3, 0, 'Rev Ltd.' );\n$worksheet->write(3, 1, 0.13, $fprice); # $0.13\n$worksheet->write(3, 2, 321, $fvolume); # 321\n$worksheet->write(3, 3, 0, $fchange); # 0 in the font color (black)\n\nDownload this example:\n\n" }, { "name": "Example: tabcolors.pl", "content": "Example of how to set Excel worksheet tab colours.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# Example of how to set Excel worksheet tab colours.\n#\n# reverse('(c)'), May 2006, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new('tabcolors.xls');\n\nmy $worksheet1 = $workbook->addworksheet();\nmy $worksheet2 = $workbook->addworksheet();\nmy $worksheet3 = $workbook->addworksheet();\nmy $worksheet4 = $workbook->addworksheet();\n\n# Worksheet1 will have the default tab colour.\n$worksheet2->settabcolor('red');\n$worksheet3->settabcolor('green');\n$worksheet4->settabcolor(0x35); # Orange\n\nDownload this example:\n\n" }, { "name": "Example: textwrap.pl", "content": "Example of formatting using the Spreadsheet::WriteExcel module\n\nThis example shows how to wrap text in a cell. There are two alternatives, vertical\njustification and text wrap.\n\nWith vertical justification the text is wrapped automatically to fit the column width. With text\nwrap you must specify a newline with an embedded \\n.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of formatting using the Spreadsheet::WriteExcel module\n#\n# This example shows how to wrap text in a cell. There are two alternatives,\n# vertical justification and text wrap.\n#\n# With vertical justification the text is wrapped automatically to fit the\n# column width. With text wrap you must specify a newline with an embedded \\n.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"textwrap.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n# Set the column width for columns 1, 2 and 3\n$worksheet->setcolumn(1, 1, 24);\n$worksheet->setcolumn(2, 2, 34);\n$worksheet->setcolumn(3, 3, 34);\n\n# Set the row height for rows 1, 4, and 6. The height of row 2 will adjust\n# automatically to fit the text.\n#\n$worksheet->setrow(0, 30);\n$worksheet->setrow(3, 40);\n$worksheet->setrow(5, 80);\n\n\n# No newlines\nmy $str1 = \"For whatever we lose (like a you or a me) \";\n$str1 .= \"it's always ourselves we find in the sea\";\n\n# Embedded newlines\nmy $str2 = \"For whatever we lose\\n(like a you or a me)\\n\";\n$str2 .= \"it's always ourselves\\nwe find in the sea\";\n\n\n# Create a format for the column headings\nmy $header = $workbook->addformat();\n$header->setbold();\n$header->setfont(\"Courier New\");\n$header->setalign('center');\n$header->setalign('vcenter');\n\n# Create a \"vertical justification\" format\nmy $format1 = $workbook->addformat();\n$format1->setalign('vjustify');\n\n# Create a \"text wrap\" format\nmy $format2 = $workbook->addformat();\n$format2->settextwrap();\n\n# Write the headers\n$worksheet->write(0, 1, \"setalign('vjustify')\", $header);\n$worksheet->write(0, 2, \"setalign('vjustify')\", $header);\n$worksheet->write(0, 3, \"settextwrap()\", $header);\n\n# Write some examples\n$worksheet->write(1, 1, $str1, $format1);\n$worksheet->write(1, 2, $str1, $format1);\n$worksheet->write(1, 3, $str2, $format2);\n\n$worksheet->write(3, 1, $str1, $format1);\n$worksheet->write(3, 2, $str1, $format1);\n$worksheet->write(3, 3, $str2, $format2);\n\n$worksheet->write(5, 1, $str1, $format1);\n$worksheet->write(5, 2, $str1, $format1);\n$worksheet->write(5, 3, $str2, $format2);\n\nDownload this example:\n\n" }, { "name": "Example: win32ole.pl", "content": "This is a simple example of how to create an Excel file using the Win32::OLE module for the sake\nof comparison.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# This is a simple example of how to create an Excel file using the\n# Win32::OLE module for the sake of comparison.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Cwd;\nuse Win32::OLE;\nuse Win32::OLE::Const 'Microsoft Excel';\n\n\nmy $application = Win32::OLE->new(\"Excel.Application\");\nmy $workbook = $application->Workbooks->Add;\nmy $worksheet = $workbook->Worksheets(1);\n\n$worksheet->Cells(1,1)->{Value} = \"Hello World\";\n$worksheet->Cells(2,1)->{Value} = \"One\";\n$worksheet->Cells(3,1)->{Value} = \"Two\";\n$worksheet->Cells(4,1)->{Value} = 3;\n$worksheet->Cells(5,1)->{Value} = 4.0000001;\n\n# Add some formatting\n$worksheet->Cells(1,1)->Font->{Bold} = \"True\";\n$worksheet->Cells(1,1)->Font->{Size} = 16;\n$worksheet->Cells(1,1)->Font->{ColorIndex} = 3;\n$worksheet->Columns(\"A:A\")->{ColumnWidth} = 25;\n\n# Write a hyperlink\nmy $range = $worksheet->Range(\"A7:A7\");\n$worksheet->Hyperlinks->Add({ Anchor => $range, Address => \"http://www.perl.com/\"});\n\n# Get current directory using Cwd.pm\nmy $dir = cwd();\n\n$workbook->SaveAs({\nFileName => $dir . '/win32ole.xls',\nFileFormat => xlNormal,\n});\n$workbook->Close;\n\nDownload this example:\n\n" }, { "name": "Example: writearrays.pl", "content": "Example of how to use the Spreadsheet::WriteExcel module to write 1D and 2D arrays of data.\n\nTo find out more about array references refer(!!) to the perlref and perlreftut manpages. To\nfind out more about 2D arrays or \"list of lists\" refer to the perllol manpage.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n#######################################################################\n#\n# Example of how to use the Spreadsheet::WriteExcel module to\n# write 1D and 2D arrays of data.\n#\n# To find out more about array references refer(!!) to the perlref and\n# perlreftut manpages. To find out more about 2D arrays or \"list of\n# lists\" refer to the perllol manpage.\n#\n# reverse('(c)'), March 2002, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"writearrays.xls\");\nmy $worksheet1 = $workbook->addworksheet('Example 1');\nmy $worksheet2 = $workbook->addworksheet('Example 2');\nmy $worksheet3 = $workbook->addworksheet('Example 3');\nmy $worksheet4 = $workbook->addworksheet('Example 4');\nmy $worksheet5 = $workbook->addworksheet('Example 5');\nmy $worksheet6 = $workbook->addworksheet('Example 6');\nmy $worksheet7 = $workbook->addworksheet('Example 7');\nmy $worksheet8 = $workbook->addworksheet('Example 8');\n\nmy $format = $workbook->addformat(color => 'red', bold => 1);\n\n\n# Data arrays used in the following examples.\n# undef values are written as blank cells (with format if specified).\n#\nmy @array = ( 'one', 'two', undef, 'four' );\n\nmy @array2d = (\n['maggie', 'milly', 'molly', 'may' ],\n[13, 14, 15, 16 ],\n['shell', 'star', 'crab', 'stone'],\n);\n\n\n# 1. Write a row of data using an array reference.\n$worksheet1->write('A1', \\@array);\n\n# 2. Same as 1. above using an anonymous array ref.\n$worksheet2->write('A1', [ @array ]);\n\n# 3. Write a row of data using an explicit writerow() method call.\n# This is the same as calling write() in Ex. 1 above.\n#\n$worksheet3->writerow('A1', \\@array);\n\n# 4. Write a column of data using the writecol() method call.\n$worksheet4->writecol('A1', \\@array);\n\n# 5. Write a column of data using a ref to an array ref, i.e. a 2D array.\n$worksheet5->write('A1', [ \\@array ]);\n\n# 6. Write a 2D array in col-row order.\n$worksheet6->write('A1', \\@array2d);\n\n# 7. Write a 2D array in row-col order.\n$worksheet7->writecol('A1', \\@array2d);\n\n# 8. Write a row of data with formatting. The blank cell is also formatted.\n$worksheet8->write('A1', \\@array, $format);\n\nDownload this example:\n\n" }, { "name": "Example: writehandler1.pl", "content": "Example of how to add a user defined data handler to the Spreadsheet:: WriteExcel write()\nmethod.\n\nThe following example shows how to add a handler for a 7 digit ID number.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to add a user defined data handler to the Spreadsheet::\n# WriteExcel write() method.\n#\n# The following example shows how to add a handler for a 7 digit ID number.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"writehandler1.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n\n###############################################################################\n#\n# Add a handler for 7 digit id numbers. This is useful when you want a string\n# such as 0000001 written as a string instead of a number and thus preserve\n# the leading zeroes.\n#\n# Note: you can get the same effect using the keepleadingzeros() method but\n# this serves as a simple example.\n#\n$worksheet->addwritehandler(qr[^\\d{7}$], \\&writemyid);\n\n\n###############################################################################\n#\n# The following function processes the data when a match is found.\n#\nsub writemyid {\n\nmy $worksheet = shift;\n\nreturn $worksheet->writestring(@);\n}\n\n\n# This format maintains the cell as text even if it is edited.\nmy $idformat = $workbook->addformat(numformat => '@');\n\n\n# Write some numbers in the user defined format\n$worksheet->write('A1', '0000000', $idformat);\n$worksheet->write('A2', '0000001', $idformat);\n$worksheet->write('A3', '0004000', $idformat);\n$worksheet->write('A4', '1234567', $idformat);\n\n# Write some numbers that don't match the defined format\n$worksheet->write('A6', '000000', $idformat);\n$worksheet->write('A7', '000001', $idformat);\n$worksheet->write('A8', '004000', $idformat);\n$worksheet->write('A9', '123456', $idformat);\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: writehandler2.pl", "content": "Example of how to add a user defined data handler to the Spreadsheet:: WriteExcel write()\nmethod.\n\nThe following example shows how to add a handler for a 7 digit ID number. It adds an additional\nconstraint to the writehandler1.pl in that it only filters data that isn't in the third column.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to add a user defined data handler to the Spreadsheet::\n# WriteExcel write() method.\n#\n# The following example shows how to add a handler for a 7 digit ID number.\n# It adds an additional constraint to the writehandler1.pl in that it only\n# filters data that isn't in the third column.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"writehandler2.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n\n###############################################################################\n#\n# Add a handler for 7 digit id numbers. This is useful when you want a string\n# such as 0000001 written as a string instead of a number and thus preserve\n# the leading zeroes.\n#\n# Note: you can get the same effect using the keepleadingzeros() method but\n# this serves as a simple example.\n#\n$worksheet->addwritehandler(qr[^\\d{7}$], \\&writemyid);\n\n\n###############################################################################\n#\n# The following function processes the data when a match is found. The handler\n# is set up so that it only filters data if it is in the third column.\n#\nsub writemyid {\n\nmy $worksheet = shift;\nmy $col = $[1];\n\n# col is zero based\nif ($col != 2) {\nreturn $worksheet->writestring(@);\n}\nelse {\n# Reject the match and return control to write()\nreturn undef;\n}\n\n}\n\n\n# This format maintains the cell as text even if it is edited.\nmy $idformat = $workbook->addformat(numformat => '@');\n\n\n# Write some numbers in the user defined format\n$worksheet->write('A1', '0000000', $idformat);\n$worksheet->write('B1', '0000001', $idformat);\n$worksheet->write('C1', '0000002', $idformat);\n$worksheet->write('D1', '0000003', $idformat);\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: writehandler3.pl", "content": "Example of how to add a user defined data handler to the Spreadsheet:: WriteExcel write()\nmethod.\n\nThe following example shows how to add a handler for dates in a specific format.\n\nSee writehandler4.pl for a more rigorous example with error handling.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to add a user defined data handler to the Spreadsheet::\n# WriteExcel write() method.\n#\n# The following example shows how to add a handler for dates in a specific\n# format.\n#\n# See writehandler4.pl for a more rigorous example with error handling.\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"writehandler3.xls\");\nmy $worksheet = $workbook->addworksheet();\nmy $dateformat = $workbook->addformat(numformat => 'dd/mm/yy');\n\n\n###############################################################################\n#\n# Add a handler to match dates in the following format: d/m/yyyy\n#\n# The day and month can be single or double digits.\n#\n$worksheet->addwritehandler(qr[^\\d{1,2}/\\d{1,2}/\\d{4}$], \\&writemydate);\n\n\n###############################################################################\n#\n# The following function processes the data when a match is found.\n# See writehandler4.pl for a more rigorous example with error handling.\n#\nsub writemydate {\n\nmy $worksheet = shift;\nmy @args = @;\n\nmy $token = $args[2];\n$token =~ qr[^(\\d{1,2})/(\\d{1,2})/(\\d{4})$];\n\n# Change to the date format required by writedatetime().\nmy $date = sprintf \"%4d-%02d-%02dT\", $3, $2, $1;\n\n$args[2] = $date;\n\nreturn $worksheet->writedatetime(@args);\n}\n\n\n# Write some dates in the user defined format\n$worksheet->write('A1', '22/12/2004', $dateformat);\n$worksheet->write('A2', '1/1/1995', $dateformat);\n$worksheet->write('A3', '01/01/1995', $dateformat);\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: writehandler4.pl", "content": "Example of how to add a user defined data handler to the Spreadsheet:: WriteExcel write()\nmethod.\n\nThe following example shows how to add a handler for dates in a specific format.\n\nThis is a more rigorous version of writehandler3.pl.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to add a user defined data handler to the Spreadsheet::\n# WriteExcel write() method.\n#\n# The following example shows how to add a handler for dates in a specific\n# format.\n#\n# This is a more rigorous version of writehandler3.pl.\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"writehandler4.xls\");\nmy $worksheet = $workbook->addworksheet();\nmy $dateformat = $workbook->addformat(numformat => 'dd/mm/yy');\n\n\n###############################################################################\n#\n# Add a handler to match dates in the following formats: d/m/yy, d/m/yyyy\n#\n# The day and month can be single or double digits and the year can be 2 or 4\n# digits.\n#\n$worksheet->addwritehandler(qr[^\\d{1,2}/\\d{1,2}/\\d{2,4}$], \\&writemydate);\n\n\n###############################################################################\n#\n# The following function processes the data when a match is found.\n#\nsub writemydate {\n\nmy $worksheet = shift;\nmy @args = @;\n\nmy $token = $args[2];\n\nif ($token =~ qr[^(\\d{1,2})/(\\d{1,2})/(\\d{2,4})$]) {\n\nmy $day = $1;\nmy $mon = $2;\nmy $year = $3;\n\n# Use a window for 2 digit dates. This will keep some ragged Perl\n# programmer employed in thirty years time. :-)\nif (length $year == 2) {\nif ($year < 50) {\n$year += 2000;\n}\nelse {\n$year += 1900;\n}\n}\n\nmy $date = sprintf \"%4d-%02d-%02dT\", $year, $mon, $day;\n\n# Convert the ISO ISO8601 style string to an Excel date\n$date = $worksheet->convertdatetime($date);\n\nif (defined $date) {\n# Date was valid\n$args[2] = $date;\nreturn $worksheet->writenumber(@args);\n}\nelse {\n# Not a valid date therefore write as a string\nreturn $worksheet->writestring(@args);\n}\n}\nelse {\n# Shouldn't happen if the same match is used in the re and sub.\nreturn undef;\n}\n}\n\n\n# Write some dates in the user defined format\n$worksheet->write('A1', '22/12/2004', $dateformat);\n$worksheet->write('A2', '22/12/04', $dateformat);\n$worksheet->write('A3', '2/12/04', $dateformat);\n$worksheet->write('A4', '2/5/04', $dateformat);\n$worksheet->write('A5', '2/5/95', $dateformat);\n$worksheet->write('A6', '2/5/1995', $dateformat);\n\n# Some erroneous dates\n$worksheet->write('A8', '2/5/1895', $dateformat); # Date out of Excel range\n$worksheet->write('A9', '29/2/2003', $dateformat); # Invalid leap day\n$worksheet->write('A10','50/50/50', $dateformat); # Matches but isn't a date\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: writetoscalar.pl", "content": "An example of writing an Excel file to a Perl scalar using Spreadsheet:: WriteExcel and the new\nfeatures of perl 5.8.\n\nFor an examples of how to write to a scalar in versions prior to perl 5.8 see the filehandle.pl\nprogram and IO:Scalar.\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# An example of writing an Excel file to a Perl scalar using Spreadsheet::\n# WriteExcel and the new features of perl 5.8.\n#\n# For an examples of how to write to a scalar in versions prior to perl 5.8\n# see the filehandle.pl program and IO:Scalar.\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nrequire 5.008;\n\n\n# Use perl 5.8's feature of using a scalar as a filehandle.\nmy $fh;\nmy $str = '';\nopen $fh, '>', \\$str or die \"Failed to open filehandle: $!\";\n\n\n# Or replace the previous three lines with this:\n# open my $fh, '>', \\my $str or die \"Failed to open filehandle: $!\";\n\n\n# Spreadsheet::WriteExce accepts filehandle as well as file names.\nmy $workbook = Spreadsheet::WriteExcel->new($fh);\nmy $worksheet = $workbook->addworksheet();\n\n$worksheet->write(0, 0, \"Hi Excel!\");\n\n$workbook->close();\n\n\n# The Excel file in now in $str. Remember to binmode() the output\n# filehandle before printing it.\nbinmode STDOUT;\nprint $str;\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodeutf16.pl", "content": "A simple example of writing some Unicode text with Spreadsheet::WriteExcel.\n\nThis example shows UTF16 encoding. With perl 5.8 it is also possible to use utf8 without\nmodification.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of writing some Unicode text with Spreadsheet::WriteExcel.\n#\n# This example shows UTF16 encoding. With perl 5.8 it is also possible to use\n# utf8 without modification.\n#\n# reverse('(c)'), May 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new('unicodeutf16.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Write the Unicode smiley face (with increased font for legibility)\nmy $smiley = pack \"n\", 0x263a;\nmy $bigfont = $workbook->addformat(size => 40);\n\n$worksheet->writeutf16bestring('A3', $smiley, $bigfont);\n\n\n# Write a phrase in Cyrillic\nmy $unistr = pack \"H*\", \"042d0442043e002004440440043004370430002004\".\n\"3d043000200440044304410441043a043e043c0021\";\n\n$worksheet->writeutf16bestring('A5', $unistr);\n\n\n$worksheet->writeutf16bestring('A7', pack \"H*\", \"0074006500730074\");\n\n\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodeutf16japan.pl", "content": "A simple example of writing some Unicode text with Spreadsheet::WriteExcel.\n\nThis creates an Excel file with the word Nippon in 3 character sets.\n\nThis example shows UTF16 encoding. With perl 5.8 it is also possible to use utf8 without\nmodification.\n\nSee also the unicode2022jp.pl and unicodeshiftjis.pl examples.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of writing some Unicode text with Spreadsheet::WriteExcel.\n#\n# This creates an Excel file with the word Nippon in 3 character sets.\n#\n# This example shows UTF16 encoding. With perl 5.8 it is also possible to use\n# utf8 without modification.\n#\n# See also the unicode2022jp.pl and unicodeshiftjis.pl examples.\n#\n# reverse('(c)'), May 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new('unicodeutf16japan.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Set a Unicode font.\nmy $unifont = $workbook->addformat(font => 'Arial Unicode MS');\n\n\n# Create some UTF-16BE Unicode text.\nmy $kanji = pack 'n*', 0x65e5, 0x672c;\nmy $katakana = pack 'n*', 0xff86, 0xff8e, 0xff9d;\nmy $hiragana = pack 'n*', 0x306b, 0x307b, 0x3093;\n\n\n\n$worksheet->writeutf16bestring('A1', $kanji, $unifont);\n$worksheet->writeutf16bestring('A2', $katakana, $unifont);\n$worksheet->writeutf16bestring('A3', $hiragana, $unifont);\n\n\n$worksheet->write('B1', 'Kanji');\n$worksheet->write('B2', 'Katakana');\n$worksheet->write('B3', 'Hiragana');\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodecyrillic.pl", "content": "A simple example of writing some Russian cyrillic text using Spreadsheet::WriteExcel and perl\n5.8.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of writing some Russian cyrillic text using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# reverse('(c)'), March 2005, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\n# In this example we generate utf8 strings from character data but in a\n# real application we would expect them to come from an external source.\n#\n\n\n# Create a Russian worksheet name in utf8.\nmy $sheet = pack \"U*\", 0x0421, 0x0442, 0x0440, 0x0430, 0x043D, 0x0438,\n0x0446, 0x0430;\n\n\n# Create a Russian string.\nmy $str = pack \"U*\", 0x0417, 0x0434, 0x0440, 0x0430, 0x0432, 0x0441,\n0x0442, 0x0432, 0x0443, 0x0439, 0x0020, 0x041C,\n0x0438, 0x0440, 0x0021;\n\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicodecyrillic.xls\");\nmy $worksheet = $workbook->addworksheet($sheet . '1');\n\n$worksheet->setcolumn('A:A', 18);\n$worksheet->write('A1', $str);\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodelist.pl", "content": "A simple example using Spreadsheet::WriteExcel to display all available Unicode characters in a\nfont.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example using Spreadsheet::WriteExcel to display all available\n# Unicode characters in a font.\n#\n# reverse('(c)'), May 2004, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new('unicodelist.xls');\nmy $worksheet = $workbook->addworksheet();\n\n\n# Set a Unicode font.\nmy $unifont = $workbook->addformat(font => 'Arial Unicode MS');\n\n# Ascii font for labels.\nmy $courier = $workbook->addformat(font => 'Courier New');\n\n\nmy $char = 0;\n\n# Loop through all 32768 UTF-16BE characters.\n#\nfor my $row (0 .. 2 12 -1) {\nfor my $col (0 .. 31) {\n\nlast if $char == 0xffff;\n\nif ($col % 2 == 0){\n$worksheet->writestring($row, $col,\nsprintf('0x%04X', $char), $courier);\n}\nelse {\n$worksheet->writeutf16bestring($row, $col,\npack('n', $char++), $unifont);\n}\n}\n}\n\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicode2022jp.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Japanese from a file with ISO-2022-JP encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Japanese from a file with ISO-2022-JP\n# encoded text.\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicode2022jp.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicode2022jp.txt';\n\nopen FH, '<:encoding(iso-2022-jp)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicode885911.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Thai from a file with ISO-8859-11 encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Thai from a file with ISO-8859-11 encoded text.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicode885911.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicode885911.txt';\n\nopen FH, '<:encoding(iso-8859-11)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicode88597.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Greek from a file with ISO-8859-7 encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Greek from a file with ISO-8859-7 encoded text.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicode88597.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicode88597.txt';\n\nopen FH, '<:encoding(iso-8859-7)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodebig5.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Chinese from a file with BIG5 encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Chinese from a file with BIG5 encoded text.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicodebig5.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 80);\n\n\nmy $file = 'unicodebig5.txt';\n\nopen FH, '<:encoding(big5)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodecp1251.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Russian from a file with CP1251 encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Russian from a file with CP1251 encoded text.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicodecp1251.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicodecp1251.txt';\n\nopen FH, '<:encoding(cp1251)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodecp1256.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Arabic text from a CP-1256 encoded file.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Arabic text from a CP-1256 encoded file.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicodecp1256.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicodecp1256.txt';\n\nopen FH, '<:encoding(cp1256)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodekoi8r.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Russian from a file with KOI8-R encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Russian from a file with KOI8-R encoded text.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicodekoi8r.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicodekoi8r.txt';\n\nopen FH, '<:encoding(koi8-r)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodepolishutf8.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Polish from a file with UTF8 encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Polish from a file with UTF8 encoded text.\n#\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicodepolishutf8.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicodepolishutf8.txt';\n\nopen FH, '<:encoding(utf8)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: unicodeshiftjis.pl", "content": "A simple example of converting some Unicode text to an Excel file using Spreadsheet::WriteExcel\nand perl 5.8.\n\nThis example generates some Japenese text from a file with Shift-JIS encoded text.\n\nSource code for this example:\n\n#!/usr/bin/perl -w\n\n##############################################################################\n#\n# A simple example of converting some Unicode text to an Excel file using\n# Spreadsheet::WriteExcel and perl 5.8.\n#\n# This example generates some Japenese text from a file with Shift-JIS\n# encoded text.\n#\n# reverse('(c)'), September 2004, John McNamara, jmcnamara@cpan.org\n#\n\n\n\n# Perl 5.8 or later is required for proper utf8 handling. For older perl\n# versions you should use UTF16 and the writeutf16bestring() method.\n# See the writeutf16bestring section of the Spreadsheet::WriteExcel docs.\n#\nrequire 5.008;\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"unicodeshiftjis.xls\");\nmy $worksheet = $workbook->addworksheet();\n$worksheet->setcolumn('A:A', 50);\n\n\nmy $file = 'unicodeshiftjis.txt';\n\nopen FH, '<:encoding(shiftjis)', $file or die \"Couldn't open $file: $!\\n\";\n\nmy $row = 0;\n\nwhile () {\nnext if /^#/; # Ignore the comments in the sample file.\nchomp;\n$worksheet->write($row++, 0, $);\n}\n\n\nEND\n\nDownload this example:\n\n" }, { "name": "Example: csv2xls.pl", "content": "Example of how to use the WriteExcel module\n\nSimple program to convert a CSV comma-separated value file to an Excel file. This is more or\nless an non-op since Excel can read CSV files. The program uses Text::CSVXS to parse the CSV.\n\nUsage: csv2xls.pl file.csv newfile.xls\n\nNOTE: This is only a simple conversion utility for illustrative purposes. For converting a CSV\nor Tab separated or any other type of delimited text file to Excel I recommend the more rigorous\ncsv2xls program that is part of H.Merijn Brand's Text::CSVXS module distro.\n\nSee the examples/csv2xls link here: \n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the WriteExcel module\n#\n# Simple program to convert a CSV comma-separated value file to an Excel file.\n# This is more or less an non-op since Excel can read CSV files.\n# The program uses Text::CSVXS to parse the CSV.\n#\n# Usage: csv2xls.pl file.csv newfile.xls\n#\n#\n# NOTE: This is only a simple conversion utility for illustrative purposes.\n# For converting a CSV or Tab separated or any other type of delimited\n# text file to Excel I recommend the more rigorous csv2xls program that is\n# part of H.Merijn Brand's Text::CSVXS module distro.\n#\n# See the examples/csv2xls link here:\n# L\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\nuse Text::CSVXS;\n\n# Check for valid number of arguments\nif (($#ARGV < 1) || ($#ARGV > 2)) {\ndie(\"Usage: csv2xls csvfile.txt newfile.xls\\n\");\n};\n\n# Open the Comma Separated Variable file\nopen (CSVFILE, $ARGV[0]) or die \"$ARGV[0]: $!\";\n\n# Create a new Excel workbook\nmy $workbook = Spreadsheet::WriteExcel->new($ARGV[1]);\nmy $worksheet = $workbook->addworksheet();\n\n# Create a new CSV parsing object\nmy $csv = Text::CSVXS->new;\n\n# Row and column are zero indexed\nmy $row = 0;\n\n\nwhile () {\nif ($csv->parse($)) {\nmy @Fld = $csv->fields;\n\nmy $col = 0;\nforeach my $token (@Fld) {\n$worksheet->write($row, $col, $token);\n$col++;\n}\n$row++;\n}\nelse {\nmy $err = $csv->errorinput;\nprint \"Text::CSVXS parse() failed on argument: \", $err, \"\\n\";\n}\n}\n\nDownload this example:\n\n" }, { "name": "Example: tab2xls.pl", "content": "Example of how to use the WriteExcel module\n\nThe following converts a tab separated file into an Excel file\n\nUsage: tab2xls.pl tabfile.txt newfile.xls\n\nNOTE: This is only a simple conversion utility for illustrative purposes. For converting a CSV\nor Tab separated or any other type of delimited text file to Excel I recommend the more rigorous\ncsv2xls program that is part of H.Merijn Brand's Text::CSVXS module distro.\n\nSee the examples/csv2xls link here: \n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to use the WriteExcel module\n#\n# The following converts a tab separated file into an Excel file\n#\n# Usage: tab2xls.pl tabfile.txt newfile.xls\n#\n#\n# NOTE: This is only a simple conversion utility for illustrative purposes.\n# For converting a CSV or Tab separated or any other type of delimited\n# text file to Excel I recommend the more rigorous csv2xls program that is\n# part of H.Merijn Brand's Text::CSVXS module distro.\n#\n# See the examples/csv2xls link here:\n# L\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\n# Check for valid number of arguments\nif (($#ARGV < 1) || ($#ARGV > 2)) {\ndie(\"Usage: tab2xls tabfile.txt newfile.xls\\n\");\n};\n\n\n# Open the tab delimited file\nopen (TABFILE, $ARGV[0]) or die \"$ARGV[0]: $!\";\n\n\n# Create a new Excel workbook\nmy $workbook = Spreadsheet::WriteExcel->new($ARGV[1]);\nmy $worksheet = $workbook->addworksheet();\n\n# Row and column are zero indexed\nmy $row = 0;\n\nwhile () {\nchomp;\n# Split on single tab\nmy @Fld = split('\\t', $);\n\nmy $col = 0;\nforeach my $token (@Fld) {\n$worksheet->write($row, $col, $token);\n$col++;\n}\n$row++;\n}\n\nDownload this example:\n\n" }, { "name": "Example: datecalc1.pl", "content": "NOTE: An easier way of writing dates and times is to use the newer writedatetime() Worksheet\nmethod. See the datetime.pl example.\n\nDemonstration of writing date/time cells to Excel spreadsheets, using UNIX/Perl time as source\nof date/time.\n\nUNIX/Perl time is the time since the Epoch (00:00:00 GMT, 1 Jan 1970) measured in seconds.\n\nAn Excel file can use exactly one of two different date/time systems. In these systems, a\nfloating point number represents the number of days (and fractional parts of the day) since a\nstart point. The floating point number is referred to as a 'serial'.\n\nThe two systems ('1900' and '1904') use different starting points:\n\n'1900'; '1.00' is 1 Jan 1900 BUT 1900 is erroneously regarded as\na leap year - see:\nhttp://support.microsoft.com/support/kb/articles/Q181/3/70.asp\nfor the excuse^H^H^H^H^H^Hreason.\n'1904'; '1.00' is 2 Jan 1904.\n\nThe '1904' system is the default for Apple Macs. Windows versions of Excel have the option to\nuse the '1904' system.\n\nNote that Visual Basic's \"DateSerial\" function does NOT erroneously regard 1900 as a leap year,\nand thus its serials do not agree with the 1900 serials of Excel for dates before 1 Mar 1900.\n\nNote that StarOffice (at least at version 5.2) does NOT erroneously regard 1900 as a leap year,\nand thus its serials do not agree with the 1900 serials of Excel for dates before 1 Mar 1900.\n\n#!/usr/bin/perl -w\n\n\n######################################################################\n#\n# NOTE: An easier way of writing dates and times is to use the newer\n# writedatetime() Worksheet method. See the datetime.pl example.\n#\n######################################################################\n#\n# Demonstration of writing date/time cells to Excel spreadsheets,\n# using UNIX/Perl time as source of date/time.\n#\n######################################################################\n#\n# UNIX/Perl time is the time since the Epoch (00:00:00 GMT, 1 Jan 1970)\n# measured in seconds.\n#\n# An Excel file can use exactly one of two different date/time systems.\n# In these systems, a floating point number represents the number of days\n# (and fractional parts of the day) since a start point. The floating point\n# number is referred to as a 'serial'.\n#\n# The two systems ('1900' and '1904') use different starting points:\n#\n# '1900'; '1.00' is 1 Jan 1900 BUT 1900 is erroneously regarded as\n# a leap year - see:\n# http://support.microsoft.com/support/kb/articles/Q181/3/70.asp\n# for the excuse^H^H^H^H^H^Hreason.\n# '1904'; '1.00' is 2 Jan 1904.\n#\n# The '1904' system is the default for Apple Macs. Windows versions of\n# Excel have the option to use the '1904' system.\n#\n# Note that Visual Basic's \"DateSerial\" function does NOT erroneously\n# regard 1900 as a leap year, and thus its serials do not agree with\n# the 1900 serials of Excel for dates before 1 Mar 1900.\n#\n# Note that StarOffice (at least at version 5.2) does NOT erroneously\n# regard 1900 as a leap year, and thus its serials do not agree with\n# the 1900 serials of Excel for dates before 1 Mar 1900.\n#\n\n# Copyright 2000, Andrew Benham, adsb@bigfoot.com\n#\n\n######################################################################\n#\n# Calculation description\n# =======================\n#\n# 1900 system\n# -----------\n# Unix time is '0' at 00:00:00 GMT 1 Jan 1970, i.e. 70 years after 1 Jan 1900.\n# Of those 70 years, 17 (1904,08,12,16,20,24,28,32,36,40,44,48,52,56,60,64,68)\n# were leap years with an extra day.\n# Thus there were 17 + 70*365 days = 25567 days between 1 Jan 1900 and\n# 1 Jan 1970.\n# In the 1900 system, '1' is 1 Jan 1900, but as 1900 was not a leap year\n# 1 Jan 1900 should really be '2', so 1 Jan 1970 is '25569'.\n#\n# 1904 system\n# -----------\n# Unix time is '0' at 00:00:00 GMT 1 Jan 1970, i.e. 66 years after 1 Jan 1904.\n# Of those 66 years, 17 (1904,08,12,16,20,24,28,32,36,40,44,48,52,56,60,64,68)\n# were leap years with an extra day.\n# Thus there were 17 + 66*365 days = 24107 days between 1 Jan 1904 and\n# 1 Jan 1970.\n# In the 1904 system, 2 Jan 1904 being '1', 1 Jan 1970 is '24107'.\n#\n######################################################################\n#\n# Copyright (c) 2000, Andrew Benham.\n# This program is free software. It may be used, redistributed and/or\n# modified under the same terms as Perl itself.\n#\n# Andrew Benham, adsb@bigfoot.com\n# London, United Kingdom\n# 11 Nov 2000\n#\n######################################################################\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\nuse Time::Local;\n\nuse vars qw/$DATESYSTEM/;\n\n# Use 1900 date system on all platforms other than Apple Mac (for which\n# use 1904 date system).\n$DATESYSTEM = ($^O eq 'MacOS') ? 1 : 0;\n\nmy $workbook = Spreadsheet::WriteExcel->new(\"dates.xls\");\nmy $worksheet = $workbook->addworksheet();\n\nmy $formatdate = $workbook->addformat();\n$formatdate->setnumformat('d mmmm yyy');\n\n$worksheet->setcolumn(0,1,21);\n\n$worksheet->writestring (0,0,\"The epoch (GMT)\");\n$worksheet->writenumber (0,1,&calcserial(0,1),0x16);\n\n$worksheet->writestring (1,0,\"The epoch (localtime)\");\n$worksheet->writenumber (1,1,&calcserial(0,0),0x16);\n\n$worksheet->writestring (2,0,\"Today\");\n$worksheet->writenumber (2,1,&calcserial(),$formatdate);\n\nmy $christmas2000 = timelocal(0,0,0,25,11,100);\n$worksheet->writestring (3,0,\"Christmas 2000\");\n$worksheet->writenumber (3,1,&calcserial($christmas2000),$formatdate);\n\n$workbook->close();\n\n#-----------------------------------------------------------\n# calcserial()\n#\n# Called with (up to) 2 parameters.\n# 1. Unix timestamp. If omitted, uses current time.\n# 2. GMT flag. Set to '1' to return serial in GMT.\n# If omitted, returns serial in appropriate timezone.\n#\n# Returns date/time serial according to $DATESYSTEM selected\n#-----------------------------------------------------------\nsub calcserial {\nmy $time = (defined $[0]) ? $[0] : time();\nmy $gmtflag = (defined $[1]) ? $[1] : 0;\n\n# Divide timestamp by number of seconds in a day.\n# This gives a date serial with '0' on 1 Jan 1970.\nmy $serial = $time / 86400;\n\n# Adjust the date serial by the offset appropriate to the\n# currently selected system (1900/1904).\nif ($DATESYSTEM == 0) { # use 1900 system\n$serial += 25569;\n} else { # use 1904 system\n$serial += 24107;\n}\n\nunless ($gmtflag) {\n# Now have a 'raw' serial with the right offset. But this\n# gives a serial in GMT, which is false unless the timezone\n# is GMT. We need to adjust the serial by the appropriate\n# timezone offset.\n# Calculate the appropriate timezone offset by seeing what\n# the differences between localtime and gmtime for the given\n# time are.\n\nmy @gmtime = gmtime($time);\nmy @ltime = localtime($time);\n\n# For the first 7 elements of the two arrays, adjust the\n# date serial where the elements differ.\nfor (0 .. 6) {\nmy $diff = $ltime[$] - $gmtime[$];\nif ($diff) {\n$serial += adjustment($diff,$);\n}\n}\n}\n\n# Perpetuate the error that 1900 was a leap year by decrementing\n# the serial if we're using the 1900 system and the date is prior to\n# 1 Mar 1900. This has the effect of making serial value '60'\n# 29 Feb 1900.\n\n# This fix only has any effect if UNIX/Perl time on the platform\n# can represent 1900. Many can't.\n\nunless ($DATESYSTEM) {\n$serial-- if ($serial < 61); # '61' is 1 Mar 1900\n}\nreturn $serial;\n}\n\nsub adjustment {\n# Based on the difference in the localtime/gmtime array elements\n# number, return the adjustment required to the serial.\n\n# We only look at some elements of the localtime/gmtime arrays:\n# seconds unlikely to be different as all known timezones\n# have an offset of integral multiples of 15 minutes,\n# but it's easy to do.\n# minutes will be different for timezone offsets which are\n# not an exact number of hours.\n# hours very likely to be different.\n# weekday will differ when localtime/gmtime difference\n# straddles midnight.\n#\n# Assume that difference between localtime and gmtime is less than\n# 5 days, then don't have to do maths for day of month, month number,\n# year number, etc...\n\nmy ($delta,$element) = @;\nmy $adjust = 0;\n\nif ($element == 0) { # Seconds\n$adjust = $delta/86400; # 60 * 60 * 24\n} elsif ($element == 1) { # Minutes\n$adjust = $delta/1440; # 60 * 24\n} elsif ($element == 2) { # Hours\n$adjust = $delta/24; # 24\n} elsif ($element == 6) { # Day of week number\n# Catch difference straddling Sat/Sun in either direction\n$delta += 7 if ($delta < -4);\n$delta -= 7 if ($delta > 4);\n\n$adjust = $delta;\n}\nreturn $adjust;\n}\n\nDownload this example:\n\n" }, { "name": "Example: datecalc2.pl", "content": "Example of how to using the Date::Calc module to calculate Excel dates.\n\nNOTE: An easier way of writing dates and times is to use the newer writedatetime() Worksheet\nmethod. See the datetime.pl example.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# Example of how to using the Date::Calc module to calculate Excel dates.\n#\n# NOTE: An easier way of writing dates and times is to use the newer\n# writedatetime() Worksheet method. See the datetime.pl example.\n#\n# reverse('(c)'), June 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\nuse Date::Calc qw(DeltaDHMS); # You may need to install this module.\n\n\n# Create a new workbook and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"exceldate2.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n# Expand the first column so that the date is visible.\n$worksheet->setcolumn(\"A:A\", 25);\n\n\n# Add a format for the date\nmy $format = $workbook->addformat();\n$format->setnumformat('d mmmm yyy HH:MM:SS');\n\n\nmy $date;\n\n# Write some dates and times\n$date = exceldate(1900, 1, 1);\n$worksheet->write(\"A1\", $date, $format);\n\n$date = exceldate(2000, 1, 1);\n$worksheet->write(\"A2\", $date, $format);\n\n$date = exceldate(2000, 4, 17, 14, 33, 15);\n$worksheet->write(\"A3\", $date, $format);\n\n\n###############################################################################\n#\n# exceldate($years, $months, $days, $hours, $minutes, $seconds)\n#\n# Create an Excel date in the 1900 format. All of the arguments are optional\n# but you should at least add $years.\n#\n# Corrects for Excel's missing leap day in 1900. See exceltime1.pl for an\n# explanation.\n#\nsub exceldate {\n\nmy $years = $[0] || 1900;\nmy $months = $[1] || 1;\nmy $days = $[2] || 1;\nmy $hours = $[3] || 0;\nmy $minutes = $[4] || 0;\nmy $seconds = $[5] || 0;\n\nmy @date = ($years, $months, $days, $hours, $minutes, $seconds);\nmy @epoch = (1899, 12, 31, 0, 0, 0);\n\n($days, $hours, $minutes, $seconds) = DeltaDHMS(@epoch, @date);\n\nmy $date = $days + ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);\n\n# Add a day for Excel's missing leap day in 1900\n$date++ if ($date > 59);\n\nreturn $date;\n}\n\n###############################################################################\n#\n# exceldate($years, $months, $days, $hours, $minutes, $seconds)\n#\n# Create an Excel date in the 1904 format. All of the arguments are optional\n# but you should at least add $years.\n#\n# You will also need to call $workbook->set1904() for this format to be valid.\n#\nsub exceldate1904 {\n\nmy $years = $[0] || 1900;\nmy $months = $[1] || 1;\nmy $days = $[2] || 1;\nmy $hours = $[3] || 0;\nmy $minutes = $[4] || 0;\nmy $seconds = $[5] || 0;\n\nmy @date = ($years, $months, $days, $hours, $minutes, $seconds);\nmy @epoch = (1904, 1, 1, 0, 0, 0);\n\n($days, $hours, $minutes, $seconds) = DeltaDHMS(@epoch, @date);\n\nmy $date = $days + ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);\n\nreturn $date;\n}\n\nDownload this example:\n\n" }, { "name": "Example: lecxe.pl", "content": "Utility program to convert an Excel file into a Spreadsheet::WriteExcel program using Win32::OLE\n\n#!/usr/bin/perl -w\n\n#\n# Utility program to convert an Excel file into a Spreadsheet::WriteExcel\n# program using Win32::OLE\n#\n\n#\n# lecxe program\n# by t0mas@netlords.net\n#\n# Version 0.01a Initial release (alpha)\n\n\n# Modules\nuse strict;\nuse Win32::OLE;\nuse Win32::OLE::Const;\nuse Getopt::Std;\n\n\n# Vars\nuse vars qw(%opts);\n\n\n# Get options\ngetopts('i:o:v',\\%opts);\n\n\n# Not enough options\nexit &usage unless ($opts{i} && $opts{o});\n\n\n# Create Excel object\nmy $Excel = new Win32::OLE(\"Excel.Application\",\"Quit\") or\ndie \"Can't start excel: $!\";\n\n\n# Get constants\nmy $ExcelConst=Win32::OLE::Const->Load(\"Microsoft Excel\");\n\n\n# Show Excel\n$Excel->{Visible} = 1 if ($opts{v});\n\n\n# Open infile\nmy $Workbook = $Excel->Workbooks->Open({Filename=>$opts{i}});\n\n\n# Open outfile\nopen (OUTFILE,\">$opts{o}\") or die \"Can't open outfile $opts{o}: $!\";\n\n\n# Print header for outfile\nprint OUTFILE <<'EOH';\n#!/usr/bin/perl -w\n\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n\nuse vars qw($workbook %worksheets %formats);\n\n\n$workbook = Spreadsheet::WriteExcel->new(\"changeme.xls\");\n\n\nEOH\n\n\n# Loop all sheets\nforeach my $sheetnum (1..$Excel->Workbooks(1)->Worksheets->Count) {\n\n\n# Format sheet\nmy $name=$Excel->Workbooks(1)->Worksheets($sheetnum)->Name;\nprint \"Sheet $name\\n\" if ($opts{v});\nprint OUTFILE \"# Sheet $name\\n\";\nprint OUTFILE \"\\$worksheets{'$name'} = \\$workbook->addworksheet('$name');\\n\";\n\n\n# Get usedrange of cells in worksheet\nmy $usedrange=$Excel->Workbooks(1)->Worksheets($sheetnum)->UsedRange;\n\n\n# Loop all columns in used range\nforeach my $j (1..$usedrange->Columns->Count){\n\n\n# Format column\nprint \"Col $j\\n\" if ($opts{v});\nmy ($colwidth);\n$colwidth=$usedrange->Columns($j)->ColumnWidth;\nprint OUTFILE \"# Column $j\\n\";\nprint OUTFILE \"\\$worksheets{'$name'}->setcolumn(\".($j-1).\",\".($j-1).\n\", $colwidth);\\n\";\n\n\n# Loop all rows in used range\nforeach my $i (1..$usedrange->Rows->Count){\n\n\n# Format row\nprint \"Row $i\\n\" if ($opts{v});\nprint OUTFILE \"# Row $i\\n\";\ndo {\nmy ($rowheight);\n$rowheight=$usedrange->Rows($i)->RowHeight;\nprint OUTFILE \"\\$worksheets{'$name'}->setrow(\".($i-1).\n\", $rowheight);\\n\";\n} if ($j==1);\n\n\n# Start creating cell format\nmy $fname=\"\\$formats{'\".$name.'R'.$i.'C'.$j.\"'}\";\nmy $format=\"$fname=\\$workbook->addformat();\\n\";\nmy $printformat=0;\n\n# Check for borders\nmy @bfnames=qw(left right top bottom);\nforeach my $k (1..$usedrange->Cells($i,$j)->Borders->Count) {\nmy $lstyle=$usedrange->Cells($i,$j)->Borders($k)->LineStyle;\nif ($lstyle > 0) {\n$format.=$fname.\"->set\".$bfnames[$k-1].\"($lstyle);\\n\";\n$printformat=1;\n}\n}\n\n\n# Check for font\nmy ($fontattr,$prop,$func,%fontsets,$fontColor);\n%fontsets=(Name=>'setfont',\nSize=>'setsize');\nwhile (($prop,$func) = each %fontsets) {\n$fontattr=$usedrange->Cells($i,$j)->Font->$prop;\nif ($fontattr ne \"\") {\n$format.=$fname.\"->$func('$fontattr');\\n\";\n$printformat=1;\n}\n\n\n}\n%fontsets=(Bold=>'setbold(1)',\nItalic=>'setitalic(1)',\nUnderline=>'setunderline(1)',\nStrikethrough=>'setstrikeout(1)',\nSuperscript=>'setscript(1)',\nSubscript=>'setscript(2)',\nOutlineFont=>'setoutline(1)',\nShadow=>'setshadow(1)');\nwhile (($prop,$func) = each %fontsets) {\n$fontattr=$usedrange->Cells($i,$j)->Font->$prop;\nif ($fontattr==1) {\n$format.=$fname.\"->$func;\\n\" ;\n\n$printformat=1;\n}\n}\n$fontColor=$usedrange->Cells($i,$j)->Font->ColorIndex();\nif ($fontColor>0&&$fontColor!=$ExcelConst->{xlColorIndexAutomatic}) {\n$format.=$fname.\"->setcolor(\".($fontColor+7).\");\\n\" ;\n$printformat=1;\n}\n\n\n\n# Check text alignment, merging and wrapping\nmy ($halign,$valign,$merge,$wrap);\n$halign=$usedrange->Cells($i,$j)->HorizontalAlignment;\nmy %hAligns=($ExcelConst->{xlHAlignCenter}=>\"'center'\",\n$ExcelConst->{xlHAlignJustify}=>\"'justify'\",\n$ExcelConst->{xlHAlignLeft}=>\"'left'\",\n$ExcelConst->{xlHAlignRight}=>\"'right'\",\n$ExcelConst->{xlHAlignFill}=>\"'fill'\",\n$ExcelConst->{xlHAlignCenterAcrossSelection}=>\"'merge'\");\nif ($halign!=$ExcelConst->{xlHAlignGeneral}) {\n$format.=$fname.\"->setalign($hAligns{$halign});\\n\";\n$printformat=1;\n}\n$valign=$usedrange->Cells($i,$j)->VerticalAlignment;\nmy %vAligns=($ExcelConst->{xlVAlignBottom}=>\"'bottom'\",\n$ExcelConst->{xlVAlignCenter}=>\"'vcenter'\",\n$ExcelConst->{xlVAlignJustify}=>\"'vjustify'\",\n$ExcelConst->{xlVAlignTop}=>\"'top'\");\nif ($valign) {\n$format.=$fname.\"->setalign($vAligns{$valign});\\n\";\n$printformat=1;\n}\n$merge=$usedrange->Cells($i,$j)->MergeCells;\nif ($merge==1) {\n$format.=$fname.\"->setmerge();\\n\";\n\n$printformat=1;\n}\n$wrap=$usedrange->Cells($i,$j)->WrapText;\nif ($wrap==1) {\n$format.=$fname.\"->settextwrap(1);\\n\";\n\n$printformat=1;\n}\n\n\n# Check patterns\nmy ($pattern,%pats);\n%pats=(-4142=>0,-4125=>2,-4126=>3,-4124=>4,-4128=>5,-4166=>6,\n-4121=>7,-4162=>8);\n$pattern=$usedrange->Cells($i,$j)->Interior->Pattern;\nif ($pattern&&$pattern!=$ExcelConst->{xlPatternAutomatic}) {\n$pattern=$pats{$pattern} if ($pattern<0 && defined $pats{$pattern});\n$format.=$fname.\"->setpattern($pattern);\\n\";\n\n# Colors fg/bg\nmy ($cIndex);\n$cIndex=$usedrange->Cells($i,$j)->Interior->PatternColorIndex;\nif ($cIndex>0&&$cIndex!=$ExcelConst->{xlColorIndexAutomatic}) {\n$format.=$fname.\"->setbgcolor(\".($cIndex+7).\");\\n\";\n}\n$cIndex=$usedrange->Cells($i,$j)->Interior->ColorIndex;\nif ($cIndex>0&&$cIndex!=$ExcelConst->{xlColorIndexAutomatic}) {\n$format.=$fname.\"->setfgcolor(\".($cIndex+7).\");\\n\";\n}\n$printformat=1;\n}\n\n\n# Check for number format\nmy ($numformat);\n$numformat=$usedrange->Cells($i,$j)->NumberFormat;\nif ($numformat ne \"\") {\n$format.=$fname.\"->setnumformat('$numformat');\\n\";\n$printformat=1;\n}\n\n\n# Check for contents (text or formula)\nmy ($contents);\n$contents=$usedrange->Cells($i,$j)->Formula;\n$contents=$usedrange->Cells($i,$j)->Text if ($contents eq \"\");\n\n\n# Print cell\nif ($contents ne \"\" or $printformat) {\nprint OUTFILE \"# Cell($i,$j)\\n\";\nprint OUTFILE $format if ($printformat);\nprint OUTFILE \"\\$worksheets{'$name'}->write(\".($i-1).\",\".($j-1).\n\",'$contents'\";\nprint OUTFILE \",$fname\" if ($printformat);\nprint OUTFILE \");\\n\";\n}\n}\n}\n}\n\n\n# Famous last words...\nprint OUTFILE \"\\$workbook->close();\\n\";\n\n\n# Close outfile\nclose (OUTFILE) or die \"Can't close outfile $opts{o}: $!\";\n\n\n####################################################################\nsub usage {\nprintf STDERR \"usage: $0 [options]\\n\".\n\"\\tOptions:\\n\".\n\"\\t\\t-v \\tverbose mode\\n\" .\n\"\\t\\t-i \\tname of input file\\n\" .\n\"\\t\\t-o \\tname of output file\\n\";\n}\n\n\n####################################################################\nsub END {\n# Quit excel\ndo {\n$Excel->{DisplayAlerts} = 0;\n$Excel->Quit;\n} if (defined $Excel);\n}\n\n\nEND\n\n\n=head1 NAME\n\n\nlecxe - A Excel file to Spreadsheet::WriteExcel code converter\n\n\n=head1 DESCRIPTION\n\n\nThis program takes an MS Excel workbook file as input and from\nthat file, produces an output file with Perl code that uses the\nSpreadsheet::WriteExcel module to reproduce the original\nfile.\n\n\n=head1 STUFF\n\n\nAdditional hands-on editing of the output file might be neccecary\nas:\n\n\n* This program always names the file produced by output script\nchangeme.xls\n\n\n* Users of international Excel versions will have som work to do\non list separators and numeric punctation characters.\n\n\n=head1 SEE ALSO\n\n\nL, L, L\n\n\n=head1 BUGS\n\n\n* Picks wrong color on cells sometimes.\n\n\n* Probably a few other...\n\n\n=head1 DISCLAIMER\n\n\nI do not guarantee B with this program. If you use it you\nare doing so B! I may or may not support this\ndepending on my time schedule...\n\n\n=head1 AUTHOR\n\n\nt0mas@netlords.net\n\n\n=head1 COPYRIGHT\n\n\nCopyright 2001, t0mas@netlords.net\n\n\nThis package is free software; you can redistribute it and/or\nmodify it under the same terms as Perl itself.\n\nDownload this example:\n\n" }, { "name": "Example: convertA1.pl", "content": "This program contains helper functions to deal with the Excel A1 cell reference notation.\n\nThese functions have been superseded by Spreadsheet::WriteExcel::Utility.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# This program contains helper functions to deal with the Excel A1 cell\n# reference notation.\n#\n# These functions have been superseded by L.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\n\nprint \"\\n\";\nprint \"Cell B7 is equivalent to (\";\nprint join \" \", celltorowcol('B7');\nprint \") in row column notation.\\n\";\n\nprint \"Cell \\$B7 is equivalent to (\";\nprint join \" \", celltorowcol('$B7');\nprint \") in row column notation.\\n\";\n\nprint \"Cell B\\$7 is equivalent to (\";\nprint join \" \", celltorowcol('B$7');\nprint \") in row column notation.\\n\";\n\nprint \"Cell \\$B\\$7 is equivalent to (\";\nprint join \" \", celltorowcol('$B$7');\nprint \") in row column notation.\\n\\n\";\n\nprint \"Row and column (1999, 29) are equivalent to \";\nprint rowcoltocell(1999, 29), \".\\n\";\n\nprint \"Row and column (1999, 29, 0, 1) are equivalent to \";\nprint rowcoltocell(1999, 29, 0, 1), \".\\n\\n\";\n\nprint \"The base cell is: Z7\\n\";\nprint \"Increment the row: \", inccellrow('Z7'), \"\\n\";\nprint \"Decrement the row: \", deccellrow('Z7'), \"\\n\";\nprint \"Increment the column: \", inccellcol('Z7'), \"\\n\";\nprint \"Decrement the column: \", deccellcol('Z7'), \"\\n\\n\";\n\n\n###############################################################################\n#\n# rowcoltocell($row, $col, $rowabsolute, $colabsolute)\n#\n# Convert a zero based row and column reference to a A1 reference. For example\n# (0, 2) to C1. $rowabsolute, $colabsolute are optional. They are boolean\n# values used to indicate if the row or column value is absolute, i.e. if it is\n# prefixed by a $ sign: eg. (0, 2, 0, 1) converts to $C1.\n#\n# Returns: a cell reference string.\n#\nsub rowcoltocell {\n\nmy $row = $[0];\nmy $col = $[1];\nmy $rowabs = $[2] || 0;\nmy $colabs = $[3] || 0;\n\n\nif ($rowabs) {\n$rowabs = '$'\n}\nelse {\n$rowabs = ''\n}\n\nif ($colabs) {\n$colabs = '$'\n}\nelse {\n$colabs = ''\n}\n\n\nmy $int = int ($col / 26);\nmy $frac = $col % 26 +1;\n\nmy $chr1 ='';\nmy $chr2 ='';\n\n\nif ($frac != 0) {\n$chr2 = chr (ord('A') + $frac -1);\n}\n\nif ($int > 0) {\n$chr1 = chr (ord('A') + $int -1);\n}\n\n$row++; # Zero index to 1-index\n\nreturn $colabs . $chr1 . $chr2 . $rowabs. $row;\n}\n\n\n###############################################################################\n#\n# celltorowcol($cellref)\n#\n# Convert an Excel cell reference in A1 notation to a zero based row and column\n# reference; converts C1 to (0, 2, 0, 0).\n#\n# Returns: row, column, rowisabsolute, columnisabsolute\n#\n#\nsub celltorowcol {\n\nmy $cell = shift;\n\n$cell =~ /(\\$?)([A-I]?[A-Z])(\\$?)(\\d+)/;\n\nmy $colabs = $1 eq \"\" ? 0 : 1;\nmy $col = $2;\nmy $rowabs = $3 eq \"\" ? 0 : 1;\nmy $row = $4;\n\n# Convert base26 column string to number\n# All your Base are belong to us.\nmy @chars = split //, $col;\nmy $expn = 0;\n$col = 0;\n\nwhile (@chars) {\nmy $char = pop(@chars); # LS char first\n$col += (ord($char) -ord('A') +1) * (26$expn);\n$expn++;\n}\n\n# Convert 1-index to zero-index\n$row--;\n$col--;\n\nreturn $row, $col, $rowabs, $colabs;\n}\n\n\n###############################################################################\n#\n# inccellrow($cellref)\n#\n# Increments the row number of an Excel cell reference in A1 notation.\n# For example C3 to C4\n#\n# Returns: a cell reference string.\n#\nsub inccellrow {\n\nmy $cell = shift;\nmy ($row, $col, $rowabs, $colabs) = celltorowcol($cell);\n\n$row++;\n\nreturn rowcoltocell($row, $col, $rowabs, $colabs);\n}\n\n\n###############################################################################\n#\n# deccellrow($cellref)\n#\n# Decrements the row number of an Excel cell reference in A1 notation.\n# For example C4 to C3\n#\n# Returns: a cell reference string.\n#\nsub deccellrow {\n\nmy $cell = shift;\nmy ($row, $col, $rowabs, $colabs) = celltorowcol($cell);\n\n$row--;\n\nreturn rowcoltocell($row, $col, $rowabs, $colabs);\n}\n\n\n###############################################################################\n#\n# inccellcol($cellref)\n#\n# Increments the column number of an Excel cell reference in A1 notation.\n# For example C3 to D3\n#\n# Returns: a cell reference string.\n#\nsub inccellcol {\n\nmy $cell = shift;\nmy ($row, $col, $rowabs, $colabs) = celltorowcol($cell);\n\n$col++;\n\nreturn rowcoltocell($row, $col, $rowabs, $colabs);\n}\n\n\n###############################################################################\n#\n# deccellcol($cellref)\n#\n# Decrements the column number of an Excel cell reference in A1 notation.\n# For example D3 to C3\n#\n# Returns: a cell reference string.\n#\nsub deccellcol {\n\nmy $cell = shift;\nmy ($row, $col, $rowabs, $colabs) = celltorowcol($cell);\n\n$col--;\n\nreturn rowcoltocell($row, $col, $rowabs, $colabs);\n}\n\nDownload this example:\n\n" }, { "name": "Example: writeA1.pl", "content": "This is an example of how to extend the Spreadsheet::WriteExcel module.\n\nCode is appended to the Spreadsheet::WriteExcel::Worksheet module by reusing the package name.\nThe new code provides a write() method that allows you to use Excels A1 style cell references.\nThis is not particularly useful but it serves as an example of how the module can be extended\nwithout modifying the code directly.\n\n#!/usr/bin/perl -w\n\n###############################################################################\n#\n# This is an example of how to extend the Spreadsheet::WriteExcel module.\n#\n# Code is appended to the Spreadsheet::WriteExcel::Worksheet module by reusing\n# the package name. The new code provides a write() method that allows you to\n# use Excels A1 style cell references. This is not particularly useful but it\n# serves as an example of how the module can be extended without modifying the\n# code directly.\n#\n# reverse('(c)'), March 2001, John McNamara, jmcnamara@cpan.org\n#\n\nuse strict;\nuse Spreadsheet::WriteExcel;\n\n# Create a new workbook called simple.xls and add a worksheet\nmy $workbook = Spreadsheet::WriteExcel->new(\"writeA1.xls\");\nmy $worksheet = $workbook->addworksheet();\n\n# Write numbers or text\n$worksheet->write (0, 0, \"Hello\");\n$worksheet->writeA1(\"A3\", \"A3\" );\n$worksheet->writeA1(\"A5\", 1.2345 );\n\n\n###############################################################################\n#\n# The following will be appended to the Spreadsheet::WriteExcel::Worksheet\n# package.\n#\n\npackage Spreadsheet::WriteExcel::Worksheet;\n\n###############################################################################\n#\n# writeA1($cell, $token, $format)\n#\n# Convert $cell from Excel A1 notation to $row, $col notation and\n# call write() on $token.\n#\n# Returns: return value of called subroutine or -4 for invalid cell\n# reference.\n#\nsub writeA1 {\nmy $self = shift;\nmy $cell = shift;\nmy $col;\nmy $row;\n\nif ($cell =~ /([A-z]+)(\\d+)/) {\n($row, $col) = convertA1($2, $1);\n$self->write($row, $col, @);\n} else {\nreturn -4;\n}\n}\n\n###############################################################################\n#\n# convertA1($row, $col)\n#\n# Convert Excel A1 notation to $row, $col notation. Convert base26 column\n# string to a number.\n#\nsub convertA1 {\nmy $row = $[0];\nmy $col = $[1]; # String in AA notation\n\nmy @chars = split //, $col;\nmy $expn = 0;\n$col = 0;\n\nwhile (@chars) {\nmy $char = uc(pop(@chars)); # LS char first\n$col += (ord($char) -ord('A') +1) * (26$expn);\n$expn++;\n}\n\n# Convert 1 index to 0 index\n$row--;\n$col--;\n\nreturn($row, $col);\n}\n\nDownload this example:\n\n" } ] }, "AUTHOR": { "content": "John McNamara jmcnamara@cpan.org\n\nContributed examples contain the original author's name.\n", "subsections": [] }, "COPYRIGHT": { "content": "Copyright MM-MMX, John McNamara.\n\nAll Rights Reserved. This module is free software. It may be used, redistributed and/or modified\nunder the same terms as Perl itself.\n", "subsections": [] } }, "summary": "Examples - Spreadsheet::WriteExcel example programs.", "flags": [], "examples": [], "see_also": [] }