程式師世界 >> 編程語言 >> .NET網頁編程 >> .NET實例教程 >> Delphi與Excel的親密接觸

Delphi與Excel的親密接觸

編輯：.NET實例教程

Delphi作為一個出色的RAD，強大的數據庫功能是其最重要的特色之一，但是操縱困難的QuickReport控件常常不能滿足數據庫報表的需要。如果你的報表非常復雜，或者要求靈活地改變格式，那麼使用Excel作為報表服務器是一個不錯的選擇。

Delphi從版本5開始提供的Excel組件極大地簡化了OLE自動化技術的應用。

不過缺漏多多的幫助文件一直是Delphi最令人诟病的地方，這些新組件也不例外，本文試圖對此作一較詳細地介紹。 Excel的對象模型是一個樹狀的層次結構，根是應用程序本身，工作簿WorkBook是根對象的屬性對象，本文主要討論的用於數據交換的WorkSheet則是工作簿的屬性對象，詳情參閱MSOffice提供的Excel VBA幫助文件。

在Delphi中控制Excel首先要與服務器程序建立連接，打開工作簿，然後與目標工作表交換數據，最後斷開連接。

打開Excel工作簿我們的例子從一個帶有TStringGrid（當然要填上一些數據）和兩個按鈕的主窗體開始，從控制面板的Servers頁簽中拖一個TExcelApplication控件放到窗體上

。首先把ConnectKind設為ckRunningOrNew，表示如果能夠檢測到運行的Excel實例則與其建立聯系，否則啟動Excel。

另外，如果希望程序一運行即與服務器程序建立聯系，可以把AutoConnect屬性設為True。

與Excel建立聯系只要一條語句就可以了： Excel . Connect; 也許你已經注意到Servers頁簽上還有其他幾個Excel控件，這些控件通過ConnectTo方法可以與前面的Excel聯系在一起：

ExcelWorkbook1.ConnectTo(Excel . ActiveWorkbook);

ExcelWorksheet1.ConnectTo(Excel . ActiveSheet as _Worksheet); ExcelWorksheet2.ConnectTo(Excel . Worksheets.Item[''Sheet2''] as _Worksheet);

要注意，使用ConnectTo方法前必須先打開相應的工作簿或工作表，

另外這些控件在多數情況下並不會帶來額外的便利，因此最好只使用一個TExcelApplication。一旦與Excel服務器建立聯系，就可以創建新的工作簿：

var

wkBook : _WorkBook;

LCID : Integer;

LCID := GetUserDefaultLCID();

wkBook := Excel.Workbooks.Add(EmptyParam, LCID);

Add函數的第一個參數用於定義新建工作簿所使用的模板，可以使用xlWBATChart、xlWBATExcel4IntlMacroSheet、 xlWBATExcel4MacroSheet或者xlWBATWorksheet常量，也可以是已有的xls文件名。

這裡的EmptyParam是Variants單元與定義的變量，表示使用默認的通用模板創建新工作簿。

如果打開已有的xls文檔，則應把要打開的文件名作為第一個參數傳遞給Open函數： wkBook:=Excel.WorkBooks.Open(edtDesFile.text,EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,LCID);

要知道，所有的數據操作主要是針對活動工作表而言的，下面的語句使用一個_WorkSheet變量代表當前的活動單元格。如果知道工作表的名稱，其中的索引號可以用工作表名代替：

wkSheet:=wkBook.Sheets[1] as _WorkSheet;

完成數據交換後需要保存工作簿：

Excel.ActiveWorkBook.SaveAs (''MyOutput'', EmptyParam,EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam, LCID);

或者： Excel.ActiveWorkBook.Save(LCID);

最後要關閉工作簿並斷開與Excel的連接：

wkBook.Close(True, SaveAsName, EmptyParam, LCID); //Excel.Quit; Excel.Disconnect;

這裡的Close方法包含有保存的功能，

第一個參數說明在關閉工作簿之前是否保存所做的修改，

第二個參數給出要保存的文件名，

第三個參數用於多位作者處理文檔的情況。

第二行要求終止Excel的運行。

與工作表交換數據輸入數據是對活動工作表的某個單元格或區域進行的，Range與cells都是工作表的對象屬性。

Cells是單元格的集合，如果沒有指定具體位置可以代表整個工作表的所有單元格，但一般使用它是為了引用某個具體的單元格，

比如WS.Cells.Item[1,1]就表示最左上角的單元格A1，

注意在VBA中Item是Cells的默認屬性可以省略，但在Delphi中就沒有這種便利了。

為單元格賦值要引用其Value屬性，不言而喻，該屬性是一個Variant變量，例如： wkSheet.Cells.Item[1, 1].Value := ''通訊錄'';

當然你也可以為單元格指定公式：

var AFormula:String;

