How to insert a row into an existing excel file using OpenXmlSDK?

Question

excel

I'm trying to insert a string into a '*.xlsx' file using the OpenXml SDK.
My code:

using var document = SpreadsheetDocument.Open("dspreadsheet.xlsx", true);
    WorksheetPart worksheetPart = document.WorkbookPart.WorksheetParts.First();
    var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
    InsertRow(sheetData, 5);
    worksheetPart.Worksheet.Save();


    static void InsertRow(SheetData sheetData, uint rowIndex)
    {
        Row row = new Row();    
                  
        Cell newCell = new Cell { DataType = CellValues.InlineString };            
        var inlineString = new InlineString();
        var text = new Text { Text = "some text for cell" };
        inlineString.Append(text);
        newCell.AppendChild(inlineString);
        row.AppendChild(newCell);        
                             
        var retRow = sheetData.Elements<Row>().ElementAt((int)rowIndex);    
        sheetData.InsertAfter(row, retRow);

        var rows = sheetData.Elements<Row>().ToArray();
        uint idx = 0;
        //here I set a sequentional RowIndex for each row
        // if I do not use this code error described below remains
        foreach (var r in rows) 
            r.RowIndex = ++idx;
    }

I want to add a new line after the 5th line. NEo when I open the generated file in Excel 2016, I get this error:



I can open this file in LibreOffice Calc 6.4.3.2, but it does not have the added line.
If you use `sheetData.Append(row);` instead of `sheetData.InsertAfter(row, retRow);` it works and the file can be opened in Excel. But this method allows you to add lines only to the end of the file, and not to the middle.
Maybe someone knows how to correctly insert lines into existing .xlsx files? Thanks in advance!

Answer 1

try like this

using (var document = SpreadsheetDocument.Open("dspreadsheet.xlsx", true))
{
    WorksheetPart worksheetPart = document.WorkbookPart.WorksheetParts.First();
    var sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
    InsertRow(sheetData, 5);
    worksheetPart.Worksheet.Save();
}

    static void InsertRow(SheetData sheetData, uint rowIndex)
    {
        Row row = new Row();    
                  
        Cell newCell = new Cell { DataType = CellValues.InlineString };            
        var inlineString = new InlineString();
        var text = new Text { Text = "some text for cell" };
        inlineString.Append(text);
        newCell.AppendChild(inlineString);
        row.AppendChild(newCell);        
                             
        var retRow = sheetData.Elements<Row>().ElementAt((int)rowIndex);    
        sheetData.InsertAfter(row, retRow);
    }