21xrx.com
2024-12-22 23:42:49 Sunday
登录
文章检索 我的文章 写文章
如何用c++调用Excel表格？
2023-06-23 21:00:12 深夜i     --     --
C++ Excel 调用 表格 API

当我们需要对多个Excel表格进行复杂的操作时，手动操作显然比较麻烦且效率低下。而使用c++调用Excel表格可以自动化处理数据，提高操作效率。

首先，我们需要安装Microsoft Office。安装完成后，在Visual Studio中新建c++项目，然后在“项目属性-VC++目录-包含目录”中添加以下路径：

"C:\Program Files (x86)\Microsoft Office\root\Office16\Excel.exe"

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSO.dll"

"C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE"

"C:\Program Files (x86)\Microsoft Office\root\Office16\MSO.DLL"

"C:\Program Files (x86)\Microsoft Office\root\Office16\Office.dll"

在代码中引用头文件：

#include

#include

#include

然后，我们可以打开Excel表格：

LPDISPATCH lpDispatchExcel; 

CoInitialize(NULL); 

if (FAILED(CoCreateInstance(CLSID_Application,NULL,CLSCTX_LOCAL_SERVER,IID_IDispatch,(void**)&lpDispatchExcel))) 

  return false; 

IDispatch* lpDispatchBooks = NULL; 

if (FAILED(lpDispatchExcel->GetTypeInfo(0,(LANGID) 0,&lpDispatchBooks))) 

{ 

  lpDispatchExcel->Release(); 

  return false; 

} 

LCID localId = GetUserDefaultLCID(); 

VARIANTARG vaParam; 

VARIANTARG vaParamResult; 

EXCEPINFO excepInfo; 

VariantInit(&vaParam); 

VariantInit(&vaParamResult); 

memset(&excepInfo, 0, sizeof(excepInfo)); 

vaParam.vt = VT_BSTR; 

vaParam.bstrVal = SysAllocString(L"Microsoft Excel"); 

DISPID dispId; 

HRESULT hr; 

hr = lpDispatchBooks->Invoke(DISPID_NewEnum,IID_NULL,LOCALE_USER_DEFAULT,DISPATCH_PROPERTYGET,&vaParamResult,NULL,&excepInfo,NULL); 

if (FAILED(hr)) 

{ 

  lpDispatchExcel->Release(); 

  lpDispatchBooks->Release(); 

  return false; 

} 

正确操作结束后，我们需要释放指针：

//释放指针 

VariantClear(&vaParam); 

VariantClear(&vaParamResult); 

lpDispatchBooks->Release(); 

lpDispatchExcel->Release(); 

CoUninitialize(); 

接着，我们可以在代码中操作具体的单元格或表格数据。例如：

#include

#include

#include

LPDISPATCH lpDispatchExcel=NULL; 

LPDISPATCH lpDispatchWorkbooks=NULL; 

LPDISPATCH lpDispatchWorkbook=NULL; 

LPDISPATCH lpDispatchWorksheets=NULL; 

LPDISPATCH lpDispatchWorksheet=NULL; 

VARIANTARG vaParamResult; 

EXCEPINFO excepInfo; 

HRESULT hr; 

CoInitialize(NULL); 

if (FAILED(CoCreateInstance(CLSID_Application,NULL,CLSCTX_LOCAL_SERVER,IID_IDispatch,(void**)&lpDispatchExcel))) 

  return false; 

if (FAILED(lpDispatchExcel->GetTypeInfo(1,localId,&lpDispatchWorkbooks))) 

{ 

  lpDispatchExcel->Release(); 

  return false; 

} 

hr=lpDispatchWorkbooks->Invoke(0xcc, IID_NULL, localId, DISPATCH_PROPERTYGET,&vaParamResult,0,0,0); 

if (FAILED(hr)) 

{ 

  lpDispatchExcel->Release(); 

  lpDispatchWorkbooks->Release(); 

  return false; 

} 

lpDispatchWorkbook = vaParamResult.pdispVal; 

VariantClear(&vaParamResult); 

HRESULT hr; 

hr=lpDispatchWorkbook->GetTypeInfo(1,localId,&lpDispatchWorksheets); 

if (FAILED(hr)) 

{ 

  lpDispatchExcel->Release(); 

  lpDispatchWorkbooks->Release(); 

  lpDispatchWorkbook->Release(); 

  return false; 

} 

hr=lpDispatchWorksheets->Invoke(0x7d, IID_NULL, localId, DISPATCH_PROPERTYGET,&vaParamResult,0,0,0); 

if (FAILED(hr)) 

{ 

  lpDispatchExcel->Release(); 

  lpDispatchWorkbooks->Release(); 

  lpDispatchWorkbook->Release(); 

  lpDispatchWorksheets->Release(); 

  return false; 

} 

lpDispatchWorksheet = vaParamResult.pdispVal; 

VariantClear(&vaParamResult); 

//打开指定的Excel表格 

vaParamResult.vt = VT_BSTR; 

vaParamResult.bstrVal = SysAllocString(L"\\test\\data.xlsx"); 

hr=lpDispatchExcel->GetTypeInfo(710,localId,&lpDispatchWorksheet); 

if (FAILED(hr)) 

{ 

  lpDispatchExcel->Release(); 

  lpDispatchWorkbooks->Release(); 

  lpDispatchWorkbook->Release(); 

  lpDispatchWorksheets->Release(); 

  return false; 

} 

hr=lpDispatchWorksheet->Invoke(DISPID_Open, IID_NULL,localId, DISPATCH_PROPERTYGET,&vaParamResult, 0, 0, 0); 

if (FAILED(hr)) 

{ 

  lpDispatchExcel->Release(); 

  lpDispatchWorkbooks->Release(); 

  lpDispatchWorkbook->Release(); 

  lpDispatchWorksheets->Release(); 

  lpDispatchWorksheet->Release(); 

  return false; 

} 

VariantClear(&vaParamResult); 

//选择指定的表格 

VARIANT sheetId; 

VariantInit(&sheetId); 

VariantClear(&vaParamResult); 

sheetId.vt = VT_BSTR; 

sheetId.bstrVal = SysAllocString(L"Sheet1"); 

hr=lpDispatchWorksheet->Invoke(DISPID_GetItem, IID_NULL,localId, DISPATCH_PROPERTYGET,&sheetId, 0, 0, 0); 

if (FAILED(hr)) 

{ 

  lpDispatchExcel->Release(); 

  lpDispatchWorkbooks->Release(); 

  lpDispatchWorkbook->Release(); 

  lpDispatchWorksheets->Release(); 

  lpDispatchWorksheet->Release(); 

  return false; 

} 

LPDISPATCH lpSheet = vaParamResult.pdispVal; 

VariantClear(&vaParamResult); 

VariantClear(&sheetId); 

//设置指定单元格的值 

VARIANTARG rgvarg[2]; 

EXCEPINFO ei; 

rgvarg[1].vt = VT_BSTR; 

rgvarg[1].bstrVal = SysAllocString(L"A1"); 

hr = lpSheet->Invoke(DISPID_GetItem,IID_NULL,LOCAL_USER_DEFAULT,DISPATCH_PROPERTYGET,&rgvarg[1],&vtResult,&excepInfo,NULL); 

if(FAILED(hr)) 

  return FALSE; 

rgvarg[0].vt = VT_I4; 

rgvarg[0].lVal = 1; 

hr = lpSheet->Invoke(DISPID_GetItem,IID_NULL,LOCAL_USER_DEFAULT,DISPATCH_PROPERTYGET,&rgvarg[0], &vtResult, &ei, NULL); 

if(FAILED(hr)) 

  return FALSE; 

VariantClear(&vtResult); 

rgvarg[0].lVal = 1; 

VARIANTARG myvalue; 

VariantInit(&myvalue); 

myvalue.vt = VT_BSTR; 

myvalue.bstrVal = SysAllocString(L"1.234"); 

hr = lpSheet->Invoke(DISPID_GetItem,IID_NULL,LOCAL_USER_DEFAULT,DISPATCH_PROPERTYGET,&rgvarg[0], &vtResult, &ei, NULL); 

hr = lpSheet->Invoke(0x0, IID_NULL,LOCAL_USER_DEFAULT, DISPATCH_METHOD ,&vtResult, &myvalue, 1, 0L); 

VariantClear(&myvalue); 

最后，我们同样需要释放指针：

//释放指针 

VariantClear(&vaParamResult); 

lpDispatchExcel->Release(); 

lpDispatchWorkbooks->Release(); 

lpDispatchWorkbook->Release(); 

lpDispatchWorksheets->Release(); 

lpDispatchWorksheet->Release(); 

CoUninitialize(); 

以上就是使用c++调用Excel表格的基本操作流程。当然，具体的操作根据需要而定，不同的操作需要使用不同的方法。但是总之，熟练掌握基本操作后，在实际工作中使用c++调用Excel表格可以极大的提高效率，减少笨重的手动操作。

  
  

评论区

{{item['qq_nickname']}}
()
回复
回复