AFormula:=''=Rand()'';

wkSheet.Range[''F3'',''G6''].Value:=AFormula;

上面的方法非常直接簡單，但是速度非常慢，不適合作大型報表。

那麼能不能把所有的數據依次傳遞給Excel呢？我們可以使用Range，

這個對象代表工作表中的一個區域，象我們用鼠標拖出的那樣，一般是一個矩形區域，只要給定其左上角和右下角單元格的位置就可以了，如Range[‘C3’,’J42’]。

這裡還有一個小問題，因為如果數據超出26列（比如有100列）或者需要在運行中確定目標區域范圍的話，使用字符名稱標記單元格就比較麻煩。

回想一下，既然“C3”是單元格的標記，那麼我們當然也可以使用Cells，

比如Range[Cells.Item[1,1], Cells.Item[100,100]]。

可以想象，Range的值應該是數組，但是絕對不能用Delphi中的Array給它賦值！要記住，在Delphi中，Excel對象的值總是Variant類型的。

var

Datas : Variant;

Ir, ic: Integer;

Datas:= varArrayCreate([1,ir,1,ic],varVariant); //這裡創建100*100的動態數組 …… //這裡為數組元素賦值

with wkSheet do Range[cells.Item[3,1],cells.Item[ir+2,ic]].Value:=Datas;

要注意，工作表與Range都有Cells屬性，為了明確起見，這裡使用了with語句。

此外，Range是有方向性的，用VarArrayCreate建立的一維數組只能賦給單行的Range，如果要為單列的Range定義值，必須使用二維數組，比如：

Datas:=VarArrayCreate([1,100,1,1], varVariant)；//創建100*1的動態數組。

順便提一下，Cells.Item[]實際上返回的也是Range對象。

從工作表中取回數據基本上是寫數據的逆過程，稍微需要注意的是如何確定工作表的數據范圍：

var

ir, ic : Integer;

wkSheet.Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;

ir := Excel.ActiveCell.Row;

ic := Excel.ActiveCell.Column;

這裡巧妙地利用特殊單元格函數SpecialCells取得包含數據的最後一個單元格。

數據編輯下面是數據編輯的兩個例子。

var

DestRange: OleVariant;

begin

DestRange := Excel.Range[''C1'', ''D4''];

Excel.Range[''A1'', ''B4''].Copy(DestRange);

上面的例子復制了8個單元格的內容。
如果給Copy函數傳遞一個空參數，則該區域的數據被復制到剪貼板，以後可以用Paste方法粘貼到別的位置。

var WS: _Worksheet;

Excel.Range[''A1'', ''B4''].Copy(EmptyParam); //在一個工作表中復制數據到剪貼板

WS := Excel.Activesheet as _Worksheet; //改變活動工作表 WS.Range[''C1'', ''D4''].Select;

WS.Paste(EmptyParam, EmptyParam, lcid); //把剪貼板中的內容粘貼到新的工作表中格式設置選擇Excel作為報表服務器主要是因為它強大的格式化能力。

我們首先把標題“通訊錄”進行單元格合並，居中顯示，然後修改字體為18磅的“隸書”，粗體：

with wkSheet.Range[''A1'',''D1''],Font do

begin Merge(True); //合並單元格

HorizontalAlignment:= xlCenter;

Size:=18;

Name:=''隸書'';

FontStyle:=Bold;

end;

如果單元格內容較長，將有部分內容無法顯示，通常的做法是雙擊選定區域右側的邊線是各列的寬度自動適應內容的長度。

在Delphi中通過AutoFit方法也可實現自適應的列寬行高，需要注意的是該方法僅能用於整行整列，否則會提示OLE方法拒絕執行的錯誤： wkSheet.Columns.EntireColumn.AutoFit;

中式報表通常需要上下封頂的表格線，可以使用Borders集合屬性。

要注意，VBA中的集合對象通常都有一個缺省的Item屬性，Delphi中是不能省略的。Weight屬性用於定義表格線的粗細：

with Aname.RefersToRange,Borders do

begin

HorizontalAlignment:= xlRight;

Item[xlEdgeBottom].Weight:=xlMedium;

Item[xlEdgeTop].Weight:=xlMedium;

Item[xlInsideHorizontal].Weight:=xlThin;

item[xlInsideVertical].Weight:=xlThin;

end;

頁面設置與打印頁面設置是通過工作表的PageSetUp對象屬性設置的。

Excel VBA中預設了40余種紙張常量，需要注意的是某些打印機只支持其中的一部分紙張類型。

屬性OrIEntation用於控制打印的方向，常量landscape = 2表示橫向打印。

布爾屬性CenterHorizontally和CenterVertically用於確定打印的內容是否在水平和垂直方向上居中。

with wkSheet.PageSetUp do

