如何导出到Excel文件中？

Question

DELPHI ，SQL SERVER后台，如何将数据表导出到EXCEL文件中？谢谢

Answer 1

参考
procedure TAccountThread.Execute;
var i,j:integer;
     v,range:variant;
begin
  { Place thread code here }
   FreeOnTerminate:=True;
   if varIsEmpty(v) then
     v:=createOleObject('excel.Application');
     v.workBooks.Add;
      v.WorkBooks[1].workSheets[1].name:='银行帐号表';
//把银行帐号表的数据导出到Excel表中
      range:=v.WorkBooks[1].WorkSheets['银行帐号表'].Range['A1:D1'];
      range.merge;
      v.cells[1,1]:='恩平市人民医院职工工资帐号表';
      Dmodule.AccountADOTable.First;
      v.cells[2,1]:='帐号';
      v.cells[2,2]:='姓名';
      v.cells[2,3]:='所属部门';
      v.cells[2,4]:='实发工资';
      i:=3;j:=1;
      while not Dmodule.AccountADOTable.Eof do
      begin
        v.Cells[i,j]:=DModule.AccountADOTable.FieldByName('帐号').AsString;
        inc(j);
        v.Cells[i,j]:=DModule.AccountADOTable.FieldByName('姓名').AsString;
        inc(j);
        v.Cells[i,j]:=DModule.AccountADOTable.FieldByName('所属部门').AsString;
         inc(j);
        v.Cells[i,j]:=DModule.AccountADOTable.FieldByName('实发工资').AsString;
        j:=1;
        Dmodule.AccountADOTable.Next;
        inc(i);
      end;
      v.visible:=True;
end;

Answer 2

正好我最近做了一个，但在我的机子上能够使用，在另一台上不能，您先试试：
uses ComObj;procedure TmainForm.QueryInFoToExcel(FileName, TitleCaption: string; makeQuery: TQuery);
var
     xlApp, xlSheet, szValue: Variant;
     ARow, iLoop: word;
begin
  if FileExists(FileName) and  (MessageDlg('此文件已存在,需要覆盖吗?',mtConfirmation, [mbYes, mbNo], 0) = mrYes) then
  begin
     xlApp := CreateOleObject('Excel.Application');
     try
         xlSheet := CreateOleObject('Excel.Sheet');
         xlSheet := xlApp.WorkBooks.Add;         // 主标题
          xlSheet.WorkSheets[1].Cells[1,1] := TitleCaption;         //表格标题
         for iLoop := 0 to ShowQuery.Fields.Count - 1 do
              xlSheet.WorkSheets[1].Cells[2, iLoop+1] := makeQuery.FieldDefs[iLoop].Name;         // 数据
         ARow := 3;
         with MakeQuery do
         begin
              DisableControls;
              First;
              while not Eof do
              begin
                   for iLoop := 0 to Fields.Count - 1 do
                   begin
                       szValue := Fields[iLoop].Value;
                       xlSheet.WorkSheets[1].Cells[ARow, iLoop+1] := szValue;
                   end;
                   Inc(ARow);
                   Next;
              end;
              First;
              EnableControls;
         end;         try
              xlSheet.SaveAs(FileName);
              Application.MessageBox('导出完毕!', '提示', MB_IconExclamation);
         finally
              xlSheet.Close;
              xlApp.Quit;
              xlApp := UnAssigned;
         end;
     except
          MessageBox(handle, '本机没有安装Excel或运行失败，请检查.', '提示',MB_IconExclamation);
     end;
  end;
end;procedure TmainForm.Button2Click(Sender: TObject);
begin
  if SaveDialog1.Execute then
  begin
    case PageControl1.ActivePageIndex of
      0:QueryInFoToExcel(SaveDialog1.FileName,'准备打印记录表    导出时间:'+DatetimeToStr(Now),ShowQuery);
      1:QueryInFoToExcel(SaveDialog1.FileName,'无效记录表    导出时间:'+DatetimeToStr(Now),ShowQuery);
      2:QueryInFoToExcel(SaveDialog1.FileName,'不打印记录表    导出时间:'+DatetimeToStr(Now),ShowQuery);
      3:QueryInFoToExcel(SaveDialog1.FileName,'全部记录表    导出时间:'+DatetimeToStr(Now),ShowQuery);
    end;
  end;
