NAME

Spreadsheet::Perl - Pure Perl implementation of a spreadsheet

SYNOPSIS

use Spreadsheet::Perl;
use Spreadsheet::Perl::Formula ;
use Spreadsheet::Perl::Format ;
...

tie my %ss, "Spreadsheet::Perl"
my $ss = tied %ss ;

$ss->SetRangeName("TestRange", 'A5:B8') ;
$ss{TestRange} = '7' ;

$ss->DefineFunction('AddOne', \&AddOne) ;

$ss{A3} = Spreadsheet::Perl::Formula('$ss->AddOne("A5") + $ss{A5}') ;
print "A3 formula => " . $ss->GetFormulaText('A3') . "\n" ;
print "A3 = $ss{A3}\n" ;

$ss{'ABC1:ABD5'} = '10' ;

$ss{A4} = Spreadsheet::Perl::Formula('$ss->Sum("A5:B8", "ABC1:ABD5")') ;
print "A4 = $ss{A4}\n" ;

...

DESCRIPTION

Spreadsheet::Perl is a pure Perl implementation of a spreadsheet.

Spreadsheet::Perl is minimal in size but can do the the folowwing:

• set and get values from cells or ranges

• cell private data

• fetch/store callback

• cell attributes access

• cell/range fillers/"wizards"

• set formulas (pure perl)

• compute the dependencies between cells

• formulas can fetch data from multiple spreadsheets and the dependencies still work

• checks for circular dependencies

• debugging triggers

• has a simple architecture for expansion

• has a simple architecture for debugging (and some flags are already implemented)

• can read it's data from a file

• supports cell naming

• cell and range locking

• input validators

• cell formats (pod, html, ...)

• can define spreadsheet functions from the scripts using it or via a new module of your own

• AUTOCALC ON/OFF, Recalculate()

• value caching to speed up formulas and 'volatile' cells

• cell address offseting functions

• Automatic formula offseting

• Relative and fixed cell addresses

• slice access

• some debugging tool (dump, formula stack trace, ...)

Look at the 'tests' directory for some examples.

DRIVING FORCE

Why

I found no spreadsheet modules on CPAN (I see a spreadsheet as a programming tool). The idea that it would be very easy to implement in perl kept going round in my head. I put the limit at 500 lines of code for a functional spreadsheeet. It took a few days to get something viable and it was just under 5OO lines.

I you have an application that takes some input and does calculation on them, chances are that implementing it through a spreadsheet will make it more maintainable and easier to develop. Here are the reasons (IMO) why:

• Spreadsheet programming (SP) is data oriented and this is what programming should be more often.

• SP is encapsulating. The processing is "hidden"behind the cell value in form of formulas.

• SP is encapsulating II. The data dependencies are automatically computed by the spreadsheet, releaving you from keeping things in synch

• SP is 2 dimensional (or 3 or 4 four that might not be easier for that), specialy if you have a gui for it.

• If you have a gui, SP is visual programming and visual debugging as the spreadsheet is the input and the dump of the data. The possibility to to show a multi-dimentional dependency is great as is the fact that you don't need to look around for where things are defined (this is more about visual programming but still fit spreadsheets as they are often gui based)

• SP allows for user customization

How

I want Spreadsheets::Perl to:

• Be Perl, be Perl, be fully Perl

• Be easy to develop, I try to implement nothing that is already there

• Be easy to expand

• Be easy to use for Perl programmers

CREATING A SPREADSHEET

