利用VC对Excel进行操作,可以对一个目录下的所有Excel表进行处理。主要代码如下:
//获取一个目录下所有Excel表名,并加入到一个数组中。 char oldPath[MAX_PATH]; getcwd(oldPath,MAX_PATH); chdir(m_strDirectory); CFileFind fileFind; BOOL bExist=FALSE; bExist=fileFind.FindFile("*.xls"); while(bExist) { bExist=fileFind.FindNextFile(); CString strTitle=fileFind.GetFileTitle(); m_strTableAll.Add(strTitle); } fileFind.Close(); chdir(oldPath); //处理所有Excel数据,并把所有数据加入到一个二维数组中 CString m_strArray[i][j] CoInitialize(NULL); int i,j; //用来循环 // 获得EXCEL的CLSID CLSID clsid; HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid); if(FAILED(hr)) { AfxMessageBox("CLSIDFromProgID() 函数调用失败!"); return; } // 创建实例 IDispatch *pXlApp; hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp); if(FAILED(hr)) { AfxMessageBox("请检查是否已经安装EXCEL!"); return; } // 显示,将Application.Visible属性置1 /VARIANT x; x.vt = VT_I4; x.lVal = 1; AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x); // 获取Workbooks集合 IDispatch *pXlBooks; { VARIANT result; VariantInit(&result); AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0); pXlBooks = result.pdispVal; } CString strName; //Excel表完整路径 CString strTmp; //临时变量,保存单元格数据中的CString型 double dblTmp; //临时变量,保存单元格数据中的double型 //用来保存信息的数组 VARIANT arr; arr.vt = VT_ARRAY | VT_VARIANT; SAFEARRAYBOUND sab[2]; sab[0].lLbound = 1; sab[0].cElements = 40; sab[1].lLbound = 1; sab[1].cElements = 16; arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab); int tableNum; int nCount=m_DataConn.m_strTableAll.GetSize(); for(tableNum=0;tableNum<nCount;tableNum++) { strName.Format("%s\\%s",m_DataConn.m_strDirectory,m_DataConn.m_strTableAll.GetAt(tableNum)); // 调用Workbooks.Open()方法,打开一个已经存在的Workbook IDispatch *pXlBook; { VARIANT parm; parm.vt = VT_BSTR; // parm.bstrVal = ::SysAllocString(L"''strName''"); parm.bstrVal=strName.AllocSysString(); VARIANT result; VariantInit(&result); AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open", 1,parm); pXlBook = result.pdispVal; } // 初始化数组内容 /* for(int i=1; i<=15; i++) { for(int j=1; j<=15; j++) { VARIANT tmp; tmp.vt = VT_BSTR; wsprintfW(szTmp,L"%i,%i",i,j); tmp.bstrVal = SysAllocString(szTmp); // 添加数据到数组中 long indices[] = {i,j}; SafeArrayPutElement(arr.parray, indices, (void *)&tmp); } } */ // 从Application.ActiveSheet属性获得Worksheet对象 IDispatch *pXlSheet; { VARIANT result; VariantInit(&result); AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0); pXlSheet = result.pdispVal; } // 选择一个16x40大小的Range IDispatch *pXlRange; { VARIANT parm; parm.vt = VT_BSTR; parm.bstrVal = ::SysAllocString(L"A1:P40"); VARIANT result; VariantInit(&result); AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm); VariantClear(&parm); pXlRange = result.pdispVal; } // 用这个Range读取数据 AutoWrap(DISPATCH_PROPERTYGET, &arr, pXlRange, L"Value",0); for(i=1; i<=40; i++) { for(j=1; j<=16; j++) { VARIANT tmp; //tmp.vt = VT_BSTR; // 添加数据到数组中 long indices[] = {i,j}; SafeArrayGetElement(arr.parray, indices, (void *)&tmp); if(tmp.vt ==VT_BSTR) { strTmp=tmp.bstrVal; } else if(tmp.vt==VT_R8) { dblTmp=tmp.dblVal; strTmp.Format("%f",dblTmp); } else if(tmp.vt=VT_NULL) { strTmp=""; } _bstr_t str1=strTmp; WCHAR *str2=str1; pDoc->m_strArray[tableNum*40+i][j]=str2; } } AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"Close", 0); VariantClear(&arr); pXlRange->Release(); pXlSheet->Release(); pXlBook->Release(); } // 退出,调用Application.Quit()方法 // 释放所有的接口以及变量 AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0); pXlBooks->Release(); pXlApp->Release(); // 注销COM库 CoUninitialize(); //**********************// // AutoWrap 函数的正体 // 先声明:这个函数不是偶写的 // AutoWrap() - Automation helper function... HRESULT CExcelView::AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) { // Begin variable-argument list... va_list marker; va_start(marker, cArgs); if(!pDisp) { AfxMessageBox("NULL IDispatch passed to AutoWrap()"); _exit(0); } // Variables used... DISPPARAMS dp = { NULL, NULL, 0, 0 }; DISPID dispidNamed = DISPID_PROPERTYPUT; DISPID dispID; HRESULT hr; char buf[200]; char szName[200]; // Convert down to ANSI WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL); // Get DISPID for name passed... hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID); if(FAILED(hr)) { sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr); AfxMessageBox(buf); _exit(0); return hr; } // Allocate memory for arguments... VARIANT *pArgs = new VARIANT[cArgs+1]; // Extract arguments... for(int i=0; i<cArgs; i++) { pArgs[i] = va_arg(marker, VARIANT); } // Build DISPPARAMS dp.cArgs = cArgs; dp.rgvarg = pArgs; // Handle special-case for property-puts! if(autoType & DISPATCH_PROPERTYPUT) { dp.cNamedArgs = 1; dp.rgdispidNamedArgs = &dispidNamed; } // Make the call! hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL); if(FAILED(hr)) { sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr); AfxMessageBox(buf); _exit(0); return hr; } // End variable-argument section... va_end(marker); delete [] pArgs; return hr; }
文章出处:http://www.diybl.com/course/3_program/vc/vc_js/2008114/96483_2.html