如何TDBGrid中的内容显示到EXCEL中

Question

如何TDBGrid中的内容显示到EXCEL中

Answer 1

TDBGrid中的内容导出到EXCEL中procedure Tfm1.Excel1Click(Sender: TObject);
var
   ExcelApplication1:TExcelApplication;
   ExcelWorksheet1:TExcelWorksheet;
   ExcelWorkbook1:TExcelWorkbook;
   i,j:integer;
   filename:string;
 begin
      filename:='预测表';
   try
    ExcelApplication1:=TExcelApplication.Create(Application);
    ExcelWorksheet1:=TExcelWorksheet.Create(Application);   
    ExcelWorkbook1:=TExcelWorkbook.Create(Application);
    ExcelApplication1.Connect;
   except
    Application.Messagebox('Excel没有安装!','Hello',MB_ICONERROR+mb_Ok);
    Abort;
   end;
   try
    ExcelApplication1.Workbooks.Add(EmptyParam,0);
    ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1]as _worksheet);
    dbgrid3.DataSource.DataSet.First;
    for j:=0 to dbgrid4.DataSource.DataSet.Fields.Count-2 do
     begin
      ExcelWorksheet1.Cells.item[3,j+1]:=dbgrid4.Columns[j].Title.Caption;//导出dbgrid中显示的字段标题
      //ExcelWorksheet1.Cells.item[3,j+1]:=dbgrid3.DataSource.DataSet.Fields[j].DisplayLabel;//导出数据库中字段名
      ExcelWorksheet1.Cells.item[3,j+1].font.size:='10';
     end;
       for i:=4 to dbgrid4.DataSource.DataSet.RecordCount + 3 do  //从excel的第4行到最后
        begin 
         for j:= 1 to dbgrid4.DataSource.DataSet.Fields.Count - 1 do  //从dbgrid4的第1列到最后
          begin 
           ExcelWorksheet1.Cells.item[i,j]:=dbgrid4.DataSource.DataSet.Fields[j].Asstring;
           ExcelWorksheet1.Cells.item[i,j].font.size:='10';
          end;   
          dbgrid4.DataSource.DataSet.Next;
         end;
          ExcelWorksheet1.Columns.AutoFit;   
          ExcelWorksheet1.Cells.item[1,2]:='预测表'+Formatdatetime('YYYYmmdd',Now);
           ExcelWorksheet1.Cells.Item[1,2].font.size:='14';
          savedialog1.FileName:='预测表'+Formatdatetime('YYYYmmdd',Now);
          savedialog1.InitialDir:=ExtractFilePath(Application.ExeName);
          saveDialog1.Filter:='*.xls';
          if SaveDialog1.Execute then
          begin
            ExcelWorksheet1.SaveAs(SaveDialog1.FileName);
            Application.Messagebox(pchar(filename+'数据成功导出'),'Hello',mb_Ok);
          end
          else
      finally
    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
    ExcelApplication1.Free;
    ExcelWorksheet1.Free;
    ExcelWorkbook1.Free;
  end;
end;

Answer 2

/////////////////////////////////////////////   
  利用剪贴板，速度很快！适合装有Excel的机器   
  USES   Clipbrd,ComObj;   
    
  procedure   TForm1.Button1Click(Sender:   TObject);   
  var   
      str:string;   
      i:Integer;   
      excelapp,sheet:Variant;   
  begin   
  //     lbl2.Caption:=DateTimeToStr(Now);   
      str:='';   
      dbgrd1.DataSource.DataSet.DisableControls;   
      for   i:=0   to   dbgrd1.DataSource.DataSet.FieldCount-1   do   
        str:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9);   
      str:=str+#13;   
      dbgrd1.DataSource.DataSet.First;   
      while   not(dbgrd1.DataSource.DataSet.eof)   do   begin   
          for   i:=0     to   dbgrd1.DataSource.DataSet.FieldCount-1   do   
            str:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9);   
          str:=str+#13;   
          dbgrd1.DataSource.DataSet.next;   
    
          lbl1.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo);   
          Application.ProcessMessages;   
        
        end;//end   while   
    
        dbgrd1.DataSource.DataSet.EnableControls;   
    
        clipboard.Clear;   
        Clipboard.Open;   
        Clipboard.AsText:=str;   
        Clipboard.Close;   
        excelapp:=createoleobject('excel.application');   
        excelapp.workbooks.add(1);   //   excelapp.workbooks.add(-4167);   
        sheet:=excelapp.workbooks[1].worksheets[1];   
        sheet.name:='sheet1';   
        sheet.paste;   
        Clipboard.Clear;   
  //       sheet.columns.font.Name:='宋体';   
  //       sheet.columns.font.size:=9;   
  //       sheet.Columns.AutoFit;   
        excelapp.visible:=true;   
  //       lbl3.Caption:=DateTimeToStr(Now);   
    
  end;   
    
  /////////////////////////////////////////////

