Excel POI解析

Excel有xlsx格式和xls格式两种，以下就是本人在开发中使用POI解析方法，有很多不足，还请指出，谢谢！

// 2007版本的Excel

private List<PrpSagreeDetailHis> trancasitionsAgreenmentExcel2007(File excelFile)
throws Exception{
int index = 0;
//List<PrpSagreeDetail> prpSagreeDetailList
String queryRiskName = null;
String usenaturecode = null;
String carTypeCode = null;
DecimalFormat df = new DecimalFormat("#.00");
List<PrpSagreeDetailHis> prpSagreeDetailHisList = new ArrayList<PrpSagreeDetailHis>();
XSSFWorkbook xwk = new XSSFWorkbook(new FileInputStream(excelFile));
Sheet sheet = xwk.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
PrpSagreeDetailHis prpSagreeDetailHis = new PrpSagreeDetailHis();
Row row = rows.next();
if (row.getRowNum() > 0) {
index++;
//设置单元格格式
if(row.getCell(0) !=null && !"".equals(row.getCell(0))){
row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
}
if(row.getCell(1) !=null && !"".equals(row.getCell(1))){
row.getCell(1).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
if(row.getCell(2) !=null && !"".equals(row.getCell(2))){
row.getCell(2).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
//设置单元格格式
if(row.getCell(3) !=null && !"".equals(row.getCell(3))){
row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
if(row.getCell(4) !=null && !"".equals(row.getCell(4))){
row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
if(row.getCell(5) !=null && !"".equals(row.getCell(5))){
row.getCell(5).setCellType(HSSFCell.CELL_TYPE_STRING);
}
if(row.getCell(6) !=null && !"".equals(row.getCell(6))){
row.getCell(6).setCellType(HSSFCell.CELL_TYPE_STRING);
}


//判断险种是否符合要求
if (row.getCell(0)==null || "".equals(row.getCell(0))) {
//使用性质代码不能为空
prpSagreeDetailHis.setRiskCode("") ;
}else if (row.getCell(0)!=null && !("".equals(row.getCell(0)))) {
//险种代码不能为空
String queryRiskCode = row.getCell(0).getStringCellValue();
//根据RiskCode查询RiskName
queryRiskName = queryPrpSagreeDetailHisRiskName(queryRiskCode);

if("n".equals(queryRiskName )){
prpSagreeDetailHis.setRiskCode( row.getCell(0).getStringCellValue()+"不存在！");
}else{
prpSagreeDetailHis.setRiskName(queryRiskName);
prpSagreeDetailHis.setRiskCode( row.getCell(0).getStringCellValue());
} 
}
if (row.getCell(1)!=null && !("".equals(row.getCell(1)))) {
Double f = row.getCell(1).getNumericCellValue();
prpSagreeDetailHis.setBottomCommission(Double.valueOf(df.format(f)));
}else{
prpSagreeDetailHis.setBottomCommission(0.00);
}

if (row.getCell(2)!=null && !("".equals(row.getCell(2)))) {
Double f = row.getCell(2).getNumericCellValue();
prpSagreeDetailHis.setTopCommission(Double.valueOf(df.format(f)));
}else{
prpSagreeDetailHis.setTopCommission(0.00);
}

if (row.getCell(3)!=null && !("".equals(row.getCell(3)))) {
Double f = row.getCell(3).getNumericCellValue();
prpSagreeDetailHis.setDefaultCommission(Double.valueOf(df.format(f)));
}else{
prpSagreeDetailHis.setDefaultCommission(0.00);
}

if (row.getCell(4)!=null && !("".equals(row.getCell(4)))) {
Double f = row.getCell(4).getNumericCellValue();
prpSagreeDetailHis.setIntervalCommission(Double.valueOf(df.format(f)));
}else{
prpSagreeDetailHis.setIntervalCommission(0.00);
}

if (row.getCell(5)==null || "".equals(row.getCell(5))) {
//使用性质代码不能为空
prpSagreeDetailHis.setFactor1("") ;
}else if(!"n".equals(queryRiskName)){
//usenaturename useNatureCode
String riskCode =row.getCell(0).getStringCellValue();
String queryUseNatureName = row.getCell(5).getStringCellValue().trim();
usenaturecode = queryUseNatureCodeBy(riskCode,queryUseNatureName);
if ("n".equals(usenaturecode)) {
}else{
prpSagreeDetailHis.setFactor1(usenaturecode) ;
}
}

if (row.getCell(6)==null || "".equals(row.getCell(6))) {
//车辆种类代码不能为空
prpSagreeDetailHis.setFactor2(row.getCell(6) ==null ? "" : row.getCell(6).getStringCellValue());
}else{

if (!"n".equals(usenaturecode)) {
//carTypeName
String useNatureName = row.getCell(5).getStringCellValue().trim();
String querycarTypeName = row.getCell(6).getStringCellValue().trim();
carTypeCode = queryCarTypeCodeBy(useNatureName,querycarTypeName);
if ("n".equals(carTypeCode)) {

}else{
prpSagreeDetailHis.setFactor2(carTypeCode);
}
}
}

prpSagreeDetailHisList.add(prpSagreeDetailHis);
}
}
return prpSagreeDetailHisList;
}





private String getCellValue(HSSFCell cell) {
String cellValue = "";
if (cell == null) {
cellValue = "";
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
cellValue = cell.getStringCellValue();
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
cellValue = String.valueOf(cell.getNumericCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
cellValue = String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
cellValue = String.valueOf(cell.getNumericCellValue());
} else {
cellValue = cell.toString();
}
return cellValue;
}


// 2003版本的Excel
private List<PrpSagreeDetailHis> trancasitionsAgreenmentExcel2003(
File excelFile) throws Exception {
String queryRiskName = null;
//String usenaturename = null;
String carTypeCode = null;
String usenaturecode = null;
DecimalFormat df = new DecimalFormat("#.00");
List<PrpSagreeDetailHis> prpSagreeDetailHisList = new ArrayList<PrpSagreeDetailHis>();
HSSFWorkbook hwk = new HSSFWorkbook(new FileInputStream(excelFile));
HSSFSheet sheet = hwk.getSheetAt(0);
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
PrpSagreeDetailHis prpSagreeDetailHis = new PrpSagreeDetailHis();
HSSFRow row = (HSSFRow) rows.next();
if (row.getRowNum() > 0) {
// 设置单元格格式
if (row.getCell(0) != null && !"".equals(row.getCell(0))) {
row.getCell(0).setCellType(HSSFCell.CELL_TYPE_STRING);
}
if (row.getCell(1) != null && !"".equals(row.getCell(1))) {
row.getCell(1).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
if (row.getCell(2) != null && !"".equals(row.getCell(2))) {
row.getCell(2).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
// 设置单元格格式
if (row.getCell(3) != null && !"".equals(row.getCell(3))) {
row.getCell(3).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
if (row.getCell(4) != null && !"".equals(row.getCell(4))) {
row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
}
if (row.getCell(5) != null && !"".equals(row.getCell(5))) {
row.getCell(5).setCellType(HSSFCell.CELL_TYPE_STRING);
}
if (row.getCell(6) != null && !"".equals(row.getCell(6))) {
row.getCell(6).setCellType(HSSFCell.CELL_TYPE_STRING);
}
if(!(getCellValue(row.getCell(0)) ==null) && !("".equals(getCellValue(row.getCell(0))))){
//险种代码不能为空
String queryRiskCode = getCellValue(row.getCell(0));
//根据RiskCode查询RiskName
queryRiskName = queryPrpSagreeDetailHisRiskName(queryRiskCode);
if("n".equals(queryRiskName )){

prpSagreeDetailHis.setRiskCode( getCellValue(row.getCell(0))+"不存在！");
}else{
prpSagreeDetailHis.setRiskName(queryRiskName);
prpSagreeDetailHis.setRiskCode( getCellValue(row.getCell(0)));
}
}else{

prpSagreeDetailHis.setRiskCode(getCellValue(row.getCell(0)));
}

if("".equals(getCellValue(row.getCell(1))) || row.getCell(1) ==null){

}else{
Double f = Double.parseDouble(getCellValue(row.getCell(1)));//Double.valueOf(df.format(f)
prpSagreeDetailHis.setBottomCommission(Double.valueOf(df.format(f)));
/*prpSagreeDetailHis.setBottomCommission(Double
.parseDouble(getCellValue(row.getCell(1))));*/
}

if("".equals(getCellValue(row.getCell(2))) || row.getCell(2) ==null){

}else{
Double f = Double.parseDouble(getCellValue(row.getCell(2)));
prpSagreeDetailHis.setTopCommission(Double.valueOf(df.format(f)));
}
if(!"".equals(getCellValue(row.getCell(3))) && row.getCell(3) !=null){
Double f = Double.parseDouble(getCellValue(row.getCell(3)));

prpSagreeDetailHis.setDefaultCommission(Double.valueOf(df.format(f)));
}

if("".equals(getCellValue(row.getCell(4))) || row.getCell(4) ==null){

}else{
Double f = Double.parseDouble(getCellValue(row.getCell(4)));
prpSagreeDetailHis.setIntervalCommission(Double.valueOf(df.format(f)));
}


if(!(getCellValue(row.getCell(5)) ==null) && !("".equals(getCellValue(row.getCell(5)))) && !("n".equals(queryRiskName ))){
String riskCode =row.getCell(0).getStringCellValue();
String queryUseNatureName = row.getCell(5).getStringCellValue().trim();
usenaturecode = queryUseNatureCodeBy(riskCode,queryUseNatureName);
if ("n".equals(usenaturecode)) {
}else{
prpSagreeDetailHis.setFactor1(usenaturecode);

}
}else{
prpSagreeDetailHis.setFactor1("");
}

if(!(getCellValue(row.getCell(6)) ==null) && !("".equals(getCellValue(row.getCell(6)))) 
&&(!("n".equals(usenaturecode)))){

//carTypeName 车辆类型名称
String useNatureName = row.getCell(5).getStringCellValue().trim();
String querycarTypeName = row.getCell(6).getStringCellValue().trim();
carTypeCode = queryCarTypeCodeBy(useNatureName,querycarTypeName);
if ("n".equals(carTypeCode)) {
}else{
prpSagreeDetailHis.setFactor2(carTypeCode);
}

}else{
prpSagreeDetailHis.setFactor2("");
}

prpSagreeDetailHisList.add(prpSagreeDetailHis);
}
}
return prpSagreeDetailHisList;
}


// Excel文件分为2007和2003两个版本
public List<PrpSagreeDetailHis> agreementImportExcel(File excelFile,
HttpServletRequest request,List<PrpSagreeDetailHis> prpSagreeDetailHisList) throws IOException {

List<PrpSagreeDetailHis> prpSagreeDetailHisList1 = new ArrayList<PrpSagreeDetailHis>();


try {
prpSagreeDetailHisList1 = trancasitionsAgreenmentExcel2007(
excelFile);
} catch (Exception e) {
try {
prpSagreeDetailHisList1 = trancasitionsAgreenmentExcel2003(
excelFile);
} catch (Exception e1) {
throw new BusinessException("文件格式错误!" + e1, false);
}
}

}
return prpSagreeDetailHisList2;


}