How to insert an empty cell to a DocumentFormat.OpenXml.Spreadsheet.Row?

Question

I'm using DocumentFormat.OpenXml 2.20.0 to insert a row to a sheet in an Excel file. I use the code below to append cells into a row then insert the row to the sheet.

// Alloc a new row.
Row newRow = new Row();
foreach (string value in values)
{
    // Add a new cell to the row
    if (string.IsNullOrEmpty(value))
    {
        newRow.Append(new Cell()
        {
            DataType = new EnumValue<CellValues>(CellValues.String), // Force to string instead of sharedString
            CellValue = new CellValue() // empty cell.  
        });
    }
    else
    {
        newRow.Append(new Cell()
        {
            DataType = new EnumValue<CellValues>(CellValues.String), // Force to string instead of sharedString
            CellValue = new CellValue(value)
        });
    }
}

sheetData.InsertAt(newRow, lineCnt++);

If all values are not empty, the updated Excel file is good. However, if any of the cells are empty, I get the message "We found a problem with some content in ... Do you want us to try to recover as much as we can ...". Any idea what DataType should be used for an empty cell? Do I miss anything else?

Answer 1

I think the problem happen when you insert at not the next row, Update your code like this

            // Alloc a new row.
            Row newRow = new Row();
            foreach (string value in values)
            {
                // Add a new cell to the row
                newRow.Append(new Cell()
                {
                    DataType = new EnumValue<CellValues>(CellValues.String), // Force to string instead of sharedString
                    CellValue = new CellValue(value)
                });
            }
            // check if it is the first row
            Row lastRow = sheetData.Elements<Row>().LastOrDefault();
            if (lastRow != null)
            {
                newRow.RowIndex = (lastRow.RowIndex + 1);
                sheetData.InsertAfter(newRow, lastRow);
            }
            else
            {
                newRow.RowIndex = 0;
                sheetData.InsertAt(newRow, 0);
            }
            worksheet.Save();