Working with Excel via C++

cpp-manipulate-excel-2010.cpp

// 包含ATL库
#include <atlbase.h>

// 导入Office库
#import "C:\Program Files\Common Files\Microsoft Shared\OFFICE14\MSO.DLL" \
    rename( "RGB", "MSORGB" )
using namespace Office;

// 导入VB库
#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
using namespace VBIDE;

// 导入Excel库
#import "C:\Program Files\Microsoft Office\Office14\EXCEL.EXE" \
    rename( "DialogBox", "ExcelDialogBox" ) \
    rename( "RGB", "ExcelRGB" ) \
    rename( "CopyFile", "ExcelCopyFile" ) \
    rename( "ReplaceText", "ExcelReplaceText" )
using namespace Excel;

// 包含STL库
#include <iostream>
#include <memory>
#include <string>
#include <algorithm>
using namespace std;


int WorkWithExcel()
{
    HRESULT hr;
    _ApplicationPtr spExcel;

    try
    {
        // 创建Excel对象
        hr = spExcel.CreateInstance(_T("Excel.Application"));
        if(FAILED(hr) || !spExcel)
        {
            cout << "Failed to create excel applciation" << endl;
            return -1;
        }

        // 设置Excel可见
        spExcel->PutVisible(0, VARIANT_TRUE);

        // 创建一个工作簿
        auto spWorkbook = spExcel->Workbooks->Add();
        if(!spWorkbook)
        {
            cout << "Failed to create excel workbook" << endl;
            return -1;
        }

        // 创建一个工作页，注意这里使用QIPtr自动调用Add()方法返回值IDispatch的QueryInterface方法
        // 所以不能使用auto来定义，否则将得到一个IDispatchPtr对象
        _WorksheetPtr spSheet = spWorkbook->Worksheets->Add();
        if(!spSheet)
        {
            cout << "Failed to create excel worksheet" << endl;
            return -1;
        }

        // 工作页改名字为"我爱中国"
        spSheet->Name = _bstr_t(_T("我爱中国"));

        // 获取A1~C3
        auto spRange = spSheet->Range[_variant_t(_T("A1"))][_variant_t(_T("C3"))];
        if(!spRange)
        {
            cout << "Failed to get range (A1, C3)" << endl;
            return -1;
        }

        // 设值，注意使用Value2而不是Value
        spRange->Value2 = _variant_t(_T("Hey"));

        // 等待一定时间观察结果
        Sleep(5000);
        //

        // 关闭工作簿并且不保存
        hr = spWorkbook->Close(_variant_t(false));
        if(FAILED(hr))
        {
            cout << "Failed to close excel workbook" << endl;
            return -1;
        }

        // 退出Excel程序
        hr = spExcel->Quit();
        if(FAILED(hr))
        {
            cout << "Failed to quit excel applciation" << endl;
            return -1;
        }
    }
    catch(...)
    {
        cout << "Error occured, quit" << endl;
        if(spExcel)
            spExcel->Quit();
        return -1;
    }

    cout << "Done!" << endl;

    // 由于使用了智能指针，不需要释放对象
    return 0;
}

int main()
{
    CoInitialize(NULL);
    WorkWithExcel();
    CoUninitialize();
    return 0;
}