基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出研究與實(shí)現(xiàn)

蔡小艷

摘 要：為了提高信息管理系統(tǒng)中數(shù)據(jù)導(dǎo)入導(dǎo)出操作的靈活性和通用性，本文詳細(xì)介紹了在Visual Studio 2008使用C#語言、通過引用Office的COM組件技術(shù)，結(jié)合Microsoft SQL Server 2005實(shí)現(xiàn)了Excel數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫以及數(shù)據(jù)庫數(shù)據(jù)導(dǎo)出到Excel的設(shè)計(jì)思路和實(shí)現(xiàn)方法。實(shí)踐證明該方法有較強(qiáng)的通用性和實(shí)用性。

關(guān)鍵詞：C#；COM組件；Excel；導(dǎo)入導(dǎo)出

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：2095-2163（2014）05-

Research and Implementation of Excel Data Import and Export Based on C#

CAI Xiaoyan， LI Longteng， GE Yu， ZHENG Yan

（Wuhan ordnance officer school，Wuhan 430075，China）

Abstract： In order to improve the flexibility and popularity of data import and export in information management system， this paper introduces the ideas and the ways of implementation of Excel data importing to database and database data exporting to Excel， which combined with Microsoft SQL Server 2005 in Visual Studio 2008， uses C# programme， and applies COM components technology of Office. The operation practices show that the method if widely useful and available.

Key words： C#； COM components； Excel； import and export

0 引 言

Excel是Office的重要組成，是保存統(tǒng)計(jì)數(shù)據(jù)的常用軟件格式之一。在程序編制和數(shù)據(jù)匯總交換過程中，能否將系統(tǒng)中錄入的數(shù)據(jù)以及查詢的結(jié)果按標(biāo)準(zhǔn)的格式導(dǎo)出為Excel報(bào)表，以及能否將Excel表中的數(shù)據(jù)進(jìn)行整理后批量導(dǎo)入數(shù)據(jù)庫，以上過程均對(duì)系統(tǒng)設(shè)計(jì)提出了較高要求。基于此，本文即介紹了在.Net環(huán)境下，使用C#語言實(shí)現(xiàn)系統(tǒng)中Excel數(shù)據(jù)與SQL Server2005數(shù)據(jù)庫中數(shù)據(jù)的導(dǎo)入導(dǎo)出。

1 設(shè)計(jì)思路

1.1數(shù)據(jù)導(dǎo)入設(shè)計(jì)思路

在此，將Excel表格中的數(shù)據(jù)導(dǎo)入到SQL Server數(shù)據(jù)庫的設(shè)計(jì)思路進(jìn)行如下表述：

（1）由c#程序控制Excel表格，首先打開Excel表格；

（2）運(yùn)用SQL語句，執(zhí)行SQL，并根據(jù)不同的規(guī)則，將表格中的數(shù)據(jù)讀入到臨時(shí)表temp_table中；

（3）通過對(duì)比temp_table和table，判斷是否存在重復(fù)的數(shù)據(jù)，提示是否重復(fù)導(dǎo)入；

（4）如果重復(fù)導(dǎo)入，可將temp_table的數(shù)據(jù)直接添加到table中；否則即需將temp_table中除關(guān)鍵字沖突外的其它數(shù)據(jù)添加到table中。

1.2數(shù)據(jù)導(dǎo)出設(shè)計(jì)思路

相應(yīng)地，SQL Server數(shù)據(jù)庫中的數(shù)據(jù)導(dǎo)出到Excel表格的設(shè)計(jì)思路[1-3]可列述如下：

（1）選擇需要導(dǎo)出的數(shù)據(jù)；

（2）利用Worksheet類創(chuàng)建Excel工作表對(duì)象，確定即將寫入數(shù)據(jù)的單元格位置；

（3）從數(shù)據(jù)庫中讀取數(shù)據(jù)，根據(jù)數(shù)據(jù)類型的不同，選擇不同的寫入方式；

（4）保存Excel表格并退出內(nèi)存。

數(shù)據(jù)批量導(dǎo)入和數(shù)據(jù)批量導(dǎo)出的設(shè)計(jì)流程可分別表示為如圖1和圖2所示。

圖1 數(shù)據(jù)導(dǎo)入流程 圖2 數(shù)據(jù)導(dǎo)出流程

Fig.1 Process of data import Fig.2 Process of data export

2關(guān)鍵技術(shù)實(shí)現(xiàn)

2.1數(shù)據(jù)導(dǎo)出

2.1.1添加COM組件的引用

只有添加Excel的COM組件才能在程序中調(diào)用Excel。添加COM組件的引用步驟可做如下實(shí)現(xiàn)[4]：

