Springboot下载Excel的3种方式
汇总一下浏览器下载和代码本地下载实现的3种方式。
（其实一般都是在代码生成excel，然后上传到oss，然后传链接给前台，但是我好像没有实现过直接点击就能在浏览器下载的功能，所以这次一起汇总一下3种实现方式。）
文章目录
🔥1.EasyExcel–浏览器下载
1.Maven环境
网络上有很多maven的easyexcel版本，还是推荐alibaba的easyexcel，操作简单，代码不冗余
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
2.完整代码实现
-
控制层：设置response格式然后直接下载即可
package com.empirefree.springboot.controller; import com.alibaba.excel.EasyExcel; import com.empirefree.springboot.pojo.User; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; /** * @program: springboot * @description: * @author: huyuqiao * @create: 2021/07/04 15:01 */ @RestController public class UserController { /** * Author: HuYuQiao * Description: 浏览器下载--excel */ @GetMapping("/testRespExcel") public void testRespExcel(HttpServletResponse response){ response.addHeader("Content-Disposition", "attachment;filename=" + "huyuqiao.xlsx"); response.setContentType("application/vnd.ms-excel;charset=gb2312"); try { // 从HttpServletResponse中获取OutputStream输出流 ServletOutputStream outputStream = response.getOutputStream(); /* * EasyExcel 有多个不同的read方法，适用于多种需求 * 这里调用EasyExcel中通过OutputStream流方式输出Excel的write方法 * 它会返回一个ExcelWriterBuilder类型的返回值 * ExcelWriterBuilde中有一个doWrite方法，会输出数据到设置的Sheet中 */ EasyExcel.write(outputStream, User.class).sheet("测试数据").doWrite(getAllUser()); } catch (IOException e) { e.printStackTrace(); } } public List<User> getAllUser(){ List<User> userList = new ArrayList<>(); for (int i=0;i<100;i++){ User user = User.builder().name("胡宇乔"+ i).password("huyuqiao").age(i).build(); userList.add(user); } return userList; } } -
实体类：给User设置对应的excel属性即可,value代表excel中名字，index代表第几列
package com.empirefree.springboot.pojo; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.metadata.BaseRowModel; import lombok.Builder; import lombok.Data; /** * @program: springboot * @description: user * @author: huyuqiao * @create: 2021/07/04 14:53 */ @Data @Builder public class User extends BaseRowModel{ @ExcelProperty(value = "姓名",index = 0) private String name; @ExcelProperty(value = "密码",index = 1) private String password; @ExcelProperty(value = "年龄",index = 2) private Integer age; }
3.实现效果
🔥2.EasyExcel–本地下载
1.完整代码实现
maven和上面一样，只是文件输出流设置一下即可
package com.empirefree.springboot.controller;
import com.alibaba.excel.EasyExcel;
import com.empirefree.springboot.pojo.User;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
/**
* @program: springboot
* @description:
* @author: huyuqiao
* @create: 2021/07/04 15:01
*/
@RestController
public class UserController {
/**
* Author: HuYuQiao
* Description:本地生成--excel
*/
@GetMapping("/testLocalExcel")
public void testLocalExcel(){
// 文件输出位置
OutputStream out = null;
try {
out = new FileOutputStream("C:\\Users\\EDY\\Desktop\\empirefree.xlsx");
EasyExcel.write(out, User.class).sheet("测试数据").doWrite(getAllUser());
} catch (FileNotFoundException e) {
e.printStackTrace();
}finally {
try {
// 关闭流
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
public List<User> getAllUser(){
List<User> userList = new ArrayList<>();
for (int i=0;i<100;i++){
User user = User.builder().name("张三"+ i).password("1234").age(i).build();
userList.add(user);
}
return userList;
}
}
2.实现效果
🔥3.Poi–浏览器实现下载
1.Maven环境
<!-- excel导出工具 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>RELEASE</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>RELEASE</version>
</dependency>
2.代码实现
- 控制层
/**
* Author: HuYuQiao
* Description: excle-export
*/
@GetMapping("/export")
public String exportExcel(HttpServletResponse response) {
System.out.println("成功到达到处excel....");
String fileName = "test.xls";
if (fileName == null || "".equals(fileName)) {
return "文件名不能为空！";
} else {
if (fileName.endsWith("xls")) {
Boolean isOk = excelService.exportExcel(response, fileName, 1, 10);
if (isOk) {
return "导出成功！";
} else {
return "导出失败！";
}
}
return "文件格式有误！";
}
}
-
serviceimpl层
/** * Author: HuYuQiao * Description: excel-impl */ @Override public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) { log.info("导出数据开始。。。。。。"); //查询数据并赋值给ExcelData List<User> userList = userMapper.find(); System.out.println(userList.size() + "size"); List<String[]> list = new ArrayList<String[]>(); for (User user : userList) { String[] arrs = new String[4]; arrs[0] = String.valueOf(user.getId()); arrs[1] = user.getUsername(); arrs[2] = user.getPassword(); arrs[3] = String.valueOf(user.getEnable()); list.add(arrs); } //表头赋值 String[] head = {"序列", "用户名", "密码", "状态"}; ExcelData data = new ExcelData(); data.setHead(head); data.setData(list); data.setFileName(fileName); //实现导出 try { ExcelUtil.exportExcel(response, data); log.info("导出数据结束。。。。。。"); return true; } catch (Exception e) { log.info("导出数据失败。。。。。。"); return false; } } -
工具类
package com.example.demo.utils; import com.example.demo.entity.ExcelData; import com.example.demo.entity.User; import lombok.extern.slf4j.Slf4j; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.ss.usermodel.*; import javax.servlet.http.HttpServletResponse; import java.io.BufferedOutputStream; import java.io.FileInputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import static org.apache.poi.ss.usermodel.CellType.*; /** * Author: HuYuQiao * Description: excelUtil */ @Slf4j public class ExcelUtil { /** * Author: HuYuQiao * Description: excelUtil-export */ public static void exportExcel(HttpServletResponse response, ExcelData data) { log.info("导出解析开始，fileName:{}",data.getFileName()); try { //实例化HSSFWorkbook HSSFWorkbook workbook = new HSSFWorkbook(); //创建一个Excel表单，参数为sheet的名字 HSSFSheet sheet = workbook.createSheet("sheet"); //设置表头 setTitle(workbook, sheet, data.getHead()); //设置单元格并赋值 setData(sheet, data.getData()); //设置浏览器下载 setBrowser(response, workbook, data.getFileName()); log.info("导出解析成功!"); } catch (Exception e) { log.info("导出解析失败!"); e.printStackTrace(); } } /** * Author: HuYuQiao * Description: excelUtil-settitle */ private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) { try { HSSFRow row = sheet.createRow(0); //设置列宽，setColumnWidth的第二个参数要乘以256，这个参数的单位是1/256个字符宽度 for (int i = 0; i <= str.length; i++) { sheet.setColumnWidth(i, 15 * 256); } //设置为居中加粗,格式化时间格式 HSSFCellStyle style = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setBold(true); style.setFont(font); style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); //创建表头名称 HSSFCell cell; for (int j = 0; j < str.length; j++) { cell = row.createCell(j); cell.setCellValue(str[j]); cell.setCellStyle(style); } } catch (Exception e) { log.info("导出时设置表头失败！"); e.printStackTrace(); } } /** * Author: HuYuQiao * Description: excelUtil-setData */ private static void setData(HSSFSheet sheet, List<String[]> data) { try{ int rowNum = 1; for (int i = 0; i < data.size(); i++) { HSSFRow row = sheet.createRow(rowNum); for (int j = 0; j < data.get(i).length; j++) { row.createCell(j).setCellValue(data.get(i)[j]); } rowNum++; } log.info("表格赋值成功！"); }catch (Exception e){ log.info("表格赋值失败！"); e.printStackTrace(); } } /** * Author: HuYuQiao * Description: excelUtil-setBrowser */ private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) { try { //清空response response.reset(); //设置response的Header response.addHeader("Content-Disposition", "attachment;filename=" + fileName); OutputStream os = new BufferedOutputStream(response.getOutputStream()); response.setContentType("application/vnd.ms-excel;charset=gb2312"); //将excel写入到输出流中 workbook.write(os); os.flush(); os.close(); log.info("设置浏览器下载成功！"); } catch (Exception e) { log.info("设置浏览器下载失败！"); e.printStackTrace(); } } /** * Author: HuYuQiao * Description: excelUtil--importExcel */ public static List<Object[]> importExcel(String fileName) { log.info("导入解析开始，fileName:{}",fileName); try { List<Object[]> list = new ArrayList<>(); InputStream inputStream = new FileInputStream(fileName); Workbook workbook = WorkbookFactory.create(inputStream); Sheet sheet = workbook.getSheetAt(0); //获取sheet的行数 int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < rows; i++) { //过滤表头行 if (i == 0) { continue; } //获取当前行的数据 Row row = sheet.getRow(i); Object[] objects = new Object[row.getPhysicalNumberOfCells()]; int index = 0; for (Cell cell : row) { if (cell.getCellType().equals(NUMERIC)) { objects[index] = (int) cell.getNumericCellValue(); } if (cell.getCellType().equals(STRING)) { objects[index] = cell.getStringCellValue(); } if (cell.getCellType().equals(BOOLEAN)) { objects[index] = cell.getBooleanCellValue(); } if (cell.getCellType().equals(ERROR)) { objects[index] = cell.getErrorCellValue(); } index++; } list.add(objects); } log.info("导入文件解析成功！"); return list; }catch (Exception e){ log.info("导入文件解析失败！"); e.printStackTrace(); } return null; } //测试导入 public static void main(String[] args) { try { String fileName = "E:/test.xlsx"; List<Object[]> list = importExcel(fileName); for (int i = 0; i < list.size(); i++) { User user = new User(); user.setId((Integer) list.get(i)[0]); user.setUsername((String) list.get(i)[1]); user.setPassword((String) list.get(i)[2]); user.setEnable((Integer) list.get(i)[3]); System.out.println(user.toString()); } } catch (Exception e) { e.printStackTrace(); } } }
3.实现效果
🔥4.总结
总体看来：当excel需要在浏览器下载时，使用alibaba的easyexcel最快最方便，并且注意需要设置response格式
3710
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
