none
How to delete a sheet which contains a defined-name successfully? RRS feed

  • Question

  • Hi all,

     I need your help. It's a problem about Open XML SDK programming. Here is my concern.

    I wrote a program to try to delete a sheet in a Excel file. In normal conditions, this program works very well. But when I try to delete a sheet which contains a customer-defined name whose scope is "Sheet1"(name of the sheet I want to delete), a problem occurs.

    Here is the problem description:

    I am getting a recovery message when i am trying to open the changed spreadsheet. 
    "Excel found unreadable content in "AutoDelete.xlsx" Do you want to recover the contents of the workbok?...."

    I beleive 'Sheet1' and defined name 'X' keeps some relational information some where else too and that also needs to be removed.  But how to implement this by writing codes? Is there anyone can help me?

    Friday, December 4, 2009 8:00 AM

Answers

  • operate on one sheet each time, that's my advice. Remove a sheet and update all localSheetId value of remaining worksheet scoped definedName, then go to next one. this will ensure the whole speadsheet document's validity.
    • Marked as answer by kevinbest0702 Wednesday, December 9, 2009 1:47 AM
    Tuesday, December 8, 2009 5:12 AM

All replies

  • you're  quite right. there does exist  a relationship betweenthe sheet and definednames. And the definednames is declared in workbook.xml part.
    For example, i defined a range in sheet1, it's A1:B3 and named it as range3x2 and scope sheet1, the following information will be added into workbook.xml part:

     <definedNames>
      <definedName localSheetId="0" name="range3x2">
       Sheet1!$A$1:$B$3
      </definedName>
     </definedNames>
    

    so if you want to delete a sheet, first of all you should search for definednames in workbook part and delete all defiendnames that refer to this sheet.

    Monday, December 7, 2009 1:58 AM
  • The "localSheetId" attribute represents the sheet id of the sheet in which the definedName is defined, which means that I not only need to remove the relevant relationship entries when removing the sheet to which they refer but also have to modify the value of "localSheetId". It's quite annoying, isn't it? 

    And there maybe exist some worse situations, imagine this: I have five sheets("Sheet1","Sheet2","Sheet3","Sheet4") in a workbook, and each sheet has a sheet-scope definedName. I would encounter a very complex situation when I try to delete "Sheet1" and "Sheet3". It would not be easy to modify the "localSheetId" values. Any advice about this? 
    Monday, December 7, 2009 7:43 AM
  • yeah, it's sort of annoying. About deleting definedNames along with its hosting worksheet, here is a more complex case. I created a range3x2 whose scope is worksheet1 local, range2x2 which defiend on worksheet2 and effective in whole workbook scope.
     <definedNames>
      <definedName name="range2x2">
       Sheet2!$A$1:$B$2
      </definedName>
      <definedName localSheetId="0" name="range3x2">
       Sheet1!$A$1:$B$3
      </definedName>
     </definedNames>
    

    I guess the only way to decide a definedName is related to a specific sheet is check the value of definedName, which looks like Sheet1!$A$1:$B$3.
    as for localSheetId, i found the following explaination for it in ECMA spec:
       

    Specifies the sheet index in this workbook where data from an external reference is displayed.

    In the following example, the defined name refers to a range whose data source is an external database called Northwind_Database: 

    [Example:

    <definedName name="Northwind_Database"
      localSheetId="2">Sheet5!$A$1:$T$47</definedName>

    end example]

     The possible values for this attribute are defined by the XML Schema unsignedInt datatype.

    Monday, December 7, 2009 8:06 AM
  • I know the use of "localSheetId". What I mean is that it would not be easy to modify the value of it in the above example I give since two sheets will be deleted and indexes need to be changed correspondingly.
    Tuesday, December 8, 2009 2:03 AM
  • operate on one sheet each time, that's my advice. Remove a sheet and update all localSheetId value of remaining worksheet scoped definedName, then go to next one. this will ensure the whole speadsheet document's validity.
    • Marked as answer by kevinbest0702 Wednesday, December 9, 2009 1:47 AM
    Tuesday, December 8, 2009 5:12 AM
  • it's a workaround though still very troubling.

    Wednesday, December 9, 2009 1:46 AM