Springboot如何下载Excel

小编给大家分享一下Springboot如何下载Excel，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

?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.实现效果

以上是“Springboot如何下载Excel”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！