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

Discussion in 'Excel Programming' started by martin durtschi, Aug 19, 2004.

  1. 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

    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

    Thanks for any help
    - martin durtschi
    martin durtschi, Aug 19, 2004
    1. Advertisements

  2. martin durtschi


    Well, this issue still exists 10 years later, and I haven't found a solution yet.
    , May 12, 2014
    1. Advertisements

  3. martin durtschi

    GS Guest

    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

    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?


    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    GS, May 13, 2014
  4. martin durtschi


    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:

    And some other hits around the web:

    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.
    , May 13, 2014
  5. martin durtschi

    GS Guest

    Currently it looks to me like there's a bug in this interop wrapper.
    I understand what you're saying here. Sorry I don't speak .Net, and so
    can't help you further with this!<g>


    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    GS, May 13, 2014
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.