将Excel⽂件数据导⼊到SqlServer数据库的三种⽅案
最近在⼀个项⽬中需要⽤到Excel⽂件导⼊数据库的功能，本⼈很懒，所以到⽹上搜了⼀堆⽅法，但是通过对⽐，觉得⼀下三种是⽐较好⽤或者不是很常见的⽅法，希望对⼤家有所帮助。
⽅案⼀：通过OleDB⽅式获取Excel⽂件的数据，然后通过DataSet中转到SQL Server，这种⽅法的优点是⾮常的灵活，可以对Excel表中的各个单元格进⾏⽤户所需的操作。
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";
9.
10. try
11. {
12. OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
13. oleDbConnection.Open();
14.
15. //获取excel表
16. DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
17.
18. //获取sheet名，其中[0][1]...[N]: 按名称排列的表单元素
19. string tableName = dataTable.Rows[0][2].ToString().Trim();
20. tableName = "[" + tableName.Replace("'","") + "]";
21.
22. //利⽤SQL语句从Excel⽂件⾥获取数据
excel连接sql数据库教程23. //string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName;
24. string query = "SELECT ⽇期，开课城市，讲师，课程名称，持续时间 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. //从excel⽂件获得数据后，插⼊记录到SQL Server的数据表
32. DataTable dataTable1 = new DataTable();
33.
34. SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT classID, classDate,
35. classPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info",sqlConnection1);
36.
37. //SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
38.
39. sqlDA1.Fill(dataTable1);
40.
41. foreach(DataRow dataRow in dataSet.Tables["gch_Class_Info"].Rows)
42. {
43. DataRow dataRow1 = dataTable1.NewRow();
44.
45. dataRow1["classDate"] = dataRow["⽇期"];
46. dataRow1["classPlace"] = dataRow["开课城市"];
47. dataRow1["classTeacher"] = dataRow["讲师"];
48. dataRow1["classTitle"] = dataRow["课程名称"];
49. dataRow1["durativeDate"] = dataRow["持续时间"];
50.
51. dataTable1.Rows.Add(dataRow1);
52. }
53.
54. Console.WriteLine("新插⼊ " + dataTable1.Rows.Count.ToString() + " 条记录");
55. sqlDA1.Update(dataTable1);
56.
57. oleDbConnection.Close();
58.
59. }
60. catch(Exception ex)
61. {
62. Console.WriteLine(ex.ToString());
63. }
64. }
⽅案⼆：直接通过SQL语句执⾏SQL Server的功能函数将Excel⽂件转换到SQL Server数据库。
OpenFileDialog openFileDialog = new OpenFileDialog();
1. openFileDialog.Filter = "Excel files(*.xls)|*.xls";
2.
3. SqlConnection sqlConnection1 = null;
4.
5. if(openFileDialog.ShowDialog()==DialogResult.OK)
6. {
7. string filePath = openFileDialog.FileName;
8.
9. sqlConnection1 = new SqlConnection();
10. sqlConnection1.ConnectionString = "server=(local);integrated security=SSPI;initial catalog=Library";
11.
12. //import excel into SQL Server 2000
13. /*string importSQL = "SELECT * into live41 FROM OpenDataSource" +
14. "('Microsoft.Jet.OLEDB.4.0','Data Source=" + "\"" + "E:\\022n.xls" + "\"" +
15. "; User ID=;Password=; Extended properties=Excel 5.0')...[Sheet1$]";*/
16.
17. //export SQL Server 2000 into excel
18. string exportSQL = @"p_cmdshell
19. 'bcp Library.dbo.live41 out " + filePath + "-c -q -S" + "\"" + "\"" +
20. " -U" + "\"" + "\"" + " -P" + "\"" + "\"" + "\'";
21.
22. try
23. {
24. sqlConnection1.Open();
25.
26. //SqlCommand sqlCommand1 = new SqlCommand();
27. //sqlCommand1.Connection = sqlConnection1;
28. //sqlCommand1.CommandText = importSQL;
29. //sqlCommand1.ExecuteNonQuery();
30. //MessageBox.Show("import finish!");
31.
32. SqlCommand sqlCommand2 = new SqlCommand();
33. sqlCommand2.Connection = sqlConnection1;
34. sqlCommand2.CommandText = exportSQL;
35. sqlCommand2.ExecuteNonQuery();
36. MessageBox.Show("export finish!");
37. }
38. catch(Exception ex)
39. {
40. MessageBox.Show(ex.ToString());
41. }
42. }
43.
44. if(sqlConnection1!=null)
45. {
46. sqlConnection1.Close();
47. sqlConnection1 = null;
48. }
⽅案三：通过到⼊Excel的VBA dll，通过VBA接⼝获取Excel数据到DataSet
1. OpenFileDialog openFile = new OpenFileDialog();
2. openFile.Filter = "Excel files(*.xls)|*.xls";
3.
4. ExcelIO excelio = new ExcelIO();
5.
6. if(openFile.ShowDialog()==DialogResult.OK)
7. {
8. if(excelio!=null)
9. excelio.Close();
10.
11. excelio = new ExcelIO(openFile.FileName);
12. object[,] range = excelio.GetRange();
13. excelio.Close();
14.
15.
16. DataSet ds = new DataSet("xlsRange");
17.
18. int x = range.GetLength(0);
19. int y = range.GetLength(1);
20.
21. DataTable dt = new DataTable("xlsTable");
22. DataRow dr;
23. DataColumn dc;
24.
25. ds.Tables.Add(dt);
26.
27. for(int c=1; c<=y; c++)
28. {
29. dc = new DataColumn();
30. dt.Columns.Add(dc);
31. }
32.
33. object[] temp = new object[y];
34.
35. for(int i=1; i<=x; i++)
36. {
37. dr = dt.NewRow();
38.
39. for(int j=1; j<=y; j++)
40. {
41. temp[j-1] = range[i,j];
42. }
43.
44. dr.ItemArray = temp;
45. ds.Tables[0].Rows.Add(dr);
46. }
47.
48. dataGrid1.SetDataBinding(ds,"xlsTable");
49.
50. if(excelio!=null)
51. excelio.Close();
52. }
当然还有其他⼀些⽅法，如遍历Excel⽂件中的数据然后构造sql语句，直接利⽤sql操作Excel⽂件导⼊数据库等，这些都是很常见的⽅法，因此就不再做收录了。最后说明下，以上的⽅法是我从⽹上的源码并做了⼀定的修改。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论