Detecting Empty Worksheets

Check for Populated Cells

Worksheets can have one or more cells populated with values where a value can be simple (text, numeric, date/time) or a formula or a formula‑based value. In such a case, it is easy to detect if a given worksheet is empty or not. All we have to check is the Cells.max_data_row or Cells.max_data_column properties. If the aforementioned properties return zero or positive values, that means one or more cells have been populated; however, if either property returns -1, it indicates that no cells have been populated in the given worksheet.

The rows & columns collections have zero‑based indices; therefore, a cell at row 0 & column 0 refers to the first cell in the worksheet, which is A1.

Check for Empty Initialized Cells

All cells that have values are automatically initialized, however, there is a possibility that a worksheet has cells with only formatting applied. In such a scenario, the Cells.max_data_row or Cells.max_data_column properties will return -1, indicating the absence of any populated values, but initialized cells due to formatting cannot be detected using this approach. To check if a worksheet contains initialized cells (even if they are empty), it is advisable to use the IEnumerator.MoveNext method on the enumerator obtained from the Cells collection. If the IEnumerator.MoveNext method returns true, that means there is at least one initialized cell in the given worksheet.

Check for Shapes

It is possible that a given worksheet does not have any populated cells; however, it could contain shapes & objects such as controls, charts, images, and so on. If we need to check whether a worksheet contains any shapes, we can do so by inspecting the ShapeCollection elements. Any positive value indicates the presence of shape(s) in the worksheet.

Programming Sample