Best Excel import practices (2)

Related Links
Best Excel import practices (1)
Best Excel import practices (2)
Best Excel import practices (III)
Best Excel import practices (4)
Best Excel import practices (5)

Let's take the sales order as an example to look at the actual business scenario: Usually there is a document save operation in a common business system. For example, the method for saving a sales order may be as follows: orderservice. save (order ). Excel import is actually a save function, but the input method is different. The save method is reused in this import solution because of the normal UI and excel. In addition, there are many necessary checks for import (such as data types and value ranges ).
To meet all the import functions in the system and embody the object-oriented design principles, the following import rule classes are introduced:

 

This example is based on the business scenario of the table header (for example, a single order may have entries for multiple items). If it is just a single table, the structure is simpler than this.
The rules include common checks and methods to be executed during storage. The rule class is created based on the following configuration file (of course, it can also be from the database ):

 

< Importconfig ID = "Order" >
<! -- This design is to discuss a complicated import that contains the table body of the table header (for example, an order may contain entries of multiple items ). -->
<! -- The Object Name (header) mapped to records in Excel) -->
< Headclass > Businessrule. Order </ Headclass >
<! -- Name of the attribute referenced by the table object in the table header. If it is not the structure of the table header, you can delete this node. -->
< Entryproperty > Orderentries </ Entryproperty >
<! -- The Object Name (Table body) mapped to the record in Excel ). If it is not the structure of the table header, you can delete this node. -->
< Entryclass > Businessrule. orderentry </ Entryclass >
<! -- Class Name of the Save Method -->
< Methodclass > Businessrule. orderservice </ Methodclass >
<! -- Name of the Save method called after the object is constructed -->
< Savemethod > Save </ Savemethod >
<! -- There may be some business logic checks before calling the Save method. This is the method name for the check. If not, you can delete this node. -->
< Exvalidatemethod > Exvalidate </ Exvalidatemethod >

<! -- The information configuration of each column in Excel is as follows: -->
<! --
Isentry: whether the table body field is used. The default value is 0, which is not a table body field;
Isprimarykey: whether it is a unique field. Because one order header may correspond to multiple order bodies, that is, multiple records in Excel correspond to one order. During import, the system determines which rows belong to an order based on the field value of isprimarykey = 1.
Property: The property name of the object class.
Columnname: the name of the corresponding column header in Excel.
Requried: Indicates whether an entry is required. The default value is 0, which is not required.
Datatype: The validity verification of corresponding columns is generated based on this method. If it is a reference type (datatype = ref), it will generate a drop-down list based on refconfig and convert it when it is saved (for example, converting the customer name to the customer ID, because ID is saved in the database and name is displayed in Excel ).
Refconfig: configuration information of the reference type. The reference type usually needs to be converted and saved, so the configuration items are converted fields in sequence; tables to be searched during conversion; comparison fields to be used during conversion; and filter conditions.
Defvalue: default value. Supports multiple default values: $ f {XXX} indicates getting system-level variables. Of course, these variables must exist first in the system; $ E {. b} indicates taking the relevant attributes of other objects; $ c {XXX} indicates that the default value is a constant; $ s {XXX} indicates that the default value is obtained through SQL.
Min: minimum value.
MAX: maximum value.
Maxlen: Maximum length.
Comment: generate comments for the column header when the template is introduced.
-->
<! -- Header field -->
< Column Isprimarykey = "1" Property = "Orderno" Columnname = "Order No" Required = "1" Datatype = "String" Comment = "A number can only consist of letters or numbers. " Maxlen = "40" > </ Column >
< Column Property = "Orderdate" Columnname = "Order Date" Required = "1" Datatype = "Date" Defvalue = "$ F {sysdate }" Comment = "Date format is YYYY-MM-DD, if not entered, take the current date of the system. " > </ Column >
< Column Property = "Customerid" Columnname = "Customer name" Required = "1" Datatype = "Ref" Refconfig = "Customerid; t_customer; customername; isused = 1" > </ Column >

<! -- Table body Field -->
< Column Isentry = "1" Property = "Invid" Columnname = "Product name" Required = "1" Datatype = "Ref" Defvalue = "Invid; t_inventory; invname ;" > </ Column >
< Column Isentry = "1" Property = "QTY" Columnname = "Quantity" Required = "1" Datatype = "Decimal"   > </ Column >
< Column Isentry = "1" Property = "Price" Columnname = "Unit price" Required = "1" Datatype = "Decimal" Defvalue = "$ E {Invid. standardprice }" > </ Column >
< Column Isentry = "1" Property = "Discount" Columnname = "Discount rate (% )" Datatype = "Decimal" Min = "0" Max = "100" Defvalue = "$ C {0 }" Comment = "Enter a number between 0 and. " > </ Column >

A single user does not need to display the data in the import template, but the database requires a value. Therefore, the columnname attribute is not set, only the default value -->
column property =" createuser " defvalue =" $ f {userno} " > column >
importconfig >

The description of each node has been described in detail in the annotations. Note thatCodeBetter readability. The refconfig node is converted into the reffield, reftable, refconvertfield, and reffilter attributes of the object. The SQL statement similar to the following is executed during conversion during import:

Select   Top   1 Reffield From Reftable Where Refconvertfield = ' Xxx '   And Reffilter

 

Next we will discuss how to generate an Excel import template.