土製Excel匯入匯出及相關問題探討

funnyZpC發表於2019-02-18

轉載請註明出處https://www.cnblogs.com/funnyzpc/p/10392085.html

新的一年，又一個開始，不見收穫，卻見年齡，好一個豬年，待我先來一首里爾克的詩:

《沉重的時刻》(里爾克)

    此刻有誰在世上某處哭，無緣無故在世上哭，在哭我。
    此刻有誰在夜間某處笑，無緣無故在夜間笑，在笑我。
    此刻有誰在世上某處走，無緣無故在世上走，走向我。
    此刻有誰在世上某處死，無緣無故在世上死，望著我。

ok,這次說說專案中經常用到的Excel匯出問題，目前就用到的可以操作Excel的技術(在java中)大致有兩類：

JXL
- 僅僅支援對xls的檔案讀寫，
- 僅包含Excel基礎api，比較老，很久不更新
- 讀寫速度還行，對於要求低同時相容性較好的推薦
POI
- 可支援xls、xlsx兩種格式的Excel檔案讀寫
  - HSSF：操作Excel 97(.xls)格式
  - XSSF：操作Excel 2007 OOXML (.xlsx)格式，操作EXCEL記憶體佔用高於HSSF
  - SXSSF: 從POI3.8 beta3開始支援，基於XSSF，低記憶體佔用。
- 技術較新，保留了最大相容性，可對Excel做複雜對資料極樣式處理
- 讀寫速度上 SXSSF快於XSSF ，HSSF速度同略遜於JXL

以上對於這兩種技術做了簡要對描述，在開發中，我們一般將POI作為首選，同時以上還可能存在一個問題是：大資料量匯出。大資料匯出，一般我們需要解決兩個問題：

大資料量讀寫容易造成記憶體不足問題
長時讀寫容易造成客戶端請求超時，造成匯出失敗問題
大資料量處理耗時問題

對於以上幾個問題，解決思路大致有下：

資料庫資料查詢階段建議使用fetch分批次查詢，減少資料庫壓力
單個檔案讀寫建議使用SXSSF，以減少記憶體佔用
對於單個sheet超過十萬的建議分sheet做多執行緒寫入,這裡分享一個網友寫的Demo
- example：CreateMultipleSheetDemo.java
對於POI寫入效率的問題官方給了個Demo，這個例子大致是使用xml文件拼接的方式+xml檔案壓縮的方式
- example：BigGridDemo.java

ok，對於以上核心問題個人都嘗試過，憚於目前專案進度較為緊張，個人做了個限制匯出處理，以避免(客戶端請求)超時問題，說白了就是將問題扔給下一期去集中解決。

不考慮以上所說的問題，個人花了些許時間寫了兩個Excel 工具類：ExcelReadUtil以及ExcelWriteUtil (程式碼地址見篇尾)，集中處理了包含單不僅限於以下問題：

物件列表("List")型別資料匯出問題
資料分sheet問題
表頭字型極單元格內換行問題
資料單元格多樣式問題
資料單元格多資料型別及格式化問題
單元格列寬度調整問題
大資料拆分問題
通用物件匯出問題(使用泛型)

當然，解決這些問題也查閱了不少官方資料，同時也做了大量的測試才得以投入正式專案使用,在這中間也跳過一些坑，接下來我就講一講我在開發所跳過的坑,這些坑均是相對於上一個版本而言的：

使用通用泛型接收引數物件問題
一開始(在上一個專案)做了個初稿，在呼叫匯出方法時直接傳入固定型別物件，一開始這樣：

            toXlsxByMap(List<Map<String,Object>> dataList,
                                  String[] headerNames,
                                  String[] cellNames,
                                  CellFmt[] cellFmts)

導致的問題是傳入的dataList內部的Map的value必須是Object型別，之後鑽研了下java泛型，使用這種方式輕鬆解決問題！

        public static <V extends Object> File toXlsxByMap(List<Map<String,V>> dataList,
                                  String[] headerNames,
                                  String[] cellNames,
                                  CellFmt[] cellFmts)

分資料分sheet問題
這個問題其實很簡單，就是先資料分組，然後迴圈每組資料時再createSheet，程式碼片段：

           //資料分組
          List<List<Map<String, V>>> mData = splitMapList(dataList);
                    //迴圈每組資料 並建立sheet>寫單元格
                    for (List<Map<String, V>> subList : mData) {
                        //第一個sheet 引數(sheet名稱,sheet的序號)
                        sheet = workbook.createSheet(String.format("%s~%s",
                                (dataList.size() > DATA_SPLIT_GROP_SIZE ?
                                        mData.indexOf(subList) * DATA_SPLIT_GROP_SIZE + 1
                                        : 0) + "",
                                (dataList.size() > DATA_SPLIT_GROP_SIZE ?
                                        (mData.size() == (mData.indexOf(subList) + 1) ? dataList.size() : DATA_SPLIT_GROP_SIZE * (mData.indexOf(subList) + 1))
                                        : dataList.size()) + "")
                        );
                        LOGGER.info(">>>sheet name : {}",sheet.getSheetName());
                        PoiCellProcess.writeHeaderCell(sheet,headerCellStyle,headerNames);
                        PoiCellProcess.writeBodyCellByMap(sheet,bodyCellStyle,cellNames,subList, cellFmts);
                    }

