详解Springboot下载Excel的三种⽅式
汇总⼀下浏览器下载和代码本地下载实现的3种⽅式。
（其实⼀般都是在代码⽣成excel，然后上传到oss，然后传链接给前台，但是我好像没有实现过直接点击就能在浏览器下载的功能，所以这次⼀起汇总⼀下3种实现⽅式。） 1.EasyExcel--浏览器下载
1.Maven环境
⽹络上有很多maven的easyexcel版本，还是推荐alibaba的easyexcel，操作简单，代码不冗余
<!-- mvnrepository/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
2.完整代码实现
控制层：设置response格式然后直接下载即可
ller;
import l.EasyExcel;
pirefree.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
springboot原理图解
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 = OutputStream();
/*
* 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代表第⼏列
pirefree.springboot.pojo;
import l.annotation.ExcelProperty;
import l.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.完整代码实现
m aven和上⾯⼀样，只是⽂件输出流设置⼀下即可
ller;
import l.EasyExcel;
pirefree.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("成功到达到处");
String fileName = "test.xls";
if (fileName == null || "".equals(fileName)) {
return "⽂件名不能为空！";
} else {
if (dsWith("xls")) {
Boolean isOk = portExcel(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.Id());
arrs[1] = Username();
arrs[2] = Password();
arrs[3] = String.Enable());
list.add(arrs);
}
//表头赋值
String[] head = {"序列", "⽤户名", "密码", "状态"};
ExcelData data = new ExcelData();
data.setHead(head);
data.setData(list);
data.setFileName(fileName);
//实现导出
try {
log.info("导出数据结束。。。。。。");
return true;
} catch (Exception e) {
log.info("导出数据失败。。。。。。");
return false;
}
}
⼯具类
ample.demo.utils;
ity.ExcelData;
ity.User;
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:{}",FileName());
try {
//实例化HSSFWorkbook
HSSFWorkbook workbook = new HSSFWorkbook();
//创建⼀个Excel表单，参数为sheet的名字
HSSFSheet sheet = ateSheet("sheet");
//设置表头
setTitle(workbook, sheet, Head());
//设置单元格并赋值
setData(sheet, Data());
//设置浏览器下载
setBrowser(response, workbook, FileName());
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 = ateRow(0);
/
/设置列宽，setColumnWidth的第⼆个参数要乘以256，这个参数的单位是1/256个字符宽度
for (int i = 0; i <= str.length; i++) {
sheet.setColumnWidth(i, 15 * 256);
}
//设置为居中加粗，格式化时间格式
HSSFCellStyle style = ateCellStyle();
HSSFFont font = ateFont();
font.setBold(true);
style.setFont(font);
style.BuiltinFormat("m/d/yy h:mm"));
//创建表头名称
HSSFCell cell;
for (int j = 0; j < str.length; j++) {
cell = ateCell(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 = ateRow(rowNum);
for (int j = 0; j < (i).length; 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的Header
response.addHeader("Content-Disposition", "attachment;filename=" + fileName);
OutputStream os = new OutputStream());
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 = ate(inputStream);
Sheet sheet = SheetAt(0);
//获取sheet的⾏数
int rows = PhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
/
/过滤表头⾏
if (i == 0) {
continue;
}
//获取当前⾏的数据
Row row = Row(i);
Object[] objects = new PhysicalNumberOfCells()];                int index = 0;
for (Cell cell : row) {
if (CellType().equals(NUMERIC)) {
objects[index] = (int) NumericCellValue();
}
if (CellType().equals(STRING)) {
objects[index] = StringCellValue();
}
if (CellType().equals(BOOLEAN)) {
objects[index] = BooleanCellValue();
}
if (CellType().equals(ERROR)) {
objects[index] = ErrorCellValue();
}
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) (i)[0]);
user.setUsername((String) (i)[1]);
user.setPassword((String) (i)[2]);
user.setEnable((Integer) (i)[3]);
System.out.String());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
3.实现效果

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。