收录日期:2019/06/20 01:17:49 时间:2016/06/01 15:43:41 标签:语言基础/算法/系统设计
我用查询语句查询出来的内容,我想把它导入Excel,请问如何实现?
给你个demo,建立odbc为qq,导出sqlserver2000northwind数据库中的orders表数据,测试通过
by jinjazz
----------
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls, ExcelXP, OleServer;

type
  TForm1 = class(TForm)
    Button1: TButton;
    ExcelApp: TExcelApplication;
    ExcelQueryTable: TExcelQueryTable;
    ExcelBook: TExcelWorkbook;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
    function ToExcelFile(var Filename,SheetName,WIndowCaption:OleVariant;SqlStr:String):Boolean;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation
     uses ComObj;
{$R *.dfm}

function TForm1.ToExcelFile(var Filename,SheetName,WindowCaption:OleVariant;SqlStr:String):Boolean;
var
NewTemplate,ItemIndex:olevariant;
CurSheet:_WorkSheet;
connectStr:olevariant;
begin
NewTemplate:=emptyParam;
try
try
ExcelApp.Connect;
except
MessageDlg('您可能没有安装EXCEL', mtError, [mbOk], 0);
Abort;
end;
ExcelApp.visible[0]:=TRUE;
ExcelApp.Caption:=WindowCaption;
ExcelApp.Workbooks.Add(Newtemplate,0);
ExcelBook.ConnectTo(ExcelApp.workBooks.Item[1]);
ExcelBook.Title[0]:=WIndowCaption;
ExcelBook.Activate;
CurSheet:=ExcelBook.Sheets[1] as _worksheet;
CurSheet.Name:=SheetName;
connectstr := 'ODBC;DSN=qq;';
CurSheet.QueryTables.Add(connectstr,CurSheet.range['a3','e3'],SqlStr);
ExcelQueryTable.ConnectTo(CurSheet.QueryTables.Item(1));
ExcelQueryTable.Refresh;
except
on E: Exception do
begin
ItemIndex:=FALSE;
ExcelBook.Close(ItemIndex);
ExcelApp.Disconnect;
ExcelApp.Quit;
Application.MessageBox('数据输出到EXCEL过程出错,请检查EXCEL版本是否一致','警告',mb_ok);
exit;
end;
end;
ExcelQueryTable.Disconnect;
ExcelBook.Disconnect;
ExcelApp.Disconnect;
Result:=TRUE;
end;
procedure TForm1.Button1Click(Sender: TObject);
var XlsApp,XlsSheet,WndCaption:OleVariant;
begin
   XlsApp:=OleVariant('c:\aa.xls');
   XlsSheet:=OleVariant('a');
   WndCaption:=OleVariant('test');
   ToExcelFile(XlsApp,XlsSheet,WndCaption,'select * from orders');

end;

end.

---------------------

ExcelApp.visible[0]:=false;就可以不打开excle窗口
加入ExcelApp.Save(XlsApp);自动保存
啊,我的天呐,这么麻烦啊,有没有简单点的,我只是用Delphi7做的程序,数据库用的是Access,在Delphi中用查询控件实现查询,有一个按钮为“导入Excel”,这个按钮的实现需要如上面那位朋友所说的那么麻烦吗?而且我的机子上也没有sqlserver2000,怎么弄啊,拜托,有没有简单点的啊。
//转贴个函数
function ExportToExcel(dbgrid:tdbgrid):boolean;
const
  xlNormal=-4143;
var
  i,j,k:integer;
  str,filename:string;
  excel:OleVariant;
  SavePlace: TBookmark;
  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.GetBookmark;
  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.GotoBookmark(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;
如果觉得麻烦
ExcelApplication、ExcelWorkbook、ExcelWorksheet三个组件可以帮你解决问题
具体用法不清楚可以网上查查

创建删除文件的功能实现(求助!) 在C#中,在windows窗体控件中添加了个windows media player 播放器控件,怎样可以选择哪一首旧播放哪一首?? .NET生成解决方案失败 枚举当前进程取得MD5值,磁盘IO读取数据太大,如何改进? c# 正则表达式,获得最外层括号中的字符串 她睁开眼,昨夜的醉意已经褪去。发现房子是新的,门口停的玛莎拉蒂是新的。而陌生男人已经系好爱马仕腰带正要开门而去。她突然有些忧伤,即脱口而出:我还不知道你的名字呢 菜鸟提问关于NFC代码的问题 list如何转为json格式?? 关于编写程序 win7第一次安装VMware workstation8出现vmware evmcisockets.msi failed 两句合成一句,求真相 怎么根据奇偶来添加列啊,如果是教授的ID是偶数保留教授电话,如果是奇数保留他的办公室号码。 程序员想涨工资最好的方式是不是就是跳槽啊! 怎么获取相机的相片 out of memory。。。。 win7第一次安装VMware workstation8出现vmware evmcisockets.msi failed win7第一次安装VMware workstation8出现vmware evmcisockets.msi failed webroot路径问题?? 好热· 数据结构学习 求REPLACE写法? 求编程初学者要学的内容 新手求助 box2d 碰撞过滤和碰撞检测 如果是 新手问题,VectorPush不进去 在VMware Workstation 8中安装Fedora 16后,Fedora无法访问互联网 SSH架构登录注册是出现java.lang.NullPointerException 求解啊! Run-Time,check failure#2-stack around the variable \'a\'was corrupted 請告訴我這函式作用是??