1.在stdafx.h定义
//操作EXCEL文件用智能指针需要
#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE16\\MSO.DLL" rename("RGB", "MSRGB")
#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\VBA6\\VBE6EXT.OLB" raw_interfaces_only, rename("Reference", "ignorethis"), rename("VBE", "testVBE")
#import "C:\\Program Files (x86)\\Microsoft Office\\Office16\\EXCEL.EXE" exclude("IFont", "IPicture"), rename("RGB", "ignorethis"), rename("DialogBox", "ignorethis"), rename("VBE", "testVBE"), rename("ReplaceText", "EReplaceText"), rename("CopyFile","ECopyFile"), rename("FindText", "EFindText"), rename("NoPrompt", "ENoPrompt"),rename("FontPtr","ExcelFontPtr")
注意:安装位置不同使用的路径不同
2.创建类OperaExcel
3.在OperaExcel.h文件中创建全局变量
Excel::_ApplicationPtr m_app;
Excel::_WorkbookPtr m_Workbook;
Excel::_WorksheetPtr m_Worksheet;
4.在OperaExcel.cpp中创建函数
(1)打开Excel文件
BOOL COperaExcel::ExcelOpen(CString FilePath)
{
try
{
if (m_app != NULL)
{
m_app.Release();
m_app = NULL;
}
HRESULT hr = m_app.CreateInstance(L"Excel.Application");
if(FAILED(hr))
{
AfxMessageBox(_T("无法连接Excel!"));
m_app.Release();
m_app = NULL;
return FALSE;
}
m_app->PutVisible(0,VARIANT_TRUE);//使EXCEL可见
m_app->PutDisplayAlerts(0,VARIANT_FALSE);//不弹出提示
ATLASSERT(SUCCEEDED(hr));
CComVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
Excel::WorkbooksPtr mBook = m_app->Workbooks;
m_Workbook = mBook->Open((_bstr_t)FilePath,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,covOptional,VARIANT_TRUE,covOptional,covOptional,covOptional,covOptional,covOptional);
return TRUE;
}
catch (_com_error& error)
{
AfxMessageBox(CString(error.ErrorMessage()));
return FALSE;
}
}
(2)关闭Excel
BOOL COperaExcel::ExcelClose(CString filePath)
{
try
{
m_app->PutAlertBeforeOverwriting(0,VARIANT_FALSE);
m_app->PutDisplayAlerts(0,VARIANT_FALSE);//屏蔽警告
m_Workbook->Close(VARIANT_TRUE);//保存关闭
m_app->Quit();//退出
//CComVariant covOptional((long)DISP_E_PARAMNOTFOUND,VT_ERROR);
//m_Workbook->SaveAs((_bstr_t)cstrPath,covOptional,covOptional,covOptional,covOptional,covOptional,Excel::xlNoChange,covOptional,covOptional,covOptional,covOptional,covOptional,VARIANT_FALSE);//另存为
//m_Workbook->ExportAsFixedFormat(Excel::XlFixedFormatType::xlTypePDF, (_bstr_t)filePath);//Excel转PDF
m_app = NULL;
return TRUE;
}
catch (_com_error& e)
{
ATLASSERT(FALSE);
AfxMessageBox(e.ErrorMessage());//输出错误
return FALSE;
}
}
(3)写Excel单元格值
BOOL COperaExcel::ExcelWrite(CString LookSheetName,int row,int col,int IntValue,double DoubValue,CString StrValue)
{
try
{
int isheetnum = m_Workbook->Worksheets->Count;
int k = 1;
for (; k <= isheetnum; k++)
{
m_Worksheet = m_Workbook->Worksheets->Item[(long)k];
CString SheNa = m_Worksheet->Name;
if (LookSheetName == SheNa)
{
if (IntValue != -1 && DoubValue == -1 && StrValue == L"0")
{
Excel::RangePtr m_range = m_Worksheet->Cells;
m_range->Item[row][col] = IntValue;
break;
}
if (IntValue == -1 && DoubValue != -1 && StrValue == L"0")
{
Excel::RangePtr m_range = m_Worksheet->Cells;
m_range->Item[row][col] = DoubValue;
break;
}
if(IntValue == -1 && DoubValue== -1 && StrValue != L"0")
{
Excel::RangePtr m_range = m_Worksheet->Cells;
m_range->Item[row][col] = _variant_t(StrValue);
break;
}
}
}
if (k > isheetnum)
{
AfxMessageBox(L"配置文件和计算书没有相同的Sheet名!\n" + LookSheetName);
return FALSE;
}
return TRUE;
}
catch (_com_error& e)
{
AfxMessageBox(e.ErrorMessage());
return FALSE;
}
}
(4)读Excel单元格数据
BOOL COperaExcel::ExcelRead(CString LookSheetName, int iSheetNum, int Erow, int Ecol, CString& cstrValue)
{
try
{
if (LookSheetName != _T(""))
{
m_Worksheet = m_Workbook->Worksheets->GetItem(_variant_t(LookSheetName));
}
if (iSheetNum > 0)
{
m_Worksheet = m_Workbook->Worksheets->GetItem(iSheetNum);
}
if (m_Worksheet == NULL)
{
AfxMessageBox(_T("ExcelRead获取Sheet失败!"));
return FALSE;
}
Excel::RangePtr m_Range = m_Worksheet->Cells;
cstrValue = m_Range->Item[Erow][Ecol];
return TRUE;
}
catch (_com_error& e)
{
AfxMessageBox(e.ErrorMessage());
return FALSE;
}
}