0

here's my problem. I am importing an Excel file with a field having a time field. On some servers (french) when I do 

double cellValueAsDouble = double.Parse(cellValue.Replace(".", ","));
MyDate.AddDays(cellValueAsDouble);

to add more than 12hours it works. But on an another servers (english) I have an error throwing an ArgumentOutOfRangeException. If I put less than or 12:00:00 it works fine. Does anyone knows why and how I can fix that?

Nordes
  • 2,493
  • 2
  • 21
  • 30
  • what is the message of exception? does it say anything? – tom.maruska Jan 11 '14 at 10:01
  • Error : Value to add was out of range. Parameter name: value. The stack trace doesn't really help me. It works locally but not on one server. – Nordes Jan 11 '14 at 10:06
  • What is `callValue`? Are you actually trying to parse "12:00:00" as a number? – fejesjoco Jan 11 '14 at 10:07
  • Actually in Excel it's setup as Hour field, so it gives a "double" value like 0.58333333 in my case (> 12:00:00). – Nordes Jan 11 '14 at 10:09
  • `0.58333333` should not be valid. – Khanh TO Jan 11 '14 at 10:12
  • Why? It works on a 24h system. "0.5883" gives "14:07:09". – Nordes Jan 11 '14 at 10:18
  • are you sure double parse gives 0.583? The error might happen due to a locale where the decimal point is a ',' so the value cant be converted. – thumbmunkeys Jan 11 '14 at 10:25
  • I actualy do a "double cellValueAsDouble = double.Parse(cellValue.Replace(".", ","));". Like I said it works for time less than 12:00:00 – Nordes Jan 11 '14 at 10:26
  • but that wont work on an english system, they use . for decimal point – thumbmunkeys Jan 11 '14 at 10:40
  • Why don't you just change the settings on your development PC to using a comma as a decimal separator and debug your code? – alzaimar Jan 11 '14 at 10:49
  • DateTime.AddDays() indeed can throw this exception. It will happen when you add enough days to exceed the year 10,000. Which requires adding at least 87,000,000 days. Easy to get that when you don't parse the number correctly. And you don't on a machine that uses the period for the decimal separator. Like an English machine. This problem started with the spreadsheet storing a string instead of a number. – Hans Passant Jan 11 '14 at 10:57

2 Answers2

2

I try to guess the answer:

  1. The cellValue contains a comma as a decimal separator. so it's value is e.g. 0,5833333333.
  2. You replace the comma with a point => 0.5833333-
  3. Converting this to a double on a french server reveals 58333333333.
  4. Adding this to a date produces the exception.

Your software does not expect a point '.' as the decimal separator, but whatever is configured on the target system. As long as the excel source remains on the same computer, you don't have to exchange anything.

Also, is it really and XLS-format or are you talking about CSV? If your data comes from a french EXCEL exported as CSV, you will end up in having decimal values formatted using a comma. Now you check your software on your local PC (which has US culture I guess) and you are adding the code to replace the comma with a point. Wow, it now works on your PC! But don't expect that same code to work on another machine.

You either have to parse the decimal value manually or just rely that the data source (your EXCEL data) is formatted on a machine having the same culture settings as your the machine running your application.

One guess why values less than 12:00 work: Maybe by accident. If you check 06:00 it will be 0,25 as decimal and converted to 25 which is perfectly alright (well, it does not throw an exception).

Please don't forget: It is only a guess as we haven't seen all relevant information.

alzaimar
  • 4,572
  • 1
  • 16
  • 30
  • I'll look into that. I just did "double cellValueAsDouble = double.Parse(cellValue.Replace(".", ","), _frenchTouch);". I need to wait for the deployment – Nordes Jan 11 '14 at 12:08
1

Its probably a problem with the culture and the decimal point.

Use this to convert the cell value with a ´.´ as decimal point:

Double.Parse(cellValue, CultureInfo.InvariantCulture);
Community
  • 1
  • 1
thumbmunkeys
  • 20,606
  • 8
  • 62
  • 110