Java导出Excel/xlsx实战(带插入图片及多Sheet页)

不会弹吉他更不会写代码

已于 2023-10-09 20:11:57 修改

阅读量2.7k

点赞数 2

分类专栏：服务端文章标签： java excel 信息可视化 echarts

于 2023-02-06 10:24:51 首次发布

本文链接：https://blog.csdn.net/xzl0527/article/details/128897439

版权

服务端专栏收录该内容

1 篇文章 0 订阅

订阅专栏

Java导出Excel/xlsx实战(带插入图片及多Sheet页）

前言

前言

在Web做大屏的项目中经常会使用Echart做数据可视化，在此需求下用户经常会有保存这些Echart信息及即时统计数据的需求，本文以 前端完成[截图]也就是将Echart的dom元素转换成base64图片编码为前提，发送给服务端，依赖于HUTOOL java工具包官网地址完成对Excel插入图片，插入数值，编辑多Sheet页，修改Excel样式的操作。

先看一下最终效果截图
在这里插入图片描述

PS: Hutool也是基于Poi封装功能，对于开发者比Poi更加易用。文档中关键类名cn.hutool.poi.excel
Excel 写入器
此工具用于通过POI将数据写出到Excel，此对象可完成以下两个功能

编辑已存在的Excel，可写出原Excel文件，也可写出到其它地方（到文件或到流）
新建一个空的Excel工作簿，完成数据填充后写出（到文件或到流）

依赖引用

项目工程基于gradle构建，直接在dependencies.gradle文件中引用Hutool。引用前注意版本，低于截图中版本可能有部分功能还未支持。在这里插入图片描述

服务端定义RESTFUL接口

接收前端数据DTO定义

根据自身业务确定需要用到的数据

import io.swagger.annotations.ApiModelProperty;

import java.util.List;
import java.util.Map;
/**
 * @创建人 CSDN不会吉他更不会写代码
 * @创建时间 2022/04/15
 * @描述 导出xlsx 所需要的数据从前端传来
 */
public class ExportModel {
    @ApiModelProperty(value = "base64 图片")
    private String image;

    @ApiModelProperty(value = "查询的时间")
    private String month;

    @ApiModelProperty(value = "sheet页的name")
    private String name;

    @ApiModelProperty(value = "当月统计数据")
    private  Object data;
    
    public ExportModel() {
    }
    Getter  Setter...
}

接口定义

HttpServletResponse类是关键

import javax.servlet.http.HttpServletResponse;
import java.util.List;


public interface ExportXLSXService {

    /**
     * 导出 Excel
     * 创建人 CSDN不会吉他更不会写代码
     * @return file
     */
    Boolean export(HttpServletResponse response, List<ExportModel> exportModel);
}

接口实现

笔者平常写代码爱写注释关键代码的功能都在注释中写出。xxx是项目中的数据脱敏，可以忽视。

/**
     * 导出 Excel
     * 创建人 CSDN不会吉他更不会写代码
     * @param exportModel
     * @return file
     */
    @Override
    public Boolean export(HttpServletResponse response, List<ExportModel> exportModel) {
        // 获取当前区间的月份
        List monthList = DateUtils.getExportTime(exportModel.get(0).getMonth());
        String time = monthList.get(0).toString().substring(0, 10) + "~" + monthList.get(1).toString().substring(0, 10);
        Boolean result;
        // 初始化Hutool中操作Excel的对象(基于Poi)
        ExcelWriter excelWriter = new ExcelWriter(true, "XXX");
        //设置标题字体
        Font titleFont = excelWriter.createFont();
        //设置字体
        titleFont.setFontName("黑体");
        //设置标题字体大小
        titleFont.setFontHeightInPoints((short) 11);
        CellStyle keyCellStyle = excelWriter.getOrCreateRowStyle(2);
        keyCellStyle.setFont(titleFont);
        // 设置边框
        keyCellStyle.setBorderBottom(BorderStyle.THIN);
        keyCellStyle.setBorderTop(BorderStyle.THIN);
        keyCellStyle.setBorderLeft(BorderStyle.THIN);
        keyCellStyle.setBorderRight(BorderStyle.THIN);
        keyCellStyle.setAlignment(HorizontalAlignment.LEFT);
        exportModel.forEach(model -> {
            // 第一次循环时改sheet名
            if (exportModel.indexOf(model) == 0) {
                excelWriter.renameSheet(0, model.getName());
            } else {
                // 设置sheet页名
                excelWriter.setSheet(model.getName());
            }
            switch (model.getName()) {
                // 第一个Sheet Overall Performance
                case "Overall Performance": {
                    excelWriter.merge(1, time, true);
                    excelWriter.merge(1, "Sheet页Title标题", true);
                    // 取业务数据插值
                    List<String> row1 = CollUtil.newArrayList("xxx", model.getMonth());
                    List<String> row2 = CollUtil.newArrayList("xxx", model.getData().toString());
                    List<List<String>> rows = CollUtil.newArrayList(row1, row2);
                    excelWriter.write(rows, true);
                    // 循环给标题cell单元格设置样式
                    for (int i = 0; i <= 1; i++) {
                        excelWriter.setStyle(keyCellStyle, i, 2);
                    }
                    excelWriter.autoSizeColumnAll();
                    // 插入图片
                    byte[] bytes = XLSXUtil.base64ToByte(model.getImage());
                    // 第二个参数枚举值可以看看文档中能生成什么格式的图片，我使用JPEG格式，后几个int参数为写入图片相较于index(0,0)的位置
                    excelWriter.writeImg(bytes, Workbook.PICTURE_TYPE_JPEG, 0, 0, 0, 0, 0, 6, 4, 26);
                    break;
                }
                case "Recurrent Cases(Only)": {
                    List<RecurrentCaseCountModel> data = (List<RecurrentCaseCountModel>) model.getData();
                    List<List> list = recurrentCase(data);
                    excelWriter.merge(4, time);
                    excelWriter.merge(4, "xxx");
                    // 漫长的取值插值
                    List<String> row1 = CollUtil.newArrayList("Key Statistics for the month", "Cleaning (SLA: <20%)", "xxx (SLA: <5%)", "xxx(SLA: 0%)", "xxx(SLA: <10%)");
                    List<String> row2 = CollUtil.newArrayList("Total volume of CM cases per category", list.get(0).get(0).toString(), list.get(3).get(0).toString(), list.get(4).get(0).toString(), list.get(2).get(0).toString());
                    List<String> row3 = CollUtil.newArrayList("% recurrent", list.get(0).get(1).toString(), list.get(3).get(1).toString(), list.get(4).get(1).toString(), list.get(2).get(1).toString());
                    List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3);
                    excelWriter.write(rows, true);
                    // 循环给标题cell单元格设置样式
                    for (int i = 0; i <= 4; i++) {
                        excelWriter.setStyle(keyCellStyle, i, 2);
                    }
                    excelWriter.autoSizeColumnAll();
                    // 插入图片
                    byte[] bytes = XLSXUtil.base64ToByte(model.getImage());
                    excelWriter.writeImg(bytes, Workbook.PICTURE_TYPE_JPEG, 0, 0, 0, 0, 0, 9, 5, 28);
                    break;
                }
                case "Summary Of All Handled In The Past 6 Months": {
                    List<HandledCaseCountModel> data = (List<HandledCaseCountModel>) model.getData();
                    List<List> list = handledCase(data);
                    // 合并单元格
                    excelWriter.merge(4, time);
                    excelWriter.merge(4, "Summary Of All Cases Handled In The Past 6 Months");
                    // 漫长的取值插值
                    List<String> row1 = CollUtil.newArrayList("Key Statistics for the month", "xxx", "xxx/xxx", "xxx", "xxx");
                    List<String> row2 = CollUtil.newArrayList("Total self-reported cases", list.get(0).get(0).toString(), list.get(3).get(0).toString(), list.get(4).get(0).toString(), list.get(2).get(0).toString());
                    List<String> row3 = CollUtil.newArrayList("Total xxx", list.get(0).get(1).toString(), list.get(3).get(1).toString(), list.get(4).get(1).toString(), list.get(2).get(1).toString());
                    List<String> row4 = CollUtil.newArrayList("% self-reported out of total", list.get(0).get(2).toString(), list.get(3).get(2).toString(), list.get(4).get(2).toString(), list.get(2).get(2).toString());
                    List<String> row5 = CollUtil.newArrayList("% change of self-reported cases from previous month", list.get(0).get(3).toString(), list.get(3).get(3).toString(), list.get(4).get(3).toString(), list.get(2).get(3).toString());
                    List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);
                    excelWriter.write(rows, true);
                    // 循环给标题cell单元格设置样式
                    for (int i = 0; i <= 4; i++) {
                        excelWriter.setStyle(keyCellStyle, i, 2);
                    }
                    excelWriter.autoSizeColumnAll();
                    // 插入图片
                    byte[] bytes = XLSXUtil.base64ToByte(model.getImage());
                    excelWriter.writeImg(bytes, Workbook.PICTURE_TYPE_JPEG, 0, 0, 0, 0, 0, 9, 5, 28);
                    break;
                }
                case "xxx": {
                    // float结果保留两位小数
                    DecimalFormat df = new DecimalFormat("#.##");
                    df.setRoundingMode(RoundingMode.HALF_UP.HALF_UP);
                    Map data = (Map) model.getData();
                    List list = dataFTE(data);
                    Map Cleaning = (Map) data.get("xxx");
                    Map Enforcement = (Map) data.get("xxx");
                    Map Infra = (Map) data.get("xxx");
                    Map Pests = (Map) data.get("xxx");
                    Map Triaging = (Map) data.get("xxx");
                    excelWriter.merge(6, time);
                    excelWriter.merge(6, "Monthly Full-Time Equivalent (FTE) Manpower");
                    // 漫长的取值插值
                    List<String> row1 = CollUtil.newArrayList("Key Statistics for the month (in FTE)", "xxx", "xxx", "xxx", "xxx", "Triaging", "Total");
                    List<String> row2 = CollUtil.newArrayList("Preventive maintenance (PM)", Cleaning.get("Preventive maintenance (PM)").toString(), Pests.get("Preventive maintenance (PM)").toString(), Infra.get("Preventive maintenance (PM)").toString(), Enforcement.get("Preventive maintenance (PM)").toString(), "", df.format(Double.valueOf(Cleaning.get("Preventive maintenance (PM)").toString()) + Double.valueOf(Pests.get("Preventive maintenance (PM)").toString()) + Double.valueOf(Infra.get("Preventive maintenance (PM)").toString()) + Double.valueOf(Enforcement.get("Preventive maintenance (PM)").toString())));
                    List<String> row3 = CollUtil.newArrayList("CM - xxx", Cleaning.get("CM - xxx").toString(), Pests.get("CM - xxx").toString(), Infra.get("CM - xxx").toString(), Enforcement.get("CM - xxx").toString(), "", df.format(Double.valueOf(Cleaning.get("CM - xxx").toString()) + Double.valueOf(Pests.get("CM - xxx").toString()) + Double.valueOf(Infra.get("CM - xxx").toString()) + Double.valueOf(Enforcement.get("CM - xxx").toString())));
                    List<String> row4 = CollUtil.newArrayList("CM - Self", Cleaning.get("CM - Self").toString(), Pests.get("CM - Self").toString(), Infra.get("CM - Self").toString(), Enforcement.get("CM - Self").toString(), "", df.format(Double.valueOf(Cleaning.get("CM - Self").toString()) + Double.valueOf(Pests.get("CM - Self").toString()) + Double.valueOf(Infra.get("CM - Self").toString()) + Double.valueOf(Enforcement.get("CM - Self").toString())));
                    List<String> row5 = CollUtil.newArrayList("Triaging", "", "", "", "", Triaging.get("Triaging").toString(), "");
                    List<String> row6 = CollUtil.newArrayList("Total", list.get(0).toString(), list.get(1).toString(), list.get(2).toString(), list.get(3).toString(), "", list.get(4).toString());
                    List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5, row6);
                    excelWriter.write(rows, true);
                    // 循环给标题cell单元格设置样式
                    for (int i = 0; i <= 6; i++) {
                        excelWriter.setStyle(keyCellStyle, i, 2);
                    }
                    excelWriter.autoSizeColumnAll();
                    // 插入图片
                    byte[] bytes = XLSXUtil.base64ToByte(model.getImage());
                    excelWriter.writeImg(bytes, Workbook.PICTURE_TYPE_JPEG, 0, 0, 0, 0, 0, 10, 7, 28);
                    break;
                }
                case "Misrouted Cases (xxx Only)": {
                    List<MisRoutedCountModel> data = (List<MisRoutedCountModel>) model.getData();
                    List<List> list = misroutedCase(data);
                    excelWriter.merge(4, time);
                    excelWriter.merge(4, "Misrouted Cases (xxxOnly)");
                    List<String> row1 = CollUtil.newArrayList("Key Statistics for the month", "xxx", "xxx/xxx", "xxx", "xxx");
                    List<String> row2 = CollUtil.newArrayList("Total xxx", list.get(0).get(0).toString(), list.get(1).get(0).toString(), list.get(2).get(0).toString(), list.get(3).get(0).toString());
                    List<String> row3 = CollUtil.newArrayList("Total xxxMisrouted Cases", list.get(0).get(1).toString(), list.get(1).get(1).toString(), list.get(2).get(1).toString(), list.get(3).get(1).toString());
                    List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3);
                    excelWriter.write(rows, true);
                    for (int i = 0; i <= 4; i++) {
                        excelWriter.setStyle(keyCellStyle, i, 2);
                    }
                    excelWriter.autoSizeColumnAll();
                    // 插入图片
                    byte[] bytes = XLSXUtil.base64ToByte(model.getImage());
                    excelWriter.writeImg(bytes, Workbook.PICTURE_TYPE_JPEG, 0, 0, 0, 0, 0, 10, 5, 28);
                    break;
                }
                case "Cases Which Exceeded The Case Resolution Time (xxxonly)": {
                    List<ExceededCaseCountModel> data = (List<ExceededCaseCountModel>) model.getData();
                    List<List> list = exceededData(data);
                    excelWriter.merge(4, time);
                    excelWriter.merge(4, "Cases Which Exceeded The Case Resolution Time (xxxonly)");
                    List<String> row1 = CollUtil.newArrayList("Key Statistics for the month", "xxx", "xxx/xxx", "xxx", "xxx");
                    List<String> row2 = CollUtil.newArrayList("Total volume of corrective maintenance (CM) cases per category", list.get(0).get(0).toString(), list.get(1).get(0).toString(), list.get(2).get(0).toString(), list.get(3).get(0).toString());
                    List<String> row3 = CollUtil.newArrayList("% failed time-based SLA*", list.get(0).get(1).toString(), list.get(1).get(1).toString(), list.get(2).get(1).toString(), list.get(3).get(1).toString());
                    List<String> row4 = CollUtil.newArrayList("% closed within 0.5 days/2 days before time-based SLA for cleaning/other services respectively**", list.get(0).get(2).toString(), list.get(1).get(2).toString(), list.get(2).get(2).toString(), list.get(3).get(2).toString());
                    List<String> row5 = CollUtil.newArrayList("Case resolution time for 90th percentile", list.get(0).get(3).toString(), list.get(1).get(3).toString(), list.get(2).get(3).toString(), list.get(3).get(3).toString());
                    List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);
                    excelWriter.write(rows, true);
                    for (int i = 0; i <= 4; i++) {
                        excelWriter.setStyle(keyCellStyle, i, 2);
                    }
                    excelWriter.autoSizeColumnAll();
                    // 插入图片
                    byte[] bytes = XLSXUtil.base64ToByte(model.getImage());
                    excelWriter.writeImg(bytes, Workbook.PICTURE_TYPE_JPEG, 0, 0, 0, 0, 0, 10, 5, 28);
                    break;
                }
            }
        });
        // 定义单元格背景色
        StyleSet style = excelWriter.getStyleSet();
        CellStyle headCellStyle = style.getHeadCellStyle();
        //设置内容字体
        Font font = excelWriter.createFont();
        //设置字体
        font.setFontName("黑体");
        //加粗
        font.setBold(false);
        //设置标题字体大小
        font.setFontHeightInPoints((short) 11);
        headCellStyle.setFont(font);
        // 合并单元格居中对齐
        excelWriter.getHeadCellStyle().setAlignment(HorizontalAlignment.CENTER);
        excelWriter.setStyleSet(style);
        // 常规单元格水平中间对齐，垂直中间对齐
        CellStyle cellStyle = excelWriter.getCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.LEFT);
        // 根据单元格内容自动调整单元格宽度
        excelWriter.autoSizeColumnAll();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=test.xlsx");
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            excelWriter.flush(outputStream, true);
            logger.info("文件流已开始传输");
            result = true;
        } catch (IOException e) {
            logger.error("导出Excel输出到响应流失败---" + e);
            throw new BusinessException(ErrorCode.UNEXPECTED_ERROR);
        } finally {
            excelWriter.close();
            IoUtil.close(outputStream);
        }
        logger.info("Excel导出结束");
        return result;
    }