單元格內換行問題
其實這是個小問題，只需給CellStyle設定一個setWrapText(true)，大致邏輯這樣：

    public static CellStyle headerCellStyle(SXSSFWorkbook wb){
        CellStyle headerStyle = wb.createCellStyle();
        //...some code
        //允許單元格內換行
        headerStyle.setWrapText(true);
        return  headerStyle;
    }

單元格型別及格式處理問題

這個問題其實分為多個，而且密切相關，大致有這幾個：
- 單元格樣式類
- 單元格樣式類
- 單元格資料型別
- 單元格寫入資料格式

但是，處理了這幾個問題其實還不夠完美
至於不完美的原因是什麼呢，一個是Excel資料格式與java資料格式不一致(這個體現在日期，長數字，小數的處理上)，比如你要格式化的日期後為“yyyy-mm-dd” 這種型別，
但是在Excel中相近的格式型別只有這樣“yyyy/M/d”，如果強制單元格樣式型別為“yyyy-mm-dd HH24:mi:ss” 其實也是可以的，只不過會變成自定義格式，而且是Excel的自定義格式,
具體如下圖：

土製Excel匯入匯出及相關問題探討

另外一個問題是單元格型別與程式語言的資料型別相異同時與poi所能提供的資料型別也相異，如圖：

土製Excel匯入匯出及相關問題探討

列寬調整問題
記得在第一版的時候列寬問題其實並不重要，遂就做個了固定長度
在第二版的時候為了保證可以動態調整列寬，就剔除了第一版的固定長度處理，將長度資料作為一個Integer陣列傳入
由於第二版先期已經投入開發中了，再在方法裡面加入長度陣列實感覺不合適，於是，想了個用程式碼做動態列寬，這裡實現的思路大致有下：
- 由於表頭也是作為一個引數傳入的，所以將表頭字元個數作為欄位倍數長度，資料行過長時將表頭欄位新增適當個數的空格即可(資料傳入的時候)
- 實際顯示的時候由於存在單元格內換行問題，所以在程式碼處理的時候先判斷換行，所以：
  - 有換行時單元格列寬=基準長度(自己定義的單字元長度)*字元個數/2
  - 無換行時單元格列寬=基準長度(自己定義的單字元長度)*字元個數
    這是最終的程式碼：
```
  public static void writeHeaderCell(SXSSFSheet sheet, CellStyle headerCellStyle, String[] headerNames) {
  SXSSFRow row = sheet.createRow(0);
  row.setHeight((short) 30);
  row.setHeightInPoints((short) 30);
  SXSSFCell headerCell;
  for (int i = 0; i < headerNames.length; i++) {
      headerCell = row.createCell(i);
      headerCell.setCellStyle(headerCellStyle);
      headerCell.setCellValue(headerNames[i]);
      sheet.setColumnWidth(i,
              null == headerNames[i] ? CELL_BASE_LENGTH
                      : (headerNames[i].contains("\r\n") ? CELL_CHARSET_LENGTH * headerNames[i].length() / 2
                      : CELL_CHARSET_LENGTH * headerNames[i].length()));
  }
  }
```
  這是最終處理的結果：

物件匯出問題
這個問題耗時較多，由於我的同事所處理的源資料是這樣子 "List" ，這樣做其實有個很大的問題就是 java程式碼沒法動態針對不同物件做getter和setter處理，遂每一個匯出功能就需要單獨寫poi的匯出邏輯，緩慢而且耽擱開發進度，這個時候在寫第二版的時候徹底優化了，這裡的思路和注意事項大致有下：

利用反射動態獲取欄位資料，這裡是不得已而為之(其實jvm做頻繁反射處理時並不慢)
反射處理時必需要將最終資料排序，不然迴圈 Field[] 獲取到的資料結果並不一定與表頭欄位資料一致
這裡是最終程式碼：

     public static Object[] fieldValues(final Object obj, final String[] fieldNames,Object[] valueList) {
      for (Class<?> superClass = obj.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
          try {
              Field[] fields = superClass.getDeclaredFields();
              for(int k=0;k<fields.length;k++){
                  if ((!Modifier.isPublic(fields[k].getModifiers()) ||
                          !Modifier.isPublic(fields[k].getDeclaringClass().getModifiers()) ||
                          Modifier.isFinal(fields[k].getModifiers())) &&
                          !fields[k].isAccessible()) {
                      fields[k].setAccessible(true);
                  }
                  /**
                   * 需要排序，否則順序不一致
                   */
                  for(int j=0;j<fieldNames.length;j++){
                      if(fields[k].getName().equals(fieldNames[j])){
                          valueList[j] = fields[k].get(obj);
                          break;
                      }
                  }
              }
              return valueList;
          } catch (Exception e) {
              e.printStackTrace();
          }
      }
      //這裡新增一個,否則陣列越界
      return new Object[fieldNames.length];
  }