使用SpringBoot实现excel生成和下载，生成模板如下
controller
1
2
3
4
5
6
7
8
9
10
@RequestMapping(value = { "/downloadExcelTemplate" }, method = RequestMethod.GET)
public String downloadExcelTemplate(HttpSession httpSession, HttpServletResponse response) {
try {
dealExcelService.downloadExcelTemplate(response);
return "success";
} catch (Exception e) {
logger.error("downloadExcelTemplate_error", e);
return "failure";
}
}
service
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
public void downloadExcelTemplate(HttpServletResponse response) throws Exception {
//文件名
SimpleDateFormat format3 = new SimpleDateFormat("yyyyMMddHHmm");
String fileName = new String(("文件名" + format3.format(new Date()) + "导入模板").getBytes(), "ISO8859_1");
//配置请求头
ServletOutputStream outputStream = response.getOutputStream();
// 组装附件名称和格式
response.setHeader("Content-disposition", "attachment; filename=" + fileName + ".xlsx");
// 创建一个workbook 对应一个excel应用文件
XSSFWorkbook workBook = new XSSFWorkbook();
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = workBook.createSheet("模板");
ExportUtil exportUtil = new ExportUtil(workBook, sheet);
XSSFCellStyle headStyle = exportUtil.getHeadStyle();
XSSFCellStyle bodyStyle = exportUtil.getBodyStyle2();
// 构建表头
XSSFRow headRow = ExportUtil.createRow(sheet, 0);
XSSFCell cell;
String[] titles = {"表头一", "表头二", "表头三"};
int index = 0;
for (String title : titles) {
cell = ExportUtil.createCell(headRow, index);
cell.setCellStyle(headStyle);
cell.setCellValue(title);
index++;
}
try {
workBook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
ExportUtil导出工具类
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
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package com.shengsheng.utils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
/**
* excel 表格导出工具类
*
* @author shengshenglalala
*/
public class ExportUtil {
private XSSFWorkbook wb;
private XSSFSheet sheet;
/**
* @param wb
* @param sheet
*/
public ExportUtil(XSSFWorkbook wb, XSSFSheet sheet) {
this.wb = wb;
this.sheet = sheet;
}
/**
* 合并单元格后给合并后的单元格加边框
*
* @param region
* @param cs
*/
public void setRegionStyle(CellRangeAddress region, XSSFCellStyle cs) {
int toprowNum = region.getFirstRow();
for (int i = toprowNum; i <= region.getLastRow(); i++) {
XSSFRow row = sheet.getRow(i);
for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
XSSFCell cell = row.getCell(j);
cell.setCellStyle(cs);
}
}
}
/**
* 设置表头的单元格样式
*
* @return
*/
public XSSFCellStyle getHeadStyle() {
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// // 设置单元格的背景颜色为淡蓝色
cellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
cellStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
// 设置单元格居中对齐
cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 设置单元格垂直居中对齐
cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格内容显示不下时自动换行
// cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
// font.setFontHeight((short) 200);
cellStyle.setFont(font);
// 设置单元格边框为细线条
// cellStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);
// cellStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);
return cellStyle;
}
/**
* 设置表体的单元格样式
*
* @return
*/
public XSSFCellStyle getBodyStyle2() {
// 创建单元格样式
// 创建单元格样式
XSSFCellStyle cellStyle = wb.createCellStyle();
// 创建单元格内容显示不下时自动换行
// cellStyle.setWrapText(true);
// 设置单元格字体样式
XSSFFont font = wb.createFont();
// 设置字体加粗
// font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
font.setColor(HSSFColor.BLACK.index);
cellStyle.setFont(font);
// 设置单元格边框为细线条
return cellStyle;
}
/**
* 没有行，就创建行
*
* @param sheet
* @param index
* @return
*/
public static XSSFRow createRow(XSSFSheet sheet, Integer index) {
XSSFRow row = sheet.getRow(index);
if (row == null) {
return sheet.createRow(index);
}
return row;
}
/**
* 如果没有列，就创建列
*
* @param row
* @param index
* @return
*/
public static XSSFCell createCell(XSSFRow row, Integer index) {
XSSFCell cell = row.getCell(index);
if (cell == null) {
return row.createCell(index);
}
return cell;
}
}
以上就是本文的全部内容