VS2010操作Excel2010，報錯解決及讀寫excel

版權所有，未經允許不可作為商業用途！！轉載請注明出處！！謝謝合作！！

2013-03-20
操作系統：Windows 7旗艦版 工具：VS2010 語言：MFC C++

第一步：初化COM接口。在InitInstance中加入如下代碼：

1 2 3 4 5

if(!AfxOleInit()) { AfxMessageBox(_T( "Cannot initialize COM dll" )); return FALSE; }

第二步：向工程添加Excel的C++操作類。具體步驟見下圖：
1.Ctrl+Shift+X 打開類向導。

2.添加需要的類文件。如下圖：

4.将剛才導入的類頭文件，加到模塊std中。

#include "CApplication.h" #include "CWorkbooks.h" #include "CWorkbook.h" #include "CWorksheets.h" #include "CWorksheet.h" #include "CRanges.h" #include "CRange.h" //以上加入到 stdafx.h中
5、将以上頭文件的#import "C:\\Program Files\\Microsoft Office\\Office14\\EXCEL.EXE" no_namespace 替換成 以下： #pragma region Import the type libraries //#import "libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52" #import "C:\\Program Files\\Common Files\\Microsoft Shared\\Office14\\MSO.DLL" \ rename("RGB", "ExclRGB") rename("DocumentProperties", "ExclDocumentProperties") \ rename("SearchPath", "ExclSearchPath") using namespace Office; //#import "libid:0002E157-0000-0000-C000-000000000046" #import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB" \ rename("Reference", "ignorethis") using namespace VBIDE;
#pragma warning( disable : 4049 ) //#import "libid:00020813-0000-0000-C000-000000000046"
#import "C:\\Program Files\\Microsoft Office\\Office14\\EXCEL.EXE" \ exclude("IFont", "IPicture") \ rename("RGB", "ExclRGB") \ rename("DocumentProperties", "ExclDocumentProperties") \ rename("SearchPath", "ExclSearchPath") \ rename("CopyFile", "ExclCopyFile") \ rename("DialogBox", "ExclDialogBox") \ rename("ReplaceText", "ExclReplaceText")
using namespace Excel;
6、編譯，出現 雙擊error C2059，将VARIANT DialogBox()改成 VARIANT _DialogBox() 再次編譯，通過！！
以下為讀excel操作 CApplication ExcelApp; CWorkbooks books; CWorkbook book; CWorksheets sheets; CWorksheet sheet; CRange range; CRange excel_current_range_; LPDISPATCH lpDisp = NULL; CString name; CString FilePathname; CString Filescr = _T("F:\\BenderMenu.xlsx"); CString strSheetName = _T("Menu"); CString strItem1; COleVariant varItem; COleVariant File; int PointSum = 0; long i = 0, j = 0; CEdit* pEdit = NULL; int IDL = 0; int IDA = 0;
//創建Excel 服務器(啟動Excel) if(!ExcelApp.CreateDispatch(_T("Excel.Application"),NULL)) { AfxMessageBox(_T("啟動Excel服務器失敗!")); return; } ExcelApp.put_Visible(FALSE); ExcelApp.put_UserControl(TRUE);
books.AttachDispatch(ExcelApp.get_Workbooks()); CFileDialog Fdlg(TRUE, _T("Worksheet Files (*.xlsx)|*.xlsx"), NULL, OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT, _T("Worksheet Files (*.xlsx)|*.xlsx"), NULL); Fdlg.m_ofn.lpstrInitialDir = _T("F:\\");
if(IDOK == Fdlg.DoModal()) { //get path of the file FilePathname = Fdlg.GetPathName(); //File = FilePathname; } else { return; }
//打開一個工作簿 lpDisp = books.Open(FilePathname, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing, vtMissing);
if(lpDisp != NULL) { book.AttachDispatch(lpDisp); } sheets.AttachDispatch(book.get_Sheets()); lpDisp = sheets.get_Item(_variant_t(strSheetName)); if(lpDisp != NULL) { sheet.AttachDispatch(lpDisp); } //以上為關聯sheet,book等，，以下為讀操作 range.AttachDispatch(sheet.get_Cells());//一定要，不知道為何 range.AttachDispatch(range.get_Item(COleVariant((long)20), COleVariant((long)2)).pdispVal, TRUE);//關聯（20，2）單元格 varItem = range.get_Value2(); strItem1.Format(_T("%.lf"), varItem.dblVal);//知道是double類型，直接使用，若不知道得做類型判斷 PointSum = _ttoi(strItem1); m_PIC.m_pointSum = PointSum; range.ReleaseDispatch(); //以下為寫excel range.AttachDispatch(sheet.get_Cells()); range.AttachDispatch(range.get_Item(COleVariant((long)i), COleVariant((long)2)).pdispVal, TRUE); //varItem.dblVal = m_PIC.m_Len[i - 2]; //range.put_Item(COleVariant((long)i), COleVariant((long)2), COleVariant(m_PIC.m_Len[i - 2])); range.put_Value2(COleVariant(m_PIC.m_Len[i - 2])); range.ReleaseDispatch(); //最後保存 book.Save(); //釋放對象 sheet.ReleaseDispatch(); sheets.ReleaseDispatch(); book.ReleaseDispatch(); books.ReleaseDispatch(); ExcelApp.Quit(); ExcelApp.ReleaseDispatch();