C # Implementation reads data from Excel files to SQL Server database

With third-party components: Npoi component implementation

Go to the official website: http://npoi.codeplex.com/The download requires the introduction of a DLL (you can select the. net2.0 or. net4.0 dll), and then add references to the Web site. With Npoi, you can read and write Word/excel documents on a machine that does not have Office installed or the appropriate environment.

Create an entity class first:

[Table ("Customer") ]     Public classCustomer {[Key] Public intId {Get;Set; }  Public stringFirstName {Get;Set; }  Public stringLastName {Get;Set; }  Public intAge {Get;Set; }  Public intGender {Get;Set; } }

Create a new entity class customer

Create a class to implement reading data from an Excel file to list<customer>.

  Public classImportexcel { PublicIlist<customer> Importexecltocustomer (stringFilePath) {            varCustomerList =NewList<customer>();            Customer customer; if(string. IsNullOrEmpty (FilePath)) {return NULL; } FileStream FileStream=NewFileStream (FilePath, FileMode.Open, FileAccess.Read); Xssfworkbook WorkBook=NewXssfworkbook (FileStream); intSheetcount =Workbook.count; if(Sheetcount >0)            {                varSheet = Workbook.getsheetat (0); //start the import from the second line, the first row is the column name                 for(intI=1; i<= sheet. Lastrownum; i++) {IRow row=sheet.                                        GetRow (i); //get the value of each column in the row                    stringTmpfirstname = Getcellvaluestringfromisheet (Row,0); stringTmplastname = Getcellvaluestringfromisheet (Row,1); stringTmpage = Getcellvaluestringfromisheet (Row,2); stringTmpgender = Getcellvaluestringfromisheet (Row,3); Customer=NewCustomer () {FirstName=Tmpfirstname, LastName=Tmplastname, age=Convert.ToInt32 (tmpage), Gender=Convert.ToInt32 (Tmpgender)};                customerList.Add (customer); }            }            returncustomerlist; }        Private stringGetcellvaluestringfromisheet (IRow Row,intColindex) {            if(Row! =NULL) {Icell cell=row.                Getcell (Colindex); if(Cell! =NULL)                {                    if(Cell. Celltype = =celltype.string) {returncell.                    Stringcellvalue.trim (); }                    if(Cell. Celltype = =celltype.numeric) {returncell. Numericcellvalue.tostring ().                    Trim (); }                    returncell.                Stringcellvalue.trim (); }            }            return string.        Empty; }    }

implements reading data from Excel into List

Methods in the main function to implement BULK INSERT data into SQL Server database tables

classProgram {Static voidMain (string[] args) {            stringFilePath =@"E:\Customer_Test.xlsx"; Importexcel Importexcel=NewImportexcel (); varCustomerList =Importexcel.importexecltocustomer (FilePath); #regionAdding data to a databaseusing(Codefirstdbcontext context =NewCodefirstdbcontext ()) {                //EF Big Data batch processingcontext.                Bulkinsert (customerlist); Context.            SaveChanges ();            }; #endregionConsole.readkey (); }    }

inserting data into the database is implemented in the main function

The code is applied to the EF create entity, BULK Insert data method, in subsequent articles will be listed in detail

C # Implementation reads data from Excel files to SQL Server database