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

This is a discussion on How to use values in Excel for calculations in C++ within the C++ Programming forums, part of the General Programming Boards category; Hello, I'm trying to use numerical data that is in one column on an Excel spreadsheet for calculations of mean ...

  1. #1
    Registered User
    Join Date
    Oct 2009
    Posts
    3

    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
               
    }

  2. #2
    Registered User
    Join Date
    Aug 2006
    Posts
    99
    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.

  3. #3
    3735928559
    Join Date
    Mar 2008
    Location
    RTP
    Posts
    839
    edit: nm

  4. #4
    Registered User
    Join Date
    Oct 2009
    Posts
    3
    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

  5. #5
    Registered User
    Join Date
    Oct 2006
    Posts
    250
    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.

  6. #6
    Registered User
    Join Date
    Oct 2009
    Posts
    3
    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!

  7. #7
    Registered User
    Join Date
    Sep 2009
    Posts
    63
    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.

  8. #8
    Registered User
    Join Date
    Aug 2006
    Posts
    99
    Quote Originally Posted by AndrewFluck View Post
    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?

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Replies: 1
    Last Post: 12-30-2007, 09:08 AM
  2. Need help with project
    By chrisa777 in forum C++ Programming
    Replies: 5
    Last Post: 06-19-2006, 05:01 PM
  3. Sending values to a control
    By Zyk0tiK in forum C Programming
    Replies: 6
    Last Post: 12-02-2005, 05:29 PM
  4. Struct Values
    By Muphin in forum C++ Programming
    Replies: 5
    Last Post: 08-13-2005, 09:24 PM
  5. Replies: 1
    Last Post: 02-03-2005, 02:33 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21