The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
NAME
    Spreadsheet::Engine - Core calculation engine for a spreadsheet

SYNOPSIS
      use Spreadsheet::Engine;
  
      my $sheet = Spreadsheet::Engine->new;
      my $sheet = Spreadsheet::Engine->load_data([@data]);

      $sheet->execute('set A1 value n 2');
      $sheet->execute('set A2 value n 4');
      $sheet->execute('set A3 formula SUM(A1:A2)');
      $sheet->recalc;

      my $data = $sheet->raw;
      print $data->{datavalues}{A3}; # 6

DESCRIPTION
    This provides back-end spreadsheet functionality for creating a sheet,
    setting cells to have value or formulae, and performing all necessary
    calculations. There is no front-end UI provided - this is purely the
    calculation engine.

    Over 110 spreadsheet functions are provided: see
    Spreadsheet::Engine::Functions for the full list.

METHODS
  new
      my $sheet = Spreadsheet::Engine->new;

    Instantiate a new blank spreadsheet.

  load_data
      my $sheet = Spreadsheet::Engine->load_data([@data]);

    Instantiate a sheet from lines of data in the saved file format (see
    Spreadsheet::Engine::Sheet for doumentation>)

  execute
      $sheet->execute('set A1 value n 2');
      $sheet->execute('set A2 value n 4');
      $sheet->execute('set A3 formula SUM(A1:A2)');

    Execute the given command against the sheet. See
    Spreadsheet::Engine::Sheet for documentation of commands.

  recalc
      $sheet->recalc;

    Recalculate the values for all formulae in the sheet. This never happens
    automatically - it must be explicitly called.

  raw
      my $data = $sheet->raw;
      print $data->{datavalues}{A3}; # 6

    Access the raw datastructure for the sheet. This is a temporary method
    until we provide proper accessors to the underlying data.

WARNING
    Although the core underlying code is relatively mature and featureful,
    there will be significant interface changes and refactoring going
    forward with this version. There are very few automated tests as yet, so
    this process is likely to introduce bugs. Please pay close attention to
    the CHANGES file if you upgrade this package.

OPEN FORMULA SPECIFICATION
    Spreadsheet::Engine attemps to conform as closely as possible to the
    Open Formula specification and provide all the features of the "Small"
    group as defined there. Divergences from that are detailed below. It is
    hoped to add the extra functionality of the "Medium" and "Large" groups
    eventually, but the initial work is on refactoring the code base to make
    it easier for users to plug in their own extensions to provide more of
    that functionality.

    The latest version of the specification can be found at
    <http://www.oasis-open.org/committees/office>

EXTRAS
    On top of the "Small" group functionality the following features are
    provided:

    * "Year 1583": Dates between 1593 and 1900 are handled correctly
    * Text is automatically converted to numbers in some (but not all)
    circumstances (see t/of-autonum.t gives examples)

KNOWN BUGS AND SHORTCOMINGS
    Patches are welcome to fix any of these!

    * (4.8.7) Whitespace is significant in database tests: '> 2006-01-01
    * (5.6) Empty parameters cannot be omitted: IF(FALSE(),7,)
    * (5.8) Semicolon is not recognised as a separator in function calls,
    cell/range lists, etc.: SUM(A1;B2;B3), IF(FALSE();7;8)
    * (5.8) Cell references of the form [.B1] are not supported
    * (5.8) References to other sheets are not supported
    * (5.10.1) Range names cannot contain unicode characters
    * (5.10.1) Range names do not support $$ markers
    * (5.11) Errors cannot be entered as strings: #N/A, #DIV/0! etc
    * (6.2.4) Empty cell in numeric context is not treated as 0
    * (6.2.4) Reference to TRUE in numeric context is not treated as 0
    * (6.3.10) Whitespace is significant in string-concatenation using &
    * (6.3.2) Whitespace is significant in unary minus: (5 - - 2) vs (5--2)
    * (6.3.6) Different types can be equal: 5 = "5"
    * (6.3.9) Range extensions are not implemented: B4:C4:C5
    * (6.3.11) Range intersections are not implemented: B3:B5!B5:B6
    * (6.3.14) Strings are converted to number with prefix +: +"Hello"
    * (6.9.1) Roll-over dates may not be correct (DATE(2006,25,34) =
    2008-02-05 vs 2008-02-03)
    * (6.12.5) COUNT() includes TRUE/FALSE values
    * (6.12.32) VALUE("-1 1/2") is interpreted (-1)+(1/2) not -1.5
    * (6.12.32) VALUE("3/32/2006") is interpreted as 1st April 2006
    * (6.12.32) VALUE does not raise error for false leap years
    * (6.12.32) Cannot take VALUE() of a datetime (with space or T)
    * (6.12.32) Cannot enter dates with alphabetic month names
    * (6.13.11) VLOOKUP for an integer must be exact, not <=
    * (6.14.3) IF() does not have default ifTrue/ifFalse values
    * (6.15.29) FACT() operates on negative numbers
    * (6.15.37) LOG() does not default to base 10
    * (6.15.44) PRODUCT() with no paramters is an error
    * (6.17.45) MIN("a") returns 0 rather than an error
    * (6.19.11) LEFT() does not have a default length
    * (6.19.14) MID() with a start beyond string returns undef
    * (6.19.14) MID() does not accept a zero length
    * (6.19.18) RIGHT() does not have a default length

HISTORY
    The original Spreadsheet::Engine code was taken from SocialCalc version
    1.1.0, which in turn originated as wikiCalc(R) version 1.0.

AUTHORS
    Spreadsheet::Engine is developed and maintained by Tony Bowden
    <tony@tmtm.com>

    SocialCalc 1.1.0 was developed by Dan Bricklin, Casey West, and Tony
    Bowden, at Socialtext, Inc.

    wikiCalc was developed by Dan Bricklin, at Software Garden, Inc.

COPYRIGHT
    Portions (c) Copyright 2005, 2006, 2007 Software Garden, Inc. All Rights
    Reserved.

    Portions (c) Copyright 2007 Socialtext, Inc. All Rights Reserved.

    Portions (c) Copyright 2007, 2008 Tony Bowden. Some Rights Reserved.

LICENSE
    The contents of this file are subject to the Artistic License 2.0; you
    may not use this file except in compliance with the License. You may
    obtain a copy of the License at
    http://www.perlfoundation.org/artistic_license_2_0