Npoi operation excel--Using the reflection mechanism, Npoi read Excel data accurately mapped to database fields

>

In fact, the demand is very clear, is a lot of different Excel, each Excel corresponding to a database table, we need to provide users to upload Excel, we parse the data warehousing function.

So, this involves a problem: we can read the table header of Excel, but how do we know each table header specifically corresponds to the field in the database?

Bloggers after a period of thinking and ideas, think of a law: Now the case is that we have Excel table A, corresponding to the database table B, but A and B specific attribute field mapping relationship we do not know. Can we have a mapping file C for a to B?

I think, when it comes to this, everyone is very clear ...

The first step : Create a mapping file (XML configuration file) for each Excel that corresponds to the database table, which we call the ruleset, of course, our requirement is that the list header of Excel is the same (order can be changed---reason please continue to look down)

<?xml version= "1.0"  encoding= "Utf-8"  ?><module>  <add  firstheaderrow= "5"                  lastheaderrow= "7"     sheetcount= "1"    supplementary= "0"     />  <add headertext= "Year"                   propertyname= "Year"                              datatype= "System.Int32"/>  <add headertext= "gross domestic product"           propertyname= "Gdpvalue"                         datatype= "System.double"/ >  <add headertext= "Primary Industry"              propertyname= " Primaryindustryamount "          datatype=" System.double "/ >  <add headertext= "secondary Industry"               propertyname= "Secondaryindustry_totalamount"   datatype= "System.double"/>   <add headertext= "Industry"                   propertyname= "Secondaryindustry_industry"      datatype= "System.Double"/>  <add headertext= "Construction"                 propertyname= "Secondaryindustry_construction"  dataType= " System.Double "/>  <add headertext=" "Tertiary Industry"               propertyname= "Thirdindustryamount"             datatype= " System.Double "/>  <add headertext=" per capita gross domestic product (yuan) " propertyname=" Gdpperperson "                     Datatype= "System.Double"/></module>

Of course, this XML file is a node of our own definition, so the meaning of each word is not explained in detail.

Attached: Excel original table as follows:

Where the "Reject line keyword" is described in the following detailed code, please continue to follow Weibo.

The header contains a hierarchical structure, and the specific reading method is also described in the following detailed code ... Today's talk about design ideas and some basic code.

Step Two: create a new class RegularXMLReaderService.cs that reads our own defined XML file:

public class regularxmlreaderservice : iregularxmlreaderservice    {         public list<regular> getxmlinfo (string  Xmlpath)         {             //xmlpath path name FOR XML              var reader = new xmltextreader (Xmlpath);             var doc = new xmldocument ();             //loading XML documents from the specified XmlReader              doc. Load (reader);            var headerlist  = new list<regular> ();             foreach  (Xmlnode node in doc. Documentelement.childnodes)             {                 var header  = new regular ();                 if  (node. attributes["Firstheaderrow"] != null)                      header. Headerregular.add ("Firstheaderrow",  int. Parse (node. attributes["Firstheaderrow"]. Value));                 if   (node. attributes["Lastheaderrow"] != null)                      header. Headerregular.add ("Lastheaderrow",  int. Parse (node. attributes["Lastheaderrow"]. Value));                 if   (node. attributes["Sheetcount"] != null)                      header. Headerregular.add ("Sheetcount",  int. Parse (node. attributes["Sheetcount"]. Value));                 if   (node. attributes["Exceltype"] != null)                      header. Headerregular.add ("Exceltype",  int. Parse (node. attributes["Exceltype"]. Value));                 if   (node. attributes["Supplementary"] != null)             &nbSp;        header. Headerregular.add ("Supplementary",  int. Parse (node. Attributes["Supplementary"]. Value));                 if   (node. attributes["HeaderText"] != null)                      header. Headertext = node. attributes["HeaderText"]. value;                if  (node. attributes["PropertyName"] != null)                      header. Propertyname = node. attributes["PropertyName"]. value;                if  (node. attributes["DataType"] != null)   &nbsP;                 header . Datatype = node. attributes["DataType"]. value;                 Headerlist.add (header);            }             return headerList;         }    }

This code is believed to be understood by everyone and not explained in detail. Which is designed into a ruleset class Regular.cs

 <summary>    ///  Template Rule Class     /// </summary >    public class Regular    {         /// <summary>        ///  Table Header Text    --corresponding Excel header name         /// </summary>         public string headertext { set; get; }         /// <summary>         ///  property name     --corresponds to the database field name         // / </summary>        public string propertyname  { set; get; }        /// <summary>         ///  data type    ---corresponding database field type (used to determine whether Excel data is legitimate)          /// </summary>        public  String datatype { set; get; }        private  Dictionary<string, int> _regular = new Dictionary<string, int> ();        /// <summary>         ///  Header rules   --the specific rules we define, such as the start line, end line, table singular, and so on, in our examples          /// </summary>        public  dictionary<string, int> headerregular        {             get { return _regular; }             set { _regular = value; }         }    }

This class may vary depending on the specific requirements, depending on the design.

Step three : Create a class that parses Excel data (the more complex Excel is, the harder it is to implement), the first assumption is that it's built (follow-up blog for detailed code)

 <summary>    /// excel table check and data reading interface     /// < /summary>    public interface iexcelimportservice    {         /// <summary>         ///  initialize Excel data and configuration files         /// </summary>         /// <param name= "FilePath" >excel file </param >        /// <param name= "XmlPath" > configuration file </param >        /// <param name= "Nullable" > Can be empty </param >        void initdataandconfig (string filePath,  string xmlpath, bool nullable);        /// < Summary>        ///  Comprehensive validation of Excel table compliance         ///  </summary>        /// <param name= " Customvalidate "> Custom test Interface for a cell item </param>        /// < returns></returns>        uploadexcelfileresult  Validateexcel (ispecification<keyvaluepair<string, string>> customvalidate);         /// <summary>        // /  Importing Excel Files         /// </summary>         /// <typeparam name= "Tabledto" > Data Objects dto</typeparam>         /// <returns>excel Data Collection </returns>         list<tabledTo> import<tabledto> ();        /// <summary>         ///  Importing excel  files------Matrix class template          /// 19.6-Yangtze River Main Line cargo flow direction         /// 20.5- Flow direction         /// </summary>     of Xijiang shipping Main Line     /// <typeparam name= "Tabledto" ></typeparam>         /// <returns></returns>         List<TableDTO> ImportForMatrix<TableDTO> ();     }

Here is the parsed Excel interface that has been built. (Please refer to the following blog for specific implementation)

Bo Master's design philosophy is: Users upload an Excel after we take three steps in the background:

1. Initialize data (Excel file stream, corresponding configuration file-our ruleset XML file, and some other configuration parameters)

2, verify that the data in Excel (by verifying the header can determine whether the user mistakenly upload Excel files, verify that the data inside the violation (format error, etc.) and so on need to verify the aspect). after the validation succeeds, the data such as the base header, additional information is sent to the user to confirm and submit, the failure causes the specific failure, and accurately locates the number of rows of failed data.

3, the user sees the verification through the return result, confirms the error to click Commits, at this time reads the data storage.

At this point, our entire parsing read into the Excel process is completed, heavy in the design rule class this design idea, which involves a lot of specific implementation of the code, please refer to the following blog topic.