Delphi里如何保存并关闭excel

2010-07-05 09:38:40 948 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

delphi 调用excel的宏，并传递参数给宏 excel单元格中要显示换行的字符串，没有找到直接的方法，所以通过调用宏来实现宏：Sub Macro1(var1 As String, var2 As String) Macro2 Macro 宏由 liuin 录制，时间: 2007-10-26 ActiveCell.FormulaR1C1 = var1 + Chr(10) + var2End Subdelphi中阅读详情

使用comobj，对excel表格进行了读、写操作，比如想在点击button1后保存该excel，并关闭退出该excel。
语句怎么写。

eclApp.WorkBooks.Close;
eclApp.Quit;

貌似还不够

...全文

delphi 调用excel，修改excel并显示目的：将查询结果输出到excel，并打开excelfunction TFrmDBCZ_Report_DLBBFK.ProcessExcel(date: string): Boolean;var xlapp,MySheet,MyBooks:Variant; i,j:Integer; filename,savefile :string;begin filename := Extrac 阅读详情

thunderingman

数据库相关 2,508 成员 88,437 内容

+ 关注 + 加入社区

切换为时间正序

lovelymelon 2010-07-07

打赏
举报

这个直接中止excel进程



procedure TForm1.teminate_excel;

var

  lppe: TProcessEntry32;

  found : boolean;

  Hand : THandle;

  hh:hwnd;

  s:string;

begin

  Hand := CreateToolhelp32Snapshot(TH32CS_SNAPALL,0);

  found := Process32First(Hand,lppe);

  while found do

  begin

    s:=strpas(lppe.szExeFile);

    if uppercase(s)='EXCEL.EXE' then

    begin

      hh:=openprocess(PROCESS_ALL_ACCESS,true,lppe.th32ProcessID);

      terminateprocess(hh,0); //中止进程

      exit;

    end;

    found := Process32Next(Hand,lppe);

  end;

end;

lovelymelon 2010-07-07

打赏
举报



  if self.OpenDialog1.Execute then

    filename:=self.OpenDialog1.FileName;

  if filename='' then

    Exit;

  //打开Excel报表

  form1.teminate_excel;

  try

    Self.ExcelApplication1:=TExcelApplication.Create(Self);

    Self.ExcelApplication1.Connect;

  except

    messagebox(application.Handle,'无法生成Excel报表，请确定安装了Excel后重试','信息',mb_ok or mb_iconinformation);

    exit;

  end;

  Self.ExcelApplication1.Visible[0]:=False;

  Self.ExcelApplication1.DisplayAlerts[0]:=False;//这句屏蔽提示对话框，可以实现保存时不出现是否覆盖的提示

  self.ExcelApplication1.Workbooks.Open(filename,EmptyParam,

                                         EmptyParam,EmptyParam,EmptyParam,EmptyParam,

                                         EmptyParam,EmptyParam,EmptyParam,EmptyParam,

                                         EmptyParam,EmptyParam,EmptyParam,0);

  self.ExcelWorkbook1.ConnectTo(Self.ExcelApplication1.Workbooks[1]);

  self.ExcelWorksheet1:=TExcelWorkSheet.Create(self);

  self.ExcelWorksheet1.ConnectTo(Self.ExcelWorkbook1.Worksheets[1] as _worksheet);



  self.ExcelWorksheet1.SaveAs('f:\统计报表.xls');//这句实现保存

kye_jufei 2010-07-06

打赏
举报

分別導出excel



procedure TCQ_PG_REPORT_F.ExportToExcel;

var

  iLoop:Integer;

  ExcelApp:Variant;

  objServiceManager,objDesktop,oDocument,osheets,osheet:OleVariant;

  isMsOffice:Boolean;

