Unable to add clone row before a particular row using openxml C# in VS2017

Question

I am currently having an issue when I try to clone and insert a line using open xml.

I noticed that in my code below, if the srcRowIdx and the refRow is the same index, I would be able to clone the row with the inserted line and save the excel sheet successfully. However, if srcRowIdx and refRow is different, the excel sheet will be corrupted. Anyone able to enlighten me on why this is so?

public Row CopyLine(SpreadsheetDocument excelDoc, string sheetName, int srcRowIdx, int refRow)
            {
                var worksheetPart = GetWorksheetPart(excelDoc, sheetName);
                var sheetData = worksheetPart.Worksheet.Descendants<SheetData>().First();
                var srcRow = GetRow(worksheetPart, srcRowIdx);
                var clonedRow = (Row)srcRow.CloneNode(true);
                var rowRef = GetRow(worksheetPart, refRow);

                //Update all indexes to index+1
                IEnumerable<Row> rows = sheetData.Descendants<Row>().Where(r => r.RowIndex.Value >= refRow);
                foreach (Row row in rows)
                {
                    var clonedRowIndex = Convert.ToUInt32(row.RowIndex.Value + 1);

                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // Update the references for reserved cells.
                        string cellReference = cell.CellReference.Value;
                        cell.CellReference =
                            new StringValue(cellReference.Replace(row.RowIndex.Value.ToString(), clonedRowIndex.ToString()));
                    }
                    // Update the row index.
                    row.RowIndex = new UInt32Value(clonedRowIndex);
                }

                sheetData.InsertBefore(clonedRow, rowRef);

                worksheetPart.Worksheet.Save();
                return clonedRow;
            }

if you post a link to your spreadsheet, we can better help you. — Taterhead
This is the link of the template i am using drive.google.com/file/d/0BxwJ_UygiRkKTlR4ZGM3VHdteDQ/… — Little Kid
Validation error: drive.google.com/file/d/0BxwJ_UygiRkKOHBEWHcwZXkyUlk/… — Little Kid

Taterhead Taterhead · Accepted Answer · 2017-10-04T19:44:12

OK - the reason why this works when the src and ref rows are equal is because in line 4:

var clonedRow = (Row)srcRow.CloneNode(true);

you never change the RowIndex or any of the CellReferences of this cloned Row. So it always stays at the index of the src.

When src and ref are equal, the RowIndex and CellReferences of the clone node are ok, and every row after is incremented by 1. And Excel validates this correctly. For example, when src and ref are 24, the cloned node CellReferences stay at 24, all Rows CellReferences 24 and up are incremented by 1, so the sheet is in order.

However, take the case when src is 24 and ref is 27. The cloned row has RowIndex and CellReferences where the Index is 24. When you insert this in the 27th spot and increment all of the rows after that by 1, then your sheet Rows are off:

(starting at Row 23:)
Row 23 has Row Index of 23 and cells with CellReferences of 23
Row 24 has Row Index of 24 and cells with CellReferences of 24
Row 25 has Row Index of 25 and cells with CellReferences of 25
Row 26 has Row Index of 26 and cells with CellReferences of 26
Row 27 has Row Index of 24 and cells with CellReferences of 24
Row 28 has Row Index of 28 and cells with CellReferences of 28

(and so on)

When Excel opens this and validates, it chokes on row 27 and will remove it for you if you let it.

To solve this problem, you need to update the clonedRows RowIndex and CellReferences to the target rows numbers. So you are real close.

Unable to add clone row before a particular row using openxml C# in VS2017

1 Answers