要在应用程序中控制Excel的运行,首先必须在编制自动化客户程序时包含Comobj.hpp
#include "Comobj.hpp "
C++ Builder把Excel自动化对象的功能包装在下面的四个Ole Object Class函数中,应用人员可以很方便地进行调用。
设置对象属性:Variant OlePropertySet(属性名,参数……);
获得对象属性:void 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等。
(1)创建应用对象:如:
Variant ExcelApp;
ExcelApp=Variant::CreateObject ( "Excel.Application ");
或者
ExcelApp=CreateOleObject ( "Excel.Application ");
(2)创建工作簿对象:
Variant WorkBook1;
WorkBook1=ExcelApp.OlePropertyGet( "ActiveWorkBook ");
(3)创建工作表对象:
Variant Sheet1;
Sheet1=WorkBook1.OlePropertyGet( "ActiveSheet ");
(4)创建区域对象:
Variant Range;
Range=Sheet1.OlePropertyGet( "Range ", "A1:A10 ");
2、常用的属性操作:
(1)使Excel程序不可见
ExcelApp.OlePropertySet( "Visible ",(Variant)false);
(2)新建EXCEL文件:
(a):新建系统模板的工作簿
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ") //默认工作簿
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ",1) //单工作表
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ",2) //图表
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ",3) //宏表
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ",4) //国际通用宏表
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ",5) //与默认的相同
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ",6) //工作簿且只有一个表
(b):新建自己创建的模板的工作簿
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "Add ", "C:\\Templates\\result.xlt ");
(3)打开工作簿:
ExcelApp.OlePropertyGet( "workbooks ").OleFunction( "open ", "路径名.xls ")
(4)保存工作簿:
WorkBook1.OleFunction( "Save "); //保存工作簿
WorkBook1.OleFunction( "SaveAs ", "文件名 "); //工作簿保存为,文件路径注意用“\\”
(5)退出EXCEL:
ExcelApp.OleFunction ( "Quit ");
ExcelApp=Unassigned;
(6)操作工作表
(a)选择选择工作表中第一个工作表
Workbook1.OlePropertyGet( "Sheets ",1).OleProcedure( "Select ");
Sheet1=Workbook1.OlePropertyGet( "ActiveSheet ");
(b)重命名工作表
Sheet1.OlePropertySet( "Name ", "Sheet的新名字 ");
(c)当前工作簿中的工作表总数
int nSheetCount=Workbook1.OlePropertyGet( "Sheets ").OlePropertyGet( "Count ");
(7)操作行和列:
(a)获取当前工作表中有多少行和多少列:
Sheet1.OlePropertyGet( "UsedRange ").OlePropertyGet( "Columns ").OlePropertyGet( "Count "); //列数
Sheet1.OlePropertyGet( "UsedRange ").OlePropertyGet( "Rows ").OlePropertyGet( "Count "); //行数
(b)设置列宽
ExcelApp.OlePropertyGet( "Columns ",1).OlePropertySet( "ColumnWidth ",22);
(c)设置行高
ExcelApp.OlePropertyGet( "Rows ",2).OlePropertySet( "RowHeight ",25);
(d)在工作表最前面插入一行
Sheet1.OlePropertyGet( "Rows ",1).OleProcedure( "Insert ");
(e)删除一行
ExcelApp.OlePropertyGet( "Rows ",2).OleProcedure( "Delete "); //将第2行删除
(7)操作单元格
(a):设置单元格字体
Sheet1.OlePropertyGet( "Cells ",1,1).OlePropertyGet( "Font ").OlePropertySet( "Name ", "隶书 "); //字体
Sheet1.OlePropertyGet( "Cells ",2,3).OlePropertyGet( "Font ").OlePropertySet( "size ",28); //大小
(b):设置所选区域字体
Range.OlePropertyGet( "Cells ").OlePropertyGet( "Font ").OlePropertySet( "Size ",28);
Range.OlePropertyGet( "Cells ").OlePropertyGet( "Font ").OlePropertySet( "Color ",RGB(0,0,255));
其中参数的设置:
Font Name : "隶书 " //字体名称
Size : 12 //字体大小
Color : RGB(*,*,*) //颜色
Underline : true/false //下划线
Italic: true/false //斜体
(c)设置单元格格式为小数百分比
Sheet1.OlePropertyGet( "Cells ",1,1).OlePropertySet( "NumberFormatLocal ", "0.00% ");
(8)单元格的合并:
(a)Range=Sheet1.OlePropertyGet( "Range ", "A1:A2 "); //A1和A2单元格合并
(b)String strRange= "A "+IntToStr(j)+ ": "+ "C "+IntToStr(j); //比如:A1:C5
Range1=Sheet1.OlePropertyGet( "Range ",strRange.c_str()); //可以用变量控制单元格合并
Range1.OleFunction( "Merge ",false);
(9)读写单元格:
(a):指定单元格赋值
String strValue= "abcdefg ";
Sheet1.OlePropertyGet( "Cells ",3,6).OlePropertySet( "Value ",strValue.c_str());
Sheet1.OlePropertyGet( "Cells ",j,1).OlePropertySet( "Value ", "总记录: "+String(j-6));
(b):所选区域单元格赋值
Range.OlePropertyGet( "Cells ").OlePropertySet( "Value ",10);
(c):所选区域行赋值
Range.OlePropertyGet( "Rows ",1).OlePropertySet( "Value ",1234);
(d):工作表列赋值
Sheet1.OlePropertyGet( "Columns ",1).OlePropertySet( "Value ",1234);
(c):读取取值语句:
String strValue=Sheet1.OlePropertyGet( "Cells ",3,5).OlePropertyGet( "Value ");
(10)区域选择:
Range1.OlePropertyGet( "Cells ").OleFunction( "Select ");
(11)窗口属性:
(a)显示属性
ExcelApp.OlePropertySet( "Windowstate ",3); //最大化显示
1---------xlNormal //正常显示
2---------xlMinimized //最小化显示
3---------xlMaximized //最大化显示
(b)状态栏属性
ExcelApp.OlePropertySet( "StatusBar ", "您好,请您稍等。正在查询! ");
ExcelApp.OlePropertySet( "StatusBar ", false); //还原成默认值
(c)标题属性:
ExcelApp.OlePropertySet( "Caption ", "查询系统 ");
另外,为保证程序能正常运行,需要在程序中判断目标机器是否安装了Office;
try
{
ExcelApp=Variant::CreateObject ( "Excel.Application ");
}
catch(...)
{
ShowMessage( "运行Excel出错,请确认安装了Office ");
return;
}