阅读Excel中的空白单元格，并返回列表＆LT;名单，LT;弦乐＆GT;＆GT; [英] Reading blank cells of excel and returning List<List<String>>

问题描述

我的excel文件有4行2列像
具有3row第一栏为空白值连续值象下面这样

my excel file has 4 rows and 2 columns like with continuous values having 3row 1st column as blank value like given below

phone       plan
------------------
85366667777 PLATINUM
85367890000 GOLD
85362524232 SILVER
null        SILVER
85362522436 DIAMOND

---

public List<List<String>> readExcelFile(String fileName) {

        try {
            FileInputStream fileInputStream = new FileInputStream(fileName);
            POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
            HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
            HSSFSheet hssfSheet = workBook.getSheetAt(0);
            Iterator<Row> rowIterator = hssfSheet.rowIterator();
            boolean headerRow = true;
            while (rowIterator.hasNext()) {
                if (headerRow == false) {
                String Rows = util.loadfile("config/ConfigValue.properties","NoOfRowsToSkip");
                int i = Integer.parseInt(Rows);
                    HSSFRow hssfRow = (HSSFRow) rowIterator.next();
                    if(hssfRow.getRowNum()==0||hssfRow.getRowNum()<=i){
                           continue; //just skip the rows if row number is 0 TO i
                          }
                    Iterator<Cell> iterator = hssfRow.cellIterator();
                    List cellTempList = new ArrayList();
                    while (iterator.hasNext()) {
                        HSSFCell hssfCell = (HSSFCell) iterator.next();

                    if(hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC)
                        {DecimalFormat df = new DecimalFormat("#");
                        String cellValue=df.format(hssfCell.getNumericCellValue());
                        cellTempList.add(cellValue.toString());

                        }
                         else if(hssfCell.getCellType() == hssfCell.CELL_TYPE_STRING)
                         {
                         cellTempList.add(hssfCell.toString());
                     }

                    }

                    cellDataList.add(cellTempList);
                } else {
                    headerRow = false;
                }
            }
        } catch (Exception e) {

            e.printStackTrace();
            logger.error("Other Exception : " + e.getStackTrace());
        }
        System.out.println(cellDataList); 
        return cellDataList;
    }
    }

输出： [85366667777，铂金]，[85367890000，金]，[85362524232，SILVER]，[银]，[85362522436，钻石]]

为什么不打印 [空，银] 或 [0，SILVER] 时，有没有价值在相应的单元格？

Why it is not printing [null,Silver] or [0,SILVER] when there is no value in its corresponding cell ?

推荐答案

您应该使用缺少格策。在某些情况下，当迭代，你需要对缺失或空白单元格如何对待完全控制，你需要确保你访问的每一个细胞，而不仅仅是那些文件中定义。在这样的情况下你所提到的，你应该为取一排的第一个和最后一列的信息，然后调用getCell（INT，MissingCellPolicy）来获取细胞。使用 MissingCellPolicy 来控制如何空白或空细胞进行处理。

You should use Missing Cell Policy. In some cases, when iterating, you need full control over how missing or blank cells are treated, and you need to ensure you visit every cell and not just those defined in the file. In cases such as you have mentioned, 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.

举个例子，行像

下面A2为空，insted的空白，为你的情况。在这里，你需要单元格的值指定为手机电池= row.getCell（INT为arg0，ARG1 MissingCellPolicy）：细胞-行

Here A2 is null, insted of blank, as in your case. Here you need to assign cell value as Cell cell = row.getCell(int arg0, MissingCellPolicy arg1): Cell-Row

有三种类型MissingCellPolicy作为

There are three types of MissingCellPolicy as

CREATE_NULL_AS_BLANK ， CREATE_BLANK_AS_NULL 和 RETURN_NULL_AND_BLANK

使用它作为以下

for (int i = 0; i<3 ;i++){
    for(int j=0 ; j<2; j++){
        Row row = sheet1.getRow(i);
        Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
//              Cell cell = row.getCell(j, Row.RETURN_NULL_AND_BLANK);
//              if(row.getCell(j)==null){
//                  System.out.print("Null ");
//              }else
            System.out.print(cell.toString()+" - ");
    }
    System.out.println();
}

下面每当遇到null值的任何单元格，然后 Row.CREATE_NULL_AS_BLANK 空白返回单元格的值。而不是抛出 NullPointerException异常。在这种情况下，输出将是

Here whenever any cell with null value is encountered, then Row.CREATE_NULL_AS_BLANK return the cell value with BLANK. instead of throwing NullPointerException. In this case the output will be

A1 - B1 - 
 - B2 - 
A3 - B3 - 

如果您使用 Row.RETURN_NULL_AND_BLANK ，那么它会在任何细胞的情况下返回NULL没有定义。所以你需要创建一个空检查（如code注释）。在这种情况下，输出将是

If you use Row.RETURN_NULL_AND_BLANK, then it will return Null in case of any cell is not defined. so you need to create a null check (As commented in code). in this case Output will be

 A1 - B1 - 
 Null B2 - 
 A3 - B3 - 

这篇关于阅读Excel中的空白单元格，并返回列表＆LT;名单，LT;弦乐＆GT;＆GT;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！