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
    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
     
    martin durtschi, Aug 19, 2004
    #1
    1. Advertisements

  2. martin durtschi

    Guest

    Re: 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.
     
    , May 12, 2014
    #2
    1. Advertisements

  3. martin durtschi

    GS Guest

    > 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
     
    GS, May 13, 2014
    #3
  4. martin durtschi

    Guest

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

    GS Guest

    > 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
     
    GS, May 13, 2014
    #5
    1. Advertisements

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.
Similar Threads
  1. Patrick Molloy

    Copy with NumberFormatLocal included

    Patrick Molloy, Jul 17, 2003, in forum: Excel Programming
    Replies:
    0
    Views:
    207
    Patrick Molloy
    Jul 17, 2003
  2. Martin V

    NumberFormat vs NumberFormatLocal

    Martin V, Sep 2, 2004, in forum: Excel Programming
    Replies:
    1
    Views:
    361
    Dave Peterson
    Sep 3, 2004
  3. bavjean

    Numberformat for Cells using Automation

    bavjean, Feb 2, 2005, in forum: Excel Programming
    Replies:
    3
    Views:
    137
    bavjean
    Feb 8, 2005
  4. faberk

    Numberformat: automation from MS Access

    faberk, Apr 11, 2005, in forum: Excel Programming
    Replies:
    1
    Views:
    123
    arunkhemlai
    Apr 11, 2005
  5. Phil Rayner
    Replies:
    5
    Views:
    271
    Peter Huang [MSFT]
    Jan 31, 2006
  6. NumberFormat vs. NumberFormatLocal

    , Mar 9, 2006, in forum: Excel Programming
    Replies:
    2
    Views:
    248
  7. ppt_puppet

    Cell Format Function "NumberFormatLocal" in VBA

    ppt_puppet, Jan 4, 2010, in forum: Excel Programming
    Replies:
    1
    Views:
    239
    Gary Keramidas
    Jan 4, 2010
  8. Charlotte E.

    Converting NumberFormatLocal to NumberFormat

    Charlotte E., Oct 5, 2013, in forum: Excel Programming
    Replies:
    7
    Views:
    292
    Hermann Maier
    Nov 29, 2013
Loading...