SpreadScript Perl Example

This is a simple Perl script using SpreadScript to change and print the contents of an Excel spreadsheet in order to demonstrate some of the features available in SpreadScript. The most recent version of this script is included in the distribution in the "samples" directory.
#!/usr/bin/env perl

##
## SpreadScript example code
##
## This script demonstrates some of the functions available in the API.
##
## NOTE:  To use this script, you may need to change the path
##        to the license below.
##

use strict;
use spreadscript;

# Change this to point to your license.

my $SPREADSCRIPT_LICENSE = $ENV{'SPREADSCRIPT_LICENSE'} ||
                           "/opt/SpreadScript/SpreadScript-license.txt";

sub dumpCell
{
    my $sheet = shift;
    my $row = shift;
    my $col = shift;

# Get the cell type.
    my $type = $sheet->getType($row, $col);

# Get the formatted contents of the cell.
    my $value = $sheet->getText($row, $col);

# Convert "type" to a printable string.
    my $typeString = spreadscript::getTypeString($type);

# Print the location and contents of the cell.
    print $row, ", ", $col, ": ", $value, " -- ", $typeString;

# If the cell contains a formula print it, too.
    if ($type == $spreadscript::SS_FORMULA_CELL)
    {
        print "  <", $sheet->getFormula($row, $col), ">\n";
    }
    else
    {
        print "\n";
    }
}

sub dumpSheet
{
    my $sheet = shift;

# Print the sheet by getting the sheet's dimensions and
# iterating over the cells.

    print "******* dump sheet: ", $sheet->getName(), " ********\n";
    my $dims = $sheet->getDimensions();
    my $firstRow = $dims->getMinRow();
    my $lastRow = $dims->getMaxRow();
    my $firstCol = $dims->getMinColumn();
    my $lastCol = $dims->getMaxColumn();

    my $col = $firstCol;
    while ($col <= $lastCol)
    {
        my $row = $firstRow;
        while ($row <= $lastRow)
        {
            dumpCell($sheet, $row, $col);
            $row = $row + 1;
        }
        $col = $col + 1;
    }
}

### main

# Initialize the library, passing in the license file location.
spreadscript::init($SPREADSCRIPT_LICENSE);


# Create a new workbook object and make it active (this is required).
# (Note: use 'my' to make variables local so that things get properly freed.)
my $book = new Workbook();
$book->activate();

# Read in the spreadsheet "sample.xls"
$book->open("sample.xls");

# Get the first (and only) sheet from the workbook.
my $sheet = $book->getSheet(1);

# Store a formula in cell B3 and recalculate the sheet.
$sheet->setFormula(3, 2, "=min(a1..a3)");
$sheet->recalc();

# Set the default format to "fixed" with 3 places after the decimal
# and print the contents of the sheet.
$sheet->setDefaultFormat($spreadscript::SS_FMT_FIXED);
$sheet->setDefaultPlaces(1);
dumpSheet($sheet);

# Now change the default format to "float" with 3 places after the decimal
# and print the contents of the sheet.
$sheet->setDefaultFormat($spreadscript::SS_FMT_FLOAT);
$sheet->setDefaultPlaces(3);
dumpSheet($sheet);

# Set borders on B2; only the Excel file format saves borders.
$sheet->setBorder(2, 2,
                  new Border($spreadscript::SS_BORDER_THIN,
                             $spreadscript::SS_BORDER_MEDIUM, 
                             $spreadscript::SS_BORDER_DASHED,
                             $spreadscript::SS_BORDER_DOTTED));

# Save the workbook as an XS3 file and as an Excel file
$book->save("sample_out.xs3", $spreadscript::SS_NEXS_XS3);
$book->save("sample_out.xls", $spreadscript::SS_EXCEL_95);

# Export range A1..C3 as an HTML table.
my $r = new Range($sheet, 1, 1, 3, 3);
$r->exportHTML("sample_out.html");

# Create a new workbook and make it active.
$book = new Workbook();
$book->activate();

# Create a new sheet named "The Sheet" in the workbook.
$sheet = $book->createSheet("The Sheet");

# Store the even numbers 10 through 28 in cells A5 through A14
my $i = 5;
while ($i < 15)
{
    $sheet->setNumber($i, 1, 2 * $i);
    $i = $i + 1;
}

# Print out the contents of the sheet.
dumpSheet($sheet);

print "Done!\n"