如何嵌入EXCEL(100分)

我想把一数据库的数据转入EXCEL中，即生成一个EXCEL表，但总不能成功，
对EXCEL对象不太清楚，谁能教我？最好写出程式。

简单的代码如下：

var a: Variant;
begin
a:=CreateOleObject('excel.application');
a.workbooks.open('c:/配置.xls');
a.ActiveCell.Value := 'Monthly Totals';
a.save;
end;
excel的方法很多，你可安装excel的vb帮助，上面有详细的介绍，在delphi中用法
差不多。

在delphi中使用excel这个automation没有在vb中使用那么方便。delphi中有一个如何使用word的demo，与你的问题类似，我想可以对你有些帮助。

menxin 的方法是 OLE自动化，我觉得这种做法很好

不过其中的
var a: Variant;
最好改为
var a: OleVariant;

Do you know "TypeLib" in delphi? you can import excel object into you
project. so you treat excel as an object. it has property, function
easy to understand and program. there is a help file in MSOffice's office directory to let you understand excel object.
another way there is a unit in internet, it support excel file format. so you only use it's function to write you table content to a file directly. if you want the unit, pls mail me.

my mail: lzok@hotmail.com

procedure TForm1.Button1Click(Sender: TObject);
var
V: Variant ;
begin
V := CreateOleObject('Excel.Application');
v.Workbooks.Add; //新建Excel文件
v.Range['C5'].Select ; //选择单元
v.ActiveCell.FormulaR1C1 := 'dfad' ; //对所选择的单元赋值
//以下设置所选择单元的字体
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Name := 'Times New Roman' ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.FontStyle := '常规' ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Size := 16 ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Strikethrough := False ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Superscript := False;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Subscript := False ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.OutlineFont := False ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Shadow := False ;
//最后保存文件及关闭
v.ActiveWorkbook.SaveAs('c:/test.xls');
v.Workbooks.Close;

end;

procedure TForm1.Button1Click(Sender: TObject);
var
V: Variant ;
begin
V := CreateOleObject('Excel.Application');
v.Workbooks.Add;
v.Range['C5'].Select ;
v.ActiveCell.FormulaR1C1 := 'dfad' ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Name := 'Times New Roman' ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.FontStyle := '常规' ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Size := 16 ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Strikethrough := False ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Superscript := False;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Subscript := False ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.OutlineFont := False ;
v.ActiveCell.Characters(Start:=1, Length:=4).Font.Shadow := False ;

v.ActiveWorkbook.SaveAs('c:/test.xls');
v.Workbooks.Close;

end;

z_cd能否将Variant和OLEVariant的差异详述一下。

我发给你个例子!查收

用COM实现，基本方法好多书都有。我的经验是导出一个类型库，然后加入工程。
excel文档对象的方法和属性，就一目了然了。
具体方法：菜单：project|import type library|

你的问题解决了没有.

To menxin
No!我在Ole/Automation分类目录上贴出了我的问题，请你去看一看。今晚，你
何时离开？我这电压太低，只有0时左右才上来，能否多给点提示！当然自有报答！

我正在线,你还有什么问题?还在用d3吗?

就是你的那两个待答问题吗?140分?

Yea! 我的学生那好象有D4,但我想：D4解决不了Ole->Word的问题！你有何办法？

Yea! 40分的可暂缓一下，先看100分的！

下面是我的程序供你参考：（你可类推）,（你要会导进EXCEL_TLB,用project/import Type Libray /Add 选MSExcel8.olb)


unit Unit1xldb;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
ExtCtrls, DBCtrls, Db, DBTables, StdCtrls, Mask,Excel_TLB,Comobj, Grids,
DBGrids;

type
TForm1 = class(TForm)
DBEdit1: TDBEdit;
DBEdit2: TDBEdit;
DBEdit3: TDBEdit;
DBEdit4: TDBEdit;
DBEdit5: TDBEdit;
Name: TDBText;
Captial: TDBText;
Continent: TDBText;
Population: TDBText;
Area: TDBText;
DataSource1: TDataSource;
Table1: TTable;
DBNavigator1: TDBNavigator;
Button1: TButton;
DBGrid1: TDBGrid;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
IExcel:Excel_TLB.Application_;

