详解Springboot下载Excel的三种方式

汇总一下浏览器下载和代码本地下载实现的3种方式。

（其实一般都是在代码生成excel，然后上传到oss，然后传链接给前台，但是我好像没有实现过直接点击就能在浏览器下载的功能，所以这次一起汇总一下3种实现方式。）

1.EasyExcel--浏览器下载

1.Maven环境

​网络上有很多maven的easyexcel版本，还是推荐alibaba的easyexcel，操作简单，代码不冗余

1. <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
2. <dependency>
3. <groupId>com.alibaba</groupId>
4. <artifactId>easyexcel</artifactId>
5. <version>2.2.10</version>
6. </dependency>

2.完整代码实现

控制层：设置response格式然后直接下载即可

1. package com.empirefree.springboot.controller;
2.  
3. import com.alibaba.excel.EasyExcel;
4. import com.empirefree.springboot.pojo.User;
5. import org.springframework.web.bind.annotation.GetMapping;
6. import org.springframework.web.bind.annotation.RestController;
7.  
8. import javax.servlet.ServletOutputStream;
9. import javax.servlet.http.HttpServletResponse;
10. import java.io.FileNotFoundException;
11. import java.io.FileOutputStream;
12. import java.io.IOException;
13. import java.io.OutputStream;
14. import java.util.ArrayList;
15. import java.util.List;
16.  
17. /**
18. * @program: springboot
19. * @description:
20. * @author: huyuqiao
21. * @create: 2021/07/04 15:01
22. */
23.  
24. @RestController
25. public class UserController {
26.  
27. /**
28. * Author: HuYuQiao
29. * Description: 浏览器下载--excel
30. */
31. @GetMapping("/testRespExcel")
32. public void testRespExcel(HttpServletResponse response){
33. response.addHeader("Content-Disposition", "attachment;filename=" + "huyuqiao.xlsx");
34. response.setContentType("application/vnd.ms-excel;charset=gb2312");
35. try {
36. // 从HttpServletResponse中获取OutputStream输出流
37. ServletOutputStream outputStream = response.getOutputStream();
38. /*
39. * EasyExcel 有多个不同的read方法，适用于多种需求
40. * 这里调用EasyExcel中通过OutputStream流方式输出Excel的write方法
41. * 它会返回一个ExcelWriterBuilder类型的返回值
42. * ExcelWriterBuilde中有一个doWrite方法，会输出数据到设置的Sheet中
43. */
44. EasyExcel.write(outputStream, User.class).sheet("测试数据").doWrite(getAllUser());
45. } catch (IOException e) {
46. e.printStackTrace();
47. }
48. }
49.  
50. public List<User> getAllUser(){
51. List<User> userList = new ArrayList<>();
52. for (int i=0;i<100;i++){
53. User user = User.builder().name("胡宇乔"+ i).password("huyuqiao").age(i).build();
54. userList.add(user);
55. }
56. return userList;
57. }
58. }

实体类：给User设置对应的excel属性即可,value代表excel中名字，index代表第几列

1. package com.empirefree.springboot.pojo;
2.  
3. import com.alibaba.excel.annotation.ExcelProperty;
4. import com.alibaba.excel.metadata.BaseRowModel;
5. import lombok.Builder;
6. import lombok.Data;
7.  
8. /**
9. * @program: springboot
10. * @description: user
11. * @author: huyuqiao
12. * @create: 2021/07/04 14:53
13. */
14. @Data
15. @Builder
16. public class User extends BaseRowModel{
17.  
18. @ExcelProperty(value = "姓名",index = 0)
19. private String name;
20.  
21. @ExcelProperty(value = "密码",index = 1)
22. private String password;
23.  
24. @ExcelProperty(value = "年龄",index = 2)
25. private Integer age;
26. }

3.实现效果

2.EasyExcel--本地下载

1.完整代码实现

​maven和上面一样，只是文件输出流设置一下即可

1. package com.empirefree.springboot.controller;
2.  
3. import com.alibaba.excel.EasyExcel;
4. import com.empirefree.springboot.pojo.User;
5. import org.springframework.web.bind.annotation.GetMapping;
6. import org.springframework.web.bind.annotation.RestController;
7.  
8. import javax.servlet.ServletOutputStream;
9. import javax.servlet.http.HttpServletResponse;
10. import java.io.FileNotFoundException;
11. import java.io.FileOutputStream;
12. import java.io.IOException;
13. import java.io.OutputStream;
14. import java.util.ArrayList;
15. import java.util.List;
16.  
17. /**
18. * @program: springboot
19. * @description:
20. * @author: huyuqiao
21. * @create: 2021/07/04 15:01
22. */
23.  
24. @RestController
25. public class UserController {
26. /**
27. * Author: HuYuQiao
28. * Description:本地生成--excel
29. */
30. @GetMapping("/testLocalExcel")
31. public void testLocalExcel(){
32. // 文件输出位置
33. OutputStream out = null;
34. try {
35. out = new FileOutputStream("C:\\Users\\EDY\\Desktop\\empirefree.xlsx");
36. EasyExcel.write(out, User.class).sheet("测试数据").doWrite(getAllUser());
37. } catch (FileNotFoundException e) {
38. e.printStackTrace();
39. }finally {
40. try {
41. // 关闭流
42. out.close();
43. } catch (IOException e) {
44. e.printStackTrace();
45. }
46. }
47.  
48. }
49. public List<User> getAllUser(){
50. List<User> userList = new ArrayList<>();
51. for (int i=0;i<100;i++){
52. User user = User.builder().name("张三"+ i).password("1234").age(i).build();
53. userList.add(user);
54. }
55. return userList;
56. }
57. }

