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

问题描述

我使用的Apache POI 从数据导入excel文件到数据库。（新手到Apache POI）

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

在这，我允许用户从Excel工作表中的列和映射这些列到数据库列。映射列之后，当我尝试从 Excel中记录插入到数据库则：

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:

• 如果用列在其中没有空白值映射然后正确的数据被插入到数据库


• 如果列与映射在其中BLANK 值，那么如果一个 Excel单元格中有空白值，那么$ P那$ pvious值列分配。

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

来源$ C ​​$ C：

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

我看就这样类似的问题，但仍然没有能够解决这个问题。所以任何帮助将AP preciated。

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

您将要改变你code看有点像例子在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屋！