CODEWITHSUNDEEP
c#asp.netexcelopenxml
mattfetz
mattfetz

Reputation: 425

Modify Excel's defined Name

I have to rename Excel sheets using OpenXml but when this is done the references to these sheets will no longer work. I've been trying to alter the innerXml of DefinedName using OpenXml but it won't set the value. Here's what I have so far:

private void Renamesheet(string sheetName, string newSheetName, string filePath)
{
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
    {
        WorkbookPart wbPart = document.WorkbookPart;

        Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();

        sheet.Name = newSheetName;
        var names = wbPart.Workbook.DefinedNames.Where(x => x.InnerText.ToString().Contains("'" + sheetName + "'"));
        var i = 0;
        while (i < names.Count())
        {
            names.ElementAt(i).InnerXml = names.ElementAt(i).InnerXml.Replace(sheetName, newSheetName);   
            i++;
        }
        wbPart.Workbook.Save();
    }

}

I'm using the while loop instead of a foreach because I read that might fix this issue but it did not. Any help would be great.

Upvotes: 0

Views: 1159

Answers (1)

Dreamweaver
Dreamweaver

Reputation: 1346

   WorkbookPart w=  document.WorkbookPart;
                Sheet sheet = w.Workbook.Descendants<Sheet>().Where(s => s.Name == "abc").FirstOrDefault();
                sheet.Name = "dreamers";  (1)
                w.Workbook.Save();

Above three lines changes the sheetname, as soon as line (1) will be executed var names = wbPart.Workbook.DefinedNames.Where(x => x.InnerText.ToString().Contains("'" + sheetName + "'"));

line will throw a null reference exception as the name will be changed.

And in any case the excel dont have same DefinedNames for two sheets so where will return one sheet, hence while loop not needed. User FirstOrDefault() instead of while.

Upvotes: 1

Related Questions