Interfacing Excel in C++ is task that I needed to overcome recently, so I thought I would post some code and instructions on the said topic. Some online articles that I found to be useful include the following:
A Brief Introduction to C++ and Interfacing with Excel
Accessing Excel Spreadsheets via C++
Programming Excel COM Objects in C++
Without further ado, or huge swathes of boring theory, here are the steps needed to at least get you interfacing with Microsoft Excel via C++:
1: Ensure pre-requisite files are installed and located
We must ensure that a number of Microsoft libraries and their file locations have been installed and located, namely MSO.DLL, VBE6EXT.OLB and EXCEL.EXE. Make sure that you have a suitable version of Microsoft Excel installed, any version should do. In this example, Excel 2003 was used.
The locations of these files will obviously differ from machine to machine, so the first step is to determine their whereabouts. In Windows 7 for example, I recommend using Windows Explorer search facility. To locate (say) ‘VBE6EXT.OLB‘, click on the ‘Computer’ icon at the left, and in the edit box at the upper right simply enter ‘VBE6EXT.OLB‘ and let Explorer do the rest:
Right-click on the file and select ‘Open file location’. If you then click inside the file location edit box, this will give you the location you need, which you can then copy and paste:
In my example, the location of the ‘VBE6EXT.OLB‘ is
"C:\Program Files (x86)\Common Files\microsoft shared\VBA\VBA6\\VBE6EXT.OLB"
Do the same for the MSO.DLL and EXCEL.EXE files and you are then in a position to proceed to the next step.
2. Set up the Microsoft libraries
In order to use Excel functionality from within a C++ program we use the Microsoft Component Object Model (COM). The required import libraries are shown below and need to be included in any C++ application that interfaces with Excel.
Just use the file directories you found on your own computer for each #import, remembering to replace single backslashes (‘\’) with double backslashes (‘\\’), since the single backslash is classed as a special character:
#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE11\\MSO.DLL" \
rename( "RGB", "MSORGB" )
using namespace Office;
#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"
using namespace VBIDE;
#import "C:\\Program Files (x86)\\Microsoft Office\\OFFICE11\\EXCEL.EXE" \
rename( "DialogBox", "ExcelDialogBox" ) \
rename( "RGB", "ExcelRGB" ) \
rename( "CopyFile", "ExcelCopyFile" ) \
rename( "ReplaceText", "ExcelReplaceText" ) \
exclude( "IFont", "IPicture" ) no_dual_interfaces
rename is used to change certain strings in the import library to avoid clashes with other libraries containing functions, variables or classes with the same name. exclude prevents the import of specified items. no_dual_interfaces must also be included for the correct functioning of the Excel application function.
3. Use the Excel Object Model in your C++ code
The Excel Object Model contains a huge number of functions and objects. We will concentrate on just a few of those required to perform this tasks of reading from and writing to the Excel spreadsheet. Declaring an Excel Application Object pointer is simple enough:
Excel::_ApplicationPtr pXL;
Once created, we can use this pointer to open for reading and writing our Excel Workbook of choice:
pXL->Workbooks->Open( L"C:\\dump\\book.xls" );
Set the Visible parameter depending on whether you would like the Excel spreadsheet to be displayed or not:
pXL->Visible = false;
This is how we can access the active Excel Worksheet and the cells within it:
Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet; Excel::RangePtr pRange = pWksheet->Cells;
To read the values of individual cells, simply use the Excel::RangePtr pointer above, remembering that in Excel cells must start from index = 1:
double value = pRange->Item[1][1];
While writing and modifying individual cells is just as straightforward:
pRange->Item[1][1] = 5.4321;
For example, I use the following simple Excel worksheet with a few modified cells:
I then run the example code in order to read the cell values, modify them and save the spreadsheet respectively:
Upon re-opening the spreadsheet “book.xls” notice how the cells have been updated and saved:
Full code listing: Office 2003 Example
#include <iostream>
// Make sure your file paths for MSO.DLL, VBE6EXT.OLB and EXCEL.EXE are 100% correct
// For 32-bit systems the file path is usually "C:\\Program Files\\..."
// For 64-bit systems the file path is usually "C:\\Program Files (x86)\\..."
// #import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\OFFICE11\\MSO.DLL"
#import "C:\\Program Files\\Common Files\\Microsoft Shared\\OFFICE11\\MSO.DLL" \
rename( "RGB", "MSORGB" )
using namespace Office;
//#import "C:\\Program Files (x86)\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"
#import "C:\Program Files\\Common Files\\microsoft shared\\VBA\\VBA6\\VBE6EXT.OLB"
using namespace VBIDE;
// #import "C:\\Program Files (x86)\\Microsoft Office\\OFFICE11\\EXCEL.EXE"
#import "C:\\Program Files\\Microsoft Office\\OFFICE11\\EXCEL.EXE" \
rename( "DialogBox", "ExcelDialogBox" ) \
rename( "RGB", "ExcelRGB" ) \
rename( "CopyFile", "ExcelCopyFile" ) \
rename( "ReplaceText", "ExcelReplaceText" )
int main()
{
HRESULT hr = CoInitializeEx(0, COINIT_MULTITHREADED);
if (FAILED(hr))
{
std::cout << "Failed to initialize COM library. Error code = 0x"
<< std::hex << hr << std::endl;
return hr;
}
// Create Excel Application Object pointer
Excel::_ApplicationPtr pXL;
if ( FAILED( pXL.CreateInstance( "Excel.Application" ) ) )
{
std::cout << "Failed to initialize Excel::_Application!" << std::endl;
return 0;
}
// Open the Excel Workbook, but don't make it visible
pXL->Workbooks->Open( L"C:\\dump\\book.xls" );
//pXL->Visible[ 0 ] = false; // sometimes generates error?
pXL->PutVisible(0, FALSE);
// Access Excel Worksheet and return pointer to Worksheet cells
Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;
pWksheet->Name = L"Sheet1";
Excel::RangePtr pRange = pWksheet->Cells;
// Read an Excel data cell. (Note Excel cells start from index = 1)
double value1 = pRange->Item[1][1];
std::cout << "Value in CELL [1][1] = " << value1 << std::endl;
double value2 = pRange->Item[1][2];
std::cout << "Value in CELL [1][2] = " << value2 << std::endl;
// Write/modify Excel data cells + save. (reopen xls file to verify)
std::cout << std::endl;
std::cout << "Modifying Excel cell values..." << std::endl;
std::cout << std::endl;
pRange->Item[1][1] = 5.4321;
pRange->Item[1][2] = 1.1211;
// Output new value
value1 = pRange->Item[1][1];
std::cout << "New value in CELL [1][1] = " << value1 << std::endl;
value2 = pRange->Item[1][2];
std::cout << "New value in CELL [1][2] = " << value2 << std::endl;
// Switch off alert prompting to save as
pXL->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_FALSE );
// Save the values in book.xml and release resources
pWksheet->SaveAs( "C:\\dump\\book.xls" );
pWksheet->Release();
// And switch back on again...
pXL->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_TRUE );
pXL->Quit();
return 0;
}
Full code listing: Office 2013 Example
The following example was also seen to work on a more recent version of Excel (2013).
One or two differences in where the pre-requisite import files, executables etc were located but that’s about it:
#include <iostream>
// Make sure your file paths for MSO.DLL, VBE6EXT.OLB and EXCEL.EXE are 100% correct
#import "C:\\Program Files\\Microsoft Office 15\\root\\vfs\\ProgramFilesCommonX86\\Microsoft Shared\\OFFICE15\\MSO.DLL" \
rename( "RGB", "MSORGB" ) \
rename( "DocumentProperties", "DocumentPropertiesXL" ) no_dual_interfaces
using namespace Office;
#import "C:\\Program Files\\Microsoft Office 15\\root\\vfs\\ProgramFilesCommonX86\\Microsoft Shared\\VBA\\VBA6\\VBE6EXT.OLB"
using namespace VBIDE;
#import "C:\\Program Files\\Microsoft Office 15\\root\\office15\\EXCEL.EXE" \
exclude("IFont", "IPicture") \
rename( "DialogBox", "ExcelDialogBox" ) \
rename( "RGB", "ExcelRGB" ) \
rename( "CopyFile", "ExcelCopyFile" ) \
rename( "ReplaceText", "ExcelReplaceText" )
int main()
{
HRESULT hr = CoInitializeEx(0, COINIT_MULTITHREADED);
if (FAILED(hr))
{
std::cout << "Failed to initialize COM library. Error code = 0x"
<< std::hex << hr << std::endl;
return hr;
}
// Create Excel Application Object pointer
Excel::_ApplicationPtr pXL;
if ( FAILED( pXL.CreateInstance( "Excel.Application" ) ) )
{
std::cout << "Failed to initialize Excel::_Application!" << std::endl;
return 0;
}
// Open the Excel Workbook, but don't make it visible
pXL->Workbooks->Open( L"C:\\dump\\book.xls" );
//pXL->Visible[ 0 ] = false; // sometimes generates error?
pXL->PutVisible(0, FALSE);
// Access Excel Worksheet and return pointer to Worksheet cells
Excel::_WorksheetPtr pWksheet = pXL->ActiveSheet;
pWksheet->Name = L"Sheet1";
Excel::RangePtr pRange = pWksheet->Cells;
// Read an Excel data cell. (Note Excel cells start from index = 1)
double value1 = pRange->Item[1][1];
std::cout << "Value in CELL [1][1] = " << value1 << std::endl;
double value2 = pRange->Item[1][2];
std::cout << "Value in CELL [1][2] = " << value2 << std::endl;
// Write/modify Excel data cells + save. (reopen xls file to verify)
std::cout << std::endl;
std::cout << "Modifying Excel cell values..." << std::endl;
std::cout << std::endl;
pRange->Item[1][1] = 5.4321;
pRange->Item[1][2] = 1.1211;
// Output new value
value1 = pRange->Item[1][1];
std::cout << "New value in CELL [1][1] = " << value1 << std::endl;
value2 = pRange->Item[1][2];
std::cout << "New value in CELL [1][2] = " << value2 << std::endl;
// Switch off alert prompting to save as
pXL->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_FALSE );
// Save the values in book.xml and release resources
pWksheet->SaveAs( "C:\\dump\\book.xls" );
pWksheet->Release();
// And switch back on again...
pXL->PutDisplayAlerts( LOCALE_USER_DEFAULT, VARIANT_TRUE );
pXL->Quit();
return 0;
}
One final thing – please be aware that any existing Excel processes still running in the background must be closed down before running this program, otherwise errors are likely to occur. For example you may be debugging this program and then decide to exit before pXL->Quit() is called:
