[大数据]EasyPoi导出excel以及整改表格宽度

一、背景

最近做了一个excel导出功能，最开始查阅资料，EasyPoi以及阿里出的EasyExcel都是不错的工具，二者对比，EasyPoi导出效率优于EasyExcel，但是前者要比后者更消耗内存。（这个对比后续我会单独做总结，这篇文章主讲使用EasyPoi的导出excel的使用）

二、导出步骤

（1）maven

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

（2）定义实体

/**
 * @ClassName: MsgClient
 * @Description:
 * @Author: WM
 * @Date: 2021-07-24 19:09
 **/
@Data
@ExcelTarget("msgClient")
public class MsgClient {
    @Excel(name = "客户姓名")
    private String clientName;
    @Excel(name = "客户电话号码")
    private String clientPhone;
    @Excel(name = "创建人")
    private String createBy;
    @Excel(name = "ID")
    private String id;
    @Excel(name = "标记")
    private String remark;
    @Excel(name = "生日")
    private Date birthday;
}

（3）定义导出的excel的样式

/**
 * @ClassName: ExcelStyleUtil
 * @Description: Excel样式设置
 * @Author: WM
 * @Date: 2021-07-24 18:23
 **/
public class ExcelStyleUtil 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 ExcelStyleUtil(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 excelExportEntity 数据内容
     * @return
     */
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity excelExportEntity) {
        return styles;
    }

    /**
     * 数据行样式
     *
     * @param cell              单元格
     * @param dataRow           数据行
     * @param excelExportEntity 数据内容
     * @param obj               对象
     * @param data              数据
     * @return
     */
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity excelExportEntity, Object obj, Object data) {
        return getStyles(true, excelExportEntity);
    }

    /**
     * 模板使用的样式设置
     *
     * @param b
     * @param excelForEachParams
     * @return
     */
    @Override
    public CellStyle getTemplateStyles(boolean b, 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;
    }
}

（4）定义Excel导出工具类

/**
 * @ClassName: ExcelExportUtil
 * @Description: Excel导出工具类
 * @Author: WM
 * @Date: 2021-07-24 18:47
 **/
@Slf4j
@Component
public class MyExcelExportUtil {
    @Resource
    private IExcelExportServer excelExportServer;
    /**
     * 小量数据允许导出的最大条数
     */
    private static final Integer EXPORT_EXCEL_BASE_MAX_NUM = 100000;

    /**
     * 获取导出的 Workbook对象
     * 普通导出
     *
     * @param title     大标题
     * @param sheetName 页签名
     * @param object    导出实体
     * @param list      普通导出传入的数据集合
     * @param list      数据集合
     * @return Workbook
     */
    public static Workbook getWorkbook(String title, String sheetName, Class<?> object, List<?> list, ExcelType excelType) {
        // 判断导出数据是否为空
        if (list == null) {
            list = new ArrayList<>();
        }
        // 判断导出数据数量是否超过限定值
        if (list.size() > EXPORT_EXCEL_BASE_MAX_NUM) {
            title = "导出数据行数超过:" + EXPORT_EXCEL_BASE_MAX_NUM + "条，无法导出！";
            list = new ArrayList<>();
        }
        // 获取导出参数
        ExportParams exportParams = new ExportParams(title, sheetName, excelType);
        // 设置导出样式
        exportParams.setStyle(ExcelStyleUtil.class);
        // 设置行高
        exportParams.setHeight((short) 8);
        // 普通导出，输出Workbook流
        return ExcelExportUtil.exportExcel(exportParams, object, list);
    }

    /**
     * 导出大数据量
     *
     * @param title
     * @param sheetName
     * @param object
     * @param queryParams 大数据量导出，查询数据的参数条件
     * @param excelType
     * @return
     */
    public Workbook getWorkbook(String title, String sheetName, Class<?> object, Object queryParams, ExcelType excelType) {
        // 获取导出参数
        ExportParams exportParams = new ExportParams(title, sheetName, excelType);
        // 设置导出样式
        exportParams.setStyle(ExcelStyleUtil.class);
        // 设置行高
        exportParams.setHeight((short) 6);
        // 导出大数据量，输出Workbook流
        return ExcelExportUtil.exportBigExcel(exportParams, object, excelExportServer, queryParams);
    }

