DBGrid+Excel如何实现？

Question

如何把DBGrid中的数据用Excel编辑打印呢？

Answer 1

你是说把dbgrid中的数据抽到excel中，然后再进行编辑和打印吗

Answer 2

对对对！就是这个意思！
因为我的dbgrid显示的字段太多了，用qreport打印太费劲了，所以我想调用excel，应该会方便一些吧。

Answer 3

太感谢了！！
我的信箱：[email protected]
谢谢你！

Answer 4

用我的函数吧
function ExportToExcel(dbgrid:tdbgrid):boolean;
const
  xlNormal=-4143;
var
  i,j,k:integer;
  str,filename:string;
  excel:OleVariant;
  SavePlace: TBook;
  savedialog:tsavedialog;
  ProgressBar1:TProgressBar;
begin
  result:=false;
  filename:='';
  if dbgrid.DataSource.DataSet.RecordCount>65536 then
     begin
       if application.messagebox('需要导出的数据过大，Excel最大只能容纳65536行,是否还要继续？','询问',mb_yesno+mb_iconquestion)=idno then
          exit;
     end;
  screen.Cursor:=crHourGlass;
  try
    excel:=CreateOleObject('Excel.Application');
    excel.workbooks.add;
  except
     screen.cursor:=crDefault;
    showmessage('无法调用Excel！');
    exit;
  end;
  savedialog:=tsavedialog.Create(nil);
  savedialog.Filter:='Excel文件(*.xls)|*.xls';
  if savedialog.Execute then
     begin
       if FileExists(savedialog.FileName) then
          try
            if application.messagebox('该文件已经存在，要覆盖吗？','询问',mb_yesno+mb_iconquestion)=idyes then
               DeleteFile(PChar(savedialog.FileName))
            else
               begin
                 Excel.Quit;
                 savedialog.free;
                  screen.cursor:=crDefault;
                 Exit;
               end;
          except
            Excel.Quit;
            savedialog.free;
             screen.cursor:=crDefault;
            Exit;
          end;
       filename:=savedialog.FileName;
     end;
  savedialog.free;
  if filename='' then
     begin
       result:=true;
       Excel.Quit;
       screen.cursor:=crDefault;
       exit;
     end;
  k:=0;
  for i:=0 to dbgrid.Columns.count-1 do
    begin
      if dbgrid.Columns.Items[i].Visible then
         begin
           //Excel.Columns[k+1].ColumnWidth:=dbgrid.Columns.Items[i].Title.Column.Width;
           excel.cells[1,k+1]:=dbgrid.Columns.Items[i].Title.Caption;
           inc(k);
         end;
    end;  dbgrid.DataSource.DataSet.DisableControls;
  saveplace:=dbgrid.DataSource.DataSet.GetBook;
  dbgrid.DataSource.dataset.First;
  i:=2;
  if dbgrid.DataSource.DataSet.recordcount>65536 then
     ProgressBar1:=ProgressBarform(65536)
  else
     ProgressBar1:=ProgressBarform(dbgrid.DataSource.DataSet.recordcount);
  while not dbgrid.DataSource.dataset.Eof do
    begin
      k:=0;
      for j:=0 to dbgrid.Columns.count-1 do
        begin
          if dbgrid.Columns.Items[j].Visible then
             begin
               excel.cells[i,k+1].NumberFormat:='@';
               if not dbgrid.DataSource.dataset.fieldbyname(dbgrid.Columns.Items[j].FieldName).isnull then
                  begin
                    str:=dbgrid.DataSource.dataset.fieldbyname(dbgrid.Columns.Items[j].FieldName).value;
                    Excel.Cells[i, k + 1] := Str;
                  end;
               inc(k);
             end
          else
             continue;
        end;
      if i=65536 then
         break;
      inc(i);
      ProgressBar1.StepBy(1);
      dbgrid.DataSource.dataset.next;
    end;
  progressbar1.Parent.Free;  dbgrid.DataSource.dataset.GotoBook(SavePlace);
  dbgrid.DataSource.dataset.EnableControls;  try
    if copy(FileName,length(FileName)-3,4)<>'.xls' then
       FileName:=FileName+'.xls';
    Excel.ActiveWorkbook.SaveAs(FileName, xlNormal, '', '', False, False);
  except
    Excel.Quit;
     screen.cursor:=crDefault;
    exit;
  end;
  Excel.Visible := true;
  screen.cursor:=crDefault;
  Result := true;
end;function ProgressBarform(max:integer):tProgressBar;
var
  ProgressBar1:tProgressBar;
  form:tform;
begin
  application.CreateForm(tform,form);
  form.Position:=poScreenCenter;
  form.BorderStyle:=bsnone;
  form.Height:=30;
  form.Width:=260;
  ProgressBar1:=tProgressBar.Create(form);
  ProgressBar1.Smooth:=true;
  ProgressBar1.Max:=max;
  ProgressBar1.Parent:=form;
  ProgressBar1.Height:=20;
  ProgressBar1.Width:=250;
  ProgressBar1.Left:=5;
  ProgressBar1.Top:=5;
  ProgressBar1.Step:=1;
  form.Show;
  result:=ProgressBar1;
