SpringBoot导出xlsx文章目录
- SpringBoot导出xlsx
- 1.需求
- 2.架构选定
- 2.Hutool 工具类
- 3.POI 工具类
- 4.前端响应方法
在Sprongboot项目中，导出数据为xlsx，然后放入HttpServletResponse中，响应给客户端；
2.架构选定-
Hutool
优点：
- hutool对poi操作进行大量的封装，可以简单调用工具类进行快速开发；
缺点：
- 不能进行细粒度开发，比如给每个单元格设置不同的的背景色，hutool没有提供相应的工具类；
- API网址：https://www.hutool.cn/docs/#/poi/Excel%E7%94%9F%E6%88%90-ExcelWriter
-
Apache POI
优点：
- 提供丰富的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
1. 依赖：
cn.hutool hutool-all5.7.17
2. 工具类：
public static void export(List
合并策略类
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.experimental.Accessors;
@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结果：
1. 依赖
commons-fileupload commons-fileupload1.3.1 commons-io commons-io2.5 org.apache.poi poi3.17 org.apache.poi poi-ooxml3.17
2. 工具类
public static void createXSS(String sheetName, List> title,
List> data,List 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();
XSSFWorkbook wb = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet(sheetName);
sheet.setDefaultColumnWidth(15);
sheet.setVerticallyCenter(true);
List 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 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 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 values = new ArrayList();
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();
}
}
调用方法
private static void setUpColor(XSSFCell cell,List color,int j,int distance){
List> 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 list = total.get(i);
if(list.contains(j)){
cell.setCellStyle(color.get(i));
return;
}
}
}
private static List getBackgroundColour(XSSFWorkbook wb){
List 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.导出结果
存在两列标题，第一列进行了合并，并设置了不同的背景色：
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)
}
}
