EPPlus InsertRow corrupts excel file

Question

I am currently comparing different files with each other and put the output in an Excel Workbook with multiple worksheets (one per file comparison) using EPPlus 4.5.3.3. The worksheets are simple ranges - no tables. Sometimes it is required to move the content of one Excel Sheet to a certain row in another Excel Sheet. For this I am using the InsertRow function of EPPlus. The function itself does the trick and inserts the content at the desired row. However, when opening the Workbook I receive these errors:

The content of that XML file is the following:

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">

<logFileName>error044200_02.xml</logFileName>

<summary>Errors were detected in file 'C:\Users\...\test.xlsx'</summary>


-<repairedParts>

<repairedPart>Repaired Part: /xl/worksheets/sheet5.xml part with XML error. Load error. Line 1, column 0.</repairedPart>

</repairedParts>

When I am adding the content at the last row without using InsertRow everything is working fine. Also, if I save the ExcelPackage immediately after using InsertRow the error is also present, so it definitely has something to do with this function (also if I am only using one Worksheet). After using InsertRow it seems like the whole formatting of the respective Excel Sheet is screwed up. The inserted content appears to be right though. The excel is initially created from an empty template excel that contains nothing but the header names (also had the same issue with a template without anything inside):

The following code can be used to reproduce (this is just an example - in my case every sheet has more than 50 rows):

    FileInfo filePath= new FileInfo(fileName);
    excel = new ExcelPackage(filePath);
    ExcelWorkbook wb = excel.Workbook;
    foreach (ExcelWorksheet ws in wb.Worksheets)
    {
        int i = 5;
        ws.InsertRow(i, 1);
        //same effect with ws.InsertRows(i, 1, i-1)
        ws.Cells[i, 1].Value = "test";
        ws.Cells[i, 2].Value = "test";
       //excel.Save();
    }
    excel.Save();

Answer 1

Although my answer is not based on EPPlus, have you looked at this post ?

Inserting new rows and moving exsisting ones with OpenXML SDK 2.0

I am guessing that you are getting errors because the newly added rows from the ws.InsertRow function are not updating the required row indexes. This is mentioned in the post.

Also there is a recommendation in the comments "To anyone who is getting an error when opening the file try this line before you save your document'... xlsFile.WorkbookPart.DeletePart(xlsFile.WorkbookPart.CalculationChainPart);