DBGrid导出EXCEL

[老杨_sz]

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs;

type
  TForm1 = class(TForm)
    lbl1: TLabel;
    edt1: TEdit;
    btn1: TButton;
    btn2: TButton;
    dbgrd1: TDBGrid;
    ds1: TDataSource;
    con1: TADOConnection;
    qry1: TADOQuery;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

end.

如何把查询出的数据，从DBGrid导出btn2到EXCEL中，求如上补充实例源码

[bdmh]

用delphi提供的server中的组件,或者创建原生对象,去逐个写入,资料网上很多

如果你闲麻烦,可以改用dxdbgrid组件,来显示数据,dxdbgrid可以直接导出到excel,省你很多事,而且导出样子还不错

[de410]

procedure Tbafm.Excel2Click(Sender: TObject);//dbgrid导出Excel
var
ExcelApp:Variant;
i,j:integer;
DlgSave:TsaveDialog;
filename:string;
begin
DlgSave:=TsaveDialog.Create(nil);
DlgSave.Filter:='*.xls|*.xls';
if DlgSave.Execute then
filename:=DlgSave.FileName;
begin
try
ExcelApp:=CreateOleObject('Excel.Application');
except
Application.Messagebox('Excel没有安装!','Hello',MB_ICONERROR+mb_Ok);
exit;
end;
try
dbgrid3.DataSource.DataSet.First;
ExcelApp.WorkBooks.Add;
with dbgrid3.DataSource.DataSet do
begin
dbgrid3.DataSource.DataSet.First;
dbgrid3.DataSource.DataSet.DisableControls;
for i:=0 to dbgrid3.Columns.Count-1 do
begin
ExcelApp.Cells.item[1,i+1]:=dbgrid3.Columns[i].Title.Caption;
end;
for j:=2 to RecordCount+1 do
begin
for i:=0 to FieldCount-1 do
begin
ExcelApp.WorkSheets[1].Cells[j,i+1].Value:=Fields[i].AsVariant;
end;
Next;
end;
end;
finally
ExcelApp.Columns.AutoFit;
ExcelApp.ActiveWorkBook.Saveas(filename);
ExcelApp.WorkBooks.close;
ExcelApp.Quit;
ExcelApp:=unassigned;
DlgSave.Destroy;
end;
end;
end;

[dinoalex]

可以用F1BOOK吖,也可以用cxExportGrid4Link单元里的ExportGrid4Toexcel

[阿三]

引用ComObj单元,窗体个adoconnection,一个adoquery控件

procedure TForm1.btn1Click(Sender: TObject);
var
  OleObjExcel,hWorkBooks,hWorkSheet :Variant;
  i,j:Integer;
  curname,ls_text:String;
begin
  OleObjExcel := CreateOleObject('Excel.Application');
  try
    Try
      hWorkBooks := OleObjExcel.Workbooks.Add;
    Except
      OleObjExcel.Quit;
      ShowMessage('运行Excel文件时出现异常，无法继续');
      Exit;
    End;
    hWorkSheet:= hWorkBooks.Worksheets[1];
    qry1.First;
    j:=1;
    while (not qry1.Eof) do
    begin
      for i:=0 to qry1.FieldCount - 1 do
      begin
        hWorkSheet.Cells[j,i+1].Value:=qry1.Fields[i].AsString;
      end;
      Inc(j);
      qry1.Next;
    end;
    hWorkSheet.SaveAs('c:\1.xls');
  finally
    OleObjExcel.Quit;
  end;
end;

[bdmh]

在C#中,数据导出到excel很方便,使用excel本身的数据查询功能,也是使用的微软接口,我没研究过,你可以对照着,看在delphi中是否可以使用
c#中需引用Office.dll,和Microsoft.Office.Interop.Excel.dll

            Application excel;
            _Workbook xBk;
            _Worksheet xSt;
            _QueryTable xQt;
            excel = new ApplicationClass();
            if (excel == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                return;
            } 
            xBk = excel.Workbooks.Add(true);
            xSt = (_Worksheet)xBk.ActiveSheet;
            string Conn = "ODBC;DRIVER=SQL Server;SERVER=" + host + ";UID=sa;PWD=58325245;DATABASE=CMSChina";
            xQt = xSt.QueryTables.Add(Conn, xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, 1]), sql);
            xQt.Name = "导出示例";
            xQt.FieldNames = true;
            xQt.RowNumbers = false;
            xQt.FillAdjacentFormulas = false;
            xQt.PreserveFormatting = false;
            xQt.BackgroundQuery = true;
            xQt.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells;
            xQt.AdjustColumnWidth = true;
            xQt.RefreshPeriod = 0;
            xQt.PreserveColumnInfo = true;
            xQt.Refresh(xQt.BackgroundQuery);
            excel.Visible = true;
        }

[macchen1224]

你好，先uses comobj，然後CreateOleObject('Excel.Application')，然後二個for即可，如果需要考慮到導入的時間，我再提供大量從clientdataset導入到excel的程式，但只限定純文字格式，謝謝。

[leetzh]

请教de410,阿三，
如何控制导出的excel的行高、列宽等