    /**
     * 获取导出的 Workbook对象
     *
     * @param path 模板路径
     * @param map  导出内容map
     * @return Workbook
     */
    public static Workbook getWorkbook(String path, Map<String, Object> map) {
        // 获取导出模板
        TemplateExportParams params = new TemplateExportParams(path);
        // 设置导出样式
        params.setStyle(ExcelStyleUtil.class);
        // 输出Workbook流
        return ExcelExportUtil.exportExcel(params, map);
    }

    /**
     * 导出Excel
     *
     * @param workbook workbook流
     * @param fileName 文件名
     * @param response 响应
     */
    public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {
        // 输出文件
        try (OutputStream out = response.getOutputStream()) {
            // 获取文件名并转码
            String name = URLEncoder.encode(fileName, "UTF-8");
            // 编码
            response.setCharacterEncoding("UTF-8");
            // 设置强制下载不打开
            response.setContentType("application/force-download");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xlsx");
            // 输出表格
            workbook.write(out);
        } catch (IOException e) {
            log.error("文件导出异常，详情如下:", e);
            throw new RuntimeException("文件导出异常");
        } finally {
            try {
                if (workbook != null) {
                    // 关闭输出流
                    workbook.close();
                }
            } catch (IOException e) {
                log.error("文件导出异常,详情如下:", e);
            }
        }
    }
}

（5）Controller

普通导出测试：
EasyPoi自带了小数据量导出的方法，当小于10w条数使用XSSFWorkbook，但是其在创建Excel、或者说写Excel的时候内存开销是很大的。当超过10w条数据使用SXSSFWorkbook。SXSSFWorkbook是apache的POI项目推出了一个实现大数据量的流式版本XSSFWorkbook，对于编写非常大的文件时不会耗尽内存，因为在任何时候只有行的可配置部分保存在内存中，例如合并区域，注释等。
查看ExcelExportUtil.exportExcel底层的代码发现：

/**
 * @ClassName:ExcelExportController
 * @Description:
 * @Author:Deamer
 * @Date:2020/6/15 22:42
 **/
@RestController
public class ExcelExportController {

    @Resource
    private MyExcelExportUtil myExcelExportUtil;
    @Resource
    private IExcelExportServer exportBigExcel;

