Delete Row using C# DocumentFormat.OpenXml

Question

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}";
    }

Answer 1

I tried to hide the extra empty rows. Just like what we discussed, the row count in my file is 5 so that I can't remove rows which index is more than 10. Then I have a workaround to hide the rows and the test result is like below.

WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

var rowcounts = sheetData.Elements<Row>().Count();
var rowsToRemove = sheetData.Elements<Row>()
                    .Where(row => row.RowIndex > 10)
                    .ToList();
// Remove empty rows
foreach (var row in rowsToRemove)
{
    sheetData.RemoveChild(row);
}

for (uint rowIndex = 10; rowIndex <= 1048576; rowIndex++) // Excel has 1048576 rows in total
{
    Row row = new Row() { RowIndex = rowIndex, Hidden = true };
    sheetData.Append(row); // Add hidden rows
}

worksheetPart.Worksheet.Save();