easypoi导出,导入excel

小灰灰 2022-03-16 04:06 435阅读 0赞

在pom.xml引入easypoi的依赖包：

<dependency>
    			<groupId>cn.afterturn</groupId>
    			<artifactId>easypoi-base</artifactId>
    			<version>3.2.0</version>
    		</dependency>
    		<dependency>
    			<groupId>cn.afterturn</groupId>
    			<artifactId>easypoi-web</artifactId>
    			<version>3.2.0</version>
    		</dependency>
    		<dependency>
    			<groupId>cn.afterturn</groupId>
    			<artifactId>easypoi-annotation</artifactId>
    			<version>3.2.0</version>
    		</dependency>

Controller:

批量导出：

/**
    	 * 批量导出数据,
    	 * @param response
    	 */
    	@RequestMapping(value = "/downloadStuScoreWriter")  
        public void downloadStuScoreWriter(HttpServletResponse response,HttpServletRequest request,String ids) {  
    		List<StuScore> am=null;
    		if(ids==null||"".equals(ids)) {//查询所有数据
    			am=stuScoreService.selectDcWriterAll();
    		}else {//查询指定数据
    			try {
    				List<String> list = new ArrayList<>();
    				String str[]=ids.split(",");
    				for(int i=0,j=str.length;i<j;i++) {
    					list.add(str[i]);
    				}
    				am=stuScoreService.selectDcWriterAllZd(list);
    			} catch (NumberFormatException e) {
    				e.printStackTrace();
    			}
    		}
            try {  
                String filename="笔试成绩表";
                if (isMSBrowsers.isMSBrowser(request)) {
    				filename = URLEncoder.encode(filename, "UTF-8");
    			} else {
    				filename = new String(filename.getBytes("utf-8"), "ISO8859-1");
    			}
                response.setContentType("application/ms-excel;charset=utf-8");
                response.setHeader("Content-disposition", "attachment;filename=" + filename + "." +"xls");// 设置头部信息  
                ServletOutputStream out=response.getOutputStream();
                Workbook wb=getExcelStuScoreWriter(am);
                wb.write(out);
        		out.flush();
        		out.close();
            } catch (IOException e) {
            	e.printStackTrace();
            } 
        }
    	
    	/**
    	 * 封装
    	 * @param l
    	 * @return
    	 */
    	private Workbook getExcelStuScoreWriter(List<StuScore> stuScores) {
            List<ExcelExportEntity> colList = new ArrayList<ExcelExportEntity>();
            ExcelExportEntity colEntity = new ExcelExportEntity("序号", "xh");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            colEntity = new ExcelExportEntity("考场号", "roomCode");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            
            colEntity = new ExcelExportEntity("座位号", "seatNum");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            colEntity = new ExcelExportEntity("考生号", "ksh");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
        
            colEntity = new ExcelExportEntity("考生名称", "name");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            
            colEntity = new ExcelExportEntity("考生身份证号", "idnumber");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            
            colEntity = new ExcelExportEntity("语文成绩", "chinese");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            
            colEntity = new ExcelExportEntity("数学成绩", "math");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            
            colEntity = new ExcelExportEntity("英语成绩", "english");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            
            colEntity = new ExcelExportEntity("笔试总分", "bszf");
            colEntity.setNeedMerge(true);
            colList.add(colEntity);
            
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            int i=1;
            for (StuScore s:stuScores) {
                Map<String, Object> valMap = new HashMap<String, Object>();
                valMap.put("xh",i++);
                valMap.put("roomCode",s.getRoomCode());
                valMap.put("seatNum",s.getSeatNum());
                valMap.put("ksh",s.getKsh());
                valMap.put("name",s.getName());
                valMap.put("idnumber",s.getIdnumber());
                valMap.put("chinese",s.getChinese());
                valMap.put("math",s.getMath());
                valMap.put("english",s.getEnglish());
                valMap.put("bszf",Integer.parseInt(s.getChinese().equals("")?"0":s.getChinese())+Integer.parseInt(s.getMath().equals("")?"0":s.getMath())+Integer.parseInt(s.getEnglish().equals("")?"0":s.getEnglish()));
    	        list.add(valMap);
                }
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("笔试成绩表", "数据"), colList,list);
            return workbook;
        }

批量导入：

/**
    	 * *批量导入
    	 * @param file
    	 * @return
    	 */
    	@RequestMapping("/imports")
    	@ResponseBody
    	public MyAjaxResult imports(MultipartFile file) {
    		ImportParams params = new ImportParams();
    		List<Map<String, Object>> list = null;
    		Map<String,Object> map;
    		try {
    			params.setTitleRows(1);
    			params.setHeadRows(1);
    			list = ExcelImportUtil.importExcel(file.getInputStream(), Map.class, params);
    			
    			map=majorService.imports(list,"1");
    			
    		} catch (Exception e) {
    			e.printStackTrace();
    			log.info("批量导入转换异常");
    			return MyAjaxResult.fail_300("批量导入转换异常");
    		}
    		return MyAjaxResult.success(map);
    	}