VS2008具体操作Excel

前一篇博文中转载了有人对VS2008操作Excel的总结，网址见http://blog.sina.com.cn/s/blog_6163bdeb0102dxcy.html

之前做过使用VS2008以OLE方式操作PPT（见http://blog.sina.com.cn/s/blog_6163bdeb0100nn1p.html），所以想尝试一下OLE操作Excel，基本过程时差不多的，但有些地方还是不一样，基本过程参考上面的操作PPT的博文，不一样的地方总结于此。

 

参考了如下两篇博文：

VS2010+MFC解析Excel文件中数据，介绍的有关Excel的设置方面很全

http://www.vcfans.com/2010/08/vs2010-mfc-excel-file-in-the-data-analysis.html

OLE操作Excel编译错误处理，里面有具体操作的函数使用方法

http://www.cppblog.com/greatws/archive/2008/09/21/62423.html

Excel的设置方法及使用如下：

设置上与PPT最大的不同在于，添加如下代码

#pragma region Import the type libraries

//#import "libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52" \
//    rename("RGB", "MSORGB") \
//    rename("DocumentProperties", "MSODocumentProperties")
//    rename("DialogBox", "ignorethis"),
// [-or-]
#import "C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE14\\MSO.DLL" \
    rename("RGB", "MSORGB") \
    rename("DocumentProperties", "MSODocumentProperties") \
    rename("DialogBoxW", "ignorethis")

using namespace Office;

//#import "libid:0002E157-0000-0000-C000-000000000046"
// [-or-]
#import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB"

using namespace VBIDE;

//#import "libid:00020813-0000-0000-C000-000000000046" \
//    rename("DialogBox", "ExcelDialogBox") \
//    rename("RGB", "ExcelRGB") \
//    rename("CopyFile", "ExcelCopyFile") \
//    rename("ReplaceText", "ExcelReplaceText") \
//    no_auto_exclude
// [-or-]
#import "C:\\Program Files\\Microsoft Office\\Office14\\EXCEL.EXE" \
    rename("DialogBox", "ExcelDialogBox") \
    rename("RGB", "ExcelRGB") \
    rename("CopyFile", "ExcelCopyFile") \
    rename("ReplaceText", "ExcelReplaceText") \
    no_auto_exclude

#pragma endregion

（PPT添加的代码如下

#import "C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE11\\mso.dll" \
rename_namespace("Office"), named_guids, exclude("Pages")
using namespace Office;

#import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.olb" \
rename_namespace("VBE6")
using namespace VBE6;

可见上面给的代码更规范）

设置完后（其他设置与PPT的相同），编译会发现如下错误

warning C4003: “DialogBoxW”宏的实参不足

有人说可以升级你的windows SDK，或者在错误处将DialogBox() 改为 _DialogBox() ，就可以编译成功了。

 

具体的实现可以参考OLE操作Excel编译错误处理那边博文，现简单整理了一个Excel的接口，完成了少量功能，代码如下

CExcelInterface.h文件

#pragma once

#pragma region Import the type libraries

//#import "libid:2DF8D04C-5BFA-101B-BDE5-00AA0044DE52" \
//    rename("RGB", "MSORGB") \
//    rename("DocumentProperties", "MSODocumentProperties")
//    rename("DialogBox", "ignorethis"),
// [-or-]
#import "C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE14\\MSO.DLL" \
    rename("RGB", "MSORGB") \
    rename("DocumentProperties", "MSODocumentProperties") \
    rename("DialogBoxW", "ignorethis")

using namespace Office;

//#import "libid:0002E157-0000-0000-C000-000000000046"
// [-or-]
#import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB"

using namespace VBIDE;

//#import "libid:00020813-0000-0000-C000-000000000046" \
//    rename("DialogBox", "ExcelDialogBox") \
//    rename("RGB", "ExcelRGB") \
//    rename("CopyFile", "ExcelCopyFile") \
//    rename("ReplaceText", "ExcelReplaceText") \
//    no_auto_exclude
// [-or-]
#import "C:\\Program Files\\Microsoft Office\\Office14\\EXCEL.EXE" \
    rename("DialogBox", "ExcelDialogBox") \
    rename("RGB", "ExcelRGB") \
    rename("CopyFile", "ExcelCopyFile") \
    rename("ReplaceText", "ExcelReplaceText") \
    no_auto_exclude

#pragma endregion

#include "CApplication.h"
#include "CWorkbooks.h"
#include "CWorkbook.h"
#include "CWorksheets.h"
#include "CWorksheet.h"
#include "CRange.h"

