NAME

Spreadsheet::Perl - Pure Perl implementation of a spreadsheet engine

SYNOPSIS

use Spreadsheet::Perl;
use Spreadsheet::Perl::Arithmetic ;

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

$ss->SetNames("TEST_RANGE" => 'A5:B8') ;
$ss{TEST_RANGE} = '7' ;

DefineSpreadsheetFunction('AddOne', \&AddOne) ;

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

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

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

...

DESCRIPTION

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

Spreadsheet::Perl is quite small but can do the the following:

set and get values from cells or ranges
cell private data
fetch/store callback
cell attributes access
cell/range fillers (auto-fill functionality)
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 its 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
Recalculate() / AUTOCALC
value caching to speed up formulas and 'volatile' cells
cell address offsetting functions
Automatic formula offsetting
Relative and fixed cell addresses
slice access
Perl variable mapping to a cell
some debugging tool (dump, dump table, dump to HTML, 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 spreadsheet. 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, relieving you from keeping things in synch
SP is 2 dimensional (or 3 or 4), specially 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-dimensional 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

For a more technical insight check:

http://www.cs.uno.edu/~markus/02_Courses/past/csci6990/6990.03_Spreadsheet.ppt.pdf

The Spreadsheet FAQ might be of use:

http://www.faqs.org/faqs/spreadsheets/faq/

and an interresting curiosa:

http://www.uq.net.au/detective/

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'
		  , DEBUG => { PRINT_FORMULA => 1} ;

reading, cell only, 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 => PerlFormula('$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" or confess "Couldn't read setup file '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
------------------------------------------------------------

reading and writing a spreadsheet from a file

Version 0.06 has, prototype, functionality to read and write spreadsheets.

use Spreadsheet::Perl ;

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

$ss->Read('ss_data.pl') ;

print $ss->DumpTable() ;

$ss->Write('generated_ss_data.pl') ;

undef $ss ;
untie %ss ;

$ss = tie %ss, "Spreadsheet::Perl" ;
$ss->Read('generated_ss_data.pl') ;

print $ss->DumpTable() ;

You can find a small example in tests/read_write.. See also: "Function definition" bellow.

Dumping a table

Håkon Nessjøen (author of Text::ASCIITable) was nice enough to contribute a module to dump the spreadsheet in table form.

The functionality can be access through two, equivalent, function names: DumpTable (an alias) and GenerateASCIITable. The functions take the following arguments:

1- a list of ranges within an array reference or 'undef' for the whole spreadsheet
2- a boolean, when set, the spreadsheet attributes are also displayed
3- options passed to Text::ASCIITable
4- arguments passed to Text::ASCIITable::draw

Most of the time you'll call DumpTable without argument or with the first argument set.

print $ss->DumpTable() ;

generates :

.----------------------------------------------------.
| @  | A   | B   | C   | D   | E   | F   | G   | H   |
|====================================================|
| 1  | A1  | B1  | C1  | D1  | E1  | F1  | G1  | H1  |
|----+-----+-----+-----+-----+-----+-----+-----+-----|
| 2  | A2  | B2  | C2  | D2  | E2  | F2  | G2  | H2  |
|----+-----+-----+-----+-----+-----+-----+-----+-----|
| 3  | A3  | B3  | C3  | D3  | E3  | F3  | G3  | H3  |
|----+-----+-----+-----+-----+-----+-----+-----+-----|
...
...
|----+-----+-----+-----+-----+-----+-----+-----+-----|
| 10 | A10 | B10 | C10 | D10 | E10 | F10 | G10 | H10 |
'----------------------------------------------------'

print $ss->DumpTable(['B4:C5', 'A2:B6', 'NAMED_RANGE']) ;

.-------------.
| @ | B  | C  |
|=============|
| 4 | B4 | C4 |
|---+----+----|
| 5 | B5 | C5 |
'-------------'

.-------------.
| @ | A  | B  |
|=============|
| 2 | A2 | B2 |
|---+----+----|
| 3 | A3 | B3 |
|---+----+----|
| 4 | A4 | B4 |
|---+----+----|
| 5 | A5 | B5 |
|---+----+----|
| 6 | A6 | B6 |
'-------------'

.-------------------------------------------------------.
| @ | A  | B  | C  | D  | E  | F  | G  | H  | I | J | K |
|=======================================================|
| 4 | A4 | B4 | C4 | D4 | E4 | F4 | G4 | H4 |   |   |   |
|---+----+----+----+----+----+----+----+----+---+---+---|
| 5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | H5 |   |   |   |
'-------------------------------------------------------'

print $ss->DumpTable
		(
		  undef
		, undef 
		, {
		    alignHeadRow => 'center',
		  , headingText  => 'Some Title'
		  }
		) ;

.------------------------------------------------------.
|                      Some Title                      |
|======================================================|
| @ |                     A                    | B | C |
|======================================================|
| 1 | datadatadatadatadatadatadatadatadatadata | B | B |
|---+------------------------------------------+---+---|
| 2 | datadatadatadatadatadatadatadatadatadata | B | B |
|---+------------------------------------------+---+---|
| 3 | datadatadatadatadatadatadatadatadatadata |   |   |
|---+------------------------------------------+---+---|
| 4 | datadatadatadatadatadatadatadatadatadata |   |   |
|---+------------------------------------------+---+---|
| 5 | datadatadatadatadatadatadatadatadatadata |   |   |
|---+------------------------------------------+---+---|
| 6 |                                          |   |   |
|---+------------------------------------------+---+---|
| 7 |                                          |   |   |
|---+------------------------------------------+---+---|
| 8 | C                                        |   |   |
'------------------------------------------------------'

It is possible to give a page width. if the page width is not set, the screen width is used. If there is no screen width available (redirecting to a file for example) 78 is used as a width.

print $ss->DumpTable(['A4:O5'], undef, {pageWidth => 40}) ;

.--------------------------------------------
| @ | A  | B  | C  | D  | E  | F  | G  | H  |
|============================================
| 4 | A4 | B4 | C4 | D4 | E4 | F4 | G4 | H4 |
|---+----+----+----+----+----+----+----+----+
| 5 | A5 | B5 | C5 | D5 | E5 | F5 | G5 | H5 |
'--------------------------------------------
'TEST' 1/4.

.--------------------------------
| @ | I | J | K | L | M | N | O |
|================================
| 4 |   |   |   |   |   |   |   |
|---+---+---+---+---+---+---+---|
| 5 |   |   |   |   |   |   |   |
'--------------------------------
'TEST' 2/4.

...

You can set the 'noPageCount' option if you don't want the page count.

See Text::ASCIITable.

CELL and RANGE: ADDRESSING, NAMING

Cells are index with a scheme I call baseAA (please let me know if it has a better name). A cell address is a combination of letters and a figure, ex: '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

Addresses 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

For a range, the order of the baseAA figures is important!

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

but

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

because formulas get regenerated 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:

my $ss = tie my %ss, "Spreadsheet::Perl" ;
@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" ;

Names must be upper case.

LABELING ROW AND COLUMN HEADERS

The spread cells are indexed from '1,1' which is 'A1' in baseAA. The column headers start at 'A0' to 'ZZZZ0'. The row header start at '0,1' to '0,n'. You can either use the previous notation or use '@' to represent 0 in baseAA thus '@1' represents the header for row 1.

$ss{A0} = 'column 1' ;

$ss{'@1'} = 'row 1' ; 
# or
$ss{'0,1'} = 'row 1' ;

OTHER SPREADSHEET

To use inter-spreadsheet 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} = PerlFormula('$ss->Sum("JULIETTE!A4") + $ss->Sum("ROMEO!B1:B2")') ;

SPREADSHEET Functions

Locking

Locking the spreadsheet:

tie my %ss, "Spreadsheet::Perl", LOCKED => 1 ;
$ss->Lock() ;
$ss->Lock(1) ;

Unlocking the spreadsheet:

$ss->Lock(0) ;

Locking a Range

Locking a range:

LockRange('A1:B6') ;
LockRange('A1:B6', 1) ;

Unlocking a range:

LockRange('A1:B6', 0) ;

Cache

Spreadsheet::Perl caches the result of the formulas and recalculates cell values only when needed.

Calculation control

Spreadsheet::Perl computes the value of a cell (see Cache above) when the cell is accessed. If a cell A1 depends on cell A2 and cell A2 is modified, the value of cell A1 is not updated until it is accessed. If you want to update all the cell (in need of being updated) use:

$ss->Recalculate() ;

This comes handy if you want to flush the result to a database linked to the spreadsheet

It is possible to force the recalculation of the spreadsheet every time a cell with dependent is set:

tie my %ss, "Spreadsheet::Perl", AUTOCALC => 1 ;
$ss->SetAutocalc() ;
$ss->SetAutocalc(1) ;

Turning off auto recalculation:

$ss->SetAutocalc(0) ;

AUTOCALC is set to 0 by default.

Function definition

Spreadsheet::Perl comes with a single formula function defined (Sum).

Spreadsheet::Perl uses perl arithmetics so all the functions available in perl are available to you. You can define your own functions.

sub AddOne
{
my $ss = shift ;
my $address = shift ;

return($ss->Get($address) + 1) ;
}

DefineSpreadsheetFunction('AddOne', \&AddOne) ;

$ss{A3} = PerlFormula('$ss->AddOne("A1") + $ss{A2}') ;

Sub AddOne is now available in all your spreadsheets.

DefineSpreadsheetFunction takes the following parameters:

1 - A function name
2 - A sub reference or undef if item 3 is defined
3 - A text representation for the function (for file serialization)
2 - A module name (for file serialization)

The sub will be passed a reference to the spreadsheet object as first argument. The other argument are those you pass to the function in your formula.

Function modules

If you implement more than a few formula functions, you may want to move those functions into a perl module. "use" Spreadsheet::Perl in your module and register your functions through DefineSpreadsheetFunction.

package MyPackageName ;

sub DoSomething{}

AddSpreadsheetFunction('DoSomething', \&DoSomething, undef, __PACKAGE__) ;

Later in a script:

use Spreadsheet::Perl ;
use MyPackageName ;

# DoSomething is now available within formulas
$ss{A1} = PF('$ss->DoSomething('A2:A3', 'arg2', 'arg3')') ;
...
$ss->Write('somefile.pl') ; # serializes the formula and "MyPackageName" module name in the file.

The saved file will now "use" MyPackageName automaticaly when you read the file.

Please contribute your functions to Spreadsheet::Perl.

Misc spreadsheet functions

SetName, sets the name of the spreadsheet object
GetName, returns the name of the spreadsheet object
GetCellList, returns the list of the defined cells
GetLastIndexes, returns the last column and the last row used
GetCellsToUpdate, returns the list of the cells needing update

SETTING AND READING CELLS

Cells have one value and attributes. Cells values are perl scalars, anything you can assign to a perl scalar can be assigned to a cell value (see bellow for the one exception). Attributes have different format and are handled by the spreadsheet.

Setting a value

Anything that can be assigned to a perl variable can be assigned to a cell with the exception of object rooted in "Spreadsheet::Perl" which are reserved and carry a special meaning.

$ss{A1} = 458_627 ;
$ss{A1} = undef ;
$ss{A1} = '' ;
$ss{A1} = function_call() ; # assign the value returned from the call
$ss{A1} = \&Function ;
$ss{A1} = \@_ ;

$ss{A1} = $object_within_spreadsheet_perl_hierarchy ; # this is valid but may (and will) carry a special meaning.

$ss->Set('A1', "some value') ; # OO style

locking

Cell locking is done through the LockRange function:

$ss->LockRange('A1') ;

Finding out the lock state of a cell:

$cell_is_locked = $ss->IsCellLocked('A1') ;

Formulas

cell dependencies

Cell dependencies are automatically handled by Spreadsheet::Perl. If a dependency is changed, the formula will be re-evaluated next time the cell, containing the formula, is accessed.

circular dependencies

If circular dependencies between cells exist, Spreadsheet::Perl will generate a dump of the cycle as well as a perl stack dump to help you debug your formulas. The following formulas:

$ss{'A1:A5'} = PerlFormula('$ss{"A2"}') ; #automatic address offsetting
$ss{A6} = PerlFormula('$ss{A1}') ;
print "$ss{A1}\n" ;

generate:

-----------------
Spreadsheet::Perl=HASH(0x813d234) 'TEST' Dependent stack:
-----------------
TEST!A1 : $ss->Get("A2")[main] cyclic_error.pl:18
TEST!A2 : $ss->Get("A3")[main] cyclic_error.pl:18
TEST!A3 : $ss->Get("A4")[main] cyclic_error.pl:18
TEST!A4 : $ss->Get("A5")[main] cyclic_error.pl:18
TEST!A5 : $ss->Get("A6")[main] cyclic_error.pl:18
TEST!A6 : $ss->Get("A1")[main] cyclic_error.pl:19
TEST!A1 : $ss->Get("A2")[main] cyclic_error.pl:18
-----------------

At cell 'TEST!A6' formula: $ss->Get("A1") defined at 'main cyclic_error.pl 19':
	Found cyclic dependencies! at /usr/local/lib/perl5/site_perl/5.8.0/Spreadsheet/Perl.pm line 242.
#error

setting a formula

Formulas can be written in different formats. The native format is perl code. There seems to be a consensus about what standard format the formulas should use, that format is called common format.

Native format

PerlFormula and PF (an alias to PerlFormula) take a string as argument. The string must be a valid Perl code.

$ss{'A1:A5'} = PerlFormula('$ss{"A2"}') ;

$ss{'A1'} = PerlFormula('ANY VALID PERL CODE') ;

It is also possible to use PerlFormula as a member function and define multiple formulas in one call

$ss->PerlFormula
	(
	  'B1'    => '$ss{A1} + $ss{A2}'
	, 'B2'    => '$ss{A4} + $ss{A3}'
	, 'B3:B5' => '$ss{A4} + $ss{A3}'
	) ;
	
$ss->{DEBUG}{INLINE_INFORMATION}++ ; # show the formulas in the table dump
print $ss->DumpTable() ;:

Variables available in a formula

The following variables are available in the formula:

$ss, a spreadsheet object reference
%ss, a hash tied to the spreadsheet object
$cell, the address of the cell for which the formula is evaluated

Automatic cell address offsetting

If a range is assigned a formula, the cell addresses within the formulas are automatically offseted, fixed address element can be protected by square brackets.

# formula 1
$ss{'C1:C2'} = PerlFormula('$ss->Sum("A1:A2")') ;

Formula definition (anchor'C1:C2' @ cell 'C1'): $ss->Sum("A1:A2")
generated formula => $ss->Sum("A1:A2")

Formula definition (anchor'C1:C2' @ cell 'C2'): $ss->Sum("A1:A2")
generated formula  => $ss->Sum("A2:A3")

# formula 2
$ss{'D1:E2'} = PerlFormula('$ss->Sum("[A]1:A[3]")') ;

Formula definition (anchor'D1:E2' @ cell 'D1'): $ss->Sum("[A]1:A[3]")
generated formula => $ss->Sum("A1:A3")

Formula definition (anchor'D1:E2' @ cell 'D2'): $ss->Sum("[A]1:A[3]")
generated formula => $ss->Sum("A2:A3")

Formula definition (anchor'D1:E2' @ cell 'E1'): $ss->Sum("[A]1:A[3]")
generated formula => $ss->Sum("A1:B3")

Formula definition (anchor'D1:E2' @ cell 'E2'): $ss->Sum("[A]1:A[3]")
generated formula => $ss->Sum("A2:B3")

common format

This is the format accepted by excel and gnumeric. I will _not_ implement that format because:

=SUM(IF(A2:A20=A2,IF(B2:B20=38,1,0)))

is about the ugliest a formula language can get. Is all this user friendly syntax only because someone thought it was too difficult to present a mutiline editor to the end user?

If Someone feels that the common format (or any other language) is more "appropriate" than Perl and contributes a translator, I'll be happy to add it to the distribution.

RangeValues

There are different way to assign values to a range.

$ss{'A1:A5'} = 5 ; # all the cells within the range have "5" as value.
@ss{'A1', 'A2', 'A3', 'A4', 'A5'} = (10 .. 15) ; # perl slice notation 
$ss{'A1:A5'} = RangeValues(10 .. 15) ;

$ss{'A1:A5'} = RangeValuesSub(\my_sub, $argument_1, $argument_2) ;

RangeValuesSub

RangeValuesSub is passed the following arguments:

1 - a sub reference
2 - an optional list of arguments

The sub is called, multiple times, to fill the cell of ranges. It is passed these arguments:

1 - a reference to the spreadsheet
2 - an anchor (the first cell of the range)
3 - the address of the cell to generate a value for
4 - the optional list of arguments passed to RangeValuesSub

RangeValuesSub can be used when the values are to be generated dynamically or could be used to create 'Auto-fill' functionality.

Setting formats

the cell formats are hold within a hash, you can set as many different formats as you wish. Your format can be a complex perl structure, Spreadsheet::Perl only handle the first level of the hash:

$ss{A1} = Format(ANSI => {HEADER => "blink"}) ;
$ss{A1} = Format(ANSI => {HEADER => "red_on_black"}) ; # override previous
$ss{A1} = Format(POD => {FOOTER => "B<>"}) ; # add this format to cell A1

The format data must be passed as a perl hash reference.

Setting Validators

a Validator is defined in this way:

$ss{'A1:A2'} = Validator('only letters', \&OnlyLetters) ;

Validator, removes all previously set validators and sets the validator passed as argument. Validator takes these arguments:

1 - a name
2 - a sub reference
3 - an optional list of arguments

A cell can have multiple validators. use ValidatorAdd to append new validators.

Validators are passed the following arguments:

1 - a reference to the spreadsheet
2 - the address of the cell to be set
3 - a reference to the cell to be set
4 - the optional list of arguments passed to Validator[Add]

The value is set if all the cell validators return true. Spreadsheet::Perl is silent, your validator has to give the user feedback.

Setting User data

You can store private data into the cell. It is out of limits for Spreadsheet::Perl. the user data is stored in a hash.

$ss{A1} = UserData(NAME => 'private data', ARRAY => ['hi']) ;

Setting fetch and store callbacks

You can map your own set of Fetch and Store data from/in a cell. You will be working with the spreadsheet internals.

Fetch callback

I recommend that you don't use this system to compute values depending on other cells; the dependency mechanism will still work but it is better to use formula so it will still work when row/columns deleting/inserting is implemented. This mechanism is still very useful when you need to access a value that changes between cell access and is not depending on other cells. The description field is displayed when generating a table and $ss->{DEBUG}{INLINE_INFORMATION} is set, that can be of a great help when debugging your spreadsheet.

$ss{A1} = FetchFunction('some description', \&MySub) ;

FetchFunction takes these arguments

1 - a descriptioin string
2 - a sub reference
3 - an optional list of arguments

The following arguments are passed to the fetch callback

1 - a reference to the spreadsheet
2 - the address of the cell
3 - the optional list of arguments passed to FetchFunction

Caching (volatile cells)

Spreadsheet::Perl caches cell values (and updates them when a dependency has changed). If you want a cell to return a different value every time it is accessed (when using AUTOCALC = 0 and Recalculate for example), you need to turn caching off for that cell.

${A1} = NoCache() ;

Store callback

You can also attach a 'store' sub to a cell. whenever the cell is assigned a value, your sub will be called.

$ss{'A1:A5'} = StoreFunction('description', \&StorePlus, 5) ;

StoreFunction takes the following arguments:

1 - a descriptioin string
2 - a sub reference
3 - an optional list of arguments to be passed when the callback is, well, called.

The callback is called with these arguments

1 - a spreadsheet object reference
2 - the address of the cell to set
3 - the value to store
4 - the, optional, arguments passed to StoreFunction

Your store callback must store the data directly in the spreadsheet data structure without calling the Store/Set functions. You can find a typical implementation in the examples.s

Perl scalar mapping

Few problems fit the two dimensional mapping spreadsheets use. For a given project, you may already have data structure that you want to perform calculation on (thought spreadsheet). Mapping from the domain structure and back is time consuming, error prone and borring. Even if that process cannot be eliminated, Spreadsheet::Perl can do half the job. Here is an example:

my $variable = 25 ;

$ss{A1} = Ref('description', \$variable) ;
$ss{A2} = PerlFormula('$ss{A1}') ;

print "$ss{A1} $ss{A2}\n" ; # fetch the data from the scalar variable

$ss{A1} = 52 ; # set the scalar
  
print "\$variable = $variable\n" ;

Ref can be called as attribute creator (as above) or as a spreadsheet member (as bellow).

  $ss->Ref
	(
	'description',
	A1      => \($struct->{something}), 
	A2      => \$variable,
	'A3:A5' => \$variable
	) ;

Ref accepts reference to scalar only (as of version 0.04, this could be changed if needed)

Removing the mapping

Simply delete the cell:

delete ${A1} ;

Store on fetch

You can direct Spreadsheet::Perl to call the 'store callback' of a cell everytime the cell is fetched. What is this good for? Here is an example:

$ss{A3} = PF('$ss{A1} + $ss{A2}') ;

$ss{A3} = StoreOnFetch() ; # set the store on fetch attribute for this cell

$ss{A3} = StoreFunction('formula to db', \&MyStoreCallback) ;

$ss{'A1:A2'} = 10 ;
$ss->Recalculate() ;

This lets you calculate the value of a cell through a formula and store that value wherever you wish to. For example a database, a perl scalar or even mail the value.

Reading values

Use the normal perl assignment:

my $value = $ss{A1} ;

You can read multiple values using slices:

my ($value1, $value2) = @ss{'A1', 'A2'} ;

Reading range values

I you want to read all the values contained in a range, use the following syntax:

my $values = $ss{'A1:A10'} ;

An array reference is returned. It contains the values ordered by rows first then by columns.

Copying cell values from a spreadsheet to another spreadsheet or to another hash

Use Perl hash slices:

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

my @cells = qw(A1 B6 C4) ;

@spreadsheet{@cells} = qw( first second third ) ;

my %copy_hash ;
@copy_hash{@cells} =  @spreadsheet{@cells} ;

print DumpTree(\%copy_hash, 'CopyHash:') ;

Reading attributes

Cell attributes are handled internally by Spreadsheet::Perl, some of those attributes need to be synchronized or influence the way Spreadsheet::Perl handles the cell. You still get the attributes through an extended address. This is easier explained with an example:

$ss{A1} = UserData(FIRST => 1, SECOND => 2) ; # stored in a hash
$user_data_hash = $ss{A1.USER_DATA} ;

The attributes you can use are:

FORMAT
USER_DATA

This way of accessing the attributes, and which attributes exist, may change in the future or it may not.

OUTPUT

HTML

As of version 0.04, there is a simple way to generate HTML tables. It uses the Data::Table module. This is an interim solution and it is limited but it might just do what you want.

...
print $ss->GenerateHtml() ;
$ss->GenerateHtmlToFile('output_file_name.html') ;

See "Dumping a table".

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 passed as overrides to Data::TreeDumper

It returns a string containing the dump.

Debug handle

All debug output is done through the handle set in $ss{DEBUG}{HANDLE}. It is set to STDERR but could be set to a file or other logging facilities.

Debug flags

$ss->{DEBUG}

I don't removes the flags I create while developing Spreadsheet::Perl if I think it can be useful to the user (that's me at least). The following flags exist:

$ss->{DEBUG}{SUB}++ ; # show whenever a value has to be calculated
$ss->{DEBUG}{FETCHED}++ ; # counts how many times the cell is fetched
$ss->{DEBUG}{STORED}++ ; # counts how many times the cell is stored

$ss->{DEBUG}{PRINT_FORMULA}++ ; # show the generated formulas
$ss->{DEBUG}{INLINE_INFORMATION}++ ; #inline cell information in the table dump

$ss->{DEBUG}{DEFINED_AT}++ ; # show where the cell has been defined
$ss->{DEBUG}{ADDRESS_LIST}++ ; # shows the generated address lists
$ss->{DEBUG}{FETCH_FROM_OTHER}++ ; # show when an inter spreadsheet value is fetched
$ss->{DEBUG}{DEPENDENT_STACK}++ ; # show the dependent stack every time a value is fetched
$ss->{DEBUG}{DEPENDENT}++ ; # store information about dependent and show them in dump
$ss->{DEBUG}{VALIDATOR}++ ; # display calls to all validators in spreadsheet

$ss->{DEBUG}{FETCH}++ ; # shows when a cell value is fetched
$ss->{DEBUG}{STORE}++ ; # shows when a cell value is stored
$ss->{DEBUG}{FETCH_TRIGGER}{'A1'}++ ; # displays a message when 'A1' is fetched
$ss->{DEBUG}{FETCH_TRIGGER}{'A1'} = sub {my ($ss, $address) = @_} ; # calls the sub when 'A1' is fetched
$ss->{DEBUG}{FETCH_TRIGGER_HANDLER} = sub {my ($ss, $address) = @_} ; # calls sub when any trigger is fetched and no specific sub exists
$ss->{DEBUG}{STORE_TRIGGER}{'A1'}++ ; # displays a message when 'A1' is stored
$ss->{DEBUG}{STORE_TRIGGER}{'A1'} = sub {my ($ss, $address) = @_} ; # calls the sub when 'A1' is stored
$ss->{DEBUG}{STORE_TRIGGER_HANDLER} = sub {my ($ss, $address, $value) = @_} ; # calls sub when any trigger is stored and no specific sub exists

more will be added when the need arises.

$ss->{DEBUG_MODULE}

This flag 'family' is reserved for modules that are not part of the distribution. The 'Arithmetic.pm' module (which is a part of the distribution at version 0.04 will be made available as a separate package) includes these lines:

  if(exists $ss->{DEBUG_MODULE}{ARITHMETIC_SUM})
	  {
	  print "Sum: $current_address => $cell_value\n" ;
	  }

TODO

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:

more tests, automatic tests. Test on Win32 platform.
perl debugger support
Row/column/spreadsheet default values.
R1C1 Referencing
database interface (a handful of functions at most)
Arithmetic functions (only Sum is implemented), statistic functions
example of interface to the Inline module so you can write real fast functions in C
printing, exporting
importing from other spreadsheets
complex stuff (I am working on this but Gnumeric and excel do diffrent things)
- Insertion of rows and columns
- Deletion of rows and columns
- Sorting
a GUI (curses, tk, wxWindows, cgi, Prima) 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 or want to influence it's development, mail me! All hints, tips, flames and wishes are welcome at <nadim@khemir.net>.

DEPENDENCIES

Spreadsheet::ConvertAA.

Data::TreeDumper.

Text::ASCIITable.

Some examples need these:

Prima.

Data::Table.

3 POD Errors

The following errors were encountered while parsing the POD:

Around line 916:: Non-ASCII character seen before =encoding in ' for'. Assuming CP1252
Around line 1440:: You forgot a '=back' before '=head1'
Around line 1544:: =back without =over

To install Spreadsheet::Perl, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Spreadsheet::Perl

CPAN shell

perl -MCPAN -e shell
install Spreadsheet::Perl

For more information on module installation, please visit the detailed CPAN module installation guide.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)