目錄 下一節

相關元件：ExcelApplication, ExcelWorkbook, ExcelWorksheet, ExcelChart, SaveDialog, Edit, Label, Button, GroupBox, Combobox, Pagecontrol, Tabsheet, RadioGroup, FontDialog。

動態的將資料轉成Excel。

Wirte by Delphiwww

本支程式的重點在於將資料轉換為Excel，並轉換為統計圖，同時也介紹Excel樞紐分析的轉換方式，基本上樞紐分析的功能與Delphi Decision Cube的功能類似，不過對大多數的使用者來說，對樞紐分析應該是比較熟悉才對。當然在本範例中，也順便介紹Delphi 基本範例中，Decision Cube的製作，其中基本的範例只有利用Decision Cube基本元件的製作方式，筆者，另外介紹利用ADO元件來製作Decision Cube的範例。

1. 程式瀏覽

在本程式，前面的頁面與「動態的將資料轉成Word」相同，請各位讀者自行參考，筆者僅就重要的部分加以說明：

A. 如圖2：請注意抬頭內容的輸入，其可以分別形成座標軸，X軸與Y軸的資料

圖2

B. 如圖3：自動將第一行與第二行資料帶入

圖3

C. 資料顯示

圖4

D. 統計圖顯示

圖5

2. 程式介面設計

A. 基本的介面設計：有關於前面三頁，基本的介面設計方式，為避免佔太多版面，筆者並不在詳加說明，請各位讀者自行參閱「Delphi & Word(二)、動態的將資料轉成Word」，或者自行參考程式(相信各位讀者，需要瞭解的部分也是如此)。

B. 將資料轉換為Excel資料：筆者將產生Excel資料與統計圖的資料設定成一支單獨的procedure，其中當傳入的參數為false時表示不產生統計圖，true時表示產生統計圖，產生Excel資料如下：

procedure TForm1.CreateExcel(chart:boolean);

var i,j,co:integer;

_Template,_SourceData,_TableDestination,_TableName,_Readdata,

_RowFields,_ColumnFields:Olevariant;

_Cell1,_Cell2:OleVariant;

ChType,SheetType,_Type,_Legendkey: OleVariant;

begin

//連接Excel

lcid := GetUserDefaultLCID;

try

ExcelApplication1.Connect;

ExcelApplication1.Visible[lcid]:=True;

_Template:=xlWBATWorksheet;

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

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

ExcelWorksheet1.Name := '資料';

except

showmessage('無法連上Excel');

exit;

end;

//設定第一行的抬頭資料

co:=0;

for i:=0 to ListView1.Items.Count-1 do

begin

if ListView1.Items[i].SubItems[0]='是' then

begin

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

ExcelWorksheet1.Range[_Cell1,_Cell1].Value:=ListView1.Items[i].SubItems[1];//抬頭資料

ExcelWorksheet1.Range[_Cell1,_Cell1].Select;

//align

case ComboBox2.Items.IndexOf(ListView1.Items[i].SubItems[1]) of

0:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignLeft;

1:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignCenter;

2:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignRight;

3:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignDistributed;

4:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignFill;

5:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignGeneral;

6:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignCenterAcrossSelection;

7:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignJustify;

else

end;

Setfont(ListView1.Items[i].SubItems[3]);

co:=co+1;

end;

end;

//內容資料，從第二行開始，請注意資料為直式

ADOQuery1.Active:=True;

j:=2;

while not Adoquery1.Eof do

begin

co:=0;

for i:=0 to ListView1.Items.Count-1 do

begin

if ListView1.Items[i].SubItems[0]='是' then

begin

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

ExcelWorksheet1.Range[_Cell1,_Cell1].Value:= ADOquery1.fieldbyname(ListView1.Items[i].Caption).Value;

ExcelWorksheet1.Range[_Cell1,_Cell1].Select;

case ComboBox2.Items.IndexOf(ListView1.Items[i].SubItems[4]) of

0:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignLeft;

1:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignCenter;

2:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignRight;

3:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignDistributed;

4:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignFill;

5:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignGeneral;

6:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignCenterAcrossSelection;

7:(ExcelApplication1.Selection[Lcid] as Range).HorizontalAlignment:=xlHAlignJustify;

