使用EasyExcel导入和复杂头导出

在使用easy Excel之前，可以阅读一下它的官方的文档，因为我也是阅读文档，结合自己的业务需求写的。这里分享一下，我是如何写的。

1. pom.xml文件引入easy excel的依赖

            <!--Alibaba-Excel-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.8</version>
            </dependency>

2. 需要有一个和文件对应的实体，@ExcelProperty注解为excel文件头的名字，index为第几列

@ColumnWidth(15)  //列宽
@HeadFontStyle(fontHeightInPoints = 12, color = 9) //文件头的字体大小和颜色
// 设置头的填充类型和背景颜色
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 63)
@Data // lombok注解
public class SysParamExcel implements Serializable {

    /**
     * 颜色枚举 IndexedColors
     * 参数名称
     */
    @ExcelProperty(value = "参数名称", index = 0)
    private String paramName;

    /**
     * 参数键名
     */
    @HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 2)
    @ExcelProperty(value = "参数键名", index = 1)
    private String paramKey;

    /**
     * 参数值
     */

    @ExcelProperty(value = "参数值", index = 2)
    private String paramValue;

}

一、导入

1. 创建文件导入监听器

public class ImportDataListener<T> extends AnalysisEventListener {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */

    private static final int BATCH_COUNT = 3000;
    // 这里是一个导入的公共Service，service里主要写添加保存数据库的方法
    private IImportService<T> importService;
    List<Object> list = new ArrayList<Object>();
    private Class<T> clazz;
    public ImportDataListener(IImportService<T> importService,Class<T>  clazz) {

        this.importService = importService;
        this.clazz = clazz;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     */

    @Override
    public void invoke(Object o, AnalysisContext analysisContext) {
        list.add(o);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            importService.importData(JSON.parseArray(JSON.toJSONString(list),clazz));
            // 存储完成清理 list
            list.clear();

        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        importService.importData(JSON.parseArray(JSON.toJSONString(list),clazz));
        // 存储完成清理list
        list.clear();
    }
}

2. 导入controller

    /**
     * 导入系统参数
     */
    @PostMapping("import-sysParam")
    @ApiOperationSupport(order = 7)
    @ApiOperation(value = "导入系统参数", notes = "传入excel")
    public Result importUser(MultipartFile file) {
        String filename = file.getOriginalFilename();
        if (StringUtils.isEmpty(filename)) {
            return Result.fail("请上传文件!");
        }
        if ((!StringUtils.endsWithIgnoreCase(filename, ".xls") && !StringUtils.endsWithIgnoreCase(filename, ".xlsx"))) {
            return Result.fail("请上传正确的excel文件!");
        }
        InputStream inputStream;
        try {
            ImportDataListener<SysParam> importListener = new ImportDataListener(sysParamService,SysParam.class);
            inputStream = new BufferedInputStream(file.getInputStream());
            ExcelReaderBuilder builder = EasyExcel.read(inputStream, SysParamExcel.class, importListener);
            builder.doReadAll();
        } catch (IOException e) {
            e.printStackTrace();
            return Result.status(false);
        }
        return Result.status(true);
    }

二、导出

1. 导出模板帮助类

/**
 * @Program Hi-MDCS
 * @Description 创建模板
 * @PackageName com.hierway.basic.excel
 * @Author LiuJunbao
 * @Date 2021-04-22 11:53
 */
public class ExcelTemplateWriteHandler implements SheetWriteHandler {
    /**
     * 第一行内容
     */
    private String firstTitle;


    /**
     * 实体模板类的行高
     */
    private int height;


    /**
     * 实体类 最大的列坐标 从0开始算
     */
    private int  lastCellIndex;

    public ExcelTemplateWriteHandler(String firstTitle, int height, int cellCounts) {
        this.firstTitle = firstTitle;
        this.height = height;
        this.lastCellIndex = cellCounts;

    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);
        Row row1 = sheet.createRow(0);
        row1.setHeight((short) height);
        //字体样式
        Font font = workbook.createFont();
        font.setColor((short)8);
        // 设置字体大小
        font.setFontHeightInPoints((short)12);
        Cell cell = row1.createCell(0);

        //单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平对齐
       // cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(font);
        cellStyle.setWrapText(true);
        cell.setCellStyle(cellStyle);

        //设置单元格内容
        cell.setCellValue(firstTitle);


        //合并单元格  --> 起始行， 终止行   ，起始列，终止列
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, lastCellIndex));
    }

}

2. 导出controller

    /**
     * 导出用户
     */
    @SneakyThrows
    @GetMapping("export-sysParam")
    @ApiOperationSupport(order = 8)
    @ApiOperation(value = "导出系统参数", notes = "传入查询条件Condition")
    public void exportUser( HttpServletResponse response) {
		// \n表示换行
        String title = "填写须知：\n" +
                "1.请勿改动表格；\n" +
                "2.标红字段为必填项，黑色字段为选填字段；\n" +
                "3.测试换行；\n";

        List<SysParam> list = sysParamService.list();
        List<SysParamExcel> excels = SysParamWrapper.build().getUserExcel(list);
        response.setContentType("application/vnd.ms-excel");
        String fileName = "系统参数数据导出" + ".xlsx";
        response.setHeader("Content-Disposition","attachment;filename="+ URLEncoder.encode(fileName,"UTF-8"));
        EasyExcel.write(response.getOutputStream(), SysParamExcel.class).sheet("系统参数数据表")
                .registerWriteHandler(new ExcelTemplateWriteHandler(title, 2000, 2))
                .useDefaultStyle(true).relativeHeadRowIndex(1)
                .doWrite(excels);
    }

最后看一下导出效果图。