Example: Import relevant data (Excel file), and edit the relevant file data.
XML file configuration
Then configure the size of the file to be uploaded in the spring XML file
<!-- Upload file interception, set the maximum upload file size 10M=10*1024*1024(B)=10485760 bytes --> <bean id="multipartResolver"> <property name="maxUploadSize" value="10485760" /> </bean>
Jsp interface configuration
<div> <form id="sourcefile" name="sourcefile" action="" method="post" enctype="multipart/form-data"> <input type="button" value="add" onClick="addAirLine()" /> <input style="margin-left: 20px;" id="source_file" name="sourceFile" type="file" value="Select file" /> <input style="margin-left: 20px;" data-loading-text="do not submit repeatedly" type="submit" value="upload" onClick="upPolicy()"> <input style="margin-left: 20px;" type="submit" value="download template" onClick="return downloadTemplate();"> </form> </div>
js file
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; } } }) }Controller configuration
@RequestMapping(value = "/uploadCSV" ,method = RequestMethod.POST) @ResponseBody public String uploadCSV(@RequestParam("sourceFile") MultipartFile sourceFile, HttpServletRequest request,HttpServletResponse response) throws IOException{ //Determine whether the file is empty if (sourceFile==null) return null; //Get the file name String name=sourceFile.getOriginalFilename(); //Further determine whether the file is empty (that is, determine whether its size is 0 or whether its name is null) long size =sourceFile.getSize(); if (name==null ||("").equals(name) && size==0) return null; //Batch import. Parameters: file name, file. boolean b = batchImport(name,sourceFile); JSONObject jsonObject=new JSONObject(); if(b){ jsonObject.put("flag",0); jsonObject.put("success","batch import EXCEL successfully!"); }else{ jsonObject.put("flag",1); jsonObject.put("success","batch import EXCEL failed!"); } return jsonObject.toString(); }The layering is not that detailed, and the processing is done in the Controller
public boolean batchImport(String name,MultipartFile file){ boolean b = false; //Create and process EXCEL ExcelUtils readExcel=new ExcelUtils(); //Parse excel and get a collection of customer information. List<OTAPolicyModel> cpolicyList = readExcel.getExcelInfo(name ,file); if(cpolicyList != null){ b = true; } //Iteratively add information (Note: In fact, you can directly use the cpolicyList set as a parameter here, and use the foreach tag to add in the corresponding mapping file of Mybatis for batch addition.) for(OTAPolicyModel customer:cpolicyList){ policyDao.insertOTAPolicy(customer); } return b; }Tool class ExcelUtils.java
That is, the methods called by the readExcel.getExcelInfo(name,file); statement in the above method and other related methods
Apache POI provides APIs to Java programs to read and write Microsoft Office format archives. However, first of all, we have to judge the Excel version and choose a different Workbook (the 2003 version corresponds to HSSFWorkbook, and the 2007 version and above corresponds to XSSFWorkbook). In addition, generally speaking, first copy the file uploaded by the client user to the local disk of the server, and then read it from this copy file, thus avoiding data loss or corruption during reading due to network exceptions or other conditions of the client.
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 { //Total number of rows private int totalRows = 0; //Total number of pieces private int totalCells = 0; //Error message receiver private String errorMsg; //Construction method public ExcelUtils(){} //Get total number of rows public int getTotalRows() { return totalRows;} //Get total number of columns public int getTotalCells() { return totalCells;} //Get error message public String getErrorInfo() { return errorMsg; } /** * Verify EXCEL file* @param filePath * @return */ public boolean validateExcel(String filePath){ if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){ errorMsg = "The file name is not excel format"; return false; } return true; } /** * Read the EXCEL file and get the customer information collection* @param * @return */ public List<OTAPolicyModel> getExcelInfo(String fileName, MultipartFile Mfile){ //Convert the MultipartFile uploaded by the spring file to CommonsMultipartFile type CommonsMultipartFile cf= (CommonsMultipartFile)Mfile; //Get local storage path File file = new File("D://fileupload"); //Create a directory (its pathname is specified by the current File object, including any required parent path. ) if (!file.exists()) file.mkdirs(); //Create a new file File file1 = new File("D://fileupload" + new Date().getTime() + ".xlsx"); //Write the uploaded file to the newly created file try { cf.getFileItem().write(file1); } catch (Exception e) { e.printStackTrace(); } //Initialize the collection of customer information List<OTAPolicyModel> customerList=new ArrayList<OTAPolicyModel>(); //Initialize the input stream InputStream is = null; try{ //Verify whether the file name is qualified if(!validateExcel(fileName)){ return null; } //Default whether the file is version 2003 or version 2007 based on the file name boolean isExcel2003 = true; if(WDWUtil.isExcel2007(fileName)){ isExcel2003 = false; } //Instantiate the input stream based on the newly created file is = new FileInputStream(file1); //Read customer information based on the content in 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; } /** * Read customer information based on the content in excel* @param is Input stream* @param isExcel2003 Is excel 2003 or 2007 version* @return * @throws IOException */ public List<OTAPolicyModel> getExcelInfo(InputStream is,boolean isExcel2003){ List<OTAPolicyModel> customerList=null; try{ /** Select the way to create a Workbook according to the version*/ Workbook wb = null; //When excel is 2003 if(isExcel2003){ wb = new HSSFWorkbook(is); } else{//When excel is 2007 wb = new XSSFWorkbook(is); } //Read the information of the customer in Excel customerList=readExcelValue(wb); } catch (IOException e) { e.printStackTrace(); } return customerList; } /** * Read the information of the customer in Excel* @param wb * @return */ private List<OTAPolicyModel> readExcelValue(Workbook wb){ //Get the first shell Sheet sheet=wb.getSheetAt(0); //Get the number of rows in Excel this.totalRows=sheet.getPhysicalNumberOfRows(); //Get the number of columns in Excel (presumably there are rows) if(totalRows>=1 && sheet.getRow(0) != null){ this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells(); } List<OTAPolicyModel> oTAPolicyModelList=new ArrayList<OTAPolicyModel>(); OTAPolicyModel oTAPolicyModel; //Loop Excel row number, starting from the second line. The title is not stored 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()); //Collection of Excel for(int c = 0; c <this.totalCells; c++){ Cell cell = row.getCell(c); if (null != cell){ if(c==0){ oTAPolicyModel.setSource(cell.getStringCellValue());//Supplier}else if(c==1){ oTAPolicyModel.setVendee(cell.getStringCellValue());//Output channel}else if(c==2){ int triptype=0; if (cell.getStringCellValue()=="All"){ triptype=0; }else if (cell.getStringCellValue().equals("Single-way")){ triptype=10; }else if (cell.getStringCellValue().equals("Round Trip")){ triptype=20; }else if (cell.getStringCellValue().equals("Single-way direct flight")){ triptype=11; }else if (cell.getStringCellValue().equals("Round Trip")){ triptype=12; }else if (cell.getStringCellValue().equals("Round Trip")){ triptype=21; }else if (cell.getStringCellValue().equals("Round Trip")){ triptype=21; }else if (cell.getStringCellValue().equals("Round Trip")){ triptype=22; } oTAPolicyModel.setTriptype(triptype);//Itinerary type}else if(c==3){ oTAPolicyModel.setCarrier(cell.getStringCellValue());//Airline code}else if(c==4){ oTAPolicyModel.setDepcity(cell.getStringCellValue());//Departure city}else if(c==5){ oTAPolicyModel.setArrcity(cell.getStringCellValue());//Landing city}else if(c==6){ oTAPolicyModel.setSalebegindatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//Sales start date}else if(c==7){ oTAPolicyModel.setSaleenddatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//Sales end date}else if(c==8){ oTAPolicyModel.setTravelbegindatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//Travel start date}else if(c==9){ oTAPolicyModel.setTravelenddatel(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//Travel end date}else if(c==10){ int cabintype=9; if (cell.getStringCellValue().equals("all")){ cabintype=9; }else if (cell.getStringCellValue().equals("Economy Class")){ cabintype=1; }else if (cell.getStringCellValue().equals("Business")){ cabintype=2; }else if (cell.getStringCellValue().equals("First Class")){ cabintype=3; } oTAPolicyModel.setCobintype(cabintype);//Cadrift Level}else if(c==11){ oTAPolicyModel.setFdtype(cell.getStringCellValue().equals("by price range")?1:2);//Rebate type}else if(c==12){ oTAPolicyModel.setCabin(cell.getStringCellValue());//Cat}else if(c==13){ oTAPolicyModel.setPricebegin(cell.getNumericCellValue());//Minimum price}else if(c==14){ oTAPolicyModel.setPriceend(cell.getNumericCellValue());//Maximum price}else if(c==15){ oTAPolicyModel.setLmoney(cell.getNumericCellValue());//Save money}else if(c==16){ oTAPolicyModel.setFpercent(cell.getNumericCellValue());//Full price rebate}else if(c==17){ oTAPolicyModel.setFtpercent(cell.getNumericCellValue());//Please rebate}else if(c==18){ int carrierlimit=2; if (cell.getStringCellValue().equals("Yes")){ carrierlimit=1; }else if (cell.getStringCellValue().equals("No")){ carrierlimit=0; }else if (cell.getStringCellValue().equals("None")){ carrierlimit=2; } oTAPolicyModel.setCarrierlimit(carrierlimit);//Invoice airline restrictions}else if(c==19){ int transport=2; if (cell.getStringCellValue().equals("Yes")){ transport=1; }else if (cell.getStringCellValue().equals("No")){ transport=0; }else if (cell.getStringCellValue().equals("No")){ transport=0; }else if (cell.getStringCellValue().equals("No")){ transport=0; }else if (cell.getStringCellValue().equals("No")){ transport=0; }else if (cell.getStringCellValue().equals("No")){ transport=0; }else if (cell.getStringCellValue().equals("No")){ transport=0; }else if (cell.getStringCellValue().equals("Unlimited")){ transport=2; } oTAPolicyModel.setTransport(transport);//Support intermodal transport}else if(c==20){ int sharedflight=2; if (cell.getStringCellValue().equals("Yes")){ sharedflight=1; }else if (cell.getStringCellValue().equals("No")){ sharedflight=0; }else if (cell.getStringCellValue().equals("None")){ sharedflight=2; } oTAPolicyModel.setSharedflight(sharedflight);//Support shared flights}else if(c==21){ oTAPolicyModel.setPstatus(cell.getStringCellValue().equals("valid")?1:2);//Status}else if(c==22){ int faretype=0; if (cell.getStringCellValue().equals("private")){ faretype=1; }else if (cell.getStringCellValue().equals("publication")){ faretype=2; }else if (cell.getStringCellValue().equals("publication")){ faretype=2; }else if (cell.getStringCellValue().equals("all")){ faretype=0; } oTAPolicyModel.setFaretype(faretype);//Freight type}else if(c==23){ oTAPolicyModel.setLimitprice(new BigDecimal(cell.getNumericCellValue()).setScale(0,BigDecimal.ROUND_HALF_DOWN).longValue());//Cash increase limit}else if(c==24){ int limittransit=2; if (cell.getStringCellValue().equals("all")){ limittransit=2; }else if (cell.getStringCellValue().equals("Applicable")){ limittransit=0; }else if (cell.getStringCellValue().equals("not applicable")){ limittransit=1; } oTAPolicyModel.setLimittransit(limittransit);//Transition limit}else if(c==25){ oTAPolicyModel.setArrcity(cell.getStringCellValue());//Transition city}else if(c==26){ int limitnation=2; if (cell.getStringCellValue().equals("all")){ limitnation=2; }else if (cell.getStringCellValue().equals("applicable")){ limitnation=0; }else if (cell.getStringCellValue().equals("not applicable")){ limitnation=1; } oTAPolicyModel.setLimitnation(limitnation);//Nationality Limit}else if(c==27){ oTAPolicyModel.setArrcity(cell.getStringCellValue());//Nationality}else if (c==28){ oTAPolicyModel.setUsername(cell.getStringCellValue());//Username} } } //Add customer oTAPolicyModelList.add(oTAPolicyModel); } return oTAPolicyModelList; }}Tool class WDWUtil.java
package com.flight.inter.otaadapter.commons.util;/** * Created by ling.zhang on 2016/12/29. */ public class WDWUtil { // @Description: Whether it is excel of 2003, return true is 2003 public static boolean isExcel2003(String filePath) { return filePath.matches("^.+/.(?i)(xls)$"); }//@Description: Whether it is excel of 2007, return true is 2007public static boolean isExcel2007(String filePath) { return filePath.matches("^.+//.(?i)(xlsx)$");}}Note: For the sake of reading convenience, the above code is first posted with the parent method and then the child method. In actual code editing, the child method is generally first edited and then edited with the parent method. For example, the above should first edit the tool class code, then edit the service layer code, and finally edit the controller code.
In this way, the whole process is OK. Take it to test it now
For more exciting content, please click "Spring Upload and Download Special Topic" for in-depth study and research.
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.