OpenXML how to check if a row is empty in a sheet

Question

I want to check if a row beside the header row is empty in a spreadsheet. If it's empty, I want to delete the sheet. So far I can only get it to look at cell A2 and check if it empty, but is there a way to check the entire row?

Thanks

private void CheckForEmptyRow(WorkbookPart wbp, string sheetId)
{
   Sheet sheet = wbp.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Id == sheetId);
   WorksheetPart wsp = (WorksheetPart).(wbp.GetPartById(sheetId));
   Cell cell = wsp.Worksheet.Descendants<Cell>().Where(c => c.CellReference == "A2").FirstOrDefault();

   If (cell == null)
   {
       sheet.Remove();
       wbp.DeletePart(wsp);
   }

}

Simply use ClosedXML.Excel instead of OpenXML. See: https://stackoverflow.com/a/73319153/6853263 — Christopher Uduekwe, Aug 11 '22 at 11:00

score 5 · Answer 1 · answered Sep 21 '17 at 21:47

John, trying the row==null did not seem to work for me. For whatever reason the spreadsheets we are trying to parse seems to have hundreds of empty, but not null, rows. It looks like some data was put into there as some point and cleared. I ended up with the code below to return me valid rows. In theory you can just do !row.Elements().Any(ce => ce.DataType != null) on any check

    List<Row> rows = sheetData.Elements<Row>()
     .Where(r => r.Elements<Cell>().Any(ce => ce.DataType != null)
     )
     .ToList()
    ;

score 0 · Accepted Answer · answered Apr 21 '17 at 15:56

You can loop through each row and check if the row is null like this:

private void CheckForEmptyRow(WorkbookPart wbp, string sheetId)
{
   Sheet sheet = wbp.Workbook.Descendants<Sheet>().FirstOrDefault(s => s.Id == sheetId);
   WorksheetPart wsp = (WorksheetPart).(wbp.GetPartById(sheetId));
   IEnumerable<SheetData> sheetData = wsp.Worksheet.Elements<SheetData>();
   bool isRowEmpty = false;

   foreach (SheetData SD in sheetData)
   {
      IEnumerable<Row> row = SD.Elements<Row>(); // Get the row IEnumerator

      if (row == null)
      {
          isRowEmpty = true;
          break;
      }
   }

   if (isRowEmpty)
   {
       sheet.Remove();
       wbp.DeletePart(wsp);
   }
}

I'm having trouble with this line of code? Is there an update for how this might work? {WorksheetPart wsp = (WorksheetPart).(wbp.GetPartById(sheetId));} — IamSierraCharlie, Feb 23 '20 at 07:29

OpenXML how to check if a row is empty in a sheet

2 Answers2