Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel NumberFormat
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel NumberFormat
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 wrote in message oups.com... | 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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel NumberFormat
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. wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel NumberFormat
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). " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel automation through .NET Interop: NumberFormat property looks like NumberFormatLocal | Excel Programming | |||
NumberFormat | Excel Discussion (Misc queries) | |||
VBA NumberFormat | Excel Programming | |||
NumberFormat strings for Excel Cells | Excel Programming |