Solved

Reading from an Excel file (ODBC?)

Posted on 2006-07-24
11
1,638 Views
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:
http://www.codeproject.com/database/excel_odbc.asp

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)

Regards,
Henrik
0
Comment
Question by:henrikatwork
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 3

Expert Comment

by:mactep13
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") \
  no_dual_interfaces


using namespace std;

int main()
{
  // Initialize COM
  CoInitialize(NULL);
  try
  {
    Excel::_ApplicationPtr excel;
    // Initialize Excel and make sure it's initialized
    HRESULT hr = excel.CreateInstance(L"Excel.Application");
    if(FAILED(hr))
    {
      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
    worksheet->Save();
    workbook->Close(); // Close the workbook
    excel->Quit(); // Quit excel
  }
  catch(_com_error &ce)
  {
    // Handle the error
  }
  CoUninitialize();


  return 0;
}

Hope this helps...
Mactep.
0
 
LVL 1

Author Comment

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

0
 
LVL 86

Assisted Solution

by:jkr
jkr earned 50 total points
ID: 17172750
What about running a free utility to do the job? See e.g. http://en.wikipedia.org/wiki/Xls2csv
0
 
LVL 86

Expert Comment

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

Expert Comment

by:itsmeandnobodyelse
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);
                  switch(col)
                  {
                     case COL1:
                            rec.col1 = entry;    
                            break;
                     case COL2:
                     {
                            istringstream iss(entry);
                            iss >> rec.col2;
                            if (!iss)   // wrong integer
                               return false;
                            break;
                     }
                     case COL3:
                            istringstream iss(entry);
                            iss >> rec.col3;
                            if (!iss)   // wrong double
                               return false;
                            break;
                     default:
                            return false;  // too many columns
                  }
                  arr.push_back(rec);
                  col++;
             }
       }
       ifs.close();
       return true;

}

Regards, Alex
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

by:henrikatwork
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:
Locale::Recode
Unicode::Map
Spreadsheet::ParseExcel
Spreadsheet::ParseExcel::FmtUnicode (should be included with Spreadsheet::ParseExcel)
Text::CSV_XS

I found this application:
http://www.indigostar.com/perl2exe.htm
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.
0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
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
0
 
LVL 1

Author Comment

by:henrikatwork
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)
0
 
LVL 39

Accepted Solution

by:
itsmeandnobodyelse earned 450 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_PROC_ATTR_SEC_DESC_NULL     NULL
#define DEF_THRD_ATTR_SEC_DESC_NULL     NULL
#define DEF_ENV_BLOCK_NULL              NULL
#define DEF_CUR_DIR_NULL                NULL
#define INHERIT_HANDLES_FALSE           FALSE

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;

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

    if (!CreateProcess(NULL,
                       szCmdLine,
                       DEF_PROC_ATTR_SEC_DESC_NULL,
                       DEF_THRD_ATTR_SEC_DESC_NULL,
                       INHERIT_HANDLES_FALSE,
                       DETACHED_PROCESS,
                       DEF_ENV_BLOCK_NULL,
                       DEF_CUR_DIR_NULL,
                       &si,
                       &pi)
       )
    {
        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)
Next


Regards, Alex

0
 
LVL 39

Expert Comment

by:itsmeandnobodyelse
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

0
 
LVL 1

Author Comment

by:henrikatwork
ID: 17185069
Well deserved points... thanks Alex!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Templates For Beginners Or How To Encourage The Compiler To Work For You Introduction This tutorial is targeted at the reader who is, perhaps, familiar with the basics of C++ but would prefer a little slower introduction to the more ad…
Introduction This article is a continuation of the C/C++ Visual Studio Express debugger series. Part 1 provided a quick start guide in using the debugger. Part 2 focused on additional topics in breakpoints. As your assignments become a little more …
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now