How to read an Excel file

22 01 2012

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.

Advertisements

Actions

Information

2 responses

27 08 2014
hyoung-taik kim

great article, even if i haven’t tried it yet. anyway when we try to write something to the excel file, do you think that we can still use sql statement with odbc like update or whatever?

23 02 2015
Luigi Wewege

Luigi Wewege

How to read an Excel file | C++ and VC++ Tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: