X
X
xmdv2020-05-14 10:26:49
excel
xmdv, 2020-05-14 10:26:49

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

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:

vCkNZ.png

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 the question

In order to leave comments, you need to log in

1 answer(s)
#
#, 2020-05-14
@xmdv

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);
    }

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question