在“解決方案資源管理器”窗口中右擊“引用”對(duì)象，并在彈出的快捷菜單中選擇“添加引用”；再在彈出的“添加引用”對(duì)話框中選擇“COM”選項(xiàng)卡，如果基于Excel 2003，則選擇“Microsoft Excel 11.0 object library”；如果基于Excel 2007，則選擇“Microsoft Excel 12.0 object library”；同時(shí)單擊“確定”按鈕，即將Excel對(duì)象引用到工程中。

需要指出的是，在編碼中主要涉及以下兩種處理功能的類：

（1） Microsoft.Office.Interop.Excel.Application：接口，表示一個(gè)Excel程序；

（2） Microsoft.Office.Interop.Excel.Workbook：接口，表示一個(gè)Excel的工作薄。

2.1.2 創(chuàng)建Excel表格

微軟的Excel對(duì)象模型包括了數(shù)百個(gè)可供用戶交互的對(duì)象，本系統(tǒng)主要使用Application、Workbook、Worksheet和Range四個(gè)類及其內(nèi)含成員。其中，Application對(duì)象處于Excel對(duì)象模型的頂層，表示整個(gè)Excel應(yīng)用程序；Workbook對(duì)象處于Application對(duì)象的下層，表示一個(gè)Excel工作薄文件；Worksheet對(duì)象包含于Workbook對(duì)象，表示一個(gè)Excel工作表；Range對(duì)象包含于worksheet對(duì)象之中，表示Excel工作表中的一個(gè)或多個(gè)單元格。

創(chuàng)建Excel表格的代碼實(shí)現(xiàn)過程如下：

Excel.Application excelApp = new Excel.Application（）； //創(chuàng)建Excel對(duì)象

Excel.Workbook wb = excelApp.Workbooks.Open（filename， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing）； //創(chuàng)建Excel工作薄

Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet； //引用工作表

此時(shí)創(chuàng)建的Excel表格ws是一個(gè)沒有內(nèi)容的空表格，接下來將數(shù)據(jù)導(dǎo)出到該表格中。

2.1.3 將數(shù)據(jù)庫數(shù)據(jù)導(dǎo)出到Excel

（1）讀取數(shù)據(jù)表信息

將指定的數(shù)據(jù)表中的數(shù)據(jù)讀取到DataSet中，具體步驟如下[5]：

①設(shè)置連接數(shù)據(jù)庫字符串；②設(shè)置查詢語句；③打開數(shù)據(jù)庫連接，創(chuàng)建SQL數(shù)據(jù)適配器；④定義DataSet對(duì)象DS，填充數(shù)據(jù)；⑤關(guān)閉數(shù)據(jù)庫連接。

（2）向工作表的每個(gè)單元格寫入數(shù)據(jù)

工作表變量ws包含有一個(gè)“Cell”屬性，這個(gè)屬性代表Excel表格中的每一個(gè)單元格，通過對(duì)“Cell”屬性賦值，從而實(shí)現(xiàn)了向Excel表格寫入相應(yīng)數(shù)據(jù)的設(shè)計(jì)需要。向Excel工作表寫入數(shù)據(jù)的核心代碼如下：

for （int i = 0； i < DS.Tables[0].Rows.Count； i++）

{

object rng = Type.Missing；

start = 0；

end = 0；

this.InsertRows（ws， i + 5）；//生成Excel文件時(shí)動(dòng)態(tài)添加行

for （int j = 0； j < DS.Tables[0].Columns.Count； j++）

{

ws.Cells[i + 5， j + 1] = DS.Tables[0].Rows[i][j].ToString（）；

}

}

private void InsertRows（Excel.Worksheet sheet， int rowIndex）

{

object missing = System.Reflection.Missing.Value；

Excel.Range r = （Excel.Range）sheet.Rows[rowIndex，missing]；

r.Insert（Excel.XlInsertShiftDirection.xlShiftDown， missing）；

}

2.1.4 保存Excel表格并退出內(nèi)存

wb.Save（）； //保存Excel文件

excelApp.Quit（）； //關(guān)閉Excel對(duì)象，回收資源

2.2數(shù)據(jù)導(dǎo)入

2.2.1 打開Excel表格文件

在讀取Excel表中數(shù)據(jù)時(shí)，先是通過文件路徑得到Excel文件，并根據(jù)Workbook類獲取workbook（工作簿），即可訪問sheet（工作表）。對(duì)應(yīng)的實(shí)現(xiàn)代碼如下：

Excel.Application excelApp = new Excel.Application（）；

Excel.Workbook wb = excelApp.Workbooks.Open（textBox1.Text， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing， Nothing）；

Excel.Worksheet ws = wb.Worksheets[1] as Excel.Worksheet；

2.2.2 將Excel文件數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫臨時(shí)表

通過SQL的Insert語句將Excel對(duì)象中的數(shù)據(jù)插入到指定的數(shù)據(jù)庫表中。該過程的詳細(xì)代碼實(shí)現(xiàn)為：

