JS Large Dataset Export

Version

Application Scenarios

Exporting large amounts of data will cause pressures on the server, network transmission, and database.

To solve the problem, FineReport 11.0 added the Large Dataset Export function, which allows directly exporting the needed dataset results.

Functions

Large Dataset Export is a resource-efficient and fast Excel export method, which supports back-end streaming export requiring no front-end data display.

The function is mainly for the detail table. You can use custom JavaScript codes to call the interface and directly export data requiring no report calculations.

The implementation logic is as follows:

1. Use SXSSFWorkbook for fast streaming row export.

2. Use the producer-consumer pattern. One thread is used to retrieve data and store the rows in a queue, while another thread to read the rows and export them.

Interface Introduction

Large dataset export interface: directExportToExcel: function (dsName, fileName, params, colNames, forMat, enCoding)

Key	Value	Example
session ID	Specific sessionID	/
dsName	Dataset name	ds1
fileName	Name of the exported file  If not specified, the default format of Template Name-Dataset Name.xlsx is used.	Sales
params	Dataset parameter in JSON format Parameter name: Parameter value	{ ID: '9527', Name:'Stephen' }
colNames	Column names, separated by commas  If not specified, all fields in the dataset are used.	col1,col2,...
forMat Note: Added in FineReport 11.0.10 and later versions.	Exported format  It only supports XLSX or CSV (can be omitted).  The default exported file format is Excel.	"xlsx" Note: If not set, it is " ".
enCoding Note: Added in FineReport 11.0.10 and later versions.	Encoding format value   (UTF-8 or GBK) It takes effect when you export CSV files.  The default is   UTF-8.	"UTF-8" Note: If not set, it is " ".

Interface example is as follows:

//The interface is directExportToExcel: function (dsName, fileName, params, colNames).
//Note that special characters in the parameters need to be URL-encoded, such as braces, colons, and so on. 
var paramStr = encodeURIComponent("{param1:1,param2:\"21','22\",param3:\"text\",...}")
//When you transfer parameters in a dataset, string parameters should be written in the format \"text\" var colNames = encodeURIComponent("col1, col2, col3,...").
//Specify the exported data columns, and exported fields are arranged in this order. If left empty, all fields are exported by default.
_g().directExportToExcel("Dataset name", "Exported file name", paramStr, colNames, forMat, enCoding)

Notes

1. The function only supports relational databases. For SQL Server databases, the cursor needs to be set as a server cursor.

2. In Oracle database, the default column names returned in SQL queries are in uppercase. Therefore, when you pass the colNames parameter in JavaScript, the letter case of the column names must be consistent with that of the default column names.

3. The function does not allow directly exporting empty values of date/datetime type. You need to add zeroDateTimeBehavior=convertToNull parameter after the JDBC data connection URL.

4. The amount of exported data should not exceed 10 million rows multiplied by 20 columns, because exporting a large amount of data may cause only part of data to be exported.

5. The exported Excel file is directly obtained from the database through SQL statements, not the data displayed in the report, so the data format and other settings in the report cannot be exported.

6. The function is not supported on mobile terminals.

7. A progress bar is displayed when the export takes more than 5 seconds.

8. CPTX templates are not supported, namely the reports created with Engine-X in FineReport 10.0.

Creating a Template

Creating a Dataset

Create an ordinary report. Create a dataset first, and the SQL statement is as follows:

ds1: SELECT * FROM Sales where 1=1 and Region in ('${area}') and Salesperson in ('${stuff}')

ds2: SELECT Salesperson FROM Sales where Region in ('${area}')

Designing a Report

The style of the report body is shown in the following figure.

Setting Widgets for Query

Go to the parameter panel and add two Label widgets, two Drop-down Checkbox widgets, and one Query widget.

Region Widget

Select the first Drop-down Checkbox widget to set area as Widget Name and region: as Label Name. In Data Dictionary, select Region in the Sales_Volume table in the database FRDemo.

Select String as Return Value Type, and ',' as Delimiter.

Salesperson Widget

Select the second Drop-down Checkbox widget to set stuff as Widget Name and Salesperson: as Label Name. In Data Dictionary, select Salesperson in the ds2 dataset.

Select String as Return Value Type, and ',' as Delimiter.

Setting Widgets for Export

Adding a Widget

Add a Button widget, choose Widget Settings > Attributes, and set Large Dataset Export as Button Name.

Setting a Click Event

Select the Button widget, choose Widget Settings > Attributes, add a Click Event, and enter JavaScript statements.

