如何将数据写入excel指定worksheet当中

Question

如题
最好能够按照quickreprot显示的格式写入excel，问了很久了，没有人回答！

Answer 1

//数据导出至excel
procedure TDataModule_public.WriteExcel(AdsData: TTable; sName, Title: string);
var
  ExcelApplication1: TExcelApplication;
  ExcelWorksheet1: TExcelWorksheet;
  ExcelWorkbook1: TExcelWorkbook;
  i, j: integer;
  filename: string;
begin
  filename := sName;
  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);
    AdsData.First;
    for j := 0 to AdsData.Fields.Count - 1 do
      begin
        ExcelWorksheet1.Cells.item[3, j + 1] := AdsData.Fields[j].DisplayLabel;
        ExcelWorksheet1.Cells.item[3, j + 1].font.size := '10';
      end;
    for i := 4 to AdsData.RecordCount + 3 do
      begin
        for j := 0 to AdsData.Fields.Count - 1 do
          begin
            ExcelWorksheet1.Cells.item[i, j + 1] :=
                AdsData.Fields[j].Asstring;
            ExcelWorksheet1.Cells.item[i, j + 1].font.size := '10';
          end;
        AdsData.Next;
      end;
    ExcelWorksheet1.Columns.AutoFit;
    ExcelWorksheet1.Cells.item[1, 2] := Title;
    ExcelWorksheet1.Cells.Item[1, 2].font.size := '14';
    ExcelWorksheet1.SaveAs(filename);
    Application.Messagebox(pchar('数据成功导出' + filename), 'Hello',
      mb_Ok);
  finally
    ExcelApplication1.Disconnect;
    ExcelApplication1.Quit;
    ExcelApplication1.Free;
    ExcelWorksheet1.Free;
    ExcelWorkbook1.Free;
  end;
end;

Answer 2

我的程序根据DBGrid导出到Excel的，你可以参阅一下：
Procedure TurnToExcel(TmpDBGrid:TDBGrid);
var
  MyExcel: Variant;
  WorkBook: OleVariant;
  WorkSheet: OleVariant;
  i,j:integer;
  xlsfilename :string;
  Savedialog1 :TSaveDialog;
begin
  SaveDialog1 :=TSaveDialog.create(Application);
  SaveDialog1.Filter := 'Excel文件(*.xls)|*.XLS';
  if savedialog1.Execute then
  if savedialog1.FileName <>'' then
  begin
    xlsfilename :=savedialog1.FileName;
  try
   MyExcel:=CreateOleObject('Excel.Application');
   MyExcel.Application.WorkBooks.Add;
   MyExcel.Caption:='将数据导入到EXCEL表中';
   MyExcel.Application.Visible:=false;
   WorkBook:=MyExcel.Application.workbooks[1];
   worksheet:=workbook.worksheets.item[1];
   except
     Application.MessageBox('EXCEL不存在！',App_caption,MB_ICONERROR+MB_OK);
    Savedialog1.Free;
    workBook.Saved := True;
    WorkBook.close;
    MyExcel.Quit;//释放VARIANT变量
    MyExcel:=Unassigned;
   end;
   i:=1;
   Frm_progress :=TFrm_progress.create(Application);//进度条窗体，用的是TGauge
  Try
    with TmpDBGrid.DataSource.DataSet   do
    begin
      Open;
      DisableControls;
      with Frm_progress.pp do
      begin
        minvalue :=0;
        maxvalue :=TmpDBGrid.Columns.Count*recordcount;
        progress :=0;
      end;
      Frm_progress.label1.caption :='正在导出到Excel...';
      Frm_progress.Show;
      Frm_progress.update;
      for j:=0 to TmpDBGrid.Columns.Count-1 do
      begin
        if TmpDBGrid.Columns[j].Visible=true then
         worksheet.cells[1,j+1]:=TmpDBGrid.Columns[j].Title.Caption;
      end;
      First;
      while not Eof do
      begin
        inc(i);
        for j:=0 to TmpDBGrid.Columns.Count-1 do
        begin
          if TmpDBGrid.Columns[j].Visible=true then
          begin
            worksheet.cells[i,j+1].NumberFormatLocal :='@';
            worksheet.cells[i,j+1]:=TmpDBGrid.Columns[j].Field.AsString ;
            Frm_progress.pp.progress :=Frm_progress.pp.progress+1;
          end;
        end;
        next;
      end;
      EnableControls;
    end;
    WorkBook.saveas(XlsFileName);
    Frm_progress.pp.progress :=TmpDBGrid.Columns.Count*TmpDBGrid.DataSource.DataSet.RecordCount;
    Application.MessageBox('导出到Excel成功！','查看数据库结构',MB_ICONINFORMATION+MB_OK);
    Frm_progress.Free;
    MyExcel.Quit;
    MyExcel := Unassigned;
    Savedialog1.Free;
  except
    Application.MessageBox('导出到Excel失败！','查看数据库结构',MB_ICONWARNING+MB_OK);
    workBook.Saved := True;
    WorkBook.close;
    MyExcel.Quit;//释放VARIANT变量
    MyExcel:=Unassigned;
    Frm_progress.Free;
    Savedialog1.Free;    
  end;
  end;end;