前段時間考試系統要新添加一個功能，要把學生表的信息批量導入，也就是需要從excel中導入到數據庫表，小女子不才，找了好長時間才解決。
一、如果表是沒有建立的，我們需要在數據庫表中重新建立一個表盛放excel數據的時候：
在sql server中的導入語句：
SELECT * intocity2 FROM OpenDataSource( 'Micros 前段時間考試系統要新添加一個功能，要把學生表的信息批量導入，也就是需要從excel中導入到數據庫表，小女子不才，找了好長時間才解決。
一、如果表是沒有建立的，我們需要在數據庫表中重新建立一個表盛放excel數據的時候：
在sql server中的導入語句：
SELECT * intocity2 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]
這里需要注意的是，如果直接寫這個語句，會出現這樣的錯誤：
SQL Server 阻止了對組件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此服務器安全配置的一部分而被關閉。系統管理員可以通過使用sp_configure 啟用'Ad Hoc Distributed Queries'。有關啟用'Ad HocDistributed Queries' 的詳細信息，請參閱SQL Server 聯機叢書中的"外圍應用配置器"。
所以，我們這里需要啟動服務：
啟動語句為：
execsp_configure 'show advanced options',1
reconfigure
execsp_configure 'Ad Hoc Distributed Queries',1
reconfigure |
當然，用完之后要記得關閉：
關閉語句為：
execsp_configure 'Ad Hoc Distributed Queries',0
reconfigure
execsp_configure 'show advanced options',0
reconfigure |
因為考試系統是基于asp.net實現的，所以，一下是asp.net的實現代碼，需要注意的是，因為語句中存在”，\等特殊符號，所以，我們需要使用轉義字符來使這些特殊符號成為字符串類型，這里是一些常用的轉義字符符號：http://baike.baidu.com/view/73.htm
protectedvoid btntoLaad_Click(object sender, EventArgs e)
{
SqlConnectionmycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");
string sqlstr = "SELECT * into cityFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=\"f:\\test.xls\";User ID=Admin;Password=;Extended properties=Excel5.0')...[Sheet1$]";
SqlCommand cmd = new SqlCommand(sqlstr, mycon);
mycon.Open();
cmd.ExecuteNonQuery();
mycon.Close();
} |
這樣，數據庫中會建立一個city表，來存儲excel中的數據。
二、將excel表導入到已經存在的數據庫表
這里需要注意的是，excel表中的數據必須要和數據庫表中的數據一致
比如，如果數據庫表的字段為
則相應的excel的表字段為：
oft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]
這里需要注意的是，如果直接寫這個語句，會出現這樣的錯誤：
SQL Server 阻止了對組件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此服務器安全配置的一部分而被關閉。系統管理員可以通過使用sp_configure 啟用'Ad Hoc Distributed Queries'。有關啟用'Ad HocDistributed Queries' 的詳細信息，請參閱SQL Server 聯機叢書中的"外圍應用配置器"。
所以，我們這里需要啟動服務：
啟動語句為：
execsp_configure 'show advanced options',1
reconfigure
execsp_configure 'Ad Hoc Distributed Queries',1
reconfigure |
當然，用完之后要記得關閉：
關閉語句為：
execsp_configure 'Ad Hoc Distributed Queries',0
reconfigure
execsp_configure 'show advanced options',0
reconfigure |
因為考試系統是基于asp.net實現的，所以，一下是asp.net的實現代碼，需要注意的是，因為語句中存在”，\等特殊符號，所以，我們需要使用轉義字符來使這些特殊符號成為字符串類型，這里是一些常用的轉義字符符號：http://baike.baidu.com/view/73.htm
protectedvoid btntoLaad_Click(object sender, EventArgs e)
{
SqlConnectionmycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");
string sqlstr = "SELECT * into cityFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=\"f:\\test.xls\";User ID=Admin;Password=;Extended properties=Excel5.0')...[Sheet1$]";
SqlCommand cmd = new SqlCommand(sqlstr, mycon);
mycon.Open();
cmd.ExecuteNonQuery();
mycon.Close();
} |
這樣，數據庫中會建立一個city表，來存儲excel中的數據。
二、將excel表導入到已經存在的數據庫表
這里需要注意的是，excel表中的數據必須要和數據庫表中的數據一致
比如，如果數據庫表的字段為
則相應的excel的表字段為：
protectedvoid btnExist_Click(object sender, EventArgs e)
{
SqlConnection mycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");
string sqlstr = " insert intocity1 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource=\"f:\\test.xls\";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]";
SqlCommand cmd = new SqlCommand(sqlstr,mycon);
mycon.Open();
cmd.ExecuteNonQuery();
mycon.Close();
} |
這里同樣需要開啟服務，和第一種的方式一樣。
三、既然已經存在的表，一般都會存在一些設置，比如說主鍵、外鍵或者是其他，如果主鍵或者外鍵沖突，就會出現導入失敗的問題。所以，我們需要對excel表中的數據進行判斷。
則先需要把數據導入到datatable中
protected void btnLeadingIn_Click(objectsender, EventArgs e)
{
DataTable dt=new DataTable();
dt = CreateExcelDataSource("F:\\abc.xls");
SqlConnection sqlCon = con();
sqlCon.Open();
GridView1.DataSource = dt;
GridView1.DataBind();
for (int i = 0; i < dt.Rows.Count;i++)
{
//導入數據庫，把數據寫入數據庫應該就是非常簡單了，這里就不多寫了
}
}
public static DataTableCreateExcelDataSource(string url)
{
DataTable dt = null;
// string connetionStr ="Provider=Microsoft.Ace.OleDb.12.0;" + "Data Source=" + url+ ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;"+ "data source=" + url + ";Extended Properties='Excel 8.0;HDR=YES; IMEX=1'";
string strSql = "select * from[Sheet1$]";
OleDbConnection oleConn = new OleDbConnection(connetionStr);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql,connetionStr);
try
{
dt = new DataTable();
oleAdapter.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
oleAdapter.Dispose();
oleConn.Close();
oleConn.Dispose();
}
}
|
當然，我感覺我這里把datatable中的數據一條一條的取出來判斷至少是非常耗時間耗內存的，而且這里最好加上回滾事物，因為在我們導入過程中會經常出現這樣活那樣的問題，采用事物，可以在出錯的時候把數據回滾到沒有導入之前的狀態，防止意外事件發生，這里我就不往上加了。
以上是三種是我們實現了的excel導入，當然，我想方法還不止這些，當然，除了導入，還有的就是從數據庫表導出到excel表中，因為我做的那部分系統沒有涉及到，所以這里就不再提了。
下面說一下我在解決的過程中繞的彎路：
一、我沒有把導入數據庫的種種做法弄清楚，比如是直接創建表呢還是在已經存在的表中導入，所以以至于剛開始總是找不到合適的方法。
二、在后來的查找過程中，我發現我有一個很大的毛病，就是我的關鍵字是在“asp.net中、、、、、”，其實，既然是往數據庫中導入，asp.net只是一個執行一下，所以，因為有了關鍵字的約束，查出來的資料少不說，而且還形成了一種思維定式，除了asp.net之外的其他都一概不看。
三、對查不來的信息不能加以理解，只是能用就用，不能用就換，也就是我因為轉義字符串那一塊弄了很長時間的原因，因為當我們在sql執行查詢語句沒有錯誤的時候，需要把它放在asp.net中執行，因為這些語句需要string字符串來顯示，而這個執行語句中有包含引號，所以需要轉義字符，在解決引號的問題之后，我發現還是不正確，一直折騰了好久才發現是路徑F:\test.xls中“\t“是table的轉義字符，所以這里需要兩個\來轉義\，這就是應該寫成這樣“F:\\test.xls”，而當我寫成這樣的時候，我才想起來，其實最開始查詢的時候所有的代碼都是這樣的，只是那些我沒用上，當時也沒多想，以為路徑就應該是這樣的，最終導致還繼續在這個上面栽跟頭。