index.jsp
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title> </head><body> 选择文件上传： <form action="${pageContext.request.contextPath}/wenwu/importExcel.action" method="post" enctype="multipart/form-data"> <input type="file" name="file" /> <input type="submit" value="导入Excel" /> </form> </body> </html>
WenwuController.java
/*
* 单文件导入Excel */ @RequestMapping(value = "/importExcel") public String importExcel(HttpServletRequest request) throws Exception{ List<Wenwu> list=new ArrayList<Wenwu>(); //将当前上下文初始化给 CommonsMutipartResolver（多部分解析器） CommonsMultipartResolver multipartResolver=new CommonsMultipartResolver(request.getSession().getServletContext()); //检查form中是否有enctype="multipart/form-data" if(multipartResolver.isMultipart(request)){ //将request变成多部分request MultipartHttpServletRequest multiRequest=(MultipartHttpServletRequest)request; //获取multiRequest 中所有的文件名 Iterator iter=multiRequest.getFileNames();while(iter.hasNext()){
//一次遍历所有文件 MultipartFile file=multiRequest.getFile(iter.next().toString()); if(file!=null){ //得到上传文件名 String filename=file.getOriginalFilename(); String path="E:/springUpload"+filename; //上传 file.transferTo(new File(path)); wenwuService.importExcelT(path); } } } wenwuService.saveAll(list); System.out.println("ok"); return "success"; }
WenwuServiceImpl.java
@Override
public void importExcelT(String path) throws IOException { //定义集合批量处理数据 List<Wenwu> list=new ArrayList<Wenwu>(); int num=0; Workbook book=null; try { book = Workbook.getWorkbook(new File(path)); //遍历excel表里有几个工作簿 num=book.getNumberOfSheets(); } catch (BiffException e) { // TODO Auto-generated catch block e.printStackTrace(); } //遍历工作表 for(int i=0;i<num;i++){ //获得第i个工作表对象 Sheet sheet = book.getSheet(i); int rowcount = sheet.getRows();//行数 //int colcount = sheet.getColumns();//列数 for(int rowindex = 2; rowindex<=rowcount;rowindex++){ int colindex = 1; //得到第1行第1列的单元格 Cell cell = sheet.getCell(0, 0); //System.out.println("==============="+cell.getContents()); Cell cell1 = sheet.getCell(colindex-1, rowindex-1); Cell cell2 = sheet.getCell(colindex-0, rowindex-1); Cell cell3 = sheet.getCell(colindex+1, rowindex-1); //实体属性初始化 String type=book.getSheet(i).getName(); String name = null; String property = null; String data = null; if("名称".equals(cell.getContents())){ name = cell1.getContents(); property = cell2.getContents(); data = cell3.getContents(); }else{ Cell cell4 = sheet.getCell(colindex+2, rowindex-1); name = cell2.getContents(); property = cell3.getContents(); data = cell4.getContents(); } Wenwu wenwu =new Wenwu(); wenwu.setType(type); wenwu.setName(name); wenwu.setProperty(property); wenwu.setData(data); if(property!=null && property!=""){ list.add(wenwu); } } } wenwuDao.saveAll(list); }