JavaScript codes are as follows:

//The Interface is directExportToExcel: function (dsName, fileName, params, colNames).
//Note that special characters in the parameters need to be URL-encoded, such as braces, colons, and so on.
var paramStr = encodeURIComponent("{area:\"California','New York\",stuff:\"Mike','John\"}")
//Transfer dataset parameters.
var colNames = encodeURIComponent("Region,Salesperson,Product_type,Product,Sales_Volume")
//Export the required data columns, and the exported fields are arranged in this order. If null, all are exported by default.
_g().directExportToExcel("ds1", "Sales_Volume", paramStr, colNames, "excel", " ")

Note:

1. The parameter name here is the name of parameters in the template, not the data column name in the dataset.

2. Add \ before and after SQL parameter values to prevent them from being parsed.

Demonstration

Save the template and click Pagination Preview. Click Large Dataset Export to export the ds1 dataset (California and New York as Region and John and Mike as Salesperson) to an Excel file.

The exported content is unrelated to the query content.

Note:

The function is not supported on mobile terminals.

Creating a Template

Creating a Dataset

Create an ordinary report. Create a dataset first, and the SQL statement is as follows:

ds1: SELECT * FROM Sales where 1=1 and Region in ('${area}') and Salesperson in ('${stuff}')

ds2: SELECT Salesperson FROM Sales where Region in ('${area}')

Designing a Report

The style of the report body is shown in the following figure.

Setting Widgets for Query

Go to the parameter panel and add two Label widgets, two Drop-down Checkbox widgets, and one Query widget.

Region Widget

Select the first Drop-down Checkbox widget to set area as Widget Name and region: as Label Name. In Data Dictionary, select Region in the Sales_Volume table in the database FRDemo. 

Select String as Return Value Type, and ',' as Delimiter.

Salesperson Widget

Select the second Drop-down Checkbox widget to set stuff as Widget Name and Salesperson: as Label Name. In Data Dictionary, select Salesperson in the ds2 dataset.

Select String as Return Value Type, and ',' as Delimiter.

Setting Dynamic Export Columns

Add a Label widget and a Drop-down Checkbox widget.

Select the Drop-down Checkbox widget, choose Widget Settings > Attributes, and set col as Widget Name and Exporting Column as Label Name.

In Data Dictionary, select Formula as Type and set TABLEDATAFIELDS("ds1") as Actual Value.

Setting Widgets for Export

Adding a Widget

Add a Button widget, choose Widget Settings > Attributes, and set Large Dataset Export as Button Name.

Setting a Click Event

Select the Button widget, choose Widget Settings > Attributes, add a Click Event, and enter JavaScript statements.

JavaScript codes are as follows:

var widgetNames = ['area', 'stuff']; //Define widget names of arrays.

function getWidgetValueByName(name) {
	var widget = _g().parameterEl.getWidgetByName(name); //Obtain widget values according to widget names.
	if (widget == undefined) return;
	var obj = {};
	obj[name] = widget.getValue();
	return obj; //Return arrays of widget values.
}
var paramJson = widgetNames.map(getWidgetValueByName).reduce(function(a, b) {
	return Object.assign(a, b)
});

var paramJsonStr = JSON.stringify(paramJson); //Converse JSON data into strings.
var col = _g().getParameterContainer().getWidgetByName("col").getValue();
//alert(col);
//URL-encode the parameters.
var colNames = encodeURIComponent(col)
//var colNames = encodeURIComponent("Region,Salesperson,Product_Type,Product,Product_Volume") //Export the required data columns,and the exported fields are arranged in this order. If null, all are exported by default.
//Call the export interface.
//console.log(paramJsonStr);
//console.log(colNames);
_g().directExportToExcel("ds1", "Sales_Volume", encodeURIComponent(paramJsonStr), colNames," "," ");
Show Code

Demonstration

Save the template and click Pagination Preview. Query Region and Salesperson, select the data columns to be exported, and click Large Dataset Export.

The export content is consistent with the query content, and only the specified data columns are exported.

Note:

The function is not supported on mobile terminals.

Example One

Click to download the template: Exporting Large Amounts of Data with Fixed Parameter Values.cpt

Example Two

Click to download the template: Exporting Large Amounts of Data with Dynamic Parameter Values.cpt

If you perform Large Dataset Export based on a Presto database connection, and the export fails with the following error message displayed:

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

You need to use presto-jdbc-339.jar to upgrade the driver. Go to the Presto official website and download the driver as shown in the following figure.