JS實現大數據集匯出Excel

1.1 版本

1.2 應用場景

大批量資料匯出的時候，會對伺服器、網路傳輸、資料庫造成一定的壓力。
為了防止這樣的風險，FineReport 11.0 新增了「大數據集匯出」的功能，可直接根據資料集結果進行匯出。

1.3 功能描述

「大數據集匯出」是一種佔用資源少且速度快的 Excel 匯出方式，無需前臺資料展示即可進行後臺流式匯出。
該功能主要是針對明細表，使用者透過自訂 JavaScrpit 代碼呼叫API，實現跨越報表計算直接取數匯出。實現原理如下：
1）使用 SXSSFWorkbook 流式行匯出，速度快。
2）使用生產者消費者模式，一個執行緒用於取數，把資料行存在佇列中，另一執行緒讀取行匯出。

1.4 API簡介

大數據集匯出API：directExportToExcel: function (dsName, fileName, params, colNames，forMat，enCoding)

API範例如下：

//API為directExportToExcel: function (dsName, fileName, params, colNames)
//注意參數中的特殊字元需要進行url編碼，比如大括號，冒號等。
var paramStr = encodeURIComponent("{param1:1,param2:\"21','22\",param3:\"text\",...}")
//資料集傳參，字串參數建議寫成格式\"text\"
var colNames = encodeURIComponent("col1, col2, col3,...")
//指定匯出的資料列，匯出欄位按此順序排列，為空預設匯出所有
_g().directExportToExcel("資料集名稱", "匯出檔案名稱稱", paramStr, colNames, forMat, enCoding)

1.5 注意事項

1）此功能只支援關系型資料庫。且 SQL Server 資料庫需要把游標設定為伺服器游標。
2）Oracle 資料庫，SQL 查詢時預設傳回的列名是大寫，所以 JS 傳入 colNames 參數時，列名大小寫要與其保持一緻。
3）此功能無法直接匯出 date/datetime 型空值，需要在 JDBC 資料連結的 URL 後新增 zeroDateTimeBehavior=convertToNull 參數。
4）建議匯出的資料量不超過「1000W 行 * 20 列」，資料量超大可能會導緻僅匯出部分資料。
5）匯出的 Excel 是透過 SQL 語句直接從資料庫中獲取的資料，並非報表中的資料，因此報表中設定的資料格式等無法被匯出。
6）此功能不支援行動端。
7）匯出超過 5s 時顯示進度條。
8）不支援cptx範本（cptx 範本指原10.0版本使用新計算引擎製作的報表。）

2.1 建立範本

2.1.1 建立資料集

建立普通報表，建立資料集，SQL 語句如下：
ds1：SELECT * FROM 銷量 where 1=1 and 地區 in ('${area}') and 銷售員 in ('${stuff}')
ds2：SELECT 銷售員 FROM 銷量 where 地區 in ('${area}')

2.1.2 設計報表

報表主體樣式如下圖所示：

2.2 設定查詢元件

編輯參數面板，新增兩個「標簽元件」，兩個「下拉複選框元件」，一個「查詢元件」。如下圖所示：

2.2.1 地區元件

選中第一個「下拉複選框元件」，設定元件名稱為「area」，標簽章稱為「地區：」，資料字典設定為資料庫 FRDemo 中「銷量」表中的「地區」，傳回值型別為「字串」，分隔符為','，如下圖所示：

2.2.2 銷售員元件

選中第二個「下拉複選框元件」，設定元件名稱為「stuff」，標簽章稱為「銷售員：」，資料字典設定為資料集「ds2」中的「銷售員」，傳回值型別為「字串」，分隔符為','，如下圖所示：

2.3 設定匯出元件

2.3.1 新增元件

新增一個「按鈕元件」，點選「元件設定>屬性」，設定按鈕名稱為「大數據集匯出」，如下圖所示：

2.3.2 設定點選事件

選中「按鈕元件」，點選「元件設定>屬性」，新增「點選事件」，輸入 JavaScript 語句，如下圖所示：

JavaScript 代碼如下：

//API為directExportToExcel: function (dsName, fileName, params, colNames)
//注意參數中的特殊字元需要進行url編碼，比如大括號，冒號等。
var paramStr = encodeURIComponent("{area:\"華北','華東\",stuff:\"孫林','王偉\"}")
//資料集傳參
var colNames = encodeURIComponent("地區,銷售員,產品型別,產品,銷量")
//指定匯出的資料列，匯出欄位按此順序排列，為空預設匯出所有
_g().directExportToExcel("ds1", "銷量", paramStr, colNames, "excel", " ")

