Data Matching by Title During Excel Import

Version

Application Scenario

When you import the Excel data into a report where the number of rows can vary dynamically, the data is matched based on the column titles by default. Data in columns with matching titles will be imported into the report, while data in columns with non-matching titles cannot be imported.

1. The following figure shows the Excel data.

2. When all the titles match, all data columns are imported successfully, as shown in the following figure.

3. When only some titles match, the data columns with matching titles are successfully imported, while those with non-matching titles cannot be imported, as shown in the following figure.

Function Logic

1. Data is matched based on the titles. Data in columns with matching titles will be imported into the report, while data in columns with non-matching titles cannot be imported.

2. The logic will only be triggered when at least two or more column titles are matched.

3. Data matching by title is independent of column positions. Columns with the matching titles can be located in different positions.

4. If data matching by title fails, the data will be matched by cell position. For details, see Data Matching by Cell Position During Excel Import.

Template Creation

Choose File > New General Report on the menu bar to create a general report, as shown in the following figure.

Report Design

1. Enter text in cells A1 to J1, and add text widgets for cells A2 to J2.

2. Set Expansion Direction to Vertical for cell A2 and design the report style, as shown in the following figure.

Note:

Whether the cells where data is to be imported contain data fields or not, the cells need to be set to be expandable. For cells that contain data fields, you need to set Data Setting to List.

Data Entry Attribute Setting

1. Choose Template > Data Entry Attribute on the menu bar, click +, and select Built-in SQL.

2. Set Submission Type to Smart Submission, select FRDemo from the drop-down list of Database, and select Product from the drop-down list of Table.

3. Click Smart Add Field, add the corresponding fields, click Smart Add Cell, add the corresponding cells, and click OK, as shown in the following figure.

Excel Import Setting

Choose Template > Excel Import Setting on the menu bar, tick Match by Title Preferentially and Cell Position Secondarily, and click OK, as shown in the following figure.

Import [Excel] Button Adding

1. Choose Template > Web Attribute on the menu bar.

2. Click Data Entry Setting, select Set for This Template Separately from the drop-down list of Following Settings, double-click the Import Excel Data button to add the button to the toolbar, and click OK, as shown in the following figure.

Effect Display

Note:

The effect cannot be previewed on mobile terminals.

Save the report and click Data Entry Preview. Click the Import [Excel] button on the toolbar, select the downloaded Excel file, and click Open. All the data are imported successfully, as shown in the following figure.

Template Copying

Copy the template in example one and rename the template obtained by copying.

Report Modification

1. Modify the column titles of Product Name and Category to Product and Category ID, respectively.

2. Swap the positions of the Category ID column and the Supplier column, as shown in the following figure.

Effect Display

Note:

The effect cannot be previewed on mobile terminals.

Save the report and click Data Entry Preview. Click the Import [Excel] button on the toolbar, select the downloaded Excel file, and click Open to import the Excel file. In this case, data columns with matching titles are successfully imported, while those with non-matching titles cannot be imported, as shown in the following figure.

For details, you can download the template Data Matching by Title During Excel Import.zip.

For details, you can download the Excel file Excel Import.xls.

If you set the log level to DEBUG, you can retrieve the details about the logic executed during this import from the logs after the import.