begin PaperSize:=xlPaperA4; //Paper type A4

PrintTitleRows := ''A1:D1''; //Repeat this row/page

LeftMargin:=18; //0.25" Left Margin

RightMargin:=18; //0.25" will vary between printers

TopMargin:=36; //0.5"

BottomMargin:=36; //0.5"

CenterHorizontally:=True;

OrIEntation:=1; //橫向打印（landscape）=2,

portrait=1

end;

打印報表可以調用工作表的PrintOut方法，VBA定義的該方法共有8個可選參數，前兩個用於規定起止頁，第三格式打印的份數，不過在Delphi中為其在最後增加了一個LCID參數，而且該參數不能使用EmptyParam

。類似地，打印預覽方法PrintPrevIEw在VBA中沒有參數，而在Delphi中調用需要兩個參數。

// wkBook.PrintPrevIEw(True,LCID);

//for prevIEwing wkSheet.PrintOut(EmptyParam,EmptyParam,1, EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,LCID);

命名區域與宏如果報表的格式比較復雜，為特定的表格區域命名然後按名引用是一種比較好的方法。

Names是WorkBook的一個集合對象屬性，它有一個的Add方法可以完成這項工作。

Var

Aname : Excel2000.Name;

Aname := wkBook.Names.Add(''通訊錄'',''=Sheet1!$A$3:$D$7'', EmptyParam, EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam);

其中Add函數的第一個參數是定義的名稱，第二個參數是名稱所表示的單元格區域。

要注意區域名稱的類型必須使用限定符，如果使用類型庫（D4），則限定符為Excel_TLB。

此外，命名的區域應使用絕對引用方式，即加上“$”符號。

一旦命名了一個區域，就可以使用這個名稱來引用它，下面的一行代碼使通訊錄內容以粗體顯示： AName.RefersToRange.Font.Bold:=True;

不過最令人驚喜的也許是你能夠在Delphi中動態地修改Excel宏程序！

下面的代碼為我們的工作簿創建了一個宏，在關閉工作簿時記錄上一次訪問的時間：

var

LineNo: integer;

CM: CodeModule;

sDate:String;

begin

CM := WkBook.VBProject.VBComponents.Item(''ThisWorkbook'').Codemodule;

LineNo := CM.CreateEventProc(''BeforeClose'', ''Workbook'');

SDate:=''上次訪問日期:''+DateToStr(Date());

CM.InsertLines(LineNo + 1, '' Range("B2").Value = "''+sDate+''"'');

End;

修改宏需要在前面的uses一節加上一個單元：VBIDE2000，如果使用類型庫則相應的單元為VBIDE_TLB。

這段代碼的關鍵是CodeModule對象，遺憾的是在Excel VBA help文中找不到該對象的蹤跡，只能去檢索MSDN了。

Delphi4及以前的版本 Delphi4沒有提供TExcelApplication對象，需要引入類型庫使用OLE自動化技術，Excel97的類型庫是Excel8.olb。

這兩種方法的主要區別在於與服務器程序建立連接的方法，下面是通過類型庫控制Excel的程序框架：

uses Windows, ComObj, ActiveX, Excel_TLB;

var

Excel: _Application;

LCID: integer;

Unknown:IUnknown;

Result: HResult;

begin

LCID := LOCALE_USER_DEFAULT;

Result := GetActiveObject(CLASS_Application, nil, Unknown); //嘗試捕獲運行中的程序實例

if (Result = MK_E_UNAVAILABLE) then

Excel := CoApplication.Create //啟動新的程序實例

else begin ...{檢查GetActiveObject方法調用過程中的錯誤}

OleCheck(Result);

OleCheck(Unknown.QueryInterface(_Application, Excel));

end; …… //進行數據處理 Excel.Visible[LCID] := True; // Excel.DisplayAlerts[LCID] := False;//顯示提示對話框

Excel.Quit;

End;

這裡沒有采用通常的try…except結構，是因為例外處理機制要進行復雜的OLE檢查，降低了except部分的執行速度。

要注意，不同的Delphi版本生成的伴隨函數CoApplication和一些常量名可能不同，應查看相應的類型庫。

在調用Quit方法之前，一定要釋放程序中創建的所有工作簿和工作表變量，否則Excel可能駐留在內存中運行（可以按下Ctrl+Alt+Del查看）。

調用GetActiveObject捕獲程序實例還有一個小問題，如果Excel處於最小化運行狀態，可能出現只顯示程序主框架而用戶區不可見的情況。

此外，如果不希望引入類型庫，還可以采用滯後綁定的方法，不過速度要慢許多。

下面的例子聲明了一個Variant變量來代表Excel應用程序：

var

Excel: Variant; …

try

Excel := GetActiveOleObject(''Excel.Application'');

except

