HExcel，一個簡單通用的匯入匯出Excel工具類

package cn.huanzi.qch.util;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;

/**
 * HExcel，一個簡單通用的匯入匯出Excel工具類
 * 1、支援複雜表頭匯出（支援表頭單元格水平合併、垂直合併，支援表頭單元格個性化樣式）
 * 2、支援匯入讀取sheet資料（只需要提供title與key的關係，不需要管列的順序）
 *
 * PS：依賴 poi 以及 hutool
 *
 * 詳情請戳：https://www.cnblogs.com/huanzi-qch/p/17797355.html
 */
public class HExcel {

    /**
     * 獲取一個HExcel範例，並初始化空Workbook物件
     */
    public static HExcel newInstance(){
        HExcel hExcelUtil = new HExcel();
        hExcelUtil.hSSFWorkbook = new HSSFWorkbook();
        return hExcelUtil;
    }

    /**
     * 獲取一個HExcel範例，並根據excelFile初始化Workbook物件
     */
    public static HExcel newInstance(File excelFile){
        HExcel hExcelUtil = new HExcel();
        try {
            hExcelUtil.hSSFWorkbook = new HSSFWorkbook(new FileInputStream(excelFile));
        } catch (IOException e) {
            throw new RuntimeException("【HExcel】 根據excelFile初始化Workbook物件異常",e);
        }
        return hExcelUtil;
    }

    /**
     * 匯入並讀取Excel
     *
     * @param sheetIndex 需要讀取的sheet下標
     * @param firstDataRow 資料起始行
     * @param headerTitleKey title與key的關係json物件
     * @return 返回資料集合
     */
    public List<Map<String, Object>> readSheet(int sheetIndex,int firstDataRow,JSONObject headerTitleKey){
        //最終返回的資料集合
        ArrayList<Map<String, Object>> list = new ArrayList<>();

        //獲取sheet
        HSSFSheet sheet = this.hSSFWorkbook.getSheetAt(sheetIndex);

        //獲取title與col的對應關係
        HashMap<Integer, String> headerMap = new HashMap<>();
        int lastCellNum = sheet.getRow(0).getLastCellNum();
        for (int i = 0; i < lastCellNum; i++) {
            for (int j = firstDataRow-1; j >=0 ; j--) {
                HSSFCell cell = sheet.getRow(j).getCell(i);
                if(cell != null && !"".equals(cell.getStringCellValue())){
                    String title = cell.getStringCellValue();
                    headerMap.put(i,title);
                    break;
                }
            }
        }

        //獲取資料
        for (int i = firstDataRow; i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            LinkedHashMap<String, Object> dateMap = new LinkedHashMap<>();
            for (int j = 0; j < lastCellNum; j++) {
                String title = headerMap.get(j);
                String key = headerTitleKey.getStr(title);

                if(key != null && !"".equals(key)){
                    String value = row.getCell(j).getStringCellValue();
                    dateMap.put(key,value);
                }
            }
            list.add(dateMap);
        }



        return list;
    }

    /**
     * 構造一個sheet，以及生成複雜表頭、表資料
     *
     * @param sheetName sheet名稱
     * @param headers 複雜表頭json陣列物件
     * @param dataLists 表資料集合
     * @return HExcel
     */
    public HExcel buildSheet(String sheetName, JSONArray headers, List<Map<String, Object>> dataLists) {
        //建立新的sheet物件
        HSSFSheet sheet = this.hSSFWorkbook.createSheet(sheetName);//設定表單名

        //生成複雜表頭
        int row = 0;//當前行
        int col = 0;//當前列
        HashMap<String, Object> hashMap = createHeader(sheet,row,col,headers);
        ArrayList<String> headerList = (ArrayList<String>) hashMap.get("keyList");
        row = (int) hashMap.get("maxRow");

        //取出水平合併區域資料
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        //垂直合併，單元格為空，且不屬於水平合併區域
        //這裡row-1是因為，生成所有表頭結束後，maxRow比最大行+1，
        for (int i = 0; i < headerList.size(); i++) {
            for (int j = 0; j <= row-1; j++) {
                boolean flag = true;

                //單元格不為空
                HSSFCell cell = sheet.getRow(j).getCell(i);
                if(cell != null){
                    continue;
                }
                //檢查合併區域
                for (CellRangeAddress cellAddresses : cellRangeAddressList) {
                    int OldFirstRow = cellAddresses.getFirstRow();
                    int OldLastRow = cellAddresses.getLastRow();
                    int OldFirstCol = cellAddresses.getFirstColumn();
                    int OldLastCol = cellAddresses.getLastColumn();

                    //與合併區域重疊
                    if ((OldFirstRow >= j && OldLastRow <= j) && (OldFirstCol >= i && OldLastCol <= i)) {
                        flag = false;
                        break;
                    }
                }

                //滿足條件，將上一個單元格與最後一個單元格合併
                if(flag){
                    mergedCell(sheet,j-1,row-1,i,i);
                    break;
                }
            }
        }

        //開始填充資料
        HSSFCellStyle dataStyle = createDataStyle(sheet);
        for (Map<String, Object> map : dataLists) {
            //建立內容行
            HSSFRow dataHSSFRow = sheet.createRow(row);
            for (int i = 0; i < headerList.size(); i++) {
                String key = headerList.get(i);
                Object val = map.get(key);
                createCell(dataHSSFRow, i, dataStyle, val == null ? "" : String.valueOf(val));
            }
            row++;
        }

        return this;
    }

