目錄
背景描述
技術準備
導出Excel——嘗鮮版
導出Excel——封裝版(通過反射)
導出Excel——深度封裝（設定下拉選項）
擴充——多個列分别是不同的下拉選項怎麼封裝
2019-10-28 更新，必看！！！
2019-12-18更新，修複小機率的檔案名亂碼問題
背景描述
最近部落客在做的Web項目中，有一個導出資料到Excel表格的需求，之前用純JS實作過，這次打算用Java在後端實作，将資料通過response以IO流的方式傳輸給前端，使浏覽器能直接下載下傳。這裡做一下記錄、筆記。
技術準備
我的項目是基于Spring Boot的，這裡隻貼出POI架構需要依賴的兩個包，其他的都無所謂，隻要能提供Controller讓浏覽器通路即可
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
導出Excel——嘗鮮版
這裡為了讓大家了解POI這個架構一系列的API，先以最low的方式去實作，稍後我們再進行封裝，達到“編寫一次、處處可用”。
我們隻需要提供一個Controller接口：
/**
* 導出資料到Excel
* @param response 響應體
* 注意，我這裡是基于Spring Boot的，全局有一個@RestController注解，是以沒加@ResponseBody，
* 如果你的不是，請加上@ResponseBody注解
* */
@GetMapping(value = "/out-excel-demo")
public Object outExcelDemo(HttpServletResponse response) throws IOException {
//建立HSSFWorkbook對象(excel的文檔對象)
HSSFWorkbook wb = new HSSFWorkbook();
//建立sheet對象（excel的表單）
HSSFSheet sheet=wb.createSheet("sheet1");
//建立第一行，這裡即是表頭。行的最小值是0，代表每一行，上限沒研究過，可參考官方的文檔
HSSFRow row1=sheet.createRow(0);
//在這一行建立單元格，并且将這個單元格的内容設為“賬号”，下面同理。
//列的最小值辨別也是0
row1.createCell(0).setCellValue("賬号");
row1.createCell(1).setCellValue("使用者名");
row1.createCell(2).setCellValue("日期");
row1.createCell(3).setCellValue("是否完成");
//第二行
HSSFRow row2=sheet.createRow(1);
row2.createCell(0).setCellValue("123456");
row2.createCell(1).setCellValue("張三");
row2.createCell(2).setCellValue("2019-08-05");
row2.createCell(3).setCellValue("是");
//第三行
HSSFRow row3=sheet.createRow(2);
row3.createCell(0).setCellValue("5681464");
row3.createCell(1).setCellValue("李四");
row3.createCell(2).setCellValue("2019-08-01");
row3.createCell(3).setCellValue("否");
//輸出Excel檔案
OutputStream output=response.getOutputStream();
response.reset();
response
.setHeader("Content-disposition", "attachment; filename=demo.xls");
response.setContentType("application/x-xls");
wb.write(output);
output.close();
return null;
}
然後你可以在頁面上寫一個按鈕，點選的時候通過location.href指向上面的接口路徑，我這裡就省略了，看一下效果：
打開表格：
到這裡，相信大家對POI有認識了吧？
其實它就是以每個HSSFRow為一個主體，每一個HSSFRow代表一行記錄，我們隻需要通過這個對象的createCell方法去建立單元格、指派就行，這樣就很清晰了吧？
導出Excel——封裝版(通過反射)
以上我們實作了簡單的資料導出，但是實際的場景根本不是這樣，我們都是從資料庫裡查出來資料，而且不可能這樣一行一行的去設定。
你肯定想到了循環，沒錯，循環是肯定的，但是僅僅循環還不夠靈活，為什麼呢？
根據面向對象的思維，我們可以将所有的表頭（即第一行）做成一個List集合參數，将所有的資料做成一個List集合參數，這個資料集合的泛型是我們的POJO實體類，然後我們兩個循環就能省略一大段代碼。
但是問題來了，我們例子中的導出表格，是“賬号、使用者名、日期、是否完成”這四個表頭，實體類也是對應的四個屬性。假如又來了一個導出需求呢？表頭不一樣了，所對應的實體類也不一樣了，難道我們再封裝成一個其他的方法？難道每個不同的Excel表結構都要封裝一個新的方法嗎？
做的時候部落客立馬就想到了反射機制，我們可以傳入List集合，對泛型不做限制，周遊資料集合的時候，通過反射得到這個對象的字段，動态指派。
但是這就有一個強制要求：在實體類聲明字段的時候，順序必須和表頭的前後順序一緻，否則循環周遊的時候會出現資料不對應的現象。
首先我們聲明一個實體類，這也符合我們真正的開發環境：
package com.dosion.smart.future.api.entity.activity.json;
import lombok.Data;
/**
* 導出報名情況的資料傳輸對象
* @author 秋楓豔夢
* @date 2019-08-05
* */
@Data
public class SignOutExcelJSON {
//使用者賬号
private String account;
//使用者名
private String username;
//報名時間
private String signDate;
//是否完成
private String finish;
}
然後封裝一個工具類出來：
package com.dosion.smart.future.utils;
import com.dosion.smart.future.api.entity.activity.json.SignOutExcelJSON;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* 資料導出excel，工具類
* @author 秋楓豔夢
* @date 209-08-05
* */
public class ExcelUtil {
/**
* 生成Excel表格
* @param sheetName sheet名稱
* @param titleList 表頭清單
* @param dataList 資料清單
* @return HSSFWorkbook對象
* */
public static HSSFWorkbook createExcel(String sheetName,
List<String> titleList,List dataList) throws IllegalAccessException {
//建立HSSFWorkbook對象
HSSFWorkbook wb = new HSSFWorkbook();
//建立sheet對象
HSSFSheet sheet=wb.createSheet(sheetName);
//在sheet裡建立第一行，這裡即是表頭
HSSFRow rowTitle=sheet.createRow(0);
//寫入表頭的每一個列
for (int i = 0; i < titleList.size(); i++) {
//建立單元格
rowTitle.createCell(i).setCellValue(titleList.get(i));
}
//寫入每一行的記錄
for (int i = 0; i < dataList.size(); i++) {
//建立新的一行，遞增
HSSFRow rowData = sheet.createRow(i+1);
//通過反射，擷取POJO對象
Class cl = dataList.get(i).getClass();
//擷取類的所有字段
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
//設定字段可見，否則會報錯，禁止通路
fields[j].setAccessible(true);
//建立單元格
rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
}
}
return wb;
}
}
然後我們模仿一下調用（這裡手動制造資料，真實情況下通過資料庫查詢）：
/**
* 導出Excel
*
*
* */
@GetMapping(value = "/out-excel-demo")
public String outExcelDemo(HttpServletResponse response) throws IOException, IllegalAccessException {
//檔案名
String fileName = "活動報名情況一覽表";
//sheet名
String sheetName = "報名情況sheet";
//表頭集合，作為表頭參數
List<String> titleList = new ArrayList<>();
titleList.add("使用者賬戶");
titleList.add("使用者名");
titleList.add("報名時間");
titleList.add("是否完成");
//資料對象，這裡模拟手動添加，真實的環境往往是從資料庫中得到
SignOutExcelJSON excelJSON = new SignOutExcelJSON();
excelJSON.setAccount("18210825916");
excelJSON.setUsername("張三");
excelJSON.setSignDate("2019-08-05");
excelJSON.setFinish("是");
SignOutExcelJSON excelJSON2 = new SignOutExcelJSON();
excelJSON2.setAccount("15939305781");
excelJSON2.setUsername("李四");
excelJSON2.setSignDate("2019-08-01");
excelJSON2.setFinish("否");
//将兩個對象加入到集合中，作為資料參數
List<SignOutExcelJSON> excelJSONList = new ArrayList<>();
excelJSONList.add(excelJSON);
excelJSONList.add(excelJSON2);
//調取封裝的方法，傳入相應的參數
HSSFWorkbook workbook = ExcelUtil.createExcel(sheetName,titleList, excelJSONList);
//輸出Excel檔案
OutputStream output=response.getOutputStream();
response.reset();
//中文名稱要進行編碼處理
response
.setHeader("Content-disposition", "attachment; filename="+new String(fileName.getBytes("GB2312"),"ISO8859-1")+".xls");
response.setContentType("application/x-xls");
workbook.write(output);
output.close();
return null;
}
運作結果：
效果是一樣的，而且很靈活。各位可以試一下，假如你導出其他子產品的資料，你隻需要傳入不同的表頭集合、實體類集合，就能實作你的需求，這也是封裝的魅力所在。
導出Excel——深度封裝（設定下拉選項）
假如我有一個需求：是否完成這一列隻能輸入是否，以下拉框的形式出現。
POI架構肯定有對應的API，大家看文檔也能學會，這裡我帶大家封裝一下，畢竟以可重用性為榮。
先封裝一個下拉條件對象：
package com.dosion.smart.future.api.entity.activity;
import lombok.Data;
/**
* 導出Excel時的條件，有下列選項時使用
* @author 秋楓豔夢
* @date 2019-08-05
* */
@Data
public class OutExcelQuery {
//起始行
private int rowStart;
//結束行
private int rowEnd;
//起始列
private int colStart;
//結束列
private int colEnd;
//下拉參數
private String[] params;
//構造函數
public OutExcelQuery(int rowStart,int rowEnd,int colStart,int colEnd,String[] params){
this.rowStart = rowStart;
this.rowEnd = rowEnd;
this.colStart = colStart;
this.colEnd = colEnd;
this.params = params;
}
}
再貼出來工具類：
package com.dosion.smart.future.utils;
import com.dosion.smart.future.api.entity.activity.OutExcelQuery;
import com.dosion.smart.future.api.entity.activity.json.SignOutExcelJSON;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* 資料導出excel，工具類
* @author 秋楓豔夢
* @date 209-08-05
* */
public class ExcelUtil {
/**
* 生成Excel表格
* @param sheetName sheet名稱
* @param titleList 表頭清單
* @param dataList 資料清單
* @param outExcelQuery 下拉選項設定
* @return HSSFWorkbook對象
* */
public static HSSFWorkbook createExcel(String sheetName, List<String> titleList,
List dataList, OutExcelQuery outExcelQuery) throws IllegalAccessException {
//建立HSSFWorkbook對象(excel的文檔對象)
HSSFWorkbook wb = new HSSFWorkbook();
//建立sheet對象（excel的表單）
HSSFSheet sheet=wb.createSheet(sheetName);
//在sheet裡建立第一行，這裡即是表頭
HSSFRow rowTitle=sheet.createRow(0);
//寫入表頭的每一個列
for (int i = 0; i < titleList.size(); i++) {
//建立單元格
rowTitle.createCell(i).setCellValue(titleList.get(i));
}
//寫入每一行的記錄
int count = 0;
for (int i = 0; i < dataList.size(); i++) {
count++;
//建立新的一行，遞增
HSSFRow rowData = sheet.createRow(i+1);
//通過反射，擷取POJO對象
Class cl = dataList.get(i).getClass();
//擷取類的所有字段
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
//設定字段可見，否則會報錯，禁止通路
fields[j].setAccessible(true);
//建立單元格
rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
}
}
//如果開啟了下拉選項
if (outExcelQuery!=null){
//如果表格中的記錄數不是0
if (count!=0){
// 擷取下拉清單資料
String[] strs = outExcelQuery.getParams();
//設定哪些行的哪些列為下拉選項
CellRangeAddressList rangeList =
new CellRangeAddressList(outExcelQuery.getRowStart(),
//結束行為-1時，說明設定所有行
outExcelQuery.getRowEnd()==-1?count:outExcelQuery.getRowEnd(),
outExcelQuery.getColStart(),outExcelQuery.getColEnd());
//綁定下拉資料
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strs);
//綁定兩者的關系
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList,constraint);
//添加到sheet中
sheet.addValidationData(dataValidation);
}
}
return wb;
}
}
如果我不想設定任何列為下拉選項，那我調用的時候将最後一個參數傳入null即可。如果想設定某一列或某幾列為下拉選項，那我調用的時候隻需要這樣（省略其他代碼）：
String[] params = new String[]{"是","否"};
//從第一行開始，到最後一行結束，設定第4列為下拉選項
OutExcelQuery outExcelQuery = new OutExcelQuery(1,-1,3,3,params);
HSSFWorkbook workbook = ExcelUtil.createExcel(sheetName,titleList,activityService.outExcel(id),outExcelQuery);
效果：
擴充——多個列分别是不同的下拉選項怎麼封裝
以上下拉選項的封裝，隻是針對某一列或某幾列使用相同的下拉選項的情況，假如幾個資料列的下拉選項不同呢？
比如，再加一個性别列，下拉選項的值是男和女，此時一張Excel表中就出現了兩個下拉選項設定，該怎麼封裝？
部落客就不再寫了，留給大家思考，有疑問的可以留言。
提示一個思路：可以應用Java可選參數的特性，傳入多個OutExcelQuery對象，進行循環添加條件。
挖坑填坑，其樂融融。
2019-10-28 更新，必看！！！
最近有一位博友用到了我的這個工具類，首先表示很榮幸。
但是幫他解決問題的過程中，也發現了這個工具類的一點瑕疵，那就是：
之前的版本，必須要求表頭、實體類的字段一一對應，且順序要一緻，比如你導出的表格中有姓名和年齡兩個列，那麼你的實體類中隻能有name和age兩個字段，且順序要一緻，否則會出現年齡的值出現在姓名列的情況。
這樣确實有點不靈活，如果一個實體類有多個字段呢？如果依然采用這種方式，那我豈不是還要為導出表格專門寫一個實體類？
是以，我做了以下改進，在循環寫入每一行的列的時候，周遊的是titleList集合的長度，而不是實體類的字段數量，這樣一來，我們有一個表頭列，就會周遊出對象的幾個屬性，對象其他的屬性将不會展現到表格裡。舉個例子：
假設導出的表頭有姓名、年齡兩個列，但是實體類有name、age、sex三個字段，那麼我們周遊的是表頭的長度，即2，那麼sex這個字段将不會被寫入表格裡。這樣一來，實體類就可以有任意個字段了，隻需要保證前n個字段與表頭保持一緻即可。
需要注意的是，你需要導出的字段，在實體類裡仍然需要按照表頭的順序進行排列，沒辦法，隻能這麼取舍了，否則就做不到萬能了，當然大家也可以根據自己的業務去做定制化。
另外，此次增加了Excel的導入功能。
最後貼出來完整的工具類：
import com.dosion.model.activity.query.OutExcelQuery;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* 資料導出、導入excel，工具類
* @author 秋楓豔夢
* @date 2019-10-28
* */
public class ExcelUtil {
/**
* 生成Excel表格
* @param sheetName sheet名稱
* @param titleList 表頭清單
* @param dataList 資料清單
* @param outExcelQuery 下拉選項設定
* @return HSSFWorkbook對象
* */
public static HSSFWorkbook createExcel(String sheetName, List<String> titleList,
List dataList, OutExcelQuery outExcelQuery) throws IllegalAccessException {
//建立HSSFWorkbook對象(excel的文檔對象)
HSSFWorkbook wb = new HSSFWorkbook();
//建立sheet對象（excel的表單）
HSSFSheet sheet=wb.createSheet(sheetName);
//在sheet裡建立第一行，這裡即是表頭
HSSFRow rowTitle=sheet.createRow(0);
//寫入表頭的每一個列
for (int i = 0; i < titleList.size(); i++) {
//建立單元格
rowTitle.createCell(i).setCellValue(titleList.get(i));
}
int count = 0;
//寫入每一行的記錄
if (dataList!=null){
for (int i = 0; i < dataList.size(); i++) {
count++;
//建立新的一行，遞增
HSSFRow rowData = sheet.createRow(i+1);
//通過反射，擷取POJO對象
Class cl = dataList.get(i).getClass();
//擷取類的所有字段
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < titleList.size(); j++) {
//設定字段可見，否則會報錯，禁止通路
fields[j].setAccessible(true);
//建立單元格
rowData.createCell(j).setCellValue((String) fields[j].get(dataList.get(i)));
}
}
}
//如果開啟了下拉選項
if (outExcelQuery!=null){
//如果表格中的記錄數不是0
if (count!=0){
// 擷取下拉清單資料
String[] strs = outExcelQuery.getParams();
//設定哪些行的哪些列為下拉選項
CellRangeAddressList rangeList =
new CellRangeAddressList(outExcelQuery.getRowStart(),
//結束行為-1時，說明設定所有行
outExcelQuery.getRowEnd()==-1?count:outExcelQuery.getRowEnd(),
outExcelQuery.getColStart(),outExcelQuery.getColEnd());
//綁定下拉資料
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strs);
//綁定兩者的關系
HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList,constraint);
//添加到sheet中
sheet.addValidationData(dataValidation);
}
}
return wb;
}
/**
* 讀入excel檔案，解析後傳回
* @param file
* @throws IOException
*/
public static List<String[]> readExcel(MultipartFile file) throws IOException{
//檢查檔案
checkFile(file);
//獲得Workbook工作薄對象
Workbook workbook = getWorkBook(file);
//建立傳回對象，把每行中的值作為一個數組，所有行作為一個集合傳回
List<String[]> list = new ArrayList<String[]>();
if(workbook != null){
for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
//獲得目前sheet工作表
Sheet sheet = workbook.getSheetAt(sheetNum);
if(sheet == null){
continue;
}
//獲得目前sheet的開始行
int firstRowNum = sheet.getFirstRowNum();
//獲得目前sheet的結束行
int lastRowNum = sheet.getLastRowNum();
//循環除了第一行的所有行
for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
//獲得目前行
Row row = sheet.getRow(rowNum);
if(row == null){
continue;
}
//獲得目前行的開始列
int firstCellNum = row.getFirstCellNum();
//獲得目前行的列數
int lastCellNum = row.getPhysicalNumberOfCells();
String[] cells = new String[row.getPhysicalNumberOfCells()];
//循環目前行
for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
Cell cell = row.getCell(cellNum);
cells[cellNum] = getCellValue(cell);
}
list.add(cells);
}
}
workbook.close();
}
return list;
}
/**
* 檢查使用者上傳的檔案
* @param file 檔案對象
* */
private static void checkFile(MultipartFile file) throws IOException{
//判斷檔案是否存在
if(null == file){
throw new FileNotFoundException("檔案不存在！");
}
//獲得檔案名
String fileName = file.getOriginalFilename();
//判斷檔案是否是excel檔案
if(!fileName.endsWith("xls") && !fileName.endsWith("xlsx")){
throw new IOException(fileName + "不是excel檔案");
}
}
/**
* 擷取Workbook對象
* @param file 檔案對象
* @return Workbook對象
* */
private static Workbook getWorkBook(MultipartFile file) {
//獲得檔案名
String fileName = file.getOriginalFilename();
//建立Workbook工作薄對象，表示整個excel
Workbook workbook = null;
try {
//擷取excel檔案的io流
InputStream is = file.getInputStream();
//根據檔案字尾名不同(xls和xlsx)獲得不同的Workbook實作類對象
if(fileName.endsWith("xls")){
//2003
workbook = new HSSFWorkbook(is);
}else if(fileName.endsWith("xlsx")){
//2007
workbook = new XSSFWorkbook(is);
}
} catch (IOException e) {
}
return workbook;
}
/**
* 擷取單元格的值
* @param cell 單元格對象
* @return 值
* */
private static String getCellValue(Cell cell){
String cellValue = "";
if(cell == null){
return cellValue;
}
//把數字當成String來讀，避免出現1讀成1.0的情況
if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
cell.setCellType(Cell.CELL_TYPE_STRING);
}
//判斷資料的類型
switch (cell.getCellType()){
case Cell.CELL_TYPE_NUMERIC: //數字
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING: //字元串
cellValue = String.valueOf(cell.getStringCellValue());
break;
case Cell.CELL_TYPE_BOOLEAN: //Boolean
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA: //公式
cellValue = String.valueOf(cell.getCellFormula());
break;
case Cell.CELL_TYPE_BLANK: //空值
cellValue = "未填寫";
break;
case Cell.CELL_TYPE_ERROR: //故障
cellValue = "非法字元";
break;
default:
cellValue = "未知類型";
break;
}
return cellValue;
}
}
2019-12-18更新，修複小機率的檔案名亂碼問題
最近有朋友反映說，他導出時有時候會出現檔案名亂碼的問題，部落客複現了很多次，都沒有複現出來。後來同僚也反映這個問題，部落客才重視起來。
經排查發現，首先之前的ISO8859-1，部落客寫的不規範，應該是ISO-8859-1(但是我感覺跟它沒關系，哈哈)；其次，部落客把響應流的類型設定為了application/x-xls，這是标準的excel響應流格式。親測有效，無論是.xls字尾還是.xlsx字尾，都沒有問題。
在這裡要感謝這篇文章，總結了各種的response響應流格式：
https://blog.csdn.net/luman1991/article/details/53423305
貼出關鍵代碼部分：
//輸出Excel檔案
OutputStream output=response.getOutputStream();
response.reset();
//中文名稱要進行編碼處理
response
.setHeader("Content-disposition", "attachment; filename="+new String("遊戲清單".getBytes("GB2312"),"ISO-8859-1")+".xlsx");
response.setContentType("application/x-xls");
workbook.write(output);
output.close();
另外，我發現有一個奇怪的現象，大部分中文名都能相容，但是我把“遊戲清單”換成“學校”，那麼就會亂碼，換成“學校清單”就又好了……
有人說跟浏覽器的編碼方式有關，需要在後端代碼做處理，但是部落客試了一個遍，還是失敗。。。部落客就退而求其次吧，我覺得這是可以接受的，換一個同義的檔案名而已。