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

..what is the difference in the cell hash for _Value and Val? I open the file with new, no converters, and then step through a sheet's cells. I've had success with these in the past using _Value for the cell contents, but today , suddenly, I saw this: 
0  Spreadsheet::ParseExcel::Cell=HASH(0x6669a58)
   'Format' => ''
   'Type' => 'Numeric'
   'Val' => 0.153
   '_Value' => 0.15

[download]
I checked in excel and indeed, the cell has 0.153 in it, as text, nothing looks unusual.

Should I be using {Val} ? I see the lone example on the module's meta cpan page uses Val, but I see no description of the cell-hash , or what the difference is between Val and _Value?

Can a kind Monk enlighten me?

TYVM

Replies are listed 'Best First'.
Re: Spreadsheet::XLSX why is Val != _Value
by Fletch (Bishop) on Jun 30, 2021 at 15:42 UTC

    The documented interface is to use the value or unformatted methods to retrieve the value in the cell. Looking under the hood the former pulls from the _Value slot and and the latter from Val.

    Edit: The reply from pryrt below pretty well fleshes out what I would have said were I being more verbose; you're living dangerously breaking encapsulation and you probably don't want to do that (not to mention the method names for the actual accessors explain the apparent discrepency in returned values pretty succinctly).

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

[reply]
[d/l]
[select]
      "The documented interface" - can tell me where this is? The metaCPAN page is minimal.

      Also, if the cell contains the text "0.153", why would "value" and "unformatted" differ by 0.003?

      Ty

[reply]
        if the cell contains the text "0.153", why would "value" and "unformatted" differ by 0.003?

        Have you ever used Excel? The value in the cell and what is displayed in the cell do not have to match. If you have the contents 0.153 and use cell formatting to display in two-decimal fixed-point as 0.15, the difference between the underlying value and the displayed value will be 0.003 -- that's the way Excel works. If you were to run the same experiment, but set the cell format to "blah" 0.00 "blah", you would find that the real value was 0.153, but the displayed value was the string blah 0.15 blah. If you want the actual data from the cell, use the unformatted method; if you want to know how Excel last rendered that cell, use the value method; if you want to run the risk of the underlying implementation of Spreadsheet::XLSX changing on you, use the hash key Val to currently be equivalent to the unformatted method or the underlying Excel data, and use the hash key _Value to currently be equivalent to running the value method (with no guarantee that in future versions those same hash keys will exist, or that it will even be a hash-based object). Using the underlying hash of an object (if it's currently implemented as a hash) is a bad idea, unless the official documentation tells you to access the hash elements directly -- and even then, using the defined accessor methods is a better idea (unless the author doesn't provide accessors).

        "The documented interface" - can tell me where this is? The metaCPAN page is minimal.
        In the docs for Spreadsheet::XLSX, it says,
        This module is a (quick and dirty) emulation of Spreadsheet::ParseExcel for Excel 2007 (.xlsx) file format. It supports styles and many of Excel's quirks, but not all. It populates the classes from Spreadsheet::ParseExcel for interoperability; including Workbook, Worksheet, and Cell.
        ... which strongly implies that it has the same interface as Spreadsheet::ParseExcel. That module documents that the value() method and the unformatted() method work as described in this thread.
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom