c++如何读取excel

1. 概述

在C++中，读取Excel可以采用OLE接口或者第三方库的方式。OLE接口是Microsoft定义的一种二进制文件格式，可以用于与Microsoft Office中的文档进行交互，包括读取和写入Excel、Word、PowerPoint等文件。第三方库包括libxl、POCO C++等。

2. 使用OLE接口读取Excel

2.1 基本流程

使用OLE接口读取Excel，需要借助COM对象。下面是基本的流程：

初始化COM库。

创建Excel应用程序对象。

打开Excel文件。

获取Workbook对象。

获取Worksheet对象。

读取单元格数据。

关闭Excel文件。

释放COM对象。

反初始化COM库。

下面是基本流程的C++代码：

#include <windows.h>
#include <ole2.h>
#include <oaidl.h>
#include <stdio.h>
 
int main(int argc, char* argv[])
{
    HRESULT hr;
 
    // Step 1: 初始化COM库
    hr = CoInitialize(NULL);
    if (FAILED(hr))
    {
        printf("初始化COM库失败");
        return 1;
    }
 
    // Step 2: 创建Excel应用程序对象
    IDispatch* pExcelApp = NULL;
    hr = CoCreateInstance(CLSID_ExcelApplication, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void**)&pExcelApp);
    if (FAILED(hr))
    {
        printf("创建Excel应用程序对象失败");
        CoUninitialize();
        return 1;
    }
 
    // Step 3: 打开Excel文件
    BSTR bsFilename = SysAllocString(L"C:\\data\\test.xlsx");
    VARIANT vtFilename;
    vtFilename.vt = VT_BSTR;
    vtFilename.bstrVal = bsFilename;
    VARIANT vtReadOnly;
    vtReadOnly.vt = VT_BOOL;
    vtReadOnly.boolVal = TRUE;
    VARIANT vtNull;
    vtNull.vt = VT_ERROR;
    vtNull.scode = DISP_E_PARAMNOTFOUND;
    IDispatch* pWorkbooks = NULL;
    hr = pExcelApp->GetIDsOfNames(IID_NULL, &bsOpenMethod, 1, LOCALE_USER_DEFAULT, &dispid);
    if (FAILED(hr))
    {
        printf("获取Open方法ID失败");
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    DISPPARAMS dp;
    dp.cArgs = 3;
    dp.rgvarg = new VARIANTARG[dp.cArgs];
    dp.rgvarg[2] = vtNull;
    dp.rgvarg[1] = vtReadOnly;
    dp.rgvarg[0] = vtFilename;
    dp.rgdispidNamedArgs = NULL;
    dp.cNamedArgs = 0;
    hr = pExcelApp->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD, &dp, &vtResult, NULL, NULL);
    if (FAILED(hr))
    {
        printf("打开Excel文件失败");
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    pWorkbooks = vtResult.pdispVal;
 
    // Step 4: 获取Workbook对象
    DISPID dispid;
    BSTR bsWorkbookName = SysAllocString(L"test.xlsx");
    hr = pWorkbooks->GetIDsOfNames(IID_NULL, &bsWorkbookName, 1, LOCALE_USER_DEFAULT, &dispid);
    if (FAILED(hr))
    {
        printf("获取Workbook对象ID失败");
        pWorkbooks->Release();
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    VARIANTARG varg;
    varg.vt = VT_BSTR;
    varg.bstrVal = bsWorkbookName;
    DISPPARAMS dp2 = { &varg, 0, 1, 0 };
    IDispatch* pWorkbook = NULL;
    hr = pWorkbooks->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD | DISPATCH_PROPERTYGET, &dp2, &vtResult, NULL, NULL);
    if (FAILED(hr))
    {
        printf("获取Workbook对象失败");
        pWorkbooks->Release();
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    pWorkbook = vtResult.pdispVal;
 
    // Step 5: 获取Worksheet对象
    BSTR bsSheetName = SysAllocString(L"Sheet1");
    VARIANTARG vargs[1] = { 0 };
    vargs[0].vt = VT_BSTR;
    vargs[0].bstrVal = bsSheetName;
    DISPPARAMS dp3 = { vargs, 0, 1, 0 };
    hr = pWorkbook->GetIDsOfNames(IID_NULL, &bsSheetMethod, 1, LOCALE_USER_DEFAULT, &dispid);
    if (FAILED(hr))
    {
        printf("获取Sheet对象ID失败");
        pWorkbook->Release();
        pWorkbooks->Release();
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    IDispatch* pWorksheets = NULL;
    hr = pWorkbook->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &dp3, &vtResult, NULL, NULL);
    if (FAILED(hr))
    {
        printf("获取Sheet对象失败");
        pWorkbook->Release();
        pWorkbooks->Release();
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    pWorksheets = vtResult.pdispVal;
 
    // Step 6: 读取单元格数据
    BSTR bsCellAddress = SysAllocString(L"A1");
    VARIANTARG varg2 = { 0 };
    varg2.vt = VT_BSTR;
    varg2.bstrVal = bsCellAddress;
    DISPPARAMS dp4 = { &varg2, 0, 1, 0 };
    hr = pWorksheets->GetIDsOfNames(IID_NULL, &bsCellMethod, 1, LOCALE_USER_DEFAULT, &dispid);
    if (FAILED(hr))
    {
        printf("获取单元格对象ID失败");
        pWorksheets->Release();
        pWorkbook->Release();
        pWorkbooks->Release();
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    VARIANTARG varg3;
    DISPPARAMS dp5 = { nullptr, nullptr, 0, 0 };
    hr = pWorksheets->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &dp4, &varg3, 0, 0);
    if (FAILED(hr))
    {
        printf("获取单元格对象失败");
        pWorksheets->Release();
        pWorkbook->Release();
        pWorkbooks->Release();
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    IDispatch* pCell = varg3.pdispVal;
    VARIANTARG varg4 = { 0 };
    DISPPARAMS dp6 = { nullptr, nullptr, 0, 0 };
    hr = pCell->Invoke(0x0 /* Value */, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &dp6, &varg4, 0, 0);
    if (FAILED(hr))
    {
        printf("读取单元格数据失败");
        pWorkbooks->Release();
        pExcelApp->Release();
        CoUninitialize();
        return 1;
    }
    printf("%S\n", varg4.bstrVal);
 
    // Step 7: 关闭Excel文件
    VARIANT vtSaveChanges;
    vtSaveChanges.vt = VT_BOOL;
    vtSaveChanges.boolVal = FALSE;
    hr = pWorkbook->GetIDsOfNames(IID_NULL, &bsSaveMethod, 1, LOCALE_USER_DEFAULT, &dispid);
    if (FAILED(hr))
    {
        printf("获取Save方法ID失败");
    }
    DISPPARAMS dp7 = { nullptr, nullptr, 0, 0 };
    hr = pWorkbook->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_METHOD, &dp7, &vtNull, NULL, NULL);
    if (FAILED(hr))
    {
        printf("关闭Excel文件失败");
    }
 
    // Step 8: 释放COM对象
    pCell->Release();
    pWorksheets->Release();
    pWorkbook->Release();
    pWorkbooks->Release();
    pExcelApp->Release();
 
    // Step 9: 反初始化COM库
    CoUninitialize();
     
    return 0;
}

2.2 读取多个单元格

读取多个单元格时，需要使用DISPPARAMS结构体和VARIANTARG数组。下面是使用DISPPARAMS结构体和VARIANTARG数组读取多个单元格的C++代码：

// 获取单元格范围
BSTR bsCellRange = SysAllocString(L"A1:B2");
VARIANTARG varg5 = { 0 };
varg5.vt = VT_BSTR;
varg5.bstrVal = bsCellRange;
DISPPARAMS dp8 = { &varg5, 0, 1, 0 };
hr = pWorksheets->GetIDsOfNames(IID_NULL, &bsCellsMethod, 1, LOCALE_USER_DEFAULT, &dispid);
if (FAILED(hr))
{
    printf("获取单元格范围对象ID失败");
    pWorksheets->Release();
    pWorkbook->Release();
    pWorkbooks->Release();
    pExcelApp->Release();
    CoUninitialize();
    return 1;
}
VARIANTARG varg6;
DISPPARAMS dp9 = { &varg5, NULL, 1, 2 };
VARIANTARG args[2];
args[1].vt = VT_BSTR;
args[1].bstrVal = SysAllocString(L"B2");
args[0].vt = VT_BSTR;
args[0].bstrVal = SysAllocString(L"A1");
dp9.rgvarg = args;
hr = pWorksheets->Invoke(dispid, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET,
    &dp9, &varg6, 0, 0);
if (FAILED(hr))
{
    printf("获取单元格范围对象失败");
    pWorksheets->Release();
    pWorkbook->Release();
    pWorkbooks->Release();
    pExcelApp->Release();
    CoUninitialize();
    return 1;
}
IDispatch* pCellRange = varg6.pdispVal;
 
// 读取单元格数据
unsigned int rows = 2;
unsigned int columns = 2;
for (unsigned int i = 1; i <= rows; i++)
{
    for (unsigned int j = 1; j <= columns; j++)
    {
        VARIANTARG varg7;
        DISPPARAMS dp10 = { NULL, NULL, 0, 0 };
        varg7.vt = VT_I4;
        varg7.lVal = i + (j - 1) * rows;
        unsigned int index = i + (j - 1) * rows - 1;
        hr = pCellRange->Invoke(DISPID_VALUE, IID_NULL, LOCALE_USER_DEFAULT, DISPATCH_PROPERTYGET, &dp10, &varg7, NULL, NULL);
        if (FAILED(hr))
        {
            printf("读取单元格数据失败");
            pWorksheets->Release();
            pWorkbook->Release();
            pWorkbooks->Release();
            pExcelApp->Release();
            CoUninitialize();
            return 1;
        }
        printf("%S\n", varg7.bstrVal);
    }
}

3. 使用第三方库读取Excel

使用第三方库读取Excel，可以省去调用COM接口的麻烦和繁琐。常用的第三方库有libxl、POCO C++等。

3.1 使用libxl读取Excel

libxl是一个跨平台的C++库，可以读取和写入Excel文件。

3.1.1 基本流程

使用libxl读取Excel，需要采用以下基本流程：

打开Excel文件。

获取Workbook对象。

获取Worksheet对象。

读取单元格数据。

释放 Workbook 对象。

关闭 Excel 文件。

下面是基本流程的C++代码：

#include <libxl.h>
 
int main()
{
    libxl::Book* book = xlCreateXMLBook();
    if (book)
    {
        if (book->load(L"C:\\data\\test.xlsx"))
        {
            libxl::Sheet* sheet = book->getSheet(0);
            if (sheet)
            {
                const wchar_t* data = sheet->readStr(1, 0);
                printf("%S", data);
                sheet->release();
            }
        }
        book->release();
    }
    return 0;
}

3.1.2 读取多个单元格

读取多个单元格时，可以使用Sheet::readStr或者Sheet::readNum函数。下面是使用Sheet::readStr函数读取多个单元格的C++代码：

#include <libxl.h>
 
int main()
{
    libxl::Book* book = xlCreateXMLBook();
    if (book)
    {
        if (book->load(L"C:\\data\\test.xlsx"))
        {
            libxl::Sheet* sheet = book->getSheet(0);
            if (sheet)
            {
                const unsigned int rows = 2;
                const unsigned int columns = 2;
                for (unsigned int i = 0; i < rows; i++)
                {
                    for (unsigned int j = 0; j < columns; j++)
                    {
                        const wchar_t* data = sheet->readStr(i + 1, j);
                        printf("%S\n", data);
                    }
                }               
            }
        }
        book->release();
    }
    return 0;
}

3.2 使用POCO C++读取Excel

POCO是一个轻量级的、用C++编写的开源C++类库，提供了大量的网络编程和通信功能等。POCO C++也提供了读取Excel文件功能。

3.2.1 基本流程

使用POCO C++读取Excel，需要采用以下基本流程：

创建Excel工作簿。

打开Excel文件。

获取Worksheet对象。

读取单元格数据。

关闭 Excel 文件。

下面是基本流程的C++代码：

#include <Poco/Excel/Workbook.h>
 
int main()
{
    Poco::Excel::Workbook book(L"C:\\data\\test.xlsx");
    if (book)
    {
        Poco::Excel::Worksheet sheet = book.getWorksheetByIndex(0);
        if (sheet)
        {
            Poco::DynamicAny data = sheet.getCellData(0, 0);
            std::wstring str = data.toString();
            printf("%S", str.c_str());
        }
    }
    return 0;
}

3.2.2 读取