如何優雅的匯出Excel

你在我家門口發表於2019-02-19
Excel

前言

公司專案最近有一個需要：報表匯出。整個系統下來，起碼超過一百張報表需要匯出。這個時候如何優雅的實現報表匯出，釋放生產力就顯得很重要了。下面主要給大家分享一下該工具類的使用方法與實現思路。

實現的功能點

對於每個報表都相同的操作，我們很自然的會抽離出來，這個很簡單。而最重要的是：如何把那些每個報表不相同的操作進行良好的封裝，儘可能的提高複用性；針對以上的原則，主要實現了一下關鍵功能點：

  • 匯出任意型別的資料
  • 自由設定表頭
  • 自由設定欄位的匯出格式

使用例項

上面說到了本工具類實現了三個功能點，自然在使用的時候設定好這三個要點即可：

  • 設定資料列表
  • 設定表頭
  • 設定欄位格式

下面的export函式可以直接向客戶端返回一個excel資料，其中productInfoPos為待匯出的資料列表，ExcelHeaderInfo用來儲存表頭資訊，包括表頭名稱，表頭的首列，尾列，首行，尾行。因為預設匯出的資料格式都是字串型，所以還需要一個Map引數用來指定某個欄位的格式化型別（例如數字型別，小數型別、日期型別）。這裡大家知道個大概怎麼使用就好了，下面會對這些引數進行詳細解釋。

@Override
    public void export(HttpServletResponse response, String fileName) {
        // 待匯出資料
        List<TtlProductInfoPo> productInfoPos = this.multiThreadListProduct();
        ExcelUtils excelUtils = new ExcelUtils(productInfoPos, getHeaderInfo(), getFormatInfo());
        excelUtils.sendHttpResponse(response, fileName, excelUtils.getWorkbook());
    }

    // 獲取表頭資訊
    private List<ExcelHeaderInfo> getHeaderInfo() {
        return Arrays.asList(
                new ExcelHeaderInfo(1, 1, 0, 0, "id"),
                new ExcelHeaderInfo(1, 1, 1, 1, "商品名稱"),

                new ExcelHeaderInfo(0, 0, 2, 3, "分類"),
                new ExcelHeaderInfo(1, 1, 2, 2, "型別ID"),
                new ExcelHeaderInfo(1, 1, 3, 3, "分類名稱"),

                new ExcelHeaderInfo(0, 0, 4, 5, "品牌"),
                new ExcelHeaderInfo(1, 1, 4, 4, "品牌ID"),
                new ExcelHeaderInfo(1, 1, 5, 5, "品牌名稱"),

                new ExcelHeaderInfo(0, 0, 6, 7, "商店"),
                new ExcelHeaderInfo(1, 1, 6, 6, "商店ID"),
                new ExcelHeaderInfo(1, 1, 7, 7, "商店名稱"),

                new ExcelHeaderInfo(1, 1, 8, 8, "價格"),
                new ExcelHeaderInfo(1, 1, 9, 9, "庫存"),
                new ExcelHeaderInfo(1, 1, 10, 10, "銷量"),
                new ExcelHeaderInfo(1, 1, 11, 11, "插入時間"),
                new ExcelHeaderInfo(1, 1, 12, 12, "更新時間"),
                new ExcelHeaderInfo(1, 1, 13, 13, "記錄是否已經刪除")
        );
    }

    // 獲取格式化資訊
    private Map<String, ExcelFormat> getFormatInfo() {
        Map<String, ExcelFormat> format = new HashMap<>();
        format.put("id", ExcelFormat.FORMAT_INTEGER);
        format.put("categoryId", ExcelFormat.FORMAT_INTEGER);
        format.put("branchId", ExcelFormat.FORMAT_INTEGER);
        format.put("shopId", ExcelFormat.FORMAT_INTEGER);
        format.put("price", ExcelFormat.FORMAT_DOUBLE);
        format.put("stock", ExcelFormat.FORMAT_INTEGER);
        format.put("salesNum", ExcelFormat.FORMAT_INTEGER);
        format.put("isDel", ExcelFormat.FORMAT_INTEGER);
        return format;
    }
複製程式碼

實現效果

如何優雅的匯出Excel

