匯總一下瀏覽器下載和代碼本地下載實現的3種方式。

（其實一般都是在代碼生成excel，然后上傳到oss，然后傳鏈接給前臺，但是我好像沒有實現過直接點擊就能在瀏覽器下載的功能，所以這次一起匯總一下3種實現方式。）

​網絡上有很多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 */@RestControllerpublic 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@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.實現效果

​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 */@RestControllerpublic class UserController { /** * Author: HuYuQiao * Description:本地生成--excel */ @GetMapping('/testLocalExcel') public void testLocalExcel(){// 文件輸出位置OutputStream out = null;try { out = new FileOutputStream('C:UsersEDYDesktopempirefree.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.實現效果

<!-- 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('導出數據開始。。。。。。');//查詢數據并賦值給ExcelDataList<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 */@Slf4jpublic 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.實現效果

總體看來：當excel需要在瀏覽器下載時，使用alibaba的easyexcel最快最方便，并且注意需要設置response格式

到此這篇關于詳解Springboot下載Excel的三種方式的文章就介紹到這了,更多相關Springboot下載Excel內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！