前段時間考試系統(tǒng)要新添加一個功能，要把學(xué)生表的信息批量導(dǎo)入，也就是需要從excel中導(dǎo)入到數(shù)據(jù)庫表，小女子不才，找了好長時間才解決。
一、如果表是沒有建立的，我們需要在數(shù)據(jù)庫表中重新建立一個表盛放excel數(shù)據(jù)的時候：
在sql server中的導(dǎo)入語句：
SELECT * intocity2 FROM OpenDataSource( 'Micros 前段時間考試系統(tǒng)要新添加一個功能，要把學(xué)生表的信息批量導(dǎo)入，也就是需要從excel中導(dǎo)入到數(shù)據(jù)庫表，小女子不才，找了好長時間才解決。
一、如果表是沒有建立的，我們需要在數(shù)據(jù)庫表中重新建立一個表盛放excel數(shù)據(jù)的時候：
在sql server中的導(dǎo)入語句：
SELECT * intocity2 FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]
這里需要注意的是，如果直接寫這個語句，會出現(xiàn)這樣的錯誤：
SQL Server 阻止了對組件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此服務(wù)器安全配置的一部分而被關(guān)閉。系統(tǒng)管理員可以通過使用sp_configure 啟用'Ad Hoc Distributed Queries'。有關(guān)啟用'Ad HocDistributed Queries' 的詳細信息，請參閱SQL Server 聯(lián)機叢書中的"外圍應(yīng)用配置器"。
所以，我們這里需要啟動服務(wù)：
啟動語句為：
execsp_configure 'show advanced options',1
reconfigure
execsp_configure 'Ad Hoc Distributed Queries',1
reconfigure |
當然，用完之后要記得關(guān)閉：
關(guān)閉語句為：
execsp_configure 'Ad Hoc Distributed Queries',0
reconfigure
execsp_configure 'show advanced options',0
reconfigure |
因為考試系統(tǒng)是基于asp.net實現(xiàn)的，所以，一下是asp.net的實現(xiàn)代碼，需要注意的是，因為語句中存在”，\等特殊符號，所以，我們需要使用轉(zhuǎn)義字符來使這些特殊符號成為字符串類型，這里是一些常用的轉(zhuǎn)義字符符號：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();
} |
這樣，數(shù)據(jù)庫中會建立一個city表，來存儲excel中的數(shù)據(jù)。
二、將excel表導(dǎo)入到已經(jīng)存在的數(shù)據(jù)庫表
這里需要注意的是，excel表中的數(shù)據(jù)必須要和數(shù)據(jù)庫表中的數(shù)據(jù)一致
比如，如果數(shù)據(jù)庫表的字段為
則相應(yīng)的excel的表字段為：
oft.Jet.OLEDB.4.0', 'DataSource="f:\test.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]
這里需要注意的是，如果直接寫這個語句，會出現(xiàn)這樣的錯誤：
SQL Server 阻止了對組件'Ad HocDistributed Queries' 的STATEMENT'OpenRowset/OpenDatasource' 的訪問，因為此組件已作為此服務(wù)器安全配置的一部分而被關(guān)閉。系統(tǒng)管理員可以通過使用sp_configure 啟用'Ad Hoc Distributed Queries'。有關(guān)啟用'Ad HocDistributed Queries' 的詳細信息，請參閱SQL Server 聯(lián)機叢書中的"外圍應(yīng)用配置器"。
所以，我們這里需要啟動服務(wù)：
啟動語句為：
execsp_configure 'show advanced options',1
reconfigure
execsp_configure 'Ad Hoc Distributed Queries',1
reconfigure |
當然，用完之后要記得關(guān)閉：
關(guān)閉語句為：
execsp_configure 'Ad Hoc Distributed Queries',0
reconfigure
execsp_configure 'show advanced options',0
reconfigure |
因為考試系統(tǒng)是基于asp.net實現(xiàn)的，所以，一下是asp.net的實現(xiàn)代碼，需要注意的是，因為語句中存在”，\等特殊符號，所以，我們需要使用轉(zhuǎn)義字符來使這些特殊符號成為字符串類型，這里是一些常用的轉(zhuǎn)義字符符號：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();
} |
這樣，數(shù)據(jù)庫中會建立一個city表，來存儲excel中的數(shù)據(jù)。
二、將excel表導(dǎo)入到已經(jīng)存在的數(shù)據(jù)庫表
這里需要注意的是，excel表中的數(shù)據(jù)必須要和數(shù)據(jù)庫表中的數(shù)據(jù)一致
比如，如果數(shù)據(jù)庫表的字段為
則相應(yīng)的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();
} |
這里同樣需要開啟服務(wù)，和第一種的方式一樣。
三、既然已經(jīng)存在的表，一般都會存在一些設(shè)置，比如說主鍵、外鍵或者是其他，如果主鍵或者外鍵沖突，就會出現(xiàn)導(dǎo)入失敗的問題。所以，我們需要對excel表中的數(shù)據(jù)進行判斷。
則先需要把數(shù)據(jù)導(dǎo)入到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++)
{
//導(dǎo)入數(shù)據(jù)庫，把數(shù)據(jù)寫入數(shù)據(jù)庫應(yīng)該就是非常簡單了，這里就不多寫了
}
}
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中的數(shù)據(jù)一條一條的取出來判斷至少是非常耗時間耗內(nèi)存的，而且這里最好加上回滾事物，因為在我們導(dǎo)入過程中會經(jīng)常出現(xiàn)這樣活那樣的問題，采用事物，可以在出錯的時候把數(shù)據(jù)回滾到?jīng)]有導(dǎo)入之前的狀態(tài)，防止意外事件發(fā)生，這里我就不往上加了。
以上是三種是我們實現(xiàn)了的excel導(dǎo)入，當然，我想方法還不止這些，當然，除了導(dǎo)入，還有的就是從數(shù)據(jù)庫表導(dǎo)出到excel表中，因為我做的那部分系統(tǒng)沒有涉及到，所以這里就不再提了。
下面說一下我在解決的過程中繞的彎路：
一、我沒有把導(dǎo)入數(shù)據(jù)庫的種種做法弄清楚，比如是直接創(chuàng)建表呢還是在已經(jīng)存在的表中導(dǎo)入，所以以至于剛開始總是找不到合適的方法。
二、在后來的查找過程中，我發(fā)現(xiàn)我有一個很大的毛病，就是我的關(guān)鍵字是在“asp.net中、、、、、”，其實，既然是往數(shù)據(jù)庫中導(dǎo)入，asp.net只是一個執(zhí)行一下，所以，因為有了關(guān)鍵字的約束，查出來的資料少不說，而且還形成了一種思維定式，除了asp.net之外的其他都一概不看。
三、對查不來的信息不能加以理解，只是能用就用，不能用就換，也就是我因為轉(zhuǎn)義字符串那一塊弄了很長時間的原因，因為當我們在sql執(zhí)行查詢語句沒有錯誤的時候，需要把它放在asp.net中執(zhí)行，因為這些語句需要string字符串來顯示，而這個執(zhí)行語句中有包含引號，所以需要轉(zhuǎn)義字符，在解決引號的問題之后，我發(fā)現(xiàn)還是不正確，一直折騰了好久才發(fā)現(xiàn)是路徑F:\test.xls中“\t“是table的轉(zhuǎn)義字符，所以這里需要兩個\來轉(zhuǎn)義\，這就是應(yīng)該寫成這樣“F:\\test.xls”，而當我寫成這樣的時候，我才想起來，其實最開始查詢的時候所有的代碼都是這樣的，只是那些我沒用上，當時也沒多想，以為路徑就應(yīng)該是這樣的，最終導(dǎo)致還繼續(xù)在這個上面栽跟頭。