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 and 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

• minimal size

• 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

Setting up data

reading data from a file

dumping a spreadsheet

Formulas

builtin functions

cell dependencies

circular dependencies

CELL and RANGE: ADDRESSING, NAMING

Address format

Slices

Names

OTHER SPREADSHEET

SPREADSHEEET Functions

Locking

Calculation control

State queries

SETTING CELLS

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

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 :-)

  Some stuff 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