Delphi如何取得excel表格中某個格子里的函數

Question

Delphi如何取得excel表格中某個格子里的函數

Answer 1

procedure TfmMain.FormCreate(Sender: TObject);
const
  xlWorksheet=-4167;
var
  i:Integer;
  sFilePath:String;
  xlsApp:Variant;
begin
  try
    try
      //sFilePath := '\\Datacenter\Public\PaUser\'+FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄.xls';
      xlsApp:=CreateOLEObject('Excel.Application');
      xlsApp.Workbooks.Add(xlWorksheet);
      xlsApp.ActiveWorkbook.ActiveSheet.Name:=FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄';
      //標題行
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,1].Value:='分機';
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,2].Value:='通話日期';
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,3].Value:='時長';
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,4].Value:='電話號碼';
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,5].Value:='話務類別';
      //標題行對齊方式
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,1].HorizontalAlignment:=3;
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,2].HorizontalAlignment:=3;
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,3].HorizontalAlignment:=3;
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,4].HorizontalAlignment:=3;
      xlsApp.ActiveWorkbook.ActiveSheet.cells[1,5].HorizontalAlignment:=3;      xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].RowHeight:=0.8/0.035;       //第一行行高
      xlsApp.ActiveWorkbook.ActiveSheet.Range['A1:E1'].Borders[4].Weight:=2;//第一行下劃線
      xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].Font.Color:=clRed;          //第一行字體顏色
      xlsApp.ActiveWorkbook.ActiveSheet.Rows[1].Font.Bold :=True;           //
      with ADS_GetTELRec do
      begin
        Active:=False;
        CommandText:=' Select ExtentionNo,[DateTime],Long,a.TelNo,'+
                     ' TelKind=Case When IsNull(b.TelKind,'''')<>'''' Then TelKind Else ''私人電話'' end'+
                     ' From Hr..Call_list a Left Outer Join Call_TelNumber b On a.TelNo=b.TelNo '+
                     ' where Year([DateTime])=Year(GetDate()) and DATEPART(Week,[DateTime])=DATEPART(Week,GetDate()) '+
                     ' and ExtentionNo not in (''20'',''15'',''16'',''17'',''18'',''21'',''22'',''28'') '+
                     ' and Convert(Int,SubString(Long,1,CHARINDEX(''-'',Long)-1))>=3 '+
                     ' Order By ExtentionNo ';
        Active:=True;
        First;
        i:=1;
        while not eof do
        begin
          i:=i+1;
          xlsApp.ActiveWorkbook.ActiveSheet.rows[i].select;          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].Value:=FieldByName('ExtentionNo').AsString;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].NumberFormatLocal:='G/通用格式';
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].HorizontalAlignment:=3;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,1].Value;          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].Value:=FieldByName('DateTime').AsDateTime;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].NumberFormatLocal:='dd.mm.yyyy hh:mm:ss';
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].HorizontalAlignment:=3;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,2].Value;          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].Value:=''''+FieldByName('Long').AsString;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].NumberFormatLocal:='@';
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].HorizontalAlignment:=3;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,3].Value;          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].Value:=''''+FieldByName('TelNo').AsString;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].NumberFormatLocal:='@';
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,4].Value;          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].Value:=FieldByName('TelKind').AsString;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].NumberFormatLocal:='@';
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].HorizontalAlignment:=4;
          xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].FormulaR1C1:=xlsApp.ActiveWorkbook.ActiveSheet.cells[i,5].Value;          Next;
        end;
        xlsApp.ActiveWorkbook.ActiveSheet.Columns[1].ColumnWidth := 5;
        xlsApp.ActiveWorkbook.ActiveSheet.Columns[2].ColumnWidth := 18;
        xlsApp.ActiveWorkbook.ActiveSheet.Columns[3].ColumnWidth := 8;
        xlsApp.ActiveWorkbook.ActiveSheet.Columns[4].ColumnWidth := 15;
        xlsApp.ActiveWorkbook.ActiveSheet.Columns[5].ColumnWidth := 15;
        sFilePath := '\\Datacenter\Public\PaUser\'+FormatDateTime('yyyy',Date)+'年第'+FloatToStr(WeekOfTheYear(Date))+'周電話記錄.xls';
        xlsApp.Visible := False;
        xlsApp.DisplayAlerts:=False;
        xlsApp.ActiveWorkbook.SaveAs(sFilePath);
        xlsApp.ActiveWorkBook.Saved := True;
        xlsApp.WorkBooks.Close;
      end;
      //以下處理發送郵件
      with SP_SendMail do
      begin
        close;
        Parameters.ParamByName('@Year').value:= FormatDateTime('yyyy',Date);
        Parameters.ParamByName('@Wek').value := FloatToStr(WeekOfTheYear(Date));
        ExecProc;
      end;
      if FileExists(sFilePath) then DeleteFile(sFilePath);
    except
      ShowMessage(Exception(ExceptObject).Message);
    end;
  finally
    xlsApp.Quit;
    Application.Terminate;
  end;
end;

Answer 2

謝謝, 我不需要常識.
某EXCEL頁的某個格子中為一個計算函數, 在DLEPHI中如何取出這個函數? 我現在只能取到這個格子的值, 即函數運算出來的結果.

Answer 3

        showmessage(Excelid.worksheets[1].Range['A5'].Formula);

Answer 4

erhan 的答案很雷人, 我得找时间试一下.