SpringBoot实现excel文件生成和下载

约定不等于承诺〃 2022-10-29 07:51 378阅读 0赞

使用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;  
\}  
\}  
以上就是本文的全部内容