Extract data from excel sheet to C++

[featips]

I have an EXCEL spread sheet containing 50 columns, and 23 columns are for numerical data, and 27 columns are for texts.

What I want to do is to extract the numerical data from the spreadsheet, use one vector to hold one column of numerical values.

Previously I used Matlab to do the calculation, now I switch to C++.

I know there are some ways to read data from Excel to C++. Since I am programming under Linux, maybe the best way is first convert xls to csv, and then maybe use getline to read one line, and use strtok to extract numerical values......

Is there a better way (I mean, more efficient, and easier) to do this? Anyone has example code? Or, any online resource available?

Thanks a for help.

 

[IlyaKEightSix]

I'm not sure the exact way it works in C++, but in R, you can simply read in a CSV and it'll record all the data for you from the get-go. I'm guessing C++ has a very similar feature somewhere.

 

[Mr Doe]

I'm guessing C++ has a very similar feature somewhere.

No, it doesn't.

I'm going to save you the trouble of (eventually) reading the terrible documentation of Excel Object Model on MSDN, and direct you to a cool lib - > http://www.libxl.com/ . Upside: very easy to use, clearly written documentation, header only lib. Downside: you need to purchase the "full" version, if you don't, it leaves a nasty comment (something like: "Please buy the LibXL full version to remove this comment") in the first row. Aside from that, I haven't noticed any other restrictions.

I would be glad, if quantnet members would mention other libs.

 

[John Jurcevic]

This is another library that can read/write Excel files.

Excel Reader engine (dll edition): native xls-file parser

The demo is restricted to reading only 50 rows. The programming interface is clunky and there is no documentation but it works once you figure it out.

You might try tracking down the Excel import filters used by OpenOffice Calc or KOffice KSpread.

 

[featips]

According to the download page:
---------------------------------
It will write banner in first row of each spreadsheet and it will be able to read only 100 cells (first row is unavailable). Buy a license key to remove banner and reading restriction.
----------------------------------
So, completely for demo purpose...

No, it doesn't.

I'm going to save you the trouble of (eventually) reading the terrible documentation of Excel Object Model on MSDN, and direct you to a cool lib - > http://www.libxl.com/ . Upside: very easy to use, clearly written documentation, header only lib. Downside: you need to purchase the "full" version, if you don't, it leaves a nasty comment (something like: "Please buy the LibXL full version to remove this comment") in the first row. Aside from that, I haven't noticed any other restrictions.

I would be glad, if quantnet members would mention other libs.

 

[featips]

Since I works under Linux and sometimes back to windows, I prefer the method is system independent. So, I guess the best way is to convert the excel to csv file.

Anyone has suggestion about how to read CSV file? I can practice different method, but if someone has good idea, that may save me a lot of time.

Thanks .

 

[iddqd]

featips-- this is a crappy CSV Reader class I wrote a while ago to do something quickly using all textual data. I don't claim that this is well-written, robust code or anything, but it worked for what i was doing.

You will need to determine how you want to handle text/numerical columns but it's a starting point. If this doesn't work for you there are probably tons of hits that you'll get on Google.

header:

#include <string>
#include <vector>
using namespace std;
class CSVReader
{
public:
       CSVReader();
       ~CSVReader();
       void LoadCSV(string fileName);
       void DestroyContents();
       string getItem( int x, int y );
       int numberOfRows();
       int numberOfColumns();
 
private:
        vector< vector<string> > _data;
 
};

#include "CSVReader.h" 
#include <fstream>
 
CSVReader::CSVReader()
{
}
CSVReader::~CSVReader()
{
}
void CSVReader::LoadCSV(string fileName)
{
      ifstream in(fileName.c_str());
      string element, delimiters = ",\n\r";
      int row = 0;
      char ch;
      _data.push_back( vector <string>() );
      while( in.read( (char*)&ch, 1 ) )
      {
            if( delimiters.find_first_of(ch) == delimiters.npos )
            {
                  element += ch;
            }
            else
            {
                  if( ch != '\r' )
                  {
                        _data[row].push_back( element );
                        element = "";
                        if( ch == '\n' )
                        {
                              _data.push_back( vector <string>() );
                              row++;
                        }
                  }
            }
      }
      if( element.size() > 0 )
            _data[row].push_back( element );
      in.close();
 
}
void CSVReader::DestroyContents()
{
     for(int i = 0; i < _data.size(); i++)
     {
             for(int j = 0; j < _data[i].size(); j++)
                     _data[i].pop_back();
             _data.pop_back();
     }
 
}
string CSVReader::getItem(int x, int y)
{
     return _data[x][y];
}
int CSVReader::numberOfColumns()
{
        if ( _data.size() > 0)
           return _data[0].size();
        else 
             return 0;
}
int CSVReader::numberOfRows()
{
     return _data.size();
}

 

[featips]

Thanks a lot, iddqd, you gave me a nice starting point.....

 

[rloz]

hi featips,

the easiest way to do this is to use the tokenizer function in boost, since it allows u to parse lines using the "," character. I would post my code, but I lost it when my computer got infected by malware. anyway, its a very easy function to use.