end;

Answer 3

procedure speedtoexcel_user(Sinput:TwwDBGrid);
var
  Ds_Master:Tdataset;
  ExcelApplication1:TExcelApplication;
  ExcelWorksheet1:TExcelWorksheet;
  ExcelWorkbook1:TExcelWorkbook;
  i,j:integer;
  stringlist1:Tstringlist;
  str1:string;
  range1:string;
begin
  Ds_Master:=Sinput.DataSource.DataSet;
  if Ds_Master.IsEmpty or (not Ds_Master.Active) then
     exit
  else
    begin
      Ds_Master.DisableControls;
      Ds_Master.First;
      try
        ExcelApplication1:=TExcelApplication.Create(Application);
        ExcelWorksheet1:=TExcelWorksheet.Create(Application);
        ExcelWorkbook1:=TExcelWorkbook.Create(Application);
        ExcelApplication1.Connect;
      except
        Application.MessageBox('Excel 没有安装','系统提示',
                               MB_IConERROR + mb_Ok);
        Abort;
      end;
    end;  try
    screen.Cursor:=crsqlwait;
    ExcelApplication1.Workbooks.Add(emptyparam,0);
    ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);
    ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1]as _worksheet);
    for j :=0  to Ds_Master.FieldCount-1 do
    begin
      ExcelWorksheet1.Cells.Item[1,j+1]:=Ds_Master.Fields[j].DisplayLabel;
      ExcelWorksheet1.Cells.Item[1,j+1].font.size:='14';
    end;
    stringlist1:=Tstringlist.Create;
    clipboard.Clear;
    with Ds_Master do
    begin
      open;
      first;
    end;
    while not Ds_Master.Eof do
    begin
      str1:='';
      for i := 0 to Ds_Master.FieldCount-1 do
      begin
        str1:=str1+Ds_Master.Fields[i].AsString+#9;
        Application.ProcessMessages;
      end;
      stringlist1.Add(str1);
      Ds_Master.Next;
    end;
    //dataset.Free;
    //dataset.Refresh;
    clipboard.AsText:=stringlist1.Text;
     i:=Ds_Master.FieldCount;
    j:=Ds_Master.RecordCount+1;       //ExcelWorksheet1.Cells.i
    //i:=cells.
    //ExcelWorksheet1.Paste;  //从A1开始粘贴
    //srt1:=ExcelWorksheet1.Cells.Item[i,j];
    //ExcelWorksheet1.Range['a2','z1000'].PasteSpecial(0,0,0,0);
    //ExcelWorksheet1.Range['a2','b4'].PasteSpecial(0,0,0,0);
    //i:=ExcelWorksheet1.Cells.Row[2];
    //j:=ExcelWorksheet1.Cells.Rows.Column;
   //fortest:=string(ExcelWorksheet1.Cells.Name);
    //fortest:=ExcelWorksheet1.Cells.Item[j,i];
    //fortest:=string(ExcelWorksheet1.Cells.Range[i,j] );
    //ExcelWorksheet1.Range['a2'].PasteSpecial(0,0,0,0);
    range1:=excelrange(j,i);
    ExcelWorksheet1.Range['a2',range1].PasteSpecial(0,0,0,0);
    stringlist1.Free;
    clipboard.Clear;
    ExcelWorksheet1.Columns.AutoFit;
    ExcelApplication1.Disconnect;
    ExcelApplication1.Visible[0]:=true;
    screen.Cursor:=crdefault;
    //dataset.Refresh;
    Ds_Master.EnableControls;
    Application.MessageBox('数据转换成功!','系统',
                  MB_ICONINFORMATION+Mb_ok);
                  
  except
    stringlist1.Free;
    clipboard.Clear;
    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
    ExcelApplication1.Free;
    ExcelWorkbook1.Free;
    ExcelWorksheet1.Free;
    screen.Cursor:=crdefault;
    Application.MessageBox('错误!','数据转换失败!',
                Mb_iconerror+Mb_ok);
    Ds_Master.EnableControls;
  end;
 end;

Answer 4

剪贴板也可以阿
先把表的数据导到richedi每列用#9隔开，没条记录后加上#$A#$D