VC++操作Excel

1、必须先安装Excel；

2、在工程中导入Excel API：
ClassWizar->Add Class->选择excel.exe->选中全部->确定
自动添加excel.h、excel.cpp到工程

3、Ole初始化：
在InitInstance()函数，添加：AfxOleInit();

4、创建Excel模板文件：
新建一个空的xls文件，改名为tpl.xls，放到运行目录下。
API新建Excel文件时会以tpl.xls为模板。

5、读取Excel文件：

bool CMainFrame::LoadExcelFile(const char* pszExcelFile)
{
if (!pszExcelFile)
{
return false;
}

COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CString sValue = “”;
_Application objApp;
Workbooks objBooks;
_Workbook objBook;
Worksheets objSheets;
_Worksheet objSheet;
Range objRange;
VARIANT vRet;
// 打开Excel文件
objApp.CreateDispatch(“Excel.Application”);
objBooks = objApp.GetWorkbooks();
objBook = objBooks.Open(pszExcelFile,  VOptional, VOptional, VOptional, VOptional,  VOptional, VOptional, VOptional, VOptional,  VOptional, VOptional, VOptional, VOptional,  VOptional, VOptional);
objSheets = objBook.GetWorksheets();
objSheet = objSheets.GetItem(COleVariant((short)1));

objRange = objSheet.GetUsedRange();
// 取行列数
vRet = objRange.GetValue(VOptional);

COleSafeArray oleSA(vRet);
long lNumRows;
long lNumCols;

oleSA.GetUBound(1, &lNumRows);
oleSA.GetUBound(2, &lNumCols);

if (lNumRows <= 1)
{
AfxMessageBox(“没有数据！”);
return true;
}

long index[2];
VARIANT vValue;
// 读取数据
for(int i=1; i<=lNumRows; ++i)
{
for(int j=1; j<=lNumCols; ++j)
{
sValue = “”;
index[0] = i;
index[1] = j;
oleSA.GetElement(index, &vValue);
switch(vValue.vt)
{
case VT_R8:
{
// 数字
sValue.Format(“%1.1f”,vValue.dblVal);
break;
}
case VT_BSTR:
{
// 文字
sValue=(CString)vValue.bstrVal;
break;
}
case VT_DATE:
{
// 日期
COleDateTime odt = COleDateTime(vValue.date);

sValue = odt.Format(“%Y-%m-%d %H:%M:%S”);
}
default:
{
break;
}
}
sValue.TrimLeft();
sValue.TrimRight();
}
}
// 关闭文件
objRange.Clear();
objBook.Close(COleVariant((short)FALSE),VOptional,VOptional);
objBooks.Close();
objApp.Quit();

return true;
}

6、新建、写入Excel文件：

bool CMainFrame::SaveExcel()
{
// 取模板文件
char szMainPath[MAX_PATH];

if (!GetModuleFileName(NULL, szMainPath, sizeof(szMainPath)))
{
return false;
}

char *pDest = strrchr(szMainPath, ‘\\’);

if (!pDest)
{
return false;
}
pDest[0] = ‘\0’;

if (szMainPath[strlen(szMainPath) – 1] != ‘\\’)
{
strcat(szMainPath, “\\tpl.xls“);
}
else
{
strcat(szMainPath, “tpl.xls”);
}
szMainPath[sizeof(szMainPath) – 1] = ‘\0’;
// 取新文件名
CFileDialog mFileDlg(FALSE, NULL,NULL,
OFN_ALLOWMULTISELECT,
_T(“Excel Files (*.xls)|*.xls|Excel2007 Files (*.xlsx)|*.xlsx|All Files (*.*)|*.*||”),
AfxGetMainWnd());
CString strPathName;

if (mFileDlg.DoModal ()==IDOK)
{
POSITION mPos = mFileDlg.GetStartPosition();

strPathName = mFileDlg.GetNextPathName(mPos);
}
else
{
return false;
}
strPathName.MakeLower();
if (strPathName.Find(“.xls”) == -1 && strPathName.Find(“.xlsx”))
{
strPathName += “.xls”;
}
// 打开新文件
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
CString sValue=””;
_Application objApp;
_Workbook objBook;
Workbooks objBooks;
Worksheets objSheets;
_Worksheet objSheet;
Range objRange;

objApp.SetAlertBeforeOverwriting(FALSE);
objApp.SetDisplayAlerts(FALSE);

objApp.CreateDispatch(“Excel.Application”);
objBooks = objApp.GetWorkbooks();
objBook = objBooks.Open(szMainPath,  VOptional, VOptional, VOptional, VOptional,  VOptional, VOptional, VOptional, VOptional,  VOptional, VOptional, VOptional, VOptional,  VOptional, VOptional);
objSheets = objBook.GetWorksheets();
objSheet = objSheets.GetItem(COleVariant((short)1));

objRange.AttachDispatch(objSheet.GetCells(),true);
// 写数据
for (int i=1; i<10; ++i)
{
for (int j=1; j<10; ++j)
{
CString strItem;

strItem.Format(“数据 %d – %d”, i, j);

COleVariant vItem(strItem);

vItem.ChangeType(VT_BSTR);
objRange.SetItem(COleVariant((long)(i)),COleVariant((long)(j)),vItem);
}
}
// 保存文件
COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

objBook.SaveAs(COleVariant(strPathName.GetBuffer(0)),covOptional,covOptional,covOptional,
covOptional,covOptional,(long)0,covOptional,covOptional,covOptional,covOptional,covOptional);
strPathName.ReleaseBuffer();
objRange.ReleaseDispatch();
// 关闭文件
objRange.Clear();
objBook.Close(COleVariant((short)FALSE),VOptional,VOptional);
objBooks.Close();
objApp.Quit();

return true;
}