在Excel-Java poi中编写错误

Question

我想这里的问题在for循环，如果有人能帮助它，它将非常感谢。谢了。

这是我的代码：-...

    @CheckMethodAuthority("PFV2300&limt_print")
    public static void importExcel(Upload upload_data) throws IOException, 
    NoSuchFieldException, SecurityException, IllegalArgumentException, 
    IllegalAccessException, 

ParseException {    

    String fileName = upload_data.getFileName();
    logger.info("H1:" + fileName);

    final int expectedSheetIndex = 0;  // 
   Sheet sheet = ExcelUtil.openWorkSheet(new ByteArrayInputStream(upload_data.asBytes()), fileName, expectedSheetIndex);

    SYS4000 account = (SYS4000)renderArgs.get("account");

    StringBuffer fileUploadErrorMsg = new StringBuffer();

    final int expectedTitleRowNum = 1;  // 
    List<POS20083> pos20083List = readAllCellsOfSheet(sheet, expectedTitleRowNum, account.user_no, fileUploadErrorMsg);

    int addItem =0;
    int upItem =0;
    int failItem =0;

    File filePath = new File("D:\\play framework workspace\\Coupon_Platform\\public\\sample\\excel\\PFV2300-sample.xlsx");
    InputStream file = new FileInputStream(filePath);
    XSSFWorkbook WB = new XSSFWorkbook(file);

    sheet = WB.getSheetAt(0);

    for (POS20083 POS20083: pos20083List)
    {

        for (int i=0; i<=pos20083List.size(); i++)
        { 
           Row row = sheet.getRow(i);
           Cell cell_store_no=  (sheet.getRow(i).getCell(0));
           Cell cell_control_qty =  (sheet.getRow(i).getCell(1));
           Cell cell_error_msg = sheet.getRow(i).getCell(2);
           sheet.autoSizeColumn(2);

           for (int j=0; j<=sheet.getFirstRowNum(); j++)
              {
                  if (row == null) 
                  {
                      row = sheet.createRow(i);
                      continue;
                  }
                  if (cell_error_msg == null) 
                  {
                      cell_error_msg = sheet.getRow(i).createCell(2);
                      continue;
                  }
                  if (row.getRowNum()==0)
                  {
                      continue;
                  }

                  POS20083 pos20083 = POS20083.find("merchant_no= ? and store_no= ?",POS20083.merchant_no, POS20083.store_no).first();

                 String sql =  insert into pos20083 (pos20081_id, merchant_no, store_no, control_qty, id)select 60, ?1, ?2, ?3,  pos20083_seq.nextval from dual;


                 String sql2 = " Select count(store_no) from twc_store"
                             + " Where exists (select store_no"
                             + " from pos20083"
                             + " where twc_store.store_no = ?4)";

                 String sql3 = " Select count(store_no) from pos20083"
                         + " Where exists (select store_no"
                         + " from twc_store"
                         + " where pos20083.store_no = ?5)";

                           EntityManager entityManager = JPA.em();
                           EntityTransaction transaction = JPA.em().getTransaction();
                           Query query = entityManager.createNativeQuery(sql);
                           Query query2 = entityManager.createNativeQuery(sql2);
                           Query query3 = entityManager.createNativeQuery(sql3);

                           query2.setParameter(4, POS20083.store_no);
                           query3.setParameter(5, POS20083.store_no);

                          if (!transaction.isActive())
                          {
                              transaction.begin();
                          }
                          int insertCount2 = query2.executeUpdate();
                          int insertCount3 = query3.executeUpdate();

                          int storeCounts = ObjectUtil.getInteger(query2.getSingleResult());
                          int storeCounts_pos20083 = ObjectUtil.getInteger(query3.getSingleResult()); 

                            if (storeCounts == 0 && ObjectUtil.getInteger(cell_control_qty.getNumericCellValue()) >0) 
                             {

                              cell_error_msg.setCellValue("Store Number ID doesn't exists in [twc_store]");

                              sheet.autoSizeColumn(2);
                             }  
                            if (storeCounts == 0 && ObjectUtil.getInteger(cell_control_qty.getNumericCellValue()) <=0) 
                            {

                             cell_error_msg.setCellValue("Store Number ID doesn't exists in [twc_store] and and control Quantity coudn't be less or equal ZERO");

                             sheet.autoSizeColumn(2);
                            }

                             if (storeCounts == 1 && storeCounts_pos20083 ==0 && ObjectUtil.getInteger(cell_control_qty.getNumericCellValue()) > 0) 
                             {
                              query.setParameter(1, POS20083.merchant_no); 
                              query.setParameter(2, POS20083.store_no); 
                              query.setParameter(3, POS20083.control_qty);

                              int insertCount = query.executeUpdate();



                              addItem++;
                              cell_error_msg.setCellValue(" ");
                              sheet.autoSizeColumn(2);

                             }

                             if (transaction.isActive()) 
                             {
                                 transaction.commit();
                             }

                          file.close();

           if (storeCounts == 1 && storeCounts_pos20083 ==0 && ObjectUtil.getInteger(cell_control_qty.getNumericCellValue()) <=0) 
         {
              cell_error_msg.setCellValue("control Quantity coudnot be less or equal ZERO");
              sheet.autoSizeColumn(2);

            //failItem++;
            //file.close();
         }
           if (storeCounts == 1 && storeCounts_pos20083 ==1 && ObjectUtil.getInteger(cell_control_qty.getNumericCellValue()) <=0) 
           {
                cell_error_msg.setCellValue("Store Number ID already exists and control Quantity coudn't be less or equal ZERO");
                sheet.autoSizeColumn(2);
                          }

           if (storeCounts == 1 && storeCounts_pos20083 ==1 && ObjectUtil.getInteger(cell_control_qty.getNumericCellValue()) > 0) 
         { 
              cell_error_msg.setCellValue("Store Number ID already exists");
              sheet.autoSizeColumn(2);

         }

       }

    }  
       failItem++; 
  }
           file.close();
           FileOutputStream fileout = new FileOutputStream(filePath);
           WB.write(fileout);
           fileout.flush();
           fileout.close();

Answer 1

我已经发现了漏洞并修复了它

有三个循环

1-for(POS20083 POS20083:pos20083List)2-for（int i=0；i<=pos20083List.size()；i++)3-for（int j=0；j<=sheet.getFirstrownum（）；j++)

因此，如果我们删除第一个，这在这里是不有用的，因为我们总是从excel表中获得数据，所以它会工作得很好······

第三个也一样，它在我的任务中也不是很有用...

所以我只需要这个循环

我们可以用这行替换pos20083list,它返回行的实数

int rows=sheet.getPhisicalNumberOfRows()-1

非常感谢Layne Bernardo的努力