释放双眼，带上耳机，听听看~！
在实际工作中，有时候会需要将列表数据导出为Excel形式，方便打印，同事间传阅等，所以本章将添加将列表数据导出为Excel形式
一：添加POI依赖
2
3
4
5
6
2 <groupId>org.apache.poi</groupId>
3 <artifactId>poi-ooxml</artifactId>
4 <version>3.9</version>
5</dependency>
6
二：创建Excel实体类
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
2
3import java.io.Serializable;
4import java.util.List;
5
6public class ExcelData implements Serializable {
7
8 private static final long serialVersionUID = 6133772627258154184L;
9 /**
10 * 表头
11 */
12 private List<String> titles;
13
14 /**
15 * 数据
16 */
17 private List<List<Object>> rows;
18
19 /**
20 * 页签名称
21 */
22 private String name;
23
24 public List<String> getTitles() {
25 return titles;
26 }
27
28 public void setTitles(List<String> titles) {
29 this.titles = titles;
30 }
31
32 public List<List<Object>> getRows() {
33 return rows;
34 }
35
36 public void setRows(List<List<Object>> rows) {
37 this.rows = rows;
38 }
39
40 public String getName() {
41 return name;
42 }
43
44 public void setName(String name) {
45 this.name = name;
46 }
47}
48
三：创建表格工具类
创建core→utils→ExcelUtils
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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
2
3import com.example.demo.model.ExcelData;
4import org.apache.poi.ss.usermodel.*;
5import org.apache.poi.ss.usermodel.Font;
6import org.apache.poi.xssf.usermodel.XSSFCellStyle;
7import org.apache.poi.xssf.usermodel.XSSFColor;
8import org.apache.poi.xssf.usermodel.XSSFSheet;
9import org.apache.poi.xssf.usermodel.XSSFWorkbook;
10import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
11
12import javax.servlet.http.HttpServletResponse;
13import java.awt.Color;
14import java.io.File;
15import java.io.FileOutputStream;
16import java.io.OutputStream;
17import java.net.URLEncoder;
18import java.util.List;
19
20public class ExcelUtils {
21
22 /**
23 * 使用浏览器选择路径下载
24 * @param response
25 * @param fileName
26 * @param data
27 * @throws Exception
28 */
29 public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
30 // 告诉浏览器用什么软件可以打开此文件
31 response.setHeader("content-Type", "application/vnd.ms-excel");
32 // 下载文件的默认名称
33 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xls", "utf-8"));
34 exportExcel(data, response.getOutputStream());
35 }
36
37 public static int generateExcel(ExcelData excelData, String path) throws Exception {
38 File f = new File(path);
39 FileOutputStream out = new FileOutputStream(f);
40 return exportExcel(excelData, out);
41 }
42
43 private static int exportExcel(ExcelData data, OutputStream out) throws Exception {
44 XSSFWorkbook wb = new XSSFWorkbook();
45 int rowIndex = 0;
46 try {
47 String sheetName = data.getName();
48 if (null == sheetName) {
49 sheetName = "Sheet1";
50 }
51 XSSFSheet sheet = wb.createSheet(sheetName);
52 rowIndex = writeExcel(wb, sheet, data);
53 wb.write(out);
54 } catch (Exception e) {
55 e.printStackTrace();
56 } finally {
57 //此处需要关闭 wb 变量
58 out.close();
59 }
60 return rowIndex;
61 }
62
63 private static int writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
64 int rowIndex = 0;
65 writeTitlesToExcel(wb, sheet, data.getTitles());
66 rowIndex = writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
67 autoSizeColumns(sheet, data.getTitles().size() + 1);
68 return rowIndex;
69 }
70
71 /**
72 * 设置表头
73 *
74 * @param wb
75 * @param sheet
76 * @param titles
77 * @return
78 */
79 private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
80 int rowIndex = 0;
81 int colIndex = 0;
82 Font titleFont = wb.createFont();
83 //设置字体
84 titleFont.setFontName("simsun");
85 //设置粗体
86 titleFont.setBoldweight(Short.MAX_VALUE);
87 //设置字号
88 titleFont.setFontHeightInPoints((short) 14);
89 //设置颜色
90 titleFont.setColor(IndexedColors.BLACK.index);
91 XSSFCellStyle titleStyle = wb.createCellStyle();
92 //水平居中
93 titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
94 //垂直居中
95 titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
96 //设置图案颜色
97 titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
98 //设置图案样式
99 titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
100 titleStyle.setFont(titleFont);
101 setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
102 Row titleRow = sheet.createRow(rowIndex);
103 titleRow.setHeightInPoints(25);
104 colIndex = 0;
105 for (String field : titles) {
106 Cell cell = titleRow.createCell(colIndex);
107 cell.setCellValue(field);
108 cell.setCellStyle(titleStyle);
109 colIndex++;
110 }
111 rowIndex++;
112 return rowIndex;
113 }
114
115 /**
116 * 设置内容
117 *
118 * @param wb
119 * @param sheet
120 * @param rows
121 * @param rowIndex
122 * @return
123 */
124 private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
125 int colIndex;
126 Font dataFont = wb.createFont();
127 dataFont.setFontName("simsun");
128 dataFont.setFontHeightInPoints((short) 14);
129 dataFont.setColor(IndexedColors.BLACK.index);
130
131 XSSFCellStyle dataStyle = wb.createCellStyle();
132 dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
133 dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
134 dataStyle.setFont(dataFont);
135 setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
136 for (List<Object> rowData : rows) {
137 Row dataRow = sheet.createRow(rowIndex);
138 dataRow.setHeightInPoints(25);
139 colIndex = 0;
140 for (Object cellData : rowData) {
141 Cell cell = dataRow.createCell(colIndex);
142 if (cellData != null) {
143 cell.setCellValue(cellData.toString());
144 } else {
145 cell.setCellValue("");
146 }
147 cell.setCellStyle(dataStyle);
148 colIndex++;
149 }
150 rowIndex++;
151 }
152 return rowIndex;
153 }
154
155 /**
156 * 自动调整列宽
157 *
158 * @param sheet
159 * @param columnNumber
160 */
161 private static void autoSizeColumns(Sheet sheet, int columnNumber) {
162 for (int i = 0; i < columnNumber; i++) {
163 int orgWidth = sheet.getColumnWidth(i);
164 sheet.autoSizeColumn(i, true);
165 int newWidth = (int) (sheet.getColumnWidth(i) + 100);
166 if (newWidth > orgWidth) {
167 sheet.setColumnWidth(i, newWidth);
168 } else {
169 sheet.setColumnWidth(i, orgWidth);
170 }
171 }
172 }
173
174 /**
175 * 设置边框
176 *
177 * @param style
178 * @param border
179 * @param color
180 */
181 private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
182 style.setBorderTop(border);
183 style.setBorderLeft(border);
184 style.setBorderRight(border);
185 style.setBorderBottom(border);
186 style.setBorderColor(BorderSide.TOP, color);
187 style.setBorderColor(BorderSide.LEFT, color);
188 style.setBorderColor(BorderSide.RIGHT, color);
189 style.setBorderColor(BorderSide.BOTTOM, color);
190 }
191}
192
注意，这里封装了常用的属性，具体api大家可以自行百度
四：创建ExcelConstant
创建core→constant→ExcelConstant
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3public class ExcelConstant {
4
5 /**
6 * 生成文件存放路径
7 */
8 public static final String FILE_PATH = "C:\\Users\\Administrator\\Desktop\\";
9
10 /**
11 * 表格默认名称
12 */
13 public static final String FILE_NAME = "TEST.xls";
14}
15
五：创建ExcelController
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
2
3import com.example.demo.core.aop.AnnotationLog;
4import com.example.demo.core.constant.ExcelConstant;
5import com.example.demo.core.ret.RetResponse;
6import com.example.demo.core.ret.RetResult;
7import com.example.demo.core.ret.ServiceException;
8import com.example.demo.core.utils.ExcelUtils;
9import com.example.demo.model.ExcelData;
10import com.example.demo.model.UserInfo;
11import com.example.demo.service.UserInfoService;
12import com.github.pagehelper.PageHelper;
13import com.github.pagehelper.PageInfo;
14import io.swagger.annotations.Api;
15import io.swagger.annotations.ApiImplicitParam;
16import io.swagger.annotations.ApiImplicitParams;
17import io.swagger.annotations.ApiOperation;
18import org.apache.shiro.SecurityUtils;
19import org.apache.shiro.authc.IncorrectCredentialsException;
20import org.apache.shiro.authc.UsernamePasswordToken;
21import org.apache.shiro.subject.Subject;
22import org.springframework.web.bind.annotation.*;
23
24import javax.annotation.Resource;
25import javax.servlet.http.HttpServletResponse;
26import java.util.ArrayList;
27import java.util.List;
28
29@RestController
30@RequestMapping("excel")
31public class ExcelController {
32
33 @Resource
34 private UserInfoService userInfoService;
35
36 @PostMapping("/test")
37 public RetResult<Integer> test(){
38 int rowIndex = 0;
39 List<UserInfo> list = userInfoService.selectAlla(0, 0);
40 ExcelData data = new ExcelData();
41 data.setName("hello");
42 List<String> titles = new ArrayList();
43 titles.add("ID");
44 titles.add("userName");
45 titles.add("password");
46 data.setTitles(titles);
47
48 List<List<Object>> rows = new ArrayList();
49 for(int i = 0, length = list.size();i<length;i++){
50 UserInfo userInfo = list.get(i);
51 List<Object> row = new ArrayList();
52 row.add(userInfo.getId());
53 row.add(userInfo.getUserName());
54 row.add(userInfo.getPassword());
55 rows.add(row);
56 }
57 data.setRows(rows);
58 try{
59 rowIndex = ExcelUtils.generateExcel(data, ExcelConstant.FILE_PATH + ExcelConstant.FILE_NAME);
60 }catch (Exception e){
61 e.printStackTrace();
62 }
63 return RetResponse.makeOKRsp(Integer.valueOf(rowIndex));
64 }
65
66 @GetMapping("/test2")
67 public void test2(HttpServletResponse response){
68 int rowIndex = 0;
69 List<UserInfo> list = userInfoService.selectAlla(0, 0);
70 ExcelData data = new ExcelData();
71 data.setName("hello");
72 List<String> titles = new ArrayList();
73 titles.add("ID");
74 titles.add("userName");
75 titles.add("password");
76 data.setTitles(titles);
77
78 List<List<Object>> rows = new ArrayList();
79 for(int i = 0, length = list.size();i<length;i++){
80 UserInfo userInfo = list.get(i);
81 List<Object> row = new ArrayList();
82 row.add(userInfo.getId());
83 row.add(userInfo.getUserName());
84 row.add(userInfo.getPassword());
85 rows.add(row);
86 }
87 data.setRows(rows);
88 try{
89 ExcelUtils.exportExcel(response,"test2",data);
90 }catch (Exception e){
91 e.printStackTrace();
92 }
93 }
94}
95
96
六：添加接口权限
2
3
2INSERT INTO `sys_permission_init` VALUES ('8', '/excel/test2', 'anon', '8');
3
这里我们需要测试在浏览器中下载，所以我们先把接口设置为不登录也可以访问
七：测试
输入localhost:8080/excel/test
打开桌面的TEST.xls
在浏览器中输入localhost:8080/excel/test2
出现如图所示页面，打开文件，数据与TEST.xls一致