注意在方法的结尾一定要执行
excelWriter.close();
IoUtil.close(outputStream);
做好工具类生命周期的正常回收，不然服务上线后会发生预期之外的错误。

Controller层实现

/**
     * Excel导出
     * 创建人 CSDN不会吉他更不会写代码
     */
    @ApiOperation(value = "Excel导出")
    @ApiResponses({
            @ApiResponse(code = 200, message = "导出成功", response = ExportModel.class),
            @ApiResponse(code = 400, message = "客户端异常", response = HttpError.class),
            @ApiResponse(code = 500, message = "服务器异常", response = HttpError.class)
    })
    @PostMapping("/excel/export")
    public ResponseEntity<List<ExportModel>> fileDownload(HttpServletResponse response, @RequestBody @Validated List<ExportModel> exportModel) {
        List<ExportModel> data = dashboardService.countOneMonthDataForExcel(exportModel);
        Boolean result = exportXLSXService.export(response , data);
        return new ResponseEntity<>(HttpStatus.OK);
    }

只需要控制好Boolean值通知前端是否可以去取接口中的二进制文件即可
二进制Excel文件的生成已经在方法Impl层通过flush方法输出到了接口的Response body

测试调用结果

用Swagger测试发现接口调用成功时在ResponseBody中可以拿到二进制文件
在这里插入图片描述

