-
Notifications
You must be signed in to change notification settings - Fork 956
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
DateTime incoherent value #649
Comments
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?) |
Hi @andersnm, thank you for your response. |
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. |
@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:
Does not seem to be any Excel writer repos in https://github.com/antonscheffer but found this in a different repo 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: |
Well wouldn't you know. Here's the code that parses the 1904 flag: ExcelDataReader/src/ExcelDataReader/Core/OpenXmlFormat/XmlFormat/XmlWorkbookReader.cs Lines 46 to 47 in 2dc4c7e
vs the code that generates the 1904 flag: <workbookPr date1904="true" defaultThemeVersion="124226"/> Took a while to notice, but indeed |
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" |
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 |
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 |
<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 |
…rom xl/worbook.xml. The only two possible values that excel recognises are "true" and "1" also, pattern matching is more readable than equals "==" aldo this can be easily changed if breaks the project pattern. ExcelDataReader#649 third solve suggestion
fix-date1904Attribute bool parsing possibility appointed on issue #649
I'm using the ExcelDataReader library to read an excel file that comes from an API web request
i read the content as Stream and pass it into the ExcelReaderFactory.CreateReader.
All works really well but, in the target table there is a date column that is being read with the wrong value.
The column is in the positon 14 of the table and is the only date column in that table so it cannot be mispositioning.
All The values from that column start in year 2023 and represent deadlines for orders. For some reason, whenever i try to
read the value in the IExcelDataReader i allways receive the value 31/08/2019, allways. I tried debugging into the library but
i couldn't find the reason why this is happening in this case. I have a copy of that table and can ensure that none of the orders have their deadline prior to 2021.
debugging:
any idea what could be causing this problem?
The text was updated successfully, but these errors were encountered: