方案一: 通過OleDB方式獲取Excel文件的數據,然後通過DataSet中轉到SQL Server,這種方法的優點是非常的靈活,可以對Excel表中的各個單元格進行用戶所需的操作。
- openFileDialog = new OpenFileDialog();
- openFileDialog.Filter = "Excel files(*.xls)|*.xls";
-
- if(openFileDialog.ShowDialog()==DialogResult.OK)
- {
- FileInfo fileInfo = new FileInfo(openFileDialog.FileName);
- string filePath = fileInfo.FullName;
- string connExcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0";
-
- try
- {
- OleDbConnection oleDbConnection = new OleDbConnection(connExcel);
- oleDbConnection.Open();
-
- //獲取excel表
- DataTable dataTable = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
-
- //獲取sheet名,其中[0][1]...[N]: 按名稱排列的表單元素
- string tableName = dataTable.Rows[0][2].ToString().Trim();
- tableName = "[" + tableName.Replace("","") + "]";
-
- //利用SQL語句從Excel文件裡獲取數據
- //string query = "SELECT classDate,classPlace,classTeacher,classTitle,classID FROM " + tableName;
- string query = "SELECT 日期,開課城市,講師,課程名稱,持續時間 FROM " + tableName;
- dataSet = new DataSet();
-
- //OleDbCommand oleCommand = new OleDbCommand(query, oleDbConnection);
- //OleDbDataAdapter oleAdapter = new OleDbDataAdapter(oleCommand);
- OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query,connExcel);
- oleAdapter.Fill(dataSet,"gch_Class_Info");
- //從excel文件獲得數據後,插入記錄到SQL Server的數據表
- DataTable dataTable1 = new DataTable();
-
- SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT classID, classDate,
- classPlace, classTeacher, classTitle, durativeDate FROM gch_Class_Info",sqlConnection1);
-
- //SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
-
- sqlDA1.Fill(dataTable1);
-
- foreach(DataRow dataRow in dataSet.Tables["gch_Class_Info"].Rows)
- {
- DataRow dataRow1 = dataTable1.NewRow();
-
- dataRow1["classDate"] = dataRow["日期"];
- dataRow1["classPlace"] = dataRow["開課城市"];
- dataRow1["classTeacher"] = dataRow["講師"];
- &nb