Java导出Excel/xlsx实战(带插入图片及多Sheet页)
- 前言
- 依赖引用
- 服务端定义RESTFUL接口
- 接收前端数据DTO定义
- 接口定义
- 接口实现
- Controller层实现
- 测试调用结果
- 前端后续对接
- 结语
前言
在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;
/**
* @创建人 不会吉他更不会写代码
* @创建时间 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
* 创建人 不会吉他更不会写代码
* @return file
*/
Boolean export(HttpServletResponse response, List<ExportModel> exportModel);
}
接口实现
笔者平常写代码爱写注释 关键代码的功能都在注释中写出。xxx是项目中的数据脱敏,可以忽视。
/**
* 导出 Excel
* 创建人 不会吉他更不会写代码
* @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导出
* 创建人 不会吉他更不会写代码
*/
@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
* 创建人 不会吉他更不会写代码
* @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,满足用户需求,并且在后续的运维期间未出过严重问题。这也是第一次写博客,有不成熟的地方多多指正,欢迎评论交流。
基于此功能前置技术点是前端完成截图,能搜到很多实现方法,如各位哥有需求,咱再出一篇前端截图技术实现~