Reading from an Excel file (ODBC?)

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.

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

BTW, with that one, you could even reconsider to make that feature cross-platform *g*

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

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.

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

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)

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

Well deserved points... thanks Alex!