JS applies formula.js of office-FineReport Help Document

Last update： 2021-06-04

I. Overview

1. Problem description

Formula.js is a library that implements powerful formulas in similar spreadsheet applications such as Microsoft Excel and Google Spreadsheets. How to use the Formula.js library to call office formulas? This article uses the TREND function as an example to introduce users to the method of using the Formula.js library to call office formulas.

2. Realization idea

Load the Formula.js library and call the office function formulas through JS.

II. Steps

1. Data preparation

In the designer, create a new general report, and enter 1.809, 8.6, 8.01, 1.85, 10.4 in cells A1, B1, C1, A2, and B2 in sequence. As shown below:

2. Widget setting

1) Select cells A1, B1, C1, D1, A2, B2, C2, D2 in turn, select widget setting in the property panel on the right, and add "Number" widgets to them;

Set the cell content to display in the center and add a border (custom).

The following figure shows the steps to add a "Number" widget to cell A1:

2) Merge cells, add a "Button" widget to it, the button name is Calculate, as shown in the figure below:

3. Quote JavaScript

1) Download the attachment: formula.zip

Unzip and put the formula folder under the path %FR_HOME%\webapps\webroot, as shown in the following figure:

2) In the designer, click Template>Web Attributes, and add numeric.min.js,numeral.min.js, jstat.js, formula.js. As shown below:

4. Add click event

Select cell A4, select Widget Setting>Event in the property panel on the right, and add "Click" event, as shown in the figure below:

The JS code is as follows:

var data_y=new Array();
var data_x=new Array();
var new_data_x=new Array();
data_y.push(contentPane.curLGP.getCellValue("A1"));
data_y.push(contentPane.curLGP.getCellValue("A2"));
data_x.push(contentPane.curLGP.getCellValue("B1"));
data_x.push(contentPane.curLGP.getCellValue("B2")); 
new_data_x.push(contentPane.curLGP.getCellValue("C1"));
var ak=formulajs.TREND(data_y,data_x,new_data_x);
contentPane.setCellValue("D1",null,ak);

You can open the formula.js with related software to find the definition of the TREND() function parameter, which is:

exports.TREND = function(data_y, data_x, new_data_x)

If the JS parameter is added, the JS code in the "Click" event of cell A4 will be more concise.

Among them, the added parameters are data_y, data_x, new_data_x, which are respectively expressed by formulas as: A1:A2, B1:B2, [C1].

As shown below:

The JS code is as follows:

var ak=formulajs.TREND(data_y,data_x,new_data_x);//Use function TREND
contentPane.setCellValue("D1",null,ak);//Set value to D1

Note: In the method of adding parameters in the click event, the data cannot be dynamically changed according to the filled content in real time, and can only follow the page loading.