Solved

Reading from an Excel file (ODBC?)

Posted on 2006-07-24
11
1,658 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

What is C++ STL?: STL stands for Standard Template Library and is a part of standard C++ libraries. It contains many useful data structures (containers) and algorithms, which can spare you a lot of the time. Today we will look at the STL Vector. …
  Included as part of the C++ Standard Template Library (STL) is a collection of generic containers. Each of these containers serves a different purpose and has different pros and cons. It is often difficult to decide which container to use and …
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

786 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