CODEWITHSUNDEEP
perlspreadsheetxlsx
LKramer
LKramer

Reputation: 369

Using Excel::XLSX to parse a spreadsheet in Perl

I am using the Perl module Spreadsheet::XLSX to parse an Excel spreadsheet. Part of the data looks like this:

    Time        A1      A2      A3      
    0m14m35     0.12    0.13    0.14
    0m29m35     0.15    0.16    0.17

Here's part of the code:

foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {

    foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {

        my $cell = $sheet->{Cells}[$row][$col];
        my $val = $cell->{Val} || "";
    }   
}

The problem is that the time values are converted to floats:

 '0m14m35' becomes:  0.0101273148148148
 '0m29m35' becomes:  0.0205439814814815

How can I keep the time string a string?

Thanks! L.

Upvotes: 3

Views: 4510

Answers (1)

runrig
runrig

Reputation: 6524

$cell->{Val} is the unformatted value (which is the same as $cell->unformatted()). Try getting $cell->value() (same as $cell-{_Value}), which is the formatted value;

Excel stores dates as floats, so if you want to format those, you can use the ExcelFmt() function from Spreadsheet::ParseExcel::Utility, and you will want to use the unformatted value as one of the arguments to that function. You might even be able to get the format argument with $cell->get_format()

Upvotes: 8

Related Questions