Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Spreadsheet::ParseExcel Numeric Format Woes

by Mr. Muskrat (Canon)
on Aug 14, 2002 at 21:51 UTC ( #190248=perlquestion: print w/replies, xml ) Need Help??

Mr. Muskrat has asked for the wisdom of the Perl Monks concerning the following question:

My Fellow Monks,

I am trying to read an Excel file and insert it into a worksheet in another Excel file. I can read the data and the formats and insert them correctly. I am using Spreadsheet::ParseExcel to get the data and format. I am using Spreadsheet::WriteExcel to insert it into a new Excel file (along with data from several other sources).

Can someone please enlighten me as to how to get the numeric format of a cell with Spreadsheet::ParseExcel?

I can see what type of cell it is by using:

my $cell = $worksheet->{Cells}[$row][$col]; my $cell_type = $cell->{Type};
(Which is 'Text', 'Numeric' or 'Date' BTW)

I can get the value before or after it is formatted with:

my $val = $cell->{Val}; my $value = $cell->{Value};
I can retrieve the cell format information (which has nothing to do with the numeric format) by using:
my %format = $cell->{Format};

Am I missing something important? Obviously I am or I would not have to ask...

If all else fails I can always just use the already formatted value...

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel Numeric Format Woes
by jmcnamara (Monsignor) on Aug 14, 2002 at 22:34 UTC

    The function that you need is FmtString() which is a method of the Spreadsheet::ParseExcel::FmtDefault class.

    Here is a short example which assumes that there is formatted data in cell A1 of the first worksheet:

    #!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; use Spreadsheet::ParseExcel::FmtDefault; my $file = Spreadsheet::ParseExcel->new(); my $format = Spreadsheet::ParseExcel::FmtDefault->new(); my $workbook = $file->Parse('test.xls'); my ($row, $col) = (0, 0); my $worksheet = $workbook->{Worksheet}[0]; my $cell = $worksheet->{Cells}[$row][$col]; print $format->FmtString($cell, $workbook), "\n";

    You could also consider the Spreadsheet::ParseExcel::SaveParser part of Spreadsheet::ParseExcel which can be used to update an existing Excel file. The following is an example from the pod of the newer simplified interface.

    use strict; use Spreadsheet::ParseExcel::SaveParser; $oBook = Spreadsheet::ParseExcel::SaveParser::Workbook->Parse('Test97.x +ls'); my $oWs = $oBook->AddWorksheet('TEST1'); $oWs->AddCell(10, 1, 'New Cell'); $oBook->SaveAs('iftest.xls');

    --
    John.

      Thanks John!

      I have been reading through the documentation for Spreadsheet::WriteExcel (You've written a great module and the docs are excellent, btw) for the last two days now and Spreadsheet::ParseExcel for most of the afternoon today. I guess I have been reading too long. :)

      Spreadsheet::ParseExcel::FmtDefault is just perfect for this project. I'll keep Spreadsheet::ParseExcel::SaveParser in mind for future needs though as it looks interesting.

        Hi All, I want to get format of one xls and its cells and copy it to other xls and correcponding cells. But I am not able to do it. I have used my %format = $cells{Format}; but I get error saying uninitialized hash. Please let me know if I need to do something else before using it. If possible please use code for explaining. Thanxs, Puneet

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://190248]
Approved by myocom
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2021-06-24 16:37 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (130 votes). Check out past polls.

    Notices?