delphi 6中如何将DBGrid中显示的数据导出到Excel中?

Question

delphi 6中如何将DBGrid中显示的数据导出到Excel中?
还有：
怎么打开一个Excel模板？

Answer 1

建议你使用dbgrideh 非常好用，可以满足你的功能。

Answer 2

正好我有一个函数,共享
function F_DbGridSaveToExcel(as_dbgrid: TDBGridEh;
  as_dataset: TAdodataset): Integer;
var
    i:integer;
    li_row:integer;
    ExcelApplication1:tExcelApplication;
    ExcelWorkbook1:tExcelWorkbook;
    ExcelWorksheet1:tExcelWorksheet;
  
    tb_temp:TBook;
    img_show:TImage;
begin    result:=0;    Try
        ExcelApplication1:=tExcelApplication.Create(nil);
        ExcelWorkbook1:=tExcelWorkbook.Create(nil);
        ExcelWorksheet1:=tExcelWorksheet.Create(nil);
        ExcelApplication1.AutoConnect:=false;
       // ExcelApplication1.ConnectTo( nil);
        ExcelApplication1.Connect;//EXCEL应用程序
//  
    Except
        MessageDlg('Excel may not be installed',mtError, [mbOk], 0);
        Abort;
    End;
 
    ExcelApplication1.Visible[0]:=true;
   // ExcelApplication1.Caption:='Excel Application';    try
      {  ExcelApplication1.Workbooks.Open(as_FileName  ,
            EmptyParam,EmptyParam,EmptyParam,EmptyParam,
            EmptyParam,EmptyParam,EmptyParam,EmptyParam,
            EmptyParam,EmptyParam,EmptyParam,EmptyParam,0);
       }//        ExcelApplication1.Workbooks.Add(EmptyParam,0);
        //ExcelWorkbook1.Connectto( ExcelApplication1.Workbooks[1] ) ;        ExcelWorkbook1.Connectto( ExcelApplication1.Workbooks.Add(EmptyParam,0)  ) ;
       // ExcelWorkbook1.ConnectTo( as _workbook  );//ExcelWorkbook1与Eexcelapplication1建立连接
        ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与
        //ExcelWorksheet1.Cells.Item[1,1]:='1';     li_row:=1;  
     ExcelWorksheet1.Rows.Font.Name:='宋体';
     ExcelWorksheet1.Rows.Font.size:=9;
     ExcelWorksheet1.Rows.AutoFit;
     //ExcelWorksheet1.rows.NumberFormat:='@';//          ExcelWorksheet1.Select                     
//     ExcelWorksheet1.Range['b1','b'+inttostr(as_dataset.RecordCount)].NumberFormat:='@';     for i:=1 to      as_dbgrid.Columns.Count do
     begin        ExcelWorksheet1.Cells.Item[li_row,i]:=as_dbgrid.Columns[i-1].Title.Caption;
     end;
     ExcelWorksheet1.Range['a1','a100'].NumberFormat:='@';
     ExcelWorksheet1.Range['b1','b100'].NumberFormat:='@';     inc(li_row);
     tb_temp:=as_dataset.GetBook;
     as_dbgrid.Enabled:=false;
     as_dataset.First;    img_show:=TImage.Create(as_dbgrid.Parent);
    img_show.Parent:=as_dbgrid;
    img_show.Name:='img_showProgress';
    img_show.Visible:=true;
    img_show.Width:=300;
    img_show.Height:=20;
    img_show.Left:=round(  (as_dbgrid.Width-300)/2 );
    img_show.Top :=round(as_dbgrid.Height/3);
    img_show.BringToFront;
     while not as_dataset.Eof do
     begin
         for i:=1 to      as_dbgrid.Columns.Count do
         begin
            ExcelWorksheet1.Cells.Item[li_row,i]:=trim(as_dataset.fieldbyname(
                                        as_dbgrid.Columns[i-1].FieldName).asstring )  ;         end;
         inc(li_row);
         application.ProcessMessages;
          P_HRDrawImage( img_show ,
                        round(as_dataset.RecNo/as_dataset.RecordCount*img_show.Width),
                        inttostr( round(as_dataset.RecNo/as_dataset.RecordCount*100) )+'%'
                    );
         as_dataset.Next;
     end;     as_dataset.GotoBook(tb_temp);
     as_dataset.FreeBook(tb_temp);
     as_dbgrid.Enabled:=true;    ExcelApplication1.Visible[0]:=true;
    //ExcelWorksheet1.PrintPreview;
    ExcelWorksheet1.Disconnect;
    ExcelWorkbook1.Disconnect;
    ExcelApplication1.Disconnect;
    ExcelWorksheet1.Free;
    ExcelWorkbook1.Free;
    ExcelApplication1.Free;
    except
      on e:exception do begin
        as_dbgrid.Enabled:=true;
        ExcelApplication1.Visible[0]:=true;
        ExcelWorksheet1.Disconnect;
        ExcelWorkbook1.Disconnect;
        ExcelApplication1.Disconnect;
        ExcelWorksheet1.Free;
        ExcelWorkbook1.Free;
        ExcelApplication1.Free;        ShowMessage(e.Message);
        exit;
      end;
    end;    img_show.Free;
