Delete Multiple Cells with ClosedXML

Question

I have to remove empty Cells from Excel Sheets. We using to create and edit files ClosedXML.

Example Excel-Before:

    XX  XX                  XX              XX  XX
    XX  XX                  XX              XX  XX
XX  XX  XX                  XX              XX  XX
XX  XX  XX                  XX              XX  XX

Example Excel-After:

XX  XX  XX  XX  XX
XX  XX  XX  XX  XX
XX  XX  XX  XX  XX  XX
XX  XX  XX  XX  XX  XX

My first test was a foreach over all empty Cells. But this is not working, because only the first cell in Row will be deleted.

    private void DeleteCell()
    {
        List<IXLCell> AllEmptyCells = ws.Cells().Where(w => String.IsNullOrEmpty(w.Value.ToString())).ToList();

        foreach(IXLCell cell in AllEmptyCells)
        {
            cell.Delete(XLShiftDeletedCells.ShiftCellsLeft);
        }
    }

My second test is working, but needs a long time to finish. With every call the list will be new generated until the list is empty.

    private void DeleteCell()
    {
        List<IXLCell> AllEmptyCells = ws.Cells().Where(w => String.IsNullOrEmpty(w.Value.ToString())).ToList();
        if(AllEmptyCells.Count > 0)
        {
            ws.Cell(AllEmptyCells.FirstOrDefault().Address).Delete(XLShiftDeletedCells.ShiftCellsLeft);
            DeleteCell();
        }

    }

For 100 Rows this part will running ~2 Min. Does anyone knows a better way?

Answer 1

Instead of looping over all cells in ws.Cells(), you can look at ws.RangeUsed().Cells(). This will ignore all cells after that last cell that is actually used.

Answer 2

1. You enumerate over all cells in the worksheet, not only the used area.
2. By deleting cells you move new (empty) cells into your range and you move cells with content into the positions of your empty cells.

A faster and cleaner approach would be something like this:

int maxColNo = sheet.LastColumnUsed().ColumnNumber();
foreach (var row in sheet.RowsUsed())
{
    for (int colNo = maxColNo; colNo > 0; colNo--)
    {
        if (row.Cell(colNo).IsEmpty())
        {
            row.Cell(colNo).Delete(XLShiftDeletedCells.ShiftCellsLeft);
        }
    }
}

Answer 3

Im now enumerate with Reverse<T> in my list. Im a little bit faster then before. I dont need to call my method recursive.

    private void DeleteCell()
    {
        AllEmptyCells = ws.Cells().Where(w => String.IsNullOrEmpty(w.Value.ToString())).ToList();

        foreach(var item in AllEmptyCells.Reverse<IXLCell>())
        {
            ws.Cell(item.Address.RowNumber, item.Address.ColumnNumber).Delete(XLShiftDeletedCells.ShiftCellsLeft);
        }
    }