2.实现效果

3.Poi--浏览器实现下载

1.Maven环境

1. <!-- excel导出工具 -->
2. <dependency>
3. <groupId>org.apache.poi</groupId>
4. <artifactId>poi</artifactId>
5. <version>RELEASE</version>
6. </dependency>
7. <dependency>
8. <groupId>org.apache.poi</groupId>
9. <artifactId>poi-ooxml</artifactId>
10. <version>RELEASE</version>
11. </dependency>

2.代码实现

控制层

1. /**
2. * Author: HuYuQiao
3. * Description: excle-export
4. */
5. @GetMapping("/export")
6. public String exportExcel(HttpServletResponse response) {
7. System.out.println("成功到达到处excel....");
8. String fileName = "test.xls";
9. if (fileName == null || "".equals(fileName)) {
10. return "文件名不能为空！";
11. } else {
12. if (fileName.endsWith("xls")) {
13. Boolean isOk = excelService.exportExcel(response, fileName, 1, 10);
14. if (isOk) {
15. return "导出成功！";
16. } else {
17. return "导出失败！";
18. }
19. }
20. return "文件格式有误！";
21. }
22. }

serviceimpl层

1. /**
2. * Author: HuYuQiao
3. * Description: excel-impl
4. */
5. @Override
6. public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
7. log.info("导出数据开始。。。。。。");
8. //查询数据并赋值给ExcelData
9. List<User> userList = userMapper.find();
10. System.out.println(userList.size() + "size");
11. List<String[]> list = new ArrayList<String[]>();
12. for (User user : userList) {
13. String[] arrs = new String[4];
14. arrs[0] = String.valueOf(user.getId());
15. arrs[1] = user.getUsername();
16. arrs[2] = user.getPassword();
17. arrs[3] = String.valueOf(user.getEnable());
18. list.add(arrs);
19. }
20. //表头赋值
21. String[] head = {"序列", "用户名", "密码", "状态"};
22. ExcelData data = new ExcelData();
23. data.setHead(head);
24. data.setData(list);
25. data.setFileName(fileName);
26. //实现导出
27. try {
28. ExcelUtil.exportExcel(response, data);
29. log.info("导出数据结束。。。。。。");
30. return true;
31. } catch (Exception e) {
32. log.info("导出数据失败。。。。。。");
33. return false;
34. }
35. }

工具类

