How to add a new cell in an existing row in .xlsx file using open xml sdk?

Question

I have an .xlsx file, which already has some data. I want to enter some data on I1 cell.

Update: I tried this:

WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();                
SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

Row row1 = new Row()
{
  RowIndex = (UInt32Value)1U
};

Cell cell = new Cell() { CellReference = "I1" };
CellFormula cellformula = new CellFormula();
cellformula.Text = "IF(A2 = A1,1,0)";
CellValue cellValue = new CellValue();
cellValue.Text = "0";
cell.Append(cellformula);
cell.Append(cellValue);

row1.Append(cell);

sheetData.Append(row1);

But it is not working.

Please provide sample code with your answer.

Answer 1

You are creating a new Row object even though one already exists in your document. You then give it a RowIndex which also already exists which means you'll end up with a corrupted file (you can't have two rows with the same index).

You need to find the existing Row and add your new Cell to that rather than creating a brand new Row. The easiest way to do that is to call GetFirstChild<Row> on sheetData.

As an aside, I would also not write the CellValue to the Cell as you are using a formula. In that instance the Value is used by Excel to prevent it having to recalculate the formula when the sheet loads. In your example though your value is wrong. Omitting the value altogether is probably the easiest way to guarentee correctness as Excel will then calculate the formula when the sheet loads.

That leads to the following code:

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filename, true))
{
    WorkbookPart workBookPart = spreadsheetDocument.WorkbookPart;

    WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
    WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

    //find the first row in the sheet data
    Row row1 = sheetData.GetFirstChild<Row>();

    //create a new cell
    Cell cell = new Cell() { CellReference = "I1" };
    CellFormula cellformula = new CellFormula();
    cellformula.Text = "IF(A2 = A1,1,0)";
    cell.Append(cellformula);

    //append the cell to the row
    row1.Append(cell);
}