テーブルをスイープして Excel インポート

1. Probelm description

Hope to clear the data in the original table before importing the Excel data and submitting it.

2. Solution

When setting the data entry attributes, the built-in SQL will be executed sequentially from top to bottom. Using this feature, set delete submission to clear the table in the built-in SQL1, and then set smart submission in the built-in SQL2 to insert data.

This method can ensure the consistency of the transaction. If the data insertion fails, the operation of emptying the table will also be rolled back.

1. Prepare data

Create a new general report, add a dataset ds1, SQL statement: SELECT * FROM Product

2. Design template

Excel: Product.xlsx

In the designer, design the table according to the Excel, and set "Cell Attributes > Expansion Direction" to "Vertical" in cell B3, as shown in the following figure:

Note: This example does not add widgets when designing the table, only supports importing data, and cannot be modified. If necessary, you can add it yourself.

Select "Individually set for the template" in "Template > Web Attributes > Data Entry Settings", and double-click "Emptying Import" to add it to the toolbar.

3. Set to clear the table

Click "Template>Data Entry Attributes" to add a built-in SQL submission, and the submission type is "Delete Submit". Select the table that needs to be cleared, add the field "ProductID" and check the primary key, use the formula SQL ("FRDemo", "SELECT ProductID FROM Product", 1) to query all ProductID as the primary key to clear the table .

4. Set to insert data

Add a built-in SQL2, the submission type is "Smart Submit", select the "Product" table in FRDemo, add all the fields intelligently, and add the corresponding cells, and check the "ProductID" as the primary key.

5. Preview effect

Click Emptying Import, and then click Submit after importing excel, the data table of the database will be cleared first and then the imported data will be inserted.

Click to download: 

Clear_table_before_import.cpt