不知能否将调用的excel放入panel中，达到excel与控制界面在同一个窗体中，但是我不晓得该如何做？请大侠们指教~~！
10 个解决方案
#1
转载他人资料
(一) 使用动态创建的方法
首先创建 Excel 对象，使用ComObj:
var ExcelApp: Variant;
ExcelApp := CreateOleObject( 'Excel.Application' );
1) 显示当前窗口：
ExcelApp.Visible := True;
2) 更改 Excel 标题栏：
ExcelApp.Caption := '应用程序调用 Microsoft Excel';
3) 添加新工作簿：
ExcelApp.WorkBooks.Add;
4) 打开已存在的工作簿：
ExcelApp.WorkBooks.Open( 'C:\Excel\Demo.xls' );
5) 设置第2个工作表为活动工作表：
ExcelApp.WorkSheets[2].Activate;
或
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;
6) 给单元格赋值：
ExcelApp.Cells[1,4].Value := '第一行第四列';
7) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
9) 在第8行之前插入分页符：
ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
10) 在第8列之前删除分页符：
ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
11) 指定边框线宽度：
ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
12) 清除第一行第四列单元格公式：
ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
13) 设置第一行字体属性：
ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine := True;
14) 进行页面设置：
a.页眉：
ExcelApp.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApp.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApp.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApp.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApp.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApp.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
15) 拷贝操作：
a.拷贝整个工作表：
ExcelApp.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApp.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApp.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApp.ActiveSheet.Range.PasteSpecial;
16) 插入一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Insert;
b. ExcelApp.ActiveSheet.Columns[1].Insert;
17) 删除一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Delete;
b. ExcelApp.ActiveSheet.Columns[1].Delete;
18) 打印预览工作表：
ExcelApp.ActiveSheet.PrintPreview;
19) 打印输出工作表：
ExcelApp.ActiveSheet.PrintOut;
20) 工作表保存：
if not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
21) 工作表另存为：
ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
22) 放弃存盘：
ExcelApp.ActiveWorkBook.Saved := True;
23) 关闭工作簿：
ExcelApp.WorkBooks.Close;
24) 退出 Excel：
ExcelApp.Quit;
(二) 使用Delphi 控件方法
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。
1） 打开Excel
ExcelApplication1.Connect;
2) 显示当前窗口：
ExcelApplication1.Visible[0]:=True;
3) 更改 Excel 标题栏：
ExcelApplication1.Caption := '应用程序调用 Microsoft Excel';
4) 添加新工作簿：
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
5) 添加新工作表：
var Temp_Worksheet: _WorkSheet;
begin
Temp_Worksheet:=ExcelWorkbook1.
WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;
ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);
End;
6) 打开已存在的工作簿：
ExcelApplication1.Workbooks.Open (c:\a.xls
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
7) 设置第2个工作表为活动工作表：
ExcelApplication1.WorkSheets[2].Activate; 或
ExcelApplication1.WorksSheets[ 'Sheet2' ].Activate;
8) 给单元格赋值：
ExcelApplication1.Cells[1,4].Value := '第一行第四列';
9) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApplication1.ActiveSheet.Columns[1].ColumnsWidth := 5;
10) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApplication1.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
11) 在第8行之前插入分页符：
ExcelApplication1.WorkSheets[1].Rows[8].PageBreak := 1;
12) 在第8列之前删除分页符：
ExcelApplication1.ActiveSheet.Columns[4].PageBreak := 0;
13) 指定边框线宽度：
ExcelApplication1.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
14) 清除第一行第四列单元格公式：
ExcelApplication1.ActiveSheet.Cells[1,4].ClearContents;
15) 设置第一行字体属性：
ExcelApplication1.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApplication1.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApplication1.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApplication1.ActiveSheet.Rows[1].Font.UnderLine := True;
16) 进行页面设置：
a.页眉：
ExcelApplication1.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApplication1.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApplication1.ActiveSheet.PageSetup.PrintGridLines := True;
17) 拷贝操作：
a.拷贝整个工作表：
ExcelApplication1.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApplication1.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18) 插入一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Insert;
b. ExcelApplication1.ActiveSheet.Columns[1].Insert;
19) 删除一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Delete;
b. ExcelApplication1.ActiveSheet.Columns[1].Delete;
20) 打印预览工作表：
ExcelApplication1.ActiveSheet.PrintPreview;
21) 打印输出工作表：
ExcelApplication1.ActiveSheet.PrintOut;
22) 工作表保存：
if not ExcelApplication1.ActiveWorkBook.Saved then
ExcelApplication1.ActiveSheet.PrintPreview;
23) 工作表另存为：
ExcelApplication1.SaveAs( 'C:\Excel\Demo1.xls' );
24) 放弃存盘：
ExcelApplication1.ActiveWorkBook.Saved := True;
25) 关闭工作簿：
ExcelApplication1.WorkBooks.Close;
26) 退出 Excel：
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;
(三) 使用Delphi 控制Excle二维图
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet
var asheet1,achart, range:variant;
1）选择当第一个工作薄第一个工作表
asheet1:=ExcelApplication1.Workbooks[1].Worksheets[1];
2）增加一个二维图
achart:=asheet1.chartobjects.add(100,100,200,200);
3）选择二维图的形态
achart.chart.charttype:=4;
4）给二维图赋值
series:=achart.chart.seriescollection;
range:=sheet1!r2c3:r3c9;
series.add(range,true);
5）加上二维图的标题
achart.Chart.HasTitle:=True;
achart.Chart.ChartTitle.Characters.Text:=’ Excle二维图’
6）改变二维图的标题字体大小
achart.Chart.ChartTitle.
(一) 使用动态创建的方法
首先创建 Excel 对象，使用ComObj:
var ExcelApp: Variant;
ExcelApp := CreateOleObject( 'Excel.Application' );
1) 显示当前窗口：
ExcelApp.Visible := True;
2) 更改 Excel 标题栏：
ExcelApp.Caption := '应用程序调用 Microsoft Excel';
3) 添加新工作簿：
ExcelApp.WorkBooks.Add;
4) 打开已存在的工作簿：
ExcelApp.WorkBooks.Open( 'C:\Excel\Demo.xls' );
5) 设置第2个工作表为活动工作表：
ExcelApp.WorkSheets[2].Activate;
或
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;
6) 给单元格赋值：
ExcelApp.Cells[1,4].Value := '第一行第四列';
7) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
9) 在第8行之前插入分页符：
ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
10) 在第8列之前删除分页符：
ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
11) 指定边框线宽度：
ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
12) 清除第一行第四列单元格公式：
ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
13) 设置第一行字体属性：
ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine := True;
14) 进行页面设置：
a.页眉：
ExcelApp.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApp.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApp.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApp.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApp.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApp.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
15) 拷贝操作：
a.拷贝整个工作表：
ExcelApp.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApp.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApp.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApp.ActiveSheet.Range.PasteSpecial;
16) 插入一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Insert;
b. ExcelApp.ActiveSheet.Columns[1].Insert;
17) 删除一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Delete;
b. ExcelApp.ActiveSheet.Columns[1].Delete;
18) 打印预览工作表：
ExcelApp.ActiveSheet.PrintPreview;
19) 打印输出工作表：
ExcelApp.ActiveSheet.PrintOut;
20) 工作表保存：
if not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
21) 工作表另存为：
ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
22) 放弃存盘：
ExcelApp.ActiveWorkBook.Saved := True;
23) 关闭工作簿：
ExcelApp.WorkBooks.Close;
24) 退出 Excel：
ExcelApp.Quit;
(二) 使用Delphi 控件方法
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。
1） 打开Excel
ExcelApplication1.Connect;
2) 显示当前窗口：
ExcelApplication1.Visible[0]:=True;
3) 更改 Excel 标题栏：
ExcelApplication1.Caption := '应用程序调用 Microsoft Excel';
4) 添加新工作簿：
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
5) 添加新工作表：
var Temp_Worksheet: _WorkSheet;
begin
Temp_Worksheet:=ExcelWorkbook1.
WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;
ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);
End;
6) 打开已存在的工作簿：
ExcelApplication1.Workbooks.Open (c:\a.xls
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
7) 设置第2个工作表为活动工作表：
ExcelApplication1.WorkSheets[2].Activate; 或
ExcelApplication1.WorksSheets[ 'Sheet2' ].Activate;
8) 给单元格赋值：
ExcelApplication1.Cells[1,4].Value := '第一行第四列';
9) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApplication1.ActiveSheet.Columns[1].ColumnsWidth := 5;
10) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApplication1.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
11) 在第8行之前插入分页符：
ExcelApplication1.WorkSheets[1].Rows[8].PageBreak := 1;
12) 在第8列之前删除分页符：
ExcelApplication1.ActiveSheet.Columns[4].PageBreak := 0;
13) 指定边框线宽度：
ExcelApplication1.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
14) 清除第一行第四列单元格公式：
ExcelApplication1.ActiveSheet.Cells[1,4].ClearContents;
15) 设置第一行字体属性：
ExcelApplication1.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApplication1.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApplication1.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApplication1.ActiveSheet.Rows[1].Font.UnderLine := True;
16) 进行页面设置：
a.页眉：
ExcelApplication1.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApplication1.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApplication1.ActiveSheet.PageSetup.PrintGridLines := True;
17) 拷贝操作：
a.拷贝整个工作表：
ExcelApplication1.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApplication1.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18) 插入一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Insert;
b. ExcelApplication1.ActiveSheet.Columns[1].Insert;
19) 删除一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Delete;
b. ExcelApplication1.ActiveSheet.Columns[1].Delete;
20) 打印预览工作表：
ExcelApplication1.ActiveSheet.PrintPreview;
21) 打印输出工作表：
ExcelApplication1.ActiveSheet.PrintOut;
22) 工作表保存：
if not ExcelApplication1.ActiveWorkBook.Saved then
ExcelApplication1.ActiveSheet.PrintPreview;
23) 工作表另存为：
ExcelApplication1.SaveAs( 'C:\Excel\Demo1.xls' );
24) 放弃存盘：
ExcelApplication1.ActiveWorkBook.Saved := True;
25) 关闭工作簿：
ExcelApplication1.WorkBooks.Close;
26) 退出 Excel：
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;
(三) 使用Delphi 控制Excle二维图
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet
var asheet1,achart, range:variant;
1）选择当第一个工作薄第一个工作表
asheet1:=ExcelApplication1.Workbooks[1].Worksheets[1];
2）增加一个二维图
achart:=asheet1.chartobjects.add(100,100,200,200);
3）选择二维图的形态
achart.chart.charttype:=4;
4）给二维图赋值
series:=achart.chart.seriescollection;
range:=sheet1!r2c3:r3c9;
series.add(range,true);
5）加上二维图的标题
achart.Chart.HasTitle:=True;
achart.Chart.ChartTitle.Characters.Text:=’ Excle二维图’
6）改变二维图的标题字体大小
achart.Chart.ChartTitle.
#2
diff,case of.
#3
转载操作单元
unit ExcelUnit;
interface
uses
Dialogs, Messages, SysUtils, Grids, Cmp_Sec, ComObj, Ads_Misc;
{!~Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
{!~Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Returns the Column String Value from its integer equilavent.}
Function ExcelColIntToStr(ColNum: Integer): ShortString;
{!~Returns the Column Integer Value from its Alpha equilavent.}
Function ExcelColStrToInt(ColStr: ShortString): Integer;
{!~Close All Workbooks. All workbooks can be saved or not.}
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Copies a range of Excel Cells to a Delphi StringGrid. If successful
True is returned, False otherwise. If SizeStringGridToFit is True
then the StringGrid is resized to be exactly the correct dimensions to
receive the input Excel cells, otherwise the StringGrid is not resized.
If ClearStringGridFirst is true then any cells outside the input range
are cleared, otherwise existing values are retained. Please not that the
Excel cell coordinates are "1" based and the Delphi StringGrid coordinates
are zero based.}
Function ExcelCopyToStringGrid(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
StringGrid : TStringGrid;
StringGridFirstRow : Integer;
StringGridFirstCol : Integer;
{Make the StringGrid the same size as the input range}
SizeStringGridToFit : Boolean;
{cells outside input range in StringGrid are cleared}
ClearStringGridFirst : Boolean
): Boolean;
{!~Delete a WorkSheet by Name}
Function ExcelDeleteWorkSheet(
Excel : Variant;
SheetName : ShortString): Boolean;
{!~Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
{!~Finds A value and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFind(
Excel : Variant;
FindString : ShortString): Boolean;
{!~Finds A value in a range and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise. The search directions can be defined.
If you want row searches to go from left to right then SearchRight should
be set to true, False otherwise. If you want column searches to go from
top to bottom then SearchDown should be set to true, false otherwise.
If RowsFirst is set to true then all the columns in a complete row will be
searched.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
{!~Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
{!~Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
{!~Returns the name of the currently active worksheet
as a shortstring}
Function ExcelGetActiveSheetName(Excel : Variant): ShortString;
{!~Gets the formula in a cell.}
Function ExcelGetCellFormula(
Excel : Variant;
RowNum, ColNum: Integer): ShortString;
{!~Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
{!~Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
{!~Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
{!~Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
{!~Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
{!~Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
{!~Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
{!~Moves the cursor to Home position, i.e., A1}
Function ExcelHome(Excel : Variant): Boolean;
{!~Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
{!~Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFile(Excel : Variant; FileName : String): Boolean;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFileComplex(
Excel : Variant;
FileName : String;
UpdateLinks : Integer;
ReadOnly : Boolean;
Format : Integer;
Password : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to to values only.}
Function ExcelPasteValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Renames a worksheet.}
Function ExcelRenameSheet(
Excel : Variant;
OldName : ShortString;
NewName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
unit ExcelUnit;
interface
uses
Dialogs, Messages, SysUtils, Grids, Cmp_Sec, ComObj, Ads_Misc;
{!~Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
{!~Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Returns the Column String Value from its integer equilavent.}
Function ExcelColIntToStr(ColNum: Integer): ShortString;
{!~Returns the Column Integer Value from its Alpha equilavent.}
Function ExcelColStrToInt(ColStr: ShortString): Integer;
{!~Close All Workbooks. All workbooks can be saved or not.}
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Copies a range of Excel Cells to a Delphi StringGrid. If successful
True is returned, False otherwise. If SizeStringGridToFit is True
then the StringGrid is resized to be exactly the correct dimensions to
receive the input Excel cells, otherwise the StringGrid is not resized.
If ClearStringGridFirst is true then any cells outside the input range
are cleared, otherwise existing values are retained. Please not that the
Excel cell coordinates are "1" based and the Delphi StringGrid coordinates
are zero based.}
Function ExcelCopyToStringGrid(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
StringGrid : TStringGrid;
StringGridFirstRow : Integer;
StringGridFirstCol : Integer;
{Make the StringGrid the same size as the input range}
SizeStringGridToFit : Boolean;
{cells outside input range in StringGrid are cleared}
ClearStringGridFirst : Boolean
): Boolean;
{!~Delete a WorkSheet by Name}
Function ExcelDeleteWorkSheet(
Excel : Variant;
SheetName : ShortString): Boolean;
{!~Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
{!~Finds A value and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFind(
Excel : Variant;
FindString : ShortString): Boolean;
{!~Finds A value in a range and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise. The search directions can be defined.
If you want row searches to go from left to right then SearchRight should
be set to true, False otherwise. If you want column searches to go from
top to bottom then SearchDown should be set to true, false otherwise.
If RowsFirst is set to true then all the columns in a complete row will be
searched.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
{!~Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
{!~Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
{!~Returns the name of the currently active worksheet
as a shortstring}
Function ExcelGetActiveSheetName(Excel : Variant): ShortString;
{!~Gets the formula in a cell.}
Function ExcelGetCellFormula(
Excel : Variant;
RowNum, ColNum: Integer): ShortString;
{!~Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
{!~Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
{!~Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
{!~Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
{!~Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
{!~Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
{!~Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
{!~Moves the cursor to Home position, i.e., A1}
Function ExcelHome(Excel : Variant): Boolean;
{!~Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
{!~Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFile(Excel : Variant; FileName : String): Boolean;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFileComplex(
Excel : Variant;
FileName : String;
UpdateLinks : Integer;
ReadOnly : Boolean;
Format : Integer;
Password : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to to values only.}
Function ExcelPasteValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Renames a worksheet.}
Function ExcelRenameSheet(
Excel : Variant;
OldName : ShortString;
NewName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
#4
{!~Saves the range on the currently active sheet
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a text file.}
Function ExcelSaveAsText(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected down and to the right.
The block proceeds down until an empty row is encountered. The
block proceeds right until an empty column is encountered.}
Function ExcelSelectBlock(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected that contains
the currently active cell. The block proceeds in each
direction until an empty row or column is encountered.}
Function ExcelSelectBlockWhole(Excel: Variant): Boolean;
{!~Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
{!~Selects a range on the currently active sheet}
Function ExcelSelectRange(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
{!~Sets the formula in a cell. Remember to include the equals sign "=".
If the function fails False is returned, True otherwise.}
Function ExcelSetCellFormula(
Excel : Variant;
FormulaString : ShortString;
RowNum, ColNum: Integer): Boolean;
{!~Sets the contents of a cell as a shortstring}
Function ExcelSetCellValue(
Excel : Variant;
RowNum, ColNum: Integer;
Value : ShortString): Boolean;
{!~Sets a Column Width on the currently active sheet}
Function ExcelSetColumnWidth(
Excel : Variant;
ColNum : Integer;
ColumnWidth: Integer): Boolean;
{!~Set Excel Visibility}
Function ExcelSetVisible(
Excel : Variant;
IsVisible: Boolean): Boolean;
{!~Saves the range on the currently active sheet
to values only.}
Function ExcelValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Returns the Excel Version as a ShortString.}
Function ExcelVersion(Excel: Variant): ShortString;
Function IsBlockColSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
Function IsBlockRowSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
implementation
type
//Declare the constants used by Excel
SourceType = (xlConsolidation, xlDatabase, xlExternal, xlPivotTable);
Orientation = (xlHidden, xlRowField, xlColumnField, xlPageField, xlDataField);
RangeEnd = (NoValue, xlToLeft, xlToRight, xlUp, xlDown);
ExcelPasteType = (xlAllExceptBorders,xlNotes,xlFormats,xlValues,xlFormulas,xlAll);
{CAUTION!!! THESE OUTPUTS ARE ALL GARBLED! YOU SELECT xlDBF3 AND EXCEL
OUTPUTS A xlCSV.}
FileFormat = (xlAddIn, xlCSV, xlCSVMac, xlCSVMSDOS, xlCSVWindows, xlDBF2,
xlDBF3, xlDBF4, xlDIF, xlExcel2, xlExcel3, xlExcel4,
xlExcel4Workbook, xlIntlAddIn, xlIntlMacro, xlNormal,
xlSYLK, xlTemplate, xlText, xlTextMac, xlTextMSDOS,
xlTextWindows, xlTextPrinter, xlWK1, xlWK3, xlWKS,
xlWQ1, xlWK3FM3, xlWK1FMT, xlWK1ALL);
{Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Worksheets.Add;
Except
MessageDlg('Unable to add a new worksheet', mtError, [mbOK], 0);
Result := False;
End;
End;
{Sets Excel Visibility}
Function ExcelSetVisible(Excel : Variant;IsVisible: Boolean): Boolean;
Begin
Result := True;
Try
Excel.Visible := IsVisible;
Except
MessageDlg('Unable to Excel Visibility', mtError, [mbOK], 0);
Result := False;
End;
End;
{Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
Begin
Result := True;
Try
ExcelCloseWorkBooks(Excel, SaveAll);
Excel.Quit;
Except
MessageDlg('Unable to Close Excel', mtError, [mbOK], 0);
Result := False;
End;
End;
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a text file.}
Function ExcelSaveAsText(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected down and to the right.
The block proceeds down until an empty row is encountered. The
block proceeds right until an empty column is encountered.}
Function ExcelSelectBlock(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected that contains
the currently active cell. The block proceeds in each
direction until an empty row or column is encountered.}
Function ExcelSelectBlockWhole(Excel: Variant): Boolean;
{!~Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
{!~Selects a range on the currently active sheet}
Function ExcelSelectRange(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
{!~Sets the formula in a cell. Remember to include the equals sign "=".
If the function fails False is returned, True otherwise.}
Function ExcelSetCellFormula(
Excel : Variant;
FormulaString : ShortString;
RowNum, ColNum: Integer): Boolean;
{!~Sets the contents of a cell as a shortstring}
Function ExcelSetCellValue(
Excel : Variant;
RowNum, ColNum: Integer;
Value : ShortString): Boolean;
{!~Sets a Column Width on the currently active sheet}
Function ExcelSetColumnWidth(
Excel : Variant;
ColNum : Integer;
ColumnWidth: Integer): Boolean;
{!~Set Excel Visibility}
Function ExcelSetVisible(
Excel : Variant;
IsVisible: Boolean): Boolean;
{!~Saves the range on the currently active sheet
to values only.}
Function ExcelValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Returns the Excel Version as a ShortString.}
Function ExcelVersion(Excel: Variant): ShortString;
Function IsBlockColSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
Function IsBlockRowSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
implementation
type
//Declare the constants used by Excel
SourceType = (xlConsolidation, xlDatabase, xlExternal, xlPivotTable);
Orientation = (xlHidden, xlRowField, xlColumnField, xlPageField, xlDataField);
RangeEnd = (NoValue, xlToLeft, xlToRight, xlUp, xlDown);
ExcelPasteType = (xlAllExceptBorders,xlNotes,xlFormats,xlValues,xlFormulas,xlAll);
{CAUTION!!! THESE OUTPUTS ARE ALL GARBLED! YOU SELECT xlDBF3 AND EXCEL
OUTPUTS A xlCSV.}
FileFormat = (xlAddIn, xlCSV, xlCSVMac, xlCSVMSDOS, xlCSVWindows, xlDBF2,
xlDBF3, xlDBF4, xlDIF, xlExcel2, xlExcel3, xlExcel4,
xlExcel4Workbook, xlIntlAddIn, xlIntlMacro, xlNormal,
xlSYLK, xlTemplate, xlText, xlTextMac, xlTextMSDOS,
xlTextWindows, xlTextPrinter, xlWK1, xlWK3, xlWKS,
xlWQ1, xlWK3FM3, xlWK1FMT, xlWK1ALL);
{Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Worksheets.Add;
Except
MessageDlg('Unable to add a new worksheet', mtError, [mbOK], 0);
Result := False;
End;
End;
{Sets Excel Visibility}
Function ExcelSetVisible(Excel : Variant;IsVisible: Boolean): Boolean;
Begin
Result := True;
Try
Excel.Visible := IsVisible;
Except
MessageDlg('Unable to Excel Visibility', mtError, [mbOK], 0);
Result := False;
End;
End;
{Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
Begin
Result := True;
Try
ExcelCloseWorkBooks(Excel, SaveAll);
Excel.Quit;
Except
MessageDlg('Unable to Close Excel', mtError, [mbOK], 0);
Result := False;
End;
End;
#5
{Close All Workbooks. All workbooks can be saved or not.}
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
var
loop: byte;
Begin
Result := True;
Try
For loop := 1 to Excel.Workbooks.Count Do
Excel.Workbooks[1].Close[SaveAll];
Except
Result := False;
End;
End;
{Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
Begin
Result := True;
Try
Excel.Sheets[SheetName].Select;
Except
Result := False;
End;
End;
{Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[RowNum, ColNum].Select;
Except
Result := False;
End;
End;
{Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
Begin
Result := '';
Try
Result := Excel.Cells[RowNum, ColNum].Value;
Except
Result := '';
End;
End;
{Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Row;
Except
Result := 1;
End;
End;
{Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Column;
Except
Result := 1;
End;
End;
{Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlUp].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToLeft].Select;
Except
Result := False;
End;
End;
{Moves the cursor to Home position}
Function ExcelHome(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[1,1].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurCol;
Excel.Selection.End[xlToRight].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlDown].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlUp].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlToLeft].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
Var
CurRow : Integer;
CurCol : Integer;
TopRowN : Integer;
LeftColN : Integer;
LastRowN : Integer;
LastColN : Integer;
ColLoop : Integer;
RowLoop : Integer;
CellValue : ShortString;
FoundRow : Integer;
FoundCol : Integer;
Found : Boolean;
Begin
Result := False;
Try
Found := False;
FindString := UpperCase(FindString);
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
FoundRow := CurRow;
FoundCol := CurCol;
If SearchRight Then
Begin
LeftColN := LeftCol;
LastColN := LastCol;
End
Else
Begin
LeftColN := LastCol;
LastColN := LeftCol;
End;
If SearchDown Then
Begin
TopRowN := TopRow;
LastRowN := LastRow;
End
Else
Begin
TopRowN := LastRow;
LastRowN := TopRow;
End;
If RowsFirst Then
Begin
For ColLoop := LeftColN To LastColN Do
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End
Else
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
For ColLoop := LeftColN To LastColN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End;
Excel.Cells[FoundRow, FoundCol].Activate;
Result := Found;
Except
Result := False;
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
Begin
Result :=
ExcelFindValue(
Excel,
FindString,
TopRow,
LeftCol,
LastRow,
LastCol,
True,
True,
True);
End;
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
var
loop: byte;
Begin
Result := True;
Try
For loop := 1 to Excel.Workbooks.Count Do
Excel.Workbooks[1].Close[SaveAll];
Except
Result := False;
End;
End;
{Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
Begin
Result := True;
Try
Excel.Sheets[SheetName].Select;
Except
Result := False;
End;
End;
{Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[RowNum, ColNum].Select;
Except
Result := False;
End;
End;
{Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
Begin
Result := '';
Try
Result := Excel.Cells[RowNum, ColNum].Value;
Except
Result := '';
End;
End;
{Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Row;
Except
Result := 1;
End;
End;
{Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Column;
Except
Result := 1;
End;
End;
{Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlUp].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToLeft].Select;
Except
Result := False;
End;
End;
{Moves the cursor to Home position}
Function ExcelHome(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[1,1].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurCol;
Excel.Selection.End[xlToRight].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlDown].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlUp].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlToLeft].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
Var
CurRow : Integer;
CurCol : Integer;
TopRowN : Integer;
LeftColN : Integer;
LastRowN : Integer;
LastColN : Integer;
ColLoop : Integer;
RowLoop : Integer;
CellValue : ShortString;
FoundRow : Integer;
FoundCol : Integer;
Found : Boolean;
Begin
Result := False;
Try
Found := False;
FindString := UpperCase(FindString);
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
FoundRow := CurRow;
FoundCol := CurCol;
If SearchRight Then
Begin
LeftColN := LeftCol;
LastColN := LastCol;
End
Else
Begin
LeftColN := LastCol;
LastColN := LeftCol;
End;
If SearchDown Then
Begin
TopRowN := TopRow;
LastRowN := LastRow;
End
Else
Begin
TopRowN := LastRow;
LastRowN := TopRow;
End;
If RowsFirst Then
Begin
For ColLoop := LeftColN To LastColN Do
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End
Else
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
For ColLoop := LeftColN To LastColN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End;
Excel.Cells[FoundRow, FoundCol].Activate;
Result := Found;
Except
Result := False;
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
Begin
Result :=
ExcelFindValue(
Excel,
FindString,
TopRow,
LeftCol,
LastRow,
LastCol,
True,
True,
True);
End;
#6
up
#7
可是对excel的控制和excel不在同一个窗体上啊~~！
我要求它们在同一个窗体中啊~！
我要求它们在同一个窗体中啊~！
#8
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
错误提示:
Undeclared identifier: 'EmptyParam'[Delphi6]
错误提示:
Undeclared identifier: 'EmptyParam'[Delphi6]
#9
有没有更更好的方法啊~~~~~~~~~~~！
#10
sc
#1
转载他人资料
(一) 使用动态创建的方法
首先创建 Excel 对象，使用ComObj:
var ExcelApp: Variant;
ExcelApp := CreateOleObject( 'Excel.Application' );
1) 显示当前窗口：
ExcelApp.Visible := True;
2) 更改 Excel 标题栏：
ExcelApp.Caption := '应用程序调用 Microsoft Excel';
3) 添加新工作簿：
ExcelApp.WorkBooks.Add;
4) 打开已存在的工作簿：
ExcelApp.WorkBooks.Open( 'C:\Excel\Demo.xls' );
5) 设置第2个工作表为活动工作表：
ExcelApp.WorkSheets[2].Activate;
或
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;
6) 给单元格赋值：
ExcelApp.Cells[1,4].Value := '第一行第四列';
7) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
9) 在第8行之前插入分页符：
ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
10) 在第8列之前删除分页符：
ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
11) 指定边框线宽度：
ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
12) 清除第一行第四列单元格公式：
ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
13) 设置第一行字体属性：
ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine := True;
14) 进行页面设置：
a.页眉：
ExcelApp.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApp.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApp.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApp.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApp.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApp.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
15) 拷贝操作：
a.拷贝整个工作表：
ExcelApp.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApp.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApp.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApp.ActiveSheet.Range.PasteSpecial;
16) 插入一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Insert;
b. ExcelApp.ActiveSheet.Columns[1].Insert;
17) 删除一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Delete;
b. ExcelApp.ActiveSheet.Columns[1].Delete;
18) 打印预览工作表：
ExcelApp.ActiveSheet.PrintPreview;
19) 打印输出工作表：
ExcelApp.ActiveSheet.PrintOut;
20) 工作表保存：
if not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
21) 工作表另存为：
ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
22) 放弃存盘：
ExcelApp.ActiveWorkBook.Saved := True;
23) 关闭工作簿：
ExcelApp.WorkBooks.Close;
24) 退出 Excel：
ExcelApp.Quit;
(二) 使用Delphi 控件方法
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。
1） 打开Excel
ExcelApplication1.Connect;
2) 显示当前窗口：
ExcelApplication1.Visible[0]:=True;
3) 更改 Excel 标题栏：
ExcelApplication1.Caption := '应用程序调用 Microsoft Excel';
4) 添加新工作簿：
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
5) 添加新工作表：
var Temp_Worksheet: _WorkSheet;
begin
Temp_Worksheet:=ExcelWorkbook1.
WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;
ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);
End;
6) 打开已存在的工作簿：
ExcelApplication1.Workbooks.Open (c:\a.xls
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
7) 设置第2个工作表为活动工作表：
ExcelApplication1.WorkSheets[2].Activate; 或
ExcelApplication1.WorksSheets[ 'Sheet2' ].Activate;
8) 给单元格赋值：
ExcelApplication1.Cells[1,4].Value := '第一行第四列';
9) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApplication1.ActiveSheet.Columns[1].ColumnsWidth := 5;
10) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApplication1.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
11) 在第8行之前插入分页符：
ExcelApplication1.WorkSheets[1].Rows[8].PageBreak := 1;
12) 在第8列之前删除分页符：
ExcelApplication1.ActiveSheet.Columns[4].PageBreak := 0;
13) 指定边框线宽度：
ExcelApplication1.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
14) 清除第一行第四列单元格公式：
ExcelApplication1.ActiveSheet.Cells[1,4].ClearContents;
15) 设置第一行字体属性：
ExcelApplication1.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApplication1.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApplication1.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApplication1.ActiveSheet.Rows[1].Font.UnderLine := True;
16) 进行页面设置：
a.页眉：
ExcelApplication1.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApplication1.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApplication1.ActiveSheet.PageSetup.PrintGridLines := True;
17) 拷贝操作：
a.拷贝整个工作表：
ExcelApplication1.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApplication1.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18) 插入一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Insert;
b. ExcelApplication1.ActiveSheet.Columns[1].Insert;
19) 删除一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Delete;
b. ExcelApplication1.ActiveSheet.Columns[1].Delete;
20) 打印预览工作表：
ExcelApplication1.ActiveSheet.PrintPreview;
21) 打印输出工作表：
ExcelApplication1.ActiveSheet.PrintOut;
22) 工作表保存：
if not ExcelApplication1.ActiveWorkBook.Saved then
ExcelApplication1.ActiveSheet.PrintPreview;
23) 工作表另存为：
ExcelApplication1.SaveAs( 'C:\Excel\Demo1.xls' );
24) 放弃存盘：
ExcelApplication1.ActiveWorkBook.Saved := True;
25) 关闭工作簿：
ExcelApplication1.WorkBooks.Close;
26) 退出 Excel：
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;
(三) 使用Delphi 控制Excle二维图
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet
var asheet1,achart, range:variant;
1）选择当第一个工作薄第一个工作表
asheet1:=ExcelApplication1.Workbooks[1].Worksheets[1];
2）增加一个二维图
achart:=asheet1.chartobjects.add(100,100,200,200);
3）选择二维图的形态
achart.chart.charttype:=4;
4）给二维图赋值
series:=achart.chart.seriescollection;
range:=sheet1!r2c3:r3c9;
series.add(range,true);
5）加上二维图的标题
achart.Chart.HasTitle:=True;
achart.Chart.ChartTitle.Characters.Text:=’ Excle二维图’
6）改变二维图的标题字体大小
achart.Chart.ChartTitle.
(一) 使用动态创建的方法
首先创建 Excel 对象，使用ComObj:
var ExcelApp: Variant;
ExcelApp := CreateOleObject( 'Excel.Application' );
1) 显示当前窗口：
ExcelApp.Visible := True;
2) 更改 Excel 标题栏：
ExcelApp.Caption := '应用程序调用 Microsoft Excel';
3) 添加新工作簿：
ExcelApp.WorkBooks.Add;
4) 打开已存在的工作簿：
ExcelApp.WorkBooks.Open( 'C:\Excel\Demo.xls' );
5) 设置第2个工作表为活动工作表：
ExcelApp.WorkSheets[2].Activate;
或
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;
6) 给单元格赋值：
ExcelApp.Cells[1,4].Value := '第一行第四列';
7) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApp.ActiveSheet.Columns[1].ColumnsWidth := 5;
8) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApp.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
9) 在第8行之前插入分页符：
ExcelApp.WorkSheets[1].Rows[8].PageBreak := 1;
10) 在第8列之前删除分页符：
ExcelApp.ActiveSheet.Columns[4].PageBreak := 0;
11) 指定边框线宽度：
ExcelApp.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
12) 清除第一行第四列单元格公式：
ExcelApp.ActiveSheet.Cells[1,4].ClearContents;
13) 设置第一行字体属性：
ExcelApp.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApp.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApp.ActiveSheet.Rows[1].Font.UnderLine := True;
14) 进行页面设置：
a.页眉：
ExcelApp.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApp.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApp.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApp.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApp.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApp.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApp.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApp.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApp.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApp.ActiveSheet.PageSetup.PrintGridLines := True;
15) 拷贝操作：
a.拷贝整个工作表：
ExcelApp.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApp.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApp.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApp.ActiveSheet.Range.PasteSpecial;
16) 插入一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Insert;
b. ExcelApp.ActiveSheet.Columns[1].Insert;
17) 删除一行或一列：
a. ExcelApp.ActiveSheet.Rows[2].Delete;
b. ExcelApp.ActiveSheet.Columns[1].Delete;
18) 打印预览工作表：
ExcelApp.ActiveSheet.PrintPreview;
19) 打印输出工作表：
ExcelApp.ActiveSheet.PrintOut;
20) 工作表保存：
if not ExcelApp.ActiveWorkBook.Saved then
ExcelApp.ActiveSheet.PrintPreview;
21) 工作表另存为：
ExcelApp.SaveAs( 'C:\Excel\Demo1.xls' );
22) 放弃存盘：
ExcelApp.ActiveWorkBook.Saved := True;
23) 关闭工作簿：
ExcelApp.WorkBooks.Close;
24) 退出 Excel：
ExcelApp.Quit;
(二) 使用Delphi 控件方法
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet。
1） 打开Excel
ExcelApplication1.Connect;
2) 显示当前窗口：
ExcelApplication1.Visible[0]:=True;
3) 更改 Excel 标题栏：
ExcelApplication1.Caption := '应用程序调用 Microsoft Excel';
4) 添加新工作簿：
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
5) 添加新工作表：
var Temp_Worksheet: _WorkSheet;
begin
Temp_Worksheet:=ExcelWorkbook1.
WorkSheets.Add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _WorkSheet;
ExcelWorkSheet1.ConnectTo(Temp_WorkSheet);
End;
6) 打开已存在的工作簿：
ExcelApplication1.Workbooks.Open (c:\a.xls
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,
EmptyParam,EmptyParam,EmptyParam,EmptyParam,0)
7) 设置第2个工作表为活动工作表：
ExcelApplication1.WorkSheets[2].Activate; 或
ExcelApplication1.WorksSheets[ 'Sheet2' ].Activate;
8) 给单元格赋值：
ExcelApplication1.Cells[1,4].Value := '第一行第四列';
9) 设置指定列的宽度（单位：字符个数），以第一列为例：
ExcelApplication1.ActiveSheet.Columns[1].ColumnsWidth := 5;
10) 设置指定行的高度（单位：磅）（1磅＝0.035厘米），以第二行为例：
ExcelApplication1.ActiveSheet.Rows[2].RowHeight := 1/0.035; // 1厘米
11) 在第8行之前插入分页符：
ExcelApplication1.WorkSheets[1].Rows[8].PageBreak := 1;
12) 在第8列之前删除分页符：
ExcelApplication1.ActiveSheet.Columns[4].PageBreak := 0;
13) 指定边框线宽度：
ExcelApplication1.ActiveSheet.Range[ 'B3:D4' ].Borders[2].Weight := 3;
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )
14) 清除第一行第四列单元格公式：
ExcelApplication1.ActiveSheet.Cells[1,4].ClearContents;
15) 设置第一行字体属性：
ExcelApplication1.ActiveSheet.Rows[1].Font.Name := '隶书';
ExcelApplication1.ActiveSheet.Rows[1].Font.Color := clBlue;
ExcelApplication1.ActiveSheet.Rows[1].Font.Bold := True;
ExcelApplication1.ActiveSheet.Rows[1].Font.UnderLine := True;
16) 进行页面设置：
a.页眉：
ExcelApplication1.ActiveSheet.PageSetup.CenterHeader := '报表演示';
b.页脚：
ExcelApplication1.ActiveSheet.PageSetup.CenterFooter := '第&P页';
c.页眉到顶端边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 2/0.035;
d.页脚到底端边距3cm：
ExcelApplication1.ActiveSheet.PageSetup.HeaderMargin := 3/0.035;
e.顶边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.TopMargin := 2/0.035;
f.底边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.BottomMargin := 2/0.035;
g.左边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.LeftMargin := 2/0.035;
h.右边距2cm：
ExcelApplication1.ActiveSheet.PageSetup.RightMargin := 2/0.035;
i.页面水平居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterHorizontally := 2/0.035;
j.页面垂直居中：
ExcelApplication1.ActiveSheet.PageSetup.CenterVertically := 2/0.035;
k.打印单元格网线：
ExcelApplication1.ActiveSheet.PageSetup.PrintGridLines := True;
17) 拷贝操作：
a.拷贝整个工作表：
ExcelApplication1.ActiveSheet.Used.Range.Copy;
b.拷贝指定区域：
ExcelApplication1.ActiveSheet.Range[ 'A1:E2' ].Copy;
c.从A1位置开始粘贴：
ExcelApplication1.ActiveSheet.Range.[ 'A1' ].PasteSpecial;
d.从文件尾部开始粘贴：
ExcelApplication1.ActiveSheet.Range.PasteSpecial;
18) 插入一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Insert;
b. ExcelApplication1.ActiveSheet.Columns[1].Insert;
19) 删除一行或一列：
a. ExcelApplication1.ActiveSheet.Rows[2].Delete;
b. ExcelApplication1.ActiveSheet.Columns[1].Delete;
20) 打印预览工作表：
ExcelApplication1.ActiveSheet.PrintPreview;
21) 打印输出工作表：
ExcelApplication1.ActiveSheet.PrintOut;
22) 工作表保存：
if not ExcelApplication1.ActiveWorkBook.Saved then
ExcelApplication1.ActiveSheet.PrintPreview;
23) 工作表另存为：
ExcelApplication1.SaveAs( 'C:\Excel\Demo1.xls' );
24) 放弃存盘：
ExcelApplication1.ActiveWorkBook.Saved := True;
25) 关闭工作簿：
ExcelApplication1.WorkBooks.Close;
26) 退出 Excel：
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;
(三) 使用Delphi 控制Excle二维图
在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet
var asheet1,achart, range:variant;
1）选择当第一个工作薄第一个工作表
asheet1:=ExcelApplication1.Workbooks[1].Worksheets[1];
2）增加一个二维图
achart:=asheet1.chartobjects.add(100,100,200,200);
3）选择二维图的形态
achart.chart.charttype:=4;
4）给二维图赋值
series:=achart.chart.seriescollection;
range:=sheet1!r2c3:r3c9;
series.add(range,true);
5）加上二维图的标题
achart.Chart.HasTitle:=True;
achart.Chart.ChartTitle.Characters.Text:=’ Excle二维图’
6）改变二维图的标题字体大小
achart.Chart.ChartTitle.
#2
diff,case of.
#3
转载操作单元
unit ExcelUnit;
interface
uses
Dialogs, Messages, SysUtils, Grids, Cmp_Sec, ComObj, Ads_Misc;
{!~Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
{!~Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Returns the Column String Value from its integer equilavent.}
Function ExcelColIntToStr(ColNum: Integer): ShortString;
{!~Returns the Column Integer Value from its Alpha equilavent.}
Function ExcelColStrToInt(ColStr: ShortString): Integer;
{!~Close All Workbooks. All workbooks can be saved or not.}
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Copies a range of Excel Cells to a Delphi StringGrid. If successful
True is returned, False otherwise. If SizeStringGridToFit is True
then the StringGrid is resized to be exactly the correct dimensions to
receive the input Excel cells, otherwise the StringGrid is not resized.
If ClearStringGridFirst is true then any cells outside the input range
are cleared, otherwise existing values are retained. Please not that the
Excel cell coordinates are "1" based and the Delphi StringGrid coordinates
are zero based.}
Function ExcelCopyToStringGrid(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
StringGrid : TStringGrid;
StringGridFirstRow : Integer;
StringGridFirstCol : Integer;
{Make the StringGrid the same size as the input range}
SizeStringGridToFit : Boolean;
{cells outside input range in StringGrid are cleared}
ClearStringGridFirst : Boolean
): Boolean;
{!~Delete a WorkSheet by Name}
Function ExcelDeleteWorkSheet(
Excel : Variant;
SheetName : ShortString): Boolean;
{!~Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
{!~Finds A value and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFind(
Excel : Variant;
FindString : ShortString): Boolean;
{!~Finds A value in a range and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise. The search directions can be defined.
If you want row searches to go from left to right then SearchRight should
be set to true, False otherwise. If you want column searches to go from
top to bottom then SearchDown should be set to true, false otherwise.
If RowsFirst is set to true then all the columns in a complete row will be
searched.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
{!~Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
{!~Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
{!~Returns the name of the currently active worksheet
as a shortstring}
Function ExcelGetActiveSheetName(Excel : Variant): ShortString;
{!~Gets the formula in a cell.}
Function ExcelGetCellFormula(
Excel : Variant;
RowNum, ColNum: Integer): ShortString;
{!~Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
{!~Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
{!~Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
{!~Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
{!~Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
{!~Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
{!~Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
{!~Moves the cursor to Home position, i.e., A1}
Function ExcelHome(Excel : Variant): Boolean;
{!~Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
{!~Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFile(Excel : Variant; FileName : String): Boolean;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFileComplex(
Excel : Variant;
FileName : String;
UpdateLinks : Integer;
ReadOnly : Boolean;
Format : Integer;
Password : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to to values only.}
Function ExcelPasteValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Renames a worksheet.}
Function ExcelRenameSheet(
Excel : Variant;
OldName : ShortString;
NewName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
unit ExcelUnit;
interface
uses
Dialogs, Messages, SysUtils, Grids, Cmp_Sec, ComObj, Ads_Misc;
{!~Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
{!~Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Returns the Column String Value from its integer equilavent.}
Function ExcelColIntToStr(ColNum: Integer): ShortString;
{!~Returns the Column Integer Value from its Alpha equilavent.}
Function ExcelColStrToInt(ColStr: ShortString): Integer;
{!~Close All Workbooks. All workbooks can be saved or not.}
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
{!~Copies a range of Excel Cells to a Delphi StringGrid. If successful
True is returned, False otherwise. If SizeStringGridToFit is True
then the StringGrid is resized to be exactly the correct dimensions to
receive the input Excel cells, otherwise the StringGrid is not resized.
If ClearStringGridFirst is true then any cells outside the input range
are cleared, otherwise existing values are retained. Please not that the
Excel cell coordinates are "1" based and the Delphi StringGrid coordinates
are zero based.}
Function ExcelCopyToStringGrid(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
StringGrid : TStringGrid;
StringGridFirstRow : Integer;
StringGridFirstCol : Integer;
{Make the StringGrid the same size as the input range}
SizeStringGridToFit : Boolean;
{cells outside input range in StringGrid are cleared}
ClearStringGridFirst : Boolean
): Boolean;
{!~Delete a WorkSheet by Name}
Function ExcelDeleteWorkSheet(
Excel : Variant;
SheetName : ShortString): Boolean;
{!~Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
{!~Finds A value and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFind(
Excel : Variant;
FindString : ShortString): Boolean;
{!~Finds A value in a range and moves the cursor there.
If the value is not found then the cursor does not move.
If nothing is found then false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise. The search directions can be defined.
If you want row searches to go from left to right then SearchRight should
be set to true, False otherwise. If you want column searches to go from
top to bottom then SearchDown should be set to true, false otherwise.
If RowsFirst is set to true then all the columns in a complete row will be
searched.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
{!~Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
{!~Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
{!~Returns the name of the currently active worksheet
as a shortstring}
Function ExcelGetActiveSheetName(Excel : Variant): ShortString;
{!~Gets the formula in a cell.}
Function ExcelGetCellFormula(
Excel : Variant;
RowNum, ColNum: Integer): ShortString;
{!~Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
{!~Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
{!~Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
{!~Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
{!~Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
{!~Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
{!~Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
{!~Moves the cursor to Home position, i.e., A1}
Function ExcelHome(Excel : Variant): Boolean;
{!~Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
{!~Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFile(Excel : Variant; FileName : String): Boolean;
{!~Open the file you want to work within Excel. If you want to
take advantage of optional parameters then you should use
ExcelOpenFileComplex}
Function ExcelOpenFileComplex(
Excel : Variant;
FileName : String;
UpdateLinks : Integer;
ReadOnly : Boolean;
Format : Integer;
Password : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to to values only.}
Function ExcelPasteValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Renames a worksheet.}
Function ExcelRenameSheet(
Excel : Variant;
OldName : ShortString;
NewName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
#4
{!~Saves the range on the currently active sheet
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a text file.}
Function ExcelSaveAsText(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected down and to the right.
The block proceeds down until an empty row is encountered. The
block proceeds right until an empty column is encountered.}
Function ExcelSelectBlock(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected that contains
the currently active cell. The block proceeds in each
direction until an empty row or column is encountered.}
Function ExcelSelectBlockWhole(Excel: Variant): Boolean;
{!~Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
{!~Selects a range on the currently active sheet}
Function ExcelSelectRange(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
{!~Sets the formula in a cell. Remember to include the equals sign "=".
If the function fails False is returned, True otherwise.}
Function ExcelSetCellFormula(
Excel : Variant;
FormulaString : ShortString;
RowNum, ColNum: Integer): Boolean;
{!~Sets the contents of a cell as a shortstring}
Function ExcelSetCellValue(
Excel : Variant;
RowNum, ColNum: Integer;
Value : ShortString): Boolean;
{!~Sets a Column Width on the currently active sheet}
Function ExcelSetColumnWidth(
Excel : Variant;
ColNum : Integer;
ColumnWidth: Integer): Boolean;
{!~Set Excel Visibility}
Function ExcelSetVisible(
Excel : Variant;
IsVisible: Boolean): Boolean;
{!~Saves the range on the currently active sheet
to values only.}
Function ExcelValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Returns the Excel Version as a ShortString.}
Function ExcelVersion(Excel: Variant): ShortString;
Function IsBlockColSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
Function IsBlockRowSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
implementation
type
//Declare the constants used by Excel
SourceType = (xlConsolidation, xlDatabase, xlExternal, xlPivotTable);
Orientation = (xlHidden, xlRowField, xlColumnField, xlPageField, xlDataField);
RangeEnd = (NoValue, xlToLeft, xlToRight, xlUp, xlDown);
ExcelPasteType = (xlAllExceptBorders,xlNotes,xlFormats,xlValues,xlFormulas,xlAll);
{CAUTION!!! THESE OUTPUTS ARE ALL GARBLED! YOU SELECT xlDBF3 AND EXCEL
OUTPUTS A xlCSV.}
FileFormat = (xlAddIn, xlCSV, xlCSVMac, xlCSVMSDOS, xlCSVWindows, xlDBF2,
xlDBF3, xlDBF4, xlDIF, xlExcel2, xlExcel3, xlExcel4,
xlExcel4Workbook, xlIntlAddIn, xlIntlMacro, xlNormal,
xlSYLK, xlTemplate, xlText, xlTextMac, xlTextMSDOS,
xlTextWindows, xlTextPrinter, xlWK1, xlWK3, xlWKS,
xlWQ1, xlWK3FM3, xlWK1FMT, xlWK1ALL);
{Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Worksheets.Add;
Except
MessageDlg('Unable to add a new worksheet', mtError, [mbOK], 0);
Result := False;
End;
End;
{Sets Excel Visibility}
Function ExcelSetVisible(Excel : Variant;IsVisible: Boolean): Boolean;
Begin
Result := True;
Try
Excel.Visible := IsVisible;
Except
MessageDlg('Unable to Excel Visibility', mtError, [mbOK], 0);
Result := False;
End;
End;
{Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
Begin
Result := True;
Try
ExcelCloseWorkBooks(Excel, SaveAll);
Excel.Quit;
Except
MessageDlg('Unable to Close Excel', mtError, [mbOK], 0);
Result := False;
End;
End;
to a DBase 4 table.}
Function ExcelSaveAsDBase4(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Saves the range on the currently active sheet
to a text file.}
Function ExcelSaveAsText(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer;
OutFilePath : ShortString;
OutFileName : ShortString): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected down and to the right.
The block proceeds down until an empty row is encountered. The
block proceeds right until an empty column is encountered.}
Function ExcelSelectBlock(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer): Boolean;
{!~Selects a range on the currently active sheet. From the
current cursor position a block is selected that contains
the currently active cell. The block proceeds in each
direction until an empty row or column is encountered.}
Function ExcelSelectBlockWhole(Excel: Variant): Boolean;
{!~Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
{!~Selects a range on the currently active sheet}
Function ExcelSelectRange(
Excel : Variant;
FirstRow : Integer;
FirstCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
{!~Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
{!~Sets the formula in a cell. Remember to include the equals sign "=".
If the function fails False is returned, True otherwise.}
Function ExcelSetCellFormula(
Excel : Variant;
FormulaString : ShortString;
RowNum, ColNum: Integer): Boolean;
{!~Sets the contents of a cell as a shortstring}
Function ExcelSetCellValue(
Excel : Variant;
RowNum, ColNum: Integer;
Value : ShortString): Boolean;
{!~Sets a Column Width on the currently active sheet}
Function ExcelSetColumnWidth(
Excel : Variant;
ColNum : Integer;
ColumnWidth: Integer): Boolean;
{!~Set Excel Visibility}
Function ExcelSetVisible(
Excel : Variant;
IsVisible: Boolean): Boolean;
{!~Saves the range on the currently active sheet
to values only.}
Function ExcelValuesOnly(
Excel : Variant;
ExcelFirstRow : Integer;
ExcelFirstCol : Integer;
ExcelLastRow : Integer;
ExcelLastCol : Integer): Boolean;
{!~Returns the Excel Version as a ShortString.}
Function ExcelVersion(Excel: Variant): ShortString;
Function IsBlockColSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
Function IsBlockRowSide(
Excel : Variant;
RowNum: Integer;
ColNum: Integer): Boolean; Forward;
implementation
type
//Declare the constants used by Excel
SourceType = (xlConsolidation, xlDatabase, xlExternal, xlPivotTable);
Orientation = (xlHidden, xlRowField, xlColumnField, xlPageField, xlDataField);
RangeEnd = (NoValue, xlToLeft, xlToRight, xlUp, xlDown);
ExcelPasteType = (xlAllExceptBorders,xlNotes,xlFormats,xlValues,xlFormulas,xlAll);
{CAUTION!!! THESE OUTPUTS ARE ALL GARBLED! YOU SELECT xlDBF3 AND EXCEL
OUTPUTS A xlCSV.}
FileFormat = (xlAddIn, xlCSV, xlCSVMac, xlCSVMSDOS, xlCSVWindows, xlDBF2,
xlDBF3, xlDBF4, xlDIF, xlExcel2, xlExcel3, xlExcel4,
xlExcel4Workbook, xlIntlAddIn, xlIntlMacro, xlNormal,
xlSYLK, xlTemplate, xlText, xlTextMac, xlTextMSDOS,
xlTextWindows, xlTextPrinter, xlWK1, xlWK3, xlWKS,
xlWQ1, xlWK3FM3, xlWK1FMT, xlWK1ALL);
{Add a blank WorkSheet}
Function ExcelAddWorkSheet(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Worksheets.Add;
Except
MessageDlg('Unable to add a new worksheet', mtError, [mbOK], 0);
Result := False;
End;
End;
{Sets Excel Visibility}
Function ExcelSetVisible(Excel : Variant;IsVisible: Boolean): Boolean;
Begin
Result := True;
Try
Excel.Visible := IsVisible;
Except
MessageDlg('Unable to Excel Visibility', mtError, [mbOK], 0);
Result := False;
End;
End;
{Close Excel}
Function ExcelClose(Excel : Variant; SaveAll: Boolean): Boolean;
Begin
Result := True;
Try
ExcelCloseWorkBooks(Excel, SaveAll);
Excel.Quit;
Except
MessageDlg('Unable to Close Excel', mtError, [mbOK], 0);
Result := False;
End;
End;
#5
{Close All Workbooks. All workbooks can be saved or not.}
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
var
loop: byte;
Begin
Result := True;
Try
For loop := 1 to Excel.Workbooks.Count Do
Excel.Workbooks[1].Close[SaveAll];
Except
Result := False;
End;
End;
{Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
Begin
Result := True;
Try
Excel.Sheets[SheetName].Select;
Except
Result := False;
End;
End;
{Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[RowNum, ColNum].Select;
Except
Result := False;
End;
End;
{Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
Begin
Result := '';
Try
Result := Excel.Cells[RowNum, ColNum].Value;
Except
Result := '';
End;
End;
{Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Row;
Except
Result := 1;
End;
End;
{Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Column;
Except
Result := 1;
End;
End;
{Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlUp].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToLeft].Select;
Except
Result := False;
End;
End;
{Moves the cursor to Home position}
Function ExcelHome(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[1,1].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurCol;
Excel.Selection.End[xlToRight].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlDown].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlUp].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlToLeft].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
Var
CurRow : Integer;
CurCol : Integer;
TopRowN : Integer;
LeftColN : Integer;
LastRowN : Integer;
LastColN : Integer;
ColLoop : Integer;
RowLoop : Integer;
CellValue : ShortString;
FoundRow : Integer;
FoundCol : Integer;
Found : Boolean;
Begin
Result := False;
Try
Found := False;
FindString := UpperCase(FindString);
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
FoundRow := CurRow;
FoundCol := CurCol;
If SearchRight Then
Begin
LeftColN := LeftCol;
LastColN := LastCol;
End
Else
Begin
LeftColN := LastCol;
LastColN := LeftCol;
End;
If SearchDown Then
Begin
TopRowN := TopRow;
LastRowN := LastRow;
End
Else
Begin
TopRowN := LastRow;
LastRowN := TopRow;
End;
If RowsFirst Then
Begin
For ColLoop := LeftColN To LastColN Do
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End
Else
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
For ColLoop := LeftColN To LastColN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End;
Excel.Cells[FoundRow, FoundCol].Activate;
Result := Found;
Except
Result := False;
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
Begin
Result :=
ExcelFindValue(
Excel,
FindString,
TopRow,
LeftCol,
LastRow,
LastCol,
True,
True,
True);
End;
Function ExcelCloseWorkBooks(Excel : Variant; SaveAll: Boolean): Boolean;
var
loop: byte;
Begin
Result := True;
Try
For loop := 1 to Excel.Workbooks.Count Do
Excel.Workbooks[1].Close[SaveAll];
Except
Result := False;
End;
End;
{Selects an Excel Sheet By Name}
Function ExcelSelectSheetByName(Excel : Variant; SheetName: String): Boolean;
Begin
Result := True;
Try
Excel.Sheets[SheetName].Select;
Except
Result := False;
End;
End;
{Selects a cell on the currently active sheet}
Function ExcelSelectCell(Excel : Variant; RowNum, ColNum: Integer): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[RowNum, ColNum].Select;
Except
Result := False;
End;
End;
{Returns the contents of a cell as a shortstring}
Function ExcelGetCellValue(Excel : Variant; RowNum, ColNum: Integer): ShortString;
Begin
Result := '';
Try
Result := Excel.Cells[RowNum, ColNum].Value;
Except
Result := '';
End;
End;
{Returns the the current row}
Function ExcelGetRow(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Row;
Except
Result := 1;
End;
End;
{Returns the the current column}
Function ExcelGetCol(Excel : Variant): Integer;
Begin
Result := 1;
Try
Result := Excel.ActiveCell.Column;
Except
Result := 1;
End;
End;
{Moves the cursor to the last column}
Function ExcelGoToLastCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row}
Function ExcelGoToLastRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Top row}
Function ExcelGoToTopRow(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlUp].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the Leftmost Column}
Function ExcelGoToLeftmostCol(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlToLeft].Select;
Except
Result := False;
End;
End;
{Moves the cursor to Home position}
Function ExcelHome(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.ActiveSheet.Cells[1,1].Select;
Except
Result := False;
End;
End;
{Moves the cursor to the last row and column}
Function ExcelEnd(Excel : Variant): Boolean;
Begin
Result := True;
Try
Excel.Selection.End[xlDown].Select;
Excel.Selection.End[xlToRight].Select;
Except
Result := False;
End;
End;
{Returns The Last Column}
Function ExcelLastCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurCol;
Excel.Selection.End[xlToRight].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The Last Row}
Function ExcelLastRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlDown].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Row}
Function ExcelFirstRow(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlUp].Select;
Result := Excel.ActiveCell.Row;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Returns The First Col}
Function ExcelFirstCol(Excel : Variant): Integer;
Var
CurRow : Integer;
CurCol : Integer;
Begin
Result := 1;
Try
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
Result := CurRow;
Excel.Selection.End[xlToLeft].Select;
Result := Excel.ActiveCell.Column;
Excel.ActiveSheet.Cells[CurRow, CurCol].Select;
Except
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindValue(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer;
SearchRight : Boolean;
SearchDown : Boolean;
RowsFirst : Boolean
): Boolean;
Var
CurRow : Integer;
CurCol : Integer;
TopRowN : Integer;
LeftColN : Integer;
LastRowN : Integer;
LastColN : Integer;
ColLoop : Integer;
RowLoop : Integer;
CellValue : ShortString;
FoundRow : Integer;
FoundCol : Integer;
Found : Boolean;
Begin
Result := False;
Try
Found := False;
FindString := UpperCase(FindString);
CurRow := Excel.ActiveCell.Row;
CurCol := Excel.ActiveCell.Column;
FoundRow := CurRow;
FoundCol := CurCol;
If SearchRight Then
Begin
LeftColN := LeftCol;
LastColN := LastCol;
End
Else
Begin
LeftColN := LastCol;
LastColN := LeftCol;
End;
If SearchDown Then
Begin
TopRowN := TopRow;
LastRowN := LastRow;
End
Else
Begin
TopRowN := LastRow;
LastRowN := TopRow;
End;
If RowsFirst Then
Begin
For ColLoop := LeftColN To LastColN Do
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End
Else
Begin
For RowLoop := TopRowN To LastRowN Do
Begin
For ColLoop := LeftColN To LastColN Do
Begin
CellValue := ExcelGetCellValue(Excel,RowLoop, ColLoop);
If UpperCase(CellValue) = FindString Then
Begin
FoundRow := RowLoop;
FoundCol := ColLoop;
Found := True;
Break;
End;
End;
If Found Then Break;
End;
End;
Excel.Cells[FoundRow, FoundCol].Activate;
Result := Found;
Except
Result := False;
End;
End;
{Finds A value in a range and moves the cursor there. If the value is
not found then the cursor does not move. If nothing is found then
false is returned, True otherwise.}
Function ExcelFindInRange(
Excel : Variant;
FindString : ShortString;
TopRow : Integer;
LeftCol : Integer;
LastRow : Integer;
LastCol : Integer): Boolean;
Begin
Result :=
ExcelFindValue(
Excel,
FindString,
TopRow,
LeftCol,
LastRow,
LastCol,
True,
True,
True);
End;
#6
up
#7
可是对excel的控制和excel不在同一个窗体上啊~~！
我要求它们在同一个窗体中啊~！
我要求它们在同一个窗体中啊~！
#8
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
错误提示:
Undeclared identifier: 'EmptyParam'[Delphi6]
错误提示:
Undeclared identifier: 'EmptyParam'[Delphi6]
#9
有没有更更好的方法啊~~~~~~~~~~~！
#10
sc