原始碼分析

哈哈，自己分析自己的程式碼，有點意思。由於不方便貼出太多的程式碼，大家可以先到github上clone原始碼，再回來閱讀文章。✨原始碼地址✨ LZ使用的poi 4.0.1版本的這個工具，想要實用海量資料的匯出自然得使用SXSSFWorkbook這個元件。關於poi的具體用法在這裡我就不多說了，這裡主要是給大家講解如何對poi進行封裝使用。

成員變數

我們重點看ExcelUtils這個類，這個類是實現匯出的核心，先來看一下三個成員變數。

    private List list;
    private List<ExcelHeaderInfo> excelHeaderInfos;
    private Map<String, ExcelFormat> formatInfo;
複製程式碼
list

該成員變數用來儲存待匯出的資料。

ExcelHeaderInfo

該成員變數主要用來儲存表頭資訊，因為我們需要定義多個表頭資訊，所以需要使用一個列表來儲存，ExcelHeaderInfo建構函式如下 ExcelHeaderInfo(int firstRow, int lastRow, int firstCol, int lastCol, String title)

  • firstRow：該表頭所佔位置的首行
  • lastRow：該表頭所佔位置的尾行
  • firstCol：該表頭所佔位置的首列
  • lastCol：該表頭所佔位置的尾行
  • title：該表頭的名稱
ExcelFormat

該引數主要用來格式化欄位，我們需要預先約定好轉換成那種格式，不能隨使用者自己定。所以我們定義了一個列舉型別的變數，該列舉類只有一個字串型別成員變數，用來儲存想要轉換的格式，例如FORMAT_INTEGER就是轉換成整型。因為我們需要接受多個欄位的轉換格式，所以定義了一個Map型別來接收，該引數可以省略（預設格式為字串）。

public enum ExcelFormat {

    FORMAT_INTEGER("INTEGER"),
    FORMAT_DOUBLE("DOUBLE"),
    FORMAT_PERCENT("PERCENT"),
    FORMAT_DATE("DATE");

    private String value;

    ExcelFormat(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }
}
複製程式碼

核心方法

1. 建立表頭

該方法用來初始化表頭，而建立表頭最關鍵的就是poi中Sheet類的addMergedRegion(CellRangeAddress var1)方法，該方法用於單元格融合。我們會遍歷ExcelHeaderInfo列表，按照每個ExcelHeaderInfo的座標資訊進行單元格融合，然後在融合之後的每個單元首行和首列的位置建立單元格，然後為單元格賦值即可，通過上面的步驟就完成了任意型別的表頭設定。

    // 建立表頭
    private void createHeader(Sheet sheet, CellStyle style) {
        for (ExcelHeaderInfo excelHeaderInfo : excelHeaderInfos) {
            Integer lastRow = excelHeaderInfo.getLastRow();
            Integer firstRow = excelHeaderInfo.getFirstRow();
            Integer lastCol = excelHeaderInfo.getLastCol();
            Integer firstCol = excelHeaderInfo.getFirstCol();

            // 行距或者列距大於0才進行單元格融合
            if ((lastRow - firstRow) != 0 || (lastCol - firstCol) != 0) {
                sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
            }
            // 獲取當前表頭的首行位置
            Row row = sheet.getRow(firstRow);
            // 在表頭的首行與首列位置建立一個新的單元格
            Cell cell = row.createCell(firstCol);
            // 賦值單元格
            cell.setCellValue(excelHeaderInfo.getTitle());
            cell.setCellStyle(style);
            sheet.setColumnWidth(firstCol, sheet.getColumnWidth(firstCol) * 17 / 12);
        }
    }
複製程式碼
2. 轉換資料

在進行正文賦值之前，我們先要對原始資料列表轉換成字串的二維陣列，之所以轉成字串格式是因為可以統一的處理各種型別，之後有需要我們再轉換回來即可。

    // 將原始資料轉成二維陣列
    private String[][] transformData() {
        int dataSize = this.list.size();
        String[][] datas = new String[dataSize][];
        // 獲取報表的列數
        Field[] fields = list.get(0).getClass().getDeclaredFields();
        // 獲取實體類的欄位名稱陣列
        List<String> columnNames = this.getBeanProperty(fields);
        for (int i = 0; i < dataSize; i++) {
            datas[i] = new String[fields.length];
            for (int j = 0; j < fields.length; j++) {
                try {
                    // 賦值
                    datas[i][j] = BeanUtils.getProperty(list.get(i), columnNames.get(j));
                } catch (Exception e) {
                    LOGGER.error("獲取物件屬性值失敗");
                    e.printStackTrace();
                }
            }
        }
        return datas;
    }
