Excel-Writer-XLSX
view release on metacpan or search on metacpan
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
$worksheet1->activate();
$worksheet2->select();
$worksheet3->select();
A selected worksheet has its tab highlighted. Selecting worksheets is a way of grouping them together so that, for example, several worksheets could be printed in one go. A worksheet that has been activated via the C<activate()> method will also appe...
=head2 hide()
The C<hide()> method is used to hide a worksheet:
$worksheet2->hide();
You may wish to hide a worksheet in order to avoid confusing a user with intermediate data or calculations.
A hidden worksheet can not be activated or selected so this method is mutually exclusive with the C<activate()> and C<select()> methods. In addition, since the first worksheet will default to being the active worksheet, you cannot hide the first work...
$worksheet2->activate();
$worksheet1->hide();
=head2 very_hidden()
The C<very_hidden()> method can be used to hide a worksheet similar to the
C<hide()> method. The difference is that the worksheet cannot be unhidden in
the the Excel user interface. The Excel worksheet "xlSheetVeryHidden" option
can only be unset programmatically by VBA.
=head2 set_first_sheet()
The C<activate()> method determines which worksheet is initially selected. However, if there are a large number of worksheets the selected worksheet may not appear on the screen. To avoid this you can select which is the leftmost visible worksheet us...
for ( 1 .. 20 ) {
$workbook->add_worksheet;
}
$worksheet21 = $workbook->add_worksheet();
$worksheet22 = $workbook->add_worksheet();
$worksheet21->set_first_sheet();
$worksheet22->activate();
This method is not required very often. The default value is the first worksheet.
=head2 protect( $password, \%options )
The C<protect()> method is used to protect a worksheet from modification:
$worksheet->protect();
The C<protect()> method also has the effect of enabling a cell's C<locked> and C<hidden> properties if they have been set. A I<locked> cell cannot be edited and this property is on by default for all cells. A I<hidden> cell will display the results o...
See the C<protection.pl> program in the examples directory of the distro for an illustrative example and the C<set_locked> and C<set_hidden> format methods in L</CELL FORMATTING>.
You can optionally add a password to the worksheet protection:
$worksheet->protect( 'drowssap' );
The password should be an ASCII string. Passing the empty string C<''> is the same as turning on protection without a password.
Note, the worksheet level password in Excel provides very weak protection. It does not encrypt your data and is very easy to deactivate. Full workbook encryption is not supported by C<Excel::Writer::XLSX> since it requires a completely different file...
You can specify which worksheet elements you wish to protect by passing a hash_ref with any or all of the following keys:
# Default shown.
%options = (
objects => 0,
scenarios => 0,
format_cells => 0,
format_columns => 0,
format_rows => 0,
insert_columns => 0,
insert_rows => 0,
insert_hyperlinks => 0,
delete_columns => 0,
delete_rows => 0,
select_locked_cells => 1,
sort => 0,
autofilter => 0,
pivot_tables => 0,
select_unlocked_cells => 1,
);
The default boolean values are shown above. Individual elements can be protected as follows:
$worksheet->protect( 'drowssap', { insert_rows => 1 } );
For chartsheets the allowable options and default values are:
%options = (
objects => 1,
content => 1,
);
=head2 unprotect_range( $cell_range, $range_name )
The C<unprotect_range()> method is used to unprotect ranges in a protected worksheet. It can be used to set a single range or multiple ranges:
$worksheet->unprotect_range( 'A1' );
$worksheet->unprotect_range( 'C1' );
$worksheet->unprotect_range( 'E1:E3' );
$worksheet->unprotect_range( 'G1:K100' );
As in Excel the ranges are given sequential names like C<Range1> and C<Range2> but a user defined name can also be specified:
$worksheet->unprotect_range( 'G4:I6', 'MyRange' );
=head2 set_selection( $first_row, $first_col, $last_row, $last_col )
This method can be used to specify which cell or cells are selected in a worksheet. The most common requirement is to select a single cell, in which case C<$last_row> and C<$last_col> can be omitted. The active cell within a selected range is determi...
Examples:
$worksheet1->set_selection( 3, 3 ); # 1. Cell D4.
$worksheet2->set_selection( 3, 3, 6, 6 ); # 2. Cells D4 to G7.
$worksheet3->set_selection( 6, 6, 3, 3 ); # 3. Cells G7 to D4.
$worksheet4->set_selection( 'D4' ); # Same as 1.
$worksheet5->set_selection( 'D4:G7' ); # Same as 2.
$worksheet6->set_selection( 'G7:D4' ); # Same as 3.
The default cell selections is (0, 0), 'A1'.
=head2 set_top_left_cell( $row, $col )
This method can be used to set the top leftmost visible cell in the worksheet:
$worksheet->set_top_left_cell( 31, 26 );
# Same as:
$worksheet->set_top_left_cell( 'AA32' );
You can also use A1 notation, as shown above, see the note about L</Cell notation>.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=head2 set_vba_name()
The Worksheet C<set_vba_name()> method can be used to set the VBA codename for the worksheet (there is a similar method for the workbook VBA name). This is sometimes required when a C<vbaProject> macro included via C<add_vba_project()> refers to the ...
See also L<WORKING WITH VBA MACROS>.
=head2 ignore_errors()
The C<ignore_errors()> method can be used to ignore various worksheet cell errors/warnings. For example the following code writes a string that looks like a number:
$worksheet->write_string('D2', '123');
This causes Excel to display a small green triangle in the top left hand corner of the cell to indicate an error/warning.
Sometimes these warnings are useful indicators that there is an issue in the spreadsheet but sometimes it is preferable to turn them off. Warnings can be turned off at the Excel level for all workbooks and worksheets by using the using "Excel options...
$worksheet->ignore_errors({number_stored_as_text => 'A1:H50'});
# Or for more than one option:
$worksheet->ignore_errors({number_stored_as_text => 'A1:H50',
eval_error => 'A1:H50'});
The range can be a single cell, a range of cells, or multiple cells and ranges separated by spaces:
# Single cell.
$worksheet->ignore_errors({eval_error => 'C6'});
# Or a single range:
$worksheet->ignore_errors({eval_error => 'C6:G8'});
# Or multiple cells and ranges:
$worksheet->ignore_errors({eval_error => 'C6 E6 G1:G20 J2:J6'});
Note: calling C<ignore_errors> multiple times will overwrite the previous settings.
You can turn off warnings for an entire column by specifying the range from the first cell in the column to the last cell in the column:
$worksheet->ignore_errors({number_stored_as_text => 'A1:A1048576'});
Or for the entire worksheet by specifying the range from the first cell in the worksheet to the last cell in the worksheet:
$worksheet->ignore_errors({number_stored_as_text => 'A1:XFD1048576'});
The worksheet errors/warnings that can be ignored are:
=over
=item * C<number_stored_as_text>: Turn off errors/warnings for numbers stores as text.
=item * C<eval_error>: Turn off errors/warnings for formula errors (such as divide by zero).
=item * C<formula_differs>: Turn off errors/warnings for formulas that differ from surrounding formulas.
=item * C<formula_range>: Turn off errors/warnings for formulas that omit cells in a range.
=item * C<formula_unlocked>: Turn off errors/warnings for unlocked cells that contain formulas.
=item * C<empty_cell_reference>: Turn off errors/warnings for formulas that refer to empty cells.
=item * C<list_data_validation>: Turn off errors/warnings for cells in a table that do not comply with applicable data validation rules.
=item * C<calculated_column>: Turn off errors/warnings for cell formulas that differ from the column formula.
=item * C<two_digit_text_year>: Turn off errors/warnings for formulas that contain a two digit text representation of a year.
=back
=head1 PAGE SET-UP METHODS
Page set-up methods affect the way that a worksheet looks when it is printed. They control features such as page headers and footers and margins. These methods are really just standard worksheet methods. They are documented here in a separate section...
The following methods are available for page set-up:
set_landscape()
set_portrait()
set_page_view()
set_pagebreak_view()
set_paper()
center_horizontally()
center_vertically()
set_margins()
set_header()
set_footer()
repeat_rows()
repeat_columns()
hide_gridlines()
print_row_col_headers()
print_area()
print_across()
fit_to_pages()
set_start_page()
set_print_scale()
print_black_and_white()
set_h_pagebreaks()
set_v_pagebreaks()
A common requirement when working with Excel::Writer::XLSX is to apply the same page set-up features to all of the worksheets in a workbook. To do this you can use the C<sheets()> method of the C<workbook> class to access the array of worksheets in a...
for $worksheet ( $workbook->sheets() ) {
$worksheet->set_landscape();
}
=head2 set_landscape()
This method is used to set the orientation of a worksheet's printed page to landscape:
$worksheet->set_landscape(); # Landscape mode
=head2 set_portrait()
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
The C<set_v_pagebreaks()> method will accept a list of page breaks and you can call it more than once:
$worksheet2->set_v_pagebreaks( 20, 40, 60, 80, 100 ); # Add breaks
$worksheet2->set_v_pagebreaks( 120, 140, 160, 180, 200 ); # Add some more
Note: If you specify the "fit to page" option via the C<fit_to_pages()> method it will override all manual page breaks.
=head1 CELL FORMATTING
This section describes the methods and properties that are available for formatting cells in Excel. The properties of a cell that can be formatted include: fonts, colours, patterns, borders, alignment and number formatting.
=head2 Creating and using a Format object
Cell formatting is defined through a Format object. Format objects are created by calling the workbook C<add_format()> method as follows:
my $format1 = $workbook->add_format(); # Set properties later
my $format2 = $workbook->add_format( %props ); # Set at creation
The format object holds all the formatting properties that can be applied to a cell, a row or a column. The process of setting these properties is discussed in the next section.
Once a Format object has been constructed and its properties have been set it can be passed as an argument to the worksheet C<write> methods as follows:
$worksheet->write( 0, 0, 'One', $format );
$worksheet->write_string( 1, 0, 'Two', $format );
$worksheet->write_number( 2, 0, 3, $format );
$worksheet->write_blank( 3, 0, $format );
Formats can also be passed to the worksheet C<set_row()> and C<set_column()> methods to define the default property for a row or column.
$worksheet->set_row( 0, 15, $format );
$worksheet->set_column( 0, 0, 15, $format );
=head2 Format methods and Format properties
The following table shows the Excel format categories, the formatting properties that can be applied and the equivalent object method:
Category Description Property Method Name
-------- ----------- -------- -----------
Font Font type font set_font()
Font size size set_size()
Font color color set_color()
Bold bold set_bold()
Italic italic set_italic()
Underline underline set_underline()
Strikeout font_strikeout set_font_strikeout()
Super/Subscript font_script set_font_script()
Outline font_outline set_font_outline()
Shadow font_shadow set_font_shadow()
Number Numeric format num_format set_num_format()
Protection Lock cells locked set_locked()
Hide formulas hidden set_hidden()
Alignment Horizontal align align set_align()
Vertical align valign set_align()
Rotation rotation set_rotation()
Text wrap text_wrap set_text_wrap()
Justify last text_justlast set_text_justlast()
Center across center_across set_center_across()
Indentation indent set_indent()
Shrink to fit shrink set_shrink()
Pattern Cell pattern pattern set_pattern()
Background color bg_color set_bg_color()
Foreground color fg_color set_fg_color()
Border Cell border border set_border()
Bottom border bottom set_bottom()
Top border top set_top()
Left border left set_left()
Right border right set_right()
Border color border_color set_border_color()
Bottom color bottom_color set_bottom_color()
Top color top_color set_top_color()
Left color left_color set_left_color()
Right color right_color set_right_color()
Diagonal type diag_type set_diag_type()
Diagonal border diag_border set_diag_border()
Diagonal color diag_color set_diag_color()
There are two ways of setting Format properties: by using the object method interface or by setting the property directly. For example, a typical use of the method interface would be as follows:
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
By comparison the properties can be set directly by passing a hash of properties to the Format constructor:
my $format = $workbook->add_format( bold => 1, color => 'red' );
or after the Format has been constructed by means of the C<set_format_properties()> method as follows:
my $format = $workbook->add_format();
$format->set_format_properties( bold => 1, color => 'red' );
You can also store the properties in one or more named hashes and pass them to the required method:
my %font = (
font => 'Calibri',
size => 12,
color => 'blue',
bold => 1,
);
my %shading = (
bg_color => 'green',
pattern => 1,
);
my $format1 = $workbook->add_format( %font ); # Font only
my $format2 = $workbook->add_format( %font, %shading ); # Font and shading
The provision of two ways of setting properties might lead you to wonder which is the best way. The method mechanism may be better if you prefer setting properties via method calls (which the author did when the code was first written) otherwise pass...
The Perl/Tk style of adding properties is also supported:
my %font = (
-font => 'Calibri',
-size => 12,
-color => 'blue',
-bold => 1,
);
=head2 Working with formats
The default format is Calibri 11 with all other properties off.
Each unique format in Excel::Writer::XLSX must have a corresponding Format object. It isn't possible to use a Format with a write() method and then redefine the Format for use at a later stage. This is because a Format is applied to a cell not in its...
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'red' );
$worksheet->write( 'A1', 'Cell A1', $format );
$format->set_color( 'green' );
$worksheet->write( 'B1', 'Cell B1', $format );
Cell A1 is assigned the Format C<$format> which is initially set to the colour red. However, the colour is subsequently set to green. When Excel displays Cell A1 it will display the final state of the Format which in this case will be the colour gree...
In general a method call without an argument will turn a property on, for example:
my $format1 = $workbook->add_format();
$format1->set_bold(); # Turns bold on
$format1->set_bold( 1 ); # Also turns bold on
$format1->set_bold( 0 ); # Turns bold off
=head1 FORMAT METHODS
The Format object methods are described in more detail in the following sections. In addition, there is a Perl program called C<formats.pl> in the C<examples> directory of the WriteExcel distribution. This program creates an Excel workbook called C<f...
The following Format methods are available:
set_font()
set_size()
set_color()
set_bold()
set_italic()
set_underline()
set_font_strikeout()
set_font_script()
set_font_outline()
set_font_shadow()
set_num_format()
set_locked()
set_hidden()
set_align()
set_rotation()
set_text_wrap()
set_text_justlast()
set_center_across()
set_indent()
set_shrink()
set_pattern()
set_bg_color()
set_fg_color()
set_border()
set_bottom()
set_top()
set_left()
set_right()
set_border_color()
set_bottom_color()
set_top_color()
set_left_color()
set_right_color()
set_diag_type()
set_diag_border()
set_diag_color()
The above methods can also be applied directly as properties. For example C<< $format->set_bold() >> is equivalent to C<< $workbook->add_format(bold => 1) >>.
=head2 set_format_properties( %properties )
The properties of an existing Format object can be also be set by means of C<set_format_properties()>:
my $format = $workbook->add_format();
$format->set_format_properties( bold => 1, color => 'red' );
However, this method is here mainly for legacy reasons. It is preferable to set the properties in the format constructor:
my $format = $workbook->add_format( bold => 1, color => 'red' );
=head2 set_font( $fontname )
Default state: Font is Calibri
Default action: None
Valid args: Any valid font name
Specify the font used:
$format->set_font('Times New Roman');
Excel can only display fonts that are installed on the system that it is running on. Therefore it is best to use the fonts that come as standard such as 'Calibri', 'Times New Roman' and 'Courier New'. See also the Fonts worksheet created by formats.p...
=head2 set_size()
Default state: Font size is 10
Default action: Set font size to 1
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
0 0x00 General
1 0x01 0
2 0x02 0.00
3 0x03 #,##0
4 0x04 #,##0.00
5 0x05 ($#,##0_);($#,##0)
6 0x06 ($#,##0_);[Red]($#,##0)
7 0x07 ($#,##0.00_);($#,##0.00)
8 0x08 ($#,##0.00_);[Red]($#,##0.00)
9 0x09 0%
10 0x0a 0.00%
11 0x0b 0.00E+00
12 0x0c # ?/?
13 0x0d # ??/??
14 0x0e m/d/yy
15 0x0f d-mmm-yy
16 0x10 d-mmm
17 0x11 mmm-yy
18 0x12 h:mm AM/PM
19 0x13 h:mm:ss AM/PM
20 0x14 h:mm
21 0x15 h:mm:ss
22 0x16 m/d/yy h:mm
.. .... ...........
37 0x25 (#,##0_);(#,##0)
38 0x26 (#,##0_);[Red](#,##0)
39 0x27 (#,##0.00_);(#,##0.00)
40 0x28 (#,##0.00_);[Red](#,##0.00)
41 0x29 _(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
42 0x2a _($* #,##0_);_($* (#,##0);_($* "-"_);_(@_)
43 0x2b _(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
44 0x2c _($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
45 0x2d mm:ss
46 0x2e [h]:mm:ss
47 0x2f mm:ss.0
48 0x30 ##0.0E+0
49 0x31 @
For examples of these formatting codes see the 'Numerical formats' worksheet created by formats.pl. See also the number_formats1.html and the number_formats2.html documents in the C<docs> directory of the distro.
Note 1. Numeric formats 23 to 36 are not documented by Microsoft and may differ in international versions.
Note 2. The built-in formats are localised according to the locale settings (regional settings on Windows) of the user when opening the file in Excel:
=over
=item * The dot appears as the defined local decimal separator.
=item * The comma appears as the defined local digit groups separator.
=item * The dollar sign appears as the defined local currency symbol.
=item * The date, time and duration formats appear as the local equivalent date or time format.
=back
=head2 set_locked()
Default state: Cell locking is on
Default action: Turn locking on
Valid args: 0, 1
This property can be used to prevent modification of a cells contents. Following Excel's convention, cell locking is turned on by default. However, it only has an effect if the worksheet has been protected, see the worksheet C<protect()> method.
my $locked = $workbook->add_format();
$locked->set_locked( 1 ); # A non-op
my $unlocked = $workbook->add_format();
$unlocked->set_locked( 0 );
# Enable worksheet protection
$worksheet->protect();
# This cell cannot be edited.
$worksheet->write( 'A1', '=1+2', $locked );
# This cell can be edited.
$worksheet->write( 'A2', '=1+2', $unlocked );
Note: This offers weak protection even with a password, see the note in relation to the C<protect()> method.
=head2 set_hidden()
Default state: Formula hiding is off
Default action: Turn hiding on
Valid args: 0, 1
This property is used to hide a formula while still displaying its result. This is generally used to hide complex calculations from end users who are only interested in the result. It only has an effect if the worksheet has been protected, see the wo...
my $hidden = $workbook->add_format();
$hidden->set_hidden();
# Enable worksheet protection
$worksheet->protect();
# The formula in this cell isn't visible
$worksheet->write( 'A1', '=1+2', $hidden );
Note: This offers weak protection even with a password, see the note in relation to the C<protect()> method.
=head2 set_align()
Default state: Alignment is off
Default action: Left alignment
Valid args: 'left' Horizontal
'center'
'right'
'fill'
'justify'
'center_across'
'top' Vertical
'vcenter'
'bottom'
'vjustify'
This method is used to set the horizontal and vertical text alignment within a cell. Vertical and horizontal alignments can be combined. The method is used as follows:
my $format = $workbook->add_format();
$format->set_align( 'center' );
$format->set_align( 'vcenter' );
$worksheet->set_row( 0, 30 );
$worksheet->write( 0, 0, 'X', $format );
Text can be aligned across two or more adjacent cells using the C<center_across> property. However, for genuine merged cells it is better to use the C<merge_range()> worksheet method.
The C<vjustify> (vertical justify) option can be used to provide automatic text wrapping in a cell. The height of the cell will be adjusted to accommodate the wrapped text. To specify where the text wraps use the C<set_text_wrap()> method.
For further examples see the 'Alignment' worksheet created by formats.pl.
lib/Excel/Writer/XLSX.pm view on Meta::CPAN
=head1 REQUIREMENTS
L<http://search.cpan.org/search?dist=Archive-Zip/>.
Perl 5.8.2.
=head1 SPEED AND MEMORY USAGE
C<Spreadsheet::WriteExcel> was written to optimise speed and reduce memory usage. However, these design goals meant that it wasn't easy to implement features that many users requested such as writing formatting and data separately.
As a result C<Excel::Writer::XLSX> takes a different design approach and holds a lot more data in memory so that it is functionally more flexible.
The effect of this is that Excel::Writer::XLSX is about 30% slower than Spreadsheet::WriteExcel and uses 5 times more memory.
In addition the extended row and column ranges in Excel 2007+ mean that it is possible to run out of memory creating large files. This was almost never an issue with Spreadsheet::WriteExcel.
This memory usage can be reduced almost completely by using the Workbook C<set_optimization()> method:
$workbook->set_optimization();
The trade-off is that you won't be able to take advantage of features that manipulate cell data after it is written. One such feature is Tables.
=head1 DOWNLOADING
The latest version of this module is always available at: L<http://search.cpan.org/search?dist=Excel-Writer-XLSX/>.
=head1 INSTALLATION
The module can be installed using the standard Perl procedure:
perl Makefile.PL
make
make test
make install # You may need to be sudo/root
=head1 DIAGNOSTICS
=over 4
=item Filename required by Excel::Writer::XLSX->new()
A filename must be given in the constructor.
=item Can't open filename. It may be in use or protected.
The file cannot be opened for writing. The directory that you are writing to may be protected or the file may be in use by another program.
=item Can't call method "XXX" on an undefined value at someprogram.pl.
On Windows this is usually caused by the file that you are trying to create clashing with a version that is already open and locked by Excel.
=item The file you are trying to open 'file.xls' is in a different format than specified by the file extension.
This warning occurs when you create an XLSX file but give it an xls extension.
=back
=head1 WRITING EXCEL FILES
Depending on your requirements, background and general sensibilities you may prefer one of the following methods of getting data into Excel:
=over 4
=item * Spreadsheet::WriteExcel
This module is the precursor to Excel::Writer::XLSX and uses the same interface. It produces files in the Excel Biff xls format that was used in Excel versions 97-2003. These files can still be read by Excel 2007 but have some limitations in relation...
L<Spreadsheet::WriteExcel>.
=item * Win32::OLE module and office automation
This requires a Windows platform and an installed copy of Excel. This is the most powerful and complete method for interfacing with Excel.
L<Win32::OLE>
=item * CSV, comma separated variables or text
Excel will open and automatically convert files with a C<csv> extension.
To create CSV files refer to the L<Text::CSV_XS> module.
=item * DBI with DBD::ADO or DBD::ODBC
Excel files contain an internal index table that allows them to act like a database file. Using one of the standard Perl database modules you can connect to an Excel file as a database.
=back
For other Perl-Excel modules try the following search: L<http://search.cpan.org/search?mode=module&query=excel>.
=head1 READING EXCEL FILES
To read data from Excel files try:
=over 4
=item * L<Spreadsheet::ParseXLSX>
A module for reading data from XLSX files. It also imports most, if
not all, of the metadata to be found in Excel XLSX files. As its
author describes it: "This module is an adaptor for
L<Spreadsheet::ParseExcel> that reads XLSX files. For documentation
about the various data that you can retrieve from these classes,
( run in 1.012 second using v1.01-cache-2.11-cpan-39bf76dae61 )