easyExcel实现导入导出

1、pom文件

        <!--alibaba easyexcel-->
        <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();
		}
	}