VC automation操作Excel问题

Question

我在以下代码:        IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
中获得的总是Excel中的sheet1,若我想获得sheet2或sheet3应该怎么获取呢? 另外Excel中默认是创建sheet1,sheet2,sheet3三个,
若要增加应该用什么方法去获得呢?  另外我不用其他命名来代替sheet1,又要用什么方法呢?  以上问题我在msdn上都找不到, 请高手指点一下, 项目紧急,谢谢

Answer 1

COleVariant vtOptional((long)DISP_E_PARAMNOTFOUND,
VT_ERROR),vtTrue((short)TRUE),vtFalse((short)FALSE);
_Application ExcelApp ;


ExcelApp.CreateDispatch("excel.application");

        Workbooks  ExcelBook=ExcelApp.GetWorkbooks();

ExcelApp.SetVisible(TRUE);

    _Workbook  workBook=ExcelBook.Add(vtOptional);

    Sheets     ExcelSheet=workBook.GetSheets();
    _Worksheet workSheet;
workBook.SaveAs(COleVariant("D:\\edm\\Report\\temp\\测量结果.xls"),vtOptional,\
vtOptional,vtOptional,vtOptional,vtOptional,0,vtOptional,\
vtOptional,vtOptional,vtOptional);
    workSheet=ExcelSheet.GetItem(COleVariant((short)1)); //得到sheet1,以此类推
workSheet.SetName("Dimensional Results");//设置sheet1 名

Answer 2

msdn是找不到的，你要到EXCEL VBA编程指南才能找到相关资料。

Answer 3

Here's latest Excel 2007 Developer Reference, it would help u.
Although it only is VBA language, but it also is applied with COM reference as I'm thinking.http://msdn.microsoft.com/en-us/library/bb149081.aspx

Answer 4

CLSID clsid;
IDispatch *dispApp, *dispWorkBook, *dispSheet;
LPOLESTR dispName;
DISPID dispID = 0, dispidNamed = 0;
DISPPARAMS dispParams = { 0 };
VARIANT vResult = { 0 }, vValue = { 0 };
CLSIDFromProgID(L"Excel.Application", &clsid);
CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (LPVOID *)&dispApp);

//WorkBook = App.WorkBooks
dispName = L"WorkBooks";
dispApp->GetIDsOfNames(IID_IDispatch, &dispName, 1, 0, &dispID);
dispParams.cArgs = 0;
dispParams.rgvarg = NULL;
dispParams.cNamedArgs = 0;
dispParams.rgdispidNamedArgs = NULL;
dispApp->Invoke(dispID, IID_NULL, 0, DISPATCH_PROPERTYGET, &dispParams, &vResult, NULL, NULL);
dispWorkBook = vResult.pdispVal; //WorkBook = WorkBook.Add
dispName = L"Add";
dispWorkBook->GetIDsOfNames(IID_IDispatch, &dispName, 1, 0, &dispID);
dispParams.cArgs = 0;
dispParams.rgvarg = NULL;
dispParams.cNamedArgs = 0;
dispParams.rgdispidNamedArgs = NULL;
dispWorkBook->Invoke(dispID, IID_NULL, 0, DISPATCH_METHOD, &dispParams, &vResult, NULL, NULL);
dispWorkBook->Release();
dispWorkBook = vResult.pdispVal; //Sheet = WorkBook.Sheets["Sheet2"]
dispName = L"Sheets";
dispWorkBook->GetIDsOfNames(IID_IDispatch, &dispName, 1, 0, &dispID);
vValue.vt = VT_BSTR;
vValue.bstrVal = SysAllocString(L"Sheet2");
dispParams.cArgs = 1;
dispParams.rgvarg = &vValue;
dispParams.cNamedArgs = 0;
dispParams.rgdispidNamedArgs = NULL;
dispWorkBook->Invoke(dispID, IID_NULL, 0, DISPATCH_PROPERTYGET, &dispParams, &vResult, NULL, NULL);
SysFreeString(vValue.bstrVal);
dispSheet = vResult.pdispVal; //Sheet.Name = "工作表"
dispName = L"Name";
dispSheet->GetIDsOfNames(IID_IDispatch, &dispName, 1, 0, &dispID);
dispidNamed = DISPID_PROPERTYPUT;
vValue.vt = VT_BSTR;
vValue.bstrVal = SysAllocString(L"工作表");
dispParams.cArgs = 1;
dispParams.rgvarg = &vValue;
dispParams.cNamedArgs = 1;
dispParams.rgdispidNamedArgs = &dispidNamed;
dispSheet->Invoke(dispID, IID_NULL, 0, DISPATCH_PROPERTYPUT, &dispParams, &vResult, NULL, NULL);
SysFreeString(vValue.bstrVal); //App.Visible = TRUE
dispName = L"Visible";
dispApp->GetIDsOfNames(IID_IDispatch, &dispName, 1, 0, &dispID);
dispidNamed = DISPID_PROPERTYPUT;
vValue.vt = VT_BOOL;
vValue.boolVal = TRUE;
dispParams.cArgs = 1;
dispParams.rgvarg = &vValue;
dispParams.cNamedArgs = 1;
dispParams.rgdispidNamedArgs = &dispidNamed;
dispApp->Invoke(dispID, IID_NULL, 0, DISPATCH_PROPERTYPUT, &dispParams, &vResult, NULL, NULL); dispSheet->Release();
dispWorkBook->Release();
dispApp->Release();
大概可以这样用

Answer 5

感谢大家的帮助, 问题已经解决了,直接用C++代码来写操作代码不是很好写. 所以我采用的方法是VBA写好代码,再用工具b2cse.exe(从微软网站下)转换成C++代码,这种方法简单方便.