Excel raw date number representation incoherency
[C# 9.0 and net7.0]
(* main request edit)
(Why can excel have the same date value representation as different dates? 43708 can be 31/08/19 and 01/09/23)
As stated by @user8728340's response, this happens because of a misalignment of bool literals representations. When scheffer represented it as "true" and the excel default (and what the library expected) was "1".
(A github issue and pool request have been created to solve the problem)
More information about the outcome of this observation can be found in the problems github issue ("DateTime incoherent value")
I have a .xlsx file from a HttpClient request and read these bytes as a stream. I'm trying to use ExcelDataReader in the stream; I detected that datetime values are not correct. The table has only one column of date and all dates start in 2021.
All the datetimes read with the IExcelDataReader are specifically 31/08/2019 - this cannot be because of the above statement.
When I tested it in my personal 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 are the same. So, here is the deal
I'm already using the ExcelDataReader library so I would like not to use another to solve the same problem.
The origin file from the request ends with "Implementation by Anton Scheffer" literary written to the file
Clarification
While the Excel saved version eds with a more understandable manner
Looking into the .xlsx file structure as a .zip in windows, we can access the entry: xl/worksheets/sheet1.xml, storing the table's data from the file. In the 14º column, the date one, the value written to the file is 43708 this value can be interpreted by excel in two ways,
as 31/08/19 or 01/09/23. I don't know what defines that.
The Excel library that created this file - PL/SQL "as_xlsx" by Anton Schleffer - saves files with a non-standard value for the "date1904" workbook property. Excel itself sets the date1904 property to 1, but in files produced by "as_xlsx" it is true. ExcelDataReader does not support this particular deviation and proceeds to return wrong dates off by 4 years.
- Why does an empty preprocessor command still evaluate to something?
- How to implement variable sized array within C struct
- Character array typecasting to integer
- How can I exclude non-numeric keys? CS50 Caesar Pset2
- How to get the sign, mantissa and exponent of a floating point number
- Why do MCU libraries use logic operations instead of bitfield structs?
- What kind of implementation can I use for a static associative array on a vintage system with very limited resources?
- Determine libraries to link against for a windows library function?
- Passing macro values to arm linker that places variable at a specific location
- running a program with wildcards as arguments
- How to perform addition of two vectors of 8-bit integers with a single addition in C/C++
- GNU RISC-V Embedded GCC throws "x ISA extension `xw' must be set with the versions" error
- Counting pulses using a swiss flow meter with an Arduino, how is it done?
- How to create a folder in C (need to run on both Linux and Windows)
- Is there any way to compute the width of an integer type at compile-time?
- How can I initialize all members of an array to the same value?
- Is C notably faster than C++
- How to get the Windows SDK version number a program is compiling with at compile time
- Confused by difference between expression inside if and expression outside if
- Equivalent of atoi for unsigned integers
- k&r: Exercise 1-18. Program takes input but doesnt produce any output?
- Using in C thrd_sleep() to either wait for time or interrupt by signal. Example?
- How can I compute `exp(x)/2` when `x` is large?
- c programming: answer always equates to 0
- Is it possible to access a parameter of a function from another function in C?
- Will this expression evaluate to true or false (1 or 0) in C?
- What Is the Return Value of strcspn() When Str1 Does not Contain Str2?
- Mapping a numeric range onto another
- Signalled and non-signalled state of event
- Why is faster to do a branch than a lookup?