缺少 Apache POI Java 的单元格策略

Missing cell policy of Apache POI Java

提问by Ashley

Can somebody please explain about the Missing cell policyof Apache POI? What are exactly missing cells ? I didn't find the Apache POIdocs linkto be self-explanatory on what exactly are missing cells.

可有人请有关解释Missing cell policy的Apache POI？究竟什么是缺失的细胞？我没有发现Apache POI文档链接对于到底缺少什么单元格是不言自明的。

回答by rgettman

Did you read the Apache POI Excel Busy Developer's Guide?

您是否阅读了Apache POI Excel Busy Developer's Guide？

In some cases, when iterating, you need full control over how missing or blank rows and cells are treated, and you need to ensure you visit every cell and not just those defined in the file. (The CellIterator will only return the cells defined in the file, which is largely those with values or stylings, but it depends on Excel).

In cases such as these, you should fetch the first and last column information for a row, then call getCell(int, MissingCellPolicy) to fetch the cell. Use a MissingCellPolicy to control how blank or null cells are handled.

在某些情况下，在迭代时，您需要完全控制如何处理缺失或空白行和单元格，并且您需要确保访问每个单元格，而不仅仅是文件中定义的单元格。（CellIterator 将只返回文件中定义的单元格，这些单元格主要是具有值或样式的单元格，但它取决于 Excel）。

在这种情况下，您应该获取一行的第一列和最后一列信息，然后调用 getCell(int, MissingCellPolicy) 来获取单元格。使用 MissingCellPolicy 来控制如何处理空白或空单元格。

If you're iterating over columns in a row, some cells that are blank may not even exist, which may causing unsuspecting code to throw a NullPointerException. A MissingCellPolicy, when passed to getCell, guides and simplifies code that tells Apache POI how to handle these kinds of cells.

如果您在一行中迭代列，则某些空白单元格甚至可能不存在，这可能会导致毫无戒心的代码抛出NullPointerException. A MissingCellPolicy，当传递给 时getCell，会引导和简化告诉 Apache POI 如何处理这些类型的单元格的代码。

• CREATE_NULL_AS_BLANK- If the Cellreturned doesn't exist, instead of returning null, create a new Cellwith a cell type of "blank". This can help avoid NullPointerExceptions conveniently.
• RETURN_BLANK_AS_NULL- Even if the cell exists but has a cell type of "blank", return null. This can allow you ignore blank cells that do exist easily.
• RETURN_NULL_AND_BLANK- Don't modify the existing structure; return nullfor cells that don't really exist and return the blank Cellif it exists but its cell type is blank. This is the behavior of the getCelloverload that doesn't take a MissingCellPolicy.

• CREATE_NULL_AS_BLANK- 如果Cell返回的不存在，而不是返回null，创建一个新Cell的单元格类型为“空白”。这可以帮助NullPointerException方便地避免s。
• RETURN_BLANK_AS_NULL- 即使单元格存在但单元格类型为“空白”，也返回null. 这可以让您忽略容易存在的空白单元格。
• RETURN_NULL_AND_BLANK- 不修改现有结构；返回null实际上不存在的单元格，Cell如果存在则返回空白，但其单元格类型为空白。这是getCell不带MissingCellPolicy.

回答by Joseph

I'm using the code in java as below, it's working good for me :) hope it helps.

我正在使用 Java 中的代码，如下所示，它对我很有用:) 希望它有所帮助。

ArrayList<ArrayList<String>> cellArrayListHolder = new ArrayList<ArrayList<String>>();
FileInputStream excelFile = new FileInputStream(new File(fileName));

Workbook workbook = new XSSFWorkbook(excelFile);
Sheet datatypeSheet = workbook.getSheetAt(0);
Iterator<Row> iterator = datatypeSheet.iterator();
while (iterator.hasNext())
{
    ArrayList<String> cellStoreArrayList = new ArrayList<String>();
    Row currentRow = iterator.next();
    Iterator<Cell> cellIterator = currentRow.iterator();
    int column_counting = 0;
    int patched_count = 0;
    while (cellIterator.hasNext() && column_counting < read_column_size) {
        column_counting ++;
        Cell currentCell = cellIterator.next();
        int missed_column = 1 - column_counting + currentCell.getColumnIndex() - patched_count;  
        for(int i=0; i<missed_column; i++){
            cellStoreArrayList.add("");
            patched_count++;
        }
        switch (currentCell.getCellType()){
        case Cell.CELL_TYPE_STRING:
            cellStoreArrayList.add(String.valueOf(currentCell).trim());
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(currentCell)) {
                DateFormat db_df = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
                cellStoreArrayList.add(db_df.format(currentCell.getDateCellValue()));
            } else {
                cellStoreArrayList.add(String.valueOf(currentCell.getNumericCellValue()));
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            cellStoreArrayList.add(String.valueOf(currentCell.getBooleanCellValue()));
            break;
        default:
            cellStoreArrayList.add("");
            break;
        }
    }
    cellArrayListHolder.add(cellStoreArrayList);
}