JS實現大數據集導出Excel

1.1 版本

1.2 應用場景

大批量數據導出的時候，會對服務器、網絡傳輸、數據庫造成一定的壓力。

爲了防止這樣的風險，FineReport 10.0 新增了「大數據集導出」的功能，可直接根據數據集結果進行導出。

1.3 功能描述

「大數據集導出」是一種占用資源少且速度快的 Excel 導出方式，無需前台數據展示即可進行後台流式導出。

該功能主要是針對明細表，用戶通過自定義 JavaScrpit 代碼調用接口，實現跳過報表計算直接取數導出。實現原理如下：

1）使用 SXSSFWorkbook 流式行導出，速度快。

2）使用生産者消費者模式，一個線程用於取數，把數據行存在隊列中，另一線程讀取行導出。

1.4 接口簡介

大數據集導出接口：directExportToExcel: function (dsName, fileName, params, colNames)

接口示例如下：

//接口爲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)

1.5 注意事項

1）此功能只支持關系型數據庫。且 SQL Server 數據庫需要把遊标設置爲服務器遊标。

2）Oracle 數據庫，SQL 查詢時默認返回的列名是大寫，所以 JS 傳入 colNames 參數時，列名大小寫要與其保持一緻。

3）此功能無法直接導出 date/datetime 型空值，需要在 JDBC 數據連接的 URL 後添加 zeroDateTimeBehavior=convertToNull 參數。

4）建議導出的數據量不超過「1000W 行 * 20 列」，數據量超大可能會導緻僅導出部分數據。

5）導出的 Excel 是通過 SQL 語句直接從數據庫中獲取的數據，并非報表中的數據，因此報表中設置的數據格式等無法被導出。

6）此功能不支持移動端。

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 代碼如下：

//接口爲directExportToExcel: function (dsName, fileName, params, colNames)
//注意參數中的特殊字符需要進行url編碼，比如大括号，冒号等
var paramStr = encodeURIComponent("{area:\"華北','華東\",stuff:\"孫林','王偉\"}")
//數據集傳參，字符串參數建議寫成格式\"text\"，參數多值寫法爲\"text1','text2\"
var colNames = encodeURIComponent("地區,銷售員,産品類型,産品,銷量")
//指定導出的數據列，導出字段按此順序排列，爲空默認導出所有
_g().directExportToExcel("ds1", "銷量", paramStr, colNames)

注1：這裏的參數名是指模板參數名，而不是數據集中的數據列名。

注2：在 SQL 參數值的前後需要加上 \ ，防止被解析。

2.4 效果預覽

保存模板，點擊「分頁預覽」。點擊「大數據集導出按鈕」，可以将「地區爲華北、華東且銷售員爲孫偉、王林」的 ds1 數據集導出爲 Excel 文件。導出内容與查詢内容無關，如下圖所示：

注：不支持移動端。

3.1 新建模板

2.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 銷售員控件

選中第二個「下拉複選框控件」，設置控件名稱爲「」，标簽名稱爲「銷售員：」，數據字典設置爲數據集 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; 
	//返回控件值組成的數組
}
//将參數拼接起來，若新增參數，直接 widgetNames中此增加控件名即可，此處無需修改。
var paramJson = widgetNames.map(getWidgetValueByName).reduce(function(a, b) {
	return Object.assign(a, b)
});
var paramJsonStr = JSON.stringify(paramJson); 
//将JSON數據轉換爲字符串
var col = this.options.form.getWidgetByName("col").getValue();
//alert(col);
//參數進行URL編碼
var colNames = encodeURIComponent(col)
//var colNames = encodeURIComponent("地區,銷售員,産品類型,産品,銷量")
//指定導出的數據列，導出字段按此順序排列，爲空默認導出所有
//調用導出接口
//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官網 ，下載驅動的方法如下圖所示：