將Excel文件數據庫導入SQL?Server的三種方案//方案一：?通過OleDB方式獲取Excel文件的數據，然后通過DataSet中轉到SQL?Server

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");

????????//dataGrid1.DataSource?=?dataSet;
????????//dataGrid1.DataMember?=?tableName;
????????dataGrid1.SetDataBinding(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["講師"];
????????????dataRow1["classTitle"]?=?dataRow["課程名稱"];
????????????dataRow1["durativeDate"]?=?dataRow["持續時間"];

????????????dataTable1.Rows.Add(dataRow1);
????????}

????????Console.WriteLine("新插入?"?+?dataTable1.Rows.Count.ToString()?+?"?條記錄");
????????sqlDA1.Update(dataTable1);
????????
????????oleDbConnection.Close();

????}
????catch(Exception?ex)
????{
????????Console.WriteLine(ex.ToString());
????}
}

?

?

//方案二：?直接通過SQL語句執行SQL?Server的功能函數將Excel文件轉換到SQL?Server數據庫

OpenFileDialog?openFileDialog?=?new?OpenFileDialog();
openFileDialog.Filter?=?"Excel?files(*.xls)|*.xls";

SqlConnection?sqlConnection1?=?null;

if(openFileDialog.ShowDialog()==DialogResult.OK)
{
????string?filePath?=?openFileDialog.FileName;

????sqlConnection1?=?new?SqlConnection();
????sqlConnection1.ConnectionString?=?"server=(local);integrated?security=SSPI;initial?catalog=Library";

????//import?excel?into?SQL?Server?2000
????/*string?importSQL?=?"SELECT?*?into?live41?FROM?OpenDataSource"?+?
????????"('Microsoft.Jet.OLEDB.4.0','Data?Source="?+?"/""?+?"E://022n.xls"?+?"/""?+?
????????";?User?ID=;Password=;?Extended?properties=Excel?5.0')...[Sheet1$]";*/

????//export?SQL?Server?2000?into?excel
????string?exportSQL?=?@"EXEC?master..xp_cmdshell
'bcp?Library.dbo.live41?out?"?+?filePath?+?"-c?-q?-S"?+?"/""?+?"/""?+
????????"?-U"?+?"/""?+?"/""?+?"?-P"?+?"/""?+?"/""?+?"/'";
????
????try
????{
????????sqlConnection1.Open();
????????
????????//SqlCommand?sqlCommand1?=?new?SqlCommand();
????????//sqlCommand1.Connection?=?sqlConnection1;
????????//sqlCommand1.CommandText?=?importSQL;
????????//sqlCommand1.ExecuteNonQuery();
????????//MessageBox.Show("import?finish!");
????????
????????SqlCommand?sqlCommand2?=?new?SqlCommand();
????????sqlCommand2.Connection?=?sqlConnection1;
????????sqlCommand2.CommandText?=?exportSQL;
????????sqlCommand2.ExecuteNonQuery();
????????MessageBox.Show("export?finish!");
????}
????catch(Exception?ex)
????{
????????MessageBox.Show(ex.ToString());
????}
}

if(sqlConnection1!=null)
{
????sqlConnection1.Close();
????sqlConnection1?=?null;
}

?

?

//方案三：?通過到入Excel的VBA?dll，通過VBA接口獲取Excel數據到DataSet

OpenFileDialog?openFile?=?new?OpenFileDialog();
openFile.Filter?=?"Excel?files(*.xls)|*.xls";

ExcelIO?excelio?=?new?ExcelIO();

if(openFile.ShowDialog()==DialogResult.OK)
{
????if(excelio!=null)
????????excelio.Close();

????excelio?=?new?ExcelIO(openFile.FileName);
????object[,]?range?=?excelio.GetRange();
????excelio.Close();

????
????DataSet?ds?=?new?DataSet("xlsRange");

????int?x?=?range.GetLength(0);
????int?y?=?range.GetLength(1);

????DataTable?dt?=?new?DataTable("xlsTable");
????DataRow?dr;
????DataColumn?dc;
????
????ds.Tables.Add(dt);

????for(int?c=1;?c<=y;?c++)
????{
????????dc?=?new?DataColumn();
????????dt.Columns.Add(dc);
????}
????
????object[]?temp?=?new?object[y];
????
????for(int?i=1;?i<=x;?i++)
????{
????????dr?=?dt.NewRow();

????????for(int?j=1;?j<=y;?j++)
????????{
????????????temp[j-1]?=?range[i,j];
????????}
????????
????????dr.ItemArray?=?temp;
????????ds.Tables[0].Rows.Add(dr);
????}

????dataGrid1.SetDataBinding(ds,"xlsTable");
????
????if(excelio!=null)
????????excelio.Close();
}

?

?

總結

以上是生活随笔為你收集整理的将Excel文件数据库导入SQL Server的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。