Delete Rows from existing excel using OpenXml

Question

I need to delete certain rows from an excel file which is created based on a template using openXml and may have extra rows at the bottom. I tried to remove it using row.Remove() and re-assign the RowIndex for the remaining rows .But nothing succeeded .

Any help is appreciated.
Thanks In Advance

Answer 1

Hi @Subin Joseph

Yes .I will find the Row to be deleted, then remove all its children and Remove Row as well. After that I will save worksheet and workBook . Data is clearing from rows but the borders are still kept .Below is the code I used.

Do you mean the row data has been deleted, but it doesn't re-index the remaining rows? Like this:

If that is the case, check your code about the Re-index remaining rows, the issue relates it.

I'm using the following code to delete rows and re-index the remaining rows, you can refer to it:

        public IActionResult ExcelIndex()
        {
            var filepath = Path.Combine(_env.WebRootPath, "files","Test.xlsx");
             
            try
            { 
                DeleteRows(filepath, 5, 10);  //worked 
            }
            catch (Exception ex)
            { 
            }
            return View();
        }
        public void DeleteRows(string filePath, int startRow, int endRow)
        {
            // Open the Excel file
            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
            {
                WorkbookPart workbookPart = document.WorkbookPart;
                // Assuming we are working on the first sheet
                WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
                SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                // Get rows to delete within the specified range
                var rowsToDelete = sheetData.Elements<Row>()
                                            .Where(r => r.RowIndex.Value >= startRow && r.RowIndex.Value <= endRow)
                                            .ToList();

                foreach (var row in rowsToDelete)
                {
                    // Remove the row from the SheetData
                    sheetData.RemoveChild(row);
                }

                // Re-index remaining rows
                #region
                var rowsAfterDeletion = sheetData.Elements<Row>()
                                                 .Where(r => r.RowIndex.Value > endRow)
                                                 .OrderBy(r => r.RowIndex.Value)
                                                 .ToList();

                foreach (var row in rowsAfterDeletion)
                {
                    // Calculate the new row index
                    uint newRowIndex = row.RowIndex.Value - (uint)(endRow - startRow + 1);

                    // Update row index
                    row.RowIndex.Value = newRowIndex;

                    // Update each cell's reference
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // Update cell reference (e.g., A10 to A9)
                        string cellReference = cell.CellReference.Value;
                        string column = new string(cellReference.Where(char.IsLetter).ToArray());
                        cell.CellReference.Value = column + newRowIndex.ToString();
                    }
                }
                #endregion

                // Save changes
                worksheetPart.Worksheet.Save();
            }
        }

After running the above method, the excel file like this:

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion