溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下Springboot如何下載Excel，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
網(wǎng)絡上有很多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.完整代碼實現(xiàn)
控制層：設置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方法，會輸出數(shù)據(jù)到設置的Sheet中
*/
EasyExcel.write(outputStream, User.class).sheet("測試數(shù)據(jù)").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;
}
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("測試數(shù)據(jù)").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;
}
}
<!-- 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>
控制層
/**
* 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("導出數(shù)據(jù)開始。。。。。。");
//查詢數(shù)據(jù)并賦值給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 = {"序列", "用戶名", "密碼", "狀態(tài)"};
ExcelData data = new ExcelData();
data.setHead(head);
data.setData(list);
data.setFileName(fileName);
//實現(xiàn)導出
try {
ExcelUtil.exportExcel(response, data);
log.info("導出數(shù)據(jù)結束。。。。。。");
return true;
} catch (Exception e) {
log.info("導出數(shù)據(jù)失敗。。。。。。");
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();
//創(chuàng)建一個Excel表單，參數(shù)為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的第二個參數(shù)要乘以256，這個參數(shù)的單位是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"));
//創(chuàng)建表頭名稱
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("表格賦值失?。?quot;);
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的行數(shù)
int rows = sheet.getPhysicalNumberOfRows();
for (int i = 0; i < rows; i++) {
//過濾表頭行
if (i == 0) {
continue;
}
//獲取當前行的數(shù)據(jù)
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();
}
}
}
以上是“Springboot如何下載Excel”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業(yè)資訊頻道！
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權內(nèi)容。
