如何通过OpenXMLReader修改Excel单元格属性并插入文本

First, let's clarify the before and after we're aiming for:

Original Target Cell XML #

<x:c r="C1" s="9"/>

Desired Modified XML #

<x:c r="C1" s="9" t="inlineStr">
  <x:is>
    <x:t>Report Title</x:t>
  </x:is>
</x:c>

Since OpenXMLReader is a forward-only, read-only parser, you can't directly modify elements while reading them. Instead, you'll need to pair it with OpenXMLWriter to copy the document content, making changes only when you hit your target cell (C1). Here's how to implement this:

Step-by-Step Implementation #

• First, initialize both the reader and writer for your worksheet part.
• As you iterate through each element with the reader:
  • For most elements, write them directly to the writer without changes.
  • When you detect the target cell (C1), modify its attributes and add the inline string content before writing it out.

Complete Code Example #

using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
using (OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart))
{
    while (reader.Read())
    {
        // Skip whitespace to avoid unnecessary nodes in the output
        if (reader.NodeType == XmlNodeType.Whitespace)
            continue;

        // Check if we've found our target cell (C1)
        bool isTargetCell = reader.ElementType == typeof(Cell) && 
                            reader.Attributes.First(a => a.LocalName == "r").Value == "C1";

        if (!isTargetCell)
        {
            // Write all non-target elements as-is
            writer.WriteNode(reader);
            continue;
        }

        // Load the current cell into a mutable object to modify it
        Cell targetCell = (Cell)reader.LoadCurrentElement();
        
        // Add the inline string data type (this sets the t="inlineStr" attribute)
        targetCell.DataType = CellValues.InlineString;
        
        // Create and append the inline string content
        InlineString inlineStr = new InlineString();
        Text cellText = new Text("Report Title");
        inlineStr.AppendChild(cellText);
        targetCell.AppendChild(inlineStr);
        
        // Write the modified cell to the output
        writer.WriteElement(targetCell);
    }
}

Key Notes #

• Mutable Element: We use reader.LoadCurrentElement() to convert the read-only view from the reader into an editable Cell object. This is essential because you can't modify elements directly via the reader.
• Strongly-Typed Properties: Instead of manually adding the t attribute, using targetCell.DataType = CellValues.InlineString ensures proper OpenXML schema compliance and avoids typos.
• Whitespace Handling: Skipping whitespace nodes helps keep the generated XML clean and matches the original document structure better.

This approach will correctly update your C1 cell to include the required data type attribute and text content while preserving the rest of your worksheet.

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