作者:乔山办公网日期:
返回目录:excel表格制作
对 Excel 操作有多种,如果要显示 excel 的界面,进行与用户的交互,可以使用 OleContainer 组件。
可以 delphi 自带的 office 控件,进行excel 操作,这种方法的zhidao缺点在于,只能支持特定的版本,比如 excel2000。
比较通用的办法,是使用 CreateOleObject 函数来建立对 excel 的操作,即使用 ActiveX 来进行操作,优点是能适应版客户机上的多种权版本,缺点是编写过程中缺乏提示、出错调试麻烦。
如果只是想对 excel 文件进行读写,推荐使用 XLSReadWriteII 组件进行。
以下是使用ole对象调用excel的标准例程:
procedure TForm1.Button7Click(Sender: TObject);
var
i:integer;
v:variant;
sheet:variant;
begin
try
v:=createoleobject('excel.application');
v.visible:=true;
v.workbooks.add;
v.workbooks[1].worksheets[1].name:='知识库';
sheet:=v.workbooks[1].worksheets['知识库'];
sheet.cells[1,1]:='日期';
sheet.cells[1,2]:='分类';
sheet.cells[1,3]:='知识主题e68a84e8a2ade79fa5e98193364';
sheet.cells[1,4]:='索引关键字';
sheet.cells[1,5]:='提交';
sheet.cells[1,6]:='ext';
i:=2;
while not table1.eof do begin
sheet.cells[i,1]:=table1.Fieldbyname('日期').AsDateTime;
sheet.cells[i,2]:=''''+table1.Fieldbyname('分类').Asstring;
sheet.cells[i,3]:=''''+table1.Fieldbyname('知识主题').Asstring;
sheet.cells[i,4]:=''''+table1.Fieldbyname('索引关键字').Asstring;
sheet.cells[i,5]:=table1.Fieldbyname('提交').Asboolean; //string;
sheet.cells[i,6]:=''''+table1.Fieldbyname('ext').Asstring;
table1.MoveBy(1);
i:=i+1;
end;
v.visible:=true;
except
showmessage('fail init excel');
v.displayalerts:=false;
v.quit;
exit;
end;
end;
一般都要用try......except......end结构。
在上面窗口中依次添加 3个Servers组件组中 TExcelApplication1、TExcelWorksheet1、TExcelWorkbook控件------interface接口部分引用
......................................................................................
uses
OleServer, ComObj, ShellAPI, ExcelWorksheet1, ExcelXP, ExcelApplication1;
......................................................................................
实现代码如下:
procedure TForm1.btnOutputClick(Sender: TObject);
var
i: Integer;
New_worksheet: _Worksheet;
//filename1: string;
begin
try
excelapplication1.Connect;
except
//application.MessageBox('默认路径下''D:\Excel\''找不到''库单信息.xls'',请确认该文件是否存在!','',16);
end;
if DBgrid1.DataSource.DataSet.IsEmpty then
Exit
else
excelapplication1.Visible[0]:=true;
//创建一个Workbook,并添加一张新Worksheet
excelworkbook1.ConnectTo(excelapplication1.Workbooks.Add(EmptyParam,0));
New_worksheet :=excelworkbook1.Worksheets.add(EmptyParam,EmptyParam,EmptyParam,EmptyParam,0) as _Worksheet;
New_worksheet.Name :='学生信息表';
excelworksheet1.ConnectTo(New_worksheet);
//连接打开的空的EXCEL
//excelworkbook1.ConnectTo(excelapplication1.Workbooks.open(filename1,
//emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,
//emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,emptyparam,
//emptyparam,emptyparam,0));
//excelworkbook1.Activate;//激活
//excelworksheet1.ConnectTo(excelworkbook1.Worksheets.item[1] as _worksheet);
//excelworksheet1.Activate;
//excelworksheet1.Name:='学生信息';//表名
excelworksheet1.UsedRange[0].ClearContents;//清空
excelworksheet1.Cells.Font.Size:=12;//设置字体
excelworksheet1.Cells.Item[1,3]:='学生信息:7a686964616fe58685e5aeb9362';
DBgrid1.DataSource.DataSet.Open;
//把dbgrid1中查询到的数据发送到excel中,以便打印;
excelworksheet1.Cells.Item[2,1]:=DBgrid1.Fields[0].FieldName;
excelworksheet1.Cells.Item[2,2]:=DBgrid1.Fields[1].FieldName;
excelworksheet1.Cells.Item[2,3]:=DBgrid1.Fields[2].FieldName;
excelworksheet1.Cells.Item[2,4]:=DBgrid1.Fields[3].FieldName;
excelworksheet1.Cells.Item[2,5]:=DBgrid1.Fields[4].FieldName;
excelworksheet1.Cells.Item[2,6]:=DBgrid1.Fields[5].FieldName;
DBgrid1.DataSource.DataSet.First;
i:=3;
while not DBgrid1.DataSource.DataSet.Eof do
begin
excelworksheet1.Cells.Item[i,1]:=DBgrid1.Fields[0].AsString;
excelworksheet1.Cells.Item[i,2]:=DBgrid1.Fields[1].AsString;
excelworksheet1.Cells.Item[i,3]:=DBgrid1.Fields[2].AsString;
excelworksheet1.Cells.Item[i,4]:=DBgrid1.Fields[3].AsString;
excelworksheet1.Cells.Item[i,5]:=DBgrid1.Fields[4].AsString;
excelworksheet1.Cells.Item[i,6]:=DBgrid1.Fields[5].AsString;
DBgrid1.DataSource.DataSet.Next;
inc(i);
end;
DBgrid1.DataSource.DataSet.Close;
end;
不过,前提是你已经安装了ExcelXP相关组件......那就绝对OK啦
试编写抄代码百如下度:
procedure TForm1.Button2Click(Sender: TObject);
var oExcel: Variant;
begin
oExcel := CreateOleObject('Excel.Application');
oExcel.Visible := True;
oExcel.WorkBooks.Open('D:\Projects\test\test.xls');
oExcel.WorkSheets.add;
end;