如何在 Apache POI 中获取 Excel 空白单元格值? [英] How to get an Excel Blank Cell Value in Apache POI?

问题描述

我有一个包含大量列的巨大 excel 文件，如下所示:-

I have a huge excel file with tons of columns which looks like this :-

Column1 Column2 Column3 Column4 Column5
abc             def             ghi
        mno             pqr
......

这是我为打印这些值而编写的代码:

This is the code that I wrote to print these values:

try {
    FileInputStream inputStr = new FileInputStream(fileName);
    XSSFWorkbook xssfWork = new XSSFWorkbook(inputStr) ;
    XSSFSheet sheet1 = xssfWork.getSheetAt(0);
    Iterator rowItr = sheet1.rowIterator();

    while ( rowItr.hasNext() ) {
        XSSFRow row = (XSSFRow) rowItr.next();
        System.out.println("ROW:-->");
        Iterator cellItr = row.cellIterator();

        while ( cellItr.hasNext() ) {
            XSSFCell cell = (XSSFCell) cellItr.next();
            System.out.println("CELL:-->"+cell.toString());
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

这段代码生成的输出是:-

The output generated by this code is :-

ROW:-->
CELL:-->Column1
CELL:-->Column2
CELL:-->Column3
CELL:-->Column4
CELL:-->Column5
ROW:-->
CELL:-->abc
CELL:-->def
CELL:-->ghi
ROW:-->
CELL:-->mno
CELL:-->pqr

所以，如果我们查看上面的输出，我们可以注意到我留下空白值的单元格没有被 POI 库提取，有没有办法让这些值作为空值.或识别所呈现的值跳过空白单元格的方法?

So, If we look at the output above we can note that the cells where I left blank values was not picked up by the POI library , is there a way in which I can get these values as null. or a way to recognize that the values presented skipped blank cells?

谢谢.

推荐答案

如果你想获取所有单元格，不管它们是否存在，那么迭代器不适合你.相反，您需要手动获取适当的单元格，可能缺少单元格策略

If you want to get all cells, no matter if they exist or not, then the iterator isn't for you. Instead, you need to manually fetch the appropriate cells, likely with a missing cell policy

for(Row row : sheet) {
   for(int cn=0; cn<row.getLastCellNum(); cn++) {
       // If the cell is missing from the file, generate a blank one
       // (Works by specifying a MissingCellPolicy)
       Cell cell = row.getCell(cn, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
       // Print the cell for debugging
       System.out.println("CELL: " + cn + " --> " + cell.toString());
   }
}

关于迭代单元格的 Apache POI 文档中有关于所有这些的更多详细信息

这篇关于如何在 Apache POI 中获取 Excel 空白单元格值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！