服务端接口层面至此完成

前端后续对接

/**
   * 导出excel
   * 创建人 CSDN不会吉他更不会写代码
   * @param data [{name:'', image: '', data: any}]
   * @returns
   */
  async exportExcel(data: any) {
    const caseServiceUrl = store.getters.configs.dashboardUrl;
    const url = stringFormat(`${caseServiceUrl}${dashboardUrl.EXPORT_EXCEL}`);
    return await this.getBlob(url, data);
  },

  getBlob(url: any, exportModel: any) {
    return new Promise((resolve: any, reject: any) => {
      const accessToken = jsonParseOrElse(sessionStorage.getItem('token'), {})['access_token'];
      const xhr = new XMLHttpRequest();
      xhr.open('POST', url, true);
      xhr.responseType = 'blob';
      xhr.timeout = 180 * 1000;
      xhr.setRequestHeader('Authorization', 'Bearer ' + accessToken);
      xhr.send(new Blob([JSON.stringify(exportModel)], {type: 'application/json'}));
      xhr.onload = () => {
        if (xhr.status === 200) {
          resolve(xhr.response);
        } else {
          reject();
        }
      };
      xhr.ontimeout = () => {
        reject('timeout');
      };
      xhr.onabort = () => {
        reject('abort');
      };
      xhr.onerror = (err) => {
        reject(err);
      };
    });
  },

