Reading from an Excel file (ODBC?)

Posted on 2006-07-24
Medium Priority
Last Modified: 2008-02-01
Hello experts,

I am trying to add a "import from excel"-feature to my C++ application.
I thought it would be a simple task of traversing a couple of cells with an easy-to-use API.
However, as the project is built with standard C++ (no .net or mfc!) the only viable way seems to be using
the rather clumsy ODBC-approach. (I already tossed the idea of making this feature cross platform out the window)

I found this link on the subject:

Unforunately, that code is closely tied to MFC. Also it just pulls names like "demo_table" and "field_1"
out of thin air!

As I've managed to get something similar (Plain-C ODBC) working when reading data from an Access database,
Im supposing doing the same thing with an excel-sheet will be much of the same, except this time I don't
know the name of the sheets and fields.

Im looking for C/C++ code examples to read a couple of cells from a an Excel-sheet (Just reading, no writing).
I would appreciate if somebody could supply that, or provide a link to a website who does.
(Im not interested in having the user manually export the .xls file to a xml or comma-separated text file,
so you don't have to bother suggesting that, unless that can be made automatically without user interaction)

Question by:henrikatwork
  • 4
  • 4
  • 2
  • +1

Expert Comment

ID: 17171953
Here is the example I found on the web... It uses COM. I know you said no MFC or .Net... but maybe com is ok!

#import "C:\Program Files\Microsoft Office\Office\MSO9.DLL"
#import "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
#import "C:\Program Files\Microsoft Office\Office\EXCEL9.OLB" \
  rename("DialogBox", "ExcelDialogBox") \
  rename("RGB", "ExcelRGB") \

using namespace std;

int main()
  // Initialize COM
    Excel::_ApplicationPtr excel;
    // Initialize Excel and make sure it's initialized
    HRESULT hr = excel.CreateInstance(L"Excel.Application");
      char msg[1024] = {0};
      sprintf(msg, "E: There was an error initializing Excel: %d", hr);
      throw std::runtime_error(msg);
    Excel::_WorkbookPtr workbook = excel->Workbooks->Add(static_cast<long>(Excel::xlWorksheet)); // Create the workbook
    Excel::_WorksheetPtr worksheet = excel->ActiveSheet; // Get the active sheet

    worksheet->Open("whatever"); // Save it
    // This is how you put the values into the worksheet
    worksheet->Range["A1"]->Value = "Hello"; // Set a value
    workbook->Close(); // Close the workbook
    excel->Quit(); // Quit excel
  catch(_com_error &ce)
    // Handle the error

  return 0;

Hope this helps...

Author Comment

ID: 17172169
Sorry, I've already seen that and its not really what im looking for.

LVL 86

Assisted Solution

jkr earned 200 total points
ID: 17172750
What about running a free utility to do the job? See e.g. http://en.wikipedia.org/wiki/Xls2csv
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LVL 86

Expert Comment

ID: 17172757
BTW, with that one, you could even reconsider to make that feature cross-platform *g*
LVL 39

Expert Comment

ID: 17173555
After you exported the contents to a .csv file by using the link jkr gave you, it is easy to read and parse the file like that:

#include <string>
#include <vector>
#include <fstream>
#include <sstream>
using namespace std;

struct Record
      string col1;
      int      col2;
      double col3;
      // ...
      enum { COL1, COL2, COL3, MAX_COLS };

bool getDataFromCsv(const string& csvfile, vector<Record>& arr)
       ifstream ifs(csvfile.c_str());
       if (!ifs) return false;
       string line;
       while (getline(ifs, line))
             // a .csv file separates each column entry by ;  (default)
             int pos = 0;
             int lpos = 0;
             int col = 0;
             line += ';';  // add a semicolon at end of line
             Record rec;
             while ((pos = line.find(';', lpos)) != string::npos)
                  string entry = line.substr(lpos, pos - lpos);
                     case COL1:
                            rec.col1 = entry;    
                     case COL2:
                            istringstream iss(entry);
                            iss >> rec.col2;
                            if (!iss)   // wrong integer
                               return false;
                     case COL3:
                            istringstream iss(entry);
                            iss >> rec.col3;
                            if (!iss)   // wrong double
                               return false;
                            return false;  // too many columns
       return true;


Regards, Alex

Author Comment

ID: 17174503
Automatically export the file to cvs? I like the idea... but I can't have the external dependency of perl! (atleast not on windows) Especially not when the script depends on the following modules:
Spreadsheet::ParseExcel::FmtUnicode (should be included with Spreadsheet::ParseExcel)

I found this application:
But it costs money.

What would do is either something that (easily) compiled the perl script, or a similar utility written i C... Or back to the ODBC-approach.
LVL 39

Expert Comment

ID: 17174790
>>>> Automatically export the file to cvs? I like the idea

You might use a vbscribt like the one that can be found at http://www.codecomments.com/archive305-2006-4-897480.html.

You could do automation in C++ as well but it would be really hard work while it is relatively simple using VB.

Simply start the vbscript by

   WinExec("xls2csv.vbs", SW_HIDE);

Regards, Alex

Author Comment

ID: 17176513
Hello Alex,

Your vb-script solution actually works! Well, not on my computer that didn't have excel installed... I suppose it uses the excel-odbc driver?

Anyway, common sense says that i should split the points between you and jkr (400/100 maybe?)
However as this is a 500pt questions I've decided to push my luck a little further...

1) WinExec didn't work as it is can only used for .exe files (?) I got it working with ShellExecute. So the question is: How do i halt the progress of my c++ application until the script has finished executing?

