海量數(shù)據(jù)下如何使用多線程的導出 Excel

前言

公司項目最近有一個需要：報表導出。整個系統(tǒng)下來，起碼超過一百張報表需要導出。這個時候如何優(yōu)雅的實現(xiàn)報表導出，釋放生產(chǎn)力就顯得很重要了。下面主要給大家分享一下該工具類的使用方法與實現(xiàn)思路。

實現(xiàn)的功能點

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

• 導出任意類型的數(shù)據(jù)
• 自由設(shè)置表頭
• 自由設(shè)置字段的導出格式

使用實例

上面說到了本工具類實現(xiàn)了三個功能點，自然在使用的時候設(shè)置好這三個要點即可：

• 設(shè)置數(shù)據(jù)列表
• 設(shè)置表頭
• 設(shè)置字段格式

下面的export函數(shù)可以直接向客戶端返回一個excel數(shù)據(jù)，其中productInfoPos為待導出的數(shù)據(jù)列表，ExcelHeaderInfo用來保存表頭信息，包括表頭名稱，表頭的首列，尾列，首行，尾行。

因為默認導出的數(shù)據(jù)格式都是字符串型，所以還需要一個Map參數(shù)用來指定某個字段的格式化類型（例如數(shù)字類型，小數(shù)類型、日期類型）。這里大家知道個大概怎么使用就好了，下面會對這些參數(shù)進行詳細解釋

@Override
????public?void?export(HttpServletResponse?response,?String?fileName)?{
????????//?待導出數(shù)據(jù)
????????List?productInfoPos?=?this.multiThreadListProduct();
????????ExcelUtils?excelUtils?=?new?ExcelUtils(productInfoPos,?getHeaderInfo(),?getFormatInfo());
????????excelUtils.sendHttpResponse(response,?fileName,?excelUtils.getWorkbook());
????}

????//?獲取表頭信息
????private?List?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,?"記錄是否已經(jīng)刪除")
????????);
????}

????//?獲取格式化信息
????private?Map?getFormatInfo()?{
????????Map?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;
????}

實現(xiàn)效果

源碼分析

哈哈，自己分析自己的代碼，有點意思。由于不方便貼出太多的代碼，大家可以先到github上clone源碼，再回來閱讀文章。

https://github.com/dearKundy/excel-utils

LZ使用的poi 4.0.1版本的這個工具，想要實用海量數(shù)據(jù)的導出自然得使用SXSSFWorkbook這個組件。關(guān)于poi的具體用法在這里我就不多說了，這里主要是給大家講解如何對poi進行封裝使用。

成員變量

我們重點看ExcelUtils這個類，這個類是實現(xiàn)導出的核心，先來看一下三個成員變量

????private?List?list;
????private?List?excelHeaderInfos;
????private?Map?formatInfo;

list

該成員變量用來保存待導出的數(shù)據(jù)

ExcelHeaderInfo

該成員變量主要用來保存表頭信息，因為我們需要定義多個表頭信息，所以需要使用一個列表來保存，ExcelHeaderInfo構(gòu)造函數(shù)如下ExcelHeaderInfo(int firstRow, int lastRow, int firstCol, int lastCol, String title)

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

ExcelFormat

該參數(shù)主要用來格式化字段，我們需要預先約定好轉(zhuǎn)換成那種格式，不能隨用戶自己定。所以我們定義了一個枚舉類型的變量，該枚舉類只有一個字符串類型成員變量，用來保存想要轉(zhuǎn)換的格式，例如FORMAT_INTEGER就是轉(zhuǎn)換成整型。

因為我們需要接受多個字段的轉(zhuǎn)換格式，所以定義了一個Map類型來接收，該參數(shù)可以省略（默認格式為字符串）

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. 創(chuàng)建表頭

該方法用來初始化表頭，而創(chuàng)建表頭最關(guān)鍵的就是poi中Sheet類的addMergedRegion(CellRangeAddress var1)方法，該方法用于單元格融合。

我們會遍歷ExcelHeaderInfo列表，按照每個ExcelHeaderInfo的坐標信息進行單元格融合，然后在融合之后的每個單元首行和首列的位置創(chuàng)建單元格，然后為單元格賦值即可，通過上面的步驟就完成了任意類型的表頭設(shè)置。

????//?創(chuàng)建表頭
????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);
????????????//?在表頭的首行與首列位置創(chuàng)建一個新的單元格
????????????Cell?cell?=?row.createCell(firstCol);
????????????//?賦值單元格
????????????cell.setCellValue(excelHeaderInfo.getTitle());
????????????cell.setCellStyle(style);
????????????sheet.setColumnWidth(firstCol,?sheet.getColumnWidth(firstCol)?*?17?/?12);
????????}
????}

2. 轉(zhuǎn)換數(shù)據(jù)

在進行正文賦值之前，我們先要對原始數(shù)據(jù)列表轉(zhuǎn)換成字符串的二維數(shù)組，之所以轉(zhuǎn)成字符串格式是因為可以統(tǒng)一的處理各種類型，之后有需要我們再轉(zhuǎn)換回來即可。