    /**
     * 儲存成File檔案
     *
     * @param path 完整檔案路徑+檔名
     */
    public void toFile(String path) {
        //try-catch語法糖
        try (FileOutputStream out = new FileOutputStream(path);){
            this.hSSFWorkbook.write(out);
        }catch (IOException e){
            throw new RuntimeException("【HExcel】 Workbook物件檔案流寫入File異常",e);
        }
    }

    /**
     * 儲存到HttpServletResponse
     *
     * @param fileName 檔名
     * @param response HttpServletResponse物件
     */
    public void toHttpServletResponse(String fileName, HttpServletResponse response) {
        //try-catch語法糖
        try (ServletOutputStream outputStream = response.getOutputStream();){
            response.setHeader("Accept-Ranges", "bytes");
            response.setHeader("Content-disposition", "attachment; filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
            response.setContentType("application/octet-stream");
            this.hSSFWorkbook.write(outputStream);
        }catch (Exception e){
            throw new RuntimeException("【HExcel】 Workbook物件檔案流寫入Response異常",e);
        }
    }

    /**
     * 關閉Workbook
     */
    public void close(){
        try{
            //關閉Workbook
            this.hSSFWorkbook.close();
        } catch (Exception e) {
            throw new RuntimeException("【HExcel】 關閉Workbook異常",e);
        }
    }


    /*          已下設定私有，對外隱藏實現細節           */

    /**
     * Workbook物件
     */
    private HSSFWorkbook hSSFWorkbook;

    /**
     * 構造表頭
     *
     * @param sheet sheet
     * @param row 當前操作行
     * @param col 當前操作列
     * @param headers 表頭資料
     * @return 返回一個map物件，供上級表頭獲取最新當前操作行、列、key集合
     */
    private HashMap<String,Object> createHeader(HSSFSheet sheet, int row, int col, JSONArray headers){
        //最終返回物件
        HashMap<String, Object> hashMap = new HashMap<>();

        //key集合
        ArrayList<String> keyList = new ArrayList<>();

        HSSFWorkbook wb = sheet.getWorkbook();
        HSSFRow headerHSSFRow = sheet.getRow(row);
        if(headerHSSFRow == null){
            headerHSSFRow = sheet.createRow(row);
        }
        for (Object object : headers) {
            JSONObject header = (JSONObject) object;
            String title = (String) header.get("title");
            String key = (String) header.get("key");
            Object width = header.get("width");
            Object align = header.get("align");
            Object backgroundColor = header.get("background-color");
            Object color = header.get("color");
            Object children = header.get("children");

            //單元格樣式
            HSSFCellStyle headerStyle = createHeaderStyle(sheet);

            //自定義單元格背景色
            if(backgroundColor != null){
                headerStyle.setFillForegroundColor(Short.parseShort(backgroundColor+""));
            }

            //自定義單元格字型顏色
            if(color != null){
                headerStyle.getFont(wb).setColor(Short.parseShort(color+""));
            }

            //預設單元格寬度，20
            sheet.setColumnWidth(col, 20 * 256);
            if(width != null){
                //自定義單元格寬度
                sheet.setColumnWidth(col, (int) width * 256);
            }

            //預設水平對齊方式（水平居中）
            if(align != null){
                //自定義水平對齊方式
                HorizontalAlignment alignment;
                switch (String.valueOf(align).toUpperCase()){
                    case "LEFT":
                        alignment = HorizontalAlignment.LEFT;
                        break;
                    case "RIGHT":
                        alignment = HorizontalAlignment.RIGHT;
                        break;
                    default:
                        alignment = HorizontalAlignment.CENTER;
                        break;
                }
                headerStyle.setAlignment(alignment);
            }

            //System.out.println(title + " " + key + " " + row + " " + col);

            //生成單元格同時設定內容
            createCell(headerHSSFRow, col, headerStyle, title);

            //無子級表頭
            if(children == null){
                //保留順序，方便後面設定資料
                keyList.add(key);

                //當前列+1
                col++;
            }
            //有子級表頭
            else{
                //遞迴生成子級表頭前，儲存父級表頭col，用於水平合併
                int firstCol = col;

                //遞迴呼叫
                HashMap<String, Object> hashMap1 = createHeader(sheet, row + 1, col, (JSONArray) children);

                //獲取最新col、key集合
                col = (int) hashMap1.get("col");
                hashMap.put("maxRow",hashMap1.get("maxRow"));
                keyList.addAll((ArrayList<String>) hashMap1.get("keyList"));

                //水平合併，這裡col-1是因為，生成子級表頭結束後，col比最後一個下級表頭+1，
                if(!(firstCol == col-1)){
                    mergedCell(sheet,row,row,firstCol,col-1);
                }
            }
        }

        //將資料設定到物件中，返回上一層
        hashMap.put("maxRow",(hashMap.get("maxRow") != null ? Integer.parseInt(hashMap.get("maxRow")+"") : 0) + 1);//最大行
        hashMap.put("row",row);//當前操作行
        hashMap.put("col",col);//當前操作列
        hashMap.put("keyList",keyList);//key集合

        return hashMap;
    }

    /**
     * 建立一個單元格
     *
     * @param hSSFRow 當前行物件
     * @param col 當前列
     * @param cellStyle 單元格樣式物件
     * @param text 單元格內容，目前只支援字串，如需支援更多格式可自行擴充套件
     */
    private void createCell(HSSFRow hSSFRow, int col, HSSFCellStyle cellStyle, String text) {
        HSSFCell cell = hSSFRow.createCell(col);  // 建立單元格
        cell.setCellStyle(cellStyle); // 設定單元格樣式
        cell.setCellValue(text);  // 設定值
    }

    /**
     * 構造表頭、資料樣式
     *
     * @param sheet sheet
     * @return 返回一個單元格樣式物件
     */
    private HSSFCellStyle createHeaderStyle(HSSFSheet sheet){
        HSSFWorkbook wb = sheet.getWorkbook();

        //表頭的樣式
        HSSFCellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //列名的字型
        HSSFFont dataFont = wb.createFont();
        dataFont.setFontHeightInPoints((short) 12);
        dataFont.setFontName("新宋體");
        headerStyle.setFont(dataFont);// 把字型 應用到當前樣式
        headerStyle.setWrapText(true);//自動換行
        //填充樣式，前景色、天空藍
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());

        // 設定邊框
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);

