Read Spreadsheet Data for math?

[Quan Chi2]

Hello. I hope not to annoy you with such a question.

I'd like to read cell data from specific columns within a spreadsheet. I'm trying to read data within the following format (from the .xls file I'm analyzing).

Wavelength (nm),Absorbance (AU),Std.Dev.
190,-0.333324432373047,0.187723662173536
191,-0.184257030487061,0.169325912513228
192,0.0989446640014648,0.243154257457939
193,-0.196856021881104,0.322099862671817
194,0.0530929565429688,0.250335468281439
195,0.146337985992432,0.301110817821903
196,0.0579915046691895,0.359937145256163
197,-0.309549331665039,0.293385120791022
198,-0.396398544311523,0.2578125

Lets say I'd like to get the average of all of the numbers within the second column? How would I go about doing that? I understand that the comma is used as a delimiter within the rows to separate the cell's association with the columns. How would I go about reading each value after the program encounters the delimiter? I want to use each value for a something mathematical, so I imagine I'd need to store each value as a number somehow.

Main Idea:

-I'd like to read each cell from a column.
-I'd like to use the values within each cell to find an average.

Can someone please help me in doing this?

I thank you very much.

[kvprajapati]

Quan Chi2,

Well, can I see your code?

[Quan Chi2]

What makes you think that I have any code at this point? lol

I'm asking mainly for approaches. What tutorials should I look for? Any recommended API that handles this sort of thing?

[kvprajapati]

Qua Chi2,

You have a CSV (text) file. Use file stream and string stream classes.
>I'm asking mainly for approaches.
Use OOP.
>What tutorials should I look for?
Read C++ posts of io stream
>Any recommended API that handles this sort of thing?
Self-Confident API.

[Quan Chi2]

Thanks, adatapost. But with many Google searches, I've already gathered the information you presented.

Anyone with more detailed answers?

[Quan Chi2]

Anyone? I hate to double post.

[Ancient Dragon]

Its almost impossible to read .xls files directly because they contain proprietary MS-Excel data. But if you really really really have to do it then read this document

The simpler solution is to convert the xls files to cvs files using MS-Excell program. The csv files do not contain all that binary data and reading is straight forward.

[Salem]

Split it up into steps

- read a line, and split at ","
- assign to fields in some structure (vector of vectors perhaps)

and so on.

[Quan Chi2]

Its almost impossible to read .xls files directly because they contain proprietary MS-Excel data. But if you really really really have to do it then read this document

The simpler solution is to convert the xls files to cvs files using MS-Excell program. The csv files do not contain all that binary data and reading is straight forward.

The great thing about this project is that there isn't anything that has to do with MS-Excel at all in the spreadsheets I'm working with.

Wavelength (nm),Absorbance (AU),Std.Dev.
190,-0.333324432373047,0.187723662173536
191,-0.184257030487061,0.169325912513228
192,0.0989446640014648,0.243154257457939

The data, as you can see, is separated by commas, and there is no other data to specify any special file conditions that can only be appropriately interpreted by M$ Excel.

*Note: The (nm) is for nanometers, and the (AU) is for gold. Std.Dev. is for Standard Deviation. This was a spectroscopy report.

[Ancient Dragon]

>>The great thing about this project is that there isn't anything that has to do with MS-Excel at all in the spreadsheets I'm working with.

You are not working with xls files as you initially stated, but comma separated csv file. The first line contains cell titles while the remaing lines contain the data.

use getline() to read each line, ignoring the first line read. After that use getline() in conjunction with stingstream to extract second colum, Something like this:

string line;
ifstream in("fiulename.csv");
getline(in, line); // ignore cell titles
while( getline(in, line) )
{
    float n;
    stringstream str(line);
    while( getline( str, line, ',') )
    {
        n = atof( line.c_str());

       // instead of atof() you could also convert string to float
       // using another stringstream object, like this:
       stringstream str1(line);
       str1 >> n;
       // now do thatever you want with this cell value
   }
}

[Quan Chi2]

>>The great thing about this project is that there isn't anything that has to do with MS-Excel at all in the spreadsheets I'm working with.

You are not working with xls files as you initially stated, but comma separated csv file. The first line contains cell titles while the remaing lines contain the data.

use getline() to read each line, ignoring the first line read. After that use getline() in conjunction with stingstream to extract second colum, Something like this:

string line;
ifstream in("fiulename.csv");
getline(in, line); // ignore cell titles
while( getline(in, line) )
{
    float n;
    stringstream str(line);
    while( getline( str, line, ',') )
    {
        n = atof( line.c_str());

       // instead of atof() you could also convert string to float
       // using another stringstream object, like this:
       stringstream str1(line);
       str1 >> n;
       // now do thatever you want with this cell value
   }
}

You're the man! Thanks a lot.