Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation through .NET Interop: NumberFormat property looks like NumberFormatLocal
Hello out there.
I got the following problem, which I can neither explain nor solve. I would be glad if anybody could help me out. My Excel is a german Excel 2000. My application uses automation to export data to Excel and also sets cell formats. The end user's Excel could be any language, so - of course - the application will set the NumberFormat property, which is language independant. Using a COM client like VB 6 or VBA works perfectly well; i can set the NumberFormat property to e.g. "d/m/yy", which causes the NumberFormatLocal property to be "T/M/YY" - the german representation of date formats. But my Application is .NET/C#, automating Excel through an interop wrapper. What happens is that the wrapper's NumberFormat property seems to be mapped to Excel's NumberFormatLocal property. So for a cell with the above format, both NumberFormat and NumberFormatLocal return "T/M/YY", and setting NumberFormat to "d/m/yy" throws the very same exception as one gets when trying to set NumberFormatLocal to "d/m/yy". I suspect this behaviour to be a problem of COM interoperability, globalization or a combination of the two. I'd appreciate any - explanation of the behaviour - solution - workaround (besides trying to work with the NumberFormatLocal property) Thanks for any help - martin durtschi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation through .NET Interop: NumberFormat propertylooks like NumberFormatLocal
On Thursday, August 19, 2004 9:34:28 AM UTC+1, martin durtschi wrote:
Hello out there. I got the following problem, which I can neither explain nor solve. I would be glad if anybody could help me out. My Excel is a german Excel 2000. My application uses automation to export data to Excel and also sets cell formats. The end user's Excel could be any language, so - of course - the application will set the NumberFormat property, which is language independant. Using a COM client like VB 6 or VBA works perfectly well; i can set the NumberFormat property to e.g. "d/m/yy", which causes the NumberFormatLocal property to be "T/M/YY" - the german representation of date formats. But my Application is .NET/C#, automating Excel through an interop wrapper. What happens is that the wrapper's NumberFormat property seems to be mapped to Excel's NumberFormatLocal property. So for a cell with the above format, both NumberFormat and NumberFormatLocal return "T/M/YY", and setting NumberFormat to "d/m/yy" throws the very same exception as one gets when trying to set NumberFormatLocal to "d/m/yy". I suspect this behaviour to be a problem of COM interoperability, globalization or a combination of the two. I'd appreciate any - explanation of the behaviour - solution - workaround (besides trying to work with the NumberFormatLocal property) Thanks for any help - martin durtschi Well, this issue still exists 10 years later, and I haven't found a solution yet. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation through .NET Interop: NumberFormat property looks like NumberFormatLocal
On Thursday, August 19, 2004 9:34:28 AM UTC+1, martin durtschi wrote:
Hello out there. I got the following problem, which I can neither explain nor solve. I would be glad if anybody could help me out. My Excel is a german Excel 2000. My application uses automation to export data to Excel and also sets cell formats. The end user's Excel could be any language, so - of course - the application will set the NumberFormat property, which is language independant. Using a COM client like VB 6 or VBA works perfectly well; i can set the NumberFormat property to e.g. "d/m/yy", which causes the NumberFormatLocal property to be "T/M/YY" - the german representation of date formats. But my Application is .NET/C#, automating Excel through an interop wrapper. What happens is that the wrapper's NumberFormat property seems to be mapped to Excel's NumberFormatLocal property. So for a cell with the above format, both NumberFormat and NumberFormatLocal return "T/M/YY", and setting NumberFormat to "d/m/yy" throws the very same exception as one gets when trying to set NumberFormatLocal to "d/m/yy". I suspect this behaviour to be a problem of COM interoperability, globalization or a combination of the two. I'd appreciate any - explanation of the behaviour - solution - workaround (besides trying to work with the NumberFormatLocal property) Thanks for any help - martin durtschi Well, this issue still exists 10 years later, and I haven't found a solution yet. VB6/VBA automated instances of Excel are created using CreateObject(), and interact directly with the object variable used. Are you saying C# doesn't have such a function so you can interact directly with your instance? Also, setting number format in VB6/VBA is done on the Range object directly. Are you saying C# also has no way to do that either? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation through .NET Interop: NumberFormat propertylooks like NumberFormatLocal
On Tuesday, May 13, 2014 9:49:54 AM UTC+1, GS wrote:
On Thursday, August 19, 2004 9:34:28 AM UTC+1, martin durtschi wrote: Hello out there. I got the following problem, which I can neither explain nor solve. I would be glad if anybody could help me out. My Excel is a german Excel 2000. My application uses automation to export data to Excel and also sets cell formats. The end user's Excel could be any language, so - of course - the application will set the NumberFormat property, which is language independant. Using a COM client like VB 6 or VBA works perfectly well; i can set the NumberFormat property to e.g. "d/m/yy", which causes the NumberFormatLocal property to be "T/M/YY" - the german representation of date formats. But my Application is .NET/C#, automating Excel through an interop wrapper. What happens is that the wrapper's NumberFormat property seems to be mapped to Excel's NumberFormatLocal property. So for a cell with the above format, both NumberFormat and NumberFormatLocal return "T/M/YY", and setting NumberFormat to "d/m/yy" throws the very same exception as one gets when trying to set NumberFormatLocal to "d/m/yy". I suspect this behaviour to be a problem of COM interoperability, globalization or a combination of the two. I'd appreciate any - explanation of the behaviour - solution - workaround (besides trying to work with the NumberFormatLocal property) Thanks for any help - martin durtschi Well, this issue still exists 10 years later, and I haven't found a solution yet. VB6/VBA automated instances of Excel are created using CreateObject(), and interact directly with the object variable used. Are you saying C# doesn't have such a function so you can interact directly with your instance? Also, setting number format in VB6/VBA is done on the Range object directly. Are you saying C# also has no way to do that either? -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion Hi Garry No, not quite. I'm not too hot on how all the MS technologies fit together, but I'll do my best. VBA/VBScript etc. interact directly with the required COM objects. ..Net/Java/all others have to use the Interop wrappers to access the same COM objects. Here is a vb script that opens an existing workbook, changes the NumberFormat of a couple of cells, and outputs the values. Dim xlApp, xlBook, xlSht Dim filename, value1, value2, value3, value4 filename = "c:\Warehouse.xls" Set xlApp = CreateObject("Excel.Application") set xlBook = xlApp.WorkBooks.Open(filename) xlApp.Application.Visible = True set xlSht = xlApp.activesheet value1 = xlSht.Cells(2, 1) value2 = xlSht.Cells(2, 2) 'the MsgBox line below would be commented out in a real application 'this is just here to show how it works... msgbox "Values a " & value1 & ", " & value2 xlSht.Range("A1").NumberFormat = "General" msgbox "NumberFormat is General" xlSht.Range("B1").NumberFormat = "@" msgbox "NumberFormat is Text" xlSht.Range("C1").NumberFormat = "" msgbox "NumberFormat is Blank" msgbox "GetNumberFormat is " + xlSht.Range("A1").NumberFormat msgbox "GetNumberFormat Local is " + xlSht.Range("A1").NumberFormatLocal If you run this with your regional settings set to English, both NumberFormat and NumberFormatLocal will return "General". If you change you regional settings to French, NumberFormat will return "General", but NumberFormatLocal will return "Standard". If you change to Chinese (Traditional, Taiwan), NumberFormat will return "General", NumberFormatLocal will return "G/通用*式". Basically, NumberFormat uses the US English value all the time, NumberFormatLocal uses a local representation. However, doing the above in .Net/Java always returns the NumberFormatLocal value, even when calling NumberFormat. Trying to set the NumberFormat results in a COM exception if you're in a non English language, as "General" is then not a valid value. There is a similair error that is covered by this MS KB article: http://support.microsoft.com/kb/320369/en-us And some other hits around the web: http://bytes.com/topic/c-sharp/answe...p-numberformat http://stackoverflow.com/questions/1...ional-settings Currently it looks to me like there's a bug in this interop wrapper. Unfortunately I don't understand what this is or does at the moment, and will probably have to follow the KB recommendation which is to set the thread I'm using to use US English. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel automation through .NET Interop: NumberFormat property looks like NumberFormatLocal
Currently it looks to me like there's a bug in this interop wrapper.
Unfortunately I don't understand what this is or does at the moment, and will probably have to follow the KB recommendation which is to set the thread I'm using to use US English I understand what you're saying here. Sorry I don't speak .Net, and so can't help you further with this!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
NumberFormat property | Setting up and Configuration of Excel | |||
Immediate Fail: Launch Excel via Interop | Excel Discussion (Misc queries) | |||
Microsoft.Office.Interop.Excel when exporting from another program | Excel Discussion (Misc queries) | |||
Excel Workbook opened twice with dotnet interop | Excel Programming | |||
Copy with NumberFormatLocal included | Excel Programming |