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};

[download]
(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};

[download]
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";

    [download]

    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');

    [download]

    --
    John.

[reply]
[d/l]
[select]

      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.

[reply]
        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
[reply]

Back to Seekers of Perl Wisdom