????//?將原始數(shù)據(jù)轉(zhuǎn)成二維數(shù)組
????private?String[][]?transformData()?{
????????int?dataSize?=?this.list.size();
????????String[][]?datas?=?new?String[dataSize][];
????????//?獲取報表的列數(shù)
????????Field[]?fields?=?list.get(0).getClass().getDeclaredFields();
????????//?獲取實體類的字段名稱數(shù)組
????????List?columnNames?=?this.getBeanProperty(fields);
????????for?(int?i?=?0;?i?????????????datas[i]?=?new?String[fields.length];
????????????for?(int?j?=?0;?j?????????????????try?{
????????????????????//?賦值
????????????????????datas[i][j]?=?BeanUtils.getProperty(list.get(i),?columnNames.get(j));
????????????????}?catch?(Exception?e)?{
????????????????????LOGGER.error("獲取對象屬性值失敗");
????????????????????e.printStackTrace();
????????????????}
????????????}
????????}
????????return?datas;
????}

這個方法中我們通過使用反射技術(shù)，很巧妙的實現(xiàn)了任意類型的數(shù)據(jù)導出（這里的任意類型指的是任意的報表類型，不同的報表，導出的數(shù)據(jù)肯定是不一樣的，那么在Java實現(xiàn)中的實體類肯定也是不一樣的）。要想將一個List轉(zhuǎn)換成相應的二維數(shù)組，我們得知道如下的信息；

• 二維數(shù)組的列數(shù)
• 二維數(shù)組的行數(shù)
• 二維數(shù)組每個元素的值

如果獲取以上三個信息呢？

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

3. 賦值正文

這里的正文指定是正式的表格數(shù)據(jù)內(nèi)容，其實這一些沒有太多的奇淫技巧，主要的功能在上面已經(jīng)實現(xiàn)了，這里主要是進行單元格的賦值與導出格式的處理（主要是為了導出excel后可以進行方便的運算）

????//?創(chuàng)建正文
????private?void?createContent(Row?row,?CellStyle?style,?String[][]?content,?int?i,?Field[]?fields)?{
????????List?columnNames?=?getBeanProperty(fields);
????????for?(int?j?=?0;?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]);
????????????}
????????}
????}

導出工具類的核心方法就差不多說完了，下面說一下關(guān)于多線程查詢的問題

多扯兩點

1. 多線程查詢數(shù)據(jù)

理想很豐滿，現(xiàn)實雖然不是很殘酷，但是也跟想象的不一樣。LZ雖然對50w的數(shù)據(jù)分別創(chuàng)建20個線程去查詢，但是總體的效率并不是50w/20，而是僅僅快了幾秒鐘，知道原因的小伙伴可以給我留個言一起探討一下。

下面先說說具體思路：因為多個線程之間是同時執(zhí)行的，你不能夠保證哪個線程先執(zhí)行完畢，但是我們卻得保證數(shù)據(jù)順序的一致性。在這里我們使用了Callable接口，通過實現(xiàn)Callable接口的線程可以擁有返回值，我們獲取到所有子線程的查詢結(jié)果，然后合并到一個結(jié)果集中即可。

那么如何保證合并的順序呢?我們先創(chuàng)建了一個FutureTask類型的List，該FutureTask的類型就是返回的結(jié)果集。

List>>?tasks?=?new?ArrayList<>();

當我們每啟動一個線程的時候，就將該線程的FutureTask添加到tasks列表中，這樣tasks列表中的元素順序就是我們啟動線程的順序。

???????????FutureTask>?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，然后執(zhí)行FutureTask的get()方法，該方法會阻塞調(diào)用它的線程，知道拿到返回結(jié)果。這樣一套循環(huán)下來，就完成了所有數(shù)據(jù)的按順序存儲。

???????for?(FutureTask>?task?:?tasks)?{
????????????try?{
????????????????productInfoPos.addAll(task.get());
????????????}?catch?(Exception?e)?{
????????????????e.printStackTrace();
????????????}
????????}

2. 如何解決接口超時

如果需要導出海量數(shù)據(jù)，可能會存在一個問題：接口超時，主要原因就是整個導出過程的時間太長了。

其實也很好解決，接口的響應時間太長，我們縮短響應時間不就可以了嘛。我們使用異步編程解決方案，異步編程的實現(xiàn)方式有很多，這里我們使用最簡單的spring中的Async注解，加上了這個注解的方法可以立馬返回響應結(jié)果。

關(guān)于注解的使用方式，大家可以自己查閱一下，下面講一下關(guān)鍵的實現(xiàn)步驟：

1. 編寫異步接口，該接口負責接收客戶端的導出請求，然后開始執(zhí)行導出（注意：這里的導出不是直接向客戶端返回，而是下載到服務器本地），只要下達了導出指令，就可以馬上給客戶端返回一個該excel文件的唯一標志（用于以后查找該文件），接口結(jié)束。
2. 編寫excel狀態(tài)接口，客戶端拿到excel文件的唯一標志之后，開始每秒輪詢調(diào)用該接口檢查excel文件的導出狀態(tài)
3. 編寫從服務器本地返回excel文件接口，如果客戶端檢查到excel已經(jīng)成功下載到到服務器本地，這個時候就可以請求該接口直接下載文件了。

這樣就可以解決接口超時的問題了。

源碼地址

https://github.com/dearKundy/excel-utils

源碼服用姿勢

1. 建表（數(shù)據(jù)自己插入哦）

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?'商品當前價格-屬于熱點數(shù)據(jù)，而且價格變化需要記錄，需要價格詳情表',
??`stock`?int(11)?NOT?NULL?COMMENT?'庫存-熱點數(shù)據(jù)',
??`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?'記錄是否已經(jīng)刪除',
??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='商品信息表';

2. 運行程序
3. 在瀏覽器的地址欄輸入：http://localhost:8080/api/excelUtils/export即可完成下載