easyExcel实现导入导出

港控/mmm° 2023-09-30 08:25 28阅读 0赞

### **1、pom文件** ###

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.6</version>
            </dependency>

### **2、表头信息对应实体类** ###

@Data
    public class ProjectTeamStructExcel {
        
    
    	@ExcelProperty(value = "团队名称")
    	@ColumnWidth(20)
    	private String teamName;
    
    	@ExcelProperty(value = "职位")
    	@ColumnWidth(20)
    	private String teamMemberJobTitle;
    
    	@ExcelProperty(value = "姓名")
    	@ColumnWidth(20)
    	private String teamMemberName;
    
    	@ExcelProperty(value = "身份证号码")
    	@ColumnWidth(20)
    	private String teamMemberHp;
    
    	@ExcelProperty(value = "员工工号")
    	@ColumnWidth(20)
    	private String teamMemberBoard;
    
    	@ExcelProperty(value = "团队成员备注")
    	@ColumnWidth(20)
    	private String teamMemberMemo;
    
    }

### 3、监听器 ###

public class ExcelListener extends AnalysisEventListener<ProjectTeamStructExcel> {
        
    	List<ProjectTeamStructExcel> projectTeamStructExcels;
    
    
    	public ExcelListener(List<ProjectTeamStructExcel> projectTeamStructExcels) {
        
    		this.projectTeamStructExcels = projectTeamStructExcels;
    	}
    
    	/**
    	 * 读取表头内容
    	 * @param headMap
    	 * @param context
    	 */
    	@Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        
            System.out.println("表头："+headMap);
        }
    
    	@Override
    	public void invoke(ProjectTeamStructExcel projectTeamStructExcel, AnalysisContext analysisContext) {
        
    		projectTeamStructExcels.add(projectTeamStructExcel);
    	}
    
    	/**
    	 * 读取完成之后
    	 * @param analysisContext
    	 */
    	@Override
        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
         }
    }

### 4、导入功能实现代码 ###

/**
    	 * 导入设备excel
    	 *
    	 * @param file
    	 * @return
    	 */
    	@PostMapping(value = "/importExcel")
    	@ApiOperation(value = "导入Level", notes = "导入Level")
    	public R importExcel(MultipartFile file,Long unitId) throws Exception {
        
    		List<ProjectTeamStructExcel> projectTeamStructExcels = new ArrayList<>();
    		ExcelListener excelListener = new ExcelListener(projectTeamStructExcels);
    		EasyExcel.read(file.getInputStream(), ProjectTeamStructExcel.class, excelListener).sheet().doRead();
    
    		if (Func.isEmpty(projectTeamStructExcels)) {
        
    			throw new Exception("导入内容不能为空！");
    		}
    
    		List<ProjectTeamStruct> projectTeamStructs = new ArrayList<>();
    		Date date = new Date();
    		Long userId = AuthUtil.getUserId();
    		Long deptId = Long.valueOf(AuthUtil.getDeptId());
    		for (ProjectTeamStructExcel projectTeamStructExcel : projectTeamStructExcels) {
        
    			ProjectTeamStruct projectTeamStruct = BeanUtil.copy(projectTeamStructExcel, ProjectTeamStruct.class);
    			projectTeamStruct.setUnitId(unitId);
    			projectTeamStruct.setCreateTime(date);
    			projectTeamStruct.setCreateUser(userId);
    			projectTeamStruct.setCreateDept(deptId);
    			projectTeamStruct.setUpdateTime(date);
    			projectTeamStruct.setIsDeleted(0);
    			projectTeamStruct.setUpdateUser(userId);
    			projectTeamStruct.setProjectId(CommonUtil.build().getProjectIdStr());
    			projectTeamStructs.add(projectTeamStruct);
    		}
    
    		if (Func.isNotEmpty(projectTeamStructs)) {
        
    			boolean saveBatch = projectTeamStructService.saveBatch(projectTeamStructs);
    			return R.status(saveBatch);
    		}
    		return R.data(false);
    	}

### 5、导出功能代码实现 ###

/**
    	 * 导出excel
    	 *
    	 * @param response
    	 * @param projectTeamStruct
    	 * @throws IOException
    	 */
    	@PostMapping(value = "/exportExcel", headers = "Accept=application/octet-stream")
    	@ApiOperation(value = "导出excel", notes = "导出excel")
    	public void exportExcel(@ApiIgnore HttpServletResponse response, ProjectTeamStruct projectTeamStruct) throws IOException {
        
    		Long projectId = CommonUtil.build().getProjectId();
    		projectTeamStruct.setProjectId(projectId.toString());
    		List<ProjectTeamStruct> projectTeamStructList = projectTeamStructService.list(Condition.getQueryWrapper(projectTeamStruct));
    		List<ProjectTeamStructExcel> projectTeamStructExcels = new ArrayList<>();
    		for (ProjectTeamStruct teamStruct : projectTeamStructList) {
        
    			ProjectTeamStructExcel teamStructExcel = BeanUtil.copy(teamStruct, ProjectTeamStructExcel.class);
    			projectTeamStructExcels.add(teamStructExcel);
    		}
    
    		response.setContentType("application/vnd.ms-excel");
    		response.setCharacterEncoding("utf-8");
    		String fileName = "人员信息表";
    		response.setHeader("Content-disposition", "attachment;filename="
    			+ new String(fileName.getBytes(StandardCharsets.UTF_8), "ISO-8859-1"));
    		EasyExcel.write(response.getOutputStream(), ProjectTeamStructExcel.class)
    			.excelType(ExcelTypeEnum.XLSX)
    			.sheet("人员信息").doWrite(projectTeamStructExcels);
    	}

### 6、下载excel模板功能代码实现 ###

/**
    	 * 下载Excel模板
    	 */
    	@GetMapping("/excel/template")
    	public void downloadTemplate(HttpServletResponse response) {
        
    		String fileName = "导入人员信息模板";
    		List<ProjectTeamStructExcel> projectTeamStructExcels = new ArrayList<>();
    		try {
        
    			response.setHeader("Content-disposition", "attachment;filename=" +
    				new String(fileName.getBytes(StandardCharsets.UTF_8), "ISO-8859-1"));
    			EasyExcel.write(response.getOutputStream(), ProjectTeamStructExcel.class)
    				.excelType(ExcelTypeEnum.XLSX)
    				.sheet("人员信息").doWrite(projectTeamStructExcels);
    		} catch (Exception e) {
        
    			e.printStackTrace();
    		}
    	}