Free Support Forum -

How to find all cell of a worksheet in null (have not value)


I have a excel file that have some row that all cell in that row , are null (have no value).

what is the best method to find that all cell of a row in a worksheet that is null (have no value)

i want to remove all row that all cell in that row is null (Have no value)


I think you may use Cells.DeleteBlankRows() method for your needs, see the sample lines of code.

Cells cells = workbook.Worksheets[0].Cells;
//To delete the blank rows.

Thank you.

Hi, Dear Amjad Sahi

Thanks. That’s Work for blank Rows.

There is a another problem. i have a excel file that last row of it has a new line (when i save it as a csv file i can see it) & cell.DeleteBlankRows() can’t delete this row.

How i can delete this row too?


Well, I think you may use Cells.DeleteRow/DeleteRows() method accordingly for your needs.

You may also use Row.GetCellOrNull(int colIndex) method to loop through each cell in a row, if you find
a cell at given position has been instantiated(such as user has modified it, set value to it, …etc.), then the instantiated Cell object will be returned. Otherwise null will be returned.

Thank you.


how i can use Row.GetCellOrNull(int colIndex)


See a sample code for reference, you may use Row.GetCellOrNull and write your own code accordingly.

Sample code:

Workbook wb = new Workbook(@“e:\test2\Book1.xls”);
Hashtable hashTable = new Hashtable();
Cells cells = wb.Worksheets[0].Cells;
for (int i = 1; i < cells.Rows.Count; i++)
Row row = cells.Rows.GetRowByIndex(i);
Aspose.Cells.Cell cell = row.GetCellOrNull(0);
if (cell != null && cell.Type != CellValueType.IsNull)
string cellValue = cell.StringValue;
hashTable.Add(cellValue, cellValue);

else {
//Your code goes here.

Thank you.