delhi从数据库导出数据生成Excel并自动发送邮件笔记

function TMainForm.CreateExcel(Customer:String): OleVariant;

var

  Excel,Sheet,workBook:OleVariant;

begin

   Excel := CreateOleObject('et.Application');

   Excel.application.workbooks.add;//newe xls file

   Excel.Visible := False;  // not display

   Excel.DisplayAlerts := False;

   Excel.WorkBooks[1].Worksheets[1].Name := '对帐单';

   sheet := Excel.WorkBooks[1].WorkSheets['对帐单'] ;

   Sheet.cells(1,1):= FormatDateTime('YYYY年MM月',Date)+Customer;

   Excel.Range['A1','I1'].font.size := 20;

   Excel.Range['A1','I1'].Font.Color := clRed;

   Excel.Range['A1','I1'].Font.Bold := True;

   //Merge Cell

   Excel.Range[sheet.cells[1,1],sheet.cells[1,8]].Select;

   Excel.Selection.MergeCells := True;

   sheet.Range['A1'].RowHeight := 36;//height

   Excel.Selection.HorizontalAlignment:=3;//center

   //A2高度

   sheet.Range['A2'].RowHeight := 20;

   //A2:B2合并

   Sheet.Cells[2,1] := '客户:'+Customer;

   Excel.Range['A2','B2'].Select;

   Excel.Selection.MergeCells := True;

   //G2:I2合并

   Sheet.Cells[2,7] := '制表日期:'+FormatDateTime('YYYY/MM/DD',Date);

   Excel.Range['G2','I2'].select;

   Excel.Selection.MergeCells := True;

   //A3:C3合并

   Sheet.Cells[3,1] :='统计日期:'+FormatDateTime('YYYY/MM/DD',Date1) + '至'+

   FormatDateTime('YYYY/MM/DD',Date2);;

   Excel.Range['A3','C3'].select;

   Excel.Selection.MergeCells := True;

   //draw line

   Excel.Range['A3','I3'].select;

   Excel.Selection.Borders[$09].LineStyle := $01;

   Excel.Selection.Borders[$09].Weight := $04;

   //fill header'name

   Sheet.Cells[4,1] :='单据日期';

   Sheet.Cells[4,2] :='单据名称';

   Sheet.Cells[4,3] :='摘要';

   sheet.Cells[4,7] :='应收金额';

   sheet.Cells[4,8] :='已收金额';

   sheet.Cells[4,9] :='结余金额';

   //合并C4:F4

   Excel.Range['c4','F4'].Select;

   Excel.Selection.MergeCells := True;

   Excel.Range['C4','I4'].RowHeight := 20;

   //draw line A4:I4

   Excel.Range['A4','I4'].select;

   Excel.Selection.Borders[$09].LineStyle := $01;

   Excel.Selection.Borders[$09].Weight := $04;

   //返利xls

   //Excel.Application.WorkBooks.Add;

   //Excel.WorkBooks[2].Worksheets[2].Name := '返利对帐单';

   //sheet := Excel.WorkBooks[1].WorkSheets['返利对帐单'] ;

   Result := Excel;

end;

function TMainForm.AutoCreateAddons(): string;
var
 ExcelApp:OleVariant;
 Sheet,WorkBook:OleVariant;
 BeginRow,BeginCol:integer;
begin
  BeginRow := 5;
  BeginCol := 1;
  //如果有数据则保存
   if spList.RecordCount > 0 then begin

     DirPath  := 'D:\hongyifoodhy\应收应付对帐单\'+FormatDateTime('yyyy',Date) +'\'+
      FormatDateTime('mm月',Date) +'\'+spListCurrency_Name.asstring;
     //附件需需要保存,目录存在吗？
      if not DirectoryExists(DirPath ) then
         ForceDirectories(DirPath);// 创建目录
      //创建文件
      FilePath :=DirPath+'\'+FormatDateTime('yyyy年mm月dd日',Date)+spListCustomer_Name.AsString+'.xls';
      if FileExists(FilePath) then
       DeleteFile(FilePath);
     try
      ExcelApp := CreateExcel(spListCustomer_Name.AsString);
     Except
      exit;
     end;
     try
     //应收应付
      sheet := ExcelApp.WorkBooks[1].WorkSheets['对帐单'];
      while  not spList.Eof do begin
        sheet.cells[BeginRow,BeginCol] :=FormatDateTime('yyyy/mm/dd',spListBillDate.AsDateTime);
        sheet.cells[beginRow,BeginCol+1] := spListBillName.asstring;  //单据名称
        Sheet.cells[beginRow,BeginCol+2] := spListBrief.asstring;     //摘要
        Sheet.cells[BeginRow,beginCol+6] := spListInvMoney.AsFloat;   //应收
        sheet.cells[BeginRow,BeginCol+7] := spListPayMoney.AsFloat;   //已收
        sheet.cells[BeginRow,Begincol+8] := spListResMoney.AsFloat;   //结余
        //合并
        ExcelApp.Range['A'+IntToStr(BeginRow),'I'+IntToStr(BeginCol)].Select;
        ExcelApp.Selection.Borders[$09].LineStyle := $01;
        ExcelApp.Selection.Borders[$09].Weight := $04;
        inc(BeginRow);  //下一行
        spList.Next;
      end;
      ExcelApp.WorkBooks[1].SaveAs(FilePath);
     finally
       ExcelApp.WorkBooks[1].Close;//close workbook
       ExcelApp.Quit;  //quit
       ExcelApp := Unassigned;//free
     //  sendEmail;
     end;