NAME

    Spreadsheet::SimpleExcel - Create Excel files with Perl

VERSION

    version 1.93

SYNOPSIS

      use Spreadsheet::SimpleExcel;
    
      binmode(\*STDOUT);
      # data for spreadsheet
      my @header = qw(Header1 Header2);
      my @data   = (['Row1Col1', 'Row1Col2'],
                    ['Row2Col1', 'Row2Col2']);
    
      # create a new instance
      my $excel = Spreadsheet::SimpleExcel->new();
    
      # add worksheets
      $excel->add_worksheet('Name of Worksheet',{-headers => \@header, -data => \@data});
      $excel->add_worksheet('Second Worksheet',{-data => \@data});
      $excel->add_worksheet('Test');
    
      # add a row into the middle
      $excel->add_row_at('Name of Worksheet',1,[qw/new row/]);
    
      # sort data of worksheet - ASC or DESC
      $excel->sort_data('Name of Worksheet',0,'DESC');
    
      # remove a worksheet
      $excel->del_worksheet('Test');
    
      # sort worksheets
      $excel->sort_worksheets('DESC');
    
      # create the spreadsheet
      $excel->output();
    
      # print sheet-names
      print join(", ",$excel->sheets()),"\n";
    
      # get the result as a string
      my $spreadsheet = $excel->output_as_string();
    
      # print result into a file and handle error
      $excel->output_to_file("my_excel.xls") or die $excel->errstr();
      $excel->output_to_file("my_excel2.xls",45000) or die $excel->errstr();
    
      ## or
    
      # data
      my @data2  = (['Row1Col1', 'Row1Col2'],
                    ['Row2Col1', 'Row2Col2']);
    
      my $worksheet = ['NAME',{-data => \@data2}];
      # create a new instance
      my $excel2    = Spreadsheet::SimpleExcel->new(-worksheets => [$worksheet]);
    
      # add headers to 'NAME'
      $excel2->set_headers('NAME',[qw/this is a test/]);
      # append data to 'NAME'
      $excel2->add_row('NAME',[qw/new row/]);
    
      $excel2->output();
      
      $excel2->output_to_XML('test.xml');
    
      ## create XLSX
      my $worksheet3 = [ 'NAME', { -data => \@data } ];
      my $file3      = 'test.xlsx';
      
      # create a new instance
      my $excel3 = Spreadsheet::SimpleExcel->new(
          -worksheets => [$worksheet3],
          -filename   => $file3,
          -format     => 'xlsx',
      );
      
      # add headers to 'NAME'
      $excel3->set_headers('NAME',[qw/this is a test/]);
      
      $excel3->output_to_file();

DESCRIPTION

    Spreadsheet::SimpleExcel simplifies the creation of excel-files in the
    web. It does provide simple cell-formats, but only three types of
    formats (to keep the module simple).

METHODS

    Added in version 1.4:

    If you want a method to do the functionality for the last inserted
    worksheet (current sheet), you don't have to pass the title as a
    parameter for the method.

    So now you can do something like this:

      $excel->add_worksheet("Test");
      $excel->add_row(\@data);
      $excel->sort_date($column_idx);

    This leads to more usability.

 new

      # create a new instance
      my $excel = Spreadsheet::SimpleExcel->new();
    
      # or
    
      my $worksheet = ['NAME',{-data => ['This','is','an','Test']}];
      my $excel2    = Spreadsheet::SimpleExcel->new(-worksheets => [$worksheet]);
    
      # to create a file
      my $filename = 'test.xls';
      my $excel = Spreadsheet::SimpleExcel->new(-filename => $filename);
      
      #if a file > 7 MB should be created
      $excel = Spreadsheet::SimpleExcel->new(-big => 1);

    If -big is set to true, Spreadsheet::WriteExcel::Big is required!

 add_worksheet

      # add worksheets
      $excel->add_worksheet('Name of Worksheet',{-headers => \@header, -data => \@data});
      $excel->add_worksheet('Second Worksheet',{-data => \@data});
      $excel->add_worksheet('Test');

    The first parameter of this method is the name of the worksheet and the
    second one is a hash with (optional) information about the headlines
    and the data. No duplicate worksheets allowed.

 del_worksheet

      # remove a worksheet
      $excel->del_worksheet('Test');

    Deletes all worksheets named like the first parameter

 add_row

      # append data to 'NAME'
      $excel->add_row('NAME',[qw/new row/]);

    Adds a new row to the worksheet named 'NAME'

 add_row_at

      # add a row into the middle
      $excel->add_row_at('Name of Worksheet',1,[qw/new row/]);

    This method inserts a row into the existing data

 sort_data

      # sort data of worksheet - ASC or DESC
      $excel->sort_data('Name of Worksheet',0,'DESC');

    sort_data sorts the rows. All sorts for one worksheet are combined, so

      $excel->sort_data('Name of Worksheet',0,'DESC');
      $excel->sort_data('Name of Worksheet',1,'ASC');

    will sort the column 0 first and then (within this sorted data) the
    column 1.

 reset_sort

      $excel->reset_sort('Name of Worksheet');

    The data won't be sorted, the data are in original order instead.

 set_headers

      # add headers to 'NAME'
      $excel->set_headers('NAME',[qw/this is a test/]);

    set the headers for the worksheet named 'NAME'

 errstr

    returns error message.

 sort_worksheets

      # sort worksheets
      $excel->sort_worksheets('DESC');

    sorts the worksheets in DESCending or ASCending order.

 output

      $excel2->output();

    prints the worksheet to the STDOUT and prints the Mime-type
    'application/vnd.ms-excel'.

 output_as_string

      # get the result as a string
      my $spreadsheet = $excel->output_as_string();

    returns a string that contains the data in excel-format

 output_to_file

      # print result into a file [output_to_file(<filename>,<lines>)]
      $excel->output_to_file("my_excel.xls");
      $excel->output_to_file("my_excel2.xls",45000) or die $excel->errstr();

    prints the data into a file. The data will be printed into more
    worksheets, if the number of rows is greater than <lines> (default
    32000).

 output_to_XML

      $excel2->output_to_XML('test.xml');

    prints the data into a XML file.

 sheets

      $ref = $excel->sheets();
      @names = $excel->sheets();

    In listcontext this subroutines returns a list of the names of sheets
    that are in $excel, in scalar context it returns a reference on an
    Array.

 set_headers_format

      # set formats for headers of 'NAME'
      # first col 'string', second col 'number', third col default format, fourth col 'number'
      $excel2->set_headers_format('NAME',['s','n',undef,'n']);

    sets the headers formats for a specified worksheet. If formats are
    commited, the default format is set. Default format is set by
    Spreadsheet::WriteExcel

 set_data_format

      # set formats for headers of 'NAME'
      # first col 'string', second col 'number', third col default format, fourth col 'number'
      $excel2->set_data_format('NAME',['s','n',undef,'n']);

    sets the data formats for a specified worksheet. If formats are
    commited, the default format is set. Default format is set by
    Spreadsheet::WriteExcel

 current_sheet

      $excel->add_worksheet('Testtitle');
      print $excel->current_sheet;

    returns the title of the current worksheet.

