Easy-poi快速导出百万数据只需25秒

pony_hjc

已于 2022-10-27 17:03:34 修改

阅读量1.6k

点赞数

分类专栏： java springboot easy-poi 文章标签： java spring boot spring cloud

于 2022-10-27 17:00:50 首次发布

本文链接：https://blog.csdn.net/qq_41513129/article/details/127555953

版权

java 同时被 3 个专栏收录

10 篇文章 0 订阅

订阅专栏

springboot

2 篇文章 0 订阅

订阅专栏

easy-poi

1 篇文章 0 订阅

订阅专栏

Easy-poi快速导出百万数据只需25秒

1 整合easy-poi

  pom文件

  <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>3.0.1</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>3.0.1</version>
        </dependency>

springboot整合的pom
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>3.3.0</version>
</dependency>

2 表格样式调整

public class ExcelStyleUtils implements IExcelExportStyler {

private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 10;
private static final short FONT_SIZE_ELEVEN = 11;
private static final short FONT_SIZE_TWELVE = 12;

/**
 * 大标题样式
 */
private CellStyle headerStyle;
/**
 * 每列标题样式
 */
private CellStyle titleStyle;
/**
 * 数据行样式
 */
private CellStyle styles;

public ExcelStyleUtils(Workbook workbook) {
    this.init(workbook);
}

/**
 * 初始化样式
 *
 * @param workbook
 */
private void init(Workbook workbook) {
    this.headerStyle = initHeaderStyle(workbook);
    this.titleStyle = initTitleStyle(workbook);
    this.styles = initStyles(workbook);
}


/**
 * 大标题样式
 *
 * @param color
 * @return
 */
@Override
public CellStyle getHeaderStyle(short color) {
    return headerStyle;
}

/**
 * 每列标题样式
 *
 * @param color
 * @return
 */
@Override
public CellStyle getTitleStyle(short color) {
    return titleStyle;
}

/**
 * 数据行样式
 *
 * @param parity 可以用来表示奇偶行
 * @param entity 数据内容
 * @return 样式
 */
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
    return styles;
}

/**
 * 获取样式方法
 *
 * @param dataRow 数据行
 * @param obj     对象
 * @param data    数据
 */
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
    return getStyles(true, entity);
}

/**
 * 模板使用的样式设置
 */
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
    return null;
}

/**
 * 初始化--大标题样式
 *
 * @param workbook
 * @return
 */
private CellStyle initHeaderStyle(Workbook workbook) {
    CellStyle style = getBaseCellStyle(workbook);
    style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
    return style;
}

/**
 * 初始化--每列标题样式
 *
 * @param workbook
 * @return
 */
private CellStyle initTitleStyle(Workbook workbook) {
    CellStyle style = getBaseCellStyle(workbook);
    style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
    //背景色
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    return style;
}

/**
 * 初始化--数据行样式
 *
 * @param workbook
 * @return
 */
private CellStyle initStyles(Workbook workbook) {
    CellStyle style = getBaseCellStyle(workbook);
    style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
    style.setDataFormat(STRING_FORMAT);
    return style;
}

/**
 * 基础样式
 *
 * @return
 */
private CellStyle getBaseCellStyle(Workbook workbook) {
    CellStyle style = workbook.createCellStyle();
    //下边框
    style.setBorderBottom(BorderStyle.THIN);
    //左边框
    style.setBorderLeft(BorderStyle.THIN);
    //上边框
    style.setBorderTop(BorderStyle.THIN);
    //右边框
    style.setBorderRight(BorderStyle.THIN);
    //水平居中
    style.setAlignment(HorizontalAlignment.CENTER);
    //上下居中
    style.setVerticalAlignment(VerticalAlignment.CENTER);
    //设置自动换行
    style.setWrapText(true);
    return style;
}

/**
 * 字体样式
 *
 * @param size   字体大小
 * @param isBold 是否加粗
 * @return
 */
private Font getFont(Workbook workbook, short size, boolean isBold) {
    Font font = workbook.createFont();
    //字体样式
    font.setFontName("宋体");
    //是否加粗
    font.setBold(isBold);
    //字体大小
    font.setFontHeightInPoints(size);
    return font;
}

3 核心代码


public class EasyPoiUtils {

