Created Excel file using OpenXML needs repair when opened in Excel

Question

I am trying to insert Excel file (.xlsx) cell using C# openXML,

but when I open the created Excel file it is need to be repaired by showing following error,

Repaired Records: Cell information from /xl/worksheets/sheet1.xml part

Here is my code.

    public void InsertText(TestModelList data)
    {
        var date_time = DateTime.Now.ToString().Replace("/", "_").Replace(":", "_");
        string OutputFileDirectory = "E:\\TEST";
        string fileFullName = Path.Combine(OutputFileDirectory, "Output.xlsx");
        if (File.Exists(fileFullName))
        {
            fileFullName = Path.Combine(OutputFileDirectory, "Output_" + date_time + ".xlsx");
        }

        using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Create(fileFullName, SpreadsheetDocumentType.Workbook))
        {
            WorkbookPart wbp = spreadSheet.AddWorkbookPart();
            WorksheetPart wsp = wbp.AddNewPart<WorksheetPart>();
            Workbook wb = new Workbook();
            Worksheet ws = new Worksheet();
            SheetData sd = new SheetData();
            InsertToCell(1, "C", "1C", sd);
            ws.Append(sd);
            wsp.Worksheet = ws;
            wsp.Worksheet.Save();
            Sheets sheets = new Sheets();
            Sheet sheet = new Sheet()
            {
                Id = wbp.GetIdOfPart(wsp),
                Name  = "test",
                SheetId = 1
            };

            sheets.Append(sheet);
            wb.Append(sheets);
            spreadSheet.WorkbookPart.Workbook = wb;
            spreadSheet.WorkbookPart.Workbook.Save();
        }
    }

    private void InsertToCell(uint rowIndex, string col, string value, SheetData sd)
    {
        var row = new Row() { RowIndex = rowIndex };

        var cellReference = col + rowIndex;

        Cell newCell = new Cell
        {
            StyleIndex = (UInt32Value)1U,
            CellValue = new CellValue(value),
            DataType = CellValues.SharedString,
            CellReference = cellReference
        };
        row.InsertBefore(newCell, null);
        sd.Append(row);
    }

Can anyone please help me to fix this issue?

Answer 1

There are a few issues with the new cell that you are creating. Firstly, you are setting the StyleIndex property (ssml:s attribute) to a value of 1U without having a WorkbookStylesPart that contains a referenceable cell style. Secondly, you are setting the DataType property (ssml:t attribute) to a value of CellValues.SharedString (i.e., "s") without having a SharedStringTablePart. The CellValue property (ssml:v element) should be the zero-based index of the SharedStringItem (ssml:si element) within the SharedStringTable (ssml:sst element) instead of "1C".

Here is some sample code in the form of a working unit test that demonstrates how to do what you want to achieve:

[Fact]
public void CanInsertCell()
{
    using var stream = new MemoryStream();
    using (var spreadsheetDocument =
        SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook))
    {
        // Create an empty workbook.
        WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
        workbookPart.Workbook = new Workbook(new Sheets());

        // Create an empty worksheet and add the worksheet to the workbook.
        var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());
        workbookPart.Workbook.Sheets.AppendChild(new Sheet
        {
            Id = workbookPart.GetIdOfPart(worksheetPart),
            Name = "Test",
            SheetId = 1
        });

        // This example correctly inserts a cell with an inline string,
        // noting that Excel always inserts shared strings as shown in
        // the next example.
        InsertCellWithInlineString(worksheetPart.Worksheet, 1, "C", "1C");

        // This example inserts a cell with a shared string that is
        // contained in the SharedStringTablePart. Note that the cell
        // value is the zero-based index of the SharedStringItem
        // contained in the SharedStringTable.
        var sharedStringTablePart = workbookPart.AddNewPart<SharedStringTablePart>();
        sharedStringTablePart.SharedStringTable =
            new SharedStringTable(
                new SharedStringItem(
                    new Text("2C")));

        InsertCellWithSharedString(worksheetPart.Worksheet, 2, "C", 0);
    }

    File.WriteAllBytes("WorkbookWithNewCells.xlsx", stream.ToArray());
}

private static void InsertCellWithInlineString(
    Worksheet worksheet,
    uint rowIndex,
    string columnName,
    string value)
{
    InsertCell(worksheet, rowIndex, new Cell
    {
        CellReference = columnName + rowIndex,
        DataType = CellValues.InlineString,
        InlineString = new InlineString(new Text(value)),
    });
}

private static void InsertCellWithSharedString(
    Worksheet worksheet,
    uint rowIndex,
    string columnName,
    uint value)

{
    InsertCell(worksheet, rowIndex, new Cell
    {
        CellReference = columnName + rowIndex,
        DataType = CellValues.SharedString,
        CellValue = new CellValue(value.ToString())
    });
}

private static void InsertCell(Worksheet worksheet, uint rowIndex, Cell cell)
{
    SheetData sheetData = worksheet.Elements<SheetData>().Single();

    // Get or create a Row with the given rowIndex.
    Row row = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
    if (row == null)
    {
        row = new Row { RowIndex = rowIndex };

        // The sample assumes that the newRow can simply be appended,
        // e.g., because rows are added in ascending order only.
        sheetData.AppendChild(row);
    }

    // The sample assumes two things: First, no cell with the same cell
    // reference exists. Second, cells are added in ascending order.
    // If that is not the case, you need to deal with that situation.
    row.AppendChild(cell);
}