# How to use values in Excel for calculations in C++

• 10-19-2009
AndrewFluck
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           }```
• 10-19-2009
rdrast
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.
• 10-19-2009
m37h0d
edit: nm
• 10-19-2009
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
• 10-19-2009
MWAAAHAAA
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.
• 10-19-2009
AndrewFluck
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!
• 10-19-2009
Zach_the_Lizard
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.
• 10-21-2009
rdrast
Quote:

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?