昨天遇到一个奇怪的问题,我的系统提供一个下载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;
}