    /**
     * 不需要创建表头
     * @param list
     * @param title
     * @param sheetName
     * @param pojoClass
     * @param fileName
     * @param response
     */
    public static void sellerExportExcel(	List<?> list, String title, String sheetName,
                                             Class<?> pojoClass, String fileName,
                                             HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
     *
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param isCreateHeader 是否创建表头
     * @param fileName
     * @param response
     * @return
     */
    public static void exportExcel(	List<?> list, String title, String sheetName, Class<?> pojoClass,
                                       String fileName, boolean isCreateHeader,
                                       HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        exportParams.setStyle(ExcelStyleUtils.class);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * 不创建表头
     * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
     *
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param fileName
     * @param response
     * @return
     */
    public static void exportExcel(	List<?> list, String title, String sheetName, Class<?> pojoClass,
                                       String fileName, HttpServletResponse response,
                                       Integer userMasterId) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    /**
     * 功能描述：Map 集合导出
     *
     * @param list 实体集合
     * @param fileName 导出的文件名称
     * @param response
     * @return
     */
    public static void exportExcel(	List<Map<String, Object>> list, String fileName,
                                       HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    /**
     * 功能描述：默认导出方法
     *
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param pojoClass pojo实体
     * @param exportParams ExportParams封装实体
     * @param response
     * @return
     */
    private static void defaultExport(	List<?> list, Class<?> pojoClass, String fileName,
                                          HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) {
            downLoadExcel(fileName, response, workbook);
        }
    }


    public static Workbook bigExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,
                                       String fileName, HttpServletResponse response
                                       ) {
        ExportParams exportParams = new ExportParams(title, sheetName);
      return  bigsExport(list, pojoClass, fileName, response, exportParams);
    }

    private static Workbook bigsExport (List<?> list, Class<?> pojoClass, String fileName,
                                          HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams, pojoClass, list);
    return workbook;
    }

    /**
     * 功能描述：默认导出方法
     *
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param response
     * @return
     */
    private static void defaultExport(	List<Map<String, Object>> list, String fileName,
                                          HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null){
            downLoadExcel(fileName, response, workbook);
        }
    }



    /**
     * 功能描述：Excel导出
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook Excel对象
     * @return
     */
    public static void downLoadExcel(String fileName, HttpServletResponse response,
                                     Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
          response.setHeader("content-Type", "multipart/form-data");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }


    public static void downLoadBigExcel(String fileName, HttpServletResponse response,
                                     Workbook workbook) {
        try {
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }



    /**
     * 功能描述：根据文件路径来导入Excel
     *
     * @param filePath 文件路径
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass Excel实体类
     * @return
     */
    public static <T> List<T> importExcel(	String filePath, Integer titleRows, Integer headerRows,
                                              Class<T> pojoClass) {
        //判断文件是否存在
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        File file = new File(filePath);
        try {
            list = ExcelImportUtil.importExcel(file, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    /**
     * 功能描述：根据文件流来导入Excel
     *
     * @param
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass Excel实体类
     * @return
     */
    public static <T> List<T> importExcel(	InputStream is, Integer titleRows, Integer headerRows,
                                              Class<T> pojoClass) {
        //判断文件是否存在
        if (is == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(is, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    /**
     * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
     *
     * @param file 上传的文件
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass Excel实体类
     * @return
     */
    public static <T> List<T> importExcel(	MultipartFile file, Integer titleRows, Integer headerRows,
                                              Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());

        }
        return list;
    }

4 测试

@RequestMapping(value=“/easypoi”,method= RequestMethod.GET)
public boolean exportIgenInvHistory(HttpServletRequest req, HttpServletResponse response){
List teachers = new ArrayList<>(); Workbook sheets = null;
for (int i = 0; i < 100; i++) {
Teacher teacher = new Teacher();
teacher.setName(“黄老师”);
teacher.setScholeName(“中学”);
teacher.setClassName(“2班”);
List students = new ArrayList<>();
Student student1 = new Student();
student1.setAge(20);
student1.setName(“123”);
students.add(student1);
Student student2 = new Student();
student2.setAge(20);
student2.setName(“123”);
students.add(student2);
teacher.setStus(students);
teachers.add(teacher);
}
EasyPoiUtils.exportExcel(teachers, “老师学生数据”, “老师学生表”, Teacher.class, “2022-10-27.xls”,true, response);
return true;
}