public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
var
IRange:Excel_TLB.Range;
i,row:integer;
Bookmark:TBookmark;
begin
IExcel:=Excel_TLB.CoApplication_.create;
IExcel.Visible[0]:=True;
IExcel.workbooks.Add(Null,0);
///
// IExcel.MenuBars[xlworksheet].Menus[1].Menuitems[1].:=false;
for i:=2 to 8 do
IExcel.MenuBars[xlworksheet].Menus.enabled:=false;
for i:=1 to 4 do
IExcel.Toolbars.visible:=false;
for i:=1 to 3 do
// IExcel.Get_Application_.ShortcutMenus.Enabled:=false;
IExcel.Get_Application_.ShortcutMenus.delete;
IRange:=IExcel.ActiveCell;
for i:=0 to Table1.Fields.Count-1 do
begin
IRange.value:=Table1.Fields.Displaylabel;
IRange:=IRange.Next;
end;
Table1.DisableControls;
try
bookmark:=Table1.Getbookmark;
try
Table1.First;
row:=2;
while not Table1.Eof do
begin
IRange:=IExcel.Range['A'+inttostr(row),'A'+inttostr(row)];
for i:=0 to Table1.Fields.count-1 do
begin
IRange.Value:=Table1.Fields.Asstring;
IRange:=IRange.Next;
end;
Table1.Next;
Inc(row);
end
finally
Table1.GotoBookmark(Bookmark);
Table1.FreeBookmark(bookmark);
end;
finally
Table1.EnableControls;
end;
IRange:=IExcel.range['A1','E'+inttostr(row-1)];
// IRange.AutoFormat(1,NULL,NULL,NULL,NULL,NULL,NULL);

end;

end.


注意：你要在你的form 上放datasource,table以及一些其他component,连上你
的数据库和表，着我的做既可。

附：
object Form1: TForm1
Left = 191
Top = 161
Width = 576
Height = 375
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object Name: TDBText
Left = 40
Top = 72
Width = 65
Height = 17
end
object Captial: TDBText
Left = 40
Top = 120
Width = 65
Height = 17
end
object Continent: TDBText
Left = 32
Top = 176
Width = 65
Height = 17
end
object Population: TDBText
Left = 32
Top = 240
Width = 65
Height = 17
end
object Area: TDBText
Left = 32
Top = 280
Width = 65
Height = 17
end
object DBEdit1: TDBEdit
Left = 112
Top = 64
Width = 121
Height = 21
DataField = '项目'
DataSource = DataSource1
TabOrder = 0
end
object DBEdit2: TDBEdit
Left = 112
Top = 112
Width = 121
Height = 21
DataField = '序号'
DataSource = DataSource1
TabOrder = 1
end
object DBEdit3: TDBEdit
Left = 112
Top = 168
Width = 121
Height = 21
DataField = '重点'
DataSource = DataSource1
TabOrder = 2
end
object DBEdit4: TDBEdit
Left = 112
Top = 232
Width = 121
Height = 21
DataField = '重要性的认定'
DataSource = DataSource1
TabOrder = 3
end
object DBEdit5: TDBEdit
Left = 112
Top = 280
Width = 121
Height = 21
DataField = '会计制度及内部控制评价的'
DataSource = DataSource1
TabOrder = 4
end
object DBNavigator1: TDBNavigator
Left = 40
Top = 0
Width = 240
Height = 25
DataSource = DataSource1
TabOrder = 5
end
object Button1: TButton
Left = 376
Top = 0
Width = 75
Height = 25
Caption = 'Excel'
TabOrder = 6
OnClick = Button1Click
end
object DBGrid1: TDBGrid
Left = 240
Top = 112
Width = 320
Height = 193
DataSource = DataSource1
TabOrder = 7
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
end
object DataSource1: TDataSource
DataSet = Table1
Left = 408
Top = 56
end
object Table1: TTable
Active = True
DatabaseName = 'STANDARD1'
TableName = 'mydatabase.db'
Left = 368
Top = 56
end
end

我这有一个控件安装即可使用，你用 TABLETOEXCEL 或 querytoexcel
既可以将数据库导入excel中，使用如下:
VAR
EXCEL :TOLEEXCEL;
BEGIN
EXCEL:=TOLEEXCEL.CREATE(SELF);
excel.CreateExcelInstance;
excel.visible:=true;
tablename.disablecontrol;//加快速度
excel.tabletoexcel(tablename);//tablename is你的表名
tablename.enablecontrol;//恢复连接
end;
//
控件的代码如下：

unit OLEExcel;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
comobj, DBTables, Grids;
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;//add by David
procedure SetExcelCellFont( var Cell: Variant );
procedure SetExcelTitleFont( var Cell: Variant );
procedure GetTableColumnName( const Table: TTable; 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 QueryToExcel( const Query: TQuery );
procedure StringGridToExcel( const StringGrid: TStringGrid );
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;

implementation

constructor 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;


procedure TOLEExcel.SetVisible( DoShow: Boolean );
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.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 Register;
begin
RegisterComponents('Arm007', [TOLEExcel]);
end;

end.

多人接受答案了。