Excel导入导出demo笔记

前言

excel 导入导出的算是项目里很常见的一个功能，以前都是使用POI来，一直知道阿里有个easyExcel 官网 这次尝试去了解了下，感觉确实很方便，上手容易。这里记录下方便后续来捞代码。

pom.xml 依赖

        com.alibabafastjson1.2.83com.alibabaeasyexcel3.1.1io.springfoxspringfox-swagger22.9.2com.github.xiaoyminswagger-bootstrap-ui1.9.6

代码案例

主要涉及：Excel的更具指定标题进行的导入导出

• 需要有 实体基类使用easyExcel的相关注解进行修饰
• 导入：需要使用监听器，可以在监听器里的invoke做业务处理
• 导出：组装数据然后通过Excel附件的形式进行下载

（一）基础类

要导出的数据类 ExportBusinessMeta,官网的 DemoData

/** 官网的 DemoData* @author lvzb* @date 2022/11/23  15:45**/
@Getter
@Setter
@EqualsAndHashCode
public class DemoData implements Serializable {private static final long serialVersionUID = 7382510541716722119L;@ExcelProperty("字符串标题")private String string;@ExcelProperty("日期标题")private Date date;@ExcelProperty("数字标题")private Double doubleData;/*** 忽略这个字段*/@ExcelIgnoreprivate String ignore;
}

/*** @author lvzb* @date 2022/11/23  15:34**/
@Data
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 24)
public class ExportBusinessMeta {@ExcelProperty("数据库类型")@ColumnWidth(value = 20)@HeadStyle(fillForegroundColor = 13)private String dataBaseType;@ExcelProperty("所属库")@ColumnWidth(value = 20)@HeadStyle(fillForegroundColor = 13)private String dataBase;@ExcelProperty("所属表")@ColumnWidth(value = 20)@HeadStyle(fillForegroundColor = 13)private String table;@ExcelProperty("字段名")@ColumnWidth(value = 20)@HeadStyle(fillForegroundColor = 13)private String column;@ExcelProperty(value = "英文全称")@ColumnWidth(value = 25)private String english_full_name;/*** 英文简称*/@ExcelProperty("英文简称")@ColumnWidth(value = 25)private String english_abbr;/*** 业务术语*/@ExcelProperty("业务术语")@ColumnWidth(value = 25)private String business_term;/*** 变量属性*/@ExcelProperty("变量属性")@ColumnWidth(value = 25)private String variable_properties;/*** 含义及用法*/@ExcelProperty("含义及用法")@ColumnWidth(value = 30)private String meaning_usage;/*** 报文域或标签*/@ExcelProperty("报文域或标签")@ColumnWidth(value = 30)private String messagedomain_label;/*** 备注*/@ExcelProperty("备注")@ColumnWidth(value = 15)private String remark;/*** 分类*/@ExcelProperty(value = "分类")private String classification;/*** 分级*/@ExcelProperty(value = "分级")private String grading;}

（二）读监听器

读监听器在执行导入解析时会被用到

