Shulou(Shulou.com)06/03 Report--

Sometimes we need to use the program to generate Excel files automatically, but the VBA with Excel itself is not easy to use, and the aggregator as a data processing tool will be much more convenient to achieve this requirement. This article will introduce how to use the aggregator to generate Excel files, but the powerful data computing power of the aggregator itself is not the focus of this article, so this paper simply uses text as an example of a data source. In practical application, we may take numbers from a variety of data sources and get the data that needs to be exported after a series of operations.

For the functions used in this article, please refer to the aggregator document "function reference".

Basic part 1. Simply export data 1.1 Export new files

In this example, the A1 cell is read into a text-formatted enterprise order table to simulate data that may be calculated. The expression in A2 is to export the data of A1 to an orders.xlsx file (if the file does not exist, it is automatically created when the program runs). In the example, x and F are not specified in the xlsexport parameter of the export function, so all fields in A1 are exported while leaving the field name unchanged. Because no parameter s is specified, it is exported to sheet1. The function uses the option @ t, so the field name is exported to the first line.

A1=file ("orders.txt": "UTF-8") .import@t () 2=file ("orders.xlsx") .xlsexport @ t (A1)

The exported excel file is shown in the following figure:

1.2 additional data

What should an enterprise do if it needs to append daily new orders to an existing orders.xlsx file with past data? Similar to the above example, in A1 is the order data for a certain day to be appended, the data structure remains the same, do not add the function option @ t when exporting in A2, because there is already a title in the file, you only need to export the data. Because the file already exists, it is automatically appended to the original data.

A1=file ("aday.txt": "UTF-8") .import@t () 2=file ("orders.xlsx") .xlsexport (A1) 1.3 Export to different sheet

If you want to pay special attention to the order for Shantai enterprise, how do you export some fields of its data to a special sheet?

You can filter the order table A1 in the A2 cell to select only the data records with the company name Shantai Enterprise, export the new order table A2 to orders.xlsx in A3, export only the four fields of order ID, company name, order date and order amount, and rename the order date to date, order amount to amount, and export the data to a new sheet called Shantai Enterprise. The corresponding script is modified as follows:

A1=file ("orders.txt": "UTF-8") .import@t () 2=A1.select (company name = = "Shantai Enterprise") 3=file ("orders.xlsx") .xlsexport @ t (A2, order ID, company name, order date: date, order amount: amount; "Shantai Enterprise")

The following figure shows the export result:

two。 Export a large amount of data

What if there is a large amount of data?

The aggregator provides cursors to deal with large amounts of data. Cursors traverse the data forward and backward when reading data, reading data from the data source one by one, not reading all the data into memory at once, so it will not be limited by insufficient memory. Moreover, aggregator cursors can be applied not only to databases, but also to data files or memory arrangements.

In this example, A1 opens the file cursor, and A2 exports big data, which the cursor refers to, to the big.xlsx file. When exporting with a cursor, add the @ s function option so that the export will be streamed and the resulting excel result file will not be occupied in memory.

A1=file ("big.txt": "UTF-8") .cursor@t () 2=file ("big.xlsx") .xlsexport @ st (A1)

The following figure is the export result of this example, in which 130727 data records are exported. In fact, we can export hundreds of millions of records, but a sheet in an excel file can only hold up to 1048576 rows of data, so when the exported data exceeds a million rows, a new sheet will be added to the excel to save it.

3. Specify display properties

In addition to exporting data directly, sometimes we want the generated excel file to be displayed beautifully, such as specifying font, color, background color, alignment, display format, and so on. At this time, as long as we pre-build the excel file (template), define these display properties we need, and then use the aggregator to export data to this file, the defined display properties will be presented.

As shown in the following figure, we write the table name on the first line of the orders.xlsx file sheet1, write the field column name on the second line, and define some style attributes for the table name and each column. Column 1, 3, and 4 are aligned in the middle, column 2 is aligned to the left, column 5 is aligned to the right, column 4 is displayed in "yyyy mm month dd date", and column 5 is displayed in "#, #. 00".

The aggregator export program is the same as the first example in this article, and the export result is shown in the following figure. When exporting to an existing file, the last non-blank line of the file is used as the header and overwritten with the exported header. Various style attributes defined in the original file are used for export (xxx is not supported for export).

4. Fixed row and column filling data

The aggregator also provides a way to read and write a cell or a block cell specified in the excel file, which is very useful when using excel for data filling. For example, the head office of a fund company sends an excel form to the branch, requiring the branch to fill in its relevant data and send it back to the head office. The excel file is as follows:

For such a form, because it needs to be reported to the head office every quarter, if you write an aggregator program to first calculate the data of the cells to be filled in, and then call the function to automatically fill in the corresponding grid, it will be a very convenient and convenient way.

The script dfx file for this example is shown in the following figure. Assuming that all the data to be filled in has been calculated, the first five lines are the data to be filled in turn.

The first six cells to be filled in the sample table are independent, so you can only fill in one cell at a time, and the sixth line is a cell that can be filled continuously. At this time, the data to be filled in can be spelled into a string separated by\ t, which can be filled in sequentially in the same line. After filling in all the data, write the excel object opened by C6 back to the hb.xlsx file.

ABCDEFG1 Bull Fund 2017 three 58.2364300

28.550200100400200

3182.676.343.728.516.4

41201.07300.27900.8

5154

six

46=file ("hb.xlsx") = A6.xlsopen () 7=C6.xlscell ("B2", 1x A1) = C6.xlscell ("J2", 1x B1) = C6.xlscell ("L2", 1x C1) 8=C6.xlscell ("B3", 1x D1) = C6.xlscell ("G3", 1x E1)

= C6.xlscell ("K3", 1bot F1) 9=C6.xlscell ("B6", 1; [A2:F2] .concat ("\ t")) = C6.xlscell ("H6", 1; [A3:E3] .concat ("\ t")) 10=C6.xlscell ("B9", 1; [A4:F4] .concat ("\ t")) = C6.xlscell ("B11", 1; [A5:G5] .concat ("\ t")) 11=A6.xlswrite (C6)

The following figure shows the contents of the completed excel file:

Advanced article

Export requirements can sometimes be very complex, such as the need for interlaced display of different background colors, dynamic correlation between cell colors and data values, drawing statistical graphs with exported data, grouping statistics of exported data, multi-dimensional crosstab, and so on. How does the aggregator implement these complex export requirements?

No rush! There are also advanced weapons libraries in the aggregator-external libraries, in which report 5 can be dried to achieve the complex function of exporting excel (you can also export pdf and word). See the external Library usage Guide (download address external Library Guide) for how to deploy external library functions, which are implemented by a series of functions starting with report_ in the aggregator.

1. Display properties of dynamic condition

Let's take a look at an example of exporting an order table, with the export requirements: the background color of the data row appears alternately in two colors, and those with an order amount greater than 2000 are shown in red and those less than 500are shown in green.

Implementation steps:

1. Open the dry report 5 designer and create a new report template "order Table .rpx". The screenshot is as follows.

Create a new report dataset ds1, which is only used to receive exported ordinal data from the aggregator, so you only need to specify the dataset name. The first row of the report is the table name, the second row is the column name to be exported, and the third row is the data record row. For more information on how to write the data record, please refer to the tutorial on drying up the report.

Select all the cells in the third row and fill in the background color expression with the following: if (row ()% 2 colors), which is used to specify two background colors that are displayed alternately.

Select the last cell in the third line, specify the display format as #. 00, and fill in the foreground color expression with the following: if (value () > 2000 mai 65536 if (value ())

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.