Open-XML-SDK

Open-XML-SDK copied to clipboard

Describe the bug I have a spreadsheet, which I get exception informing me the namespace is invalid. The exception thrown informs:

InvalidDataException: The root XML element "http://schemas.microsoft.com/office/2006/01/customui:customUI" in the part is incorrect. The expected root XML element is: "http://schemas.microsoft.com/office/2009/07/customui:customUI".

The original spreadsheet has wrong/invalid namespace, but after having used ChangeDocumentType from .xlsm to .xlsx, I think this namespace should have been resolved to the right namespace automatically by Open XML SDK even though the namespace is (what I think to be) not related to ChangeDocumentType. This could potentially be expanded to include that if I use ChangeDocumentType on any .xlsx to .xlsx then all invalid namespaces would be resolved.

The namespace is found in root folder /customUI and any customUI.xml files in the folder.

I am trying to make my own fix with the below code, BUT the code is not working and the code will not even write any namespace to console (if I add a Console.WriteLine(name.Value) line to the code). I do know spreadsheet.RibbonAndBackstageCustomizationsPart is not null, if you write anything to console immediately after the { in the first if statement. If I use the below code as part of ChangeDocumentType from .xlsm to .xlsx, then the conversion will fail, which is strange, since the conversion will even fail if I do not try to change any namespaces but if I only try to write the namespaces to console it fails.

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(output_filepath, true))
{
    if (spreadsheet.RibbonAndBackstageCustomizationsPart != null)
    {
        var namespaces = spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI.NamespaceDeclarations.ToList();
        foreach (var name in namespaces)
        {
            if (name.Value.Equals("http://schemas.microsoft.com/office/2006/01/customui:customUI"))
            {
                spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI.RemoveNamespaceDeclaration("x");
                spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI.AddNamespaceDeclaration("x", "http://schemas.microsoft.com/office/2009/07/customui:customUI");
            }
        }
    }
}

Observed behavior Wrong namespace in customUI.xml http://schemas.microsoft.com/office/2006/01/customui:customUI

also does spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI work?

Expected behavior Right namespace in customUI.xml http://schemas.microsoft.com/office/2009/07/customui:customUI after having used ChangeDocumentType, this function should update any invalid namespaces to valid namespaces.

also

spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI.NamespaceDeclarations should report any namespaces

Desktop (please complete the following information):

• OS: Windows 11
• Office version 2208
• .NET Target: .Net Core 6.0.9
• DocumentFormat.OpenXml Version: 2.18.0

Additional context See data sample: https://github.com/Asbjoedt/CLISC/blob/master/Docs/Example3.xlsm

Hi @Asbjoedt , thanks for the detailed description. I'll look into this issue too and let you know if I can find the issue.

@Asbjoedt , how is the customUI part being added to the file?

Hi @Asbjoedt , To give you a better explanation, I'm asking how the file was created is because, if you look at the underlying xml parts of data sample you posted, there is a customUI.xml and a customUI14.xml. If you open those xml files, customUI.xml correctly has "http://schemas.microsoft.com/office/2006/01/customui" for its namespace, but customUI14.xml incorrectly also has the same namespace. If you correct the namespace for customUI14.xml to "http://schemas.microsoft.com/office/2009/07/customui" and rezip the parts as an xlsm file, then the SDK will open the file without error.

With the correct namespace, spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI is defined and spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI.NamespaceDeclarations returns the list of namespaces as expected.

In your example you try to access spreadsheet.RibbonAndBackstageCustomizationsPart.CustomUI.NamespaceDeclarations, but spreadsheet.RibbonAndBackstageCustomizationsPart represents the mso14:customUI element from the "http://schemas.microsoft.com/office/2009/07/customui" namespace, so when you try to access it, the SDK finds the part, but it has the wrong namespace and throws the error.

Hi @mikeebowen

Thx I am looking into testing your reply.

