随笔记录通过easy excel实现导入导出

第一步：导入依赖

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

第二步：创建导入导出工具类

public class ExcelUtil {
    /**
     * 导出数据为excel文件
     *
     * @param filename       文件名称
     * @param sheetName      sheet名称
     * @param dataResult     集合内的bean对象类型要与clazz参数一致
     * @param clazz          集合内的bean对象类型要与clazz参数一致
     * @param response       HttpServlet响应对象
     */
//有实体对象的导出方式
    public static void export(String filename,String sheetName, List<?> dataResult, Class<?> clazz, HttpServletResponse response) {
        response.setStatus(200);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        OutputStream outputStream = null;
        ExcelWriter excelWriter = null;
        try {
            if (StringUtil.isEmpty(filename)) {
                throw new RuntimeException("'filename' 不能为空");
            }
            String fileName = filename.concat(".xls");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            outputStream = response.getOutputStream();

            // 根据不同的策略生成不同的ExcelWriter对象
            if (dataResult == null){
                excelWriter = getTemplateExcelWriter(outputStream);
            } else {
                excelWriter = getExportExcelWriter(outputStream);
            }

            //获取表头个数
            int last = clazz.getDeclaredFields().length;

            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(clazz)
                    .relativeHeadRowIndex(1)
                    .registerWriteHandler(new MonthSheetWriteHandler(sheetName,last))//设置大标题名称及其单元格合并
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽
                    .build();
            // 写出数据
            excelWriter.write(dataResult,writeSheet);

        } catch (Exception e) {
            log.error("导出excel数据异常：", e);
            throw new RuntimeException(e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (outputStream != null) {
                try {
                    outputStream.flush();
                    outputStream.close();
                } catch (IOException e) {
                    log.error("导出数据关闭流异常", e);
                }
            }
        }
    }
//没有实体对象的导出方式
    public static void export(String filename,String sheetName,String headName, List<?> dataResult, List<List<String>> head, HttpServletResponse response) {
        response.setStatus(200);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        OutputStream outputStream = null;
        ExcelWriter excelWriter = null;
        try {
            if (StringUtil.isEmpty(filename)) {
                throw new RuntimeException("'filename' 不能为空");
            }
            String fileName = filename.concat(".xls");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            outputStream = response.getOutputStream();

            // 根据不同的策略生成不同的ExcelWriter对象
            if (dataResult == null){
                excelWriter = getTemplateExcelWriter(outputStream);
            } else {
                excelWriter = getExportExcelWriter(outputStream);
            }


            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(head)
                    .relativeHeadRowIndex(1)
                    .registerWriteHandler(new MonthSheetWriteHandler(headName,head.size()))//设置大标题名称及其单元格合并
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())//自适应列宽
                    .build();
            // 写出数据
            excelWriter.write(dataResult,writeSheet);

        } catch (Exception e) {
            log.error("导出excel数据异常：", e);
            throw new RuntimeEx