Delphi里如何保存并关闭excel

Question

使用comobj，对excel表格进行了读、写操作，比如想在点击button1后保存该excel，并关闭退出该excel。
语句怎么写。  eclApp.WorkBooks.Close;
  eclApp.Quit;貌似还不够

Answer 1

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

Answer 2

      eclApp.DisplayAlerts:=false;
      eclApp.Quit;

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

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;

Answer 7

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

Answer 8

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

Answer 9

分別導出excelprocedure 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;

Answer 10

  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');//这句实现保存

Answer 11

这个直接中止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;