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
Solved

Reading from an Excel file (ODBC?)

Posted on 2006-07-24
11
1,668 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
maze travler 6 62
TCP/IP Socket - connection close results in data lost 14 140
trigs fail! I thought I knew how to do trignometry 3 75
Adding items to a C# list incrementally 5 29
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. …
C++ Properties One feature missing from standard C++ that you will find in many other Object Oriented Programming languages is something called a Property (http://www.experts-exchange.com/Programming/Languages/CPP/A_3912-Object-Properties-in-C.ht…
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.
The viewer will be introduced to the technique of using vectors in C++. The video will cover how to define a vector, store values in the vector and retrieve data from the values stored in the vector.

809 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