/*** @author lvzb* @date 2022/10/24  17:57**/
@Slf4j
public class IndexOrNameDataListener implements ReadListener {private static final int BATCH_COUNT = 100;private List cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);private DemoDAO demoDAO;public IndexOrNameDataListener() {this.demoDAO = new DemoDAO();}public IndexOrNameDataListener(DemoDAO demoDAO) {this.demoDAO = demoDAO;}/*** 这个每一条数据解析都会来调用** @param data* @param analysisContext*/@Overridepublic void invoke(ExportBusinessMeta data, AnalysisContext analysisContext) {log.info("解析到一条数据:{}", JSON.toJSONString(data));cachedDataList.add(data);if (cachedDataList.size() >= BATCH_COUNT) {saveData();// 存储完成清理 listcachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);}}/*** 所有数据解析完成了 都会来调用** @param analysisContext*/@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {// 这里也要保存数据，确保最后遗留的数据也存储到数据库saveData();log.info("所有数据解析完成！");}/*** 加上存储数据库*/private void saveData() {log.info("{}条数据，开始存储数据库！", cachedDataList.size());demoDAO.save(cachedDataList);log.info("存储数据库成功！");}
}

（三）导出/下载/导入 controller

/*** @author lvzb* @date 2022/11/15  17:37**/
@Slf4j
@RestController
@Api(tags = "控制层")
@RequestMapping("/swagger")
public class SwaggerTestController {/*** demo Data** @return*/private List data() {List list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {DemoData data = new DemoData();data.setString("字符串" + i);data.setDate(new Date());data.setDoubleData(0.56);list.add(data);}return list;}private List busiData() {List list = ListUtils.newArrayList();for (int i = 0; i < 10; i++) {ExportBusinessMeta data = new ExportBusinessMeta();data.setBusiness_term("字符串" + i);data.setGrading(new Date() + "分级：" + i);data.setEnglish_full_name("英文全称：" + i);list.add(data);}return list;}/*** 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）** @since 2.1.1*/@GetMapping("downloadFailedUsingJson")@ApiOperation(value = "EasyExcel文件下载失败时/JSON返回", tags = "V1")public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmantry {response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");// 这里需要设置不关闭流EasyExcel.write(response.getOutputStream(), ExportBusinessMeta.class).autoCloseStream(Boolean.FALSE).sheet("模板").doWrite(data());throw new BusinessException("aaaa");} catch (Exception e) {// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");Map map = MapUtils.newHashMap();map.put("status", "failure");map.put("message", "下载文件失败" + e.getMessage());response.getWriter().println(JSON.toJSONString(map));}}@GetMapping("/downLoad")@ApiOperation(value = "EasyExcel文件下载/导出案例", tags = "V1")public void downLoad(EchoRequest echoRequest, HttpServletRequest request, HttpServletResponse response) throws IOException {// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("业务元数据导入模板", StandardCharsets.UTF_8).replaceAll("\\+", "%20");response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postmanresponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");response.setCharacterEncoding("utf-8");// filename*=utf-8'' 是一种协议规范 标识fileName的value也进行编码 具体参考 ：https://juejin.cn/post/6890692829719298061response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");/// 直接的 filename= 则标识fileName的value不进行编码，那么就会在不同的浏览器上导致附件名乱码
//        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {// 设置文件头WriteSheet sheet = EasyExcel.writerSheet(0, "业务元数据11").head(ExportBusinessMeta.class).build();// busiData() 换成 new ArrayList()就是变成模版导出了excelWriter.write(busiData(), sheet);excelWriter.finish();}}@GetMapping("/downLoadTxt")@ApiOperation(value = "Txt文件下载案例", tags = "V1")public void downLoadTxt(HttpServletRequest request, HttpServletResponse response) {String content = "Hello world \n 你好世界!";String fileName = URLEncoder.encode("生成的文本", StandardCharsets.UTF_8);response.setContentType("text/plain");response.setCharacterEncoding("utf-8");response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");response.setHeader("Content-disposition", "attachment; filename*=utf8''" + fileName + ".txt");try (ServletOutputStream ots = response.getOutputStream();BufferedOutputStream bufferOts = new BufferedOutputStream(ots)) {bufferOts.write(content.getBytes(StandardCharsets.UTF_8));bufferOts.flush();} catch (IOException e) {e.printStackTrace();}}@PostMapping("/upload")@ApiOperation(value = "excel上傳解析案例", tags = "V1")public String upload(MultipartFile file) throws IOException {// 解析之前做前置校验： 文件大小、文件类型等// .......EasyExcel.read(file.getInputStream(), ExportBusinessMeta.class, new IndexOrNameDataListener(new DemoDAO())).sheet().doRead();return "success";}}

（四）扩展(项目内的文件获取方式)

涉及从项目内下载文件，例如某些模板，这里也记录一下。最好使用类似这种方式找。DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath()

拿的是target/classes 开始往下找。

/**** @author lvzb* @date 2022/10/24  17:16**/
@Slf4j
public class ReadTest {public static final String fileName = "业务元数据1.xlsx";@Testvoid testPath() {// 只到项目的目录上String workDir = System.getProperty("user.dir");// C:\Users\zlv11\IdeaProjects\dem-backendSystem.out.println(workDir);}@SneakyThrows@Testvoid classLoaderGetFilePath() {// 拿的是target/classes 开始往下找 我们预存在项目的 resources/static/excelString path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/%e4%b8%9a%e5%8a%a1%e5%85%83%e6%95%b0%e6%8d%ae1.xlsxSystem.out.println(path);// 如果路径中带有中文会被URLEncoder,因此这里需要解码// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/业务元数据1.xlsxString filePath = URLDecoder.decode(path, "UTF-8");System.out.println(filePath);}@SneakyThrows@Testvoid getFilePath() {String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/%e4%b8%9a%e5%8a%a1%e5%85%83%e6%95%b0%e6%8d%ae1.xlsxSystem.out.println(path);// toURI.getPath()也能解码中文// /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/业务元数据1.xlsxSystem.out.println(DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath());}@Testvoid readTestMethodOne() {// 未处理路径上的中文字符会导致如下报错// com.alibaba.excel.exception.ExcelCommonException: Convert excel format exception.You can try specifying the 'excelType' yourselfString path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();System.out.println(path);String fileName = path;EasyExcel.read(fileName, ColumnEntityWithBusiness.class, new PageReadListener(dataList -> {for (ColumnEntityWithBusiness c : dataList) {log.info("读取到一条数据{}", JSON.toJSONString(c));}})).sheet().doRead();}@SneakyThrows@Testvoid readTestMethodTwo() {
//        String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath();try (ExcelReader reader = EasyExcel.read(path, ColumnEntityWithBusiness.class, new IndexOrNameDataListener()).build()) {ReadSheet readSheet = EasyExcel.readSheet(0).build();reader.read(readSheet);}
//        EasyExcel.read(fileName, ColumnEntityWithBusiness.class, new DemoDataListener()).sheet().doRead();}
}