How can I programmatically delete Rows from an excel that are currently empty but have been edited once , using closed xml

Question

I need to delete empty rows from my excel using closed XML. These rows previously had some data but are now empty. I have tried using row.IsEmpty() but it does not delete any rows at all. Below is what I have tried so far:

using (XLWorkbook workBook = new XLWorkbook(destinationPath))
{
    int worksheetCount = workBook.Worksheets.Count;
    for(int worksheetIndex = 1; worksheetIndex <= worksheetCount; worksheetIndex++)
    {
        //Read the first Sheet from Excel file.
        IXLWorksheet workSheet = workBook.Worksheet(1);

        int rowCount = workSheet.RowsUsed().Count();

        //Loop through the Worksheet rows.
        foreach (IXLRow row in workSheet.RowsUsed())
        {
            if (row.IsEmpty())
            {
                row.Delete();
            }
        } 

        workBook.Save();
        int rowCountNew = workSheet.RowsUsed().Count();

My rowCount and rowCountNew have the same values when actually they should be different. Also,even though I have empty rows in the excel my if condition continues to remain false and hence never hits row.delete(). Hope my question makes sense.

Thanks In Advance!

Answer 1

Your core issue is to find out why row.IsEmpty() returns false. For the parameterless .IsEmpty() overload, ClosedXML uses the XLCellsUsedOptions.AllContents value to determine whether cells are empty. This means that if a cell contains values or comments, it is considered non-empty. My guess is that you forgot to clear some comments.

Answer 2

Using closed XML library we can iterate through each workbook and delete a given row based on the row number

     string srcFile ="srcfile.xlsx"; 
     string dstFile = "destnation file.xlsx";
     using (XLWorkbook wb = new XLWorkbook(srcFile))
        {
            foreach (var item in wb.Worksheets)
            {
                item.Row(2).Delete();
            }

            wb.SaveAs(dstFile);
        }