导入导出excel
导入导出excel
导入导出需要的jar包 <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>3.16</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-examples</artifactId> <version>3.16</version> </dependency> 导出： @RequestMapping("getCusByIds") @ResponseBody public Map<String ,Object> getCusByIds(String [] arr, HttpServletResponse response)throws Exception{ String fileName = "test.xls"; response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("gb2312"), "ISO8859-1"));//设置文件头编码格式 response.setContentType("APPLICATION/OCTET-STREAM;charset=UTF-8");//设置类型 response.setHeader("Cache-Control", "no-cache");//设置头 response.setDateHeader("Expires", 0);//设置日期头 Map<String,Object> map=new HashMap<>(); SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyy-MM-dd"); List<Customer> list= customerService.getCusByIds(arr); Workbook workbook =new HSSFWorkbook(); try { Sheet sheet1 = workbook.createSheet("sheetone"); Row row = sheet1.createRow(0); Cell[] cell =new HSSFCell[5]; for (int i = 0; i < cell.length; i++) { cell[i]=row.createCell(i); } cell[0].setCellValue("序号"); cell[1].setCellValue("联系人"); cell[2].setCellValue("公司名称"); cell[3].setCellValue("添加时间"); cell[4].setCellValue("联系电话"); for (int i = 0; i < list.size(); i++) { Row row1 = sheet1.createRow(i + 1); Cell[] cell2 =new HSSFCell[5]; for (int j = 0; j < cell2.length; j++) { cell2[j]=row1.createCell(j); } cell2[0].setCellValue(i+1); cell2[1].setCellValue(list.get(i).getCompanyperson()); cell2[2].setCellValue(list.get(i).getComname()); Date addtime = list.get(i).getAddtime(); String format = simpleDateFormat.format(addtime); cell2[3].setCellValue(format); cell2[4].setCellValue(list.get(i).getComphone()); } workbook.write(response.getOutputStream()); map.put("code",200); } catch (IOException e) { e.printStackTrace(); map.put("code",500); }finally { try { response.getOutputStream().flush(); response.getOutputStream().close(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } } return map; } 前端 function dao() { var arr=new Array(); //选择所有数据的id遍历进数组中 $("[name='id']").each(function () { var id=$(this).val(); arr.push(id); }); $.ajax({ type:"get", traditional: true, data:{arr:arr}, dataType:"json", url:"${pageContext.request.contextPath}/customer/getCusByIds", success:function (msg) { if(msg.code==200){ alert("导出成功"); }else{ alert("导出失败"); } } }) } 导入： 首先需要讲上传下载的mvc.xml层配置生效， springmvc.xml配置内容如下 <bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver"> //上传文件使用的编码 <property name="defaultEncoding" value="UTF-8"/> //上传文件的大小最大值 <property name="maxUploadSize" value="#{1024*1024*500}"/> </bean> //加上这个注解可以访问静态资源此注解必须搭配mvc注解驱动 <mvc:default-servlet-handler/> id名必须为此值， 其他一切参照文件上传 后端接口： @RequestMapping(value = "/import",method = RequestMethod.POST) @ResponseBody public Map<String,Object> importExcel(MultipartFile file) throws Exception{ Workbook wb = WorkbookFactory.create(file.getInputStream()); //这个日期格式必须与前台传过来的日期格式一致，前台如果是yyyy年MM月dd日则下面的表达式也要如此 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); List<Customer> customers = new ArrayList<Customer>(); for(int i = 0 ;i <wb.getNumberOfSheets();i++){ Sheet sheetAt = wb.getSheetAt(i); if(sheetAt != null){ for( int j = sheetAt.getFirstRowNum()+1; j<=sheetAt.getLastRowNum() ;j++){ Row row = sheetAt.getRow(j); Customer customer = new Customer(); int id = (int)row.getCell(0).getNumericCellValue(); customer.setId(id); String stringCellValue = row.getCell(1).getStringCellValue(); customer.setCompanyperson(stringCellValue); String companyName = row.getCell(2).getStringCellValue(); customer.setComname(companyName); String date = row.getCell(3).getStringCellValue(); customer.setAddtime( sdf.parse(date)); customers.add(customer); } } //打印发现没有问题，此处是实验 //正确的使用方式是此处调用业务层然后继续调用dao层把这个集合数据写入数据库中 System.out.println(customers); } System.out.println("导入方法执行了........"); Map<String,Object> map = new HashMap<String,Object>(); map.put("statusCode",200); map.put("message","导入成功"); return map; } 前端： function importExcel() { var formData = new FormData(); var interval = setInterval(function () { var file = $("#file")[0].files[0]; if(file != undefined){ formData.append("file",file); //取消定时器 clearInterval(interval); $.ajax({ type:"POST", url:"${pageContext.request.contextPath}/cust/import", data:formData, processData: false, contentType:false, cache:false, success:function (msg) { if(msg.statusCode == 200){ alert(msg.message); } } }); } },1000); } <style> #uploadImg{ font-size:16px; overflow:hidden; position:absolute } #file{ position:absolute; z-index:100; margin-left:-180px; font-size:60px; opacity:0; filter:alpha(opacity=0); margin-top:-5px; } </style> <span id="uploadImg"> <input type="file" id="file" size="1"onclick="importExcel()" > <a href="javascript:void(0)" >上传Excel</a> </span>
