如何通过EPPlus修改Excel文件中的自定义Ribbon元素？

Great question! Yes, you absolutely can modify custom Ribbon elements (add, remove, update) in an Excel file using EPPlus. Let’s walk through exactly how to do this, using your existing Custom UI XML as a reference.

Prerequisites #

• Make sure you have EPPlus installed (via NuGet or direct download).
• Familiarity with basic XML manipulation (we’ll use LINQ to XML for this example).

Step 1: Load Your Excel File #

First, use EPPlus to open the Excel file you want to modify:

using (var package = new ExcelPackage(new FileInfo(@"C:\Path\To\Your\File.xlsx")))
{
    // All modification logic goes inside this using block
}

Step 2: Retrieve the Existing Custom UI XML #

Your Excel file already has a Custom UI part added via the Custom UI Editor. We’ll first read that XML into a manipulable format:

// Locate the Custom UI part in the package
var customUiPart = package.Workbook.CustomXmlParts
    .FirstOrDefault(part => part.Uri.ToString().Contains("/customUI/"));

string customUiXml = string.Empty;
if (customUiPart != null)
{
    // Read the existing XML content
    using (var streamReader = new StreamReader(customUiPart.GetStream()))
    {
        customUiXml = streamReader.ReadToEnd();
    }
}

Step 3: Modify the Custom UI XML #

Now we’ll use LINQ to XML to make changes to your Ribbon. We’ll cover common scenarios based on your original XML:

Update an Existing Element (e.g., Change Button Label/Supertip)

Let’s modify the label and supertip of your existing btnXXX button:

XDocument ribbonDoc = XDocument.Parse(customUiXml);
XNamespace ns = "http://schemas.microsoft.com/office/2009/07/customui"; // Match your original namespace!

// Find the button by its ID
var targetButton = ribbonDoc.Descendants(ns + "button")
    .FirstOrDefault(b => b.Attribute("id")?.Value == "btnXXX");

if (targetButton != null)
{
    targetButton.SetAttributeValue("label", "Updated Button Label");
    targetButton.SetAttributeValue("supertip", "This button now does something new!");
}

Add a New Button to Your Group

Let’s add a new large button to your YYY group:

// Find the group by its ID
var targetGroup = ribbonDoc.Descendants(ns + "group")
    .FirstOrDefault(g => g.Attribute("id")?.Value == "YYY");

if (targetGroup != null)
{
    // Create the new button element
    var newButton = new XElement(ns + "button",
        new XAttribute("id", "btnNewAction"),
        new XAttribute("label", "New Action"),
        new XAttribute("imageMso", "ChartLineStyle"), // Use a built-in Office icon
        new XAttribute("size", "large"),
        new XAttribute("onAction", "NewButtonMacro"), // Macro name to execute
        new XAttribute("screentip", "New Action"),
        new XAttribute("supertip", "Click this to run the new custom action")
    );

    // Add the button to the group
    targetGroup.Add(newButton);
}

Remove an Element (e.g., Delete a Button)

If you want to remove the original btnXXX button:

var buttonToRemove = ribbonDoc.Descendants(ns + "button")
    .FirstOrDefault(b => b.Attribute("id")?.Value == "btnXXX");

buttonToRemove?.Remove(); // Safely remove if the button exists

Step 4: Save the Modified XML Back to the Excel File #

Once you’ve made your changes, write the updated XML back to the package and save it:

if (customUiPart != null)
{
    // Overwrite the existing Custom UI part
    using (var streamWriter = new StreamWriter(customUiPart.GetStream(FileMode.Create)))
    {
        streamWriter.Write(ribbonDoc.ToString());
    }
}
else
{
    // If no Custom UI part existed (unlikely in your case), add a new one
    var newCustomUiPart = package.Workbook.CustomXmlParts
        .Add(new Uri("/customUI/customUI.xml", UriKind.Relative));
    
    using (var streamWriter = new StreamWriter(newCustomUiPart.GetStream(FileMode.Create)))
    {
        streamWriter.Write(ribbonDoc.ToString());
    }
}

// Save the changes to the Excel file
package.Save();

Key Notes to Remember #

• Namespace Matching: Always use the same XML namespace as your original Custom UI (yours is http://schemas.microsoft.com/office/2009/07/customui). Using the wrong namespace will break the Ribbon.
• XML Validity: Invalid XML (e.g., missing attributes, incorrect element structure) will cause Excel to ignore the entire custom Ribbon. Always test the modified file in Excel.
• EPPlus Licensing: If you’re using EPPlus 5 or later, ensure you have the appropriate license for commercial use (EPPlus uses a dual license model).

内容的提问来源于stack exchange，提问作者Dave R