高手救命！OLE操作Excel时，按照名称对单元格取值、赋值得问题！！着急！！

Question

修正，Excel.ActiveSheet.Range('ICountUnit').Value := '002';

Answer 1

test.xls中的名称已经定义了。

Answer 2

在delphi中使用使用自动化和vba中有些不同，
应该把range对象作为一个数组来考虑，即用range[n]的方式使用对象

Answer 3

to: ibear(大熊) ：
   能给一个例子吗？我很着急用。谢谢。

Answer 4

Variant ee,ss;
    ee=CreateOleObject("excel.application");
    ee.OlePropertySet("Visible",true);
    ee.OlePropertyGet("Workbooks").OleProcedure("Add");
    ss=ee.OlePropertyGet("ActiveWorkbook").OlePropertyGet("Worksheets",1);
    ss.OlePropertySet("Range","A2","基金收支表");
    ss.OlePropertySet("Range","A4","项目");
    ss.OlePropertySet("Range","B4","月计数");
    ss.OlePropertySet("Range","B5","机关");
    ss.OlePropertySet("Range","C5","事业");
    ss.OlePropertySet("Range","D5","国有企业");
    ss.OlePropertySet("Range","E5","集体企业");
    ss.OlePropertySet("Range","F5","港澳台外资企业");
    ss.OlePropertySet("Range","G5","其它企业");
    ss.OlePropertySet("Range","H5","小计");
    ss.OlePropertySet("Range","I4","累计数");
    ss.OlePropertySet("Range","I5","机关");
    ss.OlePropertySet("Range","J5","事业");
    ss.OlePropertySet("Range","K5","国有企业");
    ss.OlePropertySet("Range","L5","集体企业");
    ss.OlePropertySet("Range","M5","港澳台外资企业");
    ss.OlePropertySet("Range","N5","其它企业");
    ss.OlePropertySet("Range","O5","小计");
    ss.OlePropertyGet("Range","B:O").OlePropertySet("NumberFormat","0.00");
    ss.OlePropertyGet("Range","A2:O2").OleProcedure("Merge");
    ss.OlePropertyGet("Range","A4:A5").OleProcedure("Merge");
    ss.OlePropertyGet("Range","B4:H4").OleProcedure("Merge");
    ss.OlePropertyGet("Range","I4:O4").OleProcedure("Merge");
    ss.OlePropertyGet("Range","A1:O5").OlePropertySet("HorizontalAlignment",-4108);
    ss.OlePropertyGet("Range","A1:O5").OlePropertySet("VerticalAlignment",-4108);
    ss.OlePropertyGet("Columns","A").OlePropertySet("ColumnWidth",30);
    ss.OlePropertyGet("Range","B:O").OlePropertySet("ColumnWidth",10);
    ss.OlePropertySet("Range","A6","一、基金收入");
    ss.OlePropertySet("Range","A7","  1、单位缴纳");
    ss.OlePropertySet("Range","A8","  2、在职职工个人缴纳");
    ss.OlePropertySet("Range","A9","  3、退休人员个人缴纳");
    ss.OlePropertySet("Range","A11","  4、基金利息收入");
    ss.OlePropertySet("Range","A12","  5、财政补贴收入");
    ss.OlePropertySet("Range","A13","  6、异地转移收入");
    ss.OlePropertySet("Range","A14","  7、基金其他收入");
    ss.OlePropertySet("Range","A15","   其中：滞纳金收入");
    ss.OlePropertySet("Range","A16","  8、上级补助收入");
    ss.OlePropertySet("Range","A17","  9、下级上解收入");
    ss.OlePropertySet("Range","A19","二、上年结余");
    ss.OlePropertySet("Range","A20","       基金");
    ss.OlePropertySet("Range","A23","三、基金支出");
    ss.OlePropertySet("Range","A24","  1、支出");
    ss.OlePropertySet("Range","A25","    (1)住院费支出");
    ss.OlePropertySet("Range","A26","    (2)门诊费支出");
    ss.OlePropertySet("Range","A27","  2、退休人员医疗费支出");
    ss.OlePropertySet("Range","A28","    (1)住院费支出");
    ss.OlePropertySet("Range","A29","    (2)门诊费支出");
    ss.OlePropertySet("Range","A30","  3、异地转移支出");
    ss.OlePropertySet("Range","A31","  4、基金其他支出");
    ss.OlePropertySet("Range","A32","  5、上解上级支出");
    ss.OlePropertySet("Range","A33","  6、补助下级支出");
    ss.OlePropertySet("Range","A35","四、累计结余");
    ss.OlePropertySet("Range","A36","       基金");
    ss.OlePropertyGet("Range","A4:O36").
        OlePropertyGet("Borders").OlePropertySet("LineStyle",1);
    //----------------------------------------
    Query1->Close();Query1->SQL->Clear();
    Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
    Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) FROM DWJF ");
    Query1->SQL->Add("WHERE JFYF=:JFYF");
    Query1->SQL->Add("AND RYFL='A' AND JFLB='2'");
    Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
    Query1->Open();
    ss.OlePropertySet("Cells",6,8,Query1->Fields->Fields[0]->AsString);
    ss.OlePropertySet("Cells",7,8,Query1->Fields->Fields[2]->AsString);
    Query1->Close();Query1->SQL->Clear();
    Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
    Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) FROM DWJF ");
    Query1->SQL->Add("WHERE JFYF=:JFYF");
    Query1->SQL->Add("AND RYFL='0' AND JFLB='2'");
    Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
    Query1->Open();
    ss.OlePropertySet("Cells",8,8,Query1->Fields->Fields[1]->AsString);
    Query1->Close();Query1->SQL->Clear();
    Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
    Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) FROM DWJF ");
    Query1->SQL->Add("WHERE JFYF=:JFYF");
    Query1->SQL->Add("AND RYFL='2' AND JFLB='2'");
    Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
    Query1->Open();
    ss.OlePropertySet("Cells",9,8,Query1->Fields->Fields[1]->AsString);
    Query1->Close();Query1->SQL->Clear();
    Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
    Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) FROM DWJF ");
    Query1->SQL->Add("WHERE JFYF<=:JFYF");
    Query1->SQL->Add("AND RYFL='A' AND JFLB='2'");
    Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
    Query1->Open();
    ss.OlePropertySet("Cells",6,15,Query1->Fields->Fields[0]->AsString);
    ss.OlePropertySet("Cells",7,15,Query1->Fields->Fields[2]->AsString);

