Excel automation through .NET Interop: NumberFormat property looks like NumberFormatLocal

[martin durtschi]

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

 

[alex]

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.

 

[GS]

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

 

[alex]

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 are: " & 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/answers/261136-c-excel-interop-numberformat
http://stackoverflow.com/questions/...-formatting-issue-with-international-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.

 

[GS]

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