CODEWITHSUNDEEP
c#excelopenxmlspreadsheetopenxml-sdk
Adam Shakhabov
Adam Shakhabov

Reputation: 1346

Open XML SDK: How to update Excel cell?

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

test.xlsx

enter image description here

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:

enter image description here

Environment:

What I do wrong?

Upvotes: 4

Views: 3158

Answers (1)

rene
rene

Reputation: 42414

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.

Upvotes: 4

Related Questions