EXAMPLES

 PRINT ON STDOUT

      #! /usr/bin/perl
    
      use strict;
      use warnings;
      use Spreadsheet::SimpleExcel;
    
      binmode(\*STDOUT);
      # data for spreadsheet
      my @header = qw(Header1 Header2);
      my @data   = (['Row1Col1', 'Row1Col2'],
                    ['Row2Col1', 'Row2Col2']);
    
      # create a new instance
      my $excel = Spreadsheet::SimpleExcel->new();
    
      # add worksheets
      $excel->add_worksheet('Name of Worksheet',{-headers => \@header, -data => \@data});
      $excel->add_worksheet('Second Worksheet',{-data => \@data});
      $excel->add_worksheet('Test');
    
      # add a row into the middle
      $excel->add_row_at('Name of Worksheet',1,[qw/new row/]);
    
      # sort data of worksheet - ASC or DESC
      $excel->sort_data('Name of Worksheet',0,'DESC');
    
      # remove a worksheet
      $excel->del_worksheet('Test');
    
      # create the spreadsheet
      $excel->output();

 RECEIVE DATA AS A SCALAR

      #!/usr/bin/perl
    
      use strict;
      use warnings;
      use Spreadsheet::SimpleExcel;
    
      # data
      my @data2  = (['Row1Col1', 'Row1Col2'],
                    ['Row2Col1', 'Row2Col2']);
    
      my $worksheet = ['NAME',{-data => \@data2}];
      # create a new instance
      my $excel2    = Spreadsheet::SimpleExcel->new(-worksheets => [$worksheet]);
    
      # add headers to 'NAME'
      $excel2->set_headers('NAME',[qw/this is a test/]);
      # append data to 'NAME'
      $excel2->add_row('NAME',[qw/new row/]);
    
      # receive as string
      my $string = $excel2->output_as_string();

 PRINT INTO FILE

      #! /usr/bin/perl
    
      use strict;
      use warnings;
      use Spreadsheet::SimpleExcel;
    
      # data
      my @data2  = (['Row1Col1', 'Row1Col2'],
                    ['Row2Col1', 'Row2Col2']);
    
      my $worksheet = ['NAME',{-data => \@data2}];
      # create a new instance
      my $excel2    = Spreadsheet::SimpleExcel->new(-worksheets => [$worksheet]);
    
      # add headers to 'NAME'
      $excel2->set_headers('NAME',[qw/this is a test/]);
      # append data to 'NAME'
      $excel2->add_row('NAME',[qw/new row/]);
    
      # print into file
      $excel2->output_to_file("my_excel.xls");

 PRINT INTO FILE (break worksheets)

      #! /usr/bin/perl
    
      use strict;
      use warnings;
      use Spreadsheet::SimpleExcel;
    
      # create a new instance
      my $excel    = Spreadsheet::SimpleExcel->new();
    
      my @header = qw(Header1 Header2);
      my @data   = (['Row1Col1', 'Row1Col2'],
                    ['Row2Col1', 'Row2Col2']);
      for(0..70000){
        push(@data,[qw/1 2 4 6 8/]);
      }
      # add worksheets
      $excel->add_worksheet('Name of Worksheet',{-headers => \@header, -data => \@data});
      $excel->add_row('Name of Worksheet',[qw/1 2 3 4 5/]);
    
      # print into file
      $excel->output_to_file("my_excel.xls",10000);

DEPENDENCIES

    This module requires Spreadsheet::WriteExcel and IO::Scalar

SEE ALSO

    Spreadsheet::WriteExcel

    IO::Scalar

    IO::File

    XML::Writer

AUTHOR

    Renee Baecker <reneeb@cpan.org>

COPYRIGHT AND LICENSE

    This software is Copyright (c) 2015 by Renee Baecker.

    This is free software, licensed under:

      The Artistic License 2.0 (GPL Compatible)