Appending new cells breaks the excel file

0
votes

This might be the duplicate as many posts refer to this kind of issues but I could not find an exact answer to this case.

So, what I have is an Excel file where cell "E4" contains a formula "=C4+D4". All other cells are empty, which means I cannot search or get them via OpenXml, simply because they do not exist in Xml. So in order to fill the cells "C4" and "D4" I have to create them (like this:)

var cell = new Cell(){
    CellReference = new StringValue("C4"),
    DataType = new EnumValue<CellValues>(CellValues.Number),
    CellValue = new CellValue("123")
}

the same for cell "D4" and then append these cells to the row

row.Append(cell);

After I open the excel file it show an error "Excel found unreadable content in file.xlsx. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes."

I checked and when there is no formula in excel file the cells are appended correctly.

So my question is, using OpenXml how do I append cells in Excel file, which contains formula, so that not to break the file?

2
c#excelopenxml

2 Answers

1
votes

So the XML is malformed and corrupted the has file, hence the error:

Excel found unreadable content in file

To troubleshoot and fix this I suggest you compare the manually created xlsx to the programmatically created xlsx.

To do this you rename both files extensions from XLSX to ZIP > extract them to different folders > use a WinDiff tool (or better the "OpenXML SDK Productivity Tool") to compare the files.

Once you find how its malformed change your code to try and fix it up.

0
votes

I solved my problem using EPPLUS

using (ExcelPackage excelPackage = new ExcelPackage(fileStream))
{
    ExcelWorkbook excelWorkBook = excelPackage.Workbook;
    ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First();
    excelWorksheet.Cells["C4"].Value = "123";
    excelWorksheet.Cells["D4"].Value = "321";

    excelPackage.SaveAs(memoryStream);
}