class CExcelInterface
{
public:
    CExcelInterface(void);
    ~CExcelInterface(void);

public:
    CApplication m_Application;
    CWorkbooks m_WorkBooks;
    CWorkbook m_WorkBook;

public:
    BOOL OpenEngine(); // open engine   

    void NewWorkBooks();
    void OpenExcelFile(CString cstrFileName);
    void InsertData(int iSheetNum, int iRow, int iColumn, CString cstrData);
    void SaveExcel(); // save PPT
    void CloseEngine(); // close PPT engine       

};

 

CExcelInterface.cpp文件

#include "StdAfx.h"
#include "ExcelInterface.h"

CExcelInterface::CExcelInterface(void)
{
}

CExcelInterface::~CExcelInterface(void)
{
    if (m_Application)
    {
        m_WorkBook.DetachDispatch();
        m_WorkBooks.DetachDispatch();
        m_Application.DetachDispatch();
    }
}

// open Excel engine   
BOOL CExcelInterface::OpenEngine()
{
   

    if (m_Application)
    {
        m_WorkBooks.ReleaseDispatch();
        m_Application.Quit();
        m_Application.DetachDispatch();
    }

    if(!m_Application.CreateDispatch(_T("Excel.Application")))
    {
        AfxMessageBox(_T("Couldn't start Excel."));
        ::CoUninitialize();
        return FALSE;
    }
    else
    {
        //Make the application visible.
        m_Application.put_Visible(TRUE);
        m_WorkBooks = m_Application.get_Workbooks();
        return TRUE;
    }
}

// create a Excel
void CExcelInterface::NewWorkBooks()
{
    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    if (m_WorkBooks)
        m_WorkBook = m_WorkBooks.Add(covOptional);
}

// open Excel file
void CExcelInterface::OpenExcelFile(CString cstrFileName)
{
    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    if (m_WorkBooks || !cstrFileName.IsEmpty())
        m_WorkBook = m_WorkBooks.Add(_variant_t(cstrFileName));
}

void CExcelInterface::InsertData(int iSheetNum, int iRow, int iColumn, CString cstrData)
{
    CWorksheets sheets;
    CWorksheet sheet;
    CRange range;

    sheets = m_WorkBook.get_Worksheets();
    sheet = sheets.get_Item(_variant_t(iSheetNum));

    range = sheet.get_Cells();
    range.put_Item(_variant_t(iRow), _variant_t(iColumn), _variant_t(cstrData));
}

// save Excel
void CExcelInterface::SaveExcel()
{
    if (m_WorkBook)
        m_WorkBook.Save();
}

// close Excel engine       
void CExcelInterface::CloseEngine()
{
    if (m_Application)
    {
        m_WorkBook.ReleaseDispatch();
        m_WorkBooks.ReleaseDispatch();
        m_Application.Quit();
        m_Application.DetachDispatch();
    }
}

 

封装成个类，在使用时会很方便，如随便建一个对话框程序，建一个按钮，按钮响应如下

void CExcelTestDlg::OnBnClickedButton1()
{
    // 浏览Excel文件
    static TCHAR BASED_CODE szFilter[] = _T("Excel Files (*.xlsx;*.xls)|*.xlsx;*.xls|All Files (*.*)|*.*||");
    CFileDialog FileDlg(TRUE,_T("Excel"),NULL,OFN_FILEMUSTEXIST|OFN_NONETWORKBUTTON|OFN_PATHMUSTEXIST,szFilter);
    FileDlg.DoModal();

    // 获取文件名
    CString cstrTemplateFile = FileDlg.GetPathName();
    CString cstrFileName = _T("Report.xlsx");
    cstrTemplateFile.MakeReverse();
    CString cstrFilePath = cstrTemplateFile.Right(cstrTemplateFile.GetLength() - cstrTemplateFile.Find( '\\'));
    cstrFilePath.MakeReverse();
    cstrTemplateFile.MakeReverse();

   // 写Excel文件
    m_ExcelInterface.OpenEngine();
    m_ExcelInterface.OpenExcelFile(cstrTemplateFile);

    m_ExcelInterface.InsertData(1, 1, 1, _T("Test"));
    m_ExcelInterface.InsertData(1, 2, 1, _T("Hello"));
    m_ExcelInterface.InsertData(1, 2, 2, _T("Excel"));
    m_ExcelInterface.InsertData(1, 2, 3, _T("!"));

    //m_ExcelInterface.SaveExcel();
    m_ExcelInterface.CloseEngine();
}