?
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
"
C:\\Temp\\result.xlt
"
);
?
???????
Range
?
=
?ExcelApp
.
PG(
"
Cells
"
,
?
2
,
?
1
);????????
???????
Range
.
PS(
"
RowHeight
"
,
?
25
);????
在應用程序中控制Excel的運行,首先必須在編制自動化客戶程序時包含Comobj.hpp
#include "Comobj.hpp"
C++ Builder把Excel自動化對象的功能包裝在下面的四個Ole Object Class函數中,應用人員可以很方便地進行調用。
設置對象屬性:void??????? OlePropertySet(屬性名,參數……);
獲得對象屬性:Variant???? OlePropertyGet(屬性名,參數……);
調用對象方法:1) Variant? OleFunction(函數名,參數……);
??????????????????????? ?2) void???? OleProcedure(過程名,參數……);
在程序中可以用宏定義來節省時間：
?
#
define???PG???OlePropertyGet
#
define???PS???OlePropertySet
#
define???FN???OleFunction
#
define???PR???OleProcedure
?
舉例：
ExcelApp
.
OlePropertyGet(
"
workbooks
"
)
.
OleFunction(
"
Add
"
);
可寫為
ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
);
?
C++ Builder中使用OLE控制Excel2000,必須掌握Excel2000的自動化對象及Microsoft Word Visual Basic幫助文件中的關于Excel的對象、方法和屬性。對象是一個Excel元素,屬性是對象的一個特性或操作的一個方面,方法是對象可以進行的動作。
首先定義以下幾個變量：
Variant ExcelApp,Workbook1,Sheet1,Range1;
1、Excel中常用的對象是:Application,Workbooks,Worksheets等。
?
? ★創建應用對象★
???????Variant?ExcelApp;
???????ExcelApp?
=
?Variant
::
CreateObject?(
"
Excel.Application
"
);
?????? 或者
???????ExcelApp?
=
?CreateOleObject?(
"
Excel.Application
"
);
?
? ★創建工作簿對象★
???????Variant?WorkBook1;
???????WorkBook1?
=
?ExcelApp
.
PG(
"
ActiveWorkBook
"
);
?
? ★創建工作表對象★
???????Variant?Sheet1;
???????Sheet1?
=
?WorkBook1
.
PG(
"
ActiveSheet
"
);
?
? ★創建區域對象★
?????? Variant Range;
???????
Range
?
=
?Sheet1
.
PG(
"
Range
"
,
"
A1:A10
"
);????
?????? 或者使用
???????Excel
.
Exec
(PropertyGet(
"
Range
"
)
<<
"
A1:C1
"
)
.
Exec
(Procedure(
"
Select
"
));
?
2、常用的屬性操作:
?
? ★使Excel程序不可見★
???????ExcelApp
.
PS(
"
Visible
"
,
?(Variant)
false
);
????????
? ★新建EXCEL文件★
???
??? ◎ 新建系統模板的工作簿
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
)??????
//
默認工作簿
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
1
)???
//
單工作表
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
2
)???
//
圖表??
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
3
)???
//
宏表?
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
4
)???
//
國際通用宏表
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
5
)???
//
與默認的相同
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
6
)???
//
工作簿且只有一個表
???????或者使用ExcelApp的Exec方法
???????Excel
.
Exec
(PropertyGet(
"
Workbooks
"
))
.
Exec
(Procedure(
"
Add
"
));
??? ◎ 新建自己創建的模板的工作簿
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
Add
"
,
?
"
C:\\Temp\\result.xlt
"
);
????????
? ★打開工作簿★
???????ExcelApp
.
PG(
"
workbooks
"
)
.
FN(
"
open
"
,
?
"
路徑名.xls
"
)??
????????
? ★保存工作簿★
???????WorkBook1
.
FN(
"
Save
"
);????????????
//
保存工作簿
???????WorkBook1
.
FN(
"
SaveAs
"
,
?
"
文件名
"
);
//
工作簿保存為,路徑注意用"\\"
????????
? ★退出EXCEL★
???????ExcelApp
.
FN?(
"
Quit
"
);
???????ExcelApp?
=
?Unassigned;
?????? 或者
???????ExcelApp
.
Exec
(Procedure(
"
Quit
"
));
????????
? ★操作工作表★
???
??? ◎ 選擇選擇工作表中第一個工作表
???????Workbook1
.
PG(
"
Sheets
"
,
?
1
)
.
PR(
"
Select
"
);
???????Sheet1?
=
?Workbook1
.
PG(
"
ActiveSheet
"
);
????
??? ◎ 重命名工作表
???????Sheet1
.
PS(
"
Name
"
,
?
"
Sheet的新名字
"
);
????
??? ◎ 當前工作簿中的工作表總數
???????int?nSheetCount
=
Workbook1
.
PG(
"
Sheets
"
)
.
PG(
"
Count
"
);???????
????????
? ★操作行和列★
??
??? ◎ 獲取當前工作表中有多少行和多少列：
???????Sheet1
.
PG(
"
UsedRange
"
)
.
PG(
"
Columns
"
)
.
PG(
"
Count
"
);?
//
列數
???????Sheet1
.
PG(
"
UsedRange
"
)
.
PG(
"
Rows
"
)
.
PG(
"
Count
"
);????
//
行數
????
??? ◎ 設置列寬
???????ExcelApp
.
PG(
"
Columns
"
,
?
1
)
.
PS(
"
ColumnWidth
"
,
?
22
);
?????? 或者
???????
Range
?
=
?ExcelApp
.
PG(
"
Cells
"
,
?
1
,
?
3
);
???????
Range
.
PS(
"
ColumnWidth
"
,
?
22
);??
????
????
??? ◎ 設置行高
???????ExcelApp
.
PG(
"
Rows
"
,
?
2
)
.
PS(
"
RowHeight
"
,
?
25
);
?????? 或者
???????
Range
?
=
?ExcelApp
.
PG(
"
Cells
"
,
?
2
,
?
1
);????????
???????
Range
.
PS(
"
RowHeight
"
,
?
25
);????
????
??? ◎ 在工作表最前面插入一行
???????Sheet1
.
PG(
"
Rows
"
,
?
1
)
.
PR(
"
Insert
"
);??????
??
????
??? ◎ 刪除一行
???????ExcelApp
.
PG(
"
Rows
"
,
?
2
)
.
PR(
"
Delete
"
);?
//
將第2行刪除
?
?????? // 本文作者：ccrun ，如轉載請保證本文檔的完整性，并注明出處。
?????? // 歡迎光臨 C++ Builder 研究
www.ccrun.com
?????? // 摘自:http://www.ccrun.com/doc/go.asp?id=529
???????
? ★操作單元格★
?
??? ◎ 設置單元格字體
????????Sheet1
.
PG(
"
Cells
"
,
?
1
,
?
1
)
.
PG(
"
Font
"
)
.
PS(
"
Name
"
,
?
"
隸書
"
);?
//
字體
????????Sheet1
.
PG(
"
Cells
"
,
?
2
,
?
3
)
.
PG(
"
Font
"
)
.
PS(
"
size
"
,
?
28
);?????
//
大小
????????
??? ◎ 設置所選區域字體
???????
Range
.
PG(
"
Cells
"
)
.
PG(
"
Font
"
)
.
PS(
"
Size
"
,
?
28
);
???????
Range
.
PG(
"
Cells
"
)
.
PG(
"
Font
"
)
.
PS(
"
Color
"
,
?RGB(
0
,
?
0
,
?
255
));
?????? 其中參數的設置:
???????Font???Name?
:
?
"
隸書
"
???????????????
//
字體名稱
??????????????Size?
:
?
12
???????????????????
//
字體大小
?????????????Color?
:
?RGB(
*,*,*
)???????????
//
顏色
?????????Underline?
:
?
true
/
false
???????????
//
下劃線
?????????????Italic
:
?
true
/
false
???????????
//
斜體
????
??? ◎ 設置單元格格式為小數百分比
???????Sheet1
.
PG(
"
Cells
"
,
?
1
,
?
1
)
.
PS(
"
NumberFormatLocal
"
,
?
"
0.00%
"
);
?
??? ◎ 設定單元格的垂直對齊方式
???????
Range
?
=
?ExcelApp
.
PG(
"
Cells
"
,
?
3
,
?
4
);
???????
//
?1=靠上?2=居中?3=靠下對齊?4=兩端對齊?5=分散對齊
???????
Range
.
PS(
"
VerticalAlignment
"
,
?
2
);??
?????
????
??? ◎ 設定單元格的文本為自動換行
???????
Range
?
=
?ExcelApp
.
PG(
"
Cells
"
,
?
3
,
?
4
);
???????
Range
.
PS(
"
WrapText
"
,
?
true
);
???????
? ★單元格的合并★
?
???????
Range
?
=
?Sheet1
.
PG(
"
Range
"
,
?
"
A1:A2
"
);??????????
//
A1和A2單元格合并
???????
String
?strRange?
=
?
"
A
"
?
+
?IntToStr(j)?
+
?
"
:
"
?
+
?
"
C
"
?
+
?IntToStr(j);?
//
比如：A1:C5
???????Range1
=
Sheet1
.
PG(
"
Range
"
,
?strRange
.
c_str());?
//
可以用變量控制單元格合并
???????Range1
.
FN(
"
Merge
"
,
?
false
);
????????
? ★讀寫單元格★
??? ◎ 指定單元格賦值
???????
String
?strValue?
=
?
"
abcdefg
"
;
???????Sheet1
.
PG(
"
Cells
"
,
?
3
,
?
6
)
.
PS(
"
Value
"
,
?strValue
.
c_str());?
???????Sheet1
.
PG(
"
Cells
"
,
?j
,
?
1
)
.
PS(
"
Value
"
,
?
"
總記錄:
"
?
+
?
String
(j
-
6
));
???????或者使用
?
???????Excel
.
Exec
(PropertyGet(
"
Cells
"
)
<<
1
<<
3
)
.
Exec
(PropertySet(
"
Value
"
)
<<
15
);
?
??? ◎ 所選區域單元格賦值
???????
Range
.
PG(
"
Cells
"
)
.
PS(
"
Value
"
,
?
10
);?????
?
??? ◎ 所選區域行賦值
???????
Range
.
PG(
"
Rows
"
,
1
)
.
PS(
"
Value
"
,
?
1234
);??
?
??? ◎ 工作表列賦值
???????Sheet1
.
PG(
"
Columns
"
,
1
)
.
PS(
"
Value
"
,
?
1234
);?
?
??? ◎ 讀取取值語句:
???????
String
?strValue?
=
?Sheet1
.
PG(
"
Cells
"
,
?
3
,
?
5
)
.
PG(
"
Value
"
);
?
? ★窗口屬性★
???
??? ◎ 顯示屬性
???????ExcelApp
.
PS(
"
Windowstate
"
,
?
3
);???????
//
最大化顯示
??????????????
1
---------
xlNormal????????????
//
正常顯示
??????????????
2
---------
xlMinimized?????????
//
最小化顯示
??????????????
3
---------
xlMaximized?????????
//
最大化顯示
?
??? ◎ 狀態欄屬性
???????ExcelApp
.
PS(
"
StatusBar
"
,
?
"
您好,請您稍等。正在查詢！
"
);
???????ExcelApp
.
PS(
"
StatusBar
"
,
?
false
);?????
//
還原成默認值
?
??? ◎ 標題屬性:
??????
ExcelApp
.
PS(
"
Caption
"
,
?
"
查詢系統
"
);
??????
3、操作圖表
???
? ★添加圖表
????
?????Variant?Chart;??
?????Chart?
=
?ExcelApp
.
Exec
(PropertyGet(
"
Charts
"
))
.
Exec
(
Function
(
"
Add
"
));
?????ExcelApp
.
Exec
(PropertySet(
"
Visible
"
)?
<<
?
true
);
?????Chart
.
Exec
(PropertySet(
"
Type
"
)?
<<
?
-
4100
);
??
? ★滾動圖表
?
?????
for
(int?nRotate
=
5
;?nRotate?
<=
?
180
;?nRotate?
+=
?
5
)
?????{
??????????Chart
.
Exec
(PropertySet(
"
Rotation
"
)?
<<
?nRotate);
?????}
?????
for
?(int?nRotate?
=
?
175
;?nRotate?
>=
?
0
;?nRotate?
-=
?
5
)
?????{
??????????Chart
.
Exec
(PropertySet(
"
Rotation
"
)?
<<
?nRotate);
?????}
????
另外，為保證程序能正常運行，需要在程序中判斷目標機器是否安裝了Office;
try
{
????ExcelApp?
=
?Variant
::
CreateObject?(
"
Excel.Application
"
);
}
catch
(
)
{
????ShowMessage(
"
運行Excel出錯,請確認安裝了Office
"
);
????
return
;
}?
?
?
#
include?"comobj.hpp"
//---------------------------------------------------------------------------
//?對指定Excel文件中的指定列進行排序
//?strExcelFileName?:?excel文件名
//?nCol?:?指定的列號
//?nSortStyle?:?1:升序，2:降序
void?SortExcelColumn(
String
?strExcelFileName
,
?int?nCol
,
?int?nSortStyle)
{
????Variant?vExcelApp
,
?vWorkbook
,
?vRange;
????vExcelApp?
=
?Variant
::
CreateObject(
"
Excel.Application
"
);
????vExcelApp
.
OlePropertySet(
"
Visible
"
,
?
false
);
????vExcelApp
.
OlePropertyGet(
"
WorkBooks
"
)
.
OleProcedure(
"
Open
"
,
?strExcelFileName
.
c_str());
????vWorkbook?
=
?vExcelApp
.
OlePropertyGet(
"
ActiveWorkbook
"
);
????vExcelApp
.
OlePropertyGet(
"
Columns
"
,
?nCol)
.
OleProcedure(
"
Select
"
);
????vExcelApp
.
OlePropertyGet(
"
ActiveSheet
"
)
.
OlePropertyGet(
"
Cells
"
,
?
1
,
?nCol)
.
OleProcedure(
"
Select
"
);
????vRange?
=
??vExcelApp
.
OlePropertyGet(
"
Selection
"
);
????vRange
.
Exec
(
Function
(
"
Sort
"
)
<<
vExcelApp
.
OlePropertyGet(
"
Selection
"
)
<<
nSortStyle);
????vWorkbook
.
OleProcedure(
"
Save
"
);
????vWorkbook
.
OleProcedure(
"
Close
"
);
????vExcelApp
.
OleFunction(
"
Quit
"
);
????vWorkbook?
=
?Unassigned;
????vExcelApp?
=
?Unassigned;
????ShowMessage(
"
ok
"
);
}
void?__fastcall?TForm1
::
Button1Click(TObject?
*
Sender)
{
????
//
?對C:\123\123.xls文件中第一個Sheet的第四列進行升序排序
????SortExcelColumn(
"
C:\\123\\123.xls
"
,
?
4
,
?
1
);
}
from:http://www.itepub.net/html/kaifawendang/C__/OLE_COM_DLL_XML/2006/0429/6995.html
?