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

Importing Excel files into relational database is a common thing in data analysis business, but the format of many Excel files is irregular, so it is necessary to structure the data in advance and then use SQL statements to write them into the database. JAVA programmers often choose to use third-party class libraries such as POI or HSSFWorkbook to achieve, usually hard-coded, if you encounter a complex format of the table, parsing workload will be multiplied, Java does not have table objects, always use the collection plus entity classes to achieve, resulting in lengthy code, not universal. SPL of the aggregator is a language that specializes in dealing with structured data. It can easily read excel data, then structure it into an "ordinal table" and import it into the database. After using the SPL language, the structured storage of Excel data, which used to require thousands of lines of code, can now be done with less than 10 lines of code, or even 2 or 3 lines of code in simple cases.

With regard to export, sometimes we need to use the program to automatically generate Excel files, but the VBA with Excel itself is not easy to use, so it will be much more convenient for the aggregator as a data processing tool to achieve this requirement.

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

Let's take a look at how the aggregator imports or exports table data:

Import 1. Normal row style

Table style:

Aggregator script:

Script description:

A1: open the "student score sheet .xlsx" file and import the sequence table. The option @ t indicates that the first row of the file is the column header.

A2: connect to demo database

A3: save the order table in A1 to the xscj table in the demo database. Because the column names in the table are the same as the field names in the order table, you only need to specify the data table name.

Import effect:

2. Multi-row header row type

Table style:

Aggregator script:

Script description:

A1: open the file and import the data sequence table. The parameter "1 sheet 5" indicates that the first file is read from line 5 to the end of the file.

A2: change the column name of the sequence table read in A1 to "serial number, project code, project name, unit of measurement, quantity, unit price, consideration price", that is, the column name of the data table to be deposited.

Import effect:

3. Free format

Table style:

Aggregator script:

Script description:

A1: create an empty table with the column "employee ID, name, gender, position, birthday, telephone number, address, zip code"

A2: open Excel data file

A3: define the cell number sequence in which the employee information is located

B3: define the sequence of cell line numbers in which employee information is located

A4: read each employee information with for loop

B4:A3. (~ / B3 (#)) first calculate the current employee cell number sequence, and then read out these cell values to form the employee information sequence. In the first cycle, it is [C1rect, C2rect, F2, C3, D4, D5, C7, C8], and in the second cycle, it is [C10, C11, F11, C12, C13, D14, C16, C17]. Add 9 to each line number. $[A2.xlscell (] and "A2.xlscell (") are the same, both represent a string, its advantage is that when writing a program in IDE, if the number of the A2 cell changes, the A2 in the $[A2.xlscell (]) will automatically change, such as inserting a line before A2, the expression will become $[A3.xlscell (]), and the quotation marks will not automatically change.

B5: determine whether the employee ID value is empty. If it is empty, exit the loop and end the run.

B6: store a piece of employee information at the end of the A1 sequence table

B7: add 9 to the line number sequence of employee information to read the next piece of employee information

Import effect:

4. Crosstab

Table style:

Aggregator script:

Script description:

A1: open the file and import the data sequence table. The parameter "1 sheet 2" means to read the first file from line 2 to the end of the file. The option @ t indicates that the starting row is a column header.

A2: because the first cell in the second row is a picture, the data read is null, and the first column has no column header, so change the name of the first column to the shipper.

A3: convert the column and column of the order table data by the shipper. The option @ r means to convert the column data into row data. After the conversion, the new column names are "shipper area" and "order quantity" respectively.

Import effect:

5. Master and child table

Table style:

Aggregator script:

Script description:

A1: create an empty table with the column name "× ×, name, gender, date of birth, nationality, mobile phone number, department, family address, marital status, entry time", which is used to save the employee information of the master table.

A2: create an empty table with the column name "× × number, name, relationship, work unit, contact number", which is used to save the family member information of the employees in the child table.

A3: define the cell sequence in which the employee information of the main table is located

A4: open Excel data file

A5: read each sheet data of Excel file in a loop

B6: reading employee information sequence

C6: save the employee information read by B6 to sequence table A1

B7: read the employee's family member information from line 6 and read only the specified 5 columns of "family member, name, relationship, work unit, contact number"

B8: rename the family member column of the B7 order table to a × × number

C8: assign the × × column of the B8 sequence table to the × × number in the employee information

B9: save the employee family member information in B8 to sequence table A2.

Import effect:

Table A1 is shown below:

The sequence table A2 is shown below:

The above situations basically list the common Excel data formats, and if you encounter more complex files, you can flexibly use the techniques in the examples to deal with them.

Export fundamentals 1. Simply export data (1) Export new files

Aggregator script:

Script description:

A1: an enterprise order table that is read into text format to simulate data that may be calculated.

A2: export the data of A1 to an orders.xlsx file (if the file does not exist, it will be 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.

Export effect:

(2) additional data

Aggregator script:

Script description:

A1: read the order data sheet for a certain day in text format

A2: do not add the function option @ t when exporting, because the file already has a title, so you only need to export the data. Because the file already exists, it is automatically appended to the original data.

(3) Export to different sheet

Aggregator script:

Script description:

A1: read the order form of an enterprise in text format

A2: filter the sequence table A1 to select only the data records whose company name is Shantai enterprise.

A3: export the new order table A2 to orders.xlsx, export only the four fields of order ID, company name, order date and order amount, and rename the order date to date and order amount to amount, and export the data to a new sheet named Shantai Enterprise.

Export effect:

2. Export a large amount of data

Aggregator script:

Script description:

A1: read into a table with a large amount of data in text format

A2: export the big data referred to by the cursor 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.

Export effect:

Note:

130727 data records are exported in this example. 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.

Table style:

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, aligned in the middle of columns 1, 3 and 4, left-aligned in column 2, and right-aligned in column 5. Column 4 is displayed in the format "yyyy mm month dd date", and column 5 is displayed in the format "#, #. 00".

Aggregator script:

Export styles:

Note: various style attributes defined in the original file will be used when exporting; xxxxxx is not supported when exporting.

4. Fill in data with fixed rows and rows

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 documents issued are as follows:

Aggregator script:

Script description:

The first five rows 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 in successively. At this time, the data to be filled in is 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.

Export styles:

Advanced article 1. Display properties of dynamic conditions

Export requirements:

The background color of the data row appears alternately with two colors interlaced, and those with an order amount greater than 2000 are shown in red and those below 500 are shown in green.

Report design:

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.