Answer 3

////////////////////////////////////////////////   
  利用TStringList，速度很快！适合没有装Excel的机器   
  procedure   TForm1.Button1Click(Sender:   TObject);   
  var   
      s:TStringList;   
      str:string;   
      i:Integer;   
  begin   
  //     lbl1.Caption:=DateTimeToStr(Now);   
      str:='';   
      dbgrd1.DataSource.DataSet.DisableControls;   
      for   i:=0   to   dbgrd1.DataSource.DataSet.FieldCount-1   do   
          str:=str+dbgrd1.DataSource.DataSet.fields[i].DisplayLabel+char(9);   
      str:=str+#13;   
      dbgrd1.DataSource.DataSet.First;   
      while   not(dbgrd1.DataSource.DataSet.eof)   do   begin   
          for   i:=0     to   dbgrd1.DataSource.DataSet.FieldCount-1   do   
              str:=str+dbgrd1.DataSource.DataSet.Fields[i].AsString+char(9);   
    
              str:=str+#13;   
              dbgrd1.DataSource.DataSet.next;   
    
  //         lbl3.Caption:=IntToStr(dbgrd1.DataSource.DataSet.RecNo);   
  //         Application.ProcessMessages;   
    
        end;//end   while   
    
        dbgrd1.DataSource.DataSet.EnableControls;   
        s:=TStringList.Create;   
        s.Add(str);   
        s.SaveToFile('c:\temp.xls');//保存到c:\temp.xls   
        s.Free;   
  //       lbl2.Caption:=DateTimeToStr(Now);   
    
  end;   
  ////////////////////////////////////////////////

Answer 4

