Delphi中DBGrid 应用全书(一).doc

在 Delphi 语言的数据库编程中，DBGrid 是显示数据的主要手段之一。但是 DBGrid 缺省的外观未免显得单调和缺乏创意。其实，我们完全可以在我们的程序中通过编程来达到美化DBGrid 外观的目的。通过编程，我们可以改变 DBGrid 的表头、网格、网格线的前景色和背景色，以及相关的字体的大小和风格。 以下的示例程序演示了对 DBGrid 各属性的设置，使 Delphi 显示的表格就像网页中的表格一样漂亮美观。 示例程序的运行： 在 Form1 上放置 DBGrid1、Query1、DataSource1 三个数据库组件，设置相关的属性，使 DBGrid1 能显示表中的数据。然后，在 DBGrid1 的 onDrawColumnCell 事件中键入以下代码，然后运行程序，就可以看到神奇的结果了。本代码在 Windows98、Delphi5.0 环境下调试通过。procedure TMainForm.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect; DataCol: Integer; Column: TColumn;State: TGridDrawState);var i :integer;begin if gdSelected in State then Exit;/定义表头的字体和背景颜色： for i :=0 to (Sender as TDBGrid).Columns.Count-1 do begin (Sender as TDBGrid).Columnsi.Title.Font.Name :=宋体; /字体 (Sender as TDBGrid).Columnsi.Title.Font.Size :=9; /字体大小 (Sender as TDBGrid).Columnsi.Title.Font.Color :=$000000ff; /字体颜色(红色) (Sender as TDBGrid).Columnsi.Title.Color :=$0000ff00; /背景色(绿色) end;/隔行改变网格背景色： if Query1.RecNo mod 2 = 0 then (Sender as TDBGrid).Canvas.Brush.Color := clInfoBk /定义背景颜色 else (Sender as TDBGrid).Canvas.Brush.Color := RGB(191, 255, 223); /定义背景颜色/定义网格线的颜色： DBGrid1.DefaultDrawColumnCell(Rect,DataCol,Column,State); with (Sender as TDBGrid).Canvas do /画 cell 的边框 begin Pen.Color := $00ff0000; /定义画笔颜色(蓝色) MoveTo(Rect.Left, Rect.Bottom); /画笔定位 LineTo(Rect.Right, Rect.Bottom); /画蓝色的横线 Pen.Color := $0000ff00; /定义画笔颜色(绿色) MoveTo(Rect.Right, Rect.Top); /画笔定位 LineTo(Rect.Right, Rect.Bottom); /画绿色的竖线 end;end;2.Delphi5 - 隔行改变DBGrid网格颜色 :在 Form1 上放置 DBGrid1、Query1、DataSource1 三个数据库组件，设置相关的属性，使 DBGrid1 能显示表中的数据。然后，在 DBGrid1 的 onDrawColumnCell 事件中键入以下代码，然后运行程序procedure TForm1.DBGrid1DrawColumnCell(Sender: TObject; const Rect: TRect;DataCol: Integer; Column: TColumn; State: TGridDrawState);var i:integer;begin if gdSelected in State then Exit; /隔行改变网格背景色： if adoQuery1.RecNo mod 2 = 0 then (Sender as TDBGrid).Canvas.Brush.Color := clinfobk /定义背景颜色 else (Sender as TDBGrid).Canvas.Brush.Color := RGB(191, 255, 223); /定义背景颜色/定义网格线的颜色： DBGrid1.DefaultDrawColumnCell(Rect,DataCol,Column,State); with (Sender as TDBGrid).Canvas do /画 cell 的边框 begin Pen.Color := $00ff0000; /定义画笔颜色(蓝色) MoveTo(Rect.Left, Rect.Bottom); /画笔定位 LineTo(Rect.Right, Rect.Bottom); /画蓝色的横线 Pen.Color := clbtnface; /定义画笔颜色(兰色) MoveTo(Rect.Right, Rect.Top); /画笔定位 LineTo(Rect.Right, Rect.Bottom); /画绿色 end;end;3. DBGrid指定列上显示DBComboBox设置DBGrid1的OnDrawDataCell事件如下：procedure TForm1.DBGrid1DrawDataCell(Sender: TObject; const Rect: TRect; Field: TField; State: TGridDrawState);begin if (gdFocused in State) then begin if (Field.FieldName = DBComboBox1.DataField ) then begin DBComboBox1.Left := Rect.Left + DBGrid1.Left; DBComboBox1.Top := Rect.Top + DBGrid1.top; DBComboBox1.Width := Rect.Right - Rect.Left; DBComboBox1.Height := Rect.Bottom - Rect.Top; DBComboBox1.Visible := True; end; end;end;2)、DBGrid指定单元格未获得焦点时不显示DBComboBox，设置DBGrid1的OnColExit事件如下：procedure TForm1.DBGrid1ColExit(Sender: TObject);begin If DBGrid1.SelectedField.FieldName = DBComboBox1.DataField then begin DBComboBox1.Visible := false; end;end;3)、当DBGrid指定列获得焦点时DrawDataCell事件只是绘制单元格，并显示DBComboBox，但是DBComboBox并没有获得焦点，数据的输入还是在单元格上进行。在DBGrid1的KeyPress事件中调用SendMessage这个 Windows API函数将数据输入传输到DBComboBox上，从而达到在DBComboBox上进行数据输入。因此还要设置KeyPress事件如下：procedure TForm1.DBGrid1KeyPress(Sender: TObject; var Key: Char);begin if (key chr(9) then begin if (DBGrid1.SelectedField.FieldName =DBComboBox1.DataField) then begin DBComboBox1.SetFocus; SendMessage(DBComboBox1.Handle，WM_Char，word(Key)，0); end; end;end;打印 TDBGrid内容-方法1procedure PrintDbGrid(DataSet:TDataSet;DbGrid:TDbGrid;Title:String);varPointX,PointY:integer;ScreenX:integer;i,lx,ly:integer;px1,py1,px2,py2:integer;RowPerPage,RowPrinted:integer;ScaleX:Real;THeight:integer;TitleWidth:integer;SumWidth:integer;PageCount:integer;SpaceX,SpaceY:integer;RowCount:integer;beginPointX:=Round(GetDeviceCaps(printer.Handle,LOGPIXELSX)/2.54);PointY:=Round(GetDeviceCaps(printer.Handle,LOGPIXELSY)/2.54);ScreenX:=Round(Screen.PixelsPerInch/2.54);ScaleX:=PointX/ScreenX;RowPrinted:=0;SumWidth:=0;printer.BeginDoc;With Printer.Canvas dobegin DataSet.DisableControls; DataSet.First ; THeight:=Round(TextHeight(我)*1.5);/设定每行高度为字符高的1.5倍 SpaceY:= Round(TextHeight(我)/4); SpaceX:=Round(TextWidth(我)/4); RowPerpage:=Round(printer.PageHeight-5*PointY)/THeight); /上下边缘各2厘米 ly:=2*PointY; PageCount:=0; while not DataSet.Eof do begin if (RowPrinted=RowPerPage) or (RowPrinted=0) then begin if RowPrinted0 then Printer.NewPage; RowPrinted:=0; PageCount:=PageCount+1; Font.Name:=宋体; Font.size:=16; Font.Style:=Font.Style+fsBold; lx:=Round(Printer.PageWidth-TextWidth(Title)/2); ly:=2*PointY; TextOut(lx,ly,Title); Font.Size:=11; Font.Style:=Font.Style-fsBold; lx:=Printer.PageWidth-5*PointX; ly:=Round(2*PointY+0.2*PointY); if RowPerPage*PageCountDataSet.RecordCount then RowCount:=DataSet.RecordCount else RowCount:=RowPerPage*PageCount; TextOut(lx,ly,第+IntToStr(RowPerPage*(PageCount-1)+1)+-+IntToStr(RowCount)+条，共+IntToStr(DataSet.RecordCount)+条); lx:=2*PointX; ly:=ly+THeight*2; py1:=ly-SpaceY; if RowCount=DataSet.RecordCount then py2:=py1+THeight*(RowCount-RowPerPage*(PageCount-1)+1) else py2:=py1+THeight*(RowPerPage+1); SumWidth:=lx; for i:=0 to DBGrid.Columns.Count-1 do begin px1:=SumWidth-SpaceX; px2:=SumWidth; MoveTo(px1,py1); LineTo(px2,py2); TitleWidth:=TextWidth(DBGrid.Columnsi.Title.Caption); lx:=Round(SumWidth+(DBGrid.Columnsi.width*scaleX-titleWidth)/2); TextOut(lx,ly,DBGrid.Columnsi.Title.Caption); SumWidth:=Round(SumWidth+DBGrid.Columnsi.width*scaleX)+SpaceX*2; end; px1:=SumWidth; /画最后一条竖线 px2:=SumWidth; MoveTo(px1,py1); LineTo(px2,py2); px1:=2*PointX; /画第一条横线 px2:=SumWidth; py1:=ly-SpaceY; py2:=ly-SpaceY; MoveTo(px1,py1); LineTo(px2,py2); py1:=py1+THeight; py2:=py2+THeight; MoveTo(px1,py1); LineTo(px2,py2); end; lx:=2*PointX; ly:=ly+THeight; px1:=lx; px2:=SumWidth; py1:=ly-SpaceY+THeight; py2:=ly-SpaceY+THeight; MoveTo(px1,py1); LineTo(px2,py2); for i:=0 to DBGrid.Columns.Count-1 do begin TextOut(lx,ly,DataSet.FieldByname(DBGrid.Columnsi.Fieldname).AsString); lx:=Round(lx+DBGrid.Columnsi.width*ScaleX+SpaceX*2); end; RowPrinted:=RowPrinted+1; DataSet.next; end; DataSet.first; DataSet.EnableControls;end;printer.EndDoc;end;把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.WorkBooks1.WorkSheetsI+1.Name := TDBGrid(ArgsI.VObject).Name; Sheet := XLApp.Workbooks1.WorkSheetsTDBGrid(ArgsI.VObject).Name; if not TDBGrid(ArgsI.VObject).DataSource.DataSet.Active then begin Screen.Cursor := crDefault; Exit; end; TDBGrid(ArgsI.VObject).DataSource.DataSet.first; for iCount := 0 to TDBGrid(ArgsI.VObject).Columns.Count - 1 do Sheet.Cells1, iCount + 1 := TDBGrid(ArgsI.VObject).Columns.ItemsiCount.Title.Caption; jCount := 1; while not TDBGrid(ArgsI.VObject).DataSource.DataSet.Eof do begin for iCount := 0 to TDBGrid(ArgsI.VObject).Columns.Count - 1 do Sheet.CellsjCount + 1, iCount + 1 := TDBGrid(ArgsI.VObject).Columns.ItemsiCount.Field.AsString; Inc(jCount); TDBGrid(ArgsI.VObject).DataSource.DataSet.Next; end; XlApp.Visible := True; end; Screen.Cursor := crDefault;end;dbgrid的數據導入到excel中方法二unit Unit1;interfaceuses 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 *.DFMprocedure 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.Visible0 := True; ExcelApplication1.Caption := Excel Application; ExcelApplication1.Workbooks.Add(Null, 0); ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks1); ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets1 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.Itemrow, column := DBGrid.DataSource.DataSet.fieldsi - 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);variCount, jCount: Integer;XLApp: Variant;Sheet: Variant;beginScreen.Cursor := crHourGlass;if not VarIsEmpty(XLApp) thenbeginXLApp.DisplayAlerts := False;XLApp.Quit;VarClear(XLApp);end;/通过ole创建Excel对象tryXLApp := CreateOleObject(Excel.Application);exceptScreen.Cursor := crDefault;Exit;end;XLApp.WorkBooks.AddXLWBatWorksheet;XLApp.WorkBooks1.WorkSheets1.Name := 测试工作薄;Sheet := XLApp.Workbooks1.WorkSheets测试工作薄;if not Target.DataSource.DataSet.Active thenbeginScreen.Cursor := crDefault;Exit;end;Target.DataSource.DataSet.first;for iCount := 0 to Target.Columns.Count - 1 dobeginSheet.cells1, iCount + 1 := Target.Columns.ItemsiCount.Title.Caption;end;jCount := 1;while not Target.DataSource.DataSet.Eof dobeginfor iCount := 0 to Target.Columns.Count - 1 dobeginSheet.cellsjCount + 1, iCount + 1 := Target.Columns.ItemsiCount.Field.AsString;end;Inc(jCount);Target.DataSource.DataSet.Next;end;XlApp.Visible := True;Screen.Cursor := crDefault;end;/调用procedure TForm2.SpeedButton5Click(Sender: TObject);begincopyDbDataToExcel(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中每栏最小宽度varXL: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.cells1,i+1.value:=Columnsi.Title.Caption; /设定列宽 if (not Columnsi.Visible)or(Columnsi.Field=nil) then XL.ActiveWorkbook.ActiveSheet.Columnsi+1.ColumnWidth:=0 else if Columnsi.WidthMinColumnWidth then XL.ActiveWorkbook.ActiveSheet.Columnsi+1.ColumnWidth:=MinColumnWidth div 5 else XL.ActiveWorkbook.ActiveSheet.Columnsi+1.ColumnWidth:=Columnsi.Width div 5; /设定列格式 if (Columnsi.Fieldnil) then begin if Columnsi.Field.DataType=ftString then XL.ActiveWorkbook.ActiveSheet.Columnsi+1.NumberFormatLocal:= else XL.ActiveWorkbook.ActiveSheet.Columnsi+1.NumberFormatLocal:=G/通用格式; end; end;/with XL.ActiveWorkbook.ActiveSheet.Rows1.HorizontalAlignment:=xlCenter; DataSource.DataSet.First; j:=1; while not DataSource.DataSet.eof do begin j:=j+1; XL.ActiveWorkbook.ActiveSheet.rowsj.select; for i:=0 to Columns.Count-1 do if (Columnsi.Fieldnil) then XL.ActiveWorkbook.ActiveSheet.cellsj,i+1.Value:=Columnsi.Field.AsString; DataSource.DataSet.Next; end;/while end;/with result:=true; EXCEPT result:=false; END;/TRYFINALLY dbgrid.datasource.dataset.EnableControls;END;/TRYend;/DBToExcelDbGrid控件的标题栏弹出菜单procedure TFrmOrderPost.DbgOrderPostMouseDown(Sender: TObject; Button: TMous