分享
 
 
 

DBGrid使用全书(三)

王朝delphi·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

把DBGrid输出到Excel表格---方法一

(支持多Sheet){

功能描述:把DBGrid输出到Excel表格(支持多Sheet)

调用格式:CopyDbDataToExcel([DBGrid1, DBGrid2]);

}

procedure CopyDbDataToExcel(Args: array of const);

var

iCount, jCount: Integer;

XLApp: Variant;

Sheet: Variant;

I: Integer;

begin

Screen.Cursor := crHourGlass;

if not VarIsEmpty(XLApp) then

begin

XLApp.DisplayAlerts := False;

XLApp.Quit;

VarClear(XLApp);

end;

try

XLApp := CreateOleObject(‘Excel.Application‘);

except

Screen.Cursor := crDefault;

Exit;

end;

XLApp.WorkBooks.Add;

XLApp.SheetsInNewWorkbook := High(Args) + 1;

for I := Low(Args) to High(Args) do

begin

XLApp.WorkBooks[1].WorkSheets[I+1].Name := TDBGrid(Args[I].VObject).Name;

Sheet := XLApp.Workbooks[1].WorkSheets[TDBGrid(Args[I].VObject).Name];

if not TDBGrid(Args[I].VObject).DataSource.DataSet.Active then

begin

Screen.Cursor := crDefault;

Exit;

end;

TDBGrid(Args[I].VObject).DataSource.DataSet.first;

for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do

Sheet.Cells[1, iCount + 1] :=

TDBGrid(Args[I].VObject).Columns.Items[iCount].Title.Caption;

jCount := 1;

while not TDBGrid(Args[I].VObject).DataSource.DataSet.Eof do

begin

for iCount := 0 to TDBGrid(Args[I].VObject).Columns.Count - 1 do

Sheet.Cells[jCount + 1, iCount + 1] :=

TDBGrid(Args[I].VObject).Columns.Items[iCount].Field.AsString;

Inc(jCount);

TDBGrid(Args[I].VObject).DataSource.DataSet.Next;

end;

XlApp.Visible := True;

end;

Screen.Cursor := crDefault;

end;

dbgrid的數據導入到excel中---方法二

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;

DBGrid导入Excel---方法四

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[i].Title.Caption;

//设定列宽

if (not Columns[i].Visible)or(Columns[i].Field=nil) then

XL.ActiveWorkbook.ActiveSheet.Columns[i+1].ColumnWidth:=0

else if Columns[i].Width<MinColumnWidth then

XL.ActiveWorkbook.ActiveSheet.Columns[i+1].ColumnWidth:=MinColumnWidth div 5

else

XL.ActiveWorkbook.ActiveSheet.Columns[i+1].ColumnWidth:=Columns[i].Width div 5;

//设定列格式

if (Columns[i].Field<>nil) then begin

if Columns[i].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[i].Field<>nil) then

XL.ActiveWorkbook.ActiveSheet.cells[j,i+1].Value:=Columns[i].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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有