for （int i = iStartRow； i <= iEndRow； i++）

{

sqlstr = "insert into temp_table （列1，列2…列n） values（"；

for （int j = 1； j <= iEndCol； j++）

{

if （（（Excel.Range）ws.Cells[i， j]）.Value2 == null）// 判斷導(dǎo)入的列值是否為空

sqlstr += "，null"；

else

{

if （j == 1） //導(dǎo)入第一列值

{

sqlstr += "'" + （（Excel.Range）ws.Cells[i， j]）.Value2.ToString（） + "'"；

}

else

sqlstr += "，'" + （（Excel.Range）ws.Cells[i， j]）.Value2.ToString（） + "'"；

}

}

sqlstr = sqlstr + "）"；

執(zhí)行sql語句

}

2.2.3 將臨時(shí)表中的數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫

在進(jìn)行插入數(shù)據(jù)庫操作前，將需要判斷插入的數(shù)據(jù)是否有重復(fù)，如果重復(fù)，則覆蓋原數(shù)據(jù)庫中數(shù)據(jù)。判斷的標(biāo)準(zhǔn)，可視數(shù)據(jù)庫表中主鍵列數(shù)據(jù)與導(dǎo)入到臨時(shí)表中對(duì)應(yīng)的數(shù)據(jù)是否相同而定。一旦相同，即刪除臨時(shí)表中對(duì)應(yīng)行數(shù)據(jù)，再將臨時(shí)表中剩余的數(shù)據(jù)插入到指定表；反之，一旦如果不同，即直接進(jìn)行數(shù)據(jù)導(dǎo)入。其程序?qū)崿F(xiàn)過程可表述為：

DataSet ds = mydataclass1.getDataSet（"select * from temp_table where 主關(guān)鍵字 in （select 主關(guān)鍵字 from table） "）；

if （ds.Tables[0].Rows.Count > 0） //存在重復(fù)的數(shù)據(jù)

{

string querystr = "導(dǎo)入數(shù)據(jù)中有" + ds.Tables[0].Rows.Count + "條數(shù)據(jù)與數(shù)據(jù)庫中已有數(shù)據(jù)重復(fù)，是否覆蓋導(dǎo)入？"；

if （MessageBox.Show（querystr， "提示信息"， MessageBoxButtons.YesNo， MessageBoxIcon.Information）.ToString（） == "Yes"）

{

string delsql = "delete from temp_table from temp_table join table on

temp_table.主關(guān)鍵字=table.主關(guān)鍵字 "；

執(zhí)行delsql語句；

}

else

{

dataGridView1.DataSource = ds.Tables[0]；//顯示重復(fù)數(shù)據(jù)

dataGridView1.Visible = true；

dataGridView1.BringToFront（）；

btnBrowse.Enabled = false；

return ；

}

}

string inssql = @"insert into table select * from temp_table"；

執(zhí)行inssql語句；

3 結(jié)束語

本文提出了如下的研究創(chuàng)新，具體可表述為：本文采用Visual Studio開發(fā)平臺(tái)，運(yùn)用基于Office的COM組件技術(shù)，結(jié)合Microsoft SQL Server 2005實(shí)現(xiàn)了Excel數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫表以及數(shù)據(jù)庫表數(shù)據(jù)導(dǎo)出到Excel。在導(dǎo)入Excel數(shù)據(jù)文件時(shí)，能實(shí)現(xiàn)覆蓋導(dǎo)入；而在導(dǎo)出Excel數(shù)據(jù)文件時(shí)，則能動(dòng)態(tài)添加行，這種批量的導(dǎo)入導(dǎo)出數(shù)據(jù)能夠提高用戶的工作效率。從應(yīng)用情況看，該種Excel數(shù)據(jù)導(dǎo)入導(dǎo)出方法通用性強(qiáng)，效果理想，值得推廣。

參考文獻(xiàn)：

[1]李明明. VS.net中用后臺(tái)線程向Excel中導(dǎo)入數(shù)據(jù)的研究[J]. 測(cè)控技術(shù)，2011，30（8）：83-86.

[2]蘇慶，李忠良，吳偉民.在C#.NET下實(shí)現(xiàn)數(shù)據(jù)導(dǎo)入Excel的方法研究[J]. 計(jì)算機(jī)與現(xiàn)代化，2011（4）：29-31.

[3]王森. 基于C#的Excel文檔導(dǎo)入技術(shù)的研究與實(shí)現(xiàn)[J]. 辦公自動(dòng)化，2011（16）：54-55.

[4]樊宇. 利用COM組件將SQL Server中的數(shù)據(jù)導(dǎo)入到Excel[J]. 電腦編程技巧與維護(hù)，2009（20）：65-66.

[5]王晟. Visual C#.NET數(shù)據(jù)庫開發(fā)經(jīng)典案例解析[M]. 北京：清華大學(xué)出版社，2010.