天天新消息丨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> 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