delphi操作excel時，如何對導出的數據進行公式計算？（如：SUM）

Question

procedure TWKO_MEO_REPORT_F.cxButton1Click(Sender: TObject);
var
  ExApp:Variant;
  iLoop:Integer;
  //recordcount:Integer;
  bzgs:Double;
begin
  if (cx_cb.Text='')or(cx_sdt.Text='')or(cx_edt.Text='') then
  begin
    FrmMsgBox('提示','導出報表廠別或日期不能為空!',MsgBox_OK_INFO);
    Exit;
  end
  else
  begin
    qry_meo_report.Close;
    qry_meo_report.SQL.Clear;
    qry_meo_report.SQL.Add('select * from (select * from wko_schedule_week union all select * from wko_schedule_week_before) a where bp='''+ cx_cb.text+''' and wko_stime between '''+ cx_sdt.text+''' and '''+ cx_edt.text+'''');
    qry_meo_report.Open;
    qry_bzgs.Close;
    qry_bzgs.SQL.Clear;
    qry_bzgs.SQL.Add('select dzhm,bzrs,times from wko_bp where dzhm='''+ qry_meo_report.fieldbyname('xbid').AsString+'''');
    qry_bzgs.Open;
    bzgs:=qry_bzgs.fieldbyname('times').AsFloat*5;
  end;  try
    Application.ProcessMessages;
    ExApp:=CreateOleObject('Excel.Application');
  except
    FrmMsgBox('提示','你電腦上沒有安裝Excel,請確認!',MsgBox_OK_INFO);
    Exit;
  end;  try
    ExApp.Caption:='昆盈嫁動明細報表';
    ExApp.Visible:=False;
    ExApp.WorkBooks.Add;
    ExApp.WorkBooks[1].WorkSheets[1].name:='昆盈嫁動明細報表';
    ExApp.WorkBooks[1].WorkSheets[1].Activate;
    Screen.Cursor:=crSQLWait;
    Application.ProcessMessages;
    ExApp.cells.Interior.ColorIndex:=2;
    ExApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.name:='新細明體';
    ExApp.WorkBooks[1].WorkSheets[1].cells.font.size:=10;
    ExApp.WorkBooks[1].WorkSheets[1].Columns[11].ColumnWidth:=11.65;
    ExApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.name:='新細明體';
    ExApp.WorkBooks[1].WorkSheets[1].cells[1,8]:=cx_cb.Text+'嫁動明細報表';
    ExApp.WorkBooks[1].WorkSheets[1].cells[1,8].font.size:=14;
    ExApp.Range['A1:K1'].MergeCells:=True;
    ExApp.Range['A1:K1'].HorizontalAlignment:=xlCenter;
    Application.ProcessMessages;    ExApp.WorkBooks[1].WorkSheets[1].cells[2,3]:='正常';
    ExApp.Range['A2:E2'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[3,3]:='超出上限';
    ExApp.Range['A3:E3'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[4,3]:='不足下限';
    ExApp.Range['A4:E4'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[5,3]:='周總工時';
    ExApp.Range['A5:E5'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[6,3]:='周平均工時';
    ExApp.Range['A6:E6'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[7,1]:='成本中心';
    ExApp.Range['A7:A8'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[7,2]:='課別';
    ExApp.Range['B7:B8'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[7,3]:='線別';
    ExApp.Range['C7:C8'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[7,4]:='負責生管';
    ExApp.Range['D7:D8'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[7,5]:='負責課長';
    ExApp.Range['E7:E8'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[7,11]:='W2(1/5~1/11)上限>60,下限<40';
    ExApp.Range['F7:K7'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[8,6]:='標准工時';
    ExApp.Range['F8:F8'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[8,7]:='平時標准工時';
    ExApp.Range['F9:F9'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[8,8]:='周六平時標准工時';
    ExApp.Range['F10:F10'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[8,9]:='投入工時';
    ExApp.Range['F11:F11'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[8,10]:='平時投入工時';
    ExApp.Range['F12:F12'].MergeCells:=True;
    ExApp.WorkBooks[1].WorkSheets[1].cells[8,11]:='周六投入工時';
    for iLoop:=0 to qry_meo_report.RecordCount -1 do
    begin
      ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,1]:=VarToStr(qry_meo_report.FieldValues['WKO_CBZX']);
      ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,2]:=VarToStr(qry_meo_report.FieldValues['KBID']);
      ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,3]:=VarToStr(qry_meo_report.FieldValues['XBID']);
      ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,4]:=VarToStr(qry_meo_report.FieldValues['WKO_PLANNER']);
      ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,5]:='NULL';
      //ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,6]:='60';
      ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,7]:='60';
      ExApp.WorkBooks[1].worksheets[1].cells[9+iloop,8]:='50';      ProgressBar1.Position:=ProgressBar1.Position+1;
      Application.ProcessMessages;
      qry_meo_report.Next;
    end;
    ExApp.Range['F13:F13'].MergeCells:=True;
    ExApp.Range['A1:K'+inttostr(ExApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].HorizontalAlignment:=xlCenter;
    ExApp.Range['A1:K'+inttostr(ExApp.WorkBooks[1].WorkSheets[1].usedrange.rows.count)].Borders.linestyle:=xlContinuous;
    progressbar1.Position:=0;
    ExApp.Visible:=True;
    Screen.Cursor:=crDefault;
    ModalResult:=mrOk;
  except
    ExApp.ActiveWorkBook.Saved:=True;
    ExApp.WorkBooks.Close;
    ExApp.Quit;
  end;
end;

Answer 1

在数据集中计算出来写入excel可以不?还有将计算公示'=sum()'作为字符串写入excel不知道行不行

Answer 2

你如果导出公式的话 需要一个解析器  支持多参数 可以用parser10 控件   具体去搜索一下吧

Answer 3

建议用  xlsreadWriteII  空间直接读写EXCEL文件， 飞快！！！ 支持公式

Answer 4

試試在next前三句加此句，視要計算的單元格進行更改
ExApp.cells[9+iLoop,6].value:='=SUM(G'+ inttostr(9+iLoop)+':H'+ inttostr(9+iLoop)+')';