C++ – Use Excel from c++ (Qt) ActiveX

A quick post to show you how to use Excel from C++.

Header

#ifndef excel_h
#define excel_h

//ATL COM includes;
#define Uses_MSO2002
//#define Uses_MSO2000
//#define Uses_MSO97

// MS Office XP
#ifdef Uses_MSO2002
#import "C:\Program Files\Common Files\Microsoft Shared\Office10\mso.dll" \
rename("RGB","_RGB")
#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\vbe6ext.olb"
#import "C:\Program Files\Microsoft Office\Office10\excel.exe" \
	rename("RGB","_RGB") \
	rename("DialogBox","_DialogBox") \
	rename("CopyFile","_CopyFile") \
	rename("ReplaceText","_ReplaceText") \
exclude("IFont","IPicture","_IMsoDispObj")
#endif

// MS Office 2000
#ifdef Uses_MSO2000
#import "C:\CompilationExcel\mso9.dll" \
rename("RGB","_RGB")
#import "C:\CompilationExcel\vbe6ext.olb"
#import "C:\CompilationExcel\excel9.olb" \
	rename("DialogBox","_DialogBox") \
	rename("RGB","_RGB") \
	rename("CopyFile","_CopyFile") \
	rename("ReplaceText","_ReplaceText") \
exclude("IFont","IPicture")
#endif

// MS Office 97
#ifdef Uses_MSO97
#import ".\Office97\mso97.dll"
#import ".\Office97\vbeext1.olb"
#import ".\Office97\excel8.olb" \
	rename("DialogBox","_DialogBox") \
	rename("RGB","_RGB") \
exclude("IFont","IPicture")
#endif

#endif

Code

In this code, we create an Excel App (you can see it in your process).
We get a range of values (from cells names) and put it on a QVector.

CoInitialize(NULL);
// Create app
Excel::_ApplicationPtr mpXL;
mpXL.CreateInstance(L"Excel.Application");
mpXL->PutDisplayAlerts(0, FALSE);
// load from a QString filename
Excel::_WorkbookPtr pBook  = mpXL->Workbooks->Open(fileName.replace('/','\\').toAscii().data());
// set active sheet - index starts from 1
Excel::_WorksheetPtr pSheet = pBook->Sheets->Item[ 1 ];
// Get range
Excel::RangePtr pRange = pSheet->GetRange( _bstr_t( L"BL2" ), _bstr_t( L"HH2" ) );
// to store data
QVector< double > fromExcel;

// for each cells
for(int iColumn = 1 ; iColumn <= 153 ; ++iColumn){
	_variant_t  vItem = pRange->Item[ 1 ][ iColumn ]; // index starts from 1 not 0
	_bstr_t     bstrText( vItem );
	// convert data
	fromExcel.append(QString(bstrText).toDouble());
}

// Close Excel app
mpXL->Quit();
CoUninitialize();

Another example

int main(int argc, char *argv[])
{
    CoInitialize(NULL);

	Excel::_ApplicationPtr mpXL;
	mpXL.CreateInstance(L"Excel.Application");
	mpXL->PutDisplayAlerts(0, FALSE);
	mpXL->put_Visible(10, true);

	//we can add a new workbook
	//_WorkbookPtr workbook = excel->Workbooks->Add(static_cast<long>(Excel::xlWorksheet));
	//or open a file
	Excel::_WorkbookPtr pBook  = mpXL->Workbooks->Open("test.xls");

	//we can get the current active sheet
	//_WorksheetPtr worksheet = excel->ActiveSheet;
	//or we can choose one
	Excel::_WorksheetPtr pSheet = pBook->Sheets->Item[ 1 ];

	//we can get a ranges
	//Excel::RangePtr pRange = pSheet->GetRange( _bstr_t( L"A1" ), _bstr_t( L"A" ) );
	//or we can take all cells
	Excel::RangePtr pRange = pSheet->Cells;

	//put values
	pRange->Item[2][1] = 0.86;
	pRange->Item[3][1] = "=0.5*2";

	//get a value
	_variant_t  vItem = pRange->Item[3][1];
	_bstr_t     bstrText( vItem );

	//will print "1" (=0.5*2)
	qWarning(QString(bstrText).toLatin1());

	//wait
	::Sleep(4000);

	//we can save it with
	//pSheet->SaveAs("c:\\test.xls");

	pBook->Close();
	mpXL->Quit();

	CoUninitialize(); 

    return 0;
}

Resources

This can help : http://support.microsoft.com/default.aspx?scid=kb;en-us;178782