none
openxml definedname - is name scoped to workbook or worksheet? RRS feed

  • Question

  • In a .Net app (not VSTO) I've used the DocumentFormat.OpenXml namespace to retrieve a list of DefinedNames from a file.  However, I noticed that one of the Excel files I tested this against had duplicated names referring to the same cell. 

     

    I opened the file in Excel to investigate.  One name was scoped to the workbook, the other was scoped to a sheet.  I need to delete the name scoped to a sheet and keep the workbook level name, yet so far have been unable to figure out how to distinguish between the 2 names.  Looking over the DefinedName class on MSDN has given me no insight

     

    Has anybody figured this out?

    Wednesday, July 10, 2013 2:37 PM

Answers

All replies

  • If I look in workbook.xml I see the element DefinedNames, as follows:

    -<definedNames><definedName name="ScopeWB">Sheet1!$A$1</definedName><definedName name="ScopeWS" localSheetId="0">Sheet1!$A$1</definedName><definedName name="ScopeWS">Sheet1!$A$1</definedName></definedNames>

    Notice the localSheetId attribute for the first ScopeWS, which isn't there for the other two names. That one is scoped to the worksheet, the others to the workbook.

    This appears to correspond to DefinedName.LocalSheetId property (http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname.localsheetid.aspx).

    OTOH, the explanation for that property (http://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.definedname.aspx) doesn't indicate this: "Specifies the sheet index in this workbook where data from an external reference is displayed." So you might want to test my observation...


    Cindy Meister, VSTO/Word MVP, my blog

    • Marked as answer by duke.carey Wednesday, July 10, 2013 5:34 PM
    Wednesday, July 10, 2013 3:38 PM
    Moderator
  • Thanks for the investigation Cindy.

    I saw that localSheetId in the list of properties but, based entirely upon the confusing description, right away jumped to the conclusion that it wasn't any help to me.

    I'll test this against a variety of workbooks to see just how reliable it is.

    Thanks again

    Wednesday, July 10, 2013 5:38 PM