Import Excel data to MYSQL

Can withstand tens of thousands of data, fast, and use the transaction, no data errors will result in some data insertion (if all successful if one is unsuccessful, the test process did not fail), the need for friends can refer to the next

1.NPOI

2.mysql.data

Here's a record to use later.

Connection String App. Config:

<connectionStrings>    <add name= "dbconnectstring" connectionstring= "SERVER=IP;DATABASE=TESTJH; Uid=user; Pwd=123456;charset=utf8 "providername=" MySql.Data.MySqlClient "/>  </connectionStrings>

executes multiple SQL statements:

        <summary>////Execute multiple SQL statements to implement database transactions. </summary>mysql database///<param Name= "sqlstringlist" > Multiple SQL statements </param> public static void Executesqltran (list<string> sqlstringlist) {using (mysqlconnection conn = new Mysqlconnection (MyS qlhelper.connectionstring)) {Conn.                Open ();                Mysqlcommand cmd = new Mysqlcommand (); Cmd.                Connection = conn; Mysqltransaction TX = conn.                BeginTransaction (); Cmd.                Transaction = TX;                        try {for (int n = 0; n < sqlstringlist.count; n++) { String strSQL = Sqlstringlist[n].                        ToString (); if (strSQL. Trim ().                            Length > 1) {cmd.commandtext = strSQL; Cmd.                        ExecuteNonQuery (); }//later addedif (n > 0 && (n = = 0 | | n = = sqlstringlist.count-1)) { Tx.                            Commit (); TX = Conn.                        BeginTransaction (); }}//tx.                    Commit ();//Original Commit} catch (System.Data.SqlClient.SqlException E) { Tx.                    Rollback ();                throw new Exception (e.message); }            }        }

　　

Click the button:

        <summary>////Click the button///</summary>//<param name= "Sender" &GT;&LT;/PARAM&G        T            <param name= "E" ></param> private void Button1_Click (object sender, EventArgs e) { Button1.            Text = "inserting in ...";            OpenFileDialog file1 = new OpenFileDialog (); File1.            Filter = "Excel file |*.xlsx"; if (File1. ShowDialog () = = DialogResult.OK) {//start reading Excel MICROSOFT.OFFICE.INTEROP.EXCEL.APPL                Ication xlapp = new Microsoft.Office.Interop.Excel.Application (); Microsoft.Office.Interop.Excel.Workbook Workbook = XlApp.Workbooks.Open (file1.                 FileName, 0, False, 5, "", "", False, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", True, False, 0, true, 1, 0); int n = workbook.                Worksheets.count;                string[] Sheetset = new String[n];                ArrayList al = new ArrayList (); for (int i = 0; i < n; i++{Sheetset[i] = ((Microsoft.Office.Interop.Excel.Worksheet) workbook. Worksheets[i + 1]).                Name;                } xlApp.Workbooks.Close (); Excelhelper ex = new Excelhelper (file1.                FileName); if (Sheetset.length > 0)//judgment exists a Sheet1 {int countlength = sheetset.length;//indicates She The number of et dt1 = ex.                Exceltodatatable (Sheetset[0], true);//"Sheet1", first if there is only one Sheet1 insertmysql (); }            }        }

Write data:

public void Insertmysql () {try {int columnscount = dt1. columns.count;//gets the number of columns in the table read out as String str = string.                Empty;                string[] Strarr = new String[columnscount]; String valuesstr = String.                Empty;                                List<string> liststr=new list<string> (); for (int i = 0; i < DT1. Rows.Count; i++) {DataRow Dr = Dt1.                    Rows[i]; for (int j = 0; J < Columnscount; J + +) {Strarr[j] = Dr. ITEMARRAY[J].                    ToString (); } string Sqlinsert =string. Format ("INSERT into t_test (torder,tname,stu_num,tage) VALUES (' {0} ', ' {1} ', ' {2} ', ' {3} ')", Strarr[0],strarr[1],strarr                    [2],strarr[3]);                    Liststr.add (Sqlinsert); #region do not use transaction//string sql = "INSERT into T_test (torder,tname,stu_num,tage) vAlues (@torder, @tname, @stu_num, @tage) ";                    Mysqlparameter[] Parms = {//New Mysqlparameter ("@torder", strarr[0]), New Mysqlparameter ("@tname", strarr[1]),//New MyS                    Qlparameter ("@stu_num", strarr[2]),//New Mysqlparameter ("@tage", Strarr[3])                    //                     }; Here you can directly call ExecuteNonQuery to the database plug-in data//Mysqlhelper.executenonquery (mysqlhelper.connectionstring, Comman                    Dtype.text, SQL, parms); #endregion} executesqltran (LISTSTR);//Use transaction Insert//button1. Text = "Insert succeeded!"                ";                Thread.Sleep (10000); Button1. Text = "Insert successfully, please select Excel file!"            ";            } catch (Exception ex) {throw ex; }                  }

Mysqlhelper: should put executesqltran this kind of method into this class, here I did not do so, project small (zero demand) did not do so!

public static string connectionString = system.configuration.configurationmanager.connectionstrings[" Dbconnectstring "]. ToString ();

　　

　

Import Excel data to MYSQL