Spreadsheet perl is implemented as a tie. Remember that you can use hash slices (I 'll give some examples). The spreadsheet functions are accessed through the tied object.

Simple creation

use Spreadsheet::Perl ;
tie my %ss, "Spreadsheet::Perl" ; 
my $ss = tied %ss ; # needed to access the spreadsheet functions.

Setting up data

Setting the cell data

  use Spreadsheet::Perl ;
  tie my %ss, "Spreadsheet::Perl"
		, CELLS =>
				{
				  A1 =>
						{
						VALUE => 'hi'
						}
					
				, A2 =>
						{
						VALUE => 'there'
						#~ or
						#~ FORMULA => '$ss{A1}'
						}
				} ;

Setting the cell data, simple way

use Spreadsheet::Perl ;
tie my %ss, "Spreadsheet::Perl"
@ss{'A1', 'B1:C2', 'A8'} = ('A', 'B', 'C');

Setting the spreadsheet attributes

  use Spreadsheet::Perl ;
  tie my %ss, "Spreadsheet::Perl"
		  , NAME => 'TEST'
		  , AUTOCALC => 0
		  , DEBUG => { PRINT_FORMULA => 1} ;

reading data from a file

<- start  of ss_setup.pl ->
# how to compute the data

sub OneMillion
{
return(1_000_000) ;
}

#-----------------------------------------------------------------
# the spreadsheet data
#-----------------------------------------------------------------
A1 => 120, 
A2 => sub{1},
A3 => Formula('$ss->Sum("A1:A2")'),

B1 => 3,

c2 => "hi there",

D1 => OneMillion()

<- end of ss_setup.pl ->

use Spreadsheet::Perl ;
tie my %ss, "Spreadsheet::Perl", NAME => 'TEST' ;
%ss = do "ss_setup.pl" ;

dumping a spreadsheet

Use the Dump function (see Debugging):

my $ss = tied %ss ;
print $ss->Dump() ;

Generates:

------------------------------------------------------------
Spreadsheet::Perl=HASH(0x825540c) 'TEST' [3550 bytes]


Cells:
|- A1
|  `- VALUE = 120
|- A2
|  `- VALUE = CODE(0x82554d8)
|- A3
|  |- ANCHOR = A3
|  |- FETCH_SUB = CODE(0x825702c)
|  |- FETCH_SUB_ARGS
|  |- FORMULA = Object of type 'Spreadsheet::Perl::Formula'
|  |  `- 0 = $ss->Sum("A1:A2")
|  |- GENERATED_FORMULA = $ss->Sum("A1:A2")
|  `- NEED_UPDATE = 1
|- B1
|  `- VALUE = 3
|- C2
|  `- VALUE = hi there
`- D1
   `- VALUE = 1000000

Spreadsheet::Perl=HASH(0x825540c) 'TEST' dump end
------------------------------------------------------------

CELL and RANGE: ADDRESSING, NAMING

Cells are index with a scheme I call baseAA1 (please let me know if it has a better name). The cel address is a combinaison of letters and a figure, ie 'A1', 'BB45', 'ABDE15'.

BaseAA figures match /[A-Z]{1,4}/. see Spreadsheet::ConvertAA. There is no limit on the numeric figure. Spreadsheet::Perl is implemented as a hash thus allowing for sparse spreadsheets.

Address format

Adresses are composed of:

• an optional spreadsheet name and '!'. ex: 'TEST!'

• a baseAA1 figure. ex 'A1'

• a ':' followed by a baseAA1 figure for ranges. ex: ':A5'

The following are valid addresses: A1 TEST!A1 A1:BB5 TESTA5:CE43

the order of the baseAA figures is important!

$ss{'A1:D5'} = 7; is equivalent to $ss{'D5:A1'} = 7; 

but

$ss{'A1:D5'} = Formula('$ss{H10}'); is NOT equivalent to $ss{'D5:A1'} = Formula('$ss{H10}'); 

because formulas get recalculated for each cell. Spreadsheet::Perl goes from the first baseAA figure to the second one by iterating the row, then the column.

it is also possible to index cells with numerals only: $ss{"1,7"}. Remember that A is 1 and there are no zeros.

Names It is possible to give a name to a cell or to a range:

tie my %ss, "Spreadsheet::Perl" ;
my $ss = tied %ss ;
@ss{'A1', 'A2'} = ('cell A1', 'cell A2') ;

$ss->SetCellName("first", "A1") ;
print  $ss{first} . ' ' . $ss{A2} . "\n" ;

$ss->SetRangeName("first_range", "A1:A2") ;
print  "First range: @{$ss{first_range}}\n" ;

OTHER SPREADSHEET

To use interspreadsheet formulas, you need to make the spreadsheet aware of the other spreadsheets by calling the AddSpreadsheet function.

tie my %romeo, "Spreadsheet::Perl", NAME => 'ROMEO' ;
my $romeo = tied %romeo ;

tie my %juliette, "Spreadsheet::Perl", NAME => 'JULIETTE' ;
my $juliette = tied %juliette ;

$romeo->AddSpreadsheet('JULIETTE', $juliette) ;
$juliette->AddSpreadsheet('ROMEO', $romeo) ;

$romeo{'B1:B5'} = 10 ;

$juliette{A4} = 5 ;
$juliette{A5} = Formula('$ss->Sum("JULIETTE!A4") + $ss->Sum("ROMEO!B1:B2")') ; 

SPREADSHEEET Functions

Locking

Calculation control

State queries and debugging

SETTING CELLS

Setting and reading cells is done in two diffrent ways. I like the way it looks now but it might change in the (near) future.

Formulas

builtin functions

cell dependencies

circular dependencies

Setting a value

RangeValues

Setting a formula

Caching

Setting a format

Setting fetch and store callbacks

Setting Validators

Setting User data

READING CELLS

Reading values

Reading internal data

Reading user data

Debugging

Dump

The Dump function, err, dumps the spreadsheet. It takes the following arguments:

• an address list withing an array reference or undef. ex: ['A1', 'B5:B8']

• a boolean. When set, the spreadsheet attributes are displayed

• an optional hash reference pased as overrides to Data::TreeDumper

If Data::TreeDumper is not installed, Data::Dumper is used.I exclusively use Data::TreeDumper so I never look at the dumps generated through Data::Dumper. It will certainly look ugly or might even be broken. Install Data::TreeDumper, it's worth it (I've written it so I have to force you to try it :-)

TODO

Unfortunately there is still a lot to do (the basics are there) and I have the feeling I will not get the time needed. If someone is willing to help or take over, I'll be glad to step aside.

Here are some of the things that I find missing, this doesn't mean all are good ideas:

• documentation, test (working on it)

• perl debugger support à la PBS

• Row/column/spreadsheet default values.

• R1C1 Referencing

• database interface (a handfull of functions at most)

• WWW interface

• Arithmetic functions (only Sum is implemented), statistic functions

• printing, exporting

• importing from other spreadsheets

• more serious file reading and file writting

• complex stuff (fixing one fixes the other)

  • Insertion of rows and columns

  • Deletion of rows and columns

  • Sorting

• a gui (curses, tk, wxWindows) would be great!

• a nice logo :-)

Lots is available on CPAN, just some glue is needed.

AUTHOR

Khemir Nadim ibn Hamouda. <nadim@khemir.net>

Copyright (c) 2004 Nadim Ibn Hamouda el Khemir. All rights
reserved.  This program is free software; you can redis-
tribute it and/or modify it under the same terms as Perl
itself.

If you find any value in this module, mail me! All hints, tips, flames and wishes are welcome at <nadim@khemir.net>.

DEPENDENCIES

Spreadsheet::ConvertAA.

Data::TreeDumper is used if found (I recommend installing it to get nice dumps).

cpanm Spreadsheet::Perl

perl -MCPAN -e shell
install Spreadsheet::Perl