result:=1;
end;

Answer 3

首先dbgrid显示的数据肯定来自于一个query，或table。所以说就是将数据库逐条写入到excel不就可以了~~~

Answer 4

//注意：下面的方法必须包含 ComObj, Excel97 单元
  procedure ExportDBGridToExcel(DBGrid1: TDBGrid);
  var
    bm: TBook;
    col, row: Integer; 
    sline: String;
    mem: TMemo;
    ExcelApp: Variant;
  begin
    Screen.Cursor := crHourglass;
    DBGrid1.DataSource.DataSet.DisableControls;
    bm := DBGrid1.DataSource.DataSet.GetBook;
    DBGrid1.DataSource.DataSet.First;
  
    // create the Excel object      ExcelApp := CreateOleObject('Excel.Application');
      ExcelApp.WorkBooks.Add(xlWBatWorkSheet);
      ExcelApp.WorkBooks[1].WorkSheets[1].Name := 'Grid Data';
    // First we send the data to a memo 
    // works faster than doing it directly to Excel
    mem := TMemo.Create(nil);
    mem.Visible := false;
    mem.Parent :=form1;
    mem.Clear;
    sline := '';     // add the info for the column names 
    for col := 0 to DBGrid1.FieldCount-1 do
      sline := sline + DBGrid1.Fields[col].DisplayLabel + #9; 
    mem.Lines.Add(sline);
  
    // get the data into the memo
    for row := 0 to DBGrid1.DataSource.DataSet.RecordCount-1 do 
    begin
      sline := ''; 
      for col := 0 to DBGrid1.FieldCount-1 do
        sline := sline + DBGrid1.Fields[col].AsString + #9; 
      mem.Lines.Add(sline);
      DBGrid1.DataSource.DataSet.Next; 
    end;
  
    // we copy the data to the clipboard
    mem.SelectAll; 
    mem.CopyToClipboard;
    //send it to Excel      ExcelApp.Workbooks[1].WorkSheets['Grid Data'].Paste; 
      ExcelApp.Visible := true;
 
    FreeAndNil(mem);
  //  FreeAndNil(ExcelApp); 
    DBGrid1.DataSource.DataSet.GotoBook(bm);
    DBGrid1.DataSource.DataSet.FreeBook(bm); 
    DBGrid1.DataSource.DataSet.EnableControls;
    Screen.Cursor := crDefault; 
  end;

Answer 5

procedure Tform_DM.N21Click(Sender: TObject);
 var
  fn:string;
  i,j:integer;
  xls:OleVariant;
