I’m using C# DocumentFormat.OpenXml to edit excel file.but have some issues.

Hope everyone can help me to resolved this issues.

I want delete (real or empty) row， but it didn’t work for me。

I’m using sheetData.Elements<Row>() it can’t find empty row.

Have any function can do it Using C# DocumentFormat.OpenXml ?

before

after

I need to delete (Row > Row 93) all Row. Like Picture not only delete cell string. This is my code to delete excel row, but it didn’t work.

static void DeleteRowsCompletely(string filePath, string sheetName, int startRow, int endRow)
    {
        using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))
        {
            WorkbookPart workbookPart = document.WorkbookPart;
            var sheets = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
            if (!sheets.Any())
            {
                throw new Exception("Sheet not found.");
            }

            var sheet = sheets.First();
            WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
            var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            for (uint i = (uint)startRow; i <= endRow; i++)
            {
                var row = sheetData.Elements<Row>().FirstOrDefault(r => r.RowIndex == i);

                if (row != null)
                {
                    if (sheetData.Contains(row))
                    {
                        sheetData.RemoveChild(row);
                    }
                }
            }

            foreach (var row in sheetData.Elements<Row>().Where(r => r.RowIndex.Value > endRow))
            {
                uint newRowIndex = row.RowIndex.Value - (uint)(endRow - startRow + 1);
                row.RowIndex.Value = newRowIndex;

                foreach (var cell in row.Elements<Cell>())
                {
                    string oldCellReference = cell.CellReference.Value;
                    cell.CellReference = new StringValue(UpdateCellReference(oldCellReference, newRowIndex));
                }
            }

            worksheetPart.Worksheet.Save();
        }
    }

    static string UpdateCellReference(string cellReference, uint newRowIndex)
    {
        string columnPart = new string(cellReference.Where(char.IsLetter).ToArray());
        return $"{columnPart}{newRowIndex}";
    }