Spring Boot项目基于POI框架导出Excel表格
生活随笔
收集整理的這篇文章主要介紹了
Spring Boot项目基于POI框架导出Excel表格
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
1. 依賴
我的項目是基于Spring Boot的，這里只貼出POI框架需要依賴的兩個包，其他的都無所謂，只要能提供Controller讓瀏覽器訪問即可。在pom.xml配置文件中增加如下兩個包：
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2. 代碼實現
package com.eg.wiener.controller; import com.eg.wiener.dto.User;
import com.eg.wiener.utils.ExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController; import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.stream.Stream; /**
* @author Wiener
* @date 2020/9/5
*/
@Slf4j
@RestController
@RequestMapping("/excel")
public class ExcelExportController { private static HSSFWorkbook workbook = null;
// 代表一行記錄
private static HSSFCell cell = null;
private static HSSFRow row = null; @RequestMapping(value = "/exportExcel")
public Object exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
String fileSuffix = ".xls";
String fileName = "導出數據至Excel示例"+ "_" + (int) (Math.random() * 9000 + 1000) + ".xls"; //【1】創(chuàng)建excel
workbook = new HSSFWorkbook();
// 設置表頭的類型
HSSFCellStyle style = setCellStyle(workbook); // 創(chuàng)建一個sheet
HSSFSheet sheet = workbook.createSheet("sheet1"); try {
List<User> dataList = getUsers(); if (CollectionUtils.isNotEmpty(dataList)) {
//【2】 設置表頭：對Excel每列命名
String[] tableHeader = Stream.of("編號", "姓名", "年齡").toArray(String[]::new);
// 設置Excel表頭
row = ExcelUtils.getExcelRow(workbook, sheet, tableHeader);
//通過反射，獲取POJO對象。由于同一個dataList中，POJO相同，故定義在for循環(huán)外部，提高遍歷性能
Class cl = dataList.get(0).getClass();
//獲取類的所有字段
Field[] fields = cl.getDeclaredFields();
//寫入每一行的記錄
for (int i = 0; i < dataList.size(); i++) {
//創(chuàng)建新的一行，遞增
row = sheet.createRow(i + 1);
// 設置行高
row.setHeight((short) 400);
for (int j = 0; j < fields.length; j++) {
//設置字段可見，否則會報錯，禁止訪問
fields[j].setAccessible(true);
//創(chuàng)建單元格
cell = row.createCell(j);
cell.setCellValue(Objects.toString(fields[j].get(dataList.get(i)), ""));
cell.setCellStyle(style);
}
}
} else {
// 沒有查到數據提示行
row = ExcelUtils.getNotResultInfoRow(workbook, sheet, "記錄為空");
}
} catch (Exception e) {
log.error("導出數據失?。?, e);
// 寫入Excel失敗原因
row = ExcelUtils.getNotResultInfoRow(workbook, sheet, "導出數據失敗");
fileName = "導出數據失敗";
}
fileName = fileName + "_" + (int) (Math.random() * 9000 + 1000) + fileSuffix; //下載文件
ExcelUtils.outputExcelStream(request, response, workbook, fileName); return "導出成功";
} private List<User> getUsers() {
List<User> list = new ArrayList<>();
User aUser = new User();
aUser.setAge(18);
aUser.setId(1);
aUser.setName("Tony");
list.add(aUser); aUser = new User();
aUser.setAge(21);
aUser.setId(2);
aUser.setName("Tom");
list.add(aUser);
aUser = new User();
aUser.setAge(20);
aUser.setId(3);
aUser.setName("店小二");
list.add(aUser);
return list;
} private HSSFCellStyle setCellStyle(HSSFWorkbook myWorkbook) {
HSSFCellStyle style = myWorkbook.createCellStyle();
//設置自動換行
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER);
//設置邊框樣式
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
//設置邊框顏色
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); HSSFDataFormat format = workbook.createDataFormat();
// 設置文本格式
style.setDataFormat(format.getFormat("@"));
// 設置字體
HSSFFont font = workbook.createFont();
font.setColor(Font.COLOR_RED);
font.setFontName("宋體");
font.setFontHeightInPoints((short) 12);// 12號字體
style.setFont(font);
return style;
}
}
通過反射獲取POJO對象，故傳入不同類型的POJO對象的dataList就可以了。下面是依賴的一個工具類：
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment; import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder; /**
* 導出Excel工具類
*
* @author Wiener
* @date 2020/9/5
*/
@Slf4j
public class ExcelUtils { /**
* 設置Excel表頭
*
* @param workbook
* @param sheet
* @param tableHeader
* @return
*/
public static HSSFRow getExcelRow(HSSFWorkbook workbook, HSSFSheet sheet, String[] tableHeader) {
short cellNumber = (short) tableHeader.length;
// Excel的第一行表頭
HSSFRow row = sheet.createRow(0);
// 設置行高
row.setHeight((short) 560);
// Excel的列
HSSFCell cell = null;
HSSFFont font = workbook.createFont();
// 粗體顯示
font.setBold(Boolean.TRUE);
font.setFontHeightInPoints((short) 15);
// 設置單元格字體的顏色
font.setColor(HSSFFont.COLOR_NORMAL);
// 樣式
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
//設置邊框樣式
style.setBorderTop(BorderStyle.DOTTED);
style.setBorderBottom(BorderStyle.DOTTED);
style.setBorderLeft(BorderStyle.DOTTED);
style.setBorderRight(BorderStyle.DOTTED);
//設置邊框顏色
style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex()); for (int k = 0; k < cellNumber; k++) {
int i = 0;
i += k;
short b = 6000;
cell = row.createCell(i); // 創(chuàng)建第0行第k列
cell.setCellValue(tableHeader[k]); // 設置第0行第k列的值
sheet.setColumnWidth(i, b); // 設置列的寬度
style.setFont(font); // 設置字體風格
cell.setCellStyle(style);
}
return row;
} /**
* 設置Excel未獲取數據提示行
*
* @param workbook
* @param sheet
* @return
*/
public static HSSFRow getNotResultInfoRow(HSSFWorkbook workbook, HSSFSheet sheet, String message) {
// Excel的第一行表頭
HSSFRow row = sheet.createRow(0);
// Excel的列
HSSFCell cell = null;
// 設置表頭的類型
HSSFCellStyle style = workbook.createCellStyle();
//設置自動換行
style.setWrapText(true);
style.setAlignment(HorizontalAlignment.CENTER); // 沒有查到數據
row = sheet.createRow((short) (0)); // 創(chuàng)建第j+1行
row.setHeight((short) 1000); // 設置行高
sheet.setColumnWidth(0, 10000);
cell = row.createCell(0); // 創(chuàng)建第i+1行第1列
cell.setCellValue(message);//
cell.setCellStyle(style); // 設置風格 return row;
} /**
* 下載EXCEL,對不同瀏覽器做中文名稱兼容<br/>
* 如果不對文件名進行編碼處理，那么有些瀏覽器無法識別下載文件
*
* @param request
* @param response
* @param workbook
* @param fileName
*/
public static void outputExcelStream(HttpServletRequest request, HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
OutputStream out = null;
try {
String excelFileName = fileName + "_" + (int) (Math.random() * 9000 + 1000) + ".xls";
if (isMsBrowser(request)) {
// ie,edge 瀏覽器
excelFileName = URLEncoder.encode(excelFileName, "UTF-8");
} else {
//其他的瀏覽器
excelFileName = new String(excelFileName.getBytes("UTF-8"), "iso-8859-1");
}
//輸出流對象
out = response.getOutputStream();
// 設置文件頭編碼方式和文件名
response.setHeader("Content-Disposition", "attachment;filename=" + excelFileName);
// 設置類型
response.setContentType("application/msexcel;charset=UTF-8");
response.setHeader("Pragma", "No-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
workbook.write(out);
out.flush();
workbook.write(out);
} catch (IOException e) {
log.error("下載EXCEL失敗，", e);
} finally {
try {
if (out != null) {
out.close();
}
} catch (IOException e) {
log.error("下載EXCEL關閉文件流失敗，", e);
}
}
} //判斷是否是IE瀏覽器
private static boolean isMsBrowser(HttpServletRequest request) {
String userAgent = request.getHeader("User-Agent");
if (StringUtils.isBlank(userAgent)) {
return false;
}
String[] IEBrowserSignals = {"msie", "trident", "edge"};
for (String signal : IEBrowserSignals) {
if (userAgent.toLowerCase().contains(signal)) {
return true;
}
}
return false;
} }
在使用的時候，僅僅需要傳入不同的表頭集合、實體類集合，就能實現需求，這就是封裝的魅力所在。
總結
以上是生活随笔為你收集整理的Spring Boot项目基于POI框架导出Excel表格的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 中国在沙特阿拉伯建高铁有实现沙特阿拉伯能
- 下一篇： ChatMoney让你变成HR高手！