注1：這裏的參數名是指範本參數名，而不是資料集中的資料列名。
注2：在 SQL 參數值的前後需要加上 \ ，防止被解析。

2.4 效果預覽

儲存範本，點選「分頁預覽」。點選「大數據集匯出按鈕」，可以將「地區為華北、華東且銷售員為孫偉、王林」的 ds1 資料集匯出為 Excel 檔案。匯出內容與查詢內容無關，如下圖所示：

注：不支援行動端。

3.1 建立範本

3.1.1 建立資料集

建立普通報表，建立資料集，SQL 語句如下：
ds1：SELECT * FROM 銷量 where 1=1 and 地區 in ('${area}') and 銷售員 in ('${stuff}')
ds2：SELECT 銷售員 FROM 銷量 where 地區 in ('${area}')

3.1.2 設計報表

報表主體樣式如下圖所示：

3.2 設定查詢元件

編輯參數面板，新增兩個「標簽元件」，兩個「下拉複選框元件」，一個「查詢元件」。如下圖所示：

3.2.1 地區元件

選中第一個「下拉複選框元件」，設定元件名稱為「area」，標簽章稱為「地區：」，資料字典設定為資料庫 FRDemo 中「銷量」表中的「地區」，傳回值型別為「字串」，分隔符為','，如下圖所示：

3.2.2 銷售員元件

選中第二個「下拉複選框」元件，設定元件名稱為「stuff」，標簽章稱為「銷售員：」，資料字典設定為資料集 ds2 中的「銷售員」，傳回值型別為「字串」，分隔符為','，如下圖所示：

3.3 設定動態匯出列

新增一個「標簽元件」，一個「下拉複選框元件」。選中「下拉複選框元件」，點選「元件設定>屬性」，設定元件名稱為「col」，標簽章稱為「匯出列」，資料字典型別為「公式」，實際值為：TABLEDATAFIELDS("ds1")，如下圖所示：

3.4 設定匯出元件

3.4.1 新增元件

新增一個「按鈕元件」，點選「元件設定>屬性」，設定按鈕名稱為「大數據集匯出」，如下圖所示：

3.4.2 設定點選事件

選中「按鈕元件」，點選「元件設定>屬性」，新增「點選事件」，輸入 JavaScript 語句，如下圖所示：

JavaScript 代碼如下：

var widgetNames = ['area', 'stuff']; //定義陣列存放元件名稱

function getWidgetValueByName(name) {
	var widget = _g().parameterEl.getWidgetByName(name); //根據元件名獲取元件值
	if (widget == undefined) return;
	var obj = {};
	obj[name] = widget.getValue();
	return obj; //傳回元件值組成的陣列
}
var paramJson = widgetNames.map(getWidgetValueByName).reduce(function(a, b) {
	return Object.assign(a, b)
});//reduce會傳入回呼函式和初始值兩個參數，a為累積的物件，b為當前的元件值物件

var paramJsonStr = JSON.stringify(paramJson); //將json資料轉換為字串
var col = _g().getParameterContainer().getWidgetByName("col").getValue();
//alert(col);
//參數進行url編碼
var colNames = encodeURIComponent(col)
//var colNames = encodeURIComponent("地區,銷售員,產品型別,產品,銷量") //指定匯出的資料列，匯出欄位按此順序排列，為空預設匯出所有
//呼叫匯出API
//console.log(paramJsonStr);
//console.log(colNames);
_g().directExportToExcel("ds1", "銷量", encodeURIComponent(paramJsonStr), colNames," "," ");

3.5 效果預覽

儲存範本，點選「分頁預覽」。查詢「地區」、「銷售員」，選擇需要匯出的資料列，點選「大數據集匯出按鈕」，匯出內容與查詢內容一緻，且僅匯出指定的資料列。如下圖所示：

注：不支援行動端。

4.1 範例一

已完成範本可參見：%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc\Parameter\大數據集匯出固定參數值.cpt
點選下載範本：大數據量匯出固定參數值.cpt

4.2 範例二

已完成範本可參見：%FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc\Parameter\大數據集匯出動態參數值.cpt
點選下載範本：大數據量匯出動態參數值.cpt

問題描述：

大數據集匯出時，資料集依賴的資料連結為 Presto 資料庫，匯出失敗時報錯：

警告:14:39:00 EventThread ERROR [standard] com.facebook.presto.jdbc.NotImplementedException: Method Connection.prepareStatement is not yet implemented

解決方案：

將工程中的資料庫驅動升級到 presto-jdbc-339.jar 即可。

進入 Presto官網 ，下載驅動的方法如下圖所示：