/**
   * 导出excel
   * 创建人 CSDN不会吉他更不会写代码
   * @param name: 导出Excel文件的文件名
   * @returns
   */
  saveAs(blob: any, name: any) {
    const link = document.createElement('a');
    link.href = URL.createObjectURL(blob);
    link.download = name;
    link.click();
  }

结语

本文基于实战应用，成功生成带图片多Sheet页的Excel xlsx，满足用户需求，并且在后续的运维期间未出过严重问题。这也是第一次写博客，有不成熟的地方多多指正，欢迎评论交流。
基于此功能前置技术点是前端完成截图，能搜到很多实现方法，如各位哥有需求，咱再出一篇前端截图技术实现~
That’s all 欢迎关注在这里插入图片描述

不会弹吉他更不会写代码

关注

2
点赞
踩
2

收藏

觉得还不错? 一键收藏
4
评论
Java导出Excel/xlsx实战(带插入图片及多Sheet页)

在Web做大屏的项目中经常会使用Echart做数据可视化，在此需求下用户经常会有保存这些Echart信息及即时统计数据的需求，本文以前端完成[截图]也就是将Echart的dom元素转换成base64图片编码为前提，发送给服务端，依赖于HUTOOL java工具包官网地址完成对Excel插入图片，插入数值，编辑多Sheet页，修改Excel样式的操作。
复制链接

扫一扫