Java开发之Excel操作

标签： 无 分类： 未分类 创建时间：2022-05-06 13:44:46 更新时间：2024-11-23 10:24:24

1.前言

最近遇到一个需求，就是将json转成导出到excel中导出,我查了下，主要的工具有apache poi,easypoi和easyexcel,我以前倒是使用过poi，但是当时没有统一的博客，可能在简书，也可能在知乎，最后的代码也不知道弄到那里去了，现在再次拾起来，如何导出excel文件。

参考文章:
1.poi、easypoi和easyexcel的使用 介绍了poi,easypoi和easyexcel，分别有相应的代码示例，都有自定义数据结构的例子。
2.java- excel工具类（EasyPoi）
3.EasyExcel与EasyPoi性能对比
4.JAVA-JSON文件转excel文件
5.JAVA使用hutool poi工具读取Excel模板并写值输出文件 这里使用了 hutool 封装的 poi 进行 excel 的读取和写入。

2.文件生成

下面的示例代码，主要展示了创建两个表格，并通过浏览器进行下载的功能。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
@RequestMapping(value = "exportToExcel")
public void exportToExcel(String json,HttpServletResponse response){
    try {
        //表头
        List<List<String>> headList = new ArrayList<>();
        headList.add(Lists.newArrayList("姓名"));
        headList.add(Lists.newArrayList("年龄"));
        headList.add(Lists.newArrayList("操作时间"));

        //数据体
        List<List<Object>> dataList = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            List<Object> data = new ArrayList<>();
            data.add("张三" + i);
            data.add(20 + i);
            data.add(new Date(System.currentTimeMillis() + i));
            dataList.add(data);
        }
        /**
            * 直接下载
            */
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(1, "模版1").head(headList).build();
        excelWriter.write(dataList,writeSheet);
        WriteSheet writeSheet2 = EasyExcel.writerSheet(2, "模版2").head(headList).build();
        excelWriter.write(dataList,writeSheet2);
        //这一步很关键，不然文件会损坏
        excelWriter.finish();

    }catch (Exception e){
        logger.error("exportToExcel",e);
    }
}
参考文章:
1.EasyExcel将数据库中的多张表数据写入文件的多个sheet 写入多张表格

3.前端下载

前端主要使用了axios进行了文件下载。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
axios({
    method: 'post',
    url,
    data,
    responseType: 'arraybuffer' // 这个是关键
})
.then(res => {
    // 导出
    let filename="测试.xlsx";
    const blob = new Blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
    const url = window.URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = [filename];
    a.click();
    window.URL.revokeObjectURL(url);
})
参考文章:
1.Blob 格式 Excel 文件下载 使用axios设置responseType:’arraybuffer’,借助于Blob实现了excel文件下载

4.样式和行高

通过实现自定义的列宽策略，可以设置不同的行和列的样式。主要就是实现 CellWriteHandler 和 RowWriteHandler 接口，在接口中，实现 after 开头的方法，通过 Workbook 创建一个样式，然后编辑这个样式，最后应用到cell上面。

1
2
3
4
5
6
7
// 设置单元格样式
Workbook workbook = context.getWriteSheetHolder().getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);

// 应用样式
cell.SetCellStyle(cellStyle);

然后将其注册EasyExcel写入的时候

1
2
3
4
5
6
7
8
9
EasyExcel.write(outputStream)
    // 这里放入动态头
    .head(headNameList)
    // java以点分割要转义符
    .sheet(fileName.split("\\.")[0])
    // 注册策略
    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25)) // 简单的列宽策略，列宽20
    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25)) // 简单的行高策略：头行高30，内容行高20
    .doWrite(datalist);
参考文章:
1.easyexcel 列宽、行高、样式 这里主要使用的是注解的方式实现的
2.alibaba easyexcel 自适应（行宽, 行高）
3.EasyExcel 的行高列宽的单位，直接设置行高列宽的大小_坐等夕阳落time的博客-程序员资料_easyexcel 设置单元格高度 EasyExcel 注解方式和非注解方式设置行宽列高
4.EasyExcel 批量设置单元格样式（字体样式、背景颜色、边框样式、对齐方式、自动换
5.POI实现EXCEL单元格合并及边框样式

5.多级表头

根据网上的一个资料，我成功实现了多级表头

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
* 生成表头
*/
JSONArray header=monthData.getJSONArray("header");
List<List<String>> headList = new ArrayList<List<String>>();
String basicInfo=""; // 第一行空白行
// 第一列
headList.add( Lists.newArrayList(basicInfo,"站点名称","站点名称" ));
// 循环生成中间多级表头
header.stream().forEach(item->{
    JSONObject headerItem=(JSONObject) item;
    String label=headerItem.getString("label");
    JSONArray children=headerItem.getJSONArray("children");
    children.stream().forEach(child->{
        JSONObject childItem=(JSONObject) child;
        String title=childItem.getString("label");
        headList.add( Lists.newArrayList(basicInfo,label ,title) );
    });
});
// 最后一列
headList.add( Lists.newArrayList(basicInfo,"月度统计","月度统计"));

