locked
Prageammatically setting style "heading 3" to a a range in non en_us Excel RRS feed

  • Question

  • Hello,

    we have a shared Excel Addin written in C# which uses the Excel Interop.

    Now we want programmatically add to a range the style which is called "heading 3" in a en-Us Excel,

    even if the used Excel is not en_us.

    In german Excel the style is called "Überschrift 3".

    tia

      Hendrik Schmieder

    Tuesday, September 22, 2020 8:55 AM

All replies

  • In your German Excel system with a workbook saved in an en-us system, what happens if you apply rng.Style = ActiveWorkbook.Styles("Heading 3"), and in similarly in German "Überschrift 3"?
    Wednesday, September 23, 2020 4:12 PM
  • Hello Mr. Thornton,

    when I try

    titleRange.Style = "Heading 3";

    I get 

    Kann Formatvorlage 'Heading 3' nicht finden.

    (In Englsih : "couldn't find style  'Heading 3' ).

    When i try

    titleRange.Style = ExcelHelper.oExcelApp.ActiveWorkbook.Styles["Heading 3"];
    

    I get

    Ungültiger Index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

    Strange is that I get german error messages, although

    System.Threading.Thread.CurrentThread.CurrentCulture
    and
    System.Threading.Thread.CurrentThread.CurrentCulture

    is explicitly set to en-US

    Thursday, September 24, 2020 9:43 AM
  • So the English name "Heading 3" can't be found, but did you try applying the German name "Überschrift 3" ?

    The Styles collection is returned in alphabetical order which is obviously not the same in different languages, or even in the same language if new styles have been added. 

    I saved a workbook that included a couple of styles in cells, and saves this in the Styles.xml

     <cellStyles count="3">
      <cellStyle name="Heading 3" xfId="1" builtinId="18" /> 
      <cellStyle name="Hyperlink" xfId="2" builtinId="8" /> 
      <cellStyle name="Normal" xfId="0" builtinId="0" /> 
     </cellStyles>
    

    I assume the builtinId for 'BuiltIn' styles is the same in all languages, but I can't see any obvious direct way to apply a style by its builtinId, maybe you or someone else can...!

    FWIW in passing, the builtin style 'Followed Hyperlink' can get temporarily added to the Styles collection (newly into alphabetical order) when a link is clicked.

    Thursday, September 24, 2020 11:58 AM
  • Using "Überschrift 3" works on a German Excel.

    I iterate through the styles collection,

    the styleobject has a 'Name' and a 'NameLocal' property.

    So I thought that Name always contains the english name for builtin styles.

    There are some where this is true.

    name "Comma [0]"
    nameloc "Dezimal [0]"

    name "Comma"
    nameloc "Komma"

    name "Percent"
    nameloc "Prozent"

    name "Normal"
    nameloc "Standard"

    name "Currency"
    nameloc "Währung"

    name "Currency [0]"
    nameloc "Währung [0]"

    But for the other builtin styles 'Name' has the same value as 'NameLocal'.

    The style collection is ordered by 'NameLocal'.

    I also saved a workbook with applied style "Überschrift 3".

    except from styles.xml

    <cellStyles count="3">
    <cellStyle name="Standard" xfId="0" builtinId="0"/>
    <cellStyle name="Überschrift" xfId="1" builtinId="15"/>
    <cellStyle name="Überschrift 3" xfId="2" builtinId="18"/>
    </cellStyles>
    Thursday, September 24, 2020 1:09 PM
  • Those styles with different local-names are the builtin styles we had in 2003 and earlier, seems like the styles introduced with 2007 don't have different local-names.

    Looks like "Heading 3" and "Überschrift 3" are both builtinId="18", as expected.

    Can you apply any of the 2007+ builtin styles from the problematic workbook's styles collection, if not by name by collection index?

    You mentioned shared addin, is the workbook also shared, and if so does temporarily un-sharing help?

    Thursday, September 24, 2020 2:50 PM
  • I can't use the index, since the index depends on the local names.

    The styles collection is orderd alphabetically by the local names

    So for a German Excel the index for "Überschrift 3" would be almost at the end

    whereas in a English Excel the index for "Heading 3" would be more in the middle.

    The workbook isn't shared and the addin isn't part of the workbook at all,

    but loaded by Excel at start, registered under

    HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Office\Excel\Addins

    Friday, September 25, 2020 2:36 PM
  • I know the index order for this objective is effectively random, but only wanted to be sure you can at least apply style with from the collection with an index (eg 10, 20, 30). That was in case the workbook was shared but you've since confirmed it isn't.

    You say you are using "en-Us Excel", so I guess the Excel Ribbon displays the style names in English, but it seems your Excel Interop is picking up the German list of local names, presumably from your system language.

    Earlier I asked if you can apply both English "Heading 3" and German "Überschrift 3". You confirmed you can't apply English name (it's not returned in the Styles collection) but you didn't indicate if you can apply the style with the German name.

    If you can - the obvious question is why not apply the German name. If it's a problem because the addin is "shared", attempt to apply the English name and if that fails attempt to apply the German name (if catering for multiple names from a lookup table).
    Saturday, September 26, 2020 1:38 PM
  • There's a misunderstanding

    I'm using not the en-us version, but the german Version.

    I had to ask a co-worker how the style "Uberschrift 3" is called in an english version.

    I'm searching for a solution, which workes for every Excel language,

    since we have customer which uses French, Hungarian or Japanese Excel and maybe even other language version.

    Currently I would need a list for all supported nlv Excel verions:

    english "heading 3"

    german "Überschrift 3"

    french ????

    italian  ???

    hungarin ???

    japanse ???

    chinese ????

    .....

    Monday, September 28, 2020 11:00 AM
  • I think I had more than one misunderstanding!

    A direct way to accomplish your objective might exist, but it might take longer to find it than making style name lists for each language you want to support!

    Could you ask one client for each language to return a workbook that includes all the builtin styles as formats, and from the style.xml cross reference the local names with the BuiltInId's. Also includes a cell that returns the xlApp.International(xlCountryCode) and maybe xlCountrySetting. You could supply a workbook with a simple VBA macro with a button to click tol get all the details you need, not so much.

    Looks like you've only got 6 or 7 languages to compile for now. If later your code is hit with a new language show a message to explain and ask the user to contact you.

    Monday, September 28, 2020 7:17 PM
  • The languages I explictly liste were exmples.

    I really need the information for all possible languages,

    since if we have no russian, korean or  ... customer,

    we could have such a customer tomoorow.

    Wednesday, September 30, 2020 11:26 AM
  • "Internationalization" of a range of topics is often required when distributing Excel solutions internationally, and some of these involve translation tables. It's a shame the newer Style names are not given dual English / Local names, or even if the collection was returned in BuiltinID order rather than alphabetical it would be helpful.

    So unless we're missing something I don't see an alternative to what I suggested. In practice it might not be as problematic as perhaps you're anticipating!
    Wednesday, September 30, 2020 6:31 PM
  • I thought of the following workaround.

    I look if the style "Überschrift 3" (Heading 3) has a unique property amoung all other builtin styles.

    It looks like "Überschrift 3" is the only builtin style with xlEdgeBottom Border, 
    which has has weight xlMedium.

    Thursday, October 1, 2020 8:33 AM
  • Good idea!

    So looking for a unique combination of properties to identify the style. Easy I guess if only looking for one style, like 'Heading 3', but less so if looking for all 49(?) builtin styles.

    It looks like first need to apply the style in a test cell before it's border properties can be read. Then, curiously, it seems in 2007 & 2010 can only read the test cell's style borders, but in later versions can read the borders directly from the styles collection (if once applied somewhere).

    Be sure to remove any 'test' styles from user's own workbook, probably better to create a temporary workbook.

    Maybe could include a combination of translation tables, and if the language is not included use your workaround, just a thought.

    Thursday, October 1, 2020 10:42 AM
  • I must only look in my use case for two styles "Uberschrift" (Heading) and "Überschrift 3" (Heading 3).

    We dropped 2007 support when the extended support for Office 2007 has ended.

    The extended support for 2010 is going to end at 13th october 2020 (state of today).

    So 2010 shouldn't be a problem either.

    Thursday, October 1, 2020 11:19 AM