基于C#的OLEDB和COM組件對Excel文件操作

陳洪磊 施秋萍

（普洱學(xué)院，云南 普洱 665000）

1 引言

C#，稱為C sharp，是C和C++語言的一種升級計算編程語言，由是微軟公司退出的一種基于.NET框架的高級程序設(shè)計語言。C#是面向?qū)ο蟮木幊陶Z言，易于學(xué)習(xí)，使得程序員可以快速地編寫各種.NET平臺的應(yīng)用程序。

Excel也是微軟公司推出的Office辦公套件中的一個處理表格的專業(yè)軟件，也是當(dāng)今最流行的辦公表格處理軟件[1]。

基于 C#對 Excel的專用應(yīng)用程序可以大大提高工作效率，本文主要介紹兩種常用C#操作Excel方法：COM組件操作法和OLEDB操作法。對于Excel最基本的操作包括：創(chuàng)建Excel文件，對Excel文檔的保存、讀寫、刪除、添加、查找，本文主要通過這些基本操作對兩種操作方式的利弊進(jìn)行了實(shí)驗(yàn)比較。

2 COM組件操作法

COM組件是微軟公司開發(fā)的一種軟件開發(fā)技術(shù)，我們狹義上可以把它理解為一個功能強(qiáng)大的微軟API，當(dāng)然它的所含的概念遠(yuǎn)遠(yuǎn)超過API。由于Excel也是由微軟公司開發(fā)的，所以COM組件中也提供了對Excel操作的一些對象，其中最重要的四個對象為Application，Workbook，Worksheet和Range對象[2]。

當(dāng)打開一個Excel文檔的時候就創(chuàng)建了一個Application對象，可以理解為只要運(yùn)行 Excel程序就創(chuàng)建了一個Application對象。如圖1所示，一個Application中包含了很多個Workbook（Workbooks），這就相當(dāng)于Excel文件可以同時打開很多個工作薄（Workbooks），其中的一個就是Workbook，在一個Workbook中又包含了很多工作表（Worksheets），其中一個就成為Worksheet。在圖1 的結(jié)構(gòu)圖中，最后一個對象Range表示一個工作表（Workbook）中單元格的使用范圍[3][4]。

圖1 Excel對象層級結(jié)構(gòu)

2.1 創(chuàng)建與保存Excel文檔

本文中的編程環(huán)境是Visual Studio 2010，在創(chuàng)建Excel文檔是首先要添加Excel的COM組件，即在項(xiàng)目中添加引用“Microsoft Excel 15.0 Object Library”，這里要特別注意，由于Office辦公套件有很多版本，所以添加引用時也有很多的版本，有些較老的版本是不支持較新的Office文件，通常最好添加新版本的COM組件[5]。

首先使用命名空間：using Excel=Microsoft.Office.Interop.Excel;

Excel.Application NewExcel = new Excel.Application();//新建一個Excel進(jìn)程

NewExcel.Application.Workbooks.Add (true );//在Excel文件中創(chuàng)建一個工作薄

通過以上就可以簡單的創(chuàng)建一個空白的Excel文件，通常情況可以通過Visible屬性來設(shè)置創(chuàng)建的Excel文件可見性，NewExcel.Visible=false這里設(shè)置Excel為不可見。

以上創(chuàng)建空白的Excel后就要對新建文檔進(jìn)行保存，這里使用SaveAs方法：

NewExcel.SaveAs(filename,Missing.Value,Missing.Value,Missi ng.Value,Missing.Value,Missing.Value,Excel.XlSaveAsAccessMo de.xlNoChange,Missing.Value,Missing.Value,Missing.Val ue,Missing.Value, Missing.Value);

SaveAs方法中有很多參數(shù)，這里我們主要關(guān)心其中的filename參數(shù)，其作用是要保存的文件名。可包含完整路徑。如果不指定路徑，文件保存到當(dāng)前文件夾中。其他的參數(shù)設(shè)置一般使用默認(rèn)方式，詳細(xì)了解可以通過微軟官方MSDN網(wǎng)站[1]。

2.2 寫入與讀取數(shù)據(jù)

