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();