详解Springboot下载Excel的三种方式

https://www.jb51.net/article/216656.htm

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

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

🔥1.EasyExcel–浏览器下载
1.Maven环境
​网络上有很多maven的easyexcel版本，还是推荐alibaba的easyexcel，操作简单，代码不冗余

1
2
3
5
6

  <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.2.10</version>
  </dependency>

2.完整代码实现

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

1
2
3
57
58
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代表第几列

1
2
25
26
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和上面一样，只是文件输出流设置一下即可

1
2
56
57
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.实现效果
Java技术迷

🔥3.Poi–浏览器实现下载
1.Maven环境
1
2
3
9
10
11

    <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.代码实现
控制层

1
2
3
21
22
/**

• 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层

1
2

34
35
/**
* Author: HuYuQiao
* Description: excel-impl
*/
@Override
public Boolean exportExcel(HttpServletResponse response, String fileName, Integer pageNum, Integer pageSize) {
log.info(“导出数据开始。。。。。。”);
//查询数据并赋值给ExcelData
List 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格式