有一个从DBGRID里到EXCEL的。 procedure TLogQueryForm.SaveAsExcel(FileName:string); var ExcelApp,WorkBook: Variant; i,j,k:integer; //i列No,j行No,k可见列 s:string; SavePlace: TBook; Range :Variant; ProgressThread:THSProgressThread; CurRecCount:integer; begin try ExcelApp := CreateOleObject( 'Excel.Application' ); WorkBook := CreateOleObject('Excel.Sheet'); Except Exception.Create('对不起,您的机器上没有安装Microsoft Excel,该文件不能生成!'); Exit; End; //try...Except if FileExists(FileName) then DeleteFile(FileName); SavePlace:=nil; try SavePlace := QryLog.GetBook; QryLog.DisableControls; QryLog.First; ProgressThread :=THSProgressThread.Create(Format('正在导出到%s文档(共%d条)...',['Excel',QryLog.RecordCount]),'导出进度',QryLog.RecordCount,1); try ExcelApp.Visible := false; ExcelApp.Caption := '导出文件'; WorkBook := ExcelApp.WorkBooks.Add; ExcelApp.ActiveSheet.Name := '系统操作日志'; //导出标题... k:=0; for i := 0 to dbgLog.Columns.Count-1 do begin if not dbgLog.Columns[i].Visible then Continue; ExcelApp.Cells(1,k+1) := dbgLog.Columns[i].Title.Caption; if dbgLog.Columns[i].Field.DataType = ftString then begin ExcelApp.ActiveSheet.Columns[k+1].numberformatlocal:= '@'; ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth:= Max(dbgLog.Columns[i].Field.Size,dbgLog.Columns[i].Width/8); { if Assigned(DBGrid.Columns[i].Field.OnGetText) then ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth := 30 //需要代码转名称字段默认长度30 else ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth := DBGrid.Columns[i].Field.Size; if Cardinal(DBGrid.Columns[i].Field.Size)<strlen(pchar(DBGrid.Columns[i].Title.Caption)) then ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth := strlen(pchar(DBGrid.Columns[i].Title.Caption)); } end; k:=k+1; end; //导出每行数据... j:=2; while not QryLog.Eof do begin k:=0; for i:=0 to dbgLog.Columns.Count-1 do begin if not dbgLog.Columns[i].Visible then Continue; s := dbgLog.Columns[i].Field.DisplayText; ExcelApp.Cells(j,k+1) := s; k:=k+1; end;//i j:=j+1; QryLog.Next; if ProgressThread.Terminated then Exit; ProgressThread.SetPosition(j); // if Assigned(FOnSaveToFile) then FOnSaveToFile(Index,FileName,QuyRecordCount,j); end; //标题栏字体,背景色 ExcelApp.ActiveSheet.Rows[1].Font.Name := '宋体'; ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlack; Range := ExcelApp.ActiveSheet.Range['A'+'1'+':'+DecTo26(k-1)+'1']; Range.interior.Color := clSilver; //自动加外边框 Range := ExcelApp.ActiveSheet.Range['A'+'1'+':'+DecTo26(k-1)+inttostr(j-1)]; Range.Borders.LineStyle := 1; Range.WrapText:=True; Range.VerticalAlignMent:= 1; Range.HorizontalAlignment:=1; //保存文件 WorkBook.Saveas(FileName); except Application.MessageBox(PChar('生成Excel文件【'+FileName+'】出错!'),'提示',MB_OK+MB_ICONINFORMATION); end; finally ProgressThread.Free; QryLog.EnableControls; QryLog.GotoBook(SavePlace); QryLog.FreeBook(SavePlace); WorkBook.Close; ExcelApp.Quit; end; end;
procedure TLogQueryForm.SaveAsExcel(FileName:string);
var
ExcelApp,WorkBook: Variant;
i,j,k:integer; //i列No,j行No,k可见列
s:string;
SavePlace: TBook;
Range :Variant;
ProgressThread:THSProgressThread;
CurRecCount:integer;
begin
try
ExcelApp := CreateOleObject( 'Excel.Application' );
WorkBook := CreateOleObject('Excel.Sheet');
Except
Exception.Create('对不起,您的机器上没有安装Microsoft Excel,该文件不能生成!');
Exit;
End; //try...Except if FileExists(FileName) then
DeleteFile(FileName); SavePlace:=nil;
try
SavePlace := QryLog.GetBook;
QryLog.DisableControls;
QryLog.First;
ProgressThread :=THSProgressThread.Create(Format('正在导出到%s文档(共%d条)...',['Excel',QryLog.RecordCount]),'导出进度',QryLog.RecordCount,1);
try
ExcelApp.Visible := false;
ExcelApp.Caption := '导出文件';
WorkBook := ExcelApp.WorkBooks.Add; ExcelApp.ActiveSheet.Name := '系统操作日志'; //导出标题...
k:=0;
for i := 0 to dbgLog.Columns.Count-1 do
begin
if not dbgLog.Columns[i].Visible then Continue;
ExcelApp.Cells(1,k+1) := dbgLog.Columns[i].Title.Caption;
if dbgLog.Columns[i].Field.DataType = ftString then
begin
ExcelApp.ActiveSheet.Columns[k+1].numberformatlocal:= '@';
ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth:= Max(dbgLog.Columns[i].Field.Size,dbgLog.Columns[i].Width/8);
{
if Assigned(DBGrid.Columns[i].Field.OnGetText) then
ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth := 30 //需要代码转名称字段默认长度30
else
ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth := DBGrid.Columns[i].Field.Size;
if Cardinal(DBGrid.Columns[i].Field.Size)<strlen(pchar(DBGrid.Columns[i].Title.Caption)) then
ExcelApp.ActiveSheet.Columns[k+1].ColumnWidth := strlen(pchar(DBGrid.Columns[i].Title.Caption));
}
end;
k:=k+1;
end; //导出每行数据...
j:=2;
while not QryLog.Eof do
begin
k:=0;
for i:=0 to dbgLog.Columns.Count-1 do
begin
if not dbgLog.Columns[i].Visible then Continue;
s := dbgLog.Columns[i].Field.DisplayText;
ExcelApp.Cells(j,k+1) := s;
k:=k+1;
end;//i
j:=j+1;
QryLog.Next; if ProgressThread.Terminated then Exit;
ProgressThread.SetPosition(j);
// if Assigned(FOnSaveToFile) then FOnSaveToFile(Index,FileName,QuyRecordCount,j);
end; //标题栏字体,背景色
ExcelApp.ActiveSheet.Rows[1].Font.Name := '宋体';
ExcelApp.ActiveSheet.Rows[1].Font.Color := clBlack; Range := ExcelApp.ActiveSheet.Range['A'+'1'+':'+DecTo26(k-1)+'1'];
Range.interior.Color := clSilver; //自动加外边框
Range := ExcelApp.ActiveSheet.Range['A'+'1'+':'+DecTo26(k-1)+inttostr(j-1)];
Range.Borders.LineStyle := 1;
Range.WrapText:=True;
Range.VerticalAlignMent:= 1;
Range.HorizontalAlignment:=1; //保存文件
WorkBook.Saveas(FileName);
except
Application.MessageBox(PChar('生成Excel文件【'+FileName+'】出错!'),'提示',MB_OK+MB_ICONINFORMATION);
end;
finally
ProgressThread.Free;
QryLog.EnableControls;
QryLog.GotoBook(SavePlace);
QryLog.FreeBook(SavePlace);
WorkBook.Close;
ExcelApp.Quit;
end;
end;
2、针对Excel文件创建一个ODBC连接,在Delphi中使用ADO或BDE连接到这个ODBC数据源。
3、读入文件,写入到连接中。
procedure TFormGzqkHZ.DataToExcel(aPath: string);
var
MySQL,ConnStr: string;
begin
ConnStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=' + aPath + ';Persist Security Info=False';
MySQL := 'SELECT * INTO [SHEET1] FROM TBLGZFFQKLS IN [ODBC]' + ' [ODBC;Driver=SQL Server;UID=sa;PWD=;Server='+ frmMain.IpCode +';DataBase=SalaryAuidit;]';
try
ADOConnExcel.Connected := False;
ADOConnExcel.ConnectionString := ConnStr;
ADOConnExcel.Connected := True;
ADOConnExcel.Execute(MySQL);
Application.MessageBox('导出EXCEL成功','信息提示!',mb_ok+mb_iconinformation);
ADOConnExcel.Connected := False;
except
Application.MessageBox('导出EXCEL失败','错误!',mb_ok+mb_iconStop);
ADOConnExcel.Connected := False;
end;
end;
2。把*.txt改为*.xls就好了