怎樣將dbgrid的數據導入到excel中?(100分)

  • 主题发起人 主题发起人 kingson
  • 开始时间 开始时间
K

kingson

Unregistered / Unconfirmed
GUEST, unregistred user!
怎麼將dbgrid中的數據導入到excel中
 
需要用到 comobj 单元,
本论坛上这方面成功的例子很多,搜索一下就是了
 
留下email发给你一个例子参考。
 
to linsb
我的e-mail是:hpj168@hotmail.com
 
和嵌入word 有区别么?
 
unit Unit1;

interface

uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, Grids, DBGrids, Db, DBTables, Excel97, OleServer, Word97;


type
TForm1 = class(TForm)
ExcelApplication1: TExcelApplication;
ExcelWorkbook1: TExcelWorkbook;
ExcelWorksheet1: TExcelWorksheet;
Table1: TTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Button1: TButton;
Button4: TButton;
WordApplication1: TWordApplication;
WordDocument1: TWordDocument;
procedure Button1Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.Button1Click(Sender: TObject);
var
i, row, column: integer;
begin
Try
ExcelApplication1.Connect;
Except
MessageDlg('Excel may not be installed',
mtError, [mbOk], 0);
Abort;
End;
ExcelApplication1.Visible[0] := True;
ExcelApplication1.Caption := 'Excel Application';
ExcelApplication1.Workbooks.Add(Null, 0);
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);
DBGrid.DataSource.DataSet.Open;
row := 1;
While Not (DBGrid.DataSource.DataSet.Eof) do
begin
column := 1;
for i := 1 to DBGrid.DataSource.DataSet.FieldCount do
begin
ExcelWorksheet1.Cells.Item[row, column] := DBGrid.DataSource.DataSet.fields[i - 1].AsString;
column := column + 1;
end;
DBGrid.DataSource.DataSet.Next;
row := row + 1;
end;
end;



procedure TForm1.Button4Click(Sender: TObject);
begin
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
end;

end.

 
一个将dbgrid导为excel文件的过程,需要引用单元comoby,一个ExcelApplication1控件:
procedure CopyDbDataToExcel(Target: TDbgrid);
var
iCount, jCount: Integer;
XLApp: Variant;
Sheet: Variant;
begin
Screen.Cursor := crHourGlass;
if not VarIsEmpty(XLApp) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
VarClear(XLApp);
end;
//通过ole创建Excel对象
try
XLApp := CreateOleObject('Excel.Application');
except
Screen.Cursor := crDefault;
Exit;
end;
XLApp.WorkBooks.Add[XLWBatWorksheet];
XLApp.WorkBooks[1].WorkSheets[1].Name := '测试工作薄';
Sheet := XLApp.Workbooks[1].WorkSheets['测试工作薄'];
if not Target.DataSource.DataSet.Active then
begin
Screen.Cursor := crDefault;
Exit;
end;
Target.DataSource.DataSet.first;

for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[1, iCount + 1] := Target.Columns.Items[iCount].Title.Caption;
end;
jCount := 1;
while not Target.DataSource.DataSet.Eof do
begin
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[jCount + 1, iCount + 1] := Target.Columns.Items[iCount].Field.AsString;
end;
Inc(jCount);
Target.DataSource.DataSet.Next;
end;
XlApp.Visible := True;
Screen.Cursor := crDefault;
end;

//调用
procedure TForm2.SpeedButton5Click(Sender: TObject);
begin
copyDbDataToExcel(dbgrid1);
end;
 
/// http://www.delphibbs.com/delphibbs/dispq.asp?lid=1557801

uses DBGrids,ComObj,db;

Function PDBGridExportToExcel(Dbgrid:tdbgrid;title:string):boolean;
const
{ XlSheetType }
xlChart = -4109;
xlDialogSheet = -4116;
xlExcel4IntlMacroSheet = 4;
xlExcel4MacroSheet = 3;
xlWorksheet = -4167;
{ XlWBATemplate }
xlWBATChart = -4109;
xlWBATExcel4IntlMacroSheet = 4;
xlWBATExcel4MacroSheet = 3;
xlWBATWorksheet = -4167;
{ HorizontalAlignment}
xlLeft=1;
xlCenter=-4108;
xlRight=-4152;
const
MinColumnWidth=8; //转入Excel中每栏最小宽度
var
XL:variant;
i,j:integer;
begin
result:=false;
if not assigned(dbgrid.DataSource) then exit;
if not assigned(dbgrid.DataSource.DataSet) then exit;
if not dbgrid.DataSource.DataSet.active then exit;

TRY
TRY
XL:=CreateOLEObject('Excel.Application');
XL.Visible := True;
XL.Workbooks.Add(xlWBatWorkSheet);
XL.ActiveWorkbook.ActiveSheet.Name:=title;

with dbgrid do
begin
dbgrid.DataSource.DataSet.DisableControls;
for i:=0 to Columns.Count-1 do
begin
XL.ActiveWorkbook.ActiveSheet.cells[1,i+1].value:=Columns.Title.Caption;

//设定列宽
if (not Columns.Visible)or(Columns.Field=nil) then
XL.ActiveWorkbook.ActiveSheet.Columns[i+1].ColumnWidth:=0
else if Columns.Width<MinColumnWidth then
XL.ActiveWorkbook.ActiveSheet.Columns[i+1].ColumnWidth:=MinColumnWidth div 5
else
XL.ActiveWorkbook.ActiveSheet.Columns[i+1].ColumnWidth:=Columns.Width div 5;

//设定列格式
if (Columns.Field<>nil) then begin
if Columns.Field.DataType=ftString then
XL.ActiveWorkbook.ActiveSheet.Columns[i+1].NumberFormatLocal:='@'
else
XL.ActiveWorkbook.ActiveSheet.Columns[i+1].NumberFormatLocal:='G/通用格式';
end;
end;//with
XL.ActiveWorkbook.ActiveSheet.Rows[1].HorizontalAlignment:=xlCenter;

DataSource.DataSet.First;
j:=1;
while not DataSource.DataSet.eof do
begin
j:=j+1;
XL.ActiveWorkbook.ActiveSheet.rows[j].select;
for i:=0 to Columns.Count-1 do
if (Columns.Field<>nil) then
XL.ActiveWorkbook.ActiveSheet.cells[j,i+1].Value:=Columns.Field.AsString;
DataSource.DataSet.Next;
end;//while
end;//with
result:=true;
EXCEPT
result:=false;
END;//TRY
FINALLY
dbgrid.datasource.dataset.EnableControls;
END;//TRY
end;//DBToExcel
 

Similar threads

S
回复
0
查看
3K
SUNSTONE的Delphi笔记
S
S
回复
0
查看
2K
SUNSTONE的Delphi笔记
S
I
回复
0
查看
644
import
I
后退
顶部