Thread: How to use values in Excel for calculations in C++

Hello,
I'm trying to use numerical data that is in one column on an Excel spreadsheet for calculations of mean and standard deviation in C++. I have written a program that performs this same task but from a .txt file. Can anyone help me in the right direction? This is an assignment for an Intro to C++ class, and we can't have Excel calculate the mean or standard deviation and we can't convert the excel spreadsheet to .csv file. I've spent all week reading tutorials and forum posts and decided to ask the community directly. Thank You for any help!

Here is what I have for the mean and standard deviation from a .txt file...

Code:

#include <fstream>//using data from file stream
#include <iostream>//allows user input and output
#include <cmath>//allows math functions
using namespace std;
void Read_Disk_File();//declares the void function "Read_Disk_File( )"
void Sum_and_Mean();//declares the void function "Sum_and_Mean( )"
void Standard_Dev();//declares the void function "Standard_Dev( )"
void Display_Results();//declares the void function "Display_Results( )"

int i, n;//integer
float sum, xx[1000];//variable 'sum' and 'xx', real numbers, 1000 spaces available for 'xx'
double mean = 0.0, sd = 0.0, a=0.0;


int main()
{
           Read_Disk_File();//calls Read_Disk_File function
           Sum_and_Mean();// calls Sum_and_Mean function
           Standard_Dev();//calls Standard_Dev function
           Display_Results();//calls Display_Results function
}
                            
/***********************************************************************************************************
VOID FUNCTION Read_Disk_File
***********************************************************************************************************/

void Read_Disk_File()//defines the void function "Read_Disk_File( )"      
{
          ifstream xxx;//provides an interface to read data from files as input streams
           xxx.open("MyData.txt");//opens a text file named "MyData.txt"
           //===================================================================
           i = 0;//assignment statement, assigns value of 0 to 'i'
           while(!xxx.eof())//while not end of file, keep reading the file(somewhere at the end of the file there is a end of file marker)
           {
                            xxx>>xx[i];
                            cout<<xx[i]<<endl;//prints to screen the array 'xx' as read from the .txt file
                            if(i>32000)//if the loop repeats more than 32000 times the cycle will break
                            break;//see above comment..
                            i++;
           }
}                 
/***********************************************************************************************************
VOID FUNCTION Sum_and_Mean
***********************************************************************************************************/

void Sum_and_Mean()//defines the void function "Sum_and_Mean( )"
{    
     n=i-1;//gives 'n' a value
           
           //calculates the sum
           sum = 0;
           for(i=0; i<=n-1; i++)
           {
           sum = sum + xx[i];//loops calculation 'i' times for the 'aNumber's entered by user (or sum += xx[i])
           }
           //calculates the mean
           mean = sum/n;//calculates the mean
}

/***********************************************************************************************************
VOID FUNCTION Standard_Dev
***********************************************************************************************************/

void Standard_Dev()//defines the void function "Standard_Dev( )"
{     
     //calculates the standard deviation

           a=0;
           for(i=0; i<=n-1; i++)
           {
           a += pow((xx[i] - mean),2);//loops calculation 'n' times for the number of digits in the array
           }
           sd = sqrt( a/n );//calculates the square root of 'a' divided by the number of numbers
}

/***********************************************************************************************************
VOID FUNCTION Display_Results
***********************************************************************************************************/

void Display_Results()//defines the void function "Display_Results( )"
{
     //displays the sum, mean, and standard deviation
           
           cout<<"the sum is.. "<<sum<<endl;
           cout<<"the mean is.. "<<mean<<endl;
           cout<<"the standard deviation is.. "<< sd <<endl;//puts the standard deviation on the screen

           system("pause");//pauses after display until user hits a key
           
}

Start here:
Microsoft Office Binary (doc, xls, ppt) File Formats
to figure out the Excel file format. It isn't just rows of cell data.

edit: nm

Yes, I'm a new member.

Wow! Thank you 'rdrast', so I looked through the 349 page pdf document on Excel and really don't understand a bit of it. I'm not looking for someone to provide me with code, but isn't there some relatively simple way to call on data stored in the cells of an excel sheet? This is an intro to C++ class, I can't imagine the professor is expecting us to learn excel's binary file format.

Any other ideas, hints, or tips? Thanks

Reading values directly from an excel (.xls) file is *not* a beginner's exercise. If you're not allow to export to a .csv or a similar text file format, you should really be having a chat with your professor as to what exactly he expect from you.

Ok, after all of this reading I've been doing.. That's the conclusion I was coming to. I'm glad to hear it from someone else! Thank You!

Wow, 349 pages of documentation. Ouch. I think your professor is asking too much. Reading a TGA file might be more inline with "Intro to C++" than reading a ridiculously complicated file type.

Originally Posted by AndrewFluck

Yes, I'm a new member.

Wow! Thank you 'rdrast', so I looked through the 349 page pdf document on Excel and really don't understand a bit of it. I'm not looking for someone to provide me with code, but isn't there some relatively simple way to call on data stored in the cells of an excel sheet? This is an intro to C++ class, I can't imagine the professor is expecting us to learn excel's binary file format.

Any other ideas, hints, or tips? Thanks

Welcome, and yes, it is not a simple matter. Specifying the XLS format is rather rude. I'd think as a beginner you would first work in something much simpler, like .CSV, but /shrug.

The link wasn't meant as a joke, obviously. It's just that in an Excel sheet, the data is only the tiniest fraction; the rest has to deal with everything from cell by cell formatting, to formulas, to potential data-access, to well, just about anything.

If I had any 'Simple' code to extract information, I'd let you have it. Generally, when I need to do that, I use Windows Automation or ODBC to actually read it. Any chance that is what your instructor has in mind?