从Excel读取数据到自己的程序中

[9616096]

我有如下程序：

_Application ExcelApp;
Workbooks wbsMyBooks;
_Workbook wbMyBook;
Worksheets wssMysheets;
_Worksheet wsMysheet;
Range rgMyRge;

if (!ExcelApp.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败!");
exit(1);
}
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);

wbMyBook.AttachDispatch(wbsMyBooks.Open(_variant_t ("F:\\ExcelTest\\book.xls")));

wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);
wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t
("Sheet1")),true);

rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
_variant_t txt(rgMyRge.GetItem(_variant_t((long)1),_variant_t((long)1)));


CString info;
info.Format("%s",txt.pbstrVal);
MessageBox(info);

rgMyRge.ReleaseDispatch();
wsMysheet.ReleaseDispatch();
wssMysheets.ReleaseDispatch();
wbMyBook.ReleaseDispatch();
wbsMyBooks.ReleaseDispatch();
ExcelApp.ReleaseDispatch();
不能得出正确结果，请问高手错在什么地方？

[partime]

是啊，错在什么地方！
自己调试啊。
怎么什么都这样依赖别人
你做什么的阿

[Toil]

1、重载
LPDISPATCH Workbooks::Add() // added
{
LPDISPATCH result;
InvokeHelper(0xb5, DISPATCH_METHOD, VT_DISPATCH,
(void*)&result, NULL);
return result;
}

2、重载

LPDISPATCH _Worksheet::GetRange(const VARIANT& Cell)
{
LPDISPATCH result;
static BYTE parms[] =VTS_VARIANT;
InvokeHelper(0xc5, DISPATCH_PROPERTYGET,
VT_DISPATCH, (void*)&result, parms,&Cell);
return result;
}
///*《技术内幕》的例子：

LPDISPATCH pDisp;
LPUNKNOWN pUnk;
CLSID clsid;
TRACE("ENTER");
BeginWaitCursor();
::CLSIDFromProgID(L"Excel.Application.8",&clsid);
if(::GetActiveObject(clsid,NULL,&pUnk)==S_OK)
{
VERIFY(pUnk->QueryInterface(
IID_IDispatch,(void**)&pDisp)==S_OK);
m_app.AttachDispatch(pDisp);
pUnk->Release();
TRACE("ATTACHED");
}
else
{
if(!m_app.CreateDispatch("Excel.Application.8"))
{
AfxMessageBox("Excel 97
program not found");
}
TRACE("CREATEED");
}
EndWaitCursor();

LPDISPATCH pRange, pWorkbooks;

/*
CWnd* pWnd = CWnd::FindWindow("XLMAIN", NULL);
if (pWnd != NULL) {
TRACE("Excel window found\n");
pWnd->ShowWindow(SW_SHOWNORMAL);
pWnd->UpdateWindow();
pWnd->BringWindowToTop();
}
*/
m_app.SetSheetsInNewWorkbook(1);

VERIFY(pWorkbooks = m_app.GetWorkbooks());
m_workbooks.AttachDispatch(pWorkbooks);

LPDISPATCH pWorkbook = NULL;
if (m_workbooks.GetCount() == 0) {
// Add returns a Workbook pointer, but we
// don't have a Workbook class
pWorkbook = m_workbooks.Add(); // Save the pointer for
// later release
}
LPDISPATCH pWorksheets = m_app.GetWorksheets();
ASSERT(pWorksheets != NULL);
m_worksheets.AttachDispatch(pWorksheets);
LPDISPATCH pWorksheet = m_worksheets.GetItem(COleVariant
((short) 1));

m_worksheet.AttachDispatch(pWorksheet);
m_worksheet.Select();

VERIFY(pRange = m_worksheet.GetRange(COleVariant("A1")));
m_range[0].AttachDispatch(pRange);

VERIFY(pRange = m_worksheet.GetRange(COleVariant("A2")));
m_range[1].AttachDispatch(pRange);

VERIFY(pRange = m_worksheet.GetRange(COleVariant("A3")));
m_range[2].AttachDispatch(pRange);

VERIFY(pRange = m_worksheet.GetRange(COleVariant("A3"),
COleVariant("C5")));
m_range[3].AttachDispatch(pRange);

VERIFY(pRange = m_worksheet.GetRange(COleVariant("A6")));
m_range[4].AttachDispatch(pRange);

m_range[4].SetValue(COleVariant(COleDateTime(1998, 4,
24, 15, 47, 8)));
// retrieve the stored date and print it as a string
COleVariant vaTimeDate = m_range[4].GetValue();
TRACE("returned date type = %d\n", vaTimeDate.vt);
COleVariant vaTemp;
vaTemp.ChangeType(VT_BSTR, &vaTimeDate);
CString str = vaTemp.bstrVal;
TRACE("date = %s\n", (const char*) str);

m_range[0].SetValue(COleVariant("test string"));

COleVariant vaResult0 = m_range[0].GetValue();
if (vaResult0.vt == VT_BSTR) {
CString str = vaResult0.bstrVal;
TRACE("vaResult0 = %s\n", (const char*) str);
}

m_range[1].SetValue(COleVariant(3.14159));

COleVariant vaResult1 = m_range[1].GetValue();
if (vaResult1.vt == VT_R8) {
TRACE("vaResult1 = %f\n", vaResult1.dblVal);
CString info;
info.Format("%f",vaResult1.dblVal);
MessageBox(info);
}

m_range[2].SetFormula(COleVariant("=$A2*2.0"));

COleVariant vaResult2 = m_range[2].GetValue();
if (vaResult2.vt == VT_R8) {
TRACE("vaResult2 = %f\n", vaResult2.dblVal);
}

COleVariant vaResult2a = m_range[2].GetFormula();
if (vaResult2a.vt == VT_BSTR) {
CString str = vaResult2a.bstrVal;
TRACE("vaResult2a = %s\n", (const char*) str);
}

m_range[3].FillRight();
m_range[3].FillDown();

// cleanup
if (pWorkbook != NULL) {
pWorkbook->Release();
}
}

