Open XML SDK: How to update Excel cell?

Question

I am trying to update a cell in Excel's spreadsheet via Open XML SDK:

test.xlsx

using System.Linq;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace DotNetSandbox.SO
{
    public class CellUpdating
    {
        public static void Update()
        {
            using SpreadsheetDocument doc = SpreadsheetDocument.Open(@"c:\temp\test.xlsx", true);
            Sheet sheet = doc.WorkbookPart.Workbook.Descendants<Sheet>().First();
            WorksheetPart wsPart = (WorksheetPart)doc.WorkbookPart.GetPartById(sheet.Id);
            Cell cell = wsPart.Worksheet.Descendants<Cell>().First(c => c.CellReference == "A2");

            cell.CellValue = new CellValue("new");

            wsPart.Worksheet.Save();
        }
    }
}

That updates cell, but only after recovering by Excel app:

Environment:

• DocumentFormat.OpenXml: 2.13.0
• Excel: Microsoft 265 MSO (16.0.14026.20270) 64 bit

What I do wrong?

Answer 1

Excel prefers to use SharedStrings instead of inline strings.

If the Cell.DataType == EnumValue<CellValues>(CellValues.SharedString) you can't replace the CellValue without also adding a SharedString.

The easiest way to fix this is to switch the DataType to inline string, aka: CellValues.String and then assign the string value:

    cell.DataType = new EnumValue<CellValues>(CellValues.String); 
    cell.CellValue = new CellValue("new");

Do note that Excel will rewrite your inline string to shared strings on load which is something to be aware of in case you expect the file before and after saving without making any changes to be exactly the same.