        return headerStyle;
    }
    private HSSFCellStyle createDataStyle(HSSFSheet sheet){
        HSSFWorkbook wb = sheet.getWorkbook();

        //內容的樣式
        HSSFCellStyle dataStyle = wb.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //內容的字型
        HSSFFont font3 = wb.createFont();
        font3.setFontHeightInPoints((short) 12);
        font3.setFontName("新宋體");
        dataStyle.setFont(font3);// 把字型 應用到當前樣式
        dataStyle.setWrapText(true);//自動換行
        //預設無填充
        dataStyle.setFillPattern(FillPatternType.NO_FILL);
        // 設定邊框
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
        dataStyle.setBorderTop(BorderStyle.THIN);

        return dataStyle;
    }

    /**
     * 合併單元格
     *
     * @param sheet sheet
     * @param firstRow 起始行
     * @param lastRow 結束行
     * @param firstCol 起始列
     * @param lastCol 結束列
     */
    private void mergedCell(HSSFSheet sheet,int firstRow, int lastRow, int firstCol, int lastCol){
        //一個單元格無需合併，例如：[0,0,0,0]
        if(firstRow == lastRow && firstCol == lastCol){
            return;
        }

        //先取出合併前的單元格樣式
        HSSFCellStyle cellStyle = sheet.getRow(firstRow).getCell(firstCol).getCellStyle();

        //合併
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));

        //解決合併後的邊框等樣式問題
        int first;
        int end;
        //垂直合併
        if(firstCol == lastCol){
            first = firstRow;
            end = lastRow+1;

            for (int i = first; i < end; i++) {
                HSSFRow row = sheet.getRow(i);
                if(row == null){
                    row = sheet.createRow(i);
                }
                HSSFCell cell = row.getCell(firstCol);
                if(cell == null){
                    cell = row.createCell(firstCol);
                }
                cell.setCellStyle(cellStyle);
            }
        }
        //水平合併
        else{
            first = firstCol;
            end = lastCol+1;

            for (int i = first; i < end; i++) {
                HSSFRow row = sheet.getRow(firstRow);
                if(row == null){
                    row = sheet.createRow(firstRow);
                }
                HSSFCell cell = row.getCell(i);
                if(cell == null){
                    cell = row.createCell(i);
                }
                cell.setCellStyle(cellStyle);
            }
        }
    }

}