Detection of empty cells

Aspose.Cells Product Family
1

Hi,
I replaced jxl library with Aspose.cell and all works fine, but I have a case of low performace when I have to detect empty cells.
With jxl I used method cell.getContent(), while with Aspose.cell I use method cells.checkCell(), but there is a great difference of performance.
To give an idea of difference, the time to execution of a same program is 43 sec with jxl and 372 sec with Aspose.cell (if I use method cell.getStringValue() the execution time is 1336 sec!!!).
Can I use a faster way to do it?

Best regards

2

Hi,

In order to increase performance when you detect empty cells, please use cell.getValue()==null condition. Please see the code below and the output.

In case, it is not null, only then you should use cell.getStringValue()=="" condition to find if the cell is blank.

Java


Workbook workbook = new Workbook();

Cell cell = workbook.getWorksheets().getSheet(0).getCells().getCell(“D1”);

if(cell.getValue()==null)
{
System.out.println(“cell is empty”);
}

Output
cell is empty

3

Hi,
I modified my program as you suggest, but performance is still too slow.

The elaboration time for the same file is 2 min 11 sec 738 msec with Aspose.cell, 1 sec 739 msec with jxl, so 120 times more slow.

The problem is in this line:
if (sh.getCell(row,col).getValue() != null || sh.getCell(row,col+1).getValue() != null) {…}

For me this elaboration time is critical.

Others suggestions?

Best regards

4

Hi,

To check empty cells, please try the following code segment:

Iterator iterRow = cells.getRowIterator();

int nextRow = 0;

while(iterRow.hasNext())

{

Row row = iterRow.next();

if(row.getRowIndex() > nextRow)

{

//all rows/cells between nextRow and row.getRowIndex are empty

}

Iterator iterCell = row.getCellIterator();

int nextCol = 0;

while(iterCell.hasNext())

{

Cell cell = iterCell.next();

if(cell.getColumnIndex() > nextCol)

{

//all cells between nextCol and cell.getColumnIndex() are empty

}

if(cell.getValueType() == CellValueType.NULL)

{

//emtpy cell which has been instantiated by has no content.

}

nextCol = cell.getColumnIndex()+1;

}

}

Hope, this helps,

Thank you.

5

Thanks a lot for this suggestion, with iterator the performance is better.
I have only another question: to scroll the rows there are 2 methods, cells.getMaxRow() and cells.getMaxDataRow(). The first detects empty rows, if they are formatted.
I’d like to know if iterator detects empty rows.

Best regards

6

Hi,

The iterator will provide all Row/Cell objects that have been instantiated, no matter if they are empty (without any custom formatting or values) or not. For example, for a new empty Workbook, before you call Cells.getRow(...)/getCell(...), there is no Row/Cell objects can be fetched from the iterator. After you can call Cells.getCell(“A1”), there will be a Cell object for “A1” and it can be fetched from the iterator even if you have not assigned style or value to it.


Thank you.