none
How to replace a text run in a Excel cell consisting of several text runs? RRS feed

  • Question

  • Dear all,

    I have read the examples about inserting and deleting text in a spreadsheet cell. I have got this to work.

    However, I need to alter text within a cell and do not just want to replace the whole cell, as I potentially lose the formatting of parts of the cell if I do so.

    Here's an example: Suppose cell A1 contains abcXdef (notice that 'b' is bold here). I want to replace 'X' with something else without losing the formatting.

    So far, all code I have seen takes the cell and replaces ALL text in it:

      Cell cell = ...;
      
      // If the value is a shared string, look it up and process the resulting parts of the cell.
      // (Notice here that shared strings may contain formatting, so there can be several parts.)
      if (cell.DataType != null && cell.DataType == CellValues.SharedString)
      {
          OpenXmlElement cellNodes = document.WorkbookPart.SharedStringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)];
    
          // Replace WHOLE cell – not what I want
          string oldValue = cellNodes.InnerText;
          string newValue = ...; // work on oldValue, replace, etc.
          cell.CellValue = new CellValue(newValue);
          cell.DataType = new EnumValue<CellValues>(CellValues.String);
      }
    

    I need to run through the elements of cellNodes (the text runs of the cell) and work on the InnerText of these elements (runs). So I tried enumerating (IEnumerable<OpenXmlCompositeElement>)cellNodes.Elements() and changing on such elements the InnerText. However, this is a read-only attribute (even though the documentation of OpenXmlCompositeElement says its get/set) so I don't know how to change it. I then tried to cell.ReplaceChild(newNode, node); where node is the existing text run – but here I need to generate a copy of node with just with the text changed. How can I do this? Any ideas?

    Thanks,
    Kaspar

    Monday, July 20, 2009 10:20 AM

All replies

  • Hi Kaspar,

    You can set the "Text" attribute of the "run" element:
     Run r = ...;
     r.Text = new Text("new");
     ssPart.SharedStringTable.Save();
    in this way, the text of the cell will change from "oldxyz" to "newxyz".
    Monday, August 3, 2009 9:23 AM