begin
 SaveDialog1.FileName:='产品记录表';
 if SaveDialog1.Execute then
    fn:=SaveDialog1.FileName;
  if trim(fn)='' then exit;
  if (DM_data.ADQ_data_nr.Active =false) or
     (DM_data.ADQ_data_nr.RecordCount =0) then
    begin
     MessageDlg(
        '导出出错！！！!',mtInformation,[mbOK],0);
      exit;
    end;
  try
    xls:=CreateOleObject('Excel.Application');  //调用 Excel      需要在单元加上ComObj;
  except
   // Msgbox(sMsgNotInstallExcel,sTitleError,1);
    exit;
  end;
  xls.WorkBooks.add;   //增加一个表
  xls.Visible:=true;   //可见
  xls.Caption:='调用 EXCEL 导出就业证信息表';
  //以下为设置EXCEL各列的宽度
  xls.Columns[1].ColumnWidth:=5;
  xls.Columns[2].ColumnWidth:=15;
  xls.Columns[3].ColumnWidth:=25;
  xls.Columns[4].ColumnWidth:=7;
  xls.Columns[5].ColumnWidth:=7;
  xls.Columns[6].ColumnWidth:=25;
  xls.Columns[7].ColumnWidth:=8;
  //xls.Columns[8].ColumnWidth:=15;
  xls.Cells(1,1):='[产品记录表]';
  //画加框线  1-左   2-右  3-顶  4-底  5-斜( \ )  6-斜( / )
  for j:=1 to 4 do
  xls.Range['A1:H1'].Borders[j].Weight:=2 ;
 // xls.Cells(1,3):='0000';
  xls.Cells(1,5):='数据导出日期：'+DateToStr(now);
  xls.Range['A1:g1'].Interior.ColorIndex := 37;  //37 为天蓝色
  //写入标题栏
  for i:=1 to DBGridEh1.FieldCount do
    begin
      xls.Cells[2,i].Value:=DBGridEh1.Columns[i-1].Title.Caption ;
    end;
  xls.Range['A2:g2'].Interior.ColorIndex := 15;  //15为灰色
  //画加框线
  for j:=1 to 4 do
    xls.Range['A2:g2'].Borders[j].Weight:=2 ;
  i:=3 ;  //将循环写入数据，从第3行开始写入数据
   with DM_data.ADQ_data_nr do
    begin
      first;  //第一条
      while not eof do
      begin
        xls.cells(i,1):=FieldByName(DBGridEh1.Columns[0].FieldName).AsString ;
        xls.cells(i,2):=FieldByName(DBGridEh1.Columns[1].FieldName).AsString ;
       xls.cells(i,3):=FieldByName(DBGridEh1.Columns[2].FieldName).AsString;
        xls.cells(i,4):=FieldByName(DBGridEh1.Columns[3].FieldName).AsString ;
        xls.cells(i,5):=FieldByName(DBGridEh1.Columns[4].FieldName).AsString ;
        xls.cells(i,6):=FieldByName(DBGridEh1.Columns[5].FieldName).AsString ;
        xls.cells(i,7):=FieldByName(DBGridEh1.Columns[6].FieldName).AsString ;
        //xls.cells(i,8):=FieldByName(DBGridEh2.Columns[7].FieldName).AsString ;
         //xls.cells(i,9):=FieldByName(DBGridEh2.Columns[8].FieldName).AsString ;
        //xls.cells(i,10):=FieldByName(DBGridEh1.Columns[9].FieldName).AsString ;
        //画加框线
        for j:=1 to 4 do
          xls.Range['A'+intToStr(i)+':H'+intToStr(i)].Borders[j].Weight:=2 ;
        inc(i);   //递增+1
        next;     //下一条数据
      end;
    end;
  //保存EXCEL表格
  if not xls.ActiveWorkBook.Saved then
         xls.ActiveWorkBook.SaveAs(fn);
  SetForegroundWindow(Application.Handle); //本语句是防止软件窗体被其它窗体覆盖
  //MsgBox(sMsgExportSucceed,sTitleHint,0);
//  xls.quit;   //退出EXCEL程序
end;