楼上的各位,不知道为什么大家都不喜欢用ADO呢? 如: Result := False; with ADOTable1 do begin {ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' + 'Data Source= D:\...;Extended Properties=Excel 8.0;' + 'Persist Security Info=False'; TableName := 'AAA#TXT'; Open; } if Active then Result := True; end;就这样就可以直接对其进行操作了。
我有一个函数!可直接用! ///// DBgrid内容导出为EXELE表格 ///// procedure ExportToExcel(Caption:String;DbGrid: TDBGrid; DataSet: TDataSet); var i,j,icell,FieldNum:integer; ExcelApp: variant; begin application.ProcessMessages; try ExcelApp:=createoleobject('Excel.application'); except messageDlg('请先安装MicroSoft Excel',mtError,[mbok],0); exit; end; ExcelApp.Visible := True; ExcelApp.Caption := Caption; //表标题 ExcelApp.WorkBooks.Add; ExcelApp.WorkSheets[1].Activate; ExcelApp.WorkSheets[1].name:=Caption; //窗体名 ExcelApp.ActiveSheet.Rows[1].Font.Bold:= True; //字体为粗体 ExcelApp.ActiveSheet.Rows[2].Font.Bold:= True; ExcelApp.ActiveSheet.Rows[1].Font.Size:= 18; ExcelApp.Columns[1].NumberFormatLocal:='@'; with DataSet do begin DisableControls; fieldNum := dbgrid.fieldCount; //列总数 ICell:=round(fieldnum/2); ExcelApp.Cells[1,ICell]:=Caption; //表名 for i:=1 to fieldNum do //表 begin ExcelApp.Cells[2,i]:=Fields[i-1].DisplayName; end; first; i:=3; while not eof do begin for j:=1 to fieldNum do begin ExcelApp.Cells[i,j]:=fields[j-1].AsString; end; inc(i); if (i mod 20)=0 then ExcelApp.Cells[i+10,1].Activate; next; end; ExcelApp.Cells[I+1,1]:=CEPS_TITLE; //表尾 ICell:=ICell+Round(ICell*2/3); ExcelApp.Cells[I+1,ICell]:='制表: '+g_strUser; //... EnableControls; end; end;
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, OleServer, Db, DbCtrls, Excel97, ComCtrls;type
TfrmExcel = class(TForm)
GroupBox1: TGroupBox;
rbCenter: TRadioButton;
rbEast: TRadioButton;
rbNorth: TRadioButton;
rbLabor: TRadioButton;
btnStart: TButton;
btnCancel: TButton;
comboMnth: TComboBox;
Label1: TLabel;
edtYear: TEdit;
prgExport: TProgressBar;
Label2: TLabel;
procedure btnStartClick(Sender: TObject);
procedure btnCancelClick(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure FormShow(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
frmExcel: TfrmExcel;implementationuses DataCtrls;{$R *.DFM}
var
xlsApp: TExcelApplication;function DbToXls(Ds : TDataSet; Y : string; M: string; xlsTitle: string) : boolean;
var
RangeE: Excel97.Range;
iniCol,iniRow : integer; //iniCol:等于ord('A'),对应A列
//iniRow:数据区的第一行的序号。
I, CurRow: Integer;
Book: TBookStr;
begin
xlsApp.Visible [0] := True;
xlsApp.Workbooks.Add (NULL, 0);
iniCol := ord('A');
iniRow := 1 ;
CurRow := iniRow; //先导表的标题,诸如“某某年几月度业绩评价”等等。。
RangeE := xlsApp.Range[chr(iniCol+1) + inttostr(iniRow),
chr(iniCol+1) + inttostr(iniRow)];
RangeE.Value := xlsTitle; with RangeE.Font do
begin
name := '楷体_GB2312';
Bold := true;
Size := 20;
end; //字段名:(第三行)
CurRow := CurRow + 1;
RangeE := xlsApp.Range[chr(iniCol) + inttostr(CurRow),chr(iniCol) + inttostr(CurRow)];; for I := 0 to ds.Fields.Count - 1 do
begin
RangeE.Value := ds.Fields [I].FieldName;
RangeE := RangeE.Next;
end; CurRow := CurRow + 1;
// add field data in following rows
// format the Data area:
RangeE := xlsApp.Range [chr(iniCol) + IntToStr (iniRow + 1),
chr(iniCol + ds.Fields.Count - 1) + IntToStr (CurRow + Ds.RecordCount-1)];
//单元格数据类型为“文本”
RangeE.NumberFormatLocal := '@';
//加边框线:
with RangeE.Borders[xlEdgeLeft] do //左边框
begin
LineStyle := xlContinuous;
Weight := xlThin;
ColorIndex := xlAutomatic;
end;
with RangeE.Borders[xlEdgeTop] do //上边框
begin
LineStyle := xlContinuous;
Weight := xlThin;
ColorIndex := xlAutomatic;
end;
with RangeE.Borders[xlEdgeRight] do //右边框
begin
LineStyle := xlContinuous;
Weight := xlThin;
ColorIndex := xlAutomatic;
end;
with RangeE.Borders[xlEdgeBottom] do //底边框
begin
LineStyle := xlContinuous;
Weight := xlThin;
ColorIndex := xlAutomatic;
end;
with RangeE.Borders[xlInsideVertical] do //内部垂直线
begin
LineStyle := xlContinuous;
Weight := xlThin;
ColorIndex := xlAutomatic;
end;
with RangeE.Borders[xlInsideHorizontal] do //内部平行线
begin
LineStyle := xlContinuous;
Weight := xlThin;
ColorIndex := xlAutomatic;
end; ds.DisableControls;
try
Book := ds.Book;
try
ds.First;
while not ds.EOF do
begin
RangeE := xlsApp.Range [chr(iniCol) + IntToStr (CurRow),
chr(iniCol) + IntToStr (CurRow)];
for I := 0 to ds.Fields.Count - 1 do
begin
RangeE.Value := ds.Fields [I].AsString;
RangeE := RangeE.Next;
end;
ds.Next;
Inc (CurRow);
end; finally
ds.Book := Book;
end;
finally
ds.EnableControls;
end; // format the section
// RangeE := xlsApp.Range ['A1', 'E' + IntToStr (Row - 1)];
// RangeE.AutoFormat (3, NULL, NULL, NULL, NULL, NULL, NULL); DbToXls := true;
//end;end;procedure TfrmExcel.btnStartClick(Sender: TObject);
var
rsType,CalMnth : integer;
SqlStr,tblTitle : string;
begin
if rbCenter.Checked then
begin
rsType := 1;
if frmExcel.Tag = 0 then
begin
tblTitle := '本部正式工 ' + edtYear.Text + '年' + ' ' + comboMnth.Text + ' 业绩评价';
end
else
begin
tblTitle := '本部钟点工 ' + edtYear.Text + '年' + ' ' + comboMnth.Text + ' 业绩评价';
end;
end;
if rbEast.Checked then
begin
rsType := 2;
if frmExcel.Tag = 0 then
begin
tblTitle := '东部正式工 ' + edtYear.Text + '年' + ' ' + comboMnth.Text + ' 业绩评价';
end
else
begin
tblTitle := '东部钟点工 ' + edtYear.Text + '年' + ' ' + comboMnth.Text + ' 业绩评价';
end;
end;
if rbNorth.Checked then
begin
rsType := 3;
if frmExcel.Tag = 0 then
begin
tblTitle := '市北正式工 ' + edtYear.Text + '年' + ' ' + comboMnth.Text + ' 业绩评价';
end
else
begin
tblTitle := '市北钟点工 ' + edtYear.Text + '年' + ' ' + comboMnth.Text + ' 业绩评价';
end;
end;
if rbLabor.Checked then
begin
rsType := 4;
tblTitle := '';
end; CalMnth := comboMnth.ItemIndex + 1;
with DataCtrls.dmDataCtrls do
begin SqlStr := 'exec spEvalGenResult ' +
'''' + inttostr(CalMnth) + '''' + ',' +
inttostr(rsType) + ',' +
inttostr(frmExcel.tag);
adocmdUpdateMain.CommandText := SqlSTr;
try
adocmdUpdateMain.Execute;
except
application.MessageBox('无法生成结果,请检查数据库是否正常连接','提示',Mb_ok);
exit;
end; SqlStr := 'select * from EvalResult ';
if adodsCommon.Active then
adodsCommon.Close; adodsCommon.CommandText := SqlStr;
try
adodsCommon.Open;
except
application.MessageBox('无法取得结果,请检查数据库是否正常连接','提示',Mb_ok);
exit;
end; if adodsCommon.RecordCount = 0 then
begin
application.MessageBox('没有找到记录','提示',Mb_ok);
exit;
end; if DbtoXls(adodsCommon,trim(edtYear.Text),comboMnth.text,tblTitle) then
//导出
begin
application.MessageBox('数据已成功导出','完成',Mb_Ok);
xlsApp.Disconnect;
exit;
end
else
begin
application.MessageBox('数据导出失败,可能是Excel版本兼容性或其他问题','完成',Mb_Ok);
xlsApp.Disconnect;
exit;
end; end;
end;procedure TfrmExcel.btnCancelClick(Sender: TObject);
begin
close;
end;procedure TfrmExcel.FormClose(Sender: TObject; var Action: TCloseAction);
var
i : integer;
begin// xlsApp.Disconnect;
xlsApp.Destroy;
end;procedure TfrmExcel.FormShow(Sender: TObject);
begin
xlsApp := TExcelApplication.Create(self);
end;end.
如:
Result := False;
with ADOTable1 do
begin
{ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;' +
'Data Source= D:\...;Extended Properties=Excel 8.0;' +
'Persist Security Info=False';
TableName := 'AAA#TXT';
Open; }
if Active then
Result := True;
end;就这样就可以直接对其进行操作了。
///// DBgrid内容导出为EXELE表格 /////
procedure ExportToExcel(Caption:String;DbGrid: TDBGrid; DataSet: TDataSet);
var
i,j,icell,FieldNum:integer;
ExcelApp: variant;
begin application.ProcessMessages;
try
ExcelApp:=createoleobject('Excel.application');
except
messageDlg('请先安装MicroSoft Excel',mtError,[mbok],0);
exit;
end;
ExcelApp.Visible := True;
ExcelApp.Caption := Caption; //表标题
ExcelApp.WorkBooks.Add;
ExcelApp.WorkSheets[1].Activate;
ExcelApp.WorkSheets[1].name:=Caption; //窗体名
ExcelApp.ActiveSheet.Rows[1].Font.Bold:= True; //字体为粗体
ExcelApp.ActiveSheet.Rows[2].Font.Bold:= True;
ExcelApp.ActiveSheet.Rows[1].Font.Size:= 18;
ExcelApp.Columns[1].NumberFormatLocal:='@'; with DataSet do
begin
DisableControls;
fieldNum := dbgrid.fieldCount; //列总数
ICell:=round(fieldnum/2);
ExcelApp.Cells[1,ICell]:=Caption; //表名
for i:=1 to fieldNum do //表
begin
ExcelApp.Cells[2,i]:=Fields[i-1].DisplayName;
end;
first;
i:=3;
while not eof do
begin
for j:=1 to fieldNum do
begin
ExcelApp.Cells[i,j]:=fields[j-1].AsString;
end;
inc(i);
if (i mod 20)=0 then
ExcelApp.Cells[i+10,1].Activate;
next;
end;
ExcelApp.Cells[I+1,1]:=CEPS_TITLE; //表尾
ICell:=ICell+Round(ICell*2/3);
ExcelApp.Cells[I+1,ICell]:='制表: '+g_strUser; //...
EnableControls;
end;
end;
uses ComObj;