Export Excel by sheet

1. Problem Description

When exporting from Excel, the user wants to realize the grouped sheet export, that is, the format of the report on each sheet page is the same, but the content is different. As shown in the figure below, sheet 1 is the sales profile in East China, and sheet 2 is the sales profile in North China.

2. Realization ideas

Add JS implementation: Add parameters that can realize pagination export to the report URL. The parameters include export format parameters and export pages parameters.

Different scenarios correspond to different implementation methods, such as the following two scenarios:

• Scenario 1: Parameter query report, the report content corresponding to each parameter value after export is on a sheet page.

• Scenario 2: Line report, after grouping pages, each group of content corresponds to a sheet page after exporting.

Using the template GettingStaredEN.cpt in the path %FR_HOME%\webapps\webroot\WEB-INF\reportlets example scenario 1, the Excel export effect is one sheet page per region.

1. Template Preparation

Open the template GettingStared.cpt and make some modifications to the template first. The revised content is as follows:

In cell A1, change the title to the formula $Region + "Region Sales Situation", and modify the report sheet name to the formula =A4, as shown in the following figure:

2. Realize pagination export

After adding the button in the parameter panel, add JS events to the button to realize the paged export by region.

1) Add export button

Add a button widget in the parameter panel and change the widget name to "Export Excel", as shown in the figure below:

2) Add JS code

Click the "Export Excel" button, and add a "click" event at "Widget Settings"> "Events", as shown in the following figure:

The JavaScript code is as follows:

Note: After adding the JS code, pay attention to adding event parameters, the parameter name: region, the value is the formula: sql("FRDemo","select distinct Region from Sales_Volume",1)

var url = 'report?reportlets=';//define url
var pars = '&format=excel&__filename__=1';//set format and filename
var path = "${reportName}";//get template and path
var json = [];
for (var i = 0; i < region.length; i++) {
	var sheet = {
		reportlet: path,
		region: region[i]
	};
	json.push(sheet);
}
jsonStr = encodeURIComponent(JSON.stringify(json));//url encodeURIComponent
url += jsonStr;
url += pars;
alert(url);
window.open(url);

3. Effect Preview

1) PC

Save the template, click "Pagination Preview", and click the "Export Excel" button on the report screen. After exporting, there will be one sheet page for each area. The effect is shown in section 1.

2) Mobile

Support mobile terminal and HTML5

Create a new line report to achieve the effect of scenario 2. After exporting, each group of content corresponds to a sheet page.

1. Template Preparation

1) Create a new template, create a new dataset ds1, the sql statement is: SELECT * FROM Sales_Volume order by Region

2) Drag the fields in the ds1 dataset to cells A1-F1 to set the cell style, as shown in the figure below:

3) Click on cell A1 and add "condition attributes" to it to realize grouping, where the paging condition is: currentValue is not equal to'=HIERARCHY(A1)' and &A1!=1, as shown in the following figure:

Note: The function of the condition attribute is to page out if the current value is not equal to the previous value. The function of HIERARCHY(A1) is to get the value of the previous area. After cell A1 is expanded, the previous value of the first value is empty, so use A1!=1 to exclude it.

2. Realize pagination export

1) Add export button

Add a button widget in the parameter panel and change the widget name to "Export Excel", as shown in the figure below:

2) Add JS code

Click the "Export Excel" button, and add a "click" event at "Widget Settings"> "Events", as shown in the following figure:

The JavaScript code is as follows:

var url = 'http://localhost:8075/webroot/decision/view/report?viewlet=Reuse/Line_report_export_excel_by_sheet.cpt';
var pars = '&format=excel&extype=sheet&__filename__=2';
url += pars;
alert(url);
window.open(encodeURI(encodeURI(url)));

Note: The url address in the JS code is not fixed, it should be modified according to the template name and template storage location.

3. Effect Preview

Save the template, click "Pagination Preview", and click the "Export Excel" button on the report screen. After exporting, there will be one sheet page for each area. The effect is shown in the following figure:

Note: Mobile terminal is not supported

Parameter query report has been completed template can refer to: %FR_HOME%\webapps\webroot\WEBINF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Parameter_report_export_excel_by_sheet.cpt

Click to download：

Parameter_report_export_excel_by_sheet.cpt

Parameter query report has been completed template can refer to: %FR_HOME%\webapps\webroot\WEBINF\reportlets\doc-EN\ReportApplication\FeaturesApplication\Line_report_export_excel_by_sheet.cpt

Click to download:

Line_report_export_excel_by_sheet.cpt