在处理阿帕奇POI空列 [英] handling empty columns in apache poi

问题描述

我有一个程序读取从Java程序Excel工作表。

I have a program reading excel sheet from a java program.

我遍历如下细胞：

Iterator cells = row.cellIterator();
String temp;
StringBuilder sb;
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");

while (cells.hasNext()) {
Cell cell = (Cell) cells.next();
temp = null;

    switch (cell.getCellType()) {
         case Cell.CELL_TYPE_STRING:
         temp = cell.getRichStringCellValue().getString();
         break;

    case Cell.CELL_TYPE_NUMERIC:

    if (DateUtil.isCellDateFormatted(cell)) {
        temp = sdf.format(cell.getDateCellValue());
       } else {
             temp = df.format(new BigDecimal(cell.getNumericCellValue()));
       }
          break;
          default:
     }
    if (temp == null || temp.equalsIgnoreCase("null")) {
        sb.append("").append(";");
    } else {
        sb.append(temp).append(";");
    }
 }

正如所看到的，我想创建一个包含分号隔开的方式从Excel列值的字符串建设者。

As seen, I am trying to create a string builder containing values from excel row in semicolon separated way.

问题是，如果列值为空，我想把它作为字符串生成器连续用两个分号空值。

Issue is, if a column value is empty, I want it as an empty value in the string builder with two consecutive semicolons.

然而，呼叫

细胞的细胞=（Cell）的cells.next（）;

Cell cell = (Cell) cells.next();

会忽略空单元格，并跃过下一个非空单元格。

simply ignores the empty cells and jumps over to next non empty cell.

所以行

如果（临时== NULL || temp.equalsIgnoreCase（空））

if (temp == null || temp.equalsIgnoreCase("null"))

时，从来没有遇到过。

is never met.

如何迭代器得到空列值的处理呢？

How to get a handle on empty column values as well in the iterator ?

推荐答案

这是无形中<一个副本href=\"http://stackoverflow.com/questions/13048981/read-excel-file-2010-apache-poi-ignoring-empty-cells\">this问题等<一个href=\"http://stackoverflow.com/questions/13048981/read-excel-file-2010-apache-poi-ignoring-empty-cells/13051187#13051187\">my这个问题的答案基本上正好适用于你。

This is virtually a duplicate of this question, and so my answer to that question basically applies exactly to you too.

的细胞迭代仅在细胞迭代的文件中定义的。如果细胞从未在Excel中使用，它可能不会出现在文件中（Excel中并不总是一致...），所以POI不会看到它

The Cell Iterator only iterates over cells that are defined in the file. If the cell has never been used in Excel, it probably won't appear in the file (Excel isn't always consistent...), so POI won't see it

如果你想确保你打的每一个细胞，你应该索引查找，而是和任何检查空细胞（表示从来没有在文件中存在的细胞），或设置 MissingCellPolicy 来控制你想怎么来对待空和空白单元格

If you want to make sure you hit every cell, you should lookup by index instead, and either check for null cells (indicating the cell has never existed in the file), or set a MissingCellPolicy to control how you want null and blank cells to be treated

所以，如果你真的想要得到的每一个细胞，这样做：

So, if you really do want to get every cell, do something like:

Row r = sheet.getRow(myRowNum);
int lastColumn = Math.max(r.getLastCellNum(), MY_MINIMUM_COLUMN_COUNT);

for (int cn=0; cn<lastColumn; cn++) {
   Cell c = r.getCell(cn, Row.RETURN_BLANK_AS_NULL);
   if (c == null) {
      // The spreadsheet is empty in this cell
   } else {
      // Do something useful with the cell's contents
   }
}

这篇关于在处理阿帕奇POI空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！