Springboot如何下载Excel
短信预约 -IT技能 免费直播动态提醒
小编给大家分享一下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>完整代码实现
控制层：设置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;@RestControllerpublic class UserController { @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.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;@Data@Builderpublic 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;@RestControllerpublic class UserController { @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.代码实现
控制层
@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层
@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.*;@Slf4jpublic class ExcelUtil { 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(); } } 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(); } } 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(); } } 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(); } } 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”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注编程网行业资讯频道！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
