Replace text in textbox in Excel using openXML

Question

I want to edit/change the text in an excel sheet which is in a textbox. I iterate through all entries in the sharedstringtable but I cannot find it.

var sharedStringTablePart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
foreach (SharedStringItem ss in sharedStringTablePart.SharedStringTable.Elements<SharedStringItem>())
{
    if (ss.InnerText == text)
    {
        //Do somethinhg
    }
}

Can someone help?

FortyTwo FortyTwo · Accepted Answer · 2017-05-23T16:24:21

Text contained within a textbox is not stored under a sharedstringtable (I know it is confusing). Hope this helps:

using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFile, true))
{
    string sheetName = "Sheet1";
    Sheet sheet = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();

    if (sheet == null)
    {
        // The specified worksheet does not exist.
        return null;
    }

    string relationshipId = sheet.Id.Value;

    WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
    var ocaElems = worksheetPart.DrawingsPart.WorksheetDrawing.Elements<OneCellAnchor>();

    foreach (OneCellAnchor oneCellAnchor in ocaElems)
    {
        var shapes = oneCellAnchor.Elements<A.Shape>();
        foreach (var shape in shapes)
        {
            var text = shape.TextBody.InnerText;
            if(text == mytext)
            {
                //You found the text
            }
        }
    }
}

Replace text in textbox in Excel using openXML

1 Answers