Excel NumberFormat

[s_schutte]

Hello,

I'm currently having an issue dealing with number/date formatting in
Excel. I have a C# winforms application which uses a third party
component (GemBox) to open and read Excel files without having to
instantiate Excel.

The problem I'm running into is that from this control, when I look at
a cell containing a date value, I get back a C# DateTime object. If
the value visibile in Excel is "12/1/2000", I get back "12/1/2000
12:00:00 AM" (the date plus the time).

Now, for each cell, there is a property called "NumberFormat" that
gives me the format that Excel is using to display the cell. In the
above date's case, the NumberFormat is "M/D/YY".

Now, the quick hack is to convert "M/D/YY" into "MM/dd/yyyy" and use
string.Format() in C# to display the date as "12/1/2000". However,
from what I have read, the string that Excel can return for its date
format (M/D/YY) can vary from culture to culture, and the app I am
developing must support many different international formats. (For
instance, I have read that in Italy, the date format might come back
as "aa/mm/gggg" or some craziness).

So what I was wondering is:
1. Has anyone ran into this situation before and been able to solve it
2. Does anyone have a table of all the different format strings Excel
uses, so I at least can try to parse them properly?
OR
3. Does anyone know of a third party control that can get the display
value for us without us having to worry about formatting? Automation
is not really an option for various reasons that I won't go into here.

-Sam

 

[Jim Rech]

I get back a C# DateTime object.

If that is true are there not properties of the object that return the day,
month, year, etc.? The DateTime object should be region agnostic I would
think.


--
Jim
| Hello,
|
| I'm currently having an issue dealing with number/date formatting in
| Excel. I have a C# winforms application which uses a third party
| component (GemBox) to open and read Excel files without having to
| instantiate Excel.
|
| The problem I'm running into is that from this control, when I look at
| a cell containing a date value, I get back a C# DateTime object. If
| the value visibile in Excel is "12/1/2000", I get back "12/1/2000
| 12:00:00 AM" (the date plus the time).
|
| Now, for each cell, there is a property called "NumberFormat" that
| gives me the format that Excel is using to display the cell. In the
| above date's case, the NumberFormat is "M/D/YY".
|
| Now, the quick hack is to convert "M/D/YY" into "MM/dd/yyyy" and use
| string.Format() in C# to display the date as "12/1/2000". However,
| from what I have read, the string that Excel can return for its date
| format (M/D/YY) can vary from culture to culture, and the app I am
| developing must support many different international formats. (For
| instance, I have read that in Italy, the date format might come back
| as "aa/mm/gggg" or some craziness).
|
| So what I was wondering is:
| 1. Has anyone ran into this situation before and been able to solve it
| 2. Does anyone have a table of all the different format strings Excel
| uses, so I at least can try to parse them properly?
| OR
| 3. Does anyone know of a third party control that can get the display
| value for us without us having to worry about formatting? Automation
| is not really an option for various reasons that I won't go into here.
|
| -Sam
|

 

[NickHK]

Sam,
I'm not sure how the gemBox and C# work, but Excel stores date/time data in
a double.
<From Excel help>
Microsoft Excel stores dates as sequential numbers which are called serial
values. By default, January 1, 1900 is serial number 1, and January 1, 2008
is serial number 39448 because it is 39,448 days after January 1, 1900.
Excel stores times as decimal fractions because time is considered a portion
of a day.
</From Excel help>

As such, gemBox has to get that double and the format string in order to
return date like that.
So it seems that you are working as a String rather than a Date. If so, then
you would have to split the string up according to the formatstring to know,
say your example is 1st Jan or 12th Dec.

But as Jim says, does your C# DateTime object not help.
There's no VB/VBA equivalent, so we <probably> can't say.

NickHK
P.S. You do know that ADO can read closed Excel workbooks if the data is in
a structured format.

 

[Peter Suurküla]

Hi,

I'm currently working in France, where Excel would use the "craziness"
"mm/jj/aaaa" to display what you ask for - if you use the dialog-box in Excel
for formatting your data. Nevertheless, in VB, VBA or any other automaton
link, Excel only accepts the "standard English" format. If you display the
date in Excel, there should therefore be no problem using the English format.

Now, you're saying that you want to display the value with C#. In that case
I don't see the problem, since it's already a C# DateTime object: Handle it
as any other date-time object, and the apply (for example) the local Data
format when displaying it (in C++ you simply put myDate.Format() if myDate is
a COleDateTime object).