Import an Excel table to a database

Import Excel table data to SQL database

The Code is as follows:

String [] data = new string [3]; // defines an array to store the data read in the drd string filePathName = ""; // storage file name // define an open file dialog box and set the relevant attributes OpenFileDialog importFileDialog = new OpenFileDialog (); importFileDialog. addExtension = true; importFileDialog. checkFileExists = true; importFileDialog. checkPathExists = true; importFileDialog. filter = "Excel table file (97-2003 )(*. xls) | *. xls | Excel table file | *. xlsx "; importFileDialog. initialDirectory = "3"; im PortFileDialog. title = "select the imported terminal information file"; importFileDialog. multiselect = false; // determines whether the user selects the "open" operation if (importFileDialog. showDialog () = System. windows. forms. dialogResult. OK) {filePathName = importFileDialog. fileName; string strConn = ""; // The extension name of the extension file is .xlsx if (filePathName. substring (filePathName. length-5 ). toLower () = ". xlsx ") {strConn =" Provider = Microsoft. ACE. OLEDB.12.0; Data Source = '"+ filePathName + "'; Extended Properties = 'excel 12.0; HDR = YES'";} // The extension name of the. xls else if (filePathName. substring (filePathName. length-4 ). toLower () = ". xls ") {strConn =" Provider = Microsoft. jet. OLEDB.4.0; Data Source = '"+ filePathName +"'; Extended Properties = 'excel 8.0; HDR = YES; '";}else {MessageBox. show ("the selected file is invalid! "); Return;} OleDbConnection conn = new OleDbConnection (strConn); string strSqlConn =" Data Source = (local); Initial Catalog = IMPORT; Persist Security Info = False; user ID = sa; pwd = sa "; SqlConnection sqlConn = new SqlConnection (strSqlConn); sqlConn. open (); if (conn. state. toString () = "Closed") conn. open (); // read the file statement string strSql = "SELECT * FROM [sheet1 $]"; OleDbCommand cmd = new OleDbCommand (strSql, Conn); OleDbDataReader drd = cmmd. executeReader (); // start to read data in drd. to read data, make sure that the data in each column in Excel is correct for the data in the database table. // note that when reading Excel, it does not read the first row of data. // (I don't know why, but I think it's possible to jump to the title line in Excel) while (drd. read () {data [0] = drd [0]. toString (); data [1] = drd [1]. toString (); data [2] = drd [2]. toString (); // ********************** this part can be separately separated *********// define insert SQL statement // here, the fields in my database table are as follows // [ImportField1], [ImportField2], [ImportField3] StringBuilder sbSql = new StringBuilder (); sbSql. append ("insert into [tbImport]"); sbSql. append ("("); sbSql. append ("[ImportField1]"); sb SQL. append (", [ImportField2]"); sbSql. append (", [ImportField3]"); sbSql. append (") VALUES ("); sbSql. append (String. format ("'{0}',", data [0]); sbSql. append (String. format ("'{0}',", data [1]); sbSql. append (String. format ("'{0}',", data [2]); sbSql. append (");"); SqlCommand sqlcmd = new SqlCommand (sbSql. toString (), sqlConn ); //************************************** * sqlcmcmmd. executeNonQuery () ;} Conn. Close (); sqlConn. Close (); MessageBox. Show ("data import successful. ");}