How to insert before row in the OpenXml SDK SpreadsheetDocument Table?

Question

This my insert after row code:

using (var spreadSheet = SpreadsheetDocument.Open(memoryStream, true, openSettings))
{

    var worksheet = GetWorksheet(spreadSheet);
    var worksheetPart = worksheet.WorksheetPart; 
    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

    var newRowIndex = 9;
    foreach (var item in Items)
    {
        newRowIndex++;

        var newRow = new Row()
        {
            RowIndex = (uint)newRowIndex
        };
        var lastRow = sheetData.Elements<Row>().LastOrDefault(l => l.RowIndex == newRowIndex - 1); 

        sheetData.InsertAfter(newRow, lastRow);
    }

    worksheet.Save(); 

}

And my excel report template:

This code works fine, but result is not correct. The problem is that new rows should insert before 9^th row.

How can I solve this problem?

Answer 1

You're not going to like this... The problem is that you have rows 9 and 11 in your template. You have to set them correctly, as in their RowIndex have to be updated, and the child Cell elements of Row elements have to have their CellReference property (as well as CellFormula if you have them) corrected.

Say you have 6 new items. Then row 9 becomes row 15. And the cell A9 in the "old" row 9 have to be updated to A15. I've given code that does the updating of the RowIndex and CellReference but it's not foolproof. YOU'VE BEEN WARNED.

Also note that I've changed the starting index from 9 to 8. That's because the code increments first (newRowIndex++) before doing the InsertAfter(). Oh you'll figure it out...

Also, I'm updating row 11 first before row 9 because I'm afraid of collisions. If you have 2 new items, and you update row 9 first, it becomes row 11. And then you have two row 11's. So which was the original row 11? In these cases, when increasing row indices, start with the row with the higher RowIndex.

using (var spreadSheet = SpreadsheetDocument.Open(memoryStream, true, openSettings))
{

    var worksheet = GetWorksheet(spreadSheet);
    var worksheetPart = worksheet.WorksheetPart;
    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

    Row currentRow;
    Row cloneRow;
    Cell currentCell;
    Cell cloneCell;

    // Replace for rows 11 and 9, because they exist after your inserted rows

    currentRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == 11);
    cloneRow = (Row)currentRow.CloneNode(true);
    cloneRow.RowIndex += (uint)Items.Count;
    foreach (var child in cloneRow.ChildElements)
    {
        if (child is Cell)
        {
            currentCell = (Cell)child;
            cloneCell = (Cell)currentCell.CloneNode(true);
            // IMPORTANT! this is a very simplistic way of replace something like
            // A11 to A16 (assuming you have 5 rows to insert)
            // A more robust way of replacing is beyond this solution's scope.
            cloneCell.CellReference = cloneCell.CellReference.Value.Replace("11", cloneRow.RowIndex);
            cloneRow.ReplaceChild<Cell>(cloneCell, currentCell);
        }
    }
    sheetData.ReplaceChild<Row>(cloneRow, currentRow);

    currentRow = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == 9);
    cloneRow = (Row)currentRow.CloneNode(true);
    cloneRow.RowIndex += (uint)Items.Count;
    foreach (var child in cloneRow.ChildElements)
    {
        if (child is Cell)
        {
            currentCell = (Cell)child;
            cloneCell = (Cell)currentCell.CloneNode(true);
            cloneCell.CellReference = cloneCell.CellReference.Value.Replace("9", cloneRow.RowIndex);
            cloneRow.ReplaceChild<Cell>(cloneCell, currentCell);
        }
    }
    sheetData.ReplaceChild<Row>(cloneRow, currentRow);

    var newRowIndex = 8;
    foreach (var item in Items)
    {
        newRowIndex++;

        var newRow = new Row()
        {
            RowIndex = (uint)newRowIndex
        };
        var lastRow = sheetData.Elements<Row>().LastOrDefault(l => l.RowIndex == newRowIndex - 1);

        sheetData.InsertAfter(newRow, lastRow);
    }

    worksheet.Save();
}

Answer 2

OK!!!,thanks all and I success this Question for answer in the EPPlus

For example:

  public static void AppendRefRow(string path)
    { 
        using (var pck = new ExcelPackage(new FileInfo(path)))
        {

            var ws = pck.Workbook.Worksheets.FirstOrDefault();
            var refRowIndex = 9;
            var refColumnIndex = 1; 

            for (int index = Items.Length - 1; index >= 0; index--)
            {
                ws.InsertRow(refRowIndex, 1, refRowIndex);

                ws.Cells[refRowIndex, refColumnIndex + 0].Value = index + 1;
                //TODO: write here other rows...

            }

            pck.Save();
        }