Import Excel matched by location

Excel import of detailed report according to the location matching means that when importing excel, the data in Excel will match the cells in the template that need to be imported, which is not related to the title name, but only related to the location.

As shown in the figure below, you can see that the column headings in Excel are completely different from those in template, but their locations are consistent. The cells of importing data in template start from B3, then the data imported from excel B3 will be imported.

Note: the premise of detailed report matching by location is that the title can't match completely. If there are two or more titles that can match, matching by title will be used. However, if matching by title is not executed through the configuration file, matching by location will be executed directly.

1. Template preparation

In the figure above, we can see that the first row of data (excluding the title) in excel is in B3 ~ K3. In the filling template, the cells of imported data should also be in B3 ~ K3. The cells should be expanded from top to bottom, and widgets should be added.

Note: if the imported report is not a blank report, but has data retrieval function, you need to set the leftmost parent cell of the report to list display.

2. Report data entry attribute setting

In the report data entry attribute, you only need to bind the first row cell to the data column of the data table.

In this example, the original data supplier and category in Excel are all specific names. If the user wants excel to import the name, but the actual input is ID, what should I do?

At this time, you can use the map function transformation in the data entry attributes, which is described in detail below.

Define the dataset required by the map function

Add dataset DS1, and the SQL statement is: select * from supplier;

Add dataset DS2, and the SQL statement is: select * from ProductCategory.

Using map function in data entry attributes.

Excel imports the name and uses the map function conversion, as shown in the following figure:

3. Toolbar settings

The default toolbar does not have the import excel button. Click template > web attibutes > data entry settings to set the template separately. Check use toolbar to set the toolbar separately for the report, clear the toolbar, and then double-click the submit button and import excel button in the button panel to add the two buttons to the top toolbar, as shown in the figure below: 

For completed templates and excel file, please refer to:

%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\DataEntry\DataEntryApplication\Excel_location_match.cpt

%fr_HOME%\webapps\webroot\WEB-INF\reportlets\doc-EN\Reference\excelimport.xls

Click to download the template:Excel_location_match.cpt

Click to download the excel: excelimport.xls

Note: if the cell of importing data in the template (the cell with extended attribute and control added) starts from line 6, the data in Excel will be imported from line 6 when importing according to the location matching.