複製程式碼

這個方法中我們通過使用反射技術，很巧妙的實現了任意型別的資料匯出（這裡的任意型別指的是任意的報表型別，不同的報表，匯出的資料肯定是不一樣的，那麼在Java實現中的實體類肯定也是不一樣的）。要想將一個List轉換成相應的二維陣列，我們得知道如下的資訊:

  • 二維陣列的列數
  • 二維陣列的行數
  • 二維陣列每個元素的值

如果獲取以上三個資訊呢？

  • 通過反射中的Field[] getDeclaredFields()這個方法獲取實體類的所有欄位，從而間接知道一共有多少列
  • List的大小不就是二維陣列的行數了嘛
  • 雖然每個實體類的欄位名不一樣，那麼我們就真的無法獲取到實體類某個欄位的值了嗎？不是的，你要知道，你擁有了反射，你就相當於擁有了全世界，那還有什麼做不到的呢。這裡我們沒有直接使用反射，而是使用了一個叫做BeanUtils的工具，該工具可以很方便的幫助我們對一個實體類進行欄位的賦值與欄位值的獲取。很簡單，通過BeanUtils.getProperty(list.get(i), columnNames.get(j))這一行程式碼，我們就獲取了實體list.get(i)中名稱為columnNames.get(j)這個欄位的值。list.get(i)當然是我們遍歷原始資料的實體類，而columnNames列表則是一個實體類所有欄位名的陣列，也是通過反射的方法獲取到的，具體實現可以參考LZ的原始碼。
3. 賦值正文

這裡的正文指定是正式的表格資料內容，其實這一些沒有太多的奇淫技巧，主要的功能在上面已經實現了，這裡主要是進行單元格的賦值與匯出格式的處理（主要是為了匯出excel後可以進行方便的運算）。

    // 建立正文
    private void createContent(Row row, CellStyle style, String[][] content, int i, Field[] fields) {
        List<String> columnNames = getBeanProperty(fields);
        for (int j = 0; j < columnNames.size(); j++) {
            if (formatInfo == null) {
                row.createCell(j).setCellValue(content[i][j]);
                continue;
            }
            if (formatInfo.containsKey(columnNames.get(j))) {
                switch (formatInfo.get(columnNames.get(j)).getValue()) {
                    case "DOUBLE":
                        row.createCell(j).setCellValue(Double.parseDouble(content[i][j]));
                        break;
                    case "INTEGER":
                        row.createCell(j).setCellValue(Integer.parseInt(content[i][j]));
                        break;
                    case "PERCENT":
                        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
                        Cell cell = row.createCell(j);
                        cell.setCellStyle(style);
                        cell.setCellValue(Double.parseDouble(content[i][j]));
                        break;
                    case "DATE":
                        row.createCell(j).setCellValue(this.parseDate(content[i][j]));
                }
            } else {
                row.createCell(j).setCellValue(content[i][j]);
            }
        }
    }
複製程式碼

匯出工具類的核心方法就差不多說完了，下面說一下關於多執行緒查詢的問題。

多扯兩點

1. 多執行緒查詢資料

理想很豐滿，現實還是有點骨感的。LZ雖然對50w的資料分別建立20個執行緒去查詢，但是總體的效率並不是50w/20，而是僅僅快了幾秒鐘，知道原因的小夥伴可以給我留個言一起探討一下。

下面先說說具體思路：因為多個執行緒之間是同時執行的，你不能夠保證哪個執行緒先執行完畢，但是我們卻得保證資料順序的一致性。在這裡我們使用了Callable介面，通過實現Callable介面的執行緒可以擁有返回值，我們獲取到所有子執行緒的查詢結果，然後合併到一個結果集中即可。那麼如何保證合併的順序呢?我們先建立了一個FutureTask型別的List，該FutureTask的型別就是返回的結果集。

