怎么样可以后台打开excel处理数据

Question

本帖最后由 tim_choi 于 2010-08-12 18:02:25 编辑

Answer 1

procedure TForm1.Button1Click(Sender: TObject);
var
    ExcelApp: TExcelApplication;
    ExcelWB: TExcelWorkbook;
    ExcelWS: TExcelWorksheet;
begin
    //创建实体
    ExcelApp := TExcelApplication.Create(nil);
    ExcelWB := TExcelWorkbook.Create(nil);
    ExcelWS := TExcelWorksheet.Create(nil);
    try
        //连接EXCEL
        with ExcelApp do begin
            Connect;
            Visible[0] := true;
            Caption := Caption;
            Workbooks.Add('E:\delphi\excel\wo\ceshi.xls', 0);
            ExcelWB.ConnectTo(ActiveWorkbook);
        end;
        ExcelWS.ConnectTo(ExcelWB.Activesheet as _WorkSheet);我现在是这样连接的

Answer 2

給你一個demo，自己參考一下：procedure TCQ_JB_SHEET_F.ExporttoisSecondCheckExcel;
var
  iLoop:Integer;
  ExcelApp:Variant;
  isMsOffice:Boolean;
begin
  try
    Application.ProcessMessages;
    ExcelApp:=CreateOleObject('Excel.Application');
    isMsOffice:=True;
  except
    try
      isMsOffice:=False;
    except
      MessageDlg('對不起,你電腦上沒有安裝MicroSoftOffice Excel或OpenOffice Calc,所以不能導出報表!',mtWarning,[mbOK],0);
      Exit;
    end;
  end;  if isMsOffice then   //MSoffice
  begin
    try
      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:='Arial';
      ExcelApp.WorkBooks[1].WorkSheets[1].cells.font.size:=9;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[1,8]:='xxx公司'+ VarToStr(cx_jbcb.Text) + VarToStr(cx_bmkb.Text) + '(部)課' + VarToStr(cx_jbzxb.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:T1'].MergeCells:=True;
      ExcelApp.Range['A1:T1'].HorizontalAlignment:=xlCenter;
      Application.ProcessMessages;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,1]:='序號';
      ExcelApp.Range['A2:A3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,2]:='工號';
      ExcelApp.Range['B2:B3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,3]:='姓名';
      ExcelApp.Range['C2:C3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,4]:='課別';
      ExcelApp.Range['D2:D3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,5]:='組別';
      ExcelApp.Range['E2:E3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,6]:='產品類別';
      ExcelApp.Range['F2:F3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,7]:='屬性類別';
      ExcelApp.Range['G2:G3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,8]:='班次';
      ExcelApp.Range['H2:H3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,9]:='預計時數';
      ExcelApp.Range['I2:I3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,10]:='預計開始';
      ExcelApp.Range['J2:J3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,11]:='預計結束';
      ExcelApp.Range['K2:K3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,12]:='加班原因';
      ExcelApp.Range['L2:L3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,13]:='申請人';
      ExcelApp.Range['M2:M3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,14]:='上班簽名';
      ExcelApp.Range['N2:N3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,15]:='下班簽名';
      ExcelApp.Range['O2:O3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,16]:='實際時數';
      ExcelApp.Range['P2:P3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,17]:='實際開始';
      ExcelApp.Range['Q2:Q3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,18]:='實際結束';
      ExcelApp.Range['R2:R3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,19]:='提前或延後'+char(10)+'下班時間';
      ExcelApp.Range['S2:S3'].MergeCells:=True;
      ExcelApp.WorkBooks[1].WorkSheets[1].cells[2,20]:='備註原因';
      ExcelApp.Range['T2:T3'].MergeCells:=True;
      Application.ProcessMessages;
      qry_export.Close;
      qry_export.SQL.Clear;
      qry_export.SQL.Add('select * from Res_User_Temp where  CB='''+ VarToStr(cx_jbcb.Text)+''' and BM='''+ VarToStr(cx_bmkb.Text)+''' and KB='''+ VarToStr(cx_jbzxb.Text)+'''');
      qry_export.Open;
      ProgressBar1.Min:=0;
      ProgressBar1.Max:=qry_export.RecordCount;
      qry_export.First;
      for iLoop:=0 to qry_export.RecordCount -1 do
      begin
        try
          ExcelApp.cells[4+iLoop,1].value:=IntToStr(iLoop+1);
          ExcelApp.cells[4+iLoop,2].value:=Trim(qry_export.FieldByName('ACCOUNT_ID').AsString);
          ExcelApp.cells[4+iLoop,3].value:=Trim(qry_export.FieldByName('FULL_NAME').AsString);
          ExcelApp.cells[4+iLoop,4].value:=Trim(qry_export.FieldByName('KB').AsString);
          ExcelApp.cells[4+iLoop,5].value:=Trim(qry_export.FieldByName('XB').AsString);
          ExcelApp.cells[4+iLoop,6].value:=Trim(qry_export.FieldByName('JZ').AsString);
          ExcelApp.cells[4+iLoop,7].value:=Trim(qry_export.FieldByName('ZJQF').AsString);
          ExcelApp.cells[4+iLoop,8].value:=Trim(qry_export.FieldByName('BC').AsString);
          ProgressBar1.Position:=ProgressBar1.Position+1;
          Application.ProcessMessages;
          qry_export.Next;
        except
          qry_export.Next;
          Continue;
        end;
      end;
      ExcelApp.cells[4+iLoop,3].value:='應到人數:';
      ExcelApp.cells[4+iLoop,4].value:=IntToStr(qry_export.RecordCount)+'人';
      ExcelApp.cells[4+iLoop,9].value:='實到人數:';
      ExcelApp.cells[4+iLoop,10].value:='人';
      ExcelApp.cells[4+iLoop,15].value:='合計:';
      ExcelApp.cells[4+iLoop,16].value:='=SUM(P4:P'+ inttostr(3+iLoop)+')';      ExcelApp.cells[5+iLoop,5].value:='核准:';
      ExcelApp.cells[5+iLoop,9].value:='審核:';
      ExcelApp.cells[5+iLoop,13].value:='填表人:';      ExcelApp.Range['A2:T'+inttostr(4+iLoop)].HorizontalAlignment:=xlCenter;
      ExcelApp.Range['A2:T'+inttostr(4+iLoop)].Borders.linestyle:=xlContinuous;
      ProgressBar1.Position:=0;
      ExcelApp.Visible:=True;
      Screen.Cursor:=crDefault;
    except
      ExcelApp.ActiveWorkBook.Saved:=True;
      ExcelApp.WorkBooks.Close;
      ExcelApp.Quit;
    end;
  end  
end;

Answer 3

ExcelApp.Visible := False;
这样就不可见了

Answer 4

var
  excel:OleVariant;
...
excel:=CreateOleObject('Excel.Application');
excel.workbooks.add; 
...
Excel.Visible    :=    true;//默认就是后台处理的，加了这一句才显示出来
Excel.ActiveWorkbook.SaveAs(YourFileName,xlNormal,'', '',False,False);

Answer 5

出现错误
 '[' expected but ':=' found