如何使用_Worksheet这个dual 接口

[ali_love]

已经正确得到指向接口Sheets(IID_Sheets)的指针，再下一个步骤获得指向"Sheet1"的指针，代码如下
ExcelWorksheet *iiSheet = 0;
VARIANT indx;
indx.vt = VT_I2; indx.iVal = 1;
iiSheets->get_Item(indx, (IDispatch**)&iDisp);

然后调用接口ExcelWorksheet中的方法都提示错误"...Access violation at address.....in module oleaut32.dll..."，不知道为什么?

[ali_love]

//以下是完整的代码

OleInitialize(NULL);

ExcelApplication *iiExcelApp = 0;
_Worksheet *iiExcelSheet= 0;
HRESULT hr = CoCreateInstance(CLSID_ExcelApplication, NULL, CLSCTX_LOCAL_SERVER, IID__Application, (void**)&iiExcelApp);

Workbooks* iiWorkBooks = 0;
iiExcelApp->get_Workbooks(&iiWorkBooks);

ExcelWorkbook* iiWorkBook = 0;
iiWorkBooks->Add(TNoParam(), 1, &iiWorkBook);

Sheets* iiSheets = 0;
iiWorkBook->get_Sheets(&iiSheets);

ExcelWorksheet *iiSheet = 0;

VARIANT indx;
indx.vt = VT_I2; indx.iVal = 1;
iiSheets->get_Item(indx, (IDispatch**)&iiSheet);

Range *iiCells = 0;
iiSheet->get_Cells(&iiCells); //执行到这里，就出错了

VARIANT rowIndex, colIndex, value;
rowIndex.vt = VT_I2; rowIndex.iVal = 1;
colIndex.vt = VT_I2; colIndex.iVal = 1;
value.vt = VT_I2; value.iVal = 3;
iiCells->set_Item(rowIndex, colIndex, value);

iiExcelApp->Quit();
iiCells->Release();
iiSheet->Release();
iiSheets->Release();
iiWorkBook->Release();
iiWorkBooks->Release();
iiExcelApp->Release();

OleUninitialize();

[ali_love]

编译是通过，但执行时，执行到 iiSheet->get_Cells(&iiCells); 就出错了

[daydayup234]

一般好象COM访问有3种方法—— 换Variant这种
类似地如下：

#ifndef Unit1H
#define Unit1H
#include <Classes.hpp>
#include <Controls.hpp>
#include <Grids.hpp>
#include <StdCtrls.hpp>
//---------------------------------------------------------------------------
class TForm1 : public TForm
{
__published:
TStringGrid *StringGrid1;
TButton *Button1;
void __fastcall Button1Click(TObject *Sender);
public:
__fastcall TForm1(TComponent* Owner);
};
//---------------------------------------------------------------------------
extern PACKAGE TForm1 *Form1;
//---------------------------------------------------------------------------
#endif


#include <vcl.h>
#pragma hdrstop
#include "Unit1.h"
#pragma package(smart_init)
#pragma resource "*.dfm"
TForm1 *Form1;
//---------------------------------------------------------------------------
__fastcall TForm1::TForm1(TComponent* Owner)
: TForm(Owner)
{
StringGrid1->Align=alClient;
StringGrid1->ColCount=5;
StringGrid1->RowCount=30;
StringGrid1->FixedCols=0;
StringGrid1->FixedRows=0;
for(int i=0;i<StringGrid1->ColCount;i++)
for(int j=0;j<StringGrid1->RowCount;j++)
StringGrid1->Cells[i][j]=IntToStr(i+1)+"列" + IntToStr(j+1) +"行";
}
//---------------------------------------------------------------------------
void __fastcall TForm1::Button1Click(TObject *Sender)
{
AnsiString yourXls;
yourXls=ExtractFilePath(Application->ExeName) +"theXLS.xls";
if(!FileExists(yourXls)){ShowMessage("当前目录下theXLS.xls不存在文件");return;}

Variant vExcelApp;
try {vExcelApp = Variant::CreateObject("Excel.Application"); }
catch(...){ShowMessage("启动 Excel 出错, 大概是没装Excel");return;}
vExcelApp.OlePropertySet("Visible", false); // 隐藏Excel界面
vExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Close");
vExcelApp.OlePropertyGet("WorkBooks").OleProcedure("Open",yourXls.c_str());
vExcelApp.OlePropertySet("Visible", true);

Variant vSheet;
vSheet = vExcelApp.OlePropertyGet("ActiveWorkbook").OlePropertyGet("Sheets",2);//选择第2个工作表
vSheet.OleProcedure("Select");
//开始续写，事先在theXLS.xls 的Sheet2上 保存4行5列数据
//1 2 3 4 5
//6 7 8 9 10
//11 12 13 14 15
//16 17 18 19 20

int therow=vSheet.OlePropertyGet("UsedRange").OlePropertyGet("Rows").OlePropertyGet("Count"); //行数

for(int i=0;i<StringGrid1->RowCount;i++)
for(int j=0;j<StringGrid1->ColCount;j++)
{
AnsiString tmpS;
tmpS=StringGrid1->Cells[i][j];
vSheet.OlePropertyGet("Cells",i+1+therow,j+1).OlePropertySet("Value",tmpS.c_str());
}

ShowMessage("即将退出Excel");
vExcelApp.Exec(Procedure("Quit"));
vSheet=Unassigned;
}
//---------------------------------------------------------------------------

[ali_love]

variant执行速度比较慢

[ali_love]

ExcelWorksheet *iiSheet = 0;

VARIANT indx;
indx.vt = VT_I2; indx.iVal = 1;
iiSheets->get_Item(indx, (IDispatch**)&iiSheet);

get_Item返回的是一个IDispatch接口，会不会是这个出了问题，不能访问iiSheet里提供的方法?

[daydayup234]

加#include "Excel_2K_SRVR.h"
编译通过了
没有这个错提示啊

[daydayup234]

↑

[daydayup234]

跟LZ探讨下
variant是慢
但是BCB好象建议用智能接口访问，
你这个不是吧

[daydayup234]

建议
iiSheets=iiWorkBook->get_Worksheets(); //你原来是iiWorkBook->get_Sheets(&iiSheets);
_WorksheetPtr iiSheet;//你原来是ExcelWorksheet
改下看