Three solutions to import Excel file data to SqlServer Database

Solution 1: Use OleDB to obtain data from an Excel file and then use DataSet to transfer data to SQL Server. This method has the advantages of being flexible, you can perform operations on cells in an Excel table as needed.

 

1. OpenFileDialog = new OpenFileDialog ();
2. OpenFileDialog. Filter = "Excel files (*. xls) | *. xls ";
3.  
4. If (openFileDialog. ShowDialog () = DialogResult. OK)
5. {
6. FileInfo fileInfo = new FileInfo (openFileDialog. FileName );
7. String filePath = fileInfo. FullName;
8. String connExcel = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source =" + filePath + "; Extended Properties = Excel 8.0 ";
10. Try
11. {
12. OleDbConnection oleDbConnection = new OleDbConnection (connExcel );
13. OleDbConnection. Open ();
15. // Obtain an excel table
16. DataTable dataTable = oleDbConnection. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, null );
17.  
18. // Obtain the sheet name, where [0] [1]... [N]: The form Element arranged by name
19. String tableName = dataTable. Rows [0] [2]. ToString (). Trim ();
20. TableName = "[" + tableName. Replace ("", "") + "]";
21.  
22. // Obtain data from an Excel file using SQL statements
23. // String query = "SELECT classDate, classpdate, classTeacher, classTitle, classID FROM" + tableName;
24. String query = "SELECT date, course class city, Lecturer, course name, duration FROM" + tableName;
25. DataSet = new DataSet ();
26.  
27. // OleDbCommand oleCommand = new OleDbCommand (query, oleDbConnection );
28. // OleDbDataAdapter oleAdapter = new OleDbDataAdapter (oleCommand );
29. OleDbDataAdapter oleAdapter = new OleDbDataAdapter (query, connExcel );
30. OleAdapter. Fill (dataSet, "gch_Class_Info ");
31. // Insert records into the SQL Server data table after obtaining data from the excel file
32. DataTable ableable1 = new DataTable ();
34. SqlDataAdapter sqlDA1 = new SqlDataAdapter (@ "SELECT classID, classDate,
35. ClassPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info ", sqlConnection1 );
37. // SqlCommandBuilder sqlCB1 = new SqlCommandBuilder (sqlDA1 );
39. SqlDA1.Fill (dataTable1 );
40.  
41. Foreach (DataRow dataRow in dataSet. Tables ["gch_Class_Info"]. Rows)
42. {
43. DataRow dataRow1 = ableable1.newrow ();
45. DataRow1 ["classDate"] = dataRow ["date"];
46. DataRow1 ["classPlace"] = dataRow ["course class city"];
47. DataRow1 ["classTeacher"] = dataRow [""];
48. & Nb