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. 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. I found no spreadsheet modules on CPAN (programmers tend to think a spreadsheet is not 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 of 500 lines of code for a functional spreadsheeet. It took a few days to get something viable and it was just under 5OO lines. When debuggin help kicked in the module became a bit bigger, still Spreadsheet::Perl is quite minimal in size and can do the the folowwing:

- set and get values from cells or ranges - cell private data - 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 - cells can be assigned a sub reference and be non-caching. this could be used for reading from a database - split in many small modules so you pay only for what you need - some debugging tool (dump, formula stack trace, ...)

All this under 1500 lines of code, the biggest module being just under 700 lines. Perl rocks.

Look at the 'tests' directory for some examples. =head1 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).