以下是.h文件
#pragma once
class CReadWriteExcel
{
private:
long CStringToLong(CString sStr);
int GetColumnWidth(CString sA,CString sB);
BOOL CheckString(CString str,CString &sLeft,CString &sRight,long &nRight);
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...);
public:
int ReadExcelStringArray(const CString strfilename,CStringArray &strArray,const CString sBeginCell,const CString sEndCell);
BOOL WriteExcelStringArray(const CString strfilename,CStringArray &strArray,long nColumn);
//BOOL SetRangValueType(int nType,const CString sBeginCell,const CString sEndCell);
};
//以下是.cpp文件
#include "stdafx.h"
#include "ReadWriteExcel.h"
//读取数据，返回列数
int CReadWriteExcel::ReadExcelStringArray(const CString strfilename,CStringArray &strArray,CString sBeginCell,CString sEndCell)
{
//检查Range输入,并计算行列
if(sBeginCell.IsEmpty()|| sEndCell.IsEmpty())
return -1;
//转成大写
sBeginCell.MakeUpper();
sEndCell.MakeUpper();
CString sBL,sBR;
long nB;
if(!CheckString(sBeginCell,sBL,sBR,nB))
return -1;
CString sEL,sER;
long nE;
if(!CheckString(sEndCell,sEL,sER,nE))
return -1;
int col=GetColumnWidth(sBL,sEL);
int row=nE-nB+1;
strArray.RemoveAll();
strArray.SetSize(row*col);
//处理所有Excel数据，并把所有数据加入到一个二维数组中
CoInitialize(NULL);
int i,j;
//用来循环
// 获得EXCEL的CLSID
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if(FAILED(hr)) {
AfxMessageBox(_T("CLSIDFromProgID() 函数调用失败!"));
return -1;
}
// 创建实例
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
AfxMessageBox(_T("请检查是否已经安装EXCEL!"));
return -1;
}
// 显示，将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 = row;
sab[1].lLbound = 1;
sab[1].cElements = col;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
int tableNum=0;
int nCount=1;
strName=strfilename;
// 调用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;
}
// 从Application.ActiveSheet属性获得Worksheet对象
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
CString strRange;
strRange=sBeginCell+_T(":")+sEndCell;
TCHAR* cRange=strRange.GetBuffer();
// 选择一个16x40大小的Range
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(cRange);
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=0;
i
for(j=0;
j
VARIANT tmp;
//tmp.vt = VT_BSTR;
// 添加数据到数组中
long indices[] = {i+1,j+1};
SafeArrayGetElement(arr.parray, indices, (void *)&tmp);
strTmp=_T("");
if(tmp.vt ==VT_BSTR)
{
strTmp=tmp.bstrVal;
}
else if(tmp.vt==VT_R8)
{
dblTmp=tmp.dblVal;
//strTmp.Format(_T("%f"),dblTmp);
//dblTmp=123;
char buffer[_CVTBUFSIZE];
_gcvt( dblTmp, 16, buffer );
if((dblTmp-(int)dblTmp)==0 && strlen(buffer)>1)
buffer[strlen(buffer)-1]=buffer[strlen(buffer)];
int i=0;
USES_CONVERSION;
LPWSTR x=A2W(buffer);
strTmp=x;
}
else if(tmp.vt==VT_DATE)
{
COleDateTime value;
value=https://www.it610.com/article/tmp.date;
strTmp=value.Format(L"%Y-%m-%d");
}
else if(tmp.vt==VT_NULL)
{
strTmp="";
}
_bstr_t str1=strTmp;
WCHAR *str2=str1;
strArray[col*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();
【VC读写EXCEL文件类（已安装excel）】// 注销COM库
CoUninitialize();
return col;
}
//**********************//
// AutoWrap 函数的正体
// 先声明：这个函数不是偶写的
// AutoWrap() - Automation helper function...
HRESULT CReadWriteExcel::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(_T("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
}
// 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;
}
//1、判断字符串是否是可输入字符
//2、判断字符串是否是合法顺序
//3、将字符和数字分开
//4、获取数字部分的值
//如A123
BOOL CReadWriteExcel::CheckString(CString str,CString &sLeft,CString &sRight,long &nRight)
{
if(str.IsEmpty())
return FALSE;
CString sAllowStr;
sAllowStr=_T("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789");
int r=-1;
//记住第一个数字字符
for(int i=0;
i {
int index=sAllowStr.Find(str[i]);
if(index==-1)
return FALSE;
if(index<26)//最后一个字母字符
r=i;
}
//没有数字字符
if(r==-1)
return FALSE;
sLeft=str.Left(r+1);
sRight=str.Right(str.GetLength()-(r+1));
//字符串部分检查
for(int i=0;
i {
int index=sAllowStr.Find(sLeft[i]);
if(index==-1 || index>25)
return FALSE;
}
//数字部分检查
for(int i=0;
i {
int index=sAllowStr.Find(sRight[i]);
if(index==-1 || index<25)
return FALSE;
}
//字符部分长度最大是2
if(sLeft.GetLength()>2)
return FALSE;
//第一个数字如果是字符
if(sAllowStr.Find(sRight[0])==0)
return FALSE;
//将字符串转成long
nRight=CStringToLong(sRight);
return TRUE;
}
//将字符串转成long
long CReadWriteExcel::CStringToLong(CString sStr)
{
TCHAR c=sStr[sStr.GetLength()];
TCHAR *cc=&c;
#ifdef UNICODE
return wcstol(sStr.GetBuffer(),&cc,10);
#else
return atol(sStr.GetBuffer());
#endif
}
//根据表头字母串差，计算表头长度
//如：AA BB
int CReadWriteExcel::GetColumnWidth(CString sA,CString sB)
{
CString sAllowStr;
sAllowStr=_T("ABCDEFGHIJKLMNOPQRSTUVWXYZ");
int a,b;
if(sA.GetLength()==1)
a=sAllowStr[sAllowStr.Find(sA[0])]-sAllowStr[0]+1;
else
a=(sAllowStr[sAllowStr.Find(sA[0])]-sAllowStr[0]+1)*26+(sAllowStr[sAllowStr.Find(sA[1])]-sAllowStr[0]+1);
if(sB.GetLength()==1)
b=sAllowStr[sAllowStr.Find(sB[0])]-sAllowStr[0]+1;
else
b=(sAllowStr[sAllowStr.Find(sB[0])]-sAllowStr[0]+1)*26+(sAllowStr[sAllowStr.Find(sB[1])]-sAllowStr[0]+1);
return (b-a)+1;
}
BOOL CReadWriteExcel::WriteExcelStringArray(const CString strfilename,CStringArray &strArray,long nColumn)
{
if(strArray.GetSize()
if(strArray.GetSize()%nColumn!=0)
return FALSE;
int col=nColumn;
int row=(int)strArray.GetSize()/nColumn;
//处理所有Excel数据，并把所有数据加入到一个二维数组中
CoInitialize(NULL);
int i,j;
//用来循环
// 获得EXCEL的CLSID
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if(FAILED(hr)) {
AfxMessageBox(_T("CLSIDFromProgID() 函数调用失败!"));
return -1;
}
// 创建实例
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
AfxMessageBox(_T("请检查是否已经安装EXCEL!"));
return -1;
}
// 显示，将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;
}
//用来保存信息的数组
VARIANT arr;
arr.vt = VT_ARRAY | VT_VARIANT;
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1;
sab[0].cElements = row;
sab[1].lLbound = 1;
sab[1].cElements = col;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
CString strTmp;
for(i=0;
i
for(j=0;
j
strTmp=strArray[i*nColumn+j];
VARIANT tmp;
tmp.vt = VT_BSTR;
tmp.bstrVal=strTmp.AllocSysString();
// 添加数据到数组中
long indices[] = {i+1,j+1};
SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
}
}
int tableNum=0;
int nCount=1;
CString strName;
//Excel表完整路径
strName=strfilename;
// 调用Workbooks.Open()方法，打开一个已经存在的Workbook
IDispatch *pXlBook;
{
VARIANT parm;
parm.vt = VT_BSTR;
CString pathname=GetCurrentPath();
pathname=pathname+_T("template.xls");
parm.bstrVal=pathname.AllocSysString();
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Open", 1,parm);
//AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 1,parm);
pXlBook = result.pdispVal;
}
// 从Application.ActiveSheet属性获得Worksheet对象
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
}
CString strRange,sBeginCell,sEndCell;
sEndCell.Format(_T("%d"),row);
sBeginCell=_T("A1");
TCHAR c1,c2;
#ifndef UNICODE
c1='A';
c2='';
#else
c1=L'A';
c2=L'';
#endif
if(row>26)
{
c2=c1+row%26;
c1=c1+row/26;
}
sEndCell=c2+sEndCell;
sEndCell=c1+sEndCell;
strRange=sBeginCell+_T(":")+sEndCell;
TCHAR* cRange=strRange.GetBuffer();
// 选择一个colxrow大小的Range
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(cRange);
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm);
pXlRange = result.pdispVal;
}
//设置Range数据类型为文本
CString strT;
strT=_T("@");
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal=strT.AllocSysString();
AutoWrap(DISPATCH_PROPERTYPUT, 0, pXlRange, L"NumberFormatLocal",1,parm);
//设置Range值
AutoWrap(DISPATCH_PROPERTYPUT, &arr, pXlRange, L"Value",1,arr);
//保存
VARIANT parmP;
parmP.vt = VT_BSTR;
parmP.bstrVal=strName.AllocSysString();
AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"SaveAs", 1,parmP);
//关闭
AutoWrap(DISPATCH_METHOD, NULL, pXlBook, L"Close", 0);
VariantClear(&arr);
pXlRange->Release();
pXlSheet->Release();
pXlBook->Release();
//AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"SaveAs", 1,parmP);
// 退出，调用Application.Quit()方法
// 释放所有的接口以及变量
AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
pXlBooks->Release();
pXlApp->Release();
// 注销COM库
//CoUninitialize();
return col;
}