Regarding your question how it was created, I don't know. We harvested 16k spreadsheets from our organization's shared drive and we are testing the Open XML SDK validator and my "check'n'change according to archival requirements" console application on these.

@Asbjoedt I contribute to a VS Code extension that makes it easier to edit OOXML files and rezip the contents. It might if you need to look at the underlying xml directly and edit it: OOXML Viewer

So now I understand things better based on your reply. The namespace is specific for file customUI14.xml. The example file has this wrong namespace markup:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"
          onLoad="rxIRibbonUI_OnLoad">

I need to programmatically change this to:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"
          onLoad="rxIRibbonUI_OnLoad">

I now use RibbonExtensibilityPart instead of RibbonAndBackstageCustomizationsPart. I now know the prefix is mso.

I am running this code but using NamespaceDeclarations.ToList() still does not register any namespaces, but if I use NamespaceUri and Prefix, I can get the namespace and I can get prefix.

I am running this code:

using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(filepath, true))
{
    // Correct the namespace for customUI14.xml
    RibbonExtensibilityPart ribbon = spreadsheet.RibbonExtensibilityPart;
    if (ribbon != null)
    {
        Uri uri = new Uri("/customUI/customUI14.xml", UriKind.Relative);
        if (spreadsheet.Package.GetPart(uri) != null)
        {
            if (ribbon.RootElement.NamespaceUri != "http://schemas.microsoft.com/office/2009/07/customui")
            {
                var list = ribbon.RootElement.NamespaceDeclarations.ToList();
                foreach (var name in list)
                {
                    Console.WriteLine(name.Key + " " + name.Value);
                }
                Console.WriteLine(ribbon.RootElement.Prefix);
                Console.WriteLine(ribbon.RootElement.NamespaceUri);
                ribbon.RootElement.RemoveNamespaceDeclaration("mso");
                ribbon.RootElement.AddNamespaceDeclaration("mso", "http://schemas.microsoft.com/office/2009/07/customui");
            }
        }
    }
}

Using RemoveNamespaceDeclaration and AddNamespaceDeclaration gives the following error:

System.Xml.XmlException: 'The prefix 'mso' cannot be redefined from 'http://schemas.microsoft.com/office/2006/01/customui' to 'http://schemas.microsoft.com/office/2009/07/customui' within the same start element tag.'

How can I fix this? I am aware this question is more suitable for StackOverflow than typical SDK Github "issue" usage.

I need to change the namespace programmatically, because the example file is a generic "project management template" which is in use by a wide range of national agencies, and we will be receiving data from them, where this file could be. Therefore any manual edit process involving the excellent OOXML Viewer is not on the table for us.

I don't know a fix, but the reason you get this error is because ribbon.RootElement.NamespaceUri is the root namespace and is not in ribbon.RootElement.NamespaceDeclarations. If you step through the code, you can see that after var list = ribbon.RootElement.NamespaceDeclarations.ToList(); , list.Count() is 0. The issue is that ribbon.RootElement.NamespaceUri doesn't have a setter, so you can't do ribbon.RootElement.NamespaceUri = "http://schemas.microsoft.com/office/2009/07/customui". Setting the xmlns attribute on ribbon.RootElement.OuterXml is also not possible because doesn't have a setter either.

I will ask some colleagues if there is another way to set the namespace. SO might be a good place to post this too, maybe someone there knows a way to set the namespace.

Hi @mikeebowen

This is an old one, but I am looking at it again, trying to fix it, but I cannot find a workable solution to changing the namespace to the correct one.

One way of continuing is to remove the initial exception thrown: InvalidDataException: The root XML element "http://schemas.microsoft.com/office/2006/01/customui:customUI" in the part is incorrect. The expected root XML element is: "http://schemas.microsoft.com/office/2009/07/customui:customUI".

When I open the spreadsheet in Excel, it does not complain or give errors. Can we remove the InvalidDataException? Why is it trying to validate the namespace, since I'm not running the validator? I'm just trying to open and change some data in the spreadsheet, and my data manipulation is not related to the xml files containing ribbon custom UI.