Detecting hidden cells in excel using apache poi

问题:

We are using apache poi 3.8 to parse excels. We need to be able to detect (and skip) hidden rows as they tend to contain junk data in our usecases.

It would seem this should work:

row.isFormatted() && row.getRowStyle().getHidden()

But there never appears to be any row-level formatting (getRowStyle() always returns null). As a last resort we thought checking cell styles might work:

for (int i = 0; i < row.getLastCellNum(); i++) {
    Cell cell = row.getCell(i);
    if (cell != null && cell.getCellStyle() != null && cell.getCellStyle().getHidden())
        ...

But for every row we get (custom output in the above for loop):

Cell 0 is not hidden org.apache.poi.hssf.usermodel.HSSFCellStyle@1b9142d0 / false

Does the "getHidden()" not work or does it not work as I think it does? Is there another way to detect hidden rows? (hidden columns would also be a nice bonus but slightly less relevant atm)

回答1:

getRowStyle should normally work as you supposed.

Otherwise, you can check the height of the row, as hidden rows tends to have a height set to 0. Using row.getHeight() or row.getZeroHeight().

回答2:

After trying a few approaches, row.getZeroHeight() worked correctly for identifying hidden row. Also for those who are stuck with Apache POI <= 3.7 version this may be the only solution.