2.2.1 寫入數(shù)據(jù)

在創(chuàng)建一個新的工作薄后，可以直接使用“Cells”對新建的Excel賦值，具體代碼如下：

NewExcel.Cells[1,1]=”第一行第一列”;

NewExcel.Cells[1,2]=”第一行第二列”;

這里的行列號是從1開始排列，不是從0開始。

2.2.2 讀取數(shù)據(jù)

讀取數(shù)據(jù)相對于寫入數(shù)據(jù)較為復(fù)雜，通常要讀取數(shù)據(jù)首先要打開有數(shù)據(jù)的 Excel文檔，這里使用 COM組件的 Open方法打開一個Excel文檔：

Excel.Workbook NewWorkbook = NewExcel.Workbooks.Open(FilePath, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing);

這里使用Application新建一個Excel進(jìn)程，然后在此基礎(chǔ)上創(chuàng)建一個新的工作薄NewWorkbook，打開的Excel文檔就要導(dǎo)入到這里工作薄中。在Open方法中有很多的參數(shù)，通常我們主要關(guān)注FilePath，這個參數(shù)就是要打開的文件路徑[6]。

接下來還要創(chuàng)建一個新的工作表（Worksheet），在工作表的基礎(chǔ)上在創(chuàng)建Range對象，數(shù)據(jù)的讀取就是通過Range對象完成的，具體過程如下：

Excel.Worksheet

NewSheet=(Excel.Worksheet)NewWorkbook. Sheets[1];//在工作薄中創(chuàng)建一個工作表

Excel.Range range= (Excel.Range)NewSheet.Cells[2,2];//這里是把工作表中的第二行第二列轉(zhuǎn)換為Range對象，并賦值給range。

得到Range對象后可以使用Text或者Value2等屬性得到里面的數(shù)據(jù)。

當(dāng)數(shù)據(jù)寫入完成后要使用 NewWorkbook.Save()或者NewWorkbook.SaveAs()進(jìn)行保存，最后在使用NewWorkbook.Close()關(guān)閉工作薄。

2.3 刪除數(shù)據(jù)

在Excel中刪除數(shù)據(jù)主要使用Range對象。

Excel.Range range= NewSheet.get_Range(”A1”,”H1”);//”A1”和”H1”是要刪除表格的范圍

Range.Delete(Type.Missing);// “Type.Missing”表示缺省值

在實(shí)際情況中，經(jīng)常用到整行、整列的刪除，在COM組件中也提供這種刪除方法。

刪除整行：

Excel.Range range = (Excel.Range) NewSheet.Rows[1,Missing.Value];//把工作表中的要刪除的行賦值給 range對象，這里是第一行。

range.EntireRow.Delete(Excel.XlDeleteShiftDirect ion.xlShiftUp);//調(diào)用range對象的EntireRow表示整行，Delete表示刪除里面的參數(shù)表示刪除后下方單元格向上移動。

刪除整列：

Excel.Range range = (Excel.Range) NewSheet.Columns[1, Missing.Value];//把工作表中的要刪除的列賦值給range對象，這里是第一列。

range.EntireColumn.Delete(Excel.XlDeleteShiftDir ection.xlShiftToLeft);//調(diào)用range對象的EntireColumn表示整列，Delete表示刪除里面的參數(shù)表示刪除后右邊單元格向左移動[6]。

2.4 增加數(shù)據(jù)

Excel表格增加數(shù)據(jù)主要就是指增加行或者列，其操作方式與刪除方式類似。

增加行：

Excel.Range range = (Excel.Range)NewSheet.Rows[2,Missing.Value];//獲取要插入的行號，并轉(zhuǎn)換為 Range對象，這里是要插入第二行。

range.Insert(Missing.Value,Excel.XlInsertFormatO rigin.xlFormatFromLeftOrAbove);//在 Excel表格中的第一行后插入一個空的第二行，Insert的第二個參數(shù)表示插入后，其他單元格移動方向。

增加列：

Excel.Range range = (Excel.Range)NewSheet. Columns[2, Missing.Value];//獲取要插入的列號，并轉(zhuǎn)換為Range對象，這里是要插入第二列。