begin

  if (Trim(cx_startdt.Text)='') and (Trim(cx_enddt.Text)='') then

  begin

    MessageDlg('提示:生成報表的開始日期和結束日期不能為空!',mtWarning,[mbOK],0);

    Exit;

  end;

  if (Trim(cx_startdt.Text)<>'') and (Trim(cx_enddt.Text)='') then

  begin

    MessageDlg('提示:生成報表的結束日期不能為空!',mtWarning,[mbOK],0);

    Exit;

  end;

  if (Trim(cx_startdt.Text)='') and (Trim(cx_enddt.Text)<>'') then

  begin

    MessageDlg('提示:生成報表的開始日期不能為空!',mtWarning,[mbOK],0);

    Exit;

  end;

  try

    Application.ProcessMessages;

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

    isMsOffice:=True;

  except

    try

      objServiceManager:=CreateOLEObject('com.sun.star.ServiceManager');

      objDesktop:=objServiceManager.createInstance('com.sun.star.frame.Desktop');

      Application.ProcessMessages;

      oDocument:=objDesktop.loadComponentFromURL('private:factory/scalc','_blank',0,VarArrayOf([]));

      oSheets:=oDocument.Sheets;

      isMsOffice:=False;

    except

      MessageDlg('提示:對不起,你電腦上沒有安裝MicroSoftOffice Excel或OpenOffice Calc,所以不能導出報表!',mtWarning,[mbOK],0);

      Exit;

    end;

  end;



  if (Trim(cx_startdt.Text)<>'') and (Trim(cx_enddt.Text)<>'') then

  begin

    if isMsOffice then //MSOffices

    begin

      try

        ExcelApp.Caption:='昆盈電子派工單報表';

        ExcelApp.Visible:=False;

        ExcelApp.WorkBooks.Add;

        ExcelApp.WorkBooks[1].WorkSheets[1].name:='電子派工單時數報表';

        ExcelApp.WorkBooks[1].WorkSheets[1].Activate;

        Screen.Cursor:=crSQLWait;

        Application.ProcessMessages;

        ExcelApp.cells.Interior.ColorIndex:=2;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.name:='標楷體';

        ExcelApp.WorkBooks[1].WorkSheets[1].cells.font.size:=9;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8]:=cx_startdt.Text +'---'+cx_enddt.Text+'東莞昆盈電腦制品有限公司電子派工單效率報表';

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.bold:=true;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.size:=14;

        ExcelApp.Range['A1:J1'].MergeCells:=True;

        ExcelApp.Range['A1:J1'].HorizontalAlignment:=xlCenter;

        Application.ProcessMessages;



        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,1]:='序號';

        ExcelApp.Range['A2:A2'].MergeCells:=True;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,2]:='編製';

        ExcelApp.Range['B2:B2'].MergeCells:=True;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,3]:='人員';

        ExcelApp.Range['C2:C2'].MergeCells:=True;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,4]:='直接區分';

        ExcelApp.Range['D2:D2'].MergeCells:=True;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,5]:='姓名';

        ExcelApp.Range['E2:E2'].MergeCells:=True;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,6]:='工作地';

        ExcelApp.Range['F2:F2'].MergeCells:=True;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,7]:='時數(HR)';

        ExcelApp.Range['G2:G2'].MergeCells:=True;

        ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,8]:='備註';

        ExcelApp.Range['H2:H2'].MergeCells:=True;



        Application.ProcessMessages;

        qry_export.Close;

        qry_export.SQL.Clear;

        qry_export.SQL.Add('select *,(C.XB+C.XM)as WHO from(select max(a.CB)as CB,max(a.BM)as BM,max(a.BH)as BH,max(a.XM)as XM,max(a.DZHM)as DZHM,max(a.XB)as XB,max(a.CBZX)as CBZX,max(a.GZDD)as GZDD,b.XB as XBDD,max(a.ZJQF)as ZJQF,');

        qry_export.SQL.Add('round(sum(datediff(mi,''00:00:00'',convert(varchar(20),a.GZSS))/60.0),1) as GZHR,a.REMARK from CQ_PG a left join RES_XB b on a.GZDD=b.DZHM where a.RQ between '''+ formatdatetime('yyyy/mm/dd',cx_startdt.Date)+''' and '''+ formatdatetime('yyyy/mm/dd',cx_enddt.Date)+''' ');

        qry_export.SQL.Add('group by a.CB,a.BM,a.BH,a.XM,a.DZHM,a.XB,a.CBZX,a.GZDD,b.XB,a.ZJQF,a.REMARK )C Order by C.BM,C.XM asc ');

        qry_export.Open;

        ProgressBar1.Min:=0;

        ProgressBar1.Max:=qry_export.RecordCount;

        qry_export.First;

        for iLoop:=0 to qry_export.RecordCount -1 do

        begin

          ExcelApp.cells[3+iLoop,1].value:=IntToStr(iLoop+1);

          ExcelApp.cells[3+iLoop,2].value:=Trim(qry_export.FieldByName('XB').AsString);

          ExcelApp.cells[3+iLoop,3].value:=Trim(qry_export.FieldByName('WHO').AsString);

          ExcelApp.cells[3+iLoop,4].value:=Trim(qry_export.FieldByName('ZJQF').AsString);

          ExcelApp.cells[3+iLoop,5].value:=Trim(qry_export.FieldByName('XM').AsString);

          ExcelApp.cells[3+iLoop,6].value:=Trim(qry_export.FieldByName('XBDD').AsString);

          ExcelApp.cells[3+iLoop,7].value:=Trim(qry_export.FieldByName('GZHR').AsString);

          ExcelApp.cells[3+iLoop,8].value:=Trim(qry_export.FieldByName('REMARK').AsString);



          ProgressBar1.Position:=ProgressBar1.Position+1;

          Application.ProcessMessages;

          qry_export.Next;

        end;

        //格式

        ExcelApp.Range['A2:J'+inttostr(4+iLoop)].HorizontalAlignment:=xlCenter;

        ExcelApp.Range['A2:J'+inttostr(4+iLoop)].Borders.linestyle:=xlContinuous;

        //樞紐分析

        ExcelApp.WorkBooks[1].WorkSheets[3].Activate;

        ExcelApp.ActiveWorkbook.PivotCaches.Create(1,'電子派工單時數報表!R2C1:R1048576C7').CreatePivotTable('Sheet3!R3C1','樞紐分析');

        ExcelApp.ActiveWorkbook.ShowPivotTableFieldList:=True;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('人員').Orientation:=xlRowField;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('人員').Position:=1;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('工作地').Orientation:=xlColumnField;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('工作地').Position:=1;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('時數(HR)').Orientation:=xlRowField;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('時數(HR)').Position:=2;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').DisplayImmediateItems:=True;

        ExcelApp.ActiveSheet.PivotTables('樞紐分析').AddDataField(ExcelApp.ActiveSheet.PivotTables('樞紐分析').PivotFields('時數(HR)'), '加總 - 時數(HR)',xlSum);

        //在樞紐分析中插入列

        ExcelApp.Columns['A:D'].Select;

        ExcelApp.Selection.Insert(xlToRight);

        //寫單元格

        ExcelApp.WorkBooks[1].WorkSheets[3].name:='電子派工單樞紐分析匯總報表';

        ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,1]:='編製';

        ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,2]:='姓名';

        ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,3]:='崗位時數';

        ExcelApp.WorkBooks[1].WorkSheets[3].cells[4,4]:='支援時數';        

        //套用公式

        ExcelApp.cells[5,1].value:='=IF(CODE(E5)=46753,LEFT(E5,2),IF(CODE(E5)>46753,"",LEFT(E5,3)))';

        ExcelApp.cells[5,2].value:='=IF(A5="",E5,IF(CODE(A5)=46753,MID(E5,3,4),MID(E5,4,4)))';

        ExcelApp.CellDragAndDrop:=True;

        //自動填充"編製"

        ExcelApp.Range['A5'].Select;

        ExcelApp.Selection.AutoFill(ExcelApp.Range['A5:A20000'],xlFillDefault);



        //自動填充"姓名"

        ExcelApp.Range['B5'].Select;

        ExcelApp.Selection.AutoFill(ExcelApp.Range['B5:B20000'],xlFillDefault);



        ProgressBar1.Position:=0;

        ExcelApp.Visible:=True;

        Screen.Cursor:=crDefault;

      except

        ExcelApp.ActiveWorkBook.Saved:=True;

        ExcelApp.WorkBooks.Close;

        ExcelApp.Quit;

      end;

   end;

end;

dinoalex 2010-07-06

打赏
举报

xlstest.Quit;
xlstest:= unassigned; // 补上这句

dinoalex 2010-07-05

打赏
举报

eclApp.DisplayAlerts:=false;
eclApp.Quit;

SuperTitan004 2010-07-05

打赏
举报

eclApp.WorkBooks.Close;
eclApp.Quit;
eclApp:=unassigned;
这样试试

thunderingman 2010-07-05

打赏
举报

谢谢大侠还有个问就是如何先判断该文件是否已经打开因为我发现任务管理器里还运行着该进程，有没有语句能判断该excel文档是否还打开着，如果打开着就先关了这个进程，谢谢

dinoalex 2010-07-05

打赏
举报

[Code=Delphi(Pascal)]
procedure TForm1.Button6Click(Sender: TObject);
var
xlstest, thesheet:Variant;
begin
try
xlstest:=CreateOleObject('Excel.Application');
except
Exit;
end;
xlstest.WorkBooks.Open(ExtractFilePath(Paramstr(0))+'tt.xls');
thesheet:= xlstest.Workbooks[1].Worksheets['sheet1'];
thesheet.cells.item[2,2]:= 'aa'; //memo1.text;
xlstest.DisplayAlerts:=false;
xlstest.ActiveWorkBook.SaveAs(ExtractFilePath(Paramstr(0))+'tt.xls');
xlstest.Quit;
end;
[/Code]

thunderingman 2010-07-05

打赏
举报

各位高手，帮忙啊

thunderingman 2010-07-05

打赏
举报

继续求教上面的问题，
eclApp.cells[2,2].value:='aa';
对某一个单元格输入内容后，希望保存该内容，然后关闭该excel，不要跳出是否保存的对话框的

thunderingman 2010-07-05

打赏
举报

SaveAs的话变成另存为了，都会跳出对话框，问是否覆盖了，这样都不好

wujinyuan 2010-07-05

打赏
举报

try
FWorkBook.WorkSheets[1].SaveAs(SaveFilaName);
FWorkBook.Save;
finally
FworkBook.Saved := True;
FExcelApp.WorkBooks.Close;
FExcelApp.Quit;
FExcelApp := Unassigned;
end;

thunderingman 2010-07-05

打赏
举报

各位学者，貌似上面的都不对么，这样会弹出是否保存的对话框的？
保存的语句是哪条？

nkym0626 2010-07-05

打赏
举报

学习了

Delphi里如何保存并关闭excel

相关推荐