DaniWeb Forum Index > Software Development > C++

Read Spreadsheet Data for math?

Please support our C++ advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Jul 2005

Posts: 67

Reputation: Quan Chi2 is an unknown quantity at this point

Solved Threads: 1

Quan Chi2

Offline

Junior Poster in Training

Read Spreadsheet Data for math?

Jul 3rd, 2009

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

 Help with Code Tags 
 C++ Syntax (Toggle Plain Text) 
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
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.

•

Join Date: Oct 2008

Posts: 2,721

Reputation: adatapost has much to be proud of

Solved Threads: 495

adatapost

Offline

Posting Maven

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

Quan Chi2,

Well, can I see your code?

•

Join Date: Jul 2005

Posts: 67

Reputation: Quan Chi2 is an unknown quantity at this point

Solved Threads: 1

Quan Chi2

Offline

Junior Poster in Training

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

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?

•

Join Date: Oct 2008

Posts: 2,721

Reputation: adatapost has much to be proud of

Solved Threads: 495

adatapost

Offline

Posting Maven

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

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.

•

Join Date: Jul 2005

Posts: 67

Reputation: Quan Chi2 is an unknown quantity at this point

Solved Threads: 1

Quan Chi2

Offline

Junior Poster in Training

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

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

Anyone with more detailed answers?

•

Join Date: Jul 2005

Posts: 67

Reputation: Quan Chi2 is an unknown quantity at this point

Solved Threads: 1

Quan Chi2

Offline

Junior Poster in Training

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

Anyone? I hate to double post.

•

Join Date: Aug 2005

Posts: 15,575

Reputation: Ancient Dragon has a reputation beyond repute

Solved Threads: 1485

Ancient Dragon Ancient Dragon is offline

Offline

Still Learning

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

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.

•

Join Date: Dec 2005

Posts: 5,850

Reputation: Salem has a reputation beyond repute

Solved Threads: 751

Salem

Offline

Void main'ers are DOOMed

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

Split it up into steps

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

and so on.

•

Join Date: Jul 2005

Posts: 67

Reputation: Quan Chi2 is an unknown quantity at this point

Solved Threads: 1

Quan Chi2

Offline

Junior Poster in Training

Re: Read Spreadsheet Data for math?

Jul 3rd, 2009

•

Originally Posted by 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.

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.

 Help with Code Tags 
 C++ Syntax (Toggle Plain Text) 
Wavelength (nm),Absorbance (AU),Std.Dev.
190,-0.333324432373047,0.187723662173536
191,-0.184257030487061,0.169325912513228
192,0.0989446640014648,0.243154257457939
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.

Last edited by Quan Chi2; Jul 3rd, 2009 at 3:32 pm.

•

Join Date: Aug 2005

Posts: 15,575

Reputation: Ancient Dragon has a reputation beyond repute

Solved Threads: 1485

Ancient Dragon Ancient Dragon is offline

Offline

Still Learning

Re: Read Spreadsheet Data for math?

#10

Jul 3rd, 2009

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

 Help with Code Tags 
 C++ Syntax (Toggle Plain Text) 
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
   }
}
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
   }
}