Delphi - 操作Excel数据公式的实现

procedure TF_SMP_FT_NEW.RzBitBtn_StartToChangeClick(Sender: TObject);
var
    i, j, ni, nj, iRows, iCol, iCol_flag: Integer;
    T_DATE, T_LC, T_CP, T_FN, T_Row_Flag, T_sl, Data_Flag, New_Path_Flag, Warning_Flag: string;
    ApplicationSheet: variant;
    nArr: array[0..5] of Integer;
    mArr: array[0..4] of Integer;
begin
    if RzOpenDialog1.FileName <> '' then
    begin
        RzEdit_Status.Text := '文件正在被转换，请稍后！';
        if RzEdit_FileName.Text <> '' then
        begin
            try
                ApplicationSheet := Createoleobject('Excel.Application'); //创建工作表
                ApplicationSheet.Visible := false; //Excel不可见
                ApplicationSheet.Workbooks.Open(RzOpenDialog1.FileName); //打开Excel并且导入文件
                ApplicationSheet.WorkSheets[1].activate; //设置第一页当前页
                New_Path_Flag := 'C:\Temp\' +'EX_'+ RzEdit_FileName.Text;
                Warning_Flag := '文件已保存到：C:\temp\'+'EX_'+ RzEdit_FileName.Text +#13+ '请注意查看！谢谢';
                //Operating the sheet.
                T_DATE := ApplicationSheet.Cells[4, 9].Value; //缓存日期
                for i := 41 to 50 do
                begin
                    if ApplicationSheet.Cells[14, i].Value = '' then
                    begin
                        if ApplicationSheet.Cells[14, i - 2].Value <> '' then
                        begin
                            T_Row_Flag := ApplicationSheet.Cells[14, i - 2].Value; //列数标记
                        end;
                    end;
                end;
                T_sl := StringReplace(T_Row_Flag, 'T', '', [rfReplaceAll]);
                iCol_flag := StrToInt(T_sl) + 2; //列数
                j := StrToInt(T_sl) + 3;
                for i := 4 to j do
                begin
                    ApplicationSheet.ActiveSheet.Columns[i].Delete; //把空白的列删除掉
                end;
                //asheet.Cells[16, 1].Value := 'Limit';     //先把格式规范化
                ApplicationSheet.Cells[17, 1].Value := 'Bias1'; //先把格式规范化
                ApplicationSheet.Cells[18, 1].Value := 'Bias2';
                ApplicationSheet.Cells[19, 1].Value := 'Bias3';

                ApplicationSheet.Cells[22, 1].Value := 'Min Limit';
                ApplicationSheet.Cells[23, 1].Value := 'Max Limit';
                ApplicationSheet.Cells[24, 1].Value := 'Average';
                ApplicationSheet.Cells[25, 1].Value := 'STDEV';
                ApplicationSheet.Cells[26, 1].Value := 'Min Data';
                ApplicationSheet.Cells[27, 1].Value := 'Max Data';

                ApplicationSheet.ActiveSheet.Rows[32].Delete; //行的处理：从下向上
                for i := 1 to 2 do
                begin
                    ApplicationSheet.ActiveSheet.Rows[28].Delete;
                end;
                ApplicationSheet.Cells[29, 1].Value := 'Serial#';
                ApplicationSheet.Cells[29, 2].Value := 'Bin#';
                ApplicationSheet.ActiveSheet.Rows[20].Delete;
                ApplicationSheet.ActiveSheet.Rows[20].Delete;
                ApplicationSheet.ActiveSheet.Rows[14].Delete;
                ApplicationSheet.ActiveSheet.Rows[14].Delete;
                ApplicationSheet.ActiveSheet.Rows[14].Delete;
                for i := 1 to 7 do
                begin
                    ApplicationSheet.ActiveSheet.Rows[4].Delete;
                end;
                ApplicationSheet.Cells[1, 1].Value := 'LotCode';
                ApplicationSheet.Cells[1, 2].Value := '';
                ApplicationSheet.Cells[1, 3].Value := 'CUSTOMER_Production';
                ApplicationSheet.Cells[1, 4].Value := 'TestFileName';
                ApplicationSheet.Cells[5, 1].Value := 'Parameter';
                ApplicationSheet.Cells[5, 2].Value := '';
                ApplicationSheet.Cells[6, 1].Value := 'Unit';
                ApplicationSheet.Cells[6, 2].Value := '';
                ApplicationSheet.ActiveSheet.Range['A2:E4'].Value := '';
                ApplicationSheet.ActiveSheet.Range['A16:E16'].Value := '';
                T_FN := RzEdit_FileName.Text;
                T_CP := Copy(T_FN, Pos('_', T_FN) + 1, 50);
                T_CP := Copy(T_CP, 1, Pos('_', T_CP) - 1); //产品名称
                T_LC := Copy(T_FN, 1, Pos('_', T_FN) - 1); //周记
                ApplicationSheet.Cells[2, 1].Value := T_LC;
                ApplicationSheet.Cells[2, 3].Value := T_CP;
                ApplicationSheet.Cells[2, 4].Value := RzEdit_FileName.Text;
                OraQuery_Tmp.Close;
                OraQuery_Tmp.Open;
                Oraquery_Tmp.First;
                i := 3;
                while not Oraquery_Tmp.Eof do
                begin
                    ApplicationSheet.Cells[5, i].Value := Oraquery_Tmp.FieldByName('CGCS').AsString;
                    ApplicationSheet.Cells[6, i].Value := Oraquery_Tmp.FieldByName('CGDW').AsString;
                    ApplicationSheet.Cells[10, i].Value := Oraquery_Tmp.FieldByName('MinLimit').AsString;
                    ApplicationSheet.Cells[11, i].Value := Oraquery_Tmp.FieldByName('MaxLimit').AsString;
                    i := i + 1;
                    Oraquery_Tmp.Next;
                end;
                //修改时间：20140924
                //增加功能：单位进一步转换达到测试要求
                iRows := 100; //从100行开始
                Data_Flag := ApplicationSheet.Cells[iRows, 3].Value;
                while Data_Flag <> '' do
                begin
                    iRows := iRows + 1;
                    Data_Flag := ApplicationSheet.Cells[iRows, 3].Value;
                end;
                iRows := iRows - 1; //计算行数

                OraQuery_Tmp.Close;
                OraQuery_Tmp.Open;
                Oraquery_Tmp.First;
                i := 0;
                while not Oraquery_Tmp.Eof do
                begin
                    mArr[i] := Oraquery_Tmp.FieldByName('ID').AsInteger + 2;
                    i := i + 1;
                    Oraquery_Tmp.Next;
                end;
                ni := i - 1;

                for iCol := 0 to ni do
                begin
                    for i := 18 to iRows do
                    begin
                        Data_Flag := ApplicationSheet.Cells[i, mArr[iCol]].Value;
                        if Data_Flag = 'Untested' then
                        begin
                            Continue;
                        end
                        else
                        begin
                            ApplicationSheet.Cells[i, mArr[iCol]].Value := ApplicationSheet.Cells[i, mArr[iCol]].Value * 1000;
                        end;
                    end;
                end;
                OraQuery_Tmp.Close;
                OraQuery_Tmp.Open;
                Oraquery_Tmp.First;
                j := 0;
                while not Oraquery_Tmp.Eof do
                begin
                    nArr[j] := Oraquery_Tmp.FieldByName('ID').AsInteger + 2;
                    j := j + 1;
                    Oraquery_Tmp.Next;
                end;
                nj := j - 1;

                for iCol := 0 to nj do
                begin
                    for i := 18 to iRows do
                    begin
                        Data_Flag := ApplicationSheet.Cells[i, nArr[iCol]].Value;
                        if Data_Flag = 'Untested' then
                        begin
                            Continue;
                        end
                        else
                        begin
                            ApplicationSheet.Cells[i, nArr[iCol]].Value := ApplicationSheet.Cells[i, nArr[iCol]].Value * 1000000000;
                        end;
                    end;
                end;
                //修改时间：2014-12-16
                //公式的实现
                for j := iCol_flag downto 3 do
                begin
                    ApplicationSheet.Cells[12, j].Value := '=AVERAGE(' + INDEX(18, j) + ':' + INDEX(iRows, j) + ')'; //Average
                    ApplicationSheet.Cells[13, j].Value := '=STDEVP(' + INDEX(18, j) + ':' + INDEX(iRows, j) + ')'; //STDEVP
                    ApplicationSheet.Cells[14, j].Value := '=MIN(' + INDEX(18, j) + ':' + INDEX(iRows, j) + ')'; //MinDate
                    ApplicationSheet.Cells[15, j].Value := '=MAX(' + INDEX(18, j) + ':' + INDEX(iRows, j) + ')'; //MaxDate
                end;

                //修改时间：2014-10-24
                //删除相同栏位，满足客户要求
                //测试程序
                for i := iCol_flag downto 3 do
                begin
                    Data_Flag := ApplicationSheet.Cells[5, i].Value;
                    if Data_Flag = 'N/A' then
                    begin
                        ApplicationSheet.ActiveSheet.Columns[i].Delete;
                    end;
                end;

                ApplicationSheet.Cells[1, 5].Value := 'DATE';
                ApplicationSheet.Cells[1, 6].Value := 'TEST TIME';
                ApplicationSheet.WorkSheets[1].Columns.NumberFormatLocal := 'G/通用格式'; //格式设置
                ApplicationSheet.Cells[2, 5].Value := T_DATE;
                ApplicationSheet.Cells.Item[2, 5].NumberFormatLocal := 'YYYYMMDD';

                ApplicationSheet.DisplayAlerts := False; //不显示保存替换对话框
                ApplicationSheet.WorkBooks[1].SaveAs(New_Path_Flag);
                ApplicationSheet.activeWorkBook.saved := True; //忽略保存
            except
                ApplicationSheet.Workbooks[1].close(True);
            end;
            ApplicationSheet.Workbooks[1].close(True);
            RzEdit_Status.Text := '文件已转换完成！';
            Application.MessageBox('文件已转换完成！', '提示', 0 + mb_iconinformation);
            ShowMessage(Warning_Flag);
        end;
    end
    else
    begin
        Application.MessageBox(' 请您先打开要操作的文件！', '提示', 0 + mb_iconinformation);
        Abort;
    end;
end;
 

end.

 

  作者：Jeremy.Wu
  出处：https://www.cnblogs.com/jeremywucnblog/
  本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。