Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Error reading Excel Cell Value

by sandeep78 (Novice)
on Oct 03, 2008 at 17:05 UTC ( [id://715225]=perlquestion: print w/replies, xml ) Need Help??

sandeep78 has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I am using the code below to read from an excel sheet. This reads a cell. It is returning some data dump rather than the actual cell value. For ex: The cell(33,13) is (4.10) formatted as Accounting (2 Decimal points) in Excel. But the script is returning...some dump 742,849,... This is not happening on all the cells in the spread sheet.
#!/usr/local/bin/perl use strict; use warnings; use Spreadsheet::ParseExcel; my $file = shift @ARGV; my $excel = Spreadsheet::ParseExcel::Workbook->Parse($file); my $worksheet = $excel->Worksheet(0); my $cell = $worksheet->{Cells}[33][13]; print $cell->Value,"\n";
Please advise.

Replies are listed 'Best First'.
Re: Error reading Excel Cell Value
by zentara (Archbishop) on Oct 03, 2008 at 20:08 UTC

      Were that the case I'd expect he would be getting an error trying to call the method Value on an undefined value (at least a quick test referencing a cell from an empty sheet in an existing xls file returns undef for me and $sheet->{Cells} is an arrayref with the 0th item being an empty arrayref (so [ [] ])). Never the less you might want to sanity check your indexen versus $sheet->{MaxRow} and $sheet->{MaxCol} (and/or the minimums if you're truly paranoid :), and also check that you've got a defined value before attempting to manipulate it.

      my $value; if( my $cell = $sheet->{Cell}->[$r]->[$c] ) { $value = $cell->Value(); }

      The cake is a lie.
      The cake is a lie.
      The cake is a lie.

        I tried this option. I am doing a sanity check on my excel in the actual program. The script pasted is just a part of it. Interesting thing here is...when I copied data from a cell that is returning me right results to that particular cell. And ran the script. It is giving good result. For ex: Cell (12,11) has 1.29 and the script returns 1.29. So, I copied the 1.29 into my 33,13 cell which is causing the error. And ran the script...it returned 1.29. I am not able to understand the issue here. Please advise.
      I tried this script...no luck. Thank you.
Re: Error reading Excel Cell Value
by jmcnamara (Monsignor) on Oct 03, 2008 at 20:51 UTC

    Try printing the cell {Val} which is the unformatted cell value:
    ... print $cell->{Val}, "\n";

    --
    John.

      I tried the option, same result. Thank you.
Re: Error reading Excel Cell Value
by no21 (Sexton) on Oct 04, 2008 at 22:01 UTC
    In debug mode, continue to a line after you set $cell = $worksheet->{Cells}[33][13].

    What do you get when you examine that cell by typing "x $cell" in the debugger?
      Hi, Please see the debug report below.
      DB<1> x $cell 0 Spreadsheet::ParseExcel::Cell=HASH(0x8f6430c) 'Code' => undef 'Format' => Spreadsheet::ParseExcel::Format=HASH(0x8f0eb44) 'AlignH' => 0 'AlignV' => 2 'BdrColor' => ARRAY(0x8f0eb5c) 0 0 1 0 2 0 3 0 'BdrDiag' => ARRAY(0x8f0eb8c) 0 0 1 0 2 0 'BdrStyle' => ARRAY(0x8f0e268) 0 0 1 0 2 0 3 0 'Fill' => ARRAY(0x8f0ebc8) 0 1 1 42 2 64 'FmtIdx' => 43 'Font' => Spreadsheet::ParseExcel::Font=HASH(0x8c4e080) 'Attr' => 0 'Bold' => 0 'Color' => 32767 'Height' => 10 'Italic' => 0 'Name' => 'Arial' 'Strikeout' => 0 'Super' => 0 'Underline' => 0 'UnderlineStyle' => 0 'FontNo' => 0 'Hidden' => 0 'Indent' => 0 'JustLast' => 0 'Key123' => 0 'Lock' => 1 'Merge' => 0 'ReadDir' => 0 'Rotate' => 0 'Shrink' => 0 'Style' => 0 'Wrap' => 0 'FormatNo' => 29 'Type' => 'Numeric' 'Val' => '7.42648557919274e+275' '_Kind' => 'RK' '_Value' => ' 742,648,557,919,274,215,596,480,721,467,753,739,797, +881,839,158,032,045,444,744,972,281,248,374,846,048,088,725,564,424,7 +05,059,145,840,738,686,965,435,358,755,836,749,146,754,873,675,932,60 +0,092,125,946,893,868,194,138,671,244,971,437,781,478,579,517,410,099 +,590,090,026,114,048,017,693,855,644,672,962,827,489,552,711,307,891, +602,716,747,903,682,255,045,301,108,736.00 '
      Thanks

        Wow, that's a big number.

        I work with this module on a daily basis, and when I see weird behavior like this, it is usually due to things like hidden worksheets in the Excel workbook, or the Excel workbook was generated by some other software package.

        I don't even know if this is possible via perlmonks.org, but if you're comfortable with posting/attaching the exact Excel file that is giving you this problem, I wouldn't mind taking a look at it myself.

        If there's no way to post a file here, I could possibly give you an email address to send the file to.

Re: Error reading Excel Cell Value
by zentara (Archbishop) on Oct 04, 2008 at 14:43 UTC
    I tried...no luck

    Linux-user says: I think you have an Excel Macro virus. :-)


    I'm not really a human, but I play one on earth Remember How Lucky You Are
Re: Error reading Excel Cell Value
by jmcnamara (Monsignor) on Oct 08, 2008 at 15:27 UTC
      What's the best way to drop back to a previous version of a module?

      I guess the real question would be, what's the best way to remove a module?

      I tried doing a 'make uninstall' from the source directory, but it refused to delete a handful of files because 'make uninstall' is 'unsafe'.


        In this case (and probably most cases) it should be enough to just install the older version over the newer version.

        --
        John.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://715225]
Approved by ikegami
Front-paged by ww
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (4)
As of 2024-06-23 02:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?
    erzuuli‥ 🛈The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.