// 生成excel表
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
WriteSheet writeSheet = EasyExcel.writerSheet(1, "月度报表").head(headList).build();
excelWriter.write(dataList,writeSheet);
参考文章:
1.EASYEXCEL的不确定表头（根据数据生成表头）的EXCEL导出和二级表头或多级表头以及设置表头的宽度自适应
2.easyExcel的复杂表头多级表头导入 这里虽然写了多级表头，但是代码部分内容很少，都是说明性的，不知道最后的结果到底是什么生成的
3.easyExcel复杂表头的设置与样式的设置(非注解) 这里有单表头和多表头的代码。
4.EasyExcel自定义多级表头导出

6.合并单元格

合并单元格主要通过自定义合并策略实现的。这里有多个方向可以实现，根据官方的代码进行分析如下：

1.官方的 LoopMergeStrategy 实现的是 RowWriteHandler 接口，这个接口里面实现了 afterRowDispose 方法，也就是渲染每一行数据的时候，都会调用的方法，在这个方法里面，可以创建一个 CellRangeAddress 需要合并的区域，包括行号和列号。
2.官方的 OnceAbsoluteMergeStrategy 合并策略，实现的是 SheetWriteHandler 接口，这个接口里面的 afterSheetCreate 方法，在创建表格的时候会执行一次，并创建一个 CellRangeAddress 合并区域，实现单元格的合并。
3.官方的 AbstractMergeStrategy 合并策略，实现的是 CellWriteHandler 接口，这个接口里面的 afterCellDispose 方法，在渲染每一个单元格的时候，会进行调用，也是创建一个 合并区域进行合并。
4.CellRangeAddress 方法包括四个参数，CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) (开始合并行，结束合并行，开始合并列，结束合并列)

总结起来，就是在渲染行或者列或者是数据表的时候，根据一定的条件，创建一个或者是多个CellRangeAddress待合并对象，这样就可以实现单元格的合并了。如果要合并行，可以实现具体实现RowWriteHandler 接口，如果要合并列，可以实现 CellWriteHandler ，这个接口里面可以获取 context.getHeadData() 表头内容，自定义策略步骤：

