Example of using the Excel data source for the computing report

As a common file format, Excel is widely used. Many reports use data from Excel. You can use Excel as a data source to develop a report. Here, we use an instance to describe the implementation process of the Set Computing report.

Report description

The stock information of a fund company is stored in an Excel file every month. The name is stock_yyyymm.xls. First, you need to query the highest and lowest closing prices of each stock in a certain period of time, and the transaction date that sets the highest or lowest closing price.

The report style is as follows:

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/41/39/wKioL1PRy6jxeqVPAABKLH83knI637.jpg "style =" float: none; "Title =" report_excel_performance_1.jpg "alt =" wkiol1pry6jxeqvpaabklh83kni637.jpg "/>

Steps

Write scripts

First, use the set calculator to read Excel files and calculate data, and write scripts (fromexcel. DFX ).

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/41/39/wKioL1PRy6yzL3jOAAFk3TM_Suc776.jpg "Title =" report_excel_performance_2.jpg "style =" float: none; "alt =" wkiol1pry6yzl3joaafk3tm_suc776.jpg "/>

A1 calculates the sequence of months involved by passing in the start and end months parameter, that is, the file to be used

A2 reads all Excel file data that needs to be used in this time range through importxls Loop

A3 group by stock code

The A4-A5 selects the highest and lowest prices for each stock respectively.

A6 Concatenates the above two result sets horizontally

A7 final result set, which is returned to the report through A8

Create report

Open the computing report designer, select File-new report from the menu bar, and click Generate blank report to create a blank report.

Set parameters

Select Report-parameters in the menu bar, and add two query parameters begin and end. The type is an integer.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/41/39/wKiom1PRypfDwXRXAACs6MDWqi8115.jpg "Title =" report_excel_cece_3.jpg "style =" float: none; "alt =" wkiom1prypfdwxrxaacs6mdwqi8115.jpg "/>

Set Dataset

On the menu bar, select Report-dataset, set the dataset type to "set calculator", select the DFX file in the displayed dataset settings window, and set input parameters, that is, two parameters of the report, begin and end, are used to create the dataset ds1.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/41/39/wKioL1PRy7SgMX7-AAEaQNKziiM773.jpg "Title =" report_excel_performance_4.jpg "style =" float: none; "alt =" wKioL1PRy7SgMX7-AAEaQNKziiM773.jpg "/>

The DFX file path can be set to an absolute path or a relative path. The relative path in the designer is relative to the Application resource path configured in the tool-> option, on the web end, it is relative to reportconfig. the reportfilehome path in XML.

Edit a report template and Expression

In the newly created report template, set the report style and expression, first behavior title, second behavior report header, and third behavior report expression.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M00/41/39/wKiom1PRypzT82_eAACo64rwdcA210.jpg "Title =" report_excel_performance_5.jpg "style =" float: none; "alt =" wkiom1prypzt82_eaaco64rwdca210.jpg "/>

A3: Use the select function to retrieve the stock encoding list based on the dataset ds1

B2-E2: According to A2 extension, through the value expression respectively obtain the maximum closing price, the date, the lowest closing price and the date of each stock.

Show reports

In the report designer, select preview report in the toolbar to view the report Presentation results in the designer.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M02/41/39/wKioL1PRy7qjUXXnAAE6AkfarsU210.jpg "Title =" report_excel_performance_6.jpg "style =" float: none; "alt =" wkiol1pry7qjuxxnaae6akfarsu210.jpg "/>

Designer preview Effect

In addition, the dataset report designer has built-in tomcat, which allows you to publish a report to the Web end to view the page effect. Click the IE icon in the upper-right corner of the toolbar, set the main directory of the report and the main directory of DFX (default can be used) to publish the report.

650) This. width = 650; "src =" http://s3.51cto.com/wyfs02/M01/41/39/wKiom1PRyqWBi2R2AADsRAo3b5Y535.jpg "Title =" report_excel_performance_7.jpg "style =" float: none; "alt =" wkiom1pryqwbi2r2aadsrao3b5y535.jpg "/>

Preview after page publishing