Apache POI 空白值 [英] Apache POI blank values

问题描述

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

I am using Apache POI to import data from excel file to database.(newbie to APACHE POI)

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

In which I am allowing user to select columns from excel sheet and Map those columns to the Database columns. After mapping the columns, when I try to insert the records from Excel to Database then:

• 如果映射了 NO 空白 值的列，则将正确的数据插入到数据库中
• 如果列在其中映射了 BLANK 值，则如果 Excel Cell 具有空白值，则分配该 column 的先前值.

• If Columns with NO blank values in them are Mapped then Proper data is inserted into the database
• If columns are Mapped with BLANK values in them, then if a Excel Cell has blank value then previous value of that column is assigned.

源代码:

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 上寻找类似的问题，但仍然无法解决问题.. 所以任何帮助将不胜感激.

I have look for similar questions on SO, but still not able to solve the issue.. So any help will be appreciated.

提前致谢..

推荐答案

你犯了一个很常见的错误，在过去的很多 StackOverflow 问题中都有涉及

You've made a very common mistake, which has been covered in rather a lot of past StackOverflow questions

正如 关于单元格迭代的 Apache POI 文档所说

在某些情况下，在迭代时，您需要完全控制缺失或空白单元格的处理方式，并且您需要确保访问每个单元格，而不仅仅是文件中定义的单元格.(CellIterator 将只返回文件中定义的单元格，这些单元格主要是具有值或样式的单元格，但它取决于 Excel).

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).

听起来您处于那种情况，您需要关心点击每一行/单元格，而不仅仅是抓取所有可用的单元格而不用担心间隙

It sounds like you are in that situation, where you need to care about hitting every row/cell, and not just grabbing all the available cells without worrying about the gaps

您需要将代码更改为 有点像 POI 文档中的示例:

You'll want to change you code to look 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
      }
   }
}

这篇关于Apache POI 空白值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！