VC 智能指针操作Excel

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转PDFm_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;}
}