NAME

Spreadsheet::TieExcel - Perl extension for tie'ing Excel spreadsheets.

SYNOPSIS

use Spreadsheet::TieExcel;
tie $x, 'Spreadsheet::TieExcel::Scalar';
tie *XL, 'Spreadsheet::TieExcel::File', {row => 1, column => 1, width => 1, height => 5};
tie %x, 'Spreadsheet::TieExcel::Hash';
tie @x, 'Spreadsheet::TieExcel::Array', {row => 1, column => 1, width => 1, height => 5};

DESCRIPTION

This moduel allows tie'ing of Excel spreadsheets to arrays, files and (soon) hashes. Tied variables can then be read and written to normally. The data is read or written to an Excel spreadsheet transparently.

USAGE

Tie'ing

See documentation for the different types of variables below.

Selecting the range

The range which will be tied can be specified in different ways:

• by passing a valid Excel range to the tie function:

  $range = Win32::OLE->GetActiveObject('Excel.Application')->Selection;
  tie @xl, Spreadsheet::TieExcel::Array, $range;

• by passing it an array, or an arrayref containing row, column, and optionally sheet name.

  tie @xl, Spreadsheet::TieExcel::Array, 1, 1, 'Sheet3';

• by passing it a hashref, containing row, column, and optionally sheet name, width and height of range to be used

       tie @xl, Spreadsheet::TieExcel::Array, {
  	                          row => 1,
  	                          column => 1,
  	                          width => 4,
                                    sheet => 'Sheet3'
                                    };

• by default, in which case the current selection is tied.

Quirks

You can't, for now, write anywhere else than the active workbook, and Excel must be open for all this to work.

In the best M$ tradition, this and other quite obvious and necessary features will be available in some future version.

Tie'ing arrays

Usage

      use Spreadsheet::TieExcel::Array;

      tie @xl, 'Spreadsheet::TieExcel::Array';

      for (@xl) {
	      # whatever you want
      }

Quirks

You can't, for now, pop or push to a tie'd array. However, addressing farther than the selection's size will write farther along, in a row-by-row fashion. This doesn't however resize the array. I still have to decide whether it's a bug or a feature.

Tie'ing filehandles

Usage

      use Spreadsheet::TieExcel::File;

      tie *XL, 'Spreadsheet::TieExcel::File';

      while (<XL>) {
	      # whatever you want
      }

      print XL 'foo bar';

Quirks

If you write an array to the filehandle, cells will be written for the whole width of the array, regardless of the original width of the range selected. Also they will overwrite any previous data, so watch out where you write.

Tie'ing scalars

Usage

Messing around

After you've tied a scalar to an Excel cell, you use a tied'ed variable to move the pointer to the cell around, changing the underlying range and acting much the way you do with a mouse.

Easier shown than said:

use Spreadsheet::TieExcel;

tie $x, 'Spreadsheet::TieExcel::Scalar', 3, 2; # Tie cell at row 3, column 2

$x = 'a';                           # assign value to cell

print $x;                           # read value from cell

$X = tied $x;
$X << 1;                            # move pointer one cell to the left
                                    # $x is now tied to cell at row 3, column 2

$x = 'b';                           # set cell
print $x;                           # read cell

Moving around

You can change the cell being pointed to explicitly:

$X->move(10, 3);                    # move 10 rows down, 3 to the right

or you can use one of the overloaded methods provided:

$X << 1;                            # move left one column
$X >> 4;                            # move right four columns

$X + 5;                             # move down five rows
$X - 4;                             # move up four rows

Setting properties

You can change the properties of the underlying cell like this:

$X->set('Font', 'Italic', 1);          # Set the cell's font to italic

much like you would do with

$cell->{'Font'}->{'Italic'} = 1;

Worksheets are round

The worksheet is almost circular (actually a torus), when you move further than the top, you end up at the bottom, and moving to muuch to the left takes you to the rightmost column of the sheet.

For example:

use Spreadsheet::TieExcel;

tie $x, 'Spreadsheet::TieExcel::Scalar', 1, 1; # Tie cell at row 1, column 1

$X = tied $x;
$X << 1;                            # you are now at the rightmost column on the sheet

Tie'ing hashes

Usage

      use Spreadsheet::TieExcel::Hash;

      tie %xl, 'Spreadsheet::TieExcel::Hash';

      $xl{'foo'} = [12, 4];
      $xl{'foo'} = 'bar';
      for (keys %xl) {
	  print
      }

Quirks

You tie a hash to Excel named ranges. It's quite straightforward, only you can't set a value to a new hash element, unless you've assigned a range to it.

Therefore you do:

tie %xl, 'Spreadsheet::TieExcel::Hash';

$xl{'foo'} = [12, 4];              # $xl{'foo'} points to range at row 12, column 4 of the active sheet
$xl{'foo'} = 'bar';                # and now you can assign a value to it

That is, the first time you assign to a new element, you actually assign the range it refers to, and only thereafter you assign its value.

Author

Simone Cesano simonecesano@libero.it

Copyright

Copyright (c), Simone Cesano. All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.

cpanm Spreadsheet::TieExcel

perl -MCPAN -e shell
install Spreadsheet::TieExcel