General Excel import tool V1.0 instructions

General Excel import tool V1.0 

Instructions for use

Sometimes our data is stored in Excel (especially for users who prefer to use Excel to collect some common data), and the system needs the data to process other businesses, then we need to import the data to the database. However, in view of the variety of Excel Styles, you must write a lot of repeatedCode. It is obviously boring for a software developer to do such repetitive work. So how can we find a common method? Today, let's take a look at how to solve this problem.

Overall Design Concept 

The purpose of using Excel is to avoid repeated work. That is to say, you do not have to start from scratch or repeat work because of changes in Excel styles and data. Therefore, we must extract a general east-west image. When using this image, we only need to focus on the relevant business, rather than over-focusing on related Excel operations and storage. Another simple point is to encapsulate things in common and expose personality. Considering this situation, we can use the configuration file to solve this problem. In the configuration file, we configure the tables and fields to be imported by excle, and then import the data to the database according to the configuration file. In this way, you only need to configure an XML file for one or more Excel files when importing an Excel file, and then call the relevant classes to complete the Excel import.

Additional note: general limitations

It is explained here that, although we designed a general Excel ImportProgramBut here the "General" is relatively speaking, it does not take into account all the situations of Excel, because the design of Excel is very complex and diverse, it is a long process to consider all the situations. Currently, the program does not consider statistical rows (including merged rows and code table fields ).

Configuration File Design

Considering the use of XML configuration to design general Excel imports, designing XML has become the focus of the design. For single-Table import (an Excel file is mainly imported to a database table, which does not rule out pulling other code tables), our configuration file is designed based on the database (mainly based on the database table structure) it can also be designed based on Excel (mainly based on the Excel format), but if it is a multi-Table import (that is, a single Excel can be imported into several tables) considering its complexity, it is more appropriate to use the database as the basis. Based on this consideration, our entire configuration design will be based on the database table structure. The final configuration file Design sample is as follows: 

 <?  XML version = "1.0"  ?>  <  Config  Endtag  = "Rowblank"  Headerindex = "1"  Dataindex  = "2"  Worksheetcount  = "0"  >        <  Table  Name  = ""  Deleterepeat  = "True"  Excludedcolumns  = ""   >                <  Column Isprimarykey  = ""  Columnname  = ""  Headertext  = ""  Required  = "True"  Datatype  = "Number"  Datalength  = "100"  Defaultvalue  = ""  Comment  = ""  >                   <  Codetalbe  Name  = ""  Primarykey  = ""  Referencecolumn  = ""  Condition  = ""  />          </  Column  >            </  Table  > </  Config  > 

 

Config Node

The Config node is located at the outermost layer, representing the entire configuration. Its Attribute endtag (the end mark of Data Reading. For example, "rowblank" indicates that a blank row ends. If a row does not have any data when reading Excel, it is deemed as the end; it can also be a column address (such as C). When this column is empty during import, the import operation will end );

Headerindex indicates the row value corresponding to the Excel header, which is equivalent to the title row (starting from 1 by default ).

Dataindex indicates the index of the starting row of the Data row (starting from 2 ).

Worksheetcount indicates that for an entire Excel file, if it contains multiple worksheets, the first few worksheets are processed based on the set value. If the attribute value is 0, all worksheets are processed. The default value is 0.

Table Node

The table node is located in the config node. It can have multiple tables in the database;

Name attribute corresponds to the name of the table to be imported;

The deleterepeat attribute indicates whether to delete duplicate rows (if true, duplicate rows are deleted based on the primary key before insertion );

Excludedcolumns indicates excluded columns. Multiple column names are separated by commas (,) (these fields are not imported ).

Column Node

The column node is located in the table node. There are usually multiple columns for corresponding configuration (note that for columns not in Excel, but for database tables to be imported, you also need to configure the column node, at this time, headertext is null or the headertext attribute is not configured );

The isprimarykey attribute indicates whether the attribute is a primary key. When the table node is configured with deleterepeat as ture, at least one column node must be specified.Isprimarykey is true. This attribute is prepared for the delete condition [it may not be a real primary key]);

Columnname indicates the corresponding column name;

Headertext indicates the corresponding Excel column header (the imported columns are determined based on the Excel alias );

Required specifies whether this column is a column that must be imported (if it is set to true, this column is null in Excel and no default value is configured, an exception is thrown );

Datatype is the data type (for example, String, number, and datetime are used for data verification );

Defaultvalue is the default value (note that its value is not necessarily the specified character value, it can be "Max" or "newid". If it is specified as "worksheetname", the name of the current workbook is used as its value. If it is Max, this column must be of the numerical type, at this time, if you need to use the default value during the import, 1 will be added to the database based on the maximum value of this column in the original database table, if newid is used as the default value during import, the system will automatically generate the guid value );

Comment is the description of this column.

Codetable Node

You can also configure the codetable node in the column node to represent the code table.

The name attribute value indicates the name of the referenced table;

Primarykey refers to the primary key of the primary table to be referenced, that is, the foreign key of the sub-table. The columnname attribute value of the column configuration node should be set to the primarykey attribute value of the codetable node, as shown in the following Configuration:

<Column columnname = "categoryid" headertext = "item type" defaultvalue = "">

<Codetalbe name = "categories" primarykey = "categoryid" referencecolumn = "categoryname"/>

</Column>

Referencecolumn indicates that the corresponding code field is associated with the name column, which is the corresponding Excel value we imported (for example, categoryid corresponds to categoryname, then referencecolumn is categoryname, usually, Excel may store items similar to categoryname rather than categoryid, but categoryid is required for import ).

Import instance description:

Figure 1 Excel file to be imported. You can import multiple workbooks at the same time)

 

Figure 2 set import rules

Figure 3 Import using the developed import tool

Figure 4 data imported into the database after import

 

Author: erichu Source:HTTP:// Www.cnblogs.com/huyongEmail: 406590790@ Qq. comqq:406590790Platform blog: [csdn]HTTP:// Blog.csdn.net/chinahuyong [cnblogs]HTTP:// Www.cnblogs.com/huyongis The Author: Senior Engineer: Information System project manager; dba. Focused on Microsoft platform project architecture, management, and enterprise solutions, with many years of project development and management experience, he has organized and developed multiple large projects for many times and is proficient in DOTNET, dB (sqlserver, Oracle, etc.) technology. Familiar with Java, delhpi, and Linux operating systems, with solid network knowledge. Have certain accomplishments in the field of object-oriented, service-oriented, and database. He is currently engaged in DB management and development, winform, WCF, WebService, web page data capturing, ASP. NET and other project management, development, architecture, and other work. If you have any questions or suggestions, please kindly advise me! The copyright of this article is shared by the author and the cnblogs blog. You are welcome to reprint this article, but you must keep this statement without the consent of the author.ArticleIf you have any questions, contact me via email or QQ. Thank you very much.