I spent a couple hours banging my head against a problem of trying to change the cell type of a spreadsheet created by OpenOffice::OODoc to something numeric for sorting purposes. The documentation that came with the module wasn't especially helpful in figuring it out, but eventually I stumbled across this article in the CPAN forums that shed some light on the issue. I thought it might be good to create a node in the monastery on the topic, so I'm going to share a little here.

use strict;
use warnings;
use OpenOffice::OODoc;

my $ods_doc = odfDocument(file   => 'somefile.ods',
              create => 'spreadsheet',);

# Set table to desired size

my $sheet    = 0; # First sheet in the file.
my $num_rows = 3;
my $num_cols = 1; # <- really small spreadsheet.

$ods_doc->expandTable($sheet, $num_rows, $num_cols);

# If you only need the cell to contain a text string, than
# all you have to do is write to it, that's the default.

$ods_doc->updateCell($sheet, 0, 0, "The title of the column");

# Even though we're writing a number, it will be escaped
# as a string because we haven't change the format of 
# the cell.

$ods_doc->updateCell($sheet, 1, 0, 2);
$ods_doc->updateCell($sheet, 2, 0, 12);

$ods_doc->save;
[download]

In that snippet, 12 will come before 2 because it's doing an ASCII sort. Not very appealing, and all too familiar. By overriding the default cell value type to floating point numbers, we can get the cells to sort properly.

# &c as above.

my $cell = $ods_doc->getCell($sheet, 1, 0);
$ods_doc->cellValueType($cell, 'float');
$ods_doc->updateCell($sheet, 1, 0, 2);

$cell = $ods_doc->getCell($sheet, 2, 0);
$ods_doc->cellValueType($cell, 'float');
$ods_doc->updateCell($sheet, 2, 0, 12);

$ods_doc->save;
[download]

I hope you have found this witty and informative.