DateTime incoherent value about exceldatareader HOT 9 OPEN

Well wouldn't you know. Here's the code that parses the 1904 flag:

vs the code that generates the 1904 flag:

https://github.com/sokolsaiti/as_xlsx/blob/f268ec3bce9cd44aac600c6f183fc9bc4568fdbb/as_xlsx.sql#L1742

<workbookPr date1904="true" defaultThemeVersion="124226"/>

Took a while to notice, but indeed 1 is not equal to true, so there's the bug.

from exceldatareader.

so the function by schaffer sets the flag to a true literall and the library was expecting a numeric representation of the boolean, this explains why the excel save solves it, since it would probably overwrite the "true" to the default from excel probably "1"

from exceldatareader.

A PR for this is welcome :) It'd be good to craft a couple .xlsx with different values for date1904 and reverse more precisely how Excel interprets the value, f.ex are only "1" and "true" valid options or can it be "yes" or "blawhabhah"; what is the default for invalid values, etc. And include the findings as tests and .xlsx in the PR. (I'm not familiar with pattern matching syntax so cannot vouch for the suggestions - the third one looks good as a quick fix, keep in mind the value can be null, so no unvalidated .Trim() etc )

from exceldatareader.

Hi @eJJonatah, can you share a file having this problem?

(If not, and its xlsx, can you unzip it and paste some examples what those dates look like in the sheet xml?)
(If not, and its xls or xlsb, its going to be difficult to help without a file to repro)

from exceldatareader.

Hi @andersnm, thank you for your response.
I downloaded the same file and read it in my pc and got everything right

I'm thinking that something can be messed up because its a web request, i can test transfering those bytes to a file then reading the file but only at monday did that kind of problem ever occured?

from exceldatareader.

when i tested in my computer the dates returned right, what I realized is in order to transfer this file to my computer I had to save it using Excel.

The stream from the request has ~2079 bytes while the (saved by Excel) file has ~5569 so I compared the byte structure in both files in notepad++ and tested a lot to ensure that the original version in fact never returns the dates right (every other data is correct) but the modified one is perfectly fine. I tested this behavior in 3 machines and the results where the same.

The request stream is probably from a sql functions that converts a query into an excel report. Because the end of the file is signed as "Implementation by Anton Scheffer"

an excel save solve its all. I will try to edit the binaries to remove sensitive data and provide it and provide the raw file here, you can access it through excel but, cannot save.

from exceldatareader.

@eJJonatah It might be premature to close this. If the original file opens correctly in Excel, then one could argue it should read in ExcelDataReader too. The key facts so far are:

• the excel file contains the binary string "Implementation by Anton Scheffer"
• the date discrepancy is exactly the result if the "1904 Date" flag was missing or wrong

Does not seem to be any Excel writer repos in https://github.com/antonscheffer but found this in a different repo
https://github.com/sokolsaiti/as_xlsx/blob/master/as_xlsx.sql

The problem can likely be reproduced using the original library, whatever language it is (PL/SQL for Oracle??). It sounds like ExcelDataReader cannot read its output "date1904" workbook flag for some reason:

https://github.com/sokolsaiti/as_xlsx/blob/f268ec3bce9cd44aac600c6f183fc9bc4568fdbb/as_xlsx.sql#L1742

from exceldatareader.

A possible solution for this would be to use C# 7 Pattern matching mdsn: source

bool date1904 = Reader.GetAttribute("date1904") is "1" or "true" or "True";
date1904 = Reader.GetAttribute("date1904").Trim().ToLower() is "1" or "true";
// if the literals are strongly patternized then
date1904 = Reader.GetAttribute("date1904") is "1" or "true";

does the library platform supports this feature? I'm not sure but i think its faster than calling equals

from exceldatareader.

   <workbookPr date1904="1" defaultThemeVersion="124226" />
   <!-- tested:
      <workbookPr date1904="1" defaultThemeVersion="124226" /> //works
      <workbookPr date1904="true" defaultThemeVersion="124226" /> //works
      <workbookPr date1904="True" defaultThemeVersion="124226" /> //doesn't work.
      <workbookPr date1904="TRUE" defaultThemeVersion="124226" /> //doens't work.
      <workbookPr date1904="blawhabhah" defaultThemeVersion="124226" /> //doens't work.
   -->

I created a test file to test the possibilities and found out that the only values that works are 1 and true
to activate the Date1904 behavior, so i cloned with a branch to apply the third suggestion in the OpenXmlReader
to handle this text boolean possibility.

from exceldatareader.