The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Spreadsheet-DataToExcel-0.0104
River stage one • 1 direct dependent • 2 total dependents
++
/ Spreadsheet::DataToExcel

NAME

Spreadsheet::DataToExcel - Simple method to generate Excel files from 2D arrayrefs

SYNOPSIS

    use strict;
    use warnings;
    use Spreadsheet::DataToExcel;

    my @data = (
        [ qw/ID Time Number/ ],
        map [ $_, time(), rand() ], 1..10,
    );

    my $dump = Spreadsheet::DataToExcel->new;
    $dump->dump( 'dump.xls', \@data )
        or die "Error: " . $dump->error;

    # dumps out the @data into Excel file while setting text wrap on new 
    # lines, centering text in cells of the first row and settings
    # column widths to the largest size of the data

DESCRIPTION

Spreadsheet::WriteExcel is a marvelous module; however, I would always find myself digging through the huge doc, not something I enjoy when all I ever want to do is simply dump my rows/columns centering first row as well as setting the sizes of columns to just be large enough to fit all the data. This is where Spreadsheet::DataToExcel comes in.

If you're looking for any more functionality than Spreadsheet::DataToExcel offers, please see Spreadsheet::WriteExcel.

CONSTRUCTOR

new

    my $dump = Spreadsheet::DataToExcel->new;

Takes no arguments, returns a freshly baked Spreadsheet::DataToExcel object.

METHODS

dump

    # different ways to use:

    # first example
    $dump->data( \@data );
    $dump->file('dump.xls');
    $dump->dump # dumps \@data into 'dump.xls' file
        or die "Error: " . $dump->error;

    # second example
    $dump->data( \@data );
    $dump->dump( 'dump.xls', undef, { text_wrap => 0 } )
        or die "Error: " . $dump->error;

    # third example
    open my $fh, '>', 'foo.xls' or die $!;
    $dump->dump( $fh, \@data, { text_wrap => 0 } )
        or die "Error: " . $dump->error;

Instructs the object to dump out our 2D arrayref into an Excel file. On success returns 1 on failure returns either undef or an empty list depending on the context and the reason for failure will be available via error() method. The arguments are all optional, but the first two must be either set in dump() method or set prior calling dump() via their respective methods (see below). Arguments are as follows:

first argument

    $dump->data(\@data);
    $dump->file('dump.xls');
    $dump->dump;

    # or

    $dump->data(\@data);
    $dump->dump('dump.xls');
    
    # or
    
    open my $fh, '>', 'foo.xls' or die $!;
    $dump->dump($fh, \@data);

The first argument is a filename of the Excel file into which you want to dump your data, or an already opened filehandle for such a file. If set to undef, then the filename will be retrieved from the file() method; if that one is also undef, then dump() will error out.

second argument

    $dump->data(\@data);
    $dump->dump('dump.xls');

    # or

    $dump->dump('dump.xls', \@data);

Second argument is an arrayref of arrayrefs of the data that you want to dump; each element (that is an arrayref) represents a row of data (and elements of that [inner] arrayref are the cells). If set to undef, then the data will be retrieved from the data() method; if that one is also undef, then dump() will error out.

third argument

    $dump->dump('dump.xls', \@data, {
            text_wrap           => 1,
            calc_column_widths  => 1,
            width_multiplier    => 1,
            center_first_row    => 1,
        }
    );

The third argument takes a hashef and is completely optional. The hashref contains keys that are dump options. The following keys are valid:

text_wrap

    $dump->dump('dump.xls', \@data, {
            text_wrap => 0, # disable
        }
    );

Takes either true or false values. When set to a true value, newlines in the data will be interpreted as line wraping characters in the Excel file ( see set_text_wrap() format method in Spreadsheet::WriteExcel ). Defaults to: 1

calc_column_widths

    $dump->dump('dump.xls', \@data, {
            calc_column_widths => 0, # disable
        }
    );

Takes either true or false values. When set to a true value, the module will set the column widths to fit the largest piece of data that will be dumped into the column, but see the notes in the width_multiplier description below. If text_wrap is also set to a true value, then the module will first split each "cell" on new lines and calculate the width based on the length of the longest of those individual lines. Defaults to: 1

width_multiplier

    $dump->dump('dump.xls', \@data, {
            width_multiplier => 2,
        }
    );

Takes a positive number as a value. Applies only when calc_column_widths option (see above) is enabled. Since calculated width is the length() of the data, it may or may not match the width of the "Excel column size" depending on the font that you're using. This is a snippet of docs from Spreadsheet::WriteExcel:

    The width corresponds to the column width value that is specified
    in Excel. It is approximately equal to the length of a string in the 
    default font of Arial 10. Unfortunately, there is no way to specify 
    "AutoFit" for a column in the Excel file format. This feature is only 
    available at runtime from within Excel.

By setting width_multiplier to any positive number but 1, the length() of the data will be multiplied by width_multiplier and this gives you means to compensate for difference between font size and Excel column size. Defaults to: 1

center_first_row

    $dump->dump('dump.xls', \@data, {
            center_first_row => 0, # disable
        }
    );

Takes either true or false values. When set to a true value, will make the data in the first row of the dump to be center aligned; e.g. you can specify column names there. Defaults to: 1

file

    my $old_filename = $dump->file;
    $dump->file('new_excel.xls');

Returns currently set filename of the Excel file as a dump. Takes one optional argument, which is (when set) specifies new name of the Excel file. See description of the first argument to dump() method above.

data

    my $old_data = $dump->data;
    $dump->data( \@new_data );

Returns currently set data for the dumping. Takes one optional argument, which is (when set) specifies new data to dump. See description of the second argument to dump() method above.

error

    $dump->dump
        or die "Error: " . $dump->error;

Returns the reason for why dump() method failed.

EXAMPLE 1 (found in examples/dump.pl)

    #!/usr/bin/env perl
    
    use strict;
    use warnings;
    
    use Spreadsheet::DataToExcel;
    
    my @data = (
        [ qw/ID Time Number/ ],
        map [ $_, time(), rand() ], 1..10,
    );

    my $dump = Spreadsheet::DataToExcel->new;
    
    $dump->dump( 'dump.xls', \@data );
    
    print "Done! See dump.xls file\n";

EXAMPLE 2 (found in examples/interactive_dump.pl)

    #!/usr/bin/env perl
    
    use strict;
    use warnings;
    
    use Spreadsheet::DataToExcel;
    
    die "Usage: perl $0 file_for_the_dump.xls\n"
        unless @ARGV;
    
    my $dump = Spreadsheet::DataToExcel->new;
    
    $dump->file( shift );
    $dump->data([]);
    
    print "Enter column names separated by spaces:\n";
    push @{ $dump->data }, [ split ' ', <STDIN> ];
    
    {
        print "Enter a row of data separated by spaces or hit CTRL+D to dump:\n";
        $_ = <STDIN>;
        defined or last;
        push @{ $dump->data }, [ split ' ' ];
        redo;
    }
    
    $dump->dump( undef, undef, { text_wrap => 0 } )
        or die "Error: " . $dump->error;
    
    print "Done! See " . $dump->file . " file\n";

AUTHOR

'Zoffix, <'zoffix at cpan.org'> (http://haslayout.net/, http://zoffix.com/, http://zofdesign.com/)

BUGS

Please report any bugs or feature requests to bug-spreadsheet-datatoexcel at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Spreadsheet-DataToExcel. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.

    perldoc Spreadsheet::DataToExcel

You can also look for information at:

COPYRIGHT & LICENSE

Copyright 2009 'Zoffix, all rights reserved.

This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.