本來筆者希望做Excel樞紐分析與Delphi Decision Cube(含ADO版本)的比較，不過筆者最近的時間比較少，想想就作罷了，對於各位讀者來說，有關於Delphi Decision Cube元件的應用，從網路上應該可以找到答案，在此先說明，不過在範例程式中，筆者一並附上，有問題的話，歡迎一起討論

1. 程式瀏覽(如圖6)：簡單介紹資料如何轉換成樞紐分析圖，有關於程式的介面部分，筆者不再另行設計

圖6，程式使用相關元件

2. 資料庫：資料採用Delphi所附的Access資料，直接利用相關路徑的方式存取，其SQL語法如下：

SELECT PaymentMethod, ShipVIA, Terms, ShipDate, COUNT(AmountPaid) as count_amt , SUM(AmountPaid) as amt, COUNT(ItemsTotal) as Item

FROM ORDERS

GROUP BY PaymentMethod, ShipVIA, Terms, ShipDate

3. 程式設計：按下輸出的按鈕後，執行。

procedure TFormdcdExcel.Button1Click(Sender: TObject);

var lcid: integer;

_Template,_SourceData,_TableDestination,_TableName,_Readdata,

_RowFields,_ColumnFields:Olevariant;

_Cell1,_Cell2:OleVariant;

conn:boolean;

i,j:integer;

Temp_Worksheet: _WorkSheet;

SheetType:OleVariant;

begin

lcid := GetUserDefaultLCID;

try //連接Excel

ExcelApplication1.Connect;

ExcelApplication1.Visible[lcid]:=True;

_Template:=xlWBATWorksheet;

ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(_Template, lcid));

ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);

ExcelWorksheet1.Name := '資料';

conn:=True;

except

showmessage('無法連上Excel');

conn:=false;

end;

if conn then

begin //資料轉入Excel

ADOQuery1.Active:=True;

for i:=0 to Adoquery1.FieldCount-1 do

begin

_Cell1:=chr(65+i)+'1';

ExcelWorksheet1.Range[_Cell1,_Cell1].Value:=ADOQuery1.Fields[i].DisplayLabel;

end;

j:=2;

while not Adoquery1.Eof do

begin

for i:=0 to Adoquery1.FieldCount-1 do

begin

_cell1:=chr(65+i)+inttostr(j);

ExcelWorksheet1.Range[_Cell1,_Cell1].Value:=ADOQuery1.Fields[i].value;

end;

Adoquery1.Next;

j:=j+1;

end;

ADOQuery1.Active:=false;

//最適欄寬

_cell2:='A1';

ExcelWorksheet1.Range[_Cell2,_Cell1].Select;

(ExcelApplication1.Selection[Lcid] as Range).Columns.AutoFit;

//樞紐分析表：先產生另外一個WordSheet，然後將樞紐分析表所需要的資料產生到該WordSheet中

Temp_Worksheet:=ExcelWorkbook1.Worksheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;

ExcelWorksheet1.ConnectTo(Temp_Worksheet);

ExcelWorksheet1.Name := '樞紐分析表';

_SourceData:='資料!R1C1:R'+inttostr(j-1)+'C'+inttostr(i); //資料範圍

_TableDestination:=ExcelWorksheet1.Range['A3','A3']; //產生位置

_TableName:='樞紐分析表';

_Readdata:=EmptyParam;

ExcelWorkbook1.PivotCaches.Add(xlDatabase,_SourceData).CreatePivotTable(_TableDestination,_TableName,_readdata);

(ExcelWorksheet1.PivotTables('樞紐分析表') as PivotTable).SmallGrid := False;

_RowFields:=vararrayof(['付款','條件']); //多row

_ColumnFields:='經由'; //單一column

(ExcelWorksheet1.PivotTables('樞紐分析表') as PivotTable).AddFields(_RowFields,_ColumnFields,EmptyParam,EmptyParam);

((ExcelWorksheet1.PivotTables('樞紐分析表') as PivotTable).PivotFields('amt') as PivotField).Orientation:= xlDataField;

//樞紐分析圖

SheetType := TOleEnum(xlChart); // cast to avoid compiler range warnings

ExcelChart1.ConnectTo(ExcelWorkbook1.Sheets.Add(EmptyParam, EmptyParam, 1, SheetType, lcid) as _Chart);

ExcelChart1.Name := '樞紐分析圖';

ExcelChart1.SetSourceData(ExcelWorksheet1.Range['A3','A3']);

ExcelChart1.Location(xlLocationAsNewSheet);

showmessage('資料輸出完畢');

ExcelWorksheet1.Disconnect;

ExcelWorkbook1.Disconnect;

ExcelApplication1.Disconnect;

end;

將資料轉換成樞紐分析圖