VC中实现读取Excel表数据总结
利用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;
}