java ole excel_用OLE操作Excel

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

posted on 2006-09-10 20:05 young 阅读(308) 评论(0)  编辑  收藏 所属分类: C++Builder