Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Changing the cell value to a numeric type using OpenOffice::OODoc

by starX (Chaplain)
on May 14, 2009 at 00:49 UTC ( [id://763963]=perlmeditation: print w/replies, xml ) Need Help??

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;
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;
I hope you have found this witty and informative.

Replies are listed 'Best First'.
Re: Changing the cell value to a numeric type using OpenOffice::OODoc
by alexander_lunev (Pilgrim) on Jun 17, 2022 at 06:31 UTC
    Thank you for this contribution! Though I've also banged my head on this, but not the couple of hours, just one hour, thanks to you!

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlmeditation [id://763963]
Approved by GrandFather
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (8)
As of 2024-03-28 11:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found