[Octave-bug-tracker] [bug #62246] [octave forge](io) xlsread miscalculates serial date number using UNO interface

[Philip Nienhuis]

Update of bug #62246 (project octave):

                  Status:             In Progress => Invalid                
             Assigned to:                    None => philipnienhuis         
             Open/Closed:                    Open => Closed                 

    _______________________________________________________

Follow-up Comment #4:

Opening the supplied file in both Excel 2016 and LibreOffice 7.0.1.2 gives a
value of 39681 for cell A2. I must say I didn't know that LibreOffice's
DATEVALUE function returns Excel datenums, i.e. with epoch 31 Dec 1899. I
tried on Linux and Windows and got the exact same datenum in LibreOffice, so
I'm at loss as to why you + colleagues get different results.

It's easy to unzip the .ods file you supplied (.ods is a zipped-up directory
structure).
If you do and inspect content.xml, you'll see:

:
<table:table-cell table:formula="of:=DATEVALUE("2008-08-21")"
office:value-type="float" office:value="39681" calcext:value-type="float">
<text:p>39681</text:p>
</table:table-cell>
:


(line wrap!)
Now, if the office:value-type tag has the value "float", Octave (i.e., the io
package) expects the office:value tag to have a float value and that value
(39681) is what you'll get; correctly IMO.
And, this is independent of operating system; there's no code in the UNO
interface functions depending on Windows or *nix or whatever.

Just FYI, some other .ods file I used for testing has date cells formatted as
follows:

:
<table:table-cell office:value-type="date" office:date-value="1978-11-03">
<text:p>November 3, 1978</text:p>
</table:table-cell>
:


Again, given your description in comment #2 I think the whole issue might be
related to .xls files being interpreted and converted w/o proper, or desired,
or unduly expected, interpretation of date cell formatting.
I do have an itchy feeling about your description in comment #2; I can't
reproduce some of the things you wrote there so I wonder if it is the complete
story.
However, AFAICS the io package / UNO interface just works as designed.

A workaround for your workflow could be to include a stanza in your Octave
script that checks if date values (datenums) are smaller than 693960 and if
so, add that number. If the dates are in a certain column it's just one
vectorized statement.

Closing report as "Invalid".
If you insist there's still a bug this report can be reopened.



    _______________________________________________________

Reply to this item at:

  <https://savannah.gnu.org/bugs/?62246>

_______________________________________________
  Message sent via Savannah
  https://savannah.gnu.org/