Answer 5

是CB的例子：
Query1->Close();Query1->SQL->Clear();
    Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
    Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) FROM DWJF ");
    Query1->SQL->Add("WHERE JFYF<=:JFYF");
    Query1->SQL->Add("AND RYFL='0' AND JFLB='2'");
    Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
    Query1->Open();
    ss.OlePropertySet("Cells",8,15,Query1->Fields->Fields[1]->AsString);
    Query1->Close();Query1->SQL->Clear();
    Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
    Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) FROM DWJF ");
    Query1->SQL->Add("WHERE JFYF<=:JFYF");
    Query1->SQL->Add("AND RYFL='2' AND JFLB='2'");
    Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
    Query1->Open();
    ss.OlePropertySet("Cells",9,15,Query1->Fields->Fields[1]->AsString);
    for(int i=2;i<8;i++) {
        Query1->Close();Query1->SQL->Clear();
        Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
        Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) ");
        Query1->SQL->Add("FROM DWJF JOIN CBDW ON DWJF.DWBH=CBDW.DWBH");
        Query1->SQL->Add("WHERE DWJF.JFYF=:JFYF AND CBDW.DWXZ=:DWXZ");
        Query1->SQL->Add("AND RYFL='A' AND JFLB='2'");
        Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
        Query1->ParamByName("DWXZ")->AsString=ss.OlePropertyGet("Cells",5,i);
        Query1->Open();
        ss.OlePropertySet("Cells",6,i,Query1->Fields->Fields[0]->AsString);
        ss.OlePropertySet("Cells",7,i,Query1->Fields->Fields[2]->AsString);
        Query1->Close();Query1->SQL->Clear();
        Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
        Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) ");
        Query1->SQL->Add("FROM DWJF JOIN CBDW ON DWJF.DWBH=CBDW.DWBH");
        Query1->SQL->Add("WHERE DWJF.JFYF=:JFYF AND CBDW.DWXZ=:DWXZ");
        Query1->SQL->Add("AND RYFL='0' AND JFLB='2'");
        Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
        Query1->ParamByName("DWXZ")->AsString=ss.OlePropertyGet("Cells",5,i);
        Query1->Open();
        ss.OlePropertySet("Cells",8,i,Query1->Fields->Fields[1]->AsString);
        Query1->Close();Query1->SQL->Clear();
        Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
        Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) ");
        Query1->SQL->Add("FROM DWJF JOIN CBDW ON DWJF.DWBH=CBDW.DWBH");
        Query1->SQL->Add("WHERE DWJF.JFYF=:JFYF AND CBDW.DWXZ=:DWXZ");
        Query1->SQL->Add("AND RYFL='2' AND JFLB='2'");
        Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
        Query1->ParamByName("DWXZ")->AsString=ss.OlePropertyGet("Cells",5,i);
        Query1->Open();
        ss.OlePropertySet("Cells",9,i,Query1->Fields->Fields[1]->AsString); }
    for(int i=9;i<15;i++) {
        Query1->Close();Query1->SQL->Clear();
        Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
        Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) ");
        Query1->SQL->Add("FROM DWJF JOIN CBDW ON DWJF.DWBH=CBDW.DWBH");
        Query1->SQL->Add("WHERE DWJF.JFYF<=:JFYF AND CBDW.DWXZ=:DWXZ");
        Query1->SQL->Add("AND RYFL='A' AND JFLB='2'");
        Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
        Query1->ParamByName("DWXZ")->AsString=ss.OlePropertyGet("Cells",5,i);
        Query1->Open();
        ss.OlePropertySet("Cells",6,i,Query1->Fields->Fields[0]->AsString);
        ss.OlePropertySet("Cells",7,i,Query1->Fields->Fields[2]->AsString);
        Query1->Close();Query1->SQL->Clear();
        Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
        Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) ");
        Query1->SQL->Add("FROM DWJF JOIN CBDW ON DWJF.DWBH=CBDW.DWBH");
        Query1->SQL->Add("WHERE DWJF.JFYF<=:JFYF AND CBDW.DWXZ=:DWXZ");
        Query1->SQL->Add("AND RYFL='0' AND JFLB='2'");
        Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
        Query1->ParamByName("DWXZ")->AsString=ss.OlePropertyGet("Cells",5,i);
        Query1->Open();
        ss.OlePropertySet("Cells",8,i,Query1->Fields->Fields[1]->AsString);
        Query1->Close();Query1->SQL->Clear();
        Query1->SQL->Add("SELECT SUM(DBSR),SUM(CASE WHEN DBSR>DWJF.DBGRYJ THEN DWJF.DBGRYJ ELSE DBSR END),");
        Query1->SQL->Add("SUM(CASE WHEN DBSR>DWJF.DBGRYJ+DWJF.DBDWYJ THEN DWJF.DBDWYJ WHEN DBSR>DWJF.DBGRYJ THEN DBSR-DWJF.DBGRYJ ELSE 0 END) ");
        Query1->SQL->Add("FROM DWJF JOIN CBDW ON DWJF.DWBH=CBDW.DWBH");
        Query1->SQL->Add("WHERE DWJF.JFYF<=:JFYF AND CBDW.DWXZ=:DWXZ");
        Query1->SQL->Add("AND RYFL='2' AND JFLB='2'");
        Query1->ParamByName("JFYF")->AsInteger=UpDown1->Position;
        Query1->ParamByName("DWXZ")->AsString=ss.OlePropertyGet("Cells",5,i);
        Query1->Open();
        ss.OlePropertySet("Cells",9,i,Query1->Fields->Fields[1]->AsString); }
    //----------------------------------------  上年结余
    Query1->Close();Query1->SQL->Clear();
    Query1->SQL->Add("SELECT SUM(DBSR) FROM DWJF ");
    Query1->SQL->Add("WHERE JFYF=0");
    Query1->SQL->Add("AND RYFL='A' AND JFLB='2'");
    Query1->Open();
    ss.OlePropertySet("Cells",20,8,Query1->Fields->Fields[0]->AsString);
    for(int i=2;i<8;i++) {
        Query1->Close();Query1->SQL->Clear();
        Query1->SQL->Add("SELECT SUM(DBSR)");
        Query1->SQL->Add("FROM DWJF JOIN CBDW ON DWJF.DWBH=CBDW.DWBH");
        Query1->SQL->Add("WHERE DWJF.JFYF=0 AND CBDW.DWXZ=:DWXZ");
        Query1->SQL->Add("AND RYFL='A' AND JFLB='2'");
        Query1->ParamByName("DWXZ")->AsString=ss.OlePropertyGet("Cells",5,i);
        Query1->Open();
        ss.OlePropertySet("Cells",20,i,Query1->Fields->Fields[0]->AsString); }
