SpreadScript Perl Example

#!/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"