range.Insert (Missing.Value, Excel.XlInsertShiftDirection.xlShiftToRight); //在 Excel表格中的第一列后插入一個空的第二列，Insert的第二個參數(shù)表示插入后，其他單元格移動方向[7]。

2.5 查找

查找也是Excel文檔中十分重要的一個功能，下面是在一個工作表中的查找代碼：

Excel.Range range = ((Excel.Range)NewSheet. UsedRange).Find("查找關(guān)鍵字", Missing.Value, Missing.Value,Missing.Value, Missing.Value, Excel.XlSearchDirection.xlNext,Missing.Value, Missing.Value, Missing.Value);

NewSheet.UsedRange：表示當(dāng)前工作表里所有使用的單元格區(qū)域組成的Range對象；

Find方法：方法中有很多個參數(shù)，主要有兩個參數(shù)必須要設(shè)置，第一個是要查找的關(guān)鍵字，另一個是 Excel.XlSearchDirection表示查詢的方向有兩個枚舉數(shù)值：xlNext向下查找和xlPrevious向前查找[7][8]。

3 OLEDB操作法

OLEDB是微軟公司退出的針對不同的數(shù)據(jù)源的應(yīng)用程序接口，也可以理解為OLEDB是一個數(shù)據(jù)庫的接口，程序可以通過 OLEDB連接到數(shù)據(jù)庫。這就說明 OLEDB操作法就是把Excel文件當(dāng)作一個數(shù)據(jù)庫進(jìn)行操作，這也就使得OLEDB操作法不能直接創(chuàng)建空白的Excel文件，這能向操作數(shù)據(jù)庫一樣連接一個已存在的一個Excel文檔。但是同時Excel本身并不是一個真正的數(shù)據(jù)庫文件，所以在 OLEDB中并不能對Excel文檔執(zhí)行Delete刪除命令。

OLEDB操作Excel一般要通過C#自身的DataSet對象。DataSet為方便數(shù)據(jù)處理開發(fā)出來的，是數(shù)據(jù)的集合，正是因?yàn)槭褂肈ataSet，才使得數(shù)據(jù)操作簡單、高效。

首先要創(chuàng)建連接：

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=0'";

這是連接字符串，其中 filepath是要連接的 Excel文件路徑。

“Microsoft.ACE.OLEDB.12.0”是連接Excel對象的接口引擎，較早的版本是“Microsoft.Jet.OLEDB.4.0”現(xiàn)在很少使用，如果程序出現(xiàn)連接方面的錯誤可以互相更換。

“Excel 12.0”表示Excel版本號，如果你的Excel是97年以前的版本要使用Excel 8.0。

“HDR” 表示第一行是否是標(biāo)題行。

“IMEX”有三種模式，分別有0、1、2表示，“0”時Excel文檔能夠進(jìn)行寫入操作；“1”時Excel文檔能夠進(jìn)行讀取操作；“2”時Excel文檔可以同時只進(jìn)行讀寫操作。

3.1 OLEDB讀寫Excel文檔

3.1.1 讀取數(shù)據(jù)

OleDbConnection conn = new OleDbConnection(strConn); //創(chuàng)建連接

conn.Open();

string strExcel = "select * from [sheet1$]";//使用SQL語句讀取Excel文檔中名為sheet1的工作表

OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);//執(zhí)行SQL語句

DataSet ds = new DataSet();

DataTable dt = new DataTable ();

ds.Tables.Add(dt);//把新建的 DataTable加入到DataSet中

myCommand.Fill(dt);//Excel文檔中的內(nèi)容導(dǎo)入到了dt表中

conn.Close();

執(zhí)行上面的程序后 Excel文檔的內(nèi)容已經(jīng)讀入到了 dt中，這里要說明下，DataSet中可以包含很多個DataTable，這種關(guān)系類似與Excel中工作薄和工作表的關(guān)系[8]。

3.1.2 寫入數(shù)據(jù)

