jxl操作excel的一个bug

昨天遇到一个奇怪的问题,我的系统提供一个下载excel和一个将excel直接解析把数据插入到DB的功能.现在出现的问题是:我用自己下载的文件做为上传的文件,居然报错,不是格式问题,错误如下:

ArrayIndexOutOfBoundsException  (id=247),

看到索引越界,肯定先去找数组,但是我根本没用到数组.很奇怪,于是我到google找了下,有人和我出过一样的错误,

http://www.blogjava.net/reeve/archive/2008/12/04/114564.html#244398,

http://www.itpub.net/archiver/tid-908759.html

但是这个兄弟的办法是改jxl.jar,我没权限装反编译器,没办法,只好找其他途径了,改用POI吧.问题还真解决了.:)

顺便谢谢这个兄弟的一点点代码了.

http://atgc.itpub.net/post/22412/227835

----------------------------

我把原来用的JXL代码贴上,把改用POI的也贴出来,

public void upload() {

  clearErrorMessages();
  log.debug("****** file name *****" + uploadFileName.getName());
  String strFileName = "";
  File f1;
  FileOutputStream fos = null;
  try {
   log.debug("fileUpload() action beginning");
   log.debug("File Name:::::::" + uploadFileName.getName());
   log.debug("File Content Type:::::::"
     + uploadFileName.getContentType());
   log.debug("File Size:::::::" + uploadFileName.getSize());
   strFileName = uploadFileName.getName().substring(
     uploadFileName.getName().lastIndexOf("//") + 1);
   log.debug("strFileName Name:::::::" + strFileName);
   String dot = ".";
   int pos = strFileName.lastIndexOf(dot);
   String fileType = strFileName.substring(pos + 1);
   log.debug("fileType::::" + fileType);
   if (!"XLS".equalsIgnoreCase(fileType)) {
    addErrorMessage("Please select a file of XLS Format");
   } else {
    log.debug("Inside else::::::");
    String filePath = CommConstant.FILE_PATH;
    log.debug("save file path:::" + filePath);
    f1 = new File(filePath + strFileName);
    if (f1.exists()) {
     f1.delete();
    }
    byte[] data = uploadFileName.getBytes();
    log.debug("the byte size:::::" + data.length);
    fos = new FileOutputStream(f1);

    fos.write(data);
    log.debug("After Writing data");
    fos.flush();
    fos.close();

    log.debug("File Name ::::" + f1.getName());
    log.debug("File Path ::::" + f1.getPath());
    log.debug("File Path ::::" + f1.length());
    WorkbookSettings wbs = new WorkbookSettings();
    wbs.setInitialFileSize((int) f1.length());
    Workbook workbook = null;
    try {
    workbook = Workbook.getWorkbook(f1, wbs);
    } catch (BiffException e) {
     addErrorMessage("Can not read data from excel, please ensure the file exist and has been closed then upload again.");
     log.error("exception occurred when get data from file." + e);
    }
    Sheet sheet = workbook.getSheet(0);
    log.info("sheet::::" + sheet.getName());
    
    Cell col0 = sheet.getCell(0, 0);
    Cell col1 = sheet.getCell(1, 0);
    Cell col2 = sheet.getCell(2, 0);
    Cell col3 = sheet.getCell(3, 0);
    Cell col4 = sheet.getCell(4, 0);
    Cell col5 = sheet.getCell(5, 0);
    Cell col6 = sheet.getCell(6, 0);
    Cell col7 = sheet.getCell(7, 0);

    log.info(col0.getContents() + "::" + col1.getContents() + "::"
      + col2.getContents() + "::" + col3.getContents() + "::"
      + col4.getContents() + "::" + col5.getContents() + "::"
      + col6.getContents() + "::" + col7.getContents());
    if (("Ar Customer Code").equals(col0.getContents())
      && ("Ar Customer Name").equals(col1.getContents())
      && ("Color Rating").equals(col2.getContents())
      && ("Customer Credit Limit").equals(col3.getContents())
      && ("Credit Terms").equals(col4.getContents())
      && ("Carc Flag").equals(col5.getContents())
      && ("PD Adj").equals(col6.getContents())
      && ("Account Type").equals(col7.getContents())) {
     log.debug("upload Action");
     CarcCustomerBean custBean = new CarcCustomerBean();
     bizFac.getUserService().truncateCustTB();
     for (int i = 1; i < sheet.getRows(); i++) {
      Cell cellCustNum = sheet.getCell(0, i);
      Cell cellCustName = sheet.getCell(1, i);
      Cell cellCustCol = sheet.getCell(2, i);
      Cell cellCustCL = sheet.getCell(3, i);
      Cell cellCustCT = sheet.getCell(4, i);
      Cell cellCustFlag = sheet.getCell(5, i);
      Cell cellCustPA = sheet.getCell(6, i);
      Cell cellCustAT = sheet.getCell(7, i);

      if (!("").equals(cellCustNum) && cellCustNum != null) {
       custBean.setCustNumber(cellCustNum.getContents());
       custBean.setCustName(cellCustName.getContents());
       custBean.setCustColor(cellCustCol.getContents());
       custBean.setCustCreditLimit(cellCustCL
         .getContents());
       custBean.setCustCreditTerms(cellCustCT
         .getContents());
       custBean.setCustFlag(cellCustFlag.getContents());
       custBean.setCustPdAdj(cellCustPA.getContents());
       custBean.setCustAccType(cellCustAT.getContents());
       bizFac.getUserService().saveCust(custBean);
      }
     }
     clearErrorMessages();
     addErrorMessage("All records updated successfully!");
    } else {
     clearErrorMessages();
     addErrorMessage("Please use the correct template, or download template from site.");
    }

   }
  } catch (IOException ex) {
   addErrorMessage("can not transfer data. please try later.");
   log.error("IOException occurred." + ex);
  } catch (CARCException ex) {
   addErrorMessage("save data to db failed. maybe the table is using, please try later.");
  } finally {
   try {
    if (null != fos) {
     fos.close();
     fos = null;
    }
   } catch (IOException IOEx) {
    addErrorMessage("Can not be closed file Stream.");
    log.error("File can not be closed. Exception :" + IOEx);
   }
  }
 }