1.实现自定义策略，根据需要继承的方法编写自定义合并策略，根据debug的现象来看，这个context中可能没有需要的信息，比如表头是什么，需要额外传入数据进行条件判断。我这里的例子，还做了一个操作，就是把一行中从第二列开始往后的每一个单元格，前一个单元格和后面一个合并，填充的值是后面一个单元格的值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public class ReportExcelMergeStrategy implements CellWriteHandler{
    private final static Logger logger= LoggerFactory.getLogger(ReportExcelMergeStrategy.class);

    private JSONArray data=null;

    private int dataRowNumber=0; // 数据开始行号

    public ReportExcelMergeStrategy(JSONArray datalist) {
        this.data=datalist;
    }
    @Override
    public void afterCellDispose(CellWriteHandlerContext context) {
        Integer rowIndex=context.getRowIndex(); // 获取当前行号
        if (context.getHead() || rowIndex == null) {
            dataRowNumber=rowIndex;
            return;
        }
        /**
         * 如果数据渲染时遇到了总表记录，需要进行单元格合并
         */
        int dataOffset=rowIndex-dataRowNumber-1; // 数据偏移量
        JSONObject rowData=this.data.getJSONObject(dataOffset);
        Integer siteLevel=rowData.getInteger("SiteLevel");
        if(siteLevel!=null&&siteLevel==1){
            int cellIndex=context.getColumnIndex(); // 当前列号
            if(cellIndex==0){ // 第一列不参与合并
                return;
            }
            if(cellIndex%2==0) { // 从第二列开始，偶数列向前合并，奇数列删除
                // 设置单元格样式
                Workbook workbook = context.getWriteSheetHolder().getSheet().getWorkbook();
                CellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                // 设置前一个单元格的内容
                Cell nowCell=context.getCell();
                Double nowValue=nowCell.getNumericCellValue();
                Row row=context.getRow();
                Cell prevCell=row.getCell(cellIndex-1);
                prevCell.setCellValue(nowValue);
                prevCell.setCellStyle(cellStyle); // 设置样式
                //添加合并单元格区域
                Sheet sheet=context.getWriteSheetHolder().getSheet(); // 数据表句柄
                CellRangeAddress cellRangeAddress = new CellRangeAddress(rowIndex, rowIndex, cellIndex-1, cellIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }
}

2.创建表格的时候，注册 registerWriteHandler 将自定义的写入策略注册进去

1
2
3
4
5
6
xcelWriter excelWriter = EasyExcel
        .write(response.getOutputStream())
        .registerWriteHandler(new ReportExcelMergeStrategy())
        .build();
WriteSheet writeSheet = EasyExcel.writerSheet(1, "月度报表").head(headList).build();
excelWriter.write(dataList,writeSheet);
参考文章:
1.EasyExcel导出自定义合并单元格的策略 这里实现了 CellWriteHandler 接口
2.EasyExcel 实现批量合并单元格（支持自定义） 这里实现了 AbstractRowWriteHandler 类。
3.OnceAbsoluteMergeStrategy
4.EasyExcel非注解式导出、单元格合并策略及自定义样式
5.EasyExcel合并单元格（一） 这个主要讲的就是 LoopMergeStrategy 这个合并策略的使用方式
6.Class CellRangeAddress 这里是 CellRangeAddress 的使用方法

问题

(1) 输出的excel文件为空，字节为0字节
本地开发的时候没有问题，但是在上传到docker中总是出现问题，不知道为什么，还没有错误消息。

【解决方法】
在dockerfile文件中增加字体配置。

1
2
3
4
5
6
7
8
9
## alpine-slim，apk类的镜像，下面的部分还包括了设置时区
RUN echo -e 'https://mirrors.aliyun.com/alpine/v3.6/main/\nhttps://mirrors.aliyun.com/alpine/v3.6/community/' > /etc/apk/repositories \
    && apk update \
    && apk upgrade \
    && apk --no-cache add ttf-dejavu fontconfig \
    && apk add --no-cache tzdata

## centos类的镜像
RUN yum install dejavu-* fontconfig -y
参考文章:
1.EasyExcel导出的文件为空/损坏
2.踩坑：EasyExcel导出excel导出数据为空
3.EasyExcel本地导出没问题，线上环境导出却是0kb,失败! 这里也是本地没有问题，线上有问题，可能是字体的问题
4.dejavu-fonts Alpine Linux中使用：sudo apk add ttf-dejavu，CentOS系统中使用：sudo yum install -y dejavu-*
**本文已结束，感谢您的观看**
鼓励一下

往期推荐

文章目录
  1. 1. 1.前言
  2. 2. 2.文件生成
  3. 3. 3.前端下载
  4. 4. 4.样式和行高
  5. 5. 5.多级表头
  6. 6. 6.合并单元格
  7. 7. 问题
小额赞助
本人提供免费与付费咨询服务，感谢您的支持！赞助请发邮件通知，方便公布您的善意！
**光 3.01 元
Sun 3.00 元
bibichuan 3.00 元
捐赠者名单 合计：9.01 元
微信公众号
广告位
诚心邀请广大金主爸爸洽谈合作
每日一省
isNaN 和 Number.isNaN 函数的区别？

1.函数 isNaN 接收参数后，会尝试将这个参数转换为数值，任何不能被转换为数值的的值都会返回 true，因此非数字值传入也会返回 true ，会影响 NaN 的判断。

2.函数 Number.isNaN 会首先判断传入参数是否为数字，如果是数字再继续判断是否为 NaN ，不会进行数据类型的转换，这种方法对于 NaN 的判断更为准确。

2021-04-07 链接地址
每日二省
为什么0.1+0.2 ! == 0.3，如何让其相等?

一个直接的解决方法就是设置一个误差范围，通常称为“机器精度”。对JavaScript来说，这个值通常为2-52，在ES6中，提供了Number.EPSILON属性，而它的值就是2-52，只要判断0.1+0.2-0.3是否小于Number.EPSILON，如果小于，就可以判断为0.1+0.2 ===0.3。

2021-04-07 链接地址
每日三省
== 操作符的强制类型转换规则？

1.首先会判断两者类型是否**相同，**相同的话就比较两者的大小。

2.类型不相同的话，就会进行类型转换。

3.会先判断是否在对比 null 和 undefined，是的话就会返回 true。

4.判断两者类型是否为 string 和 number，是的话就会将字符串转换为 number。

5.判断其中一方是否为 boolean，是的话就会把 boolean 转为 number 再进行判断。

6.判断其中一方是否为 object 且另一方为 string、number 或者 symbol，是的话就会把 object 转为原始类型再进行判断。

2021-04-07 链接地址
每日英语
Happiness is time precipitation, smile is the lonely sad.
幸福是年华的沉淀，微笑是寂寞的悲伤。
2016 ——— 美文