下面是我做的关于TDBGrid组件的应用现在想吧几位打代码加进来把TDBGrid中的内容导成Excel的
不知到几位高手能不能帮帮忙啊
unit Main;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, DBTables, Menus, StdCtrls, Buttons, Grids, DBGrids, ADODB,
  ExcelXP, OleServer;type
  TMainForm = class(TForm)
    DBGrid1: TDBGrid;
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    BitBtn2: TBitBtn;
    BitBtn4: TBitBtn;
    Label3: TLabel;
    BitBtn6: TBitBtn;
    BitBtn5: TBitBtn;
    BitBtn1: TBitBtn;
    BitBtn3: TBitBtn;
    ExcelApplication1: TExcelApplication;
    ExcelWorksheet1: TExcelWorksheet;
    ExcelWorkbook1: TExcelWorkbook;
    BitBtn7: TBitBtn;
    procedure BitBtn3Click(Sender: TObject);
    procedure BitBtn2Click(Sender: TObject);
    procedure BitBtn6Click(Sender: TObject);
    procedure BitBtn4Click(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
    procedure BitBtn5Click(Sender: TObject);
    procedure BitBtn7Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  MainForm: TMainForm;implementation{$R *.dfm}//刷新数据
procedure TMainForm.BitBtn1Click(Sender: TObject);
begin
   with ADOQuery1 do
   begin
      Close;
      SQL.Clear;
      SQL.Add('select id as 学号,userid as 学生姓名,username as 课程代号,');
      SQL.Add('logindate as 卷面成绩,logintime as 平时成绩,quittime as 实验参加次数,');
      SQL.Add('rights as 总实验次数,number as 综合成绩 from userlogin');
      Open;
   end;
end;//连接后台数据库
procedure TMainForm.BitBtn2Click(Sender: TObject);
begin
   MainForm.ADOConnection1.Connected:=True;//连接后台数据库
   if  MainForm.ADOConnection1.Connected=False then //如果连接失败
   begin
      MessageDlg('系统连接后台数据库失败。请'+#13+#10+
                ''+#13+#10+'检查数据库网络配置是否完好！',
                mtWarning,[mbYes],0);
      Application.Terminate;//应用程序退出
   end
  else
   begin
      MessageDlg('系统已成功连接后台数据库！',mtInformation,[mbYes],0);
   end;
   BitBtn2.Enabled:=False;//设置组件的使能状态
   BitBtn3.Enabled:=True;
end;//断开数据库连接
procedure TMainForm.BitBtn3Click(Sender: TObject);
begin
   ADOConnection1.Connected:=False;
   BitBtn2.Enabled:=True;
   BitBtn3.Enabled:=False;
end;//连接数据库时显示登陆提示
procedure TMainForm.BitBtn4Click(Sender: TObject);
begin
   ADOConnection1.LoginPrompt:=True;
   BitBtn4.Enabled:=False;
   BitBtn5.Enabled:=True;
end;//连接数据库时不显示登陆提示
procedure TMainForm.BitBtn5Click(Sender: TObject);
begin
   ADOConnection1.LoginPrompt:=False;
   BitBtn4.Enabled:=True;
   BitBtn5.Enabled:=False;
end;//退出
procedure TMainForm.BitBtn6Click(Sender: TObject);
begin
   Close;
end;procedure TMainForm.BitBtn7Click(Sender: TObject);
begin
ExcelApplication1.Connect;
ExcelApplication1.Visible[0]:=True;
ExcelApplication1.Caption := '应用程序调用 Microsoft Excel';
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks.Add(EmptyParam,0));
end;end.

Answer 5

procedure TPublic.CopyDbDataToExcel(aGrid: TDBGrid);
var
  m_eclApp, m_WorkBook: Variant;
  m_RowNow: Integer;
  i: Integer;
begin
  try
    m_eclApp := CreateOleObject('Excel.Application');
    m_WorkBook := CreateOleObject('Excel.sheet');
  except
    Application.Terminate;
  end;
  try
    m_WorkBook := m_eclApp.WorkBooks.Add;
    for i := 0 to aGrid.Columns.Count - 1 do
      m_eclApp.Cells(1, i + 1) := aGrid.Columns.Items[i].Title.Caption;
    m_eclApp.ActiveSheet.Columns[2].NumberFormatLocal := '@';
    m_eclApp.ActiveSheet.Columns[2].ColumnWidth :=18;
    aGrid.DataSource.DataSet.Last;
    aGrid.DataSource.DataSet.First;
    m_RowNow := 2;
    while not aGrid.DataSource.DataSet.Eof do
    begin
      for i := 0 to aGrid.Columns.Count - 1 do
        m_eclApp.Cells(m_RowNow, i + 1) := aGrid.Columns.Items[i].Field.AsString;
      Inc(m_RowNow);
      aGrid.DataSource.DataSet.Next;
    end;
    m_eclApp.ActiveSheet.Rows[1].Font.name := '宋体';
    m_eclApp.ActiveSheet.Rows[1].Font.Bold := True;
    m_eclApp.Visible := True;
  except
    Application.MessageBox('请检查是否正确安装MicroSoft Excel!',  '提示', MB_OK + MB_ICONWARNING);
    m_WorkBook.Close;
    m_eclApp.Quit;
  end;
end;

Answer 6

把excel当成一个数据库操作速度最快。

Answer 7

2楼的高手
for j:=0 to dbgrid4.DataSource.DataSet.Fields.Count-2 do
 for j:= 1 to dbgrid4.DataSource.DataSet.Fields.Count - 1 do  //从dbgrid4的第1列到最后
这样输出是不是会少一列啊