读取excel的空白单元格并返回List<List<String>> [英] Reading blank cells of excel and returning List&lt;List&lt;String&gt;&gt;

问题描述

我的 excel 文件有 4 行和 2 列，例如连续值具有第 3 行第 1 列作为空白值，如下所示

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, PLATINUM], [85367890000, GOLD], [85362524232, SILVER], [SILVER], [85362522436, DIAMOND]]

当对应的单元格中没有值时，为什么不打印 [null,Silver] 或 [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 为空，插入空白，就像你的情况一样.这里需要将单元格值赋值为 Cell cell = row.getCell(int arg0, MissingCellPolicy arg1): Cell-Row

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();
}

这里每当遇到任何具有空值的单元格时，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.所以你需要创建一个空检查(如代码中所述).在这种情况下，输出将是

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的空白单元格并返回List<List<String>>的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！