實現將Excel文件數據庫導入SQL Server 實現教程及代碼

將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;
}