java - Apache POI 空白值

Question

我正在使用 Apache POI 将数据从 excel 文件 导入到数据库。(APACHE POI 新手)

其中我允许用户从 Excel 工作表中选择列并将这些列映射到数据库列。映射列后，当我尝试将记录从 Excel 插入到 Database 时:

• 如果映射了其中没有空白值的列，则正确的数据将插入到数据库中
• 如果列中映射有 BLANK 值，则如果 Excel 单元格 具有空白值，则会分配该列 的先前值.

源代码:

FileInputStream file = new FileInputStream(new File("C:/Temp.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(file); //Get the workbook instance for XLS file
HSSFSheet sheet = workbook.getSheetAt(0);   //Get first sheet from the workbook
Iterator<Row> rowIterator = sheet.iterator(); //Iterate through each rows from first sheet
while (rowIterator.hasNext())
{
  HSSFRow hssfRow = (HSSFRow) rowIterator.next();
  Iterator<Cell> iterator = hssfRow.cellIterator();
  int current = 0, next = 1;
  while (iterator.hasNext())
  {
    HSSFCell hssfCell = (HSSFCell) iterator.next();
    current = hssfCell.getColumnIndex();
    for(int i=0;i<arrIndex.length;i++)    //arrayIndex is array of Excel cell Indexes selected by the user
    {
      if(arrIndex[i] == hssfCell.getColumnIndex())
      {
        if(current<next) 
        {
                    //System.out.println("Condition Satisfied");     
        }
        else 
        {
          System.out.println( "pstmt.setString("+next+",null);");
          pstmt.setString(next,null);
          next = next + 1;
        }
        System.out.println( "pstmt.setString("+next+","+((Object)hssfCell).toString()+");");
        pstmt.setString(next,((Object)hssfCell).toString());
        next = next + 1;
      }
    }
  }
  pstmt.addBatch();
  }

我在 SO 上寻找类似的问题，但仍然无法解决问题。所以任何帮助将不胜感激。

提前致谢..

Answer 1

您犯了一个非常常见的错误，过去的许多 StackOverflow 问题都涵盖了该错误

作为Apache POI documentation on cell iterating says

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. (The CellIterator will only return the cells defined in the file, which is largely those with values or stylings, but it depends on Excel).

听起来你正处于这种情况，你需要关心击中每一行/单元格，而不仅仅是捕获所有可用的单元格而不担心间隙

您需要将代码更改为 somewhat like the example in the POI docs :

// Decide which rows to process
int rowStart = Math.min(15, sheet.getFirstRowNum());
int rowEnd = Math.max(1400, sheet.getLastRowNum());

for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
   Row r = sheet.getRow(rowNum);

   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
         // Mark it as blank in the database if needed
      } else {
         // Do something useful with the cell's contents
      }
   }
}