Excel custom import

1. Application scenarios

The custom import Excel row and column function is suitable for scenarios where you only want to import part of the rows and columns in Excel. For example, there are 10 rows in Excel, and only 8 rows need to be imported.

2. Function entrance

Add the "Data Entry Settings >Custom Import Excel" button to the reporting toolbar.

When the front-end data entry preview, use this button to import Excel:

3. Functional logic

After clicking the "Custom Import Excel" button on the front end, select an Excel file, and the configuration page as shown in the figure below will pop up. The function of each configuration item is described below:

• Current : You can set different import configurations for each sheet. It should be noted that this setting does not mean that you can freely select a sheet for import. In a multi-sheet import scenario, the number of sheets and the position of the sheets in the Excel and the template must be the same before they can all be imported.

• Edit Import Sheet: Select the sheet to be imported, the unchecked sheet will not be imported.

• Title Row: Set which rows of the title row in Excel. The title row will not be imported. This option is required. If Excel has no title, fill in 0. Note: If the header row is filled with 0, it will follow the logic of general Exce import, otherwise it will be imported according to the cell position matching logic.

• Data Row: Optional. If you do not fill in, all data except the header row will be imported by default; if only the start row is filled in, all data in this row and after it will be imported; if only the end row is filled in, the data in this row and before it will be imported.        Note: If the data row is not filled, it will follow the logic of general Exce import, otherwise it will be imported according to the cell position matching logic.

• Data : the checked column will be imported, the unchecked column will not be imported. When there is a formula in the template cell, if data is imported, the formula will be overwritten, which will result in the subsequent failure to link calculations. However, if you use custom import and do not import the column data, you can keep the formula for calculation.

1. Prepare Excel

Prepare an Excel file with 2 sheets: Excel_custom_import.xlsx

The data of each sheet in Excel is as follows:

• sheet1

• sheet2

2. Prepare template

1) Design sheet1 

Fill in the form, as shown in the figure below:

2) Set the data entry attributes for sheet1 

As shown in the figure below:

3) Design sheet2 

Fill in the form, as shown in the figure below:

4) Set the data entry attributes for sheet2

As shown in the figure below:

3. Import preview

Save the report, select the data entry preview, click the "Custom Import Excel Record" button on the toolbar, and select the prepared Excel file.

Configure the imported content of sheet1:

Configure the imported content of sheet2:

The final import effect is shown in the following animation:

The completed template can be found in: %FR_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Form\Excel_custom_import.cpt

Click to download the template: Excel_custom_import.cpt