這里對數(shù)據(jù)的操作有兩種方法，一種是直接使用SQL語句操作Excel表格數(shù)據(jù)；另一種方法就是操作DataSet中讀入的數(shù)據(jù)，操作全部完成后在一次更新到Excel文檔中。由于直接使用SQL語句直接操作Excel文檔效率較低，所以本文主要介紹操作DataSet，再更新到Excel文檔中的方法[8]。

DataRow NewRow = dt.NewRow();//在dt表格中新建一行數(shù)據(jù)

NewRow ["序號"]= "3";//賦值給“序號”字段

NewRow ["姓名"]= "張三";//賦值給“姓名”字段

NewRow ["學(xué)號"]= "201401001";//賦值給“學(xué)號”字段

dt.Rows.Add(NewRow);//確認(rèn)插入新建的一行數(shù)據(jù)

以上增加 dt表格中的數(shù)據(jù)，接下來要把數(shù)據(jù)更新到Excel文檔中：

OleDbCommandBuilder odcb=new OleDbCommandBuilder(myCommand);

odcb.QuotePrefix = "[";

odcb.QuoteSuffix = "]";//用于糾正 INSERT INTO 語句的語法錯誤

myCommand.Update(dt);//更新dt數(shù)據(jù)到Excel文檔中

3.2 查找數(shù)據(jù)

查找數(shù)據(jù)只要使用DataTable中的Find方法。

DataRow keyword = dt.Rows.Find("張三");//查找 dt表中有關(guān)“張三”數(shù)據(jù)

4 總結(jié)與討論

本文主要介紹了COM組件和OLEDB兩種方法操作Excel文檔的基本方法。從上面的內(nèi)容可以看到OLEDB的操作比COM組件的操作功能少了很多，主要是因?yàn)镺LEDB把Excel文檔作為一個數(shù)據(jù)庫進(jìn)行處理，對數(shù)據(jù)庫處理有一定限制，比如不能隨意改變數(shù)據(jù)庫的結(jié)構(gòu)，這就是為什么OLEDB中不能添加或者刪除列；而COM組件的操作就靈活多變，基本上可以實(shí)現(xiàn)Excel文檔操作中的所有功能，除了以上介紹的基本功能，還可以設(shè)置字體、改變顏色、設(shè)置單元格大小等；但是COM組件的操作效率較低，執(zhí)行的速度遠(yuǎn)遠(yuǎn)低于OLEDB法，一個簡單的查找操作可能要比OLEDB操作的時間長10倍，COM組件法還有個非常麻煩的問題，每次操作完成后必須要手動把運(yùn)行的Excel進(jìn)程關(guān)閉，如果不關(guān)閉會影響后續(xù)對Excel文檔的處理。

最后總結(jié)，在實(shí)際編程過程中，我們可以同時使用兩種方法，發(fā)揮各自的優(yōu)點(diǎn)，這樣才能最大限度的提高程序的效率。

[1]微軟公司. Microsoft Visual Studio.Net [Z].微軟公司,2003.

[2]Ted Faison. Visual C# 基于組件的開發(fā)[M].北京:清華大學(xué)出版社, 2003.

[3]李建忠. Microsoft .NET框架程序設(shè)計[M].武漢:華中科技大學(xué)出版社, 2004.

[4]杜成龍.ASP.NET實(shí)現(xiàn)通用查詢方式[J].教育信息化,2005,(7): 77-78.

[5]張文博,余文芳.ASP.NET編程中對Excel文檔操作的探討及應(yīng)用[J].計算機(jī)系統(tǒng)應(yīng)用,2010,(3): 187-189.

[6]陳志堅(jiān). ASP.NET中Excel文檔的處理[J].寧波職業(yè)技術(shù)學(xué)院學(xué)報,2010,(5): 60-62.

[7]王興,李菊,陳瑋. ASP.NET中Excel文檔生成技術(shù)研究[J].軟件導(dǎo)刊. 2010,(4): 130-131.

[8]張立君,王維國.基干ASP.NET與ADO.NET技術(shù)訪問數(shù)據(jù)庫[J].商丘職業(yè)技術(shù)學(xué)院學(xué)報,2008,(5): 29-31.