Parsing large. NET ERP system design general Microsoft Excel import feature

Enterprise management software is difficult to avoid with Microsoft Excel, often the software is done, the customer asked to do an Excel import function. The ability to import Excel data is less difficult, from the values of Excel column data fields, to validating values, and then importing into database tables. However, has been doing repetitive work, wrote countless Excel import programs, each time just satisfied with the problem solved, and finally found a way to achieve universal import of Excel data.

Designing a common Excel import feature, the first implementation requirement is not to rely on Excel, the customer's computer or server is likely to not have Excel installed, so Microsoft Office Interop is not considered. The second implementation requirement is a high degree of abstraction, which is not dependent on a specific database table, so that the abstraction from a specific table to a common table import is highly reusable.

The first step is to generate an Excel template file, look at the field selection interface, pass in a database table, enumerate the fields of the table, to generate an Excel template file:

The function of this feature is to generate an Excel file for the user to enter data. Because table names are passed by different feature forms, the first step in generalization is achieved.

Generate the Excel file, plus the data we want to fill out, refer to the table below.

Department.dept	Department.description
R	Development Department
CAM	Programming
CNC	Computer gongs
EDM	Spark Machine
Wc	Wire Cutting
SG	Grinder
Om	Other processing
PO	Playing light
Qc	Quality control
Assm	Mold Assembly
Inje	Injection Department
Am	Administration and Management

Look at the table header of Excel, which contains the field definitions, and the fields are preceded by a table, which supports master-slave table import.

The second step is to enter the data in the Excel file generated by the above steps, and then click Import in this interface to complete the data import.

Let's take a look at how to invoke this generic import interface feature:

   protected Override voidSetupimporttemplate (entityimportargument argument) {Base.            Setupimporttemplate (argument); list<string> columnslist;//EntitymanagerArgument. Entitymanager = This. _departmentmanager;//EntityNameArgument. rootentity = entitytype.departmententity;#regionHiddencolumns#endregion            #regionRequired Columns, Columns must be selected asExport columns.#regionItem columnslist =Newlist<string> ();            Columnslist.add (DepartmentFields.Dept.Name);            Columnslist.add (DepartmentFields.Description.Name); Argument. Requiredcolumns.add (entitytype.departmententity, columnslist);#endregion            #endregion}

Design the above to invoke the interface, the code explains the following several important methods:

1 The advantage of an ORM mapping to import tables is that the entity can find the table it maps to, and the entities it maps to can be found according to the table. To run data validation, it must be implemented through the validation class of the entity, which saves a lot of validation code.

2 Save Table method The manager class implements the save Excel data to the database, for such a general structure, the preservation method must also require the method name to be highly consistent, for example the table name is SalesOrder, it maps the entity name is salesorderentity, Then the corresponding Save method must be Savesalesorder, which is called when the contract, by the System mandatory convention.

3 Value Verification If the database does not make mandatory input (nullable null), but it is logically required to enter a value, you need to run entity validation.

The third-party class library used by data import is Infragistics Excel, which first opens an Excel file, reading the first row of field names and corresponding values.

using Infragistics.Documents.Excel; //Load workbook with Data Workbook Workbook = workbook.load ("Department.xls"); Worksheet Worksheet = Workbook1. worksheets["Sheet1"]; string columnname=worksheet. Rows[0]. Cells[0]. Value;

Construct a memory dataset that constructs a table structure, such as the Excel file table above, based on the field definition of the first column, and we can construct the following table

Department (dept,description)

We can only get field name information from the Excel file, and we need to connect to the database to get the type information of the field.

SELECT  from WHERE object_id=object_id (' Department ')

Based on this query, refine the previous table definition structure, similar to this.

Department (Dept nvarchar (8), Description nvarchar (40))

At this point, you can do basic data type validation, such as the type of a table field is a number, but the value in Excel is a string that throws an exception.

After the basic type verification, we also need to do the logic verification, such as the database has defined the R & R Department, the second execution and insert a branch of the department code, need to throw an exception in time, this business logic validation, with the help of ORM framework function implementation.

LLBL Gen Pro provides an entity type definition that matches a validation type, which can be verified before the data is saved or deleted before the value is changed, and we complete this complex validation logic through the entity invocation.

Based on the definition of the previous department table, find the system metadata to know that the entity it maps is departmententity, we construct departmententity based on the values of the data table, the Excel data in the table above has multiple lines of records, A list<departmententity> is constructed, and the field values (DataRow rows) in the memory database Datatale are converted to Entities (entity objects) by reflection. There are many example codes on the Web for DataTable and List<entity> conversions.

We actively invoke the validation type of department before saving the Department method:

New Departmentvalidator (); validator. Validaterequiredfields (department);

Finally, call the Save method in the Entitymanager interface to:

typeof (departmententity), _department);

In this process, the database objects are implemented through the interface to achieve extensibility, the actual application can also import master-slave table data, you need to add associated rows of Excel each row of data relationship.

There is currently no implementation of importing layer three table data.

Parsing large. NET ERP system design general Microsoft Excel import feature