<!-- 上傳文件攔截，設置最大上傳文件大小 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)$");}}