List<FutureTask<List<TtlProductInfoPo>>> tasks = new ArrayList<>();
複製程式碼

當我們每啟動一個執行緒的時候，就將該執行緒的FutureTask新增到tasks列表中，這樣tasks列表中的元素順序就是我們啟動執行緒的順序。

           FutureTask<List<TtlProductInfoPo>> task = new FutureTask<>(new listThread(map));
            log.info("開始查詢第{}條開始的{}條記錄", i * THREAD_MAX_ROW, THREAD_MAX_ROW);
            new Thread(task).start();
            // 將任務新增到tasks列表中
            tasks.add(task);
複製程式碼

接下來，就是順序塞值了，我們按順序從tasks列表中取出FutureTask，然後執行FutureTask的get()方法，該方法會阻塞呼叫它的執行緒，知道拿到返回結果。這樣一套迴圈下來，就完成了所有資料的按順序儲存。

       for (FutureTask<List<TtlProductInfoPo>> task : tasks) {
            try {
                productInfoPos.addAll(task.get());
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
複製程式碼

2. 如何解決介面超時

如果需要匯出海量資料，可能會存在一個問題：介面超時，主要原因就是整個匯出過程的時間太長了。其實也很好解決，介面的響應時間太長，我們縮短響應時間不就可以了嘛。我們使用非同步程式設計解決方案，非同步程式設計的實現方式有很多，這裡我們使用最簡單的spring中的Async註解，加上了這個註解的方法可以立馬返回響應結果。關於註解的使用方式，大家可以自己查閱一下，下面講一下關鍵的實現步驟：

  1. 編寫非同步介面，該介面負責接收客戶端的匯出請求，然後開始執行匯出（注意：這裡的匯出不是直接向客戶端返回，而是下載到伺服器本地），只要下達了匯出指令，就可以馬上給客戶端返回一個該excel檔案的唯一標誌（用於以後查詢該檔案），介面結束。
  2. 編寫excel狀態介面，客戶端拿到excel檔案的唯一標誌之後，開始每秒輪詢呼叫該介面檢查excel檔案的匯出狀態
  3. 編寫從伺服器本地返回excel檔案介面，如果客戶端檢查到excel已經成功下載到到伺服器本地，這個時候就可以請求該介面直接下載檔案了。

這樣就可以解決介面超時的問題了。

原始碼地址

github.com/dearKundy/e…

原始碼服用姿勢

  1. 建表（資料自己插入哦）
CREATE TABLE `ttl_product_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '記錄唯一標識',
  `product_name` varchar(50) NOT NULL COMMENT '商品名稱',
  `category_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '型別ID',
  `category_name` varchar(50) NOT NULL COMMENT '冗餘分類名稱-避免跨表join',
  `branch_id` bigint(20) NOT NULL COMMENT '品牌ID',
  `branch_name` varchar(50) NOT NULL COMMENT '冗餘品牌名稱-避免跨表join',
  `shop_id` bigint(20) NOT NULL COMMENT '商品ID',
  `shop_name` varchar(50) NOT NULL COMMENT '冗餘商店名稱-避免跨表join',
  `price` decimal(10,2) NOT NULL COMMENT '商品當前價格-屬於熱點資料，而且價格變化需要記錄，需要價格詳情表',
  `stock` int(11) NOT NULL COMMENT '庫存-熱點資料',
  `sales_num` int(11) NOT NULL COMMENT '銷量',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '插入時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `is_del` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '記錄是否已經刪除',
  PRIMARY KEY (`id`),
  KEY `idx_shop_category_salesnum` (`shop_id`,`category_id`,`sales_num`),
  KEY `idx_category_branch_price` (`category_id`,`branch_id`,`price`),
  KEY `idx_productname` (`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=15000001 DEFAULT CHARSET=utf8 COMMENT='商品資訊表';
複製程式碼
  1. 執行程式
  2. 在瀏覽器的位址列輸入：http://localhost:8080/api/excelUtils/export即可完成下載

拉票環節

本次文章就寫到這裡啦，喜歡的朋友可以點贊、評論、加關注哦！

相關文章