end;

Answer 5

记得带上这些单元
uses
  Windows,Graphics,DB,Grids, DBGrids,StdCtrls,forms,Sysutils,classes,
  Controls,comobj,comctrls,Dialogs;

Answer 6

unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB,comobj, StdCtrls, Buttons,dbclient;
type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    BitBtn1: TBitBtn;
    procedure BitBtn1Click(Sender: TObject);
  private    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;
  //把数据集导入excelsheet
  function datasettoexcelsheet(dataset:Tadoquery;
                             fieldtagmax:integer;
                             //字段的tag值大于这个，就不导出到excel
                             sheet:olevariant):boolean;
  //实际使用函数内部调用datasettoexcelsheet
  function datasettoexcel(dataset:Tadoquery;               //数据集
                        fieldtagmax:integer;
                        visible:boolean;                   //是否让转换工作的excel可见
                        excelfielname:string=''):boolean;  //excel文件名implementation
function datasettoexcelsheet(dataset:Tadoquery;
                             fieldtagmax:integer;
                             sheet:olevariant):boolean;
var
  row,col,fieldindex:integer;
  bk:Tbook;
begin
  result:=false;
  if not dataset.active then
     begin
       showmessage('数据集没有激活！');
       exit;     //数据集没打开
     end;
  bk:=dataset.getbook;         //
  dataset.disablecontrols;
  //转换，通过循环，先转标题，后转内容
  sheet.activate;   ///*************
  try
    //列标题
    row:=1;
    col:=1;
    for fieldindex:=0 to dataset.fieldcount-1 do
        begin
        if dataset.fields[fieldindex].tag<=fieldtagmax then
           begin
           sheet.cells(row,col):=dataset.fields[fieldindex].DisplayLabel;
           //excell是一个二维表
           inc(col);
           end;
        end;        //表内容
        dataset.first;
        while not dataset.eof do
              begin
              row:=row+1;
              col:=1;
              for fieldindex:=0 to dataset.fieldcount -1 do
                  begin
                  if dataset.fields[fieldindex].tag<=fieldtagmax then
                     begin
                     sheet.cells(row,col):=dataset.fields[fieldindex].AsString ;
                     inc(col);
                     end;
                  end;
                  dataset.next;
              end;
              result:=true;
      //回到数据集原来的位置，恢复显示空间的同步显示
      finally
        dataset.GotoBook(bk);
        dataset.EnableControls ;
      end;
end;function datasettoexcel(dataset:Tadoquery;fieldtagmax:integer;visible:boolean;excelfielname:string=''):boolean;
var
  excelobj,workbook,sheet:olevariant;
  oldcursor:Tcursor;
  savedialog:Tsavedialog;
  excelfilename:STRING;
begin
  result:=false;
  if  not dataset.Active then
      exit;   //数据集合没打开就退出
  oldcursor:=screen.Cursor ;     //保存当前鼠标
  screen.Cursor:=crhourglass;
  //转换excel对象，如果失败，弹出提示
  try
    excelobj:=createoleobject('Excel.sheet' );
    excelobj.application.visible:=VISIBLE;             //让excel是否可见    workbook:=excelobj.application.workbooks.add;    sheet:=workbook.sheets[1];  //建立一个sheet对象  except
    showmessage('无法调用excel');
    screen.Cursor :=oldcursor;
    exit;
  end;
  //如果不可见，就要保存为文件，如果没有文件名就要弹出文件保存对话框，
  if (not visible) and (excelfielname='') then
      begin
      savedialog:=Tsavedialog.create(nil);
      savedialog.filter:='microsoft excel 文件|*.xls';
      savedialog.execute;
      updatewindow(getactivewindow);
      excelfilename:=savedialog.filEname;
      savedialog.Free ;
      end;
  //转换，excel这是是否可见
  if (visible or (excelfilename <>'' )) then
      result:=datasettoexceLsheet(dataset,fieldtagmax,sheet);  //如果不可见且转换成功就保存到文件中
  if ((not visible) and result ) then
     begin
     workbook.saveas(filename:=excelfilename);
     workbook.close;
     end;
     screen.Cursor:=oldcursor;
  end;
{$R *.dfm}procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  datasettoexcel(ADOQUERY1,1,false,'');
end;
end.

Answer 7

to wanghua00(小华) 请把你的程序(最好加源码)寄给我学习一下
关于Dbgrid导出到Execl的谢谢
我的email：[email protected]

Answer 8

http://www.playicq.com/dispdoc.php?t=19&id=1966

Answer 9

用我的最好了，只需一个参数dbgrid即可。

Answer 10

lyhold(让你飞)的只能用于ADO，而且如果数据集里的int类型的值比较大的话会出现内容丢失的现象，还有就是参数过多。

Answer 11

还有ProgressBar吧!
我添加完以后，运行成功了，可是速度极慢！
30k的表格用了将近一分钟才导出，知道为什么吗？？

Answer 12

优化一下这个循环体里面的内容就好了，但具体如何优化我就想不出来了，要不然我早就做优化了。
while not dbgrid.DataSource.dataset.Eof do