///////////// 下面这个是实现的函数procedure Tfmbaojiacbview.DbgridSaveToExcel(dbgrid:TDBgrid); var XlAPP:Variant; Sheet1:Variant; i,j:integer; curRow:integer;begin if dbgrid.DataSource.DataSet.RecordCount<1 then exit; //?建excel?象 try XlApp:=createoleobject('Excel.Application'); XLApp.Visible:=false; XLApp.Workbooks.Add(xlWBatWorkSheet); Sheet1 := XLApp.Workbooks[1].WorkSheets['sheet1']; Sheet1.Columns[1].NumberFormat:='@'; //////////設計某列為文本類型 Sheet1.Columns[2].NumberFormat:='@';// XLApp.Workbooks.Options.CheckSpellingAsYouType:= False; // XLApp.Workbooks.Options.CheckGrammarAsYouType:= False; except on e:exception do begin showmessage('excel程序出錯,無法完成此功能!'); exit; end; end; curRow:=0; for j:=0 to dbgrid.FieldCount-1 do begin sheet1.cells[1,curRow+1]:=dbgrid.Columns[j].Title.Caption; inc(curRow); end; //?理?? dbgrid.DataSource.DataSet.First; i:=2; while not dbgrid.DataSource.DataSet.Eof do begin //?理一行 curRow:=0; for j:=0 to dbgrid.Columns.Count-1 do begin Sheet1.cells[i,curRow+1]:=TRim(dbgrid.Fields[j].DisplayText) ; inc(curRow); end; i:=i+1; dbgrid.DataSource.DataSet.Next; end; XLApp.Visible:=true; end; //////////下面是调用上面的函数,非常简单procedure Tfmbaojiacbview.BitBtn3Click(Sender: TObject); begin DbgridSaveToExcel(DBgrid1); end;
var MyExcel: Variant; WorkBook: OleVariant; WorkSheet: OleVariant; i,j:integer; xlsfilename :string; Savedialog1 :TSaveDialog; begin if Application.MessageBox('确认导出到Excel?','提示',MB_ICONQUESTION+MB_YESNO)=mrno then Abort; SaveDialog1 :=TSaveDialog.create(Application); SaveDialog1.Filter := 'Excel文件(*.xls)|*.XLS'; if savedialog1.Execute then if savedialog1.FileName <>'' then begin xlsfilename :=savedialog1.FileName; try MyExcel:=CreateOleObject('Excel.Application'); MyExcel.Application.WorkBooks.Add; MyExcel.Caption:='将数据导入到EXCEL表中'; MyExcel.Application.Visible:=false; WorkBook:=MyExcel.Application.workbooks[1]; worksheet:=workbook.worksheets.item[1]; except Application.MessageBox('EXCEL不存在!','警告',MB_ICONERROR+MB_OK); Savedialog1.Free; workBook.Saved := True; WorkBook.close; MyExcel.Quit;//释放VARIANT变量 MyExcel:=Unassigned; end; i:=1; Try with DBGrideh1.DataSource.DataSet do begin Open; DisableControls; for j:=0 to DBGrideh1.Columns.Count-1 do begin if DBGrideh1.Columns[j].Visible=true then worksheet.cells[1,j+1]:=DBGrideh1.Columns[j].Title.Caption; end; First; while not Eof do begin inc(i); for j:=0 to DBGrideh1.Columns.Count-1 do begin if DBGrideh1.Columns[j].Visible=true then begin worksheet.cells[i,j+1].NumberFormatLocal :='@'; worksheet.cells[i,j+1]:=DBGrideh1.Columns[j].Field.AsString end; end; next; end; EnableControls; end; WorkBook.saveas(XlsFileName); Application.MessageBox('导出到Excel成功!','好消息',MB_ICONINFORMATION+MB_OK); MyExcel.Quit; MyExcel := Unassigned; Savedialog1.Free; except Application.MessageBox('导出到Excel失败!','好消息',MB_ICONWARNING+MB_OK); workBook.Saved:=True; WorkBook.close; MyExcel.Quit;//释放VARIANT变量 MyExcel:=Unassigned; Savedialog1.Free; end; end; END;
看來樓主還不是很會用GOOGLE。COM,我是來跟你說釣魚的方法的 到上面搜,一大把。。------------------------------------------------------------ procedure TForm1.SpeedButton5Click(Sender: TObject);var Excel,WrkBook,WrkSheet:olevariant;Begin try Excel := CreateOleObject(‘Excel.Application‘); except if Application.MessageBox(‘对不起,你的机器没有安装Microsoft Excel,是否继续导出?‘ + #13#13 + ‘导出后在您的机器上不能直接打开,必须安装Excel到机器上才能打开!‘, ‘注意‘, MB_OKCANCEL) = ID_no then Exit; end; if SaveDialog1.Execute then Begin FormMain.StatusBarMain.Panels[1].Text := ‘系统正在导出,请稍后......‘; WrkBook:=Excel.WorkBooks.Add; Row := 1; SheetCount:=1; while not Query1.Eof do Begin if Row=1 then for tmp := 0 to s_caption.Count - 1 do //插入加入标题: Excel.WorkSheets[SheetCount].Cells[Row,tmp+1].Value:=s_caption.Strings[tmp]; inc(Row); for tmp := 0 to Query1.FieldCount - 1 do Begin if Query1.Fields[tmp].FieldName=‘VIP_NO‘ then Excel.WorkSheets[SheetCount].cells[Row, Tmp + 1].NumberFormatLocal:= ‘@‘ ; Excel.WorkSheets[SheetCount].Cells[Row,Tmp+1].Value := Query1.Fields[tmp].AsString; End; if Row>50000 then Begin SheetCount:=SheetCount+1; Row:=0; if SheetCount>3 then Begin WrkSheet:=WrkBook.WorkSheets[WrkBook.WorkSheets.Count]; WrkBook.WorkSheets.Add(emptyparam,WrkSheet,1,$FFFFEFB9); End; End; Query1.Next; ProgressBar1.StepIt; End; Excel.Activeworkbook.saveas(SaveDialog1.FileName); WrkBook.close; Excel.quit ; Excel:=unassigned ; ShowMessage(‘系统已经导出,请到‘+SaveDialog1.FileName+‘里查看‘);end;
我自己的程序中 用到的 完全可用的 procedure TForm1.btninClick(Sender: TObject); const BeginRow = 3; BeginCol = 1; var Excel: OleVariant; iRow,iCol : integer; xlsFilename: string; begin if (trim(edit1.Text) = '') then begin MessageBox(GetActiveWindow(), '请正确选择相关路径!', '警告', MB_OK + MB_ICONWARNING); exit; end; xlsFilename := trim(edit1.Text); try Excel := CreateOLEObject('Excel.Application'); except Application.MessageBox('Excel没有安装!', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL); Exit; end; Excel.Visible := false; Excel.WorkBooks.Open(xlsFilename); try iRow := BeginRow; iCol := BeginCol; while trim(Excel.WorkSheets[2].Cells[iRow,iCol].value) <> '' do begin with ADOQuery1 do begin Append; Fields[0].AsString := trim(Excel.WorkSheets[2].Cells[iRow,iCol].value); Fields[1].AsString := trim(Excel.WorkSheets[2].Cells[iRow,iCol+1].value); Fields[2].Asstring := trim(Excel.WorkSheets[2].Cells[iRow,iCol+2].value); Fields[3].Asstring := trim(Excel.WorkSheets[2].Cells[iRow,iCol+3].value); Fields[4].AsString := trim(Excel.WorkSheets[2].Cells[iRow,iCol+4].value); iRow := iRow + 1; end; end; Excel.Quit; ADOQuery1.UpdateStatus ; except Application.MessageBox('导入数据出错!请检查文件的格式是否正确!', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL); Excel.Quit; end; MessageBox(GetActiveWindow(), '数据导入成功!', '警告', MB_OK + MB_ICONWARNING); end;
///////////////////////////////////////////// 利用剪贴板,速度很快!适合装有Excel的机器 USES Clipbrd,ComObj;procedure TForm1.Button1Click(Sender: TObject); var str:string; i:Integer; excelapp,sheet:Variant; begin // lbl2.Caption:=DateTimeToStr(Now); str:=''; dbgrd1.DataSource.DataSet.DisableControls; for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do str:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.First; while not(dbgrd1.DataSource.DataSet.eof) do begin for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do str:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.next; lbl1.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo); Application.ProcessMessages;
//////////////////////////////////////////////// 利用TStringList,速度很快!适合没有装Excel的机器 procedure TForm1.Button1Click(Sender: TObject); var s:TStringList; str:string; i:Integer; begin // lbl1.Caption:=DateTimeToStr(Now); str:=''; dbgrd1.DataSource.DataSet.DisableControls; for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do str:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.First; while not(dbgrd1.DataSource.DataSet.eof) do begin for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do str:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9); str:=str+#13; dbgrd1.DataSource.DataSet.next;// lbl3.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo); // Application.ProcessMessages; end;//end while dbgrd1.DataSource.DataSet.EnableControls; s:=TStringList.Create; s.Add(str); s.SaveToFile('c:\temp.xls');//保存到c:\temp.xls s.Free; // lbl2.Caption:=DateTimeToStr(Now);end; ////////////////////////////////////////////////
var
XlAPP:Variant;
Sheet1:Variant;
i,j:integer;
curRow:integer;begin
if dbgrid.DataSource.DataSet.RecordCount<1 then exit;
//?建excel?象
try
XlApp:=createoleobject('Excel.Application');
XLApp.Visible:=false;
XLApp.Workbooks.Add(xlWBatWorkSheet);
Sheet1 := XLApp.Workbooks[1].WorkSheets['sheet1']; Sheet1.Columns[1].NumberFormat:='@'; //////////設計某列為文本類型
Sheet1.Columns[2].NumberFormat:='@';// XLApp.Workbooks.Options.CheckSpellingAsYouType:= False;
// XLApp.Workbooks.Options.CheckGrammarAsYouType:= False;
except
on e:exception do
begin showmessage('excel程序出錯,無法完成此功能!');
exit;
end;
end; curRow:=0;
for j:=0 to dbgrid.FieldCount-1 do
begin sheet1.cells[1,curRow+1]:=dbgrid.Columns[j].Title.Caption;
inc(curRow);
end;
//?理?? dbgrid.DataSource.DataSet.First;
i:=2;
while not dbgrid.DataSource.DataSet.Eof do
begin
//?理一行
curRow:=0;
for j:=0 to dbgrid.Columns.Count-1 do
begin Sheet1.cells[i,curRow+1]:=TRim(dbgrid.Fields[j].DisplayText) ;
inc(curRow);
end;
i:=i+1;
dbgrid.DataSource.DataSet.Next;
end;
XLApp.Visible:=true;
end;
//////////下面是调用上面的函数,非常简单procedure Tfmbaojiacbview.BitBtn3Click(Sender: TObject);
begin
DbgridSaveToExcel(DBgrid1);
end;
MyExcel: Variant;
WorkBook: OleVariant;
WorkSheet: OleVariant;
i,j:integer;
xlsfilename :string;
Savedialog1 :TSaveDialog;
begin
if Application.MessageBox('确认导出到Excel?','提示',MB_ICONQUESTION+MB_YESNO)=mrno then
Abort;
SaveDialog1 :=TSaveDialog.create(Application);
SaveDialog1.Filter := 'Excel文件(*.xls)|*.XLS';
if savedialog1.Execute then
if savedialog1.FileName <>'' then
begin
xlsfilename :=savedialog1.FileName;
try
MyExcel:=CreateOleObject('Excel.Application');
MyExcel.Application.WorkBooks.Add;
MyExcel.Caption:='将数据导入到EXCEL表中';
MyExcel.Application.Visible:=false;
WorkBook:=MyExcel.Application.workbooks[1];
worksheet:=workbook.worksheets.item[1];
except
Application.MessageBox('EXCEL不存在!','警告',MB_ICONERROR+MB_OK);
Savedialog1.Free;
workBook.Saved := True;
WorkBook.close;
MyExcel.Quit;//释放VARIANT变量
MyExcel:=Unassigned;
end;
i:=1;
Try
with DBGrideh1.DataSource.DataSet do
begin
Open;
DisableControls;
for j:=0 to DBGrideh1.Columns.Count-1 do
begin
if DBGrideh1.Columns[j].Visible=true then
worksheet.cells[1,j+1]:=DBGrideh1.Columns[j].Title.Caption;
end;
First;
while not Eof do
begin
inc(i);
for j:=0 to DBGrideh1.Columns.Count-1 do
begin
if DBGrideh1.Columns[j].Visible=true then
begin
worksheet.cells[i,j+1].NumberFormatLocal :='@';
worksheet.cells[i,j+1]:=DBGrideh1.Columns[j].Field.AsString
end;
end;
next;
end;
EnableControls;
end;
WorkBook.saveas(XlsFileName);
Application.MessageBox('导出到Excel成功!','好消息',MB_ICONINFORMATION+MB_OK);
MyExcel.Quit;
MyExcel := Unassigned;
Savedialog1.Free;
except
Application.MessageBox('导出到Excel失败!','好消息',MB_ICONWARNING+MB_OK);
workBook.Saved:=True;
WorkBook.close;
MyExcel.Quit;//释放VARIANT变量
MyExcel:=Unassigned;
Savedialog1.Free;
end;
end;
END;
一 ) 使用动态创建的方法
首先创建 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)
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.Font.size:=6;
7) 给二维图加下标说明
achart.Chart.Axes(xlCategory, xlPrimary).HasTitle := True;
achart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text := ' 下标说明 ';
8) 给二维图加左标说明
achart.Chart.Axes(xlValue, xlPrimary).HasTitle := True;
achart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text := ' 左标说明 ';
9) 给二维图加右标说明
achart.Chart.Axes(xlValue, xlSecondary).HasTitle := True;
achart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text := ' 右标说明 ';
10) 改变二维图的显示区大小
achart.Chart.PlotArea.Left := 5;
achart.Chart.PlotArea.Width := 223;
achart.Chart.PlotArea.Height := 108;
11) 给二维图坐标轴加上说明
achart.chart.seriescollection[1].NAME:=' 坐标轴说明 ';
E-Mail: [email protected]
[email protected]
到上面搜,一大把。。------------------------------------------------------------
procedure TForm1.SpeedButton5Click(Sender: TObject);var Excel,WrkBook,WrkSheet:olevariant;Begin try Excel := CreateOleObject(‘Excel.Application‘); except if Application.MessageBox(‘对不起,你的机器没有安装Microsoft Excel,是否继续导出?‘ + #13#13 + ‘导出后在您的机器上不能直接打开,必须安装Excel到机器上才能打开!‘, ‘注意‘, MB_OKCANCEL) = ID_no then Exit; end; if SaveDialog1.Execute then Begin FormMain.StatusBarMain.Panels[1].Text := ‘系统正在导出,请稍后......‘; WrkBook:=Excel.WorkBooks.Add; Row := 1; SheetCount:=1; while not Query1.Eof do Begin if Row=1 then for tmp := 0 to s_caption.Count - 1 do //插入加入标题: Excel.WorkSheets[SheetCount].Cells[Row,tmp+1].Value:=s_caption.Strings[tmp]; inc(Row); for tmp := 0 to Query1.FieldCount - 1 do Begin if Query1.Fields[tmp].FieldName=‘VIP_NO‘ then Excel.WorkSheets[SheetCount].cells[Row, Tmp + 1].NumberFormatLocal:= ‘@‘ ; Excel.WorkSheets[SheetCount].Cells[Row,Tmp+1].Value := Query1.Fields[tmp].AsString; End; if Row>50000 then Begin SheetCount:=SheetCount+1; Row:=0; if SheetCount>3 then Begin WrkSheet:=WrkBook.WorkSheets[WrkBook.WorkSheets.Count]; WrkBook.WorkSheets.Add(emptyparam,WrkSheet,1,$FFFFEFB9); End; End; Query1.Next; ProgressBar1.StepIt; End; Excel.Activeworkbook.saveas(SaveDialog1.FileName); WrkBook.close; Excel.quit ; Excel:=unassigned ; ShowMessage(‘系统已经导出,请到‘+SaveDialog1.FileName+‘里查看‘);end;
完全可用的
procedure TForm1.btninClick(Sender: TObject);
const
BeginRow = 3; BeginCol = 1;
var
Excel: OleVariant;
iRow,iCol : integer;
xlsFilename: string;
begin
if (trim(edit1.Text) = '') then
begin
MessageBox(GetActiveWindow(), '请正确选择相关路径!', '警告', MB_OK +
MB_ICONWARNING);
exit;
end;
xlsFilename := trim(edit1.Text);
try
Excel := CreateOLEObject('Excel.Application');
except
Application.MessageBox('Excel没有安装!', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
Exit;
end;
Excel.Visible := false;
Excel.WorkBooks.Open(xlsFilename);
try
iRow := BeginRow;
iCol := BeginCol;
while trim(Excel.WorkSheets[2].Cells[iRow,iCol].value) <> '' do begin
with ADOQuery1 do begin
Append;
Fields[0].AsString := trim(Excel.WorkSheets[2].Cells[iRow,iCol].value);
Fields[1].AsString := trim(Excel.WorkSheets[2].Cells[iRow,iCol+1].value);
Fields[2].Asstring := trim(Excel.WorkSheets[2].Cells[iRow,iCol+2].value);
Fields[3].Asstring := trim(Excel.WorkSheets[2].Cells[iRow,iCol+3].value);
Fields[4].AsString := trim(Excel.WorkSheets[2].Cells[iRow,iCol+4].value);
iRow := iRow + 1;
end;
end;
Excel.Quit;
ADOQuery1.UpdateStatus ;
except
Application.MessageBox('导入数据出错!请检查文件的格式是否正确!', '提示信息', MB_OK+MB_ICONASTERISK+MB_DEFBUTTON1+MB_APPLMODAL);
Excel.Quit;
end;
MessageBox(GetActiveWindow(), '数据导入成功!', '警告', MB_OK +
MB_ICONWARNING);
end;
利用剪贴板,速度很快!适合装有Excel的机器
USES Clipbrd,ComObj;procedure TForm1.Button1Click(Sender: TObject);
var
str:string;
i:Integer;
excelapp,sheet:Variant;
begin
// lbl2.Caption:=DateTimeToStr(Now);
str:='';
dbgrd1.DataSource.DataSet.DisableControls;
for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do
str:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9);
str:=str+#13;
dbgrd1.DataSource.DataSet.First;
while not(dbgrd1.DataSource.DataSet.eof) do begin
for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do
str:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9);
str:=str+#13;
dbgrd1.DataSource.DataSet.next; lbl1.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo);
Application.ProcessMessages;
end;//end while dbgrd1.DataSource.DataSet.EnableControls; clipboard.Clear;
Clipboard.Open;
Clipboard.AsText:=str;
Clipboard.Close;
excelapp:=createoleobject('excel.application');
excelapp.workbooks.add(1); // excelapp.workbooks.add(-4167);
sheet:=excelapp.workbooks[1].worksheets[1];
sheet.name:='sheet1';
sheet.paste;
Clipboard.Clear;
// sheet.columns.font.Name:='宋体';
// sheet.columns.font.size:=9;
// sheet.Columns.AutoFit;
excelapp.visible:=true;
// lbl3.Caption:=DateTimeToStr(Now);end;/////////////////////////////////////////////
利用TStringList,速度很快!适合没有装Excel的机器
procedure TForm1.Button1Click(Sender: TObject);
var
s:TStringList;
str:string;
i:Integer;
begin
// lbl1.Caption:=DateTimeToStr(Now);
str:='';
dbgrd1.DataSource.DataSet.DisableControls;
for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do
str:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9);
str:=str+#13;
dbgrd1.DataSource.DataSet.First;
while not(dbgrd1.DataSource.DataSet.eof) do begin
for i:=0 to dbgrd1.DataSource.DataSet.FieldCount-1 do
str:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9); str:=str+#13;
dbgrd1.DataSource.DataSet.next;// lbl3.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo);
// Application.ProcessMessages; end;//end while dbgrd1.DataSource.DataSet.EnableControls;
s:=TStringList.Create;
s.Add(str);
s.SaveToFile('c:\temp.xls');//保存到c:\temp.xls
s.Free;
// lbl2.Caption:=DateTimeToStr(Now);end;
////////////////////////////////////////////////