SpringMVC上传和解析Excel方法

<!-- 上传文件拦截，设置最大上传文件大小 10M=10*1024*1024(B)=10485760 bytes --> <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">  <property name="maxUploadSize" value="10485760" /> </bean>

<div>  <form id="sourcefile" name="sourcefile" action="" method="post" enctype="multipart/form-data">  <input type="button" value="添 加" onClick="addAirLine()" />  <input style="margin-left: 20px;" id="source_file" name="sourceFile" type="file" value="选择文件" />  <input style="margin-left: 20px;" data-loading-text="请勿重复提交" type="submit" value="上 传" onClick="upPolicy()">  <input style="margin-left: 20px;" type="submit" value="下载模板" onClick="return downloadTemplate();">  </form> </div>

function upPolicy() {  document.sourcefile.action = "/login/policy/uploadCSV";  var submitUrl = document.getElementById("sourcefile").attributes["action"].value;  $.ajax({  type: "POST",  url: submitUrl,  data: $('#sourcefile').serialize(),  dataType: "json",  success: function (result) {   var json = JSON.parse(result);   if (json.flag == "0" || json.flag == "1") {   alert(tableJson.success);   return;   }  }  }) } 

@RequestMapping(value = "/uploadCSV" ,method = RequestMethod.POST) @ResponseBody public String uploadCSV(@RequestParam("sourceFile") MultipartFile sourceFile, HttpServletRequest request,HttpServletResponse response) throws IOException{  //判断文件是否为空  if (sourceFile==null) return null;  //获取文件名  String name=sourceFile.getOriginalFilename();  //进一步判断文件是否为空（即判断其大小是否为0或其名称是否为null）  long size =sourceFile.getSize();  if (name==null ||("").equals(name) && size==0) return null;  //批量导入。参数：文件名，文件。  boolean b = batchImport(name,sourceFile);  JSONObject jsonObject=new JSONObject();  if(b){   jsonObject.put("flag",0);   jsonObject.put("success","批量导入EXCEL成功！");  }else{   jsonObject.put("flag",1);   jsonObject.put("success","批量导入EXCEL失败！");  }  return jsonObject.toString(); }

public boolean batchImport(String name,MultipartFile file){  boolean b = false;  //创建处理EXCEL  ExcelUtils readExcel=new ExcelUtils();  //解析excel，获取客户信息集合。  List<OTAPolicyModel> cpolicyList = readExcel.getExcelInfo(name ,file);  if(cpolicyList != null){   b = true;  }  //迭代添加信息（注：实际上这里也可以直接将cpolicyList集合作为参数，    在Mybatis的相应映射文件中使用foreach标签进行批量添加。）  for(OTAPolicyModel customer:cpolicyList){   policyDao.insertOTAPolicy(customer);  }  return b; }

package com.flight.inter.otaadapter.commons.util;import com.flight.inter.otaadapter.model.OTAPolicyModel;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import org.springframework.web.multipart.commons.CommonsMultipartFile;import java.io.*;import java.math.BigDecimal;import java.util.ArrayList;import java.util.Date;import java.util.List;/** * Created by ling.zhang on 2016/12/29. */public class ExcelUtils { //总行数 private int totalRows = 0; //总条数 private int totalCells = 0; //错误信息接收器 private String errorMsg; //构造方法 public ExcelUtils(){} //获取总行数 public int getTotalRows() { return totalRows;} //获取总列数 public int getTotalCells() { return totalCells;} //获取错误信息 public String getErrorInfo() { return errorMsg; } /**  * 验证EXCEL文件  * @param filePath  * @return  */ public boolean validateExcel(String filePath){  if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){   errorMsg = "文件名不是excel格式";   return false;  }  return true; } /**  * 读EXCEL文件，获取客户信息集合  * @param  * @return  */ public List<OTAPolicyModel> getExcelInfo(String fileName, MultipartFile Mfile){  //把spring文件上传的MultipartFile转换成CommonsMultipartFile类型  CommonsMultipartFile cf= (CommonsMultipartFile)Mfile; //获取本地存储路径  File file = new File("D://fileupload");  //创建一个目录 （它的路径名由当前 File 对象指定，包括任一必须的父路径。）  if (!file.exists()) file.mkdirs();  //新建一个文件  File file1 = new File("D://fileupload" + new Date().getTime() + ".xlsx");  //将上传的文件写入新建的文件中  try {   cf.getFileItem().write(file1);  } catch (Exception e) {   e.printStackTrace();  }  //初始化客户信息的集合  List<OTAPolicyModel> customerList=new ArrayList<OTAPolicyModel>();  //初始化输入流  InputStream is = null;  try{   //验证文件名是否合格   if(!validateExcel(fileName)){    return null;   }   //根据文件名判断文件是2003版本还是2007版本   boolean isExcel2003 = true;   if(WDWUtil.isExcel2007(fileName)){    isExcel2003 = false;   }   //根据新建的文件实例化输入流   is = new FileInputStream(file1);   //根据excel里面的内容读取客户信息   customerList = getExcelInfo(is, isExcel2003);   is.close();  }catch(Exception e){   e.printStackTrace();  } finally{   if(is !=null)   {    try{     is.close();    }catch(IOException e){     is = null;     e.printStackTrace();    }   }  }  return customerList; } /**  * 根据excel里面的内容读取客户信息  * @param is 输入流  * @param isExcel2003 excel是2003还是2007版本  * @return  * @throws IOException  */ public List<OTAPolicyModel> getExcelInfo(InputStream is,boolean isExcel2003){  List<OTAPolicyModel> customerList=null;  try{   /** 根据版本选择创建Workbook的方式 */   Workbook wb = null;   //当excel是2003时   if(isExcel2003){    wb = new HSSFWorkbook(is);   }   else{//当excel是2007时    wb = new XSSFWorkbook(is);   }   //读取Excel里面客户的信息   customerList=readExcelValue(wb);  }  catch (IOException e) {   e.printStackTrace();  }  return customerList; } /**  * 读取Excel里面客户的信息  * @param wb  * @return  */ private List<OTAPolicyModel> readExcelValue(Workbook wb){  //得到第一个shell  Sheet sheet=wb.getSheetAt(0);  //得到Excel的行数  this.totalRows=sheet.getPhysicalNumberOfRows();  //得到Excel的列数(前提是有行数)  if(totalRows>=1 && sheet.getRow(0) != null){   this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();  }  List<OTAPolicyModel> oTAPolicyModelList=new ArrayList<OTAPolicyModel>();  OTAPolicyModel oTAPolicyModel;  //循环Excel行数,从第二行开始。标题不入库  for(int r=1;r<totalRows;r++){   Row row = sheet.getRow(r);   if (row == null) continue;   oTAPolicyModel = new OTAPolicyModel();   try {    Thread.currentThread().sleep(1);   }catch (InterruptedException e){    e.printStackTrace();   }   oTAPolicyModel.setPolicyid(System.currentTimeMillis());   //循环Excel的列   for(int c = 0; c <this.totalCells; c++){    Cell cell = row.getCell(c);    if (null != cell){     if(c==0){      oTAPolicyModel.setSource(cell.getStringCellValue());//供应商     }else if(c==1){      oTAPolicyModel.setVendee(cell.getStringCellValue());//输出渠道     }else if(c==2){      int triptype=0;      if (cell.getStringCellValue()=="全部"){       triptype=0;      }else if (cell.getStringCellValue().equals("单程")){       triptype=10;      }else if (cell.getStringCellValue().equals("往返")){       triptype=20;      }else if (cell.getStringCellValue().equals("单程直飞")){       triptype=11;      }else if (cell.getStringCellValue().equals("单程中转")){       triptype=12;      }else if (cell.getStringCellValue().equals("往返直飞")){       triptype=21;      }else if (cell.getStringCellValue().equals("往返中转")){       triptype=22;      }      oTAPolicyModel.setTriptype(triptype);//行程类型     }else if(c==3){      oTAPolicyModel.setCarrier(cell.getStringCellValue());//航司代码     }else if(c==4){      oTAPolicyModel.setDepcity(cell.getStringCellValue());//起飞城市     }else if(c==5){      oTAPolicyModel.setArrcity(cell.getStringCellValue());//降落城市     }else if(c==6){      oTAPolicyModel.setSalebegindatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//销售开始日期     }else if(c==7){      oTAPolicyModel.setSaleenddatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//销售结束日期     }else if(c==8){      oTAPolicyModel.setTravelbegindatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//旅行开始日期     }else if(c==9){      oTAPolicyModel.setTravelenddatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//旅行结束日期     }else if(c==10){      int cabintype=9;      if (cell.getStringCellValue().equals("全部")){       cabintype=9;      }else if (cell.getStringCellValue().equals("经济舱")){       cabintype=1;      }else if (cell.getStringCellValue().equals("商务")){       cabintype=2;      }else if (cell.getStringCellValue().equals("头等")){       cabintype=3;      }      oTAPolicyModel.setCabintype(cabintype);//舱位等级     }else if(c==11){      oTAPolicyModel.setFdtype(cell.getStringCellValue().equals("按价格区间")?1:2);//返点类型     }else if(c==12){      oTAPolicyModel.setCabin(cell.getStringCellValue());//舱位     }else if(c==13){      oTAPolicyModel.setPricebegin(cell.getNumericCellValue());//最低价格     }else if(c==14){      oTAPolicyModel.setPriceend(cell.getNumericCellValue());//最高价格     }else if(c==15){      oTAPolicyModel.setLmoney(cell.getNumericCellValue());//留钱     }else if(c==16){      oTAPolicyModel.setFpercent(cell.getNumericCellValue());//全价返点     }else if(c==17){      oTAPolicyModel.setFtpercent(cell.getNumericCellValue());//票面返点     }else if(c==18){      int carrierlimit=2;      if (cell.getStringCellValue().equals("是")){       carrierlimit=1;      }else if (cell.getStringCellValue().equals("否")){       carrierlimit=0;      }else if (cell.getStringCellValue().equals("无")){       carrierlimit=2;      }      oTAPolicyModel.setCarrierlimit(carrierlimit);//开票航司限制     }else if(c==19){      int transport=2;      if (cell.getStringCellValue().equals("是")){       transport=1;      }else if (cell.getStringCellValue().equals("否")){       transport=0;      }else if (cell.getStringCellValue().equals("无限制")){       transport=2;      }      oTAPolicyModel.setTransport(transport);//支持联运     }else if(c==20){      int sharedflight=2;      if (cell.getStringCellValue().equals("是")){       sharedflight=1;      }else if (cell.getStringCellValue().equals("否")){       sharedflight=0;      }else if (cell.getStringCellValue().equals("无")){       sharedflight=2;      }      oTAPolicyModel.setSharedflight(sharedflight);//支持共享航班     }else if(c==21){      oTAPolicyModel.setPstatus(cell.getStringCellValue().equals("有效")?1:2);//状态     }else if(c==22){      int faretype=0;      if (cell.getStringCellValue().equals("私有")){       faretype=1;      }else if (cell.getStringCellValue().equals("公布")){       faretype=2;      }else if (cell.getStringCellValue().equals("全部")){       faretype=0;      }      oTAPolicyModel.setFaretype(faretype);//运价类型     }else if(c==23){      oTAPolicyModel.setLimitprice(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//加价限制     }else if(c==24){      int limittransit=2;      if (cell.getStringCellValue().equals("全部")){       limittransit=2;      }else if (cell.getStringCellValue().equals("适用")){       limittransit=0;      }else if (cell.getStringCellValue().equals("不适用")){       limittransit=1;      }      oTAPolicyModel.setLimittransit(limittransit);//中转限制     }else if(c==25){      oTAPolicyModel.setArrcity(cell.getStringCellValue());//中转城市     }else if(c==26){      int limitnation=2;      if (cell.getStringCellValue().equals("全部")){       limitnation=2;      }else if (cell.getStringCellValue().equals("适用")){       limitnation=0;      }else if (cell.getStringCellValue().equals("不适用")){       limitnation=1;      }      oTAPolicyModel.setLimitnation(limitnation);//国籍限制     }else if(c==27){      oTAPolicyModel.setArrcity(cell.getStringCellValue());//国籍     }else if (c==28){      oTAPolicyModel.setUsername(cell.getStringCellValue());//用户名     }    }   }   //添加客户   oTAPolicyModelList.add(oTAPolicyModel);  }  return oTAPolicyModelList; }}

package com.flight.inter.otaadapter.commons.util;/** * Created by ling.zhang on 2016/12/29. */ public class WDWUtil { // @描述：是否是2003的excel，返回true是2003 public static boolean isExcel2003(String filePath) { return filePath.matches(“^.+/.(?i)(xls)$”); }//@描述：是否是2007的excel，返回true是2007public static boolean isExcel2007(String filePath) { return filePath.matches("^.+//.(?i)(xlsx)$");}}