SpringBoot导出xlsx
1.需求
在Sprongboot项目中，导出数据为xlsx，然后放入HttpServletResponse中，响应给客户端；
2.架构选定
-
Hutool
优点：
- hutool对poi操作进行大量的封装，可以简单调用工具类进行快速开发；
缺点：
- 不能进行细粒度开发，比如给每个单元格设置不同的的背景色，hutool没有提供相应的工具类；
- API网址：https://www.hutool.cn/docs/#/poi/Excel%E7%94%9F%E6%88%90-ExcelWriter
-
优点：
- 提供丰富的api操作，能细化到单元格进行操作；
缺点：
-
增加开发时间成本；
-
API网址：https://poi.apache.org/components/spreadsheet/quick-guide.html
-
API使用博客推荐：https://blog.csdn.net/qq_42651904/article/details/88221392?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-0.highlightwordscore&spm=1001.2101.3001.4242.1
-
使用范例博客：https://www.cnblogs.com/jike1219/p/11182303.html
2.Hutool 工具类
1. 依赖：
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.17</version>
</dependency>
2. 工具类：
/**
* 导出 xlsx 文件
* @param data 导出数据集合 not null
* @param list sheet合并策略集合
* @param response 响应类
* @param cl 列别名类 class
* @param passNum 往后跳转行数
* @param fileName 文件名称
*/
public static void export(List<Object> data, List<MergeExport> list, HttpServletResponse response,Class cl,int passNum,String fileName){
log.info("导出Excel start。。。。。");
log.info("导出数据：{}", JSONUtil.toJsonPrettyStr(data));
/*参数校验*/
if(ObjectUtil.isEmpty(data) || data.size() < 1){
log.info("停止导出没有数据的操作。。");
return ;
}
if(ObjectUtil.isEmpty(response)){
log.info("响应参数不能为空！");
return ;
}
/* 文件名称为空，生成默认文件名*/
if(StrUtil.isBlank(fileName)){
fileName = "export.xlsx";
}
log.info("文件名称：{}",fileName);
log.info("跳转行数：{}",passNum);
ServletOutputStream resp = null;
ExcelWriter writer = null;
try {
// 通过工具类创建writer，创建xlsx格式
writer = ExcelUtil.getWriter(true);
/*通过获取 @ApiModelProperty 注解，设置列别名*/
if(ObjectUtil.isNotEmpty(cl)){
Field[] declaredFields = cl.getDeclaredFields();
LinkedHashMap<String, String> collect = Arrays.stream(declaredFields)
// *** 需要有@ApiModelProperty注解的字段，没有注解的字段不要了 ***
.filter(e -> ObjectUtil.isNotEmpty(e.getAnnotation(ApiModelProperty.class)))
.collect(Collectors.toMap(Field::getName, e -> e.getAnnotation(ApiModelProperty.class).value(), (k1, k2) -> k2, LinkedHashMap::new));
writer.setHeaderAlias(collect);
}
/* 合并策略 */
if(ObjectUtil.isNotEmpty(list) && list.size() >0){
log.info("合并策略：{}", JSONUtil.toJsonPrettyStr(list));
for (MergeExport mergeExport : list) {
writer.merge(mergeExport.getFirstRow(),mergeExport.getLastRow(),mergeExport.getFirstColumn(),mergeExport.getLastColumn(),mergeExport.getContent(),true);
}
for (int i = 0; i < passNum; i++) {
/* 跳过一行
行计数 AtomicInteger currentRow 为原子类
第一行，默认 index = 0 ，跳过当前行，当前标题不会被后面数据填充所覆盖
*/
writer.passCurrentRow();
}
}
// 一次性写出内容，使用默认样式，强制输出标题
writer.write(data, true);
/*设置列宽*/
// writer.autoSizeColumnAll();
writer.setColumnWidth(-1,15);
writer.setRowHeight(0,30);
writer.setRowHeight(1,30);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename="+fileName);
resp = response.getOutputStream();
writer.flush(resp, true);
log.info("成功导出Excle：{}",fileName);
} catch (IOException e) {
log.info("导出Excle异常 error：{}",e.getMessage());
e.printStackTrace();
}finally {
log.info("关闭流 。。。。。");
// 关闭writer，释放内存
writer.close();
//此处记得关闭输出Servlet流
IoUtil.close(resp);
log.info("导出Excel end。。。。。");
}
}
合并策略类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.experimental.Accessors;
/**
*
* Excel 导出参数
* @author cs
* @since 2021-12-07
*/
@AllArgsConstructor
@Data
@Accessors(chain = true)
@ApiModel("Excel 导出参数")
public class MergeExport {
@ApiModelProperty("起始行，0开始")
private int firstRow;
@ApiModelProperty("结束行，0开始")
private int lastRow;
@ApiModelProperty("起始列，0开始")
private int firstColumn;
@ApiModelProperty("结束列，0开始")
private int lastColumn;
@ApiModelProperty("合并单元格后的内容")
private Object content;
}
3. 导出Excel结果：
3.POI 工具类
1. 依赖
<!--文件上传组件-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.5</version>
</dependency>
<!--读取excel文件-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
2. 工具类
/**
* 导出 xlsx
* @param sheetName sheet名称
* @param title 标题
* @param data 数据
* @param merge 合并策略
* @param response 响应
* @param fileName 文件名称
*/
public static void createXSS(String sheetName, List<List<String>> title,
List<LinkedHashMap<String,String>> data,List<MergeExport> merge, HttpServletResponse response
,String fileName,int distance){
log.info("export start ...");
log.info("导出标题：{}",JSONUtil.toJsonPrettyStr(title));
log.info("导出数据：{}",JSONUtil.toJsonPrettyStr(data));
log.info("合并策略：{}",JSONUtil.toJsonPrettyStr(merge));
// 创建新HSSFWorkbook，对应一个Excel文件
// HSSFWorkbook wb = new HSSFWorkbook();
/*xlsx 和xls都可以*/
XSSFWorkbook wb = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet(sheetName);
/*设置默认列宽*/
sheet.setDefaultColumnWidth(15);
/*垂直居中*/
sheet.setVerticallyCenter(true);
/*自定义设置背景色*/
/*方法见下面*/
List<XSSFCellStyle> colourStyle = getBackgroundColour(wb);
/*添加合并区域*/
for (int i = 0; i < merge.size(); i++) {
MergeExport mergeExport = merge.get(i);
CellRangeAddress rangeAddress = new CellRangeAddress(mergeExport.getFirstRow(), mergeExport.getLastRow(),
mergeExport.getFirstColumn(), mergeExport.getLastColumn());
sheet.addMergedRegion(rangeAddress);
}
//声明列对象
XSSFCell cell = null;
/*设置第一行标题*/
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
XSSFRow row = sheet.createRow(0);
row.setHeightInPoints(30);
List<String> firstTitle = title.get(0);
/*第一列设置*/
cell = row.createCell(0);
cell.setCellStyle(colourStyle.get(0));
/*其余列设置*/
int firstCol = 1;
for (int i = 0; i < firstTitle.size(); i++) {
cell = row.createCell(firstCol);
cell.setCellValue(firstTitle.get(i));
cell.setCellStyle(colourStyle.get(i+1));
firstCol+=distance;
}
/*第一行最后一列样式*/
cell = row.createCell(data.get(0).size()-1);
cell.setCellStyle(colourStyle.get(colourStyle.size()-1));
/*设置其余标题*/
for (int i = 1; i < title.size(); i++) {
XSSFRow tempRow = sheet.createRow(i);
tempRow.setHeightInPoints(30);
List<String> otherTitle = title.get(i);
for (int j = 0; j < otherTitle.size(); j++) {
cell = tempRow.createCell(j);
cell.setCellValue(otherTitle.get(j));
/*设置背景色*/
/*方法见下面*/
setUpColor(cell,colourStyle,j,otherTitle.size());
}
}
/*正式数据添加*/
for (int i = 0; i < data.size(); i++) {
XSSFRow tmpeRow = sheet.createRow(i+title.size());
List<String> values = new ArrayList<String>();
values.addAll(data.get(i).values());
for (int j = 0; j < values.size(); j++) {
cell = tmpeRow.createCell(j);
cell.setCellValue(values.get(j));
/*设置背景色*/
setUpColor(cell,colourStyle,j,values.size());
}
}
/*发送响应流*/
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition","attachment;filename="+fileName);
ServletOutputStream resp = null;
try {
resp = response.getOutputStream();
wb.write(resp);
resp.flush();
resp.close();
log.info("export sucess .....");
} catch (Exception e) {
log.info("export erro:{}",e.getMessage());
e.printStackTrace();
}
}
调用方法
/**
* 设置背景色样式
* @param cell 列对象
* @param color 背景色样式
* @param j 列数，第几列
* @param distance 总列数
*/
private static void setUpColor(XSSFCell cell,List<XSSFCellStyle> color,int j,int distance){
/**
* 构建双重集合
* 外层集合数量等于color.size
* 里面集合把 总列数按照 color的数量进行分类
*/
List<List<Integer>> total = new ArrayList<>();
/*第一列是特例*/
total.add(Arrays.asList(0));
/*颜色间距*/
int p = (distance - 1) / (color.size() - 1);
/*临时变量*/
int temp = 1;
/*构建其余子集合*/
for (int i = 1; i < color.size(); i++) {
/*根据间距 生成有序随机数*/
total.add(Arrays.stream(NumberUtil.range(temp,temp + p -1)).boxed().collect(Collectors.toList()));
temp = temp + p ;
}
/*判断当前列数该设置的背景色*/
for (int i = 0; i < total.size(); i++) {
List<Integer> list = total.get(i);
if(list.contains(j)){
cell.setCellStyle(color.get(i));
return;
}
}
}
/**
* 自定义背景色
* @param wb HSSFWorkbook 工作簿对象
* @return Cell 背景色样式集合
*/
private static List<XSSFCellStyle> getBackgroundColour(XSSFWorkbook wb){
List<XSSFCellStyle> list = new ArrayList<>();
/*灰色*/
XSSFCellStyle greeyStyle = wb.createCellStyle();
// greeyStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_80_PERCENT.getIndex());
greeyStyle.setFillForegroundColor(new XSSFColor(new Color(217,217,217)));
greeyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置水平对齐的样式为居中对齐;
greeyStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
greeyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
greeyStyle.setBorderBottom(BorderStyle.THIN);
greeyStyle.setBorderLeft(BorderStyle.THIN);
greeyStyle.setBorderTop(BorderStyle.THIN);
greeyStyle.setBorderRight(BorderStyle.THIN);
list.add(greeyStyle);
/*天蓝色*/
XSSFCellStyle blueStyle = wb.createCellStyle();
// blueStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.SKY_BLUE.getIndex());
blueStyle.setFillForegroundColor(new XSSFColor(new Color(221,235,247)));
blueStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置水平对齐的样式为居中对齐;
blueStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
blueStyle.setVerticalAlignment(VerticalAlignment.CENTER);
blueStyle.setBorderBottom(BorderStyle.THIN);
blueStyle.setBorderLeft(BorderStyle.THIN);
blueStyle.setBorderTop(BorderStyle.THIN);
blueStyle.setBorderRight(BorderStyle.THIN);
list.add(blueStyle);
/*浅黄色*/
XSSFCellStyle yellowStyle = wb.createCellStyle();
// yellowStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_YELLOW.getIndex());
yellowStyle.setFillForegroundColor(new XSSFColor(new Color(255,242,204)));
yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置水平对齐的样式为居中对齐;
yellowStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
yellowStyle.setVerticalAlignment(VerticalAlignment.CENTER);
yellowStyle.setBorderBottom(BorderStyle.THIN);
yellowStyle.setBorderLeft(BorderStyle.THIN);
yellowStyle.setBorderTop(BorderStyle.THIN);
yellowStyle.setBorderRight(BorderStyle.THIN);
list.add(yellowStyle);
/*浅绿色*/
XSSFCellStyle greenStyle = wb.createCellStyle();
// greenStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.LIGHT_GREEN.getIndex());
greenStyle.setFillForegroundColor(new XSSFColor(new Color(226,239,218)));
greenStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//设置水平对齐的样式为居中对齐;
greenStyle.setAlignment(HorizontalAlignment.CENTER);
//设置垂直对齐的样式为居中对齐;
greenStyle.setVerticalAlignment(VerticalAlignment.CENTER);
greenStyle.setBorderBottom(BorderStyle.THIN);
greenStyle.setBorderLeft(BorderStyle.THIN);
greenStyle.setBorderTop(BorderStyle.THIN);
greenStyle.setBorderRight(BorderStyle.THIN);
list.add(greenStyle);
return list;
}
3.导出结果
存在两列标题，第一列进行了合并，并设置了不同的背景色：
4.前端响应方法
async exportFile(val){
// 设置参数
this[val] = true
const param = {
startTime:this.pickNowTopDate[0],
endTime:this.pickNowTopDate[1]
}
// 调用后端方法
const res = val == 'exportVisitsInfo' ? await exportVisitsInfo(param) : val == 'exportViewsInfo' ? await exportViewsInfo(param) : await exportApplyInfo(param)
// 对响应Response的处理 接收xlsx文件
if(res.data){
this[val] = false
let filename = res.headers['content-disposition']
filename = filename.split('filename=')[1]
let link = document.createElement("a");
let blogw = new Blob([res.data],{type:"application/vnd.ms-excel;charset=utf-8"})
let objectUrl = window.URL.createObjectURL(blogw);
link.href = objectUrl;
link.download = filename;
link.click();
window.URL.revokeObjectURL(objectUrl)
}
}
扫一扫
793
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