else

end;

Setfont(ListView1.Items[i].SubItems[5]);

co:=co+1;

end;

end;

Adoquery1.Next;

j:=j+1;

end;

ADOQuery1.Active:=false;

//最適欄寬

_cell2:='A1';

ExcelWorksheet1.Range[_Cell2,_Cell1].Select;

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

if chart then

begin

//請參考C將資料轉為統計圖

end;

ExcelWorksheet1.Disconnect;

ExcelWorkbook1.Disconnect;

ExcelApplication1.Disconnect;

end;

//設定字型procedure

procedure TForm1.SetFont(SF:string);

var s:string;

i:integer;

begin

if not (sf='') then

begin

s:=sf;

with (ExcelApplication1.Selection[Lcid] as Range).Font do

begin

i:=pos(',',s);

if i>0 then

Name:=copy(s,1,i-1);

if pos('底線',s)>0 then

Underline := 2;

if pos('粗體',s)>0 then

Bold := 1;

if pos('斜體',s)>0 then

Italic := 1;

while (i>0) do

begin

s:=copy(s,i+1,length(s)-i);

i:=pos(',',s);

end;

i:=pos('號字',s);

if i>0 then

Size :=strtoint(copy(s,1,i-1));

end;

end;

end;

C. 將資料轉為統計圖

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

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

ExcelChart1.Name := 'Chart';

case ComboBox5.Items.IndexOf(ComboBox5.Text) of //請參考圖形分類

0:ExcelChart1.ChartType := TOleEnum(xlColumnClustered);

1:ExcelChart1.ChartType := TOleEnum(xlPie);

2:ExcelChart1.ChartType := TOleEnum(xlLine);

else

begin

showmessage('未設定該圖形');

exit;

end;

end;

{圖形的分類

xlColumnClustered = $00000033;

xlColumnStacked = $00000034;

xlColumnStacked100 = $00000035;

xl3DColumnClustered = $00000036;

xl3DColumnStacked = $00000037;

xl3DColumnStacked100 = $00000038;

xlBarClustered = $00000039;

xlBarStacked = $0000003A;

xlBarStacked100 = $0000003B;

xl3DBarClustered = $0000003C;

xl3DBarStacked = $0000003D;

xl3DBarStacked100 = $0000003E;

xlLineStacked = $0000003F;

xlLineStacked100 = $00000040;

xlLineMarkers = $00000041;

xlLineMarkersStacked = $00000042;

xlLineMarkersStacked100 = $00000043;

xlPieOfPie = $00000044;

xlPieExploded = $00000045;

xl3DPieExploded = $00000046;

xlBarOfPie = $00000047;

xlXYScatterSmooth = $00000048;

xlXYScatterSmoothNoMarkers = $00000049;

xlXYScatterLines = $0000004A;

xlXYScatterLinesNoMarkers = $0000004B;

xlAreaStacked = $0000004C;

xlAreaStacked100 = $0000004D;

xl3DAreaStacked = $0000004E;

xl3DAreaStacked100 = $0000004F;

xlDoughnutExploded = $00000050;

xlRadarMarkers = $00000051;

xlRadarFilled = $00000052;

xlSurface = $00000053;

xlSurfaceWireframe = $00000054;

xlSurfaceTopView = $00000055;

xlSurfaceTopViewWireframe = $00000056;

xlBubble = $0000000F;

xlBubble3DEffect = $00000057;

xlStockHLC = $00000058;

xlStockOHLC = $00000059;

xlStockVHLC = $0000005A;

xlStockVOHLC = $0000005B;

xlCylinderColClustered = $0000005C;

xlCylinderColStacked = $0000005D;

xlCylinderColStacked100 = $0000005E;

xlCylinderBarClustered = $0000005F;

xlCylinderBarStacked = $00000060;

xlCylinderBarStacked100 = $00000061;

xlCylinderCol = $00000062;

xlConeColClustered = $00000063;

xlConeColStacked = $00000064;

xlConeColStacked100 = $00000065;

xlConeBarClustered = $00000066;

xlConeBarStacked = $00000067;

xlConeBarStacked100 = $00000068;

xlConeCol = $00000069;

xlPyramidColClustered = $0000006A;

xlPyramidColStacked = $0000006B;

xlPyramidColStacked100 = $0000006C;

xlPyramidBarClustered = $0000006D;

xlPyramidBarStacked = $0000006E;

xlPyramidBarStacked100 = $0000006F;

xlPyramidCol = $00000070;

xl3DColumn = $FFFFEFFC;

xlLine = $00000004;

xl3DLine = $FFFFEFFB;

xl3DPie = $FFFFEFFA;

xlPie = $00000005;

xlXYScatter = $FFFFEFB7;

xl3DArea = $FFFFEFFE;

xlArea = $00000001;

xlDoughnut = $FFFFEFE8;

xlRadar = $FFFFEFC9;

}