	//普通导出
    @GetMapping(value = "/export")
    public void test(HttpServletResponse response) {
        List<Object> list = new ArrayList<>();
        for (int i = 0; i < 100000; i++) {  //模拟导出的数据
            MsgClient client = new MsgClient();
            client.setBirthday(new Date());
            client.setClientName("小明xxxsxsxsxsxsxsxsxsxsx" + i);
            client.setClientPhone("18797" + i);
            client.setCreateBy("JueYue");
            client.setId("1" + i);
            client.setRemark("测试" + i);
            list.add(client);
        }
        try {
            Date start = new Date();
            Workbook workbook = myExcelExportUtil.getWorkbook("计算机一班学生", "学生", MsgClient.class, list, ExcelType.XSSF);
            MyExcelExportUtil.exportExcel(workbook, String.valueOf(System.currentTimeMillis()), response);
            System.out.println("export:" + (new Date().getTime() - start.getTime()));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 }

大数据量分批导出：
数据量较大的情况，一次性从数据库查这么多数据会给数据库带来很大的压力，此时可以使用数据分页导出的方法来减轻对数据库操作的压力。

   //大数据量分页导出
    @GetMapping(value = "/bigDataExport")
    public void bigDataExport(HttpServletResponse response) throws IOException {
        Date start = new Date();
        ExportParams params = new ExportParams("大数据测试", "测试");
        Workbook workbook = ExcelExportUtil.exportBigExcel(params, MsgClient.class, exportBigExcel, new Object());
        MyExcelExportUtil.exportExcel(workbook, String.valueOf(System.currentTimeMillis()), response);
        System.out.println("bigDataExport:" + (new Date().getTime() - start.getTime()));
    }

IExcelExportServer的实现类：
查询50w条数据：

/**
 * @ClassName: IExcelExportServerImpl
 * @Description: 查询指定页码的数据
 * @Author: WM
 * @Date: 2021-07-24 19:23
 **/
@Service
public class IExcelExportServerImpl implements IExcelExportServer {
    @Override
    public List<Object> selectListForExcelExport(Object obj, int page) {
        // 在数据库中查询出数据集合
        // 此处可以写dao层分页查询的实现方法，page为当前第几页，分批次循环查询导入数据至excel中
        // obj:查询条件，page：当前第几页，pageSize：每页条数
        // List<Test> testList = dao.xxx(obj,page,pageSize);
        int pageSize = 10000;// 每页1w条数据
        List<Object> list = new ArrayList<>();
        for (int i = 0; i < 500000; i++) {  //分批导出模拟测试数据，50w条
            MsgClient client = new MsgClient();
            client.setBirthday(new Date());
            client.setClientName("小明" + i);
            client.setClientPhone("18797" + i);
            client.setCreateBy("JueYue");
            client.setId("1" + i);
            client.setRemark("测试" + i);
            list.add(client);
        }
         List partList = new ArrayList();
        if (page * pageSize <= 500000) {
            partList = list.subList((page - 1) * pageSize, page * pageSize);
        }
        return partList;
    }
}

说明：这里为什么要通过一个实现类去查询数据呢？因为要做分页，循环查出每页的数据，每次查询都要调用实现类中的查询方法。
进源码看一下：

public Workbook exportBigExcel(IExcelExportServer server, Object queryParams) {
    int page = 1;
    int var6 = page + 1;

    for(List list = server.selectListForExcelExport(queryParams, page); list != null && list.size() > 0; list = server.selectListForExcelExport(queryParams, var6++)) {
        this.appendData(list);
    }

    return this.closeExportBigExcel();
}	

（6）测试

普通导出测试：
启动项目，浏览器输入：http://localhost:8080/export
导出结果：

excel内容展示：

大数据量分页导出测试：
启动项目，浏览器输入：http://localhost:8080/bigDataExport
导出结果：

从0计数的所以是50w条数据。
第一个“测试”sheet不知道为啥是9w条数据，我看底层代码限制的是10w条数据（见下图）有知道的小伙伴麻烦留个言哈。

三、样式整改-表格宽度调节

通过查看底层源码，表格的宽度被写死了，宽度自适应的属性也不能设置了，于是乎，只能自己改源码去调节表格宽度了，如果想改成自适应的那种，改动太多了，不建议这么做了。
整改如下：
公共类MyExcelExportUtil中的修改：

自定义createExcel方法：

    /**
     * 生成Workbook
     * @param entity
     * @param pojoClass
     * @param dataSet
     * @return
     */
    public static Workbook createExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
        Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
        (new MyExcelExportService()).createSheet(workbook, entity, pojoClass, dataSet);
        return workbook;
    }

    private static Workbook getWorkbook(ExcelType type, int size) {
        if (ExcelType.HSSF.equals(type)) {
            return new HSSFWorkbook();
        } else {
            return size < USE_SXSSF_LIMIT ? new XSSFWorkbook() : new SXSSFWorkbook();
        }
    }

创建MyExcelExportService文件：

/**
 * @ClassName: MyExcelExportService
 * @Description:
 * @Author: WM
 * @Date: 2021-07-30 16:54
 **/
@Slf4j
public class MyExcelExportService {

    private static double EXCEL_WIDTH = 25.0D;// 自定义表格宽度

    public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
        if (log.isDebugEnabled()) {
            log.debug("Excel export start ,class is {}", pojoClass);
            log.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
        }

        if (workbook != null && entity != null && pojoClass != null && dataSet != null) {
            try {
                List<ExcelExportEntity> excelParams = new ArrayList();
                Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
                ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
                String targetId = etarget == null ? null : etarget.value();
                ExcelExportService excelExportService = new ExcelExportService();
                excelExportService.getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null, (ExcelEntity) null);
                excelParams.stream().forEach(x -> x.setWidth(EXCEL_WIDTH));
                excelExportService.createSheetForMap(workbook, entity, excelParams, dataSet);
            } catch (Exception var9) {
                log.error(var9.getMessage(), var9);
                throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, var9.getCause());
            }
        } else {
            throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
        }
    }
}

效果图：

四、总结：

本篇文章主要讲了通过easypoi如何导出excel的实现以及修改表格的宽度，感兴趣的小伙伴可以看看它的源码的实现，这样理解起来更加深刻。
需要更多关于easypoi功能的可以查看官方文档：
easypoi官方文档：http://easypoi.mydoc.io
百万级数据导出到excel我会在下一篇进行讲解。
可能有说的不准确的地方，欢迎指正。