使用 C++ 讀寫 Excel

使用Visual C++的擴充套件指令#import匯入Excel型別庫：

#import "C:\\Program Files\\Common Files\\microsoft shared\\OFFICE14\\MSO.DLL" rename("RGB","MsoRGB") rename("SearchPath","MsoSearchPath") #import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB" #import "C:\\Program Files\\Microsoft Office\\Office14\\EXCEL.EXE" rename( "DialogBox", "ExcelDialogBox" ) rename( "RGB", "ExcelRGB" ) rename( "CopyFile", "ExcelCopyFile" ) rename( "ReplaceText", "ExcelReplaceText" ) exclude( "IFont", "IPicture" ) no_dual_interfaces

1 2 3 4 5 6 7 8 9 10 11 12

#import "C:\\Program Files\\Common Files\\microsoft shared\\OFFICE14\\MSO.DLL"     rename("RGB","MsoRGB")     rename("SearchPath","MsoSearchPath") #import "C:\\Program Files\\Common Files\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB" #import "C:\\Program Files\\Microsoft Office\\Office14\\EXCEL.EXE"     rename( "DialogBox", "ExcelDialogBox" )     rename( "RGB", "ExcelRGB" )     rename( "CopyFile", "ExcelCopyFile" )     rename( "ReplaceText", "ExcelReplaceText" )     exclude( "IFont", "IPicture" ) no_dual_interfaces

#import指令會從指定的可執行檔案，動態連結庫等COM元件中匯出型別庫(type lib)，在Debug和Release臨時目錄中生成對應的型別庫標頭檔案(type lib header file)，以供C++程式使用。如以上三條指令在編譯後會生成excel.tlh, mso.lh和vbetext.olb三個標頭檔案，可以在Debug和Release目錄中找到。

2.訪問Excel暴露的COM物件

下面是一段比較完整的訪問Excel的例項程式碼。首先用生成的資料填充單元格，然後用這些單元格的資料生成了一個圖表(Chart):

try { Excel::_ApplicationPtr pExcelApp; HRESULT hr = pExcelApp.CreateInstance(L"Excel.Application"); ATLASSERT(SUCCEEDED(hr)); pExcelApp->Visible = true; // make Excel’s main window visible Excel::_WorkbookPtr pWorkbook = pExcelApp->Workbooks->Open(lpszPathName); // open excel file Excel::_WorksheetPtr pWorksheet = pWorkbook->ActiveSheet; pWorksheet->Name = L"Chart Data"; Excel::RangePtr pRange = pWorksheet->Cells; const int nplot = 100; const double xlow = 0.0, xhigh = 20.0; double h = (xhigh-xlow)/(double)nplot; pRange->Item[1][1] = L"x"; // read/write cell’s data pRange->Item[1][2] = L"f(x)"; for (int i=0;i<nplot;++i) { double x = xlow+i*h; pRange->Item[i+2][1] = x; pRange->Item[i+2][2] = sin(x)*exp(-x); } Excel::RangePtr pBeginRange = pRange->Item[1][1]; Excel::RangePtr pEndRange = pRange->Item[nplot+1][2]; Excel::RangePtr pTotalRange = pWorksheet->Range[(Excel::Range*)pBeginRange][(Excel::Range*)pEndRange]; Excel::_ChartPtr pChart = pExcelApp->ActiveWorkbook->Charts->Add(); // refer to : // http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart.chartwizard(v=vs.80).aspx pChart->ChartWizard( (Excel::Range*)pTotalRange, (long)Excel::xlXYScatter, 6L, (long)Excel::xlColumns, 1L,1L, true, L"My Graph", L"x",L"f(x)"); pChart->Name = L"My Data Plot"; pWorkbook->Close(VARIANT_TRUE); // save changes pExcelApp->Quit(); } catch (_com_error& error) { ATLASSERT(FALSE); ATLTRACE2(error.ErrorMessage()); }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50

try {     Excel::_ApplicationPtr pExcelApp;     HRESULT hr = pExcelApp.CreateInstance(L"Excel.Application");     ATLASSERT(SUCCEEDED(hr));     pExcelApp->Visible = true;   // make Excel’s main window visible     Excel::_WorkbookPtr pWorkbook = pExcelApp->Workbooks->Open(lpszPathName);  // open excel file     Excel::_WorksheetPtr pWorksheet = pWorkbook->ActiveSheet;     pWorksheet->Name = L"Chart Data";     Excel::RangePtr pRange = pWorksheet->Cells;     const int nplot = 100;     const double xlow = 0.0, xhigh = 20.0;     double h = (xhigh-xlow)/(double)nplot;     pRange->Item[1][1] = L"x";  // read/write cell’s data     pRange->Item[1][2] = L"f(x)";     for (int i=0;i<nplot;++i)     {         double x = xlow+i*h;         pRange->Item[i+2][1] = x;         pRange->Item[i+2][2] = sin(x)*exp(-x);     }     Excel::RangePtr pBeginRange = pRange->Item[1][1];     Excel::RangePtr pEndRange = pRange->Item[nplot+1][2];     Excel::RangePtr pTotalRange =         pWorksheet->Range[(Excel::Range*)pBeginRange][(Excel::Range*)pEndRange];     Excel::_ChartPtr pChart = pExcelApp->ActiveWorkbook->Charts->Add();     // refer to :     // http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.chart.chartwizard(v=vs.80).aspx     pChart->ChartWizard(         (Excel::Range*)pTotalRange,         (long)Excel::xlXYScatter,         6L,         (long)Excel::xlColumns,         1L,1L,         true,         L"My Graph",         L"x",L"f(x)");     pChart->Name = L"My Data Plot";     pWorkbook->Close(VARIANT_TRUE);  // save changes     pExcelApp->Quit(); } catch (_com_error& error) {     ATLASSERT(FALSE);     ATLTRACE2(error.ErrorMessage()); }

在這段程式碼中，Excel::_ApplicationPtr , Excel::_WorkbookPtr 和 Excel::_WorksheetPtr 等均是Visual C++ 編譯器根據#import指令自動生成的智慧指標，實際上就是C++模板類_com_ptr_t的typedef，其定義可在excel.tlh等型別庫標頭檔案中找到。

另外，由於#import指令中沒有指定raw_interface_only修飾符，Visual C++對Excel的COM介面進行了適當的封裝，以簡化COM介面屬性和方法的呼叫，並且將HRESULT返回值都轉換成了C++異常，因此，上面的這段程式碼不需要每一步都堅持HRESULT，而是改為捕獲C++異常。