[de410]

ExcelApp.rows.AutoFit;//自动适应行高
ExcelApp.Columns.AutoFit;//自动适应列宽

[leetzh]

ExcelApp.rows.AutoFit;//自动适应行高
ExcelApp.Columns.AutoFit;//自动适应列宽

不可以自定义，设定一个值吗？

[de410]

　　指定列的宽度：ExcelApp.ActiveSheet.Columns[i].ColumnsWidth := 5;
　　指定行的高度：ExcelApp.ActiveSheet.Rows[i].RowHeight := 0.75;

[leetzh]

多谢 de410

运行至 ExcelApp.ActiveSheet.Columns[i].ColumnsWidth := 5;
提示:"Method 'columnwidth' not supported by automation object"

[de410]

[Quote=引用 11 楼 leetzh 的回复:]
多谢 de410

运行至 ExcelApp.ActiveSheet.Columns[i].ColumnsWidth := 5;
提示:"Method 'columnwidth' not supported by automation object"

[/Quote]

ExcelApp.ActiveSheet.Columns[i].ColumnWidth := 5;
不好意思我写错了，ColumnsWidth多了一个s,应为ColumnWidth

[leetzh]

用columns[i].width和columns[i].columnwidth

都会提示"不能设置类range的width（columnwidth）属性"

[wang1]

可以 采用DBGRIDEH 其中就有导出到EXCEL的函数

[apilove]

给你这个我收藏的

unit DBGridExportToExcel;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ExtCtrls, StdCtrls, ComCtrls, DB, IniFiles, Buttons, dbgrids, ADOX_TLB, ADODB;


type TScrollEvents = class
       BeforeScroll_Event: TDataSetNotifyEvent;
       AfterScroll_Event: TDataSetNotifyEvent;
       AutoCalcFields_Property: Boolean;
  end;

procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);


implementation


procedure DisableDependencies(DataSet: TDataSet; var ScrollEvents: TScrollEvents);
begin
     with DataSet do
          begin
               DisableControls;
               ScrollEvents := TScrollEvents.Create();
               with ScrollEvents do
                    begin
                         BeforeScroll_Event := BeforeScroll;
                         AfterScroll_Event := AfterScroll;
                         AutoCalcFields_Property := AutoCalcFields;
                         BeforeScroll := nil;
                         AfterScroll := nil;
                         AutoCalcFields := False;
                    end;
          end;
end;


procedure EnableDependencies(DataSet: TDataSet; ScrollEvents: TScrollEvents);
begin
     with DataSet do
          begin
               EnableControls;
               with ScrollEvents do
                    begin
                         BeforeScroll := BeforeScroll_Event;
                         AfterScroll := AfterScroll_Event;
                         AutoCalcFields := AutoCalcFields_Property;
                    end;
          end;
end;

//主要功能在下面实现

procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);
var
  cat: _Catalog;
  tbl: _Table;
  col: _Column;
  i: integer;
  ADOConnection: TADOConnection;
  ADOQuery: TADOQuery;
  ScrollEvents: TScrollEvents;
  SavePlace: TBookmark;
begin

  cat := CoCatalog.Create;
  cat.Set_ActiveConnection('Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0');

  tbl := CoTable.Create;
  tbl.Set_Name(SheetName);

  DBGrid.DataSource.DataSet.First;
  with DBGrid.Columns do
    begin
      for i := 0 to Count - 1 do
        if Items[i].Visible then
        begin
          col := nil;
          col := CoColumn.Create;
          with col do
            begin
              Set_Name(Items[i].Title.Caption);
              Set_Type_(adVarWChar);
            end;

          tbl.Columns.Append(col, adVarWChar, 20);
        end;
    end;

  cat.Tables.Append(tbl);

  col := nil;
  tbl := nil;
  cat := nil;

  //开始导出
  ADOConnection := TADOConnection.Create(nil);
  ADOConnection.LoginPrompt := False;
  ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';
  ADOQuery := TADOQuery.Create(nil);
  ADOQuery.Connection := ADOConnection;
  ADOQuery.SQL.Text := 'Select * from [' + SheetName + '$]';
  ADOQuery.Open;


  DisableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);
  SavePlace := DBGrid.DataSource.DataSet.GetBookmark;
  try
  with DBGrid.DataSource.DataSet do
    begin
      First;
      while not Eof do
        begin
          ADOQuery.Append;
          with DBGrid.Columns do
            begin
              ADOQuery.Edit;
              for i := 0 to Count - 1 do
                if Items[i].Visible then
                  begin
                    ADOQuery.FieldByName(Items[i].Title.Caption).AsString := FieldByName(Items[i].FieldName).AsString;
                  end;
              ADOQuery.Post;
            end;
          Next;
        end;
    end;

  finally
  DBGrid.DataSource.DataSet.GotoBookmark(SavePlace);
  DBGrid.DataSource.DataSet.FreeBookmark(SavePlace);
  EnableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);

  ADOQuery.Close;
  ADOConnection.Close;

  ADOQuery.Free;
  ADOConnection.Free;

  end;

end;

end.

[apilove]

在没有安装excel的机器上可以用，非常好