Data Matching by Cell Position During Excel Import

Version

Application Scenario

Matching by cell position means that data is matched according to the cell position when you import the Excel data into the template. Whether the titles are consistent is not checked.

For example, column titles in the Excel file differ from those in the template, but the cell position of the data remains the same. In this case, the data will be matched according to the cell position when data in the Excel file is imported into the template, as shown in the following figure.

Function Logic

1. Data is matched according to the cell position when you import the Excel data into the template. Whether the titles are consistent is not checked.

2. You need to install the Excel Import Logic Setting plugin to modify the setting.

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 B2 to K2.

2. Since the first row of data (excluding the titles) in the Excel file is located in cells B3 to K3, the cells for data importing in the report should also be cells B3 to K3. In this case, add text widgets for cells B3 to K3.

3. Set Expansion Direction to Vertical for cell B3 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.

Report Data Entry Attribute Setting

You can choose Template > Data Entry Attribute and set Submission Type to Smart Submission. Then you can click Smart Add Field, add the corresponding fields from the Product table in FRDemo, click Smart Add Cell, and add the corresponding cells.

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

2. Set Submission Type to Smart Submission and select the Product table in FRDemo.

3. Click Smart Add Field, add all the fields except Unit_quantity, and tick the ProductID field as the key.

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

Excel Import Setting

Choose Template > Excel Import Setting on the menu bar and tick Match Only by Cell Position, 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, and double-click the Import Excel Data button to add the button to the toolbar, 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. The following figure shows the effect.

For details, you can download the template Matching by Cell Position During Excel Import.cpt.

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.