2) (More of a vb-script question) The script pops up an annoying "Function XLS2CVS = FALSE/TRUE" messagebox at the end, how do i get rid of it?

3) (vb-script) How do I pass parameters to the script? For instance the path to the .xls file and the .csv output file?

Actually none of these questions have anything to do with C++ (especially not 2 and 3, which I could ask in the vb-script forum)
LVL 39

Accepted Solution

itsmeandnobodyelse earned 1800 total points
ID: 17177107
>>> WinExec didn't work as it is can only used for .exe files

You might start cscript.exe rather than the vbs script itself.

>>>> How do i halt the progress of my c++ application until the script has finished executing?

You could poll until the .csv can be opened successfully

    ifstream csv;
    int count = 0;
    // wait for 5 seconds maximum
    while (++count < 50 && csv.open(csvfile.c_str()))
          Sleep(100);   // sleep 10 milliseconds
    if (!csv)
       return false;  // error

If you didn't want it stupid simple you might do that:

#include <string.h>
#include <windows.h>

#define MAX_LEN_CMDLINE                 512
#define DEF_ENV_BLOCK_NULL              NULL
#define DEF_CUR_DIR_NULL                NULL

int startVBS(const string& strVBS)
    char szCmdLine[MAX_LEN_CMDLINE] = "cscript ";
    strcat(szCmdLine, szArgs[n]);
    strcat(szCmdLine, strVBS.c_str());

    STARTUPINFO         si;
    memset(&si, 0, sizeof(STARTUPINFO));

    si.cb           = sizeof(STARTUPINFO);
    si.dwFlags      = STARTF_USESHOWWINDOW;
    si.wShowWindow  = SW_SHOWMINIMIZED;

    memset(&pi, 0, sizeof(PROCESS_INFORMATION));

    if (!CreateProcess(NULL,
        return GetLastError();

    // Wait until child process exits.
    WaitForSingleObject( pi.hProcess, INFINITE );

    // Close process and thread handles.
    CloseHandle( pi.hProcess );
    CloseHandle( pi.hThread );
    return 0;

2) The script pops up an annoying "Function XLS2CVS = FALSE/TRUE" messagebox

I don't know much of VB but I would comment the following line:

>>>> WScript.Echo "Function XLS2CSV() = " & XLS2CSV()

3) (vb-script) How do I pass parameters to the script?

Something like ...

' Display all command-line parameters
Set objArgs = Wscript.Arguments
For I = 0 to objArgs.Count - 1
          Wscript.Echo objArgs(I)

Regards, Alex

LVL 39

Expert Comment

ID: 17177123
>>>> #include <string.h>

replace that by

#include <string>
using namespace std;

I copied it from an older source which included old string header.

Regards, Alex


Author Comment

ID: 17185069
Well deserved points... thanks Alex!

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Often, when implementing a feature, you won't know how certain events should be handled at the point where they occur and you'd rather defer to the user of your function or class. For example, a XML parser will extract a tag from the source code, wh…
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question