How to read an Excel file

The previous post was about to read an xml file. Just like that, Excel(xls) files are another commonly used format to store data in our applications. In this post we will see how to read an excel file.

There are mainly two methods to read an excel file.

1) Excel Viewer ActiveX control

2) ODBC method

Using Microsoft office ActiveX control is the direct and easiest method, but it has some system dependencies  to load the file. So its better to choose ODBC method.  ODBC reading using two MFC classes CDatabase and CRecordset to read the file.  Please see the following example to get a clear idea.

See below sample code to read the content of CountryCapitalInfo.xls file.

ReadExcel.h

#pragma once

#include 

class CReadExcel
{
public: /** Public constructor/destructor */

    /** Constructor. */
    CReadExcel(void); 

    /** Destructor. */
    ~CReadExcel(void);

private: /** Prevent object cloning/copying */

    /** Copy constructor. */
    CReadExcel(const CReadExcel&);  

    /** Assignment operator overloaded */
    CReadExcel& operator=(const CReadExcel&);

public:

    /** Read the given xls file and fill the file data to an arrary.
    Table name must be Product_Info*/
    BOOL ReadExcelFile(/*[in]*/ CString& p_strFilePath,
                       /*[out]*/ std::vector& p_arrProductDetails );

private:

    /** Get the name of the Excel-ODBC driver */
    CString GetExcelDriver();
};

ReadExcel.cpp

#include "StdAfx.h"
#include "ReadExcel.h"
#include "odbcinst.h"
#include "afxdb.h"

CReadExcel::CReadExcel(void)
{
}

CReadExcel::~CReadExcel(void)
{
}

BOOL CReadExcel::ReadExcelFile(/*[in]*/ CString& p_strFilePath,
                              /*[out]*/ std::vector& p_arrProductDetails)
{
    CDatabase   database;
    CString     strDriver;
    CString     strDsn;	

    // Retrieve the name of the Excel driver.
    strDriver = GetExcelDriver();
    if (strDriver.IsEmpty())
    {
        AfxMessageBox(_T("No excel ODBC driver."));
        return FALSE;
    }

    strDsn.Format(_T("ODBC;DRIVER={%s};DSN='';DBQ=%s"), strDriver, p_strFilePath);

    try
    {
        // Open the database
        database.Open(NULL,false,false,strDsn);

        CRecordset recset( &database );

        CString strSql;

        // Build the SQL string
        strSql.Format(_T("SELECT * FROM %s"), _T("MyTable"));

        recset.Open(CRecordset::forwardOnly, strSql, CRecordset::readOnly);

        unsigned long count = recset.GetRecordCount() ; 

        CString strProductInfo;
        strProductInfo.Format(_T("%s%s%s"), _T("Country"), _T("-"), _T("Capital"));
        p_arrProductDetails.push_back(strProductInfo);

        CString str1, str2;
        CString strItem1, strItem2;
        while( !recset.IsEOF() )
        {
            str1.Empty();
            str2.Empty();

            // Read the result line
            recset.GetFieldValue(_T("Country"), strItem1);
            str1 += strItem1;    

            recset.GetFieldValue(_T("Capital"), strItem2);
            str2 += strItem2;

            strProductInfo.Format(_T("%s%s%s"), str1, _T("-"), str2);
            p_arrProductDetails.push_back(strProductInfo);

            // Skip to the next resultline
            recset.MoveNext();
        }
        // Close the database
        database.Close();

    }
    catch(CDBException e)
    {
        AfxMessageBox(_T("Database error: ") + e.m_strError);
        return FALSE;
    }
    catch(...)
    {
        AfxMessageBox(_T("Could not open  the excel file."));
        return FALSE;
    }

    return TRUE;
}

CString CReadExcel::GetExcelDriver()
{
    char szBuf[2001];
    WORD cbBufMax = 2000;
    WORD cbBufOut;
    char *pszBuf = szBuf;
    CString strDriver;

    // Get the names of the installed drivers
    if (!SQLGetInstalledDrivers(szBuf, cbBufMax,&cbBufOut))
        return "";

    // Search for the driver...
    do
    {
        if( strstr( pszBuf, _T("Excel") ) != 0 )
        {
            // Found !
            strDriver = CString( pszBuf );
            break;
        }
        pszBuf = strchr( pszBuf, _T('') ) + 1;
    }
    while (pszBuf[1] != _T(''));

    return strDriver;
}

TestCode

BOOL ReadExcelFile(CString p_strFilePath)
{
 CWaitCursor wait;
 CReadExcel readExcel;
 std::vector<CString> arrExcelInfo;
 if (!readExcel.ReadExcelFile(p_strFilePath, arrExcelInfo))
 {
  return FALSE;
 }
 return TRUE;
}

 

Don’t forget to include the header files odbcinst.h and afxdb.h to your project.
One of the drawbacks of this method is that you have to set a name for the data section (Insert->Names) in the excel sheet.