easy excel 导入导出

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

第一步：导入依赖

    com.alibaba
    easyexcel
    3.1.0


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

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> 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 RuntimeException(e);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            if (outputStream != null) {
                try {
                    outputStream.flush();
                    outputStream.close();
                } catch (IOException e) {
                    log.error("导出数据关闭流异常", e);
                }
            }
        }
    }


    /**
     * 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
     * @param outputStream  数据输出流
     * @return  模板下载ExcelWriter对象
     */
    private static ExcelWriter getTemplateExcelWriter(OutputStream outputStream){
        return EasyExcel.write(outputStream)
                //.registerWriteHandler(new CommentWriteHandler())        //增加批注策略
                //.registerWriteHandler(new CustomSheetWriteHandler())    //增加下拉框策略
                .registerWriteHandler(getStyleStrategy())               //字体居中策略
                .build();
    }

    /**
     * 根据不同策略生成不同的ExcelWriter对象， 可根据实际情况修改
     * @param outputStream  数据输出流
     * @return  数据导出ExcelWriter对象
     */
    private static ExcelWriter getExportExcelWriter(OutputStream outputStream){
        return EasyExcel.write(outputStream)
                .registerWriteHandler(getStyleStrategy())   //字体居中策略
                .build();
    }

    /**
     *  设置表格内容居中显示策略
     * @return
     */
    private static HorizontalCellStyleStrategy getStyleStrategy(){
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();

        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
        //设置头字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)10);
        headWriteFont.setBold(true);
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteFont.setFontName("宋体");
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 内容策略
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        // 设置内容水平居中
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        //垂直居中,水平居中
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        writeCellStyle.setWriteFont(headWriteFont);

        //设置 自动换行
        //contentWriteCellStyle.setWrapped(true);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, writeCellStyle);
    }

    /**
     * 根据Excel模板，批量导入数据
     * @param file  导入的Excel
     * @param clazz 解析的类型
     * @return  解析完成的数据
     */
    public static List importExcel(MultipartFile file, Class clazz){
        if (file == null || file.isEmpty()){
            throw new RuntimeException("没有文件或者文件内容为空！");
        }
        List