Excel := CreateOleObject(''Excel.Application'');

end;

Excel.Visible := True;

采用滯後綁定時，編譯器不對調用的Excel對象方法進行檢查，而把這些工作交給服務器程序在執行時完成，這樣VBA所設置的大量默認參數（經常有十幾個）就發揮了應有的作用，因此這種方法有一個意料不到的好處——代碼簡潔：

var

WBk, WS, SheetName:

OleVariant;

WBk := Excel.WorkBooks.Open(''C:Test.xls'');

WS := WBk.Worksheets.Item[''SheetName''];

WS.Activate;

WBk.Close(SaveChanges := True);

Excel.Quit; 除了運行速度慢以外，如果要使用類型庫中定義的常量，就只能自己動手了：

const xlWBATWorksheet = -4167;

XLApp.Workbooks.Add(xlWBatWorkSheet);

最後不要忘記關閉Excel之後釋放變量：

Excel := Unassigned; 以下是本文例子中所用的源代碼，

在Delphi6+MSOffice2000下通過。

unit Unit1; interface uses Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, OleServer, Excel2000, Grids, StdCtrls;

type TForm1 = class(TForm)

Button1: TButton;

StringGrid1: TStringGrid;

Excel: TExcelApplication;

procedure ForMactivate(Sender: TObject);

procedure Button1Click(Sender: TObject);

private

...{ Private declarations }

procedure Write2Xls;

procedure OpenExl;

procedure CloseExl;

procedure AddFormula;

procedure NameSheet;

procedure Formats;

procedure AddMacro;

procedure RetrIEve;

procedure Printit;

public ...{ Public declarations }

end;

var Form1: TForm1;
implementation ...{$R *.dfm}

uses VBIDE2000;

var ir,ic:Integer;

wkSheet:_WorkSheet;

LCID:Integer;

wkBook:_WorkBook;

AName:Excel2000.Name;

procedure TForm1.ForMactivate(Sender: TObject);

begin

with StringGrid1 do

begin

Rows[0].CommaText:=''姓名,性別,年齡,電話'';

Rows[1].CommaText:=''張三,男,25,010-33775566'';

Rows[2].CommaText:=''李四,男,47,012-6574906'';

Rows[3].CommaText:=''周五,女,18,061-7557381'';

Rows[4].CommaText:=''孫濤,女,31,3324559'';

end;

procedure TForm1.OpenExl;

begin with Excel do

begin

Connect;

LCID:=GetUserDefaultLCID();

wkBook:=WorkBooks.Add(EmptyParam,LCID);

wkSheet:=wkBook.Sheets[1] as _WorkSheet;

end;

procedure TForm1.Write2Xls;

var

Datas:Variant;

i,j:Integer;

begin

ir:=StringGrid1.RowCount;

ic:=StringGrid1.ColCount;

Datas:=varArrayCreate([1,ir,1,ic],varVariant);

for i:=1 to ir do

for j:=1 to ic do

Datas[i,j]:=StringGrid1.Cells[j-1,i-1];
with wkSheet do

begin

Activate(LCID); Cells.Item[1,1].Value:=''通訊錄'';

Range[cells.Item[3,1],cells.Item[ir+2,ic]].Value:=Datas;

end; // Excel.Visible[LCID]:=True; Datas:=Unassigned;

end;

procedure TForm1.RetrIEve;

var

Datas:Variant;

i,j:Integer;

begin

with wkSheet do

begin

Cells.SpecialCells(xlCellTypeLastCell,EmptyParam).Activate;

ir:=Excel.ActiveCell.Row; ic:=Excel.ActiveCell.Column;

Datas:=Range[Cells.Item[1,1],Cells.Item[ir,ic]].Value;

with StringGrid1 do

begin

ColCount:=ic;

RowCount:=ir;

ScrollBars:=ssBoth;

for i:=0 to ir-1 do

for j:=0 to ic-1 do

Cells[j,i]:=Datas[i+1,j+1];

end;

Datas:=UnAssigned;

end;

procedure TForm1.CloseExl;

const SaveAsName=''test.xls'';

begin

wkBook.Close(True,SaveAsName,EmptyParam,LCID);

Excel.Quit;

Excel.Disconnect;

end;

procedure TForm1.NameSheet;

begin

AName:=wkBook.Names.Add(''通訊錄'',''=Sheet1!$A$3:$D$7'',EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam,EmptyParam, EmptyParam,EmptyParam,EmptyParam);

end;
procedure TForm1.AddFormula;

var

AFormula:String;

begin

AFormula:=''=Rand()'';

wkSheet.Range[''F3'',''G6''].Value:=AFormula;

end;

procedure TForm1.Formats;

begin

with wkSheet.Range[''A1'',''D1''],Font do

begin

Merge(True); //合並單元格