Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / productivity / Office

How to Read and Write Excel Files in C++ via ADO

4.85/5 (22 votes)
10 Nov 2010CPOL 114.1K   6.4K  
Using ADO for Access to XLS and XLSX files in C++

Introduction

Sometimes software developers need to export some data to Excel format or read some cells from Excel file. One way to do it without Excel automation is interaction with ADO. In this case, Excel files are treated as database. This method doesn't require Microsoft Excel and quickly enough, but it doesn't support formatting and formulas.

Connection Strings

There are two types of connection strings. First for binary format (xls):

Provider=Microsoft.JET.OLEDB.4.0;Data Source=data.xls;Extended Properties="Excel 8.0"

Second for XML format (xlsx):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=data.xlsx;
Extended Properties="Excel 12.0 Xml" 

If input file doesn't have a header with column names then add ";HDR=NO" in extended properties.

Writing

First create a Connection object:

C++
TESTHR(pCon.CreateInstance(__uuidof(Connection)));
TESTHR(pCon->Open(connStr, "", "", NULL)); 

Afterwards, create Command object and table. Note that name of table is name of sheet:

C++
TESTHR(pCmd.CreateInstance(__uuidof(Command)));
pCmd->ActiveConnection = pCon;       
pCmd->CommandText = "CREATE TABLE MySheet
    (A int, B varchar, C int, D int, E int, F int, G int, H int, I int, J varchar)";   
pCmd->Execute(NULL, NULL, adCmdText); 

Create Recordset and add records with values:

C++
TESTHR(pRec.CreateInstance(__uuidof(Recordset)));
pRec->Open("SELECT * FROM MySheet", _variant_t((IDispatch*)pCon), 
	adOpenKeyset, adLockOptimistic, adCmdText);

for(int i = 0; i < writeRows; ++i)
{
    TESTHR(pRec->AddNew());

    char str[11] = {0}; for(int j = 0; j < 10; ++j) str[j] = 'a' + (rand() % 26);

    pRec->Fields->GetItem("A")->Value = _variant_t(i);            
    pRec->Fields->GetItem("B")->Value = _variant_t(str);            
    pRec->Fields->GetItem("C")->Value = _variant_t(i);
    pRec->Fields->GetItem("D")->Value = _variant_t(i);
    pRec->Fields->GetItem("E")->Value = _variant_t(i);
    pRec->Fields->GetItem("F")->Value = _variant_t(i);
    pRec->Fields->GetItem("G")->Value = _variant_t(i);
    pRec->Fields->GetItem("H")->Value = _variant_t(i);
    pRec->Fields->GetItem("I")->Value = _variant_t(i);
    pRec->Fields->GetItem("J")->Value = _variant_t(str);            
}
TESTHR(pRec->Update());
TESTHR(pRec->Close());

Reading

Create and open Recordset:

C++
TESTHR(pRec.CreateInstance(__uuidof(Recordset)));       
TESTHR(pRec->Open("SELECT * FROM [Sheet1$]", connStr, 
	adOpenStatic, adLockOptimistic, adCmdText));

If name of sheet is unknown, then it's possible to look it up by index:

C++
TESTHR(pCon.CreateInstance(__uuidof(Connection)));
TESTHR(pCon->Open(connStr, "", "", NULL));
    
pSchema = pCon->OpenSchema(adSchemaTables);      

for(int i = 0; i < sheetIndex; ++i) pSchema->MoveNext();

std::string sheetName = 
	(char*)(_bstr_t)pSchema->Fields->GetItem("TABLE_NAME")->Value.bstrVal;

Extract values of cells:

C++
while(!pRec->adoEOF)
{
    for(long i = 0; i < pRec->Fields->GetCount(); ++i)
    {
        if(i > 0) stream << ";";                    
        _variant_t v = pRec->Fields->GetItem(i)->Value;
        if(v.vt == VT_R8)                                           
            stream << v.dblVal;
        if(v.vt == VT_BSTR)
            stream << (char*)(_bstr_t)v.bstrVal;                                
    }                                    
    stream << std::endl;
    pRec->MoveNext();
} 

History

  • 10th November, 2010: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)