1. package com.example.demo.utils;
2.  
3. import com.example.demo.entity.ExcelData;
4. import com.example.demo.entity.User;
5. import lombok.extern.slf4j.Slf4j;
6. import org.apache.poi.hssf.usermodel.*;
7. import org.apache.poi.ss.usermodel.*;
8.  
9. import javax.servlet.http.HttpServletResponse;
10. import java.io.BufferedOutputStream;
11. import java.io.FileInputStream;
12. import java.io.InputStream;
13. import java.io.OutputStream;
14. import java.util.ArrayList;
15. import java.util.List;
16.  
17. import static org.apache.poi.ss.usermodel.CellType.*;
18.  
19. /**
20. * Author: HuYuQiao
21. * Description: excelUtil
22. */
23. @Slf4j
24. public class ExcelUtil {
25.  
26. /**
27. * Author: HuYuQiao
28. * Description: excelUtil-export
29. */
30. public static void exportExcel(HttpServletResponse response, ExcelData data) {
31. log.info("导出解析开始，fileName:{}",data.getFileName());
32. try {
33. //实例化HSSFWorkbook
34. HSSFWorkbook workbook = new HSSFWorkbook();
35. //创建一个Excel表单，参数为sheet的名字
36. HSSFSheet sheet = workbook.createSheet("sheet");
37. //设置表头
38. setTitle(workbook, sheet, data.getHead());
39. //设置单元格并赋值
40. setData(sheet, data.getData());
41. //设置浏览器下载
42. setBrowser(response, workbook, data.getFileName());
43. log.info("导出解析成功!");
44. } catch (Exception e) {
45. log.info("导出解析失败!");
46. e.printStackTrace();
47. }
48. }
49.  
50. /**
51. * Author: HuYuQiao
52. * Description: excelUtil-settitle
53. */
54. private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
55. try {
56. HSSFRow row = sheet.createRow(0);
57. //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度
58. for (int i = 0; i <= str.length; i++) {
59. sheet.setColumnWidth(i, 15 * 256);
60. }
61. //设置为居中加粗,格式化时间格式
62. HSSFCellStyle style = workbook.createCellStyle();
63. HSSFFont font = workbook.createFont();
64. font.setBold(true);
65. style.setFont(font);
66. style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
67. //创建表头名称
68. HSSFCell cell;
69. for (int j = 0; j < str.length; j++) {
70. cell = row.createCell(j);
71. cell.setCellValue(str[j]);
72. cell.setCellStyle(style);
73. }
74. } catch (Exception e) {
75. log.info("导出时设置表头失败！");
76. e.printStackTrace();
77. }
78. }
79.  
80. /**
81. * Author: HuYuQiao
82. * Description: excelUtil-setData
83. */
84. private static void setData(HSSFSheet sheet, List<String[]> data) {
85. try{
86. int rowNum = 1;
87. for (int i = 0; i < data.size(); i++) {
88. HSSFRow row = sheet.createRow(rowNum);
89. for (int j = 0; j < data.get(i).length; j++) {
90. row.createCell(j).setCellValue(data.get(i)[j]);
91. }
92. rowNum++;
93. }
94. log.info("表格赋值成功！");
95. }catch (Exception e){
96. log.info("表格赋值失败！");
97. e.printStackTrace();
98. }
99. }
100.  
101. /**
102. * Author: HuYuQiao
103. * Description: excelUtil-setBrowser
104. */
105. private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
106. try {
107. //清空response
108. response.reset();
109. //设置response的Header
110. response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
111. OutputStream os = new BufferedOutputStream(response.getOutputStream());
112. response.setContentType("application/vnd.ms-excel;charset=gb2312");
113. //将excel写入到输出流中
114. workbook.write(os);
115. os.flush();
116. os.close();
117. log.info("设置浏览器下载成功！");
118. } catch (Exception e) {
119. log.info("设置浏览器下载失败！");
120. e.printStackTrace();
121. }
122.  
123. }
124.  
125. /**
126. * Author: HuYuQiao
127. * Description: excelUtil--importExcel
128. */
129. public static List<Object[]> importExcel(String fileName) {
130. log.info("导入解析开始，fileName:{}",fileName);
131. try {
132. List<Object[]> list = new ArrayList<>();
133. InputStream inputStream = new FileInputStream(fileName);
134. Workbook workbook = WorkbookFactory.create(inputStream);
135. Sheet sheet = workbook.getSheetAt(0);
136. //获取sheet的行数
137. int rows = sheet.getPhysicalNumberOfRows();
138. for (int i = 0; i < rows; i++) {
139. //过滤表头行
140. if (i == 0) {
141. continue;
142. }
143. //获取当前行的数据
144. Row row = sheet.getRow(i);
145. Object[] objects = new Object[row.getPhysicalNumberOfCells()];
146. int index = 0;
147. for (Cell cell : row) {
148. if (cell.getCellType().equals(NUMERIC)) {
149. objects[index] = (int) cell.getNumericCellValue();
150. }
151. if (cell.getCellType().equals(STRING)) {
152. objects[index] = cell.getStringCellValue();
153. }
154. if (cell.getCellType().equals(BOOLEAN)) {
155. objects[index] = cell.getBooleanCellValue();
156. }
157. if (cell.getCellType().equals(ERROR)) {
158. objects[index] = cell.getErrorCellValue();
159. }
160. index++;
161. }
162. list.add(objects);
163. }
164. log.info("导入文件解析成功！");
165. return list;
166. }catch (Exception e){
167. log.info("导入文件解析失败！");
168. e.printStackTrace();
169. }
170. return null;
171. }
172.  
173. //测试导入
174. public static void main(String[] args) {
175. try {
176. String fileName = "E:/test.xlsx";
177. List<Object[]> list = importExcel(fileName);
178. for (int i = 0; i < list.size(); i++) {
179. User user = new User();
180. user.setId((Integer) list.get(i)[0]);
181. user.setUsername((String) list.get(i)[1]);
182. user.setPassword((String) list.get(i)[2]);
183. user.setEnable((Integer) list.get(i)[3]);
184. System.out.println(user.toString());
185. }
186. } catch (Exception e) {
187. e.printStackTrace();
188. }
189. }
190.  
191. }

3.实现效果

4.总结

总体看来：当excel需要在浏览器下载时，使用alibaba的easyexcel最快最方便，并且注意需要设置response格式

到此这篇关于详解Springboot下载Excel的三种方式的文章就介绍到这了,更多相关Springboot下载Excel内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/meditation5201314/p/14969210.html