[wabc]

为什么不把Excel文件作为数据库处理哪？

[9616096]

OLE DB?

[bzshow]

自已看吧.
COdb db;
CString strLevel,strCode,strIndex,str,AddStr;
if (SUCCEEDED(db.Open(CString("xxxx/xxxx@xxxx"))))
TRACE("连接数据库成功");
else
TRACE("连接数据库失败!");

try
{

_Application ExcelApp;
Workbooks wbsMyBooks;
_Workbook wbMyBook;
Worksheets wssMysheets;
_Worksheet wsMysheet;
LPDISPATCH lpDisp;
Range rgMyRge;
int nCat;
CString str,strCode,strCat,strIndex;
VARIANT variant;
char charA = 'A';


COleVariant covTrue((short)TRUE), covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);


if (!ExcelApp.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败!");
exit(1);
}

CBlkFile BlkFile;
BlkFile.SetPath("dat\\xxx.ini");
BlkFile.ReadFile();
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);
lpDisp = wbsMyBooks.Open("D:\\projects\\CalIndex\\dat\\hy.xls", // Test.xls is a workbook.
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional ); // Return Workbook's IDispatch pointer.

ASSERT(lpDisp);
wbMyBook.AttachDispatch(lpDisp);

lpDisp = wbMyBook.GetSheets();
ASSERT(lpDisp);
wssMysheets.AttachDispatch(lpDisp);

lpDisp = wssMysheets.GetItem( COleVariant((short)(1)) );

ASSERT(lpDisp);
wsMysheet.AttachDispatch(lpDisp);

long StartYear,EndYear;
int nIndX,nIndY,nIndexTemp;


//UPDATE PROFILE SET HY6 = 'AAAAAA' WHERE GSDM = '000001';

for (nIndX = 2; nIndX <= 59; nIndX++)
{
AddStr = "D";
str.Format("C%d",nIndX);
lpDisp = wsMysheet.GetRange(COleVariant(LPCTSTR(str)), COleVariant(LPCTSTR(str)));
ASSERT(lpDisp);
rgMyRge.AttachDispatch(lpDisp);
variant = rgMyRge.GetValue();
strLevel = (LPCTSTR)_bstr_t(variant);

while(1)
{
strCode.Format("%s%d",AddStr,nIndX);
lpDisp = wsMysheet.GetRange(COleVariant(LPCTSTR(strCode)), COleVariant(LPCTSTR(strCode)));
ASSERT(lpDisp);
rgMyRge.AttachDispatch(lpDisp);
variant = rgMyRge.GetValue();
strCode = (LPCTSTR)_bstr_t(variant);
strCode.TrimRight();
strCode.TrimLeft();
if (!strCode.GetLength())
break;
str.Format("UPDATE PROFILE SET HY6 = \'%s\' WHERE GSDM = \'%s\'",strLevel,strCode);
db.Exec(str);
db.Exec("COMMIT");
AddStrToNum(AddStr);
}

}



//BlkFile.WriteFile();
rgMyRge.ReleaseDispatch();
wsMysheet.ReleaseDispatch();
wssMysheets.ReleaseDispatch();
wbMyBook.ReleaseDispatch();
wbsMyBooks.ReleaseDispatch();
ExcelApp.ReleaseDispatch();
}

catch(COleException *e)
{
char buf[1024]; // For the Try...Catch error message.
sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
}

catch(COleDispatchException *e)
{
char buf[1024]; // For the Try...Catch error message.
sprintf(buf,
"COleDispatchException. SCODE: %08lx, Description: \"%s\".",
(long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));
::MessageBox(NULL, buf, "COleDispatchException",
MB_SETFOREGROUND | MB_OK);
}

catch(...)
{
::MessageBox(NULL, "General Exception caught.", "Catch-All",
MB_SETFOREGROUND | MB_OK);
}

[wabc]

其实，我的意思是用ADO或DAO通过数据源使用SQL语句访问Excel文件