ExcelChart1.SetSourceData(ExcelWorksheet1.Range[_Cell2,_Cell1],TOleEnum(xlColumns));

//以每一個column來顯示資料，需要與上面直式資料配合

if checkbox8.Checked then //抬頭

begin

ExcelChart1.HasTitle[lcid]:=CheckBox8.Checked;

ExcelChart1.ChartTitle[lcid].Caption:=Edit2.text;

end;

//請注意(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis)，要修改座標軸資料需要as Axis

_type:=xlCategory; //X軸

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).HasTitle:=CheckBox2.Checked;

if checkbox2.Checked then

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).AxisTitle.Caption:=Edit3.text;

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).HasMajorGridlines:=CheckBox9.checked;

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).HasMinorGridlines:=CheckBox10.checked;

_type:=xlValue; //Y軸

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).HasTitle:=CheckBox3.Checked;

if checkBox3.Checked then

begin

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).AxisTitle.Caption:=Edit7.text;

if CheckBox1.Checked then

begin

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).AxisTitle.HorizontalAlignment:=xlHAlignCenter;

//轉角90度

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).AxisTitle.VerticalAlignment := xlCenter;

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).AxisTitle.Orientation := xlHorizontal;

end;

end;

//主要格線與次要格線

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).HasMajorGridlines:=CheckBox11.checked;

(ExcelChart1.Axes(_type,xlPrimary,lcid) as Axis).HasMinorGridlines:=CheckBox12.checked;

ExcelChart1.HasLegend[lcid]:= CheckBox5.Checked; //圖例

if CheckBox5.Checked then

begin

case ComboBox3.Items.IndexOf(ComboBox3.Text) of

0:ExcelChart1.Legend[lcid].Position:=xlTop;

1:ExcelChart1.Legend[lcid].Position:=xlBottom;

2:ExcelChart1.Legend[lcid].Position:=xlLeft;

3:ExcelChart1.Legend[lcid].Position:=xlRight;

4:ExcelChart1.Legend[lcid].Position:=xlCorner; //右上角

else

end;

end;

ExcelChart1.HasDataTable := CheckBox4.Checked; //顯示資料表

if CheckBox4.Checked then

ExcelChart1.DataTable.ShowLegendKey := CheckBox6.Checked; //資料表圖例

case ComboBox6.Items.IndexOf(ComboBox6.Text) of //資料上顯示圖例(標籤旁顯示圖例)

0:ExcelChart1.ApplyDataLabels(xlDataLabelsShowNone,false);

1:ExcelChart1.ApplyDataLabels(xlDataLabelsShowValue,false);

2:ExcelChart1.ApplyDataLabels(xlDataLabelsShowPercent,false);

3:begin

_Legendkey:=CheckBox7.Checked;

ExcelChart1.ApplyDataLabels(xlDataLabelsShowLabel,_Legendkey);

end;

4:begin

_Legendkey:=CheckBox7.Checked;

ExcelChart1.ApplyDataLabels(xlDataLabelsShowLabelAndPercent,_Legendkey);

end;

5:ExcelChart1.ApplyDataLabels(xlDataLabelsShowBubbleSizes,false);

else

end;

{ 請注意需要配合圖形

xlDataLabelsShowNone = $FFFFEFD2;

xlDataLabelsShowValue = $00000002;

xlDataLabelsShowPercent = $00000003;

xlDataLabelsShowLabel = $00000004;

xlDataLabelsShowLabelAndPercent = $00000005;

xlDataLabelsShowBubbleSizes = $00000006;

}

ExcelChart1.Disconnect;