---------------

POI代码:

public void upload() {

  clearErrorMessages();
  log.debug("****** file name *****" + uploadFileName.getName());
  String strFileName = "";
  File f1;
  FileOutputStream fos = null;
  
  ArrayList arrlst = null;
  ArrayList contLst = new ArrayList();
  boolean saveFlag = false;
  CarcCustomerBean custBean = new CarcCustomerBean();
  try {
   
   strFileName = uploadFileName.getName().substring(
     uploadFileName.getName().lastIndexOf("//") + 1);
   log.info("strFileName Name:::::::" + strFileName);
   String dot = ".";
   int pos = strFileName.lastIndexOf(dot);
   String fileType = strFileName.substring(pos + 1);
   log.info("fileType::::" + fileType);
   if (!"XLS".equalsIgnoreCase(fileType)) {
    addErrorMessage("Please select a file of XLS Format");
   } else {
    String filePath = CommConstant.FILE_PATH;
    log.info("save file path:::" + filePath);
    f1 = new File(filePath + strFileName);
    if (f1.exists()) {
     f1.delete();
    }
    byte[] data = uploadFileName.getBytes();
    log.info("the byte size:::::" + data.length);
    fos = new FileOutputStream(f1);

    fos.write(data);
    log.info("After Writing data");
    fos.flush();
    fos.close();

    try{
     HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(f1));
     HSSFSheet sheet = workbook.getSheetAt(0);
     int rows = sheet.getPhysicalNumberOfRows();
     System.out.println(rows+" row(s) in Excel");
     
     bizFac.getUserService().truncateCustTB();
     
     for (int r = 0; r < rows; r++)
     {
      HSSFRow row = sheet.getRow(r);
      if (row != null)
      {
       int cells = row.getPhysicalNumberOfCells();
       if(cells != 8){
        clearErrorMessages();
        addErrorMessage("Please use the correct template, or download template from site.");
       }
       String value = null;
       double dbValue = 0;
       int integerValue = 0;
       log.debug(cells+" cell(s) in Excel");
       arrlst = new ArrayList();
       for (short c = 0; c < cells; c++)
       {
        HSSFCell cell = row.getCell(c);
        switch (cell.getCellType())
        {
        case HSSFCell.CELL_TYPE_NUMERIC:
         dbValue = cell.getNumericCellValue();
         integerValue = (int)dbValue;
         arrlst.add(""+integerValue);
        break;
        case HSSFCell.CELL_TYPE_STRING:
         value = cell.getStringCellValue();
         log.debug("strValue::::::::"+value);
         arrlst.add(value);
        break;
        
        }
       }
//       log.debug("ArrayList :::"+arrlst);
       contLst.add(arrlst);
      }
//      log.debug("contLst :::"+contLst);
     }
    } catch (FileNotFoundException e) {
     e.printStackTrace();
    } catch (IOException e) {
     e.printStackTrace();
    }
    saveFlag = validateDuplicate(contLst);
    log.debug("Flag:::"+saveFlag);
     
     if(saveFlag){
      for(int k=0; k<contLst.size();k++){    
       log.debug("for loop");
       ArrayList arrCust = (ArrayList)contLst.get(k);
       custBean.setCustNumber(arrCust.get(0).toString());
       custBean.setCustName(arrCust.get(1).toString());
       custBean.setCustColor(arrCust.get(2).toString());
       custBean.setCustCreditLimit(arrCust.get(3).toString());
       custBean.setCustCreditTerms(arrCust.get(4).toString());
       custBean.setCustFlag(arrCust.get(5).toString());
       custBean.setCustPdAdj(arrCust.get(6).toString());
       custBean.setCustAccType(arrCust.get(7).toString());
       try{ 
        bizFac.getUserService().saveCust(custBean);
       }catch(CARCException sqlex){
        addErrorMessage("can not insert data to DB, please try later.");
        log.error("save data to db for Carc Cust."+sqlex);
       }
       
      }
      clearErrorMessages();
      addErrorMessage("All records updated successfully!");
     }
   }
  } catch (IOException ex) {
   addErrorMessage("can not transfer data. please try later.");
   log.error("IOException occurred." + ex);
  } catch (CARCException ex) {
   try {
    bizFac.getUserService().truncateCustTB(); // add by Ming on Dec 18, 2008.
   } catch (CARCException e) {
     addErrorMessage("Can't truncate stage table. please confirm whether it is using.");
   }
  } finally {
   try {
    if (null != fos) {
     fos.close();
     fos = null;
    }
   } catch (IOException IOEx) {
    addErrorMessage("Can not be closed file Stream.");
    log.error("File can not be closed. Exception :" + IOEx);
   }
  }
   
 }

 

---------------

方法: 是判断EXCEL第一列是否存在重复的值

public boolean validateDuplicate(ArrayList arrlst) throws CARCException {
  log.debug("in the validate method....");
  boolean blFlag = false;
  String strValue = null;
  ArrayList arrLegalData = new ArrayList();
  ArrayList arrBadCustNum = new ArrayList();
  for (int i = 0; i < arrlst.size(); i++) {
   ArrayList arrC = (ArrayList) arrlst.get(i);
   if(!("").equals(arrC.get(0)) || arrC.get(0) != null)
   {
    strValue = (String)arrC.get(0);
    if(!arrLegalData.contains(arrC.get(0))){
     log.debug("there are not duplicate data.");
     arrLegalData.add(strValue);
     blFlag = true;
    }else{
     log.debug("There are duplicate data."+strValue);
     arrBadCustNum.add(strValue);
     blFlag = false;
    }
   }
  }
  if(arrBadCustNum.size()>0){
   log.debug("bad data ...."+arrBadCustNum);
   addErrorMessage("Duplicate Customer Number:"+arrBadCustNum);
  }
  return blFlag;
 }