如题,
请问,如何用OLE实现操作EXCEL,
建立新的EXCEL对像和工作薄对像都会,
如何激活每二个工作表为操作的工作表.
我用以下总是出错:
ExcelApp.WorkSheets[2].Activate;
或
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;请高手指点.
请问,如何用OLE实现操作EXCEL,
建立新的EXCEL对像和工作薄对像都会,
如何激活每二个工作表为操作的工作表.
我用以下总是出错:
ExcelApp.WorkSheets[2].Activate;
或
ExcelApp.WorksSheets[ 'Sheet2' ].Activate;请高手指点.
解决方案 »
- 用delphi进行串口编程时 出现error reading comm device错误
- 怎么把winskoet控件加到面板上来啊???
- 怎样将adoquery查询后的记录,单击后保存或输出为excel文件!!急!要实例!给分!
- 高分求购:类似Win2000登录窗口上部的“色彩渐进”进度条控件或代码。
- 谁知道InterBase6.0的注册码啊?急用!
- 请问我这个循环怎么停不下来?
- 请问,如何把一个EDIT控件中输入的字符保存的数据库中表的相应的列里?
- 又是sql语句问题
- 如何实现Ras的拨号实现啊?up,关注,收藏没有分啊!!
- 怎样通过程序自动在OICQ的“发送讯息”窗口填入文字并自动发送?
- 在线急等!! 怎么样做一个无穷播放的多媒体?
- DBImage1的问题??
{****************************************************
//
Description :
把一个表或Query或StringGrid中的数据保存到一个Execl文件中
Function List :
创建接口
procedure CreateExcelInstance;
把表内容放到Excel文件中
procedure TableToExcel( const Table: TTable );
把Query内容放到Excel文件中
procedure QueryToExcel( const Query: TQuery );
把StringGrid内容放到Excel文件中
procedure StringGridToExcel( const StringGrid: TStringGrid );
保存为Execl文件
procedure SaveToExcel( const FileName: String);调用实例如下:
OLEExcel1.CreateExcelInstance;
OLEExcel1.QuerytoExcel((CurRep.DataSet as TQuery));
OLEExcel1.SaveToExcel(SaveDlg1.FileName);
OLEExcel1.CloseExcel; //CLose Excel Instance.
****************************************************}
unit OleExcel;interfaceuses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
comobj, DBTables,variants, Grids,adodb;
type
TOLEExcel = class(TComponent)
private
FExcelCreated: Boolean;
FVisible: Boolean;
FExcel: Variant;
FWorkBook: Variant;
FWorkSheet: Variant;
FCellFont: TFont;
FTitleFont: TFont;
FFontChanged: Boolean;
FIgnoreFont: Boolean;
FFileName: TFileName;
procedure SetExcelCellFont(var Cell: Variant);
procedure SetExcelTitleFont(var Cell: Variant);
procedure GetTableColumnName(const Table: TTable; var Cell: Variant);
procedure GetAdoqueryColumnName(const AdoQuery:TadoQuery;var cell:variant);
procedure GetQueryColumnName(const Query: TQuery; var Cell: Variant);
procedure GetFixedCols(const StringGrid: TStringGrid; var Cell: Variant);
procedure GetFixedRows(const StringGrid: TStringGrid; var Cell: Variant);
procedure GetStringGridBody(const StringGrid: TStringGrid; var Cell: Variant);
protected
procedure SetCellFont(NewFont: TFont);
procedure SetTitleFont(NewFont: TFont);
procedure SetVisible(DoShow: Boolean);
function GetCell(ACol, ARow: Integer): string;
procedure SetCell(ACol, ARow: Integer; const Value: string); function GetDateCell(ACol, ARow: Integer): TDateTime;
procedure SetDateCell(ACol, ARow: Integer; const Value: TDateTime);
public
constructor Create(AOwner: TComponent); override;
destructor Destroy; override;
procedure CreateExcelInstance;
property Cell[ACol, ARow: Integer]: string read GetCell write SetCell;
property DateCell[ACol, ARow: Integer]: TDateTime read GetDateCell write SetDateCell;
function IsCreated: Boolean;
procedure TableToExcel(const Table: TTable);
procedure AdoQueryToExcel(const Adoquery:TAdoQuery);
procedure QueryToExcel(const Query: TQuery);
procedure CloseExcel;
procedure StringGridToExcel(const StringGrid: TStringGrid);
procedure SaveToExcel(const FileName: string);
published
property TitleFont: TFont read FTitleFont write SetTitleFont;
property CellFont: TFont read FCellFont write SetCellFont;
property Visible: Boolean read FVisible write SetVisible;
property IgnoreFont: Boolean read FIgnoreFont write FIgnoreFont;
property FileName: TFileName read FFileName write FFileName;
end;procedure Register;implementationconstructor TOLEExcel.Create(AOwner: TComponent);
begin
inherited Create(AOwner);
FIgnoreFont := True;
FCellFont := TFont.Create;
FTitleFont := TFont.Create;
FExcelCreated := False;
FVisible := False;
FFontChanged := False;
end;destructor TOLEExcel.Destroy;
begin
FCellFont.Free;
FTitleFont.Free;
inherited Destroy;
end;procedure TOLEExcel.SetExcelCellFont(var Cell: Variant);
begin
if FIgnoreFont then exit;
with FCellFont do
begin
Cell.Font.Name := Name;
Cell.Font.Size := Size;
Cell.Font.Color := Color;
Cell.Font.Bold := fsBold in Style;
Cell.Font.Italic := fsItalic in Style;
Cell.Font.UnderLine := fsUnderline in Style;
Cell.Font.Strikethrough := fsStrikeout in Style;
end;
end;procedure TOLEExcel.SetExcelTitleFont(var Cell: Variant);
begin
if FIgnoreFont then exit;
with FTitleFont do
begin
Cell.Font.Name := Name;
Cell.Font.Size := Size;
Cell.Font.Color := Color;
Cell.Font.Bold := fsBold in Style;
Cell.Font.Italic := fsItalic in Style;
Cell.Font.UnderLine := fsUnderline in Style;
Cell.Font.Strikethrough := fsStrikeout in Style;
end;
end;
begin
if not FExcelCreated then exit;
if DoShow then
FExcel.Visible := True
else
FExcel.Visible := False;
end;function TOLEExcel.GetCell(ACol, ARow: Integer): string;
begin
if not FExcelCreated then exit;
result := FWorkSheet.Cells[ARow, ACol];
end;procedure TOLEExcel.SetCell(ACol, ARow: Integer; const Value: string);
var
Cell: Variant;
begin
if not FExcelCreated then exit;
Cell := FWorkSheet.Cells[ARow, ACol];
SetExcelCellFont(Cell);
Cell.Value := Value;
end;
function TOLEExcel.GetDateCell(ACol, ARow: Integer): TDateTime;
begin
if not FExcelCreated then
begin
result := 0;
exit;
end;
result := StrToDateTime(FWorkSheet.Cells[ARow, ACol]);
end;procedure TOLEExcel.SetDateCell(ACol, ARow: Integer; const Value: TDateTime);
var
Cell: Variant;
begin
if not FExcelCreated then exit;
Cell := FWorkSheet.Cells[ARow, ACol];
SetExcelCellFont(Cell);
Cell.Value := '''' + DateTimeToStr(Value);
end;procedure TOLEExcel.CreateExcelInstance;
begin
try
FExcel := CreateOLEObject('Excel.Application');
FWorkBook := FExcel.WorkBooks.Add;
FWorkSheet := FWorkBook.WorkSheets.Add;
FExcelCreated := True;
except
FExcelCreated := False;
end;
end;function TOLEExcel.IsCreated: Boolean;
begin
result := FExcelCreated;
end;procedure TOLEExcel.SetTitleFont(NewFont: TFont);
begin
if NewFont <> FTitleFont then
FTitleFont.Assign(NewFont);
end;procedure TOLEExcel.SetCellFont(NewFont: TFont);
begin
if NewFont <> FCellFont then
FCellFont.Assign(NewFont);
end;procedure TOLEExcel.GetTableColumnName(const Table: TTable; var Cell: Variant);
var
Col: integer;
begin
for Col := 0 to Table.FieldCount - 1 do
begin
Cell := FWorkSheet.Cells[1, Col + 1];
SetExcelTitleFont(Cell);
Cell.Value := Table.Fields[Col].FieldName;
end;
end;procedure TOLEExcel.TableToExcel(const Table: TTable);
var
Col, Row: LongInt;
Cell: Variant;
begin
if not FExcelCreated then exit;
if Table.Active = False then exit; GetTableColumnName(Table, Cell);
Row := 2;
with Table do
begin
first;
while not EOF do
begin
for Col := 0 to FieldCount - 1 do
begin
Cell := FWorkSheet.Cells[Row, Col + 1];
SetExcelCellFont(Cell);
Cell.Value := Fields[Col].AsString;
end;
next;
Inc(Row);
end;
end;
end;
procedure TOLEExcel.GetQueryColumnName(const Query: TQuery; var Cell: Variant);
var
Col: integer;
begin
for Col := 0 to Query.FieldCount - 1 do
begin
Cell := FWorkSheet.Cells[1, Col + 1];
SetExcelTitleFont(Cell);
Cell.Value := Query.Fields[Col].FieldName;
end;
end;
procedure Toleexcel.GetAdoqueryColumnName(const AdoQuery:TAdoquery;var cell:variant);
var
Col: integer;
begin
for Col := 0 to AdoQuery.FieldCount - 1 do
begin
Cell := FWorkSheet.Cells[1, Col + 1];
SetExcelTitleFont(Cell);
Cell.Value := AdoQuery.Fields[Col].FieldName;
end;
end;
procedure TOleExcel.AdoQueryToExcel(const Adoquery:TAdoQuery);
var
Col, Row: LongInt;
Cell: Variant;
begin
if not FExcelCreated then exit;
if AdoQuery.Active = False then exit; GetAdoQueryColumnName(AdoQuery, Cell);
Row := 2;
with ADoQuery do
begin
first;
while not EOF do
begin
for Col := 0 to FieldCount - 1 do
begin
Cell := FWorkSheet.Cells[Row, Col + 1];
SetExcelCellFont(Cell);
Cell.Value := Fields[Col].AsString;
end;
next;
Inc(Row);
end;
end;
end;
procedure TOLEExcel.QueryToExcel(const Query: TQuery);
var
Col, Row: LongInt;
Cell: Variant;
begin
if not FExcelCreated then exit;
if Query.Active = False then exit; GetQueryColumnName(Query, Cell);
Row := 2;
with Query do
begin
first;
while not EOF do
begin
for Col := 0 to FieldCount - 1 do
begin
Cell := FWorkSheet.Cells[Row, Col + 1];
SetExcelCellFont(Cell);
Cell.Value := Fields[Col].AsString;
end;
next;
Inc(Row);
end;
end;
end;procedure TOLEExcel.GetFixedCols(const StringGrid: TStringGrid; var Cell: Variant);
var
Col, Row: LongInt;
begin
for Col := 0 to StringGrid.FixedCols - 1 do
for Row := 0 to StringGrid.RowCount - 1 do
begin
Cell := FWorkSheet.Cells[Row + 1, Col + 1];
SetExcelTitleFont(Cell);
Cell.Value := StringGrid.Cells[Col, Row];
end;
end;procedure TOLEExcel.GetFixedRows(const StringGrid: TStringGrid; var Cell: Variant);
var
Col, Row: LongInt;
begin
for Row := 0 to StringGrid.FixedRows - 1 do
for Col := 0 to StringGrid.ColCount - 1 do
begin
Cell := FWorkSheet.Cells[Row + 1, Col + 1];
SetExcelTitleFont(Cell);
Cell.Value := StringGrid.Cells[Col, Row];
end;
end;procedure TOLEExcel.GetStringGridBody(const StringGrid: TStringGrid; var Cell: Variant);
var
Col, Row, x, y: LongInt;
begin
Col := StringGrid.FixedCols;
Row := StringGrid.FixedRows;
for x := Row to StringGrid.RowCount - 1 do
for y := Col to StringGrid.ColCount - 1 do
begin
Cell := FWorkSheet.Cells[x + 1, y + 1];
SetExcelCellFont(Cell);
Cell.Value := StringGrid.Cells[y, x];
end;
end;procedure TOLEExcel.StringGridToExcel(const StringGrid: TStringGrid);
var
Cell: Variant;
begin
if not FExcelCreated then exit;
GetFixedCols(StringGrid, Cell);
GetFixedRows(StringGrid, Cell);
GetStringGridBody(StringGrid, Cell);
end;procedure TOLEExcel.SaveToExcel(const FileName: string);
begin
if not FExcelCreated then exit;
FWorkSheet.SaveAs(FileName);
// FExcel.Application.quit;
// FExcel:=Unassigned;
end;procedure Register;
begin
RegisterComponents('OleExcel', [TOLEExcel]);
end;procedure TOLEExcel.CloseExcel;
begin
try
Fexcel.ActiveWorkBook.Saved := True;
Fexcel.workBooks.close;
Fexcel.Quit;
Fexcel:=unassigned;
except
showmessage('Cannot Close Excel!');
end;
end;end.