Hello,
I'm trying to read date formats from a file. The dates are written in the following dd/MM/yyyy format. In my excel file, the cells are formatted as "Date".
When I call Cell_Read, I am getting differently formated dates.
04/10/1965 becomes 10/04/1965
14/12/1972 becomes 12/14/1972
20/03/1957 becomes 03/20/1957
This breaks further along in the system, as there's no such thing as a 14th or 20th month of the year.
The questions I would like to ask:
1. What area of the code is determining a change is required?
2. Is this something that can be changed on my end?
Thank you
So after about a month of investigation, I've managed to resolve this issue with a temporary fix. I'm writing this here incase someone else comes into this issue.
First of all, I created a new Read_cell method that is specifically for dates. In my use-case, I know when I will be reading a date and don't need a generic read for this.
In that method, the first thing I did was set CultureInfo to "en-GB" and applied it to the running thread.
This would override whatever was making it appear in US format.
Finally, I set the following NumberFormat on the cell before reading it @"dd/MM/yyyy". This would fix another issue with second variant (dd MMMM yyyy).
Hi David
I will need to take a look at this as it is definitely not ideal. It seems to be picking up some regional settings and using that date format.
You could, and I know this is really not ideal, adjust your code to cater for the "mm/dd/yyyy" format as an interim measure?
Do you perhaps know what the regional settings on your server are?
Regards
Hanno
Hanno wrote:
Thank you for your reply.
I'm unsure where to find my regional settings. I'm based in the UK, so it may be safe to assume that's what it is.
I've also checked the date format in Service Center -> Administration -> Environment Configuration. We're on YYYY-MM-DD format. I've changed this to DD/MM/YYYY, but experienced the same results. It seems this property is superficial.
David Bear wrote:
Hi David,
I have attached one demo file using same scenario that you mention but in my case it it working fine. Please let me know in case any concern.
Thanks,
Amit
I've been tackling this issue for a few days but I am making no progress. I've attached an example of where I am experiencing this - it's the DOB column. I would appreciate any further help debugging this issue.
David, there seems to exist a bug in the EPPlus library (perhaps re-introduced) in the current version.
If you change the date format in Excel to "yyyy-mm-dd" it should work okay.
EDIT: Note that I also had to set the ReadText property on Cell_Read to true.
Hope this helps, and sorry for the wait.
Thank you for sharing your findings with us, and Kudos for sticking it out!!
I ran into this issue recently and found this thread. We need to import spreadsheet data without expecting the user to make any changes to the document, which come from several third-parties. We wanted to avoid making any changes to the extension and eventually found an alternative approach using CellFormat_ApplyToRange to apply the date format to the cell prior to reading it.
Using the CellFormat.NumberFormat attribute, we can specify a date format such as "dd/MM/yyyy", this is passed into CellFormat_ApplyToRange.
CellFormat.NumberFormat tells the extension how to interpret a numerical value, but it works on dates too.
Thank you Simon, simple solution and solved the issue!