Bidirectional Expanded Cell for Excel Import

Version

Application Scenario

When the cells containing the headers are expanded horizontally, and cells in the import area are expanded vertically, you can use Bidirectional Expanded Cell Import in Excel Import Setting.

For example, the title fields are obtained by horizontal expansion, and the cell where uncertain rows of data are exported is expanded horizontally. The following figure shows the effect of importing data into the report, where the number of rows can vary dynamically.

Function Logic

1. This function applies to the scenario where the cells containing the headers are expanded horizontally, and cells in the import area are expanded vertically.

2. This setting is independent and does not affect the logic of data matching by title or cell position.

2. This function is disabled by default. 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.

Data Preparation

1. Create a built-in dataset Embedded1, click Table Design, add only a column of data, modify the column name to Title Row, and click OK.

2. Click Insert Row and enter the corresponding title names from the Excel file, as shown in the following figure.

3. Create a dataset query named ds1 with the SQL statement SELECT * FROM Product, as shown in the following figure.

Note:

This is only an optional step for the convenience of previewing data changes in the database table.

Report Design

1. Drag the Title Row column from the built-in dataset into cell A2 and set Expansion Direction to Horizontal.

2. Add a file widget for cell A3, set Expansion Direction to Vertical, and design the report style, as shown in the following figure.

Report 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 all the fields except Unit_quantity and Category_name, and tick the ProductID field as the key.

4. Since cells in this example are set to expand horizontally and vertically, you need to use absolute hierarchical coordinates to obtain the corresponding values of the data column.

5. For example, the value of the ProductID column is the formula A3[;A2:1], which returns the value of cell A3 corresponding to the first value in row A2. The following table shows the specific formulas and steps.

Excel Import Setting

Choose Template > Excel Import Setting on the menu bar, tick Bidirectional Expanded Cell Import, 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, click Data Entry Preview, and click the Import [Excel] icon on the toolbar. The following figure shows the effect.

For details, you can download the template Bidirectional Expanded Cell Import.cpt.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.