view release on metacpan or  search on metacpan

lib/Excel/Writer/XLSX.pm  view on Meta::CPAN

Set the name for the chart sheet. The name property is optional and if it isn't supplied will default to C<Chart1 .. n>. The name must be a valid Excel worksheet name. See C<add_worksheet()> for more details on valid sheet names. The C<name> property...

    my $chart = $workbook->add_chart( type => 'line', name => 'Results Chart' );

=item * C<embedded>

Specifies that the Chart object will be inserted in a worksheet via the C<insert_chart()> Worksheet method. It is an error to try insert a Chart that doesn't have this flag set.

    my $chart = $workbook->add_chart( type => 'line', embedded => 1 );

    # Configure the chart.
    ...

    # Insert the chart into the a worksheet.
    $worksheet->insert_chart( 'E2', $chart );

=back

See Excel::Writer::XLSX::Chart for details on how to configure the chart object once it is created. See also the C<chart_*.pl> programs in the examples directory of the distro.



=head2 add_shape( %properties )

The C<add_shape()> method can be used to create new shapes that may be inserted into a worksheet.

You can either define the properties at creation time via a hash of property values or later via method calls.

    # Set properties at creation.
    $plus = $workbook->add_shape(
        type   => 'plus',
        id     => 3,
        width  => $pw,
        height => $ph
    );


    # Default rectangle shape. Set properties later.
    $rect =  $workbook->add_shape();

See L<Excel::Writer::XLSX::Shape> for details on how to configure the shape object once it is created.

See also the C<shape*.pl> programs in the examples directory of the distro.



=head2 add_vba_project( 'vbaProject.bin' )

The C<add_vba_project()> method can be used to add macros or functions to an Excel::Writer::XLSX file using a binary VBA project file that has been extracted from an existing Excel C<xlsm> file.

    my $workbook  = Excel::Writer::XLSX->new( 'file.xlsm' );

    $workbook->add_vba_project( './vbaProject.bin' );

The supplied C<extract_vba> utility can be used to extract the required C<vbaProject.bin> file from an existing Excel file:

    $ extract_vba file.xlsm
    Extracted 'vbaProject.bin' successfully

Macros can be tied to buttons using the worksheet C<insert_button()> method (see the L</WORKSHEET METHODS> section for details):

    $worksheet->insert_button( 'C2', { macro => 'my_macro' } );

Note, Excel uses the file extension C<xlsm> instead of C<xlsx> for files that contain macros. It is advisable to follow the same convention.

See also the C<macros.pl> example file and the L<WORKING WITH VBA MACROS>.



=head2 set_vba_name()

The C<set_vba_name()> method can be used to set the VBA codename for the workbook. This is sometimes required when a C<vbaProject macro> included via C<add_vba_project()> refers to the workbook. The default Excel VBA name of C<ThisWorkbook> is used i...


=head2 close()

In general your Excel file will be closed automatically when your program ends or when the Workbook object goes out of scope. However it is recommended to explicitly call the C<close()> method close the Excel file and avoid the potential issues outli...

    $workbook->close();

The return value of C<close()> is the same as that returned by perl when it closes the file created by C<new()>. This allows you to handle error conditions in the usual way:

    $workbook->close() or die "Error closing file: $!";

An explicit C<close()> is required if the file must be closed prior to performing some external action on it such as copying it, reading its size or attaching it to an email.

In addition, C<close()> may be required to prevent perl's garbage collector from disposing of the Workbook, Worksheet and Format objects in the wrong order. Situations where this can occur are:

=over 4

=item *

If C<my()> was not used to declare the scope of a workbook variable created using C<new()>.

=item *

If the C<new()>, C<add_worksheet()> or C<add_format()> methods are called in subroutines.

=back

The reason for this is that Excel::Writer::XLSX relies on Perl's C<DESTROY> mechanism to trigger destructor methods in a specific sequence. This may not happen in cases where the Workbook, Worksheet and Format variables are not lexically scoped or wh...

To avoid these issues it is recommended that you always close the Excel::Writer::XLSX filehandle using C<close()>.




=head2 set_size( $width, $height )

The C<set_size()> method can be used to set the size of a workbook window.

    $workbook->set_size(1200, 800);

The Excel window size was used in Excel 2007 to define the width and height of a workbook window within the Multiple Document Interface (MDI). In later versions of Excel for Windows this interface was dropped. This method is currently only useful whe...

Note, this doesn't equate exactly to the Excel for Mac pixel size since it is based on the original Excel 2007 for Windows sizing.