EasyExcel Demo

EasyExcel优选的原因

摘自：Java Excel数据导出方案及性能优化策略

HSSH、 XSSF、SXSSF方案对比

导出1W条数据，12个字段，其中2个备注大文本字段；单线程；本地环境，使用spring boot搭建的web服务，eclipse配置 Xmx1024m，启动时占用内存44M左右，堆大小320M左右。

耗时文件大小内存 cpu

HSSH (xls) 5000条7min；
1W条;
36min 1948KB 启动的时候暴增，5000条后暴增;
800M左右 12%-15%，较稳定

XSSF (xlsx) 1W条50min 397KB 950M左右 12%-15%，较稳定

SXSSF (xlsx 1W条3秒;
10W25秒 929KB 500M左右峰值15%，稳定状态下0%

SXSSF会将数据缓存到磁盘，旧的行数据不再出现在滑动窗口中并变得无法访问；最后将缓存合成一个文件的时候，会比较耗时间。

之前做过的优化方案，使用高配服务器，开并发查数据，导出50+字段，其中5个以上大文本字段，平均1W条数据耗时不超过1秒。

Apache POI官方介绍

User Model的缺点是一次性将文件读入内存,构建一颗Dom树。并且在POI对Excel的抽象中，每一行，每一个单元格都是一个对象。当文件大，数据量多的时候对内存的占用可想而知。
Event Model使用的方式是边读取边解析，并且不会将这些数据封装成Row、Cell这样的对象。而都只是普通的数字或者是字符串。并且这些解析出来的对象是不需要一直驻留在内存中，而是解析完使用后就可以回收。

SXSSF、EasyExcel大数据量方案对比

导出100W条数据，11个字段，其中1个备注大文本字段；单线程；本地环境，使用spring boot搭建的web服务， eclipse配置 Xmx1024m，启动时占用内存44M左右，堆大小320M左右。

耗时文件大小内存 CPU 文件是否能打开

SXSSF (xlsx) 176秒 62902KB 860M左右 12%左右是，耗时较长

EasyExcel (xlsx) 77秒 61678kb 60M左右 12%左右是，耗时较长

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够让原本一个3M的excel用POI sax依然需要100M左右内存降低到KB级别，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便（此处引用gitHub）；

测试对比结果可能会因为实现的方式不一样，实际的结果可能出入也比较大，但是EasyExcel相对SXSSF来说有优化是肯定的。

性能优化策略

1. 服务化，功能分离单独部署，选择支持大数据量的框架
2. 提高服务器性能，CPU、内存、IO
3. 并发查询数据，使用队列写数据，分批次执行
4. 纯文本导出，去掉excel格式，这个点优化空间比较大
5. 去掉实时交互，改为数据中心交互；后端使用队列控制请求并发
6. …….

	耗时	文件大小	内存	cpu
HSSH (xls)	5000条7min； 1W条; 36min	1948KB	启动的时候暴增，5000条后暴增; 800M左右	12%-15%，较稳定
XSSF (xlsx)	1W条50min	397KB	950M左右	12%-15%，较稳定
SXSSF (xlsx	1W条3秒; 10W25秒	929KB	500M左右	峰值15%，稳定状态下0%

	耗时	文件大小	内存	CPU	文件是否能打开
SXSSF (xlsx)	176秒	62902KB	860M左右	12%左右	是，耗时较长
EasyExcel (xlsx)	77秒	61678kb	60M左右	12%左右	是，耗时较长

逻辑流程

添加相关依赖:EasyExcel XML

<!--easyExcel start-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.0-beta2</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.2.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.2.0</version>
</dependency>
<!--easyExcel end-->

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.12</version>
    <scope>compile</scope>
</dependency>

<!--Test-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>compile</scope>
</dependency>

创建导出对象1：DbInfo（数据库对象）

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

/*
 * @Author: 踏步
 * @Date: 2021/2/17 12:19
 * @Description: 数据库对象
 */
@Data
public class DbInfo {
    @ExcelProperty("序号")
    private int order;

    @ColumnWidth(18)
    @ExcelProperty("数据库名")
    private String dbName;

    @ColumnWidth(60)
    @ExcelProperty("数据库说明")
    private String description;

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String note;

}

创建导出对象2：TableInfo（数据库表对象）

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/*
 * @Author: 踏步
 * @Date: 2021/2/17 12:20
 * @Description: 数据库表对象
 */
@Data
public class TableInfo {
    @ExcelProperty(value = "序号", index = 0)
    private int order;

    @ExcelProperty(value = "表名", index = 1)
    private String tableName;

    @ExcelProperty(value = "表说明", index = 2)
    private String description;

    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

自定义样式工具类：EasyExcelStyleUtil

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;

/*
 * @Author: 踏步
 * @Date: 2021/2/17 12:24
 * @Description: EasyExcel样式工具类
 */
public class EasyExcelStyleUtil {

    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:25
     * @Description: 红色下划线
     */
    public static CellStyle getRedUnderLine(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setUnderline((byte) 1);
        font.setColor(IndexedColors.RED.getIndex());
        cellStyle.setFont(font);
        return cellStyle;
    }

    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:50
     * @Description: WriteCellStyle：样式参考
     */
    public static WriteCellStyle getWriteCellStyle(WriteCellStyle writeCellStyle) {
        if (null == writeCellStyle) {
            writeCellStyle = new WriteCellStyle();
        }
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        writeCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        writeCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        // 字体样式
        WriteFont contentWriteFont = new WriteFont();
        // 字体高度
        contentWriteFont.setFontHeightInPoints((short) 12);
        writeCellStyle.setWriteFont(contentWriteFont);
        //设置 自动换行
        writeCellStyle.setWrapped(true);
        //设置 垂直居中
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        writeCellStyle.setBorderLeft(BorderStyle.DASHED);
        writeCellStyle.setBorderTop(BorderStyle.DASHED);
        writeCellStyle.setBorderRight(BorderStyle.DASHED);
        writeCellStyle.setBorderBottom(BorderStyle.DASHED);
        return writeCellStyle;
    }


    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:42
     * @Description: 标题样式：字体高度12
     */
    public static WriteCellStyle getTitleWriteCellStyle(WriteCellStyle writeCellStyle) {
        if (null == writeCellStyle) {
            writeCellStyle = new WriteCellStyle();
        }
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        writeCellStyle.setWriteFont(headWriteFont);
        return writeCellStyle;
    }

    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:46
     * @Description: 内容样式：字体高度12,水平居中
     */
    public static WriteCellStyle getContentWriteCellStyle(WriteCellStyle writeCellStyle) {
        if (null == writeCellStyle) {
            writeCellStyle = new WriteCellStyle();
        }
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        writeCellStyle.setWriteFont(contentWriteFont);
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return writeCellStyle;
    }

    /*
     * @Author: 踏步
     * @Date: 2021/2/17 13:02
     * @Description: 此策略：标题使用标题样式,内容使用内容样式
     * @Param: titleStyle 标题样式
     * @Param: contentStyle 内容样式
     */
    public static HorizontalCellStyleStrategy getHorizontalStrategy(WriteCellStyle titleStyle, WriteCellStyle contentStyle) {
        if (null == titleStyle) {
            titleStyle = getTitleWriteCellStyle(null);
        }
        if (null == contentStyle) {
            contentStyle = getContentWriteCellStyle(null);
        }
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(titleStyle, contentStyle);
    }
}

自适应宽度Handler：AutoColumnHandler

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/*
 * @Author: 踏步
 * @Date: 2021/2/17 12:20
 * @Description: 自适应宽度
 */
public class AutoColumnHandler extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 255;
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    public AutoColumnHandler() {
    }

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData) cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

自定义Handler：EasyExcelHandler（添加超链接、内部跳转链接等）

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Hyperlink;

import java.util.List;

/*
 * @Author: 马家立
 * @Date: 2021/2/16 17:26
 * @Description: EasyExcel自定义拦截器处理类（可扩展）
 */
public class EasyExcelHandler extends AbstractCellWriteHandler {

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        /*
         * @Description: 添加超链接
         */
        if ("dbName".equals(head.getFieldName()) && !isHead) {
            String dbName = cell.getStringCellValue();
            CreationHelper helper = writeSheetHolder.getSheet().getWorkbook().getCreationHelper();
            // Hyperlink hyperlink = helper.createHyperlink(HyperlinkType.URL);
            // hyperlink.setAddress("http://www.baidu.com");
            Hyperlink hyperlink = helper.createHyperlink(HyperlinkType.DOCUMENT);
            // easyExcel 内部链接跳转 {dbName：表示sheet页名称，！A1：表示第几列第一行}
            hyperlink.setAddress("#" + dbName + "!A1");
            cell.setHyperlink(hyperlink);
            cell.setCellStyle(EasyExcelStyleUtil.getRedUnderLine(writeSheetHolder.getSheet().getWorkbook()));
        }
    }

}

Demo测试：封装数据、设置样式、自适应列宽、内部跳转链接、单个Sheet表写入数据，多个Sheet表写入数据

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.junit.Test;

import java.util.ArrayList;
import java.util.List;

/*
 * @Author: 踏步
 * @Date: 2021/2/17 12:27
 * @Description:EasyExcel测试
 */
public class EasyExcelExportDemo {

    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:29
     * @Description: 封装数据库对象信息
     */
    private List<DbInfo> getDbInfo() {
        List<DbInfo> list = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            DbInfo dbInfo = new DbInfo();
            dbInfo.setOrder(i);
            dbInfo.setDbName("db" + i);
            dbInfo.setDescription("数据库说明：123456789" + i);
            list.add(dbInfo);
        }
        return list;
    }

    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:29
     * @Description: 封装数据库表对象信息
     */
    private List<TableInfo> getTableInfo() {
        List<TableInfo> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            TableInfo tableInfo = new TableInfo();
            tableInfo.setOrder(i);
            tableInfo.setTableName("abc" + i);
            tableInfo.setDescription("表说明：123456789123456789123456789" + i);
            list.add(tableInfo);
        }
        return list;
    }


    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:31
     * @Description: 单个Sheet表写入
     */
    @Test
    public void simpleWrite() throws Exception {
        long startTime = System.currentTimeMillis();
        String PATH = "C:\\Users\\" + System.getenv().get("USERNAME") + "\\Desktop\\";
        String fileName = PATH + "EasyExcel.xlsx";
        /*
         * response方式写入文件,只需要把fileName替换为response.getOutputStream()即可
         *
         * fileName：写完后保存的路径
         * TableInfo.class：指定class去写,class中的参数可根据注解自定义设置
         * registerWriteHandler（）：自定义拦截器_操作空间极大
         * simple：sheet表的名称
         * getTableInfo（）：封装要写入的List数据
         */
        EasyExcel.write(fileName, TableInfo.class)
                .registerWriteHandler(new AutoColumnHandler())
                .sheet("simple").doWrite(getTableInfo());
        System.err.println("总耗时:" + (System.currentTimeMillis() - startTime));
    }

    /*
     * @Author: 踏步
     * @Date: 2021/2/17 12:31
     * @Description: 多个Sheet表写入
     */
    @Test
    public void multiplyWrite() {
        long startTime = System.currentTimeMillis();

        /*
         * response方式写入文件,只需要把fileName替换为response.getOutputStream()即可
         * HttpServletResponse response = null;
         * ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
         */

        String PATH = "C:\\Users\\" + System.getenv().get("USERNAME") + "\\Desktop\\";
        String fileName = PATH + "multiplyEasyExcel.xlsx";
        // 创建一个Excel写入工作薄对象
        ExcelWriter excelWriter = EasyExcel.write(fileName).build();

        /*
         * 创建Sheet表
         * sheetNo：第几个Sheet表
         * sheetName：Sheet表的名称
         * 第一个 registerWriteHandler：标题使用标题样式,内容使用内容样式
         * 第二个 registerWriteHandler：自适应宽度
         * 第三个 registerWriteHandler：内部跳转链接
         */
        WriteSheet writeSheet = EasyExcel.writerSheet(0, "数据库总表").head(DbInfo.class)
                .registerWriteHandler(EasyExcelStyleUtil.getHorizontalStrategy(null, null))
                .registerWriteHandler(new AutoColumnHandler())
                .registerWriteHandler(new EasyExcelHandler())
                .build();
        // 在Sheet写入数据
        excelWriter.write(getDbInfo(), writeSheet);

        /*
         * 创建第二个工作表并写入数据
         */
        writeSheet = EasyExcel.writerSheet(1, "db0").head(TableInfo.class)
                .registerWriteHandler(new AutoColumnHandler())
                .build();
        excelWriter.write(getTableInfo(), writeSheet);

        /*
         * 创建第三个工作表并写入数据
         */
        writeSheet = EasyExcel.writerSheet(2, "db1").head(TableInfo.class)
                .registerWriteHandler(new AutoColumnHandler())
                .build();
        excelWriter.write(getTableInfo(), writeSheet);

        /*
         * 创建第四个工作表并写入数据
         */
        writeSheet = EasyExcel.writerSheet(3, "db2").head(TableInfo.class)
                .registerWriteHandler(new AutoColumnHandler())
                .build();
        excelWriter.write(getTableInfo(), writeSheet);

        // 关闭流
        excelWriter.finish();

        System.err.println("总耗时:" + (System.currentTimeMillis() - startTime));
    }
}

效果图

posted @ 2021-02-17 14:08 踏步阅读(388) 评论(0) 编辑收藏举报

刷新页面返回顶部

EasyExcel Demo

摘自：Java Excel数据导出方案及性能优化策略

HSSH、 XSSF、SXSSF方案对比

Apache POI官方介绍

SXSSF、EasyExcel大数据量方案对比

性能优化策略

逻辑流程

效果图

公告