java - 在java中读取Excel文件不会读取空白单元格或将其计为空

Question

这个问题已经有答案了:

How to get an Excel Blank Cell Value in Apache POI? (9 个回答)

已关闭 6 年前。

我在 Java 中的 Excel 阅读器上遇到问题。它不会读取空或空白单元格并跳到下一个具有值的单元格。

示例..

header1 | header2 | header3 | header4
row1    |         | row3    | row4
row1    | row2    |         | row4
        | row2    | row3    | 

结果:

header1 | header2 | header3 | header4
row1    | row3    | row4    | 
row1    | row2    | row4    | 
row2    | row3    |         | 

预计发生的情况:

header1 | header2 | header3 | header4
row1    | null    | row3    | row4
row1    | row2    | null    | row4
null    | row2    | row3    | null

null 或 ""(空字符串)

这是我的代码:

我的readXLSFile方法:

private boolean readXLSFile(String batchRunNbr, String filename) throws IOException, ParseException {

        List sheetData = new ArrayList();
        FileInputStream fis = null;
        try {
            if ((filename.endsWith(".xlsx")) || (filename.endsWith(".XLSX"))) {
                log.info("Reading xlsx file...");
                fis = new FileInputStream(filename);
            } else{
                this.errorMessageTxt = this.errorMessageTxt+this.htmlNextLine+
                        "Unable to process the file. Please save the file to the latest Excel *.xlsm or *.xlsx file.";
                return true;
            }


            XSSFWorkbook workbook = new XSSFWorkbook(fis);
            XSSFSheet sheet = workbook.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            int counter = 0;
            while (rows.hasNext()) {
                counter++;
                XSSFRow row = ((XSSFRow) rows.next());
                Iterator<Cell> cells = row.cellIterator();

                List data = new ArrayList();
                while (cells.hasNext()) {
                    Cell cell = (XSSFCell) cells.next();

                    data.add(cell);
                }
                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
            this.errorMessageTxt = this.errorMessageTxt+this.htmlNextLine+
                    e.getMessage()+this.htmlNextLine+e.getCause();
            return true; //There is an error, return true.
        } finally {
            if (fis != null) {
                fis.close();
            }
        }

        processExcelSheet(sheetData, batchRunNbr, filename);

这是我的processExcelSheeet方法:

private void processExcelSheet(List sheetData, String batchRunNbr, String inputFileName) {
        DateFormat formatter;
        formatter = new SimpleDateFormat("dd.MM.yyyy");
        boolean firstTime = true;
        try {
            for (int i = 0; i < sheetData.size(); i++) {
                List list =  (List) sheetData.get(i);
                if (firstTime) {
                    firstTime = false;
                } else {
                    for (int ii = 0; ii < list.size(); ii++) {

                        XSSFCell cell = (XSSFCell) list.get(ii);
                        switchCase(formatter, ii, cell);
                    }

                    insertToStaging(batchRunNbr, inputFileName);//This method inserts the data to the database based on what it reads above.
                }
                //log.info("COMPLETED!");
            }

        } catch (Exception e) {
            log.info("loadStagingTable Error:" + e);
            //this.errorMessageTxt = this.errorMessageTxt+this.htmlNextLine+
            //      "Error Loading to KLTL Data Staging. "+e.getMessage()+","+e.getCause();
            e.printStackTrace();
        }
    }

我怀疑问题出在row.hasNext上？请帮忙..非常感谢。如果您需要更多详细信息，请在下面发表评论。

Answer 1

到目前为止，这是非常酷的代码!

只是一个疯狂的猜测:我敢打赌，当你得到“sheet.rowIterator()”时，你会失去你的空值。这是有道理的，因为“迭代器”经常忽略空值。

那么...如果是这种情况...您如何解决它？

也许这会起作用:

定义“sheet”后尝试执行类似的操作

for(int i = 0; i<sheet.getLastRowNum(); i++){
  XSSFRow row = ((XSSFRow) sheet.getRow(i));
  for(int j = 0; j<row.getLastCellNum(); j++){
    if(row!=null && row.getCell(j) != null ){
      String val = row.getCell(j).getStringCellValue();
      if(val.isEmpty()){
        System.out.println("empty" );
      }
      else{
        System.out.println(val);
      }
    }
    else{
      System.out.println("blank cell");
    }
  }
}

;) 编码愉快!