?
Solved

Reading from an Excel file (ODBC?)

Posted on 2006-07-24
11
Medium Priority
?
1,683 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 total points
ID: 17172750
What about running a free utility to do the job? See e.g. http://en.wikipedia.org/wiki/Xls2csv
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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 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_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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Unlike C#, C++ doesn't have native support for sealing classes (so they cannot be sub-classed). At the cost of a virtual base class pointer it is possible to implement a pseudo sealing mechanism The trick is to virtually inherit from a base class…
IntroductionThis article is the second in a three part article series on the Visual Studio 2008 Debugger.  It provides tips in setting and using breakpoints. If not familiar with this debugger, you can find a basic introduction in the EE article loc…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
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.
Suggested Courses

777 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