//    for(int i=2;i<=15;i++)  ss.OlePropertySet("Cells",36,i,"=SUM(R6C"+IntToStr(i)+",R20C"+IntToStr(i)+")");
    ss=Unassigned;
    ee=Unassigned;
 

Answer 6

OlePropertySet
方法不被支持!!!
这是为什么？

Answer 7

给你一个我曾经作过的生成Excel文件的函数
Function CreateExcelFile(MyTable1:TDataSet;MyTable2:TDataSet):string;
 //生成Excel文件函数   Created By JDF on 2001-10-24
var
   FileName:string;
   eclApp,WorkBook:Variant;
   i,j,m,n,ColCount1,RowCount1,ColCount2,RowCount2:integer;
   SaveDialogExcel:TSaveDialog;    //存储对话框
   Present: TDateTime;
   Year, Month, Day, Hour, Min, Sec, MSec: Word;begin
   Present:= Now;
   DecodeDate(Present, Year, Month, Day);
   DecodeTime(Present, Hour, Min, Sec, MSec);   SaveDialogExcel:=TSaveDialog.Create(nil);
   if not DirectoryExists('ExcelFile') then
      MkDir('ExcelFile');
   Try
      eclApp:=CreateOleObject('Excel.Application');
      WorkBook:=CreateOleObject('Excel.Sheet');
   Except
      ShowMessage('您的机器里没有安装Microsoft Excel！');
      Exit;
   End; //try...Except
   if not MyTable1.Active then
      MyTable1.Open;
   if not MyTable2.Active then
      MyTable2.Open;
   ColCount1 := MyTable1.FieldCount;    //取得列数
   RowCount1 := MyTable1.RecordCount;   //取得行数
   ColCount2 := MyTable2.FieldCount;    //取得列数
   RowCount2 := MyTable2.RecordCount;   //取得行数
   Try
      workBook:=eclApp.workBooks.Add;
      i:=1;
      j:=1;
      for j := 1 to ColCount1 do
           eclApp.Cells(1,j):= MyTable1.Fields[j-1].DisplayName;  // MyDBGrid1.Columns[j-1].Title.Caption;
      j:=1;
      while not MyTable1.Eof do
      begin
          for j:=1 to ColCount1 do
             eclApp.Cells(i+1,j) := MyTable1.Fields[j-1].AsString;
         i:=i+1;
         MyTable1.Next;
      end;
      m:=RowCount1+3;
      for n := 1 to ColCount2 do
           eclApp.Cells(m,n):=MyTable2.Fields[n-1].DisplayName;
      while not MyTable2.Eof do
      begin
          for n:=1 to ColCount2 do
             eclApp.Cells(m+1,n) :=MyTable2.Fields[n-1].AsString;
         M:=m+1;
         MyTable2.Next;
      end;
      FileName := 'SalesList_'+inttostr(Year)+inttostr(Month)+inttostr(Day)+inttostr(Hour)+inttostr(Min)+inttostr(Sec)+inttostr(MSec);
      FileName := ExtractFilePath(Application.ExeName)+'ExcelFile\' + FileName + '.xls';
      SaveDialogExcel.Title:='保存为文本文件';
      SaveDialogExcel.Filter:='文本文件[*.xls]|*.xls';   //过滤条件
      SaveDialogExcel.DefaultExt:='*.xls';         //后缀
      SaveDialogExcel.FileName:=FileName;         //缺省文件名
      SaveDialogExcel.InitialDir:=GetCurrentDir;  //缺省目录
      if  SaveDialogExcel.Execute then
      begin
         FileName:=SaveDialogExcel.FileName;    //记录下文件名
          WorkBook.Saveas(FileName);
      end;
      WorkBook.Close;
      eclApp.Quit;  //退出Excel Application
   Except
      ShowMessage('生成Excel文件失败!');
      WorkBook.Close;
      eclApp.Quit;
   End;
End; 

Answer 8

问题解决了，对象没有处于Active状态的缘故。
谢谢。现在给分。