Reading and writing to an excel file.

[rej3kt]

Is it easy enough to do? I've got my classes finally sorted out, and just as an idea I've thought about making my program read a calculation in excel (like for example its a mpg calculator, so it'll write the amount of litres used km or what ever the calc is I've got) and then calculate the mpg, output it to the screen and save it to the excel file.

I'm not looking for someone to do it for me at all, maybe point me in the way to a rescource or tell me if it's too difficult to attempt for a beginner. Thanks guys.

[Pax]

Excel can read CSV (comma seperated values) files. These are very easy to create, so if all you want to do is input some numbers and be able to open them up in Excel, then the answer is yes.

If you want to use more advanced features of Excel, such that Excel would need to save in it's own format, then the answer is no. It's possible, but would be easier for you to just take raw data then do the later manipulation with Excel macros (based on VB I think?).

[rej3kt]

would it be easier to write to a blank text document and pull a forumla off it aswel, or is that not possible?

I do have a spreadsheet which has been created by my mates with some fancy colours and boxes which works out MPG. So logically what I need to do is to create and save the variables: for distance travelled in miles and save it to the cell #17, then create another variable for litres used and save it to the next cell, then read to the next cell which tells me the mpg, might be cell 20 or something.

I've turned it into a .csv and erm wtf?

[jleach]

I'm not sure if it's possible to automate VBA through c++, but if there is a way to do it (it can be done with .net I know... you call the library through the appropriate dll which I don't know offhand, but can find out if it would be of use), you can gain complete control over the file and basically do whatever you want with it.

[rej3kt]

I'm using Linux and code::blocks so can't be using .net.

Also edited my above post

[Pax]

I'm not sure I understand.

You can input strings into a CSV file, which if formatted right Excel will treat as equations. However, Excel (at least, 2007) doesn't think forumulas are appropriate to *save* into CSV, so it will just save the resulting value.

Example:

13,2,=A1*B1

saved in a .csv file can be opened by Excel, and it will read the third cell as an equation and treat it as such.

If you were to make changes however (say changed 2 to 3), and save them, Excel would remove the equation and your file would become:

13,3,39

[rej3kt]

Nevermind I'm going to give up.

What I'm going to do instead is do the calculation in the program then output the MPG into a text document, on the main program list you will be able to call up mpg's from previous cars you've entered which will read them from the text document

[rej3kt]

I've just been reading up on how to read and write to to a .txt file. This is the code I found on the tutorial on this website:

 int main () {
  ofstream myfile;
  myfile.open ("example.txt");
  myfile << "Writing this to a file.\n";
  myfile.close();
  return 0;
}

This is great but how do I adapt it to store the information entered when I use cin? I need to save the information to a text file and a variable

[Pax]

Well, instead of "Writing this to a file", just use the variable that you put data into with cin. So something like:

int main () {
  ofstream myfile;
  double val;
  myfile.open ("example.txt");
  std::cin >> val;
  myfile << val;
  myfile.close();
  return 0;
}

[jleach]

make sure you enter the commas as well, if they aren't already there...

int main () {
  ofstream myfile;
  double val;
  myfile.open ("example.txt");
  std::cin >> val;
  myfile << val << ", ";
  myfile.close();
  return 0;
}

You might want some way to check that the last value going in doesn't have a comma... maybe a flag you can check with an if statement to add the comma except when the flag is off. I don't think you'll be allowed a trailing comma when importing the csv to excel.

hth

[rej3kt]

Basically my program gives you a list of 8 options when you open it: Enter age, colour, value, quit etc.

One option is to retrieve data and another is to save the data.

My plan is to save all the variables to the text file at the min. When my program first runs it asks you to enter your name. My end goal is for the program is save the information and recall it based on the name entered. John Smith for example entered different information to Sam Smith and I want to recall the information saved in the text file from either one.

That's the end goal anyways, I'm stuck on writing to the file at the min.

I'm getting this error when trying to save the information from a class or any variable wether its an int or w/e

/home/rej3kt/Desktop/car_class/car project/main.cpp||In function ‘int main()’:|
/home/rej3kt/Desktop/car_class/car project/main.cpp|65|error: jump to case label|
/home/rej3kt/Desktop/car_class/car project/main.cpp|60|error:   crosses initialization of ‘std::ofstream myfile’|
/home/rej3kt/Desktop/car_class/car project/main.cpp|68|error: jump to case label|
/home/rej3kt/Desktop/car_class/car project/main.cpp|60|error:   crosses initialization of ‘std::ofstream myfile’|
||=== Build finished: 4 errors, 0 warnings ===|

[jleach]

Can you post your int main() code? I'm not sure what the errors mean without seeing the syntax you're using.

[rej3kt]

#include <iostream>
#include <string.h>
#include "car.h"
#include <cstdlib>
#include <fstream>
using namespace std;

int main()
{
    char car_model[50];
    char car_colour[50];
    int car_age;
    int car_value;
    float car_mpg;
    int number;
    char name[50];
   Car c1(car_model, car_colour, car_age, car_value, car_mpg);


    cout << " Please enter your name to start: "<<endl;
    cin.getline(name, 50);

    do{

    cout << "\n\n\nWelcome, please enter a number to go to that section on the menu:\n1) Enter your car model"<<endl;
    cout << "2) Enter your car colour" << endl;
    cout << "3) Enter the age of your age"<<endl;
    cout << "4) Enter the value of your car in GBP"<<endl;
    cout << "5) Enter how many miles per gallon your car does"<<endl;
    cout << "6) Save any informaion entered" <<endl;
    cout << "7) Retrieve any previously entered information" <<endl;
    cout << "8) Exit program"<<endl;
    cin >> number;

 switch (number){
  case 1:
    cout << "Enter your car model: " << endl;
    cin >> car_model;  break;


  case 2:
    cout << "Enter your car colour: " << endl;
    cin >> car_colour; break;

  case 3:
    cout << "Enter the age of your car: " << endl;
    cin >> car_age;    break;

  case 4:
    cout << "Enter the value of your car in GBP: " << endl;
    cin >> car_value;
                       break;
  case 5:
    cout << "Enter how many miles per gallon your car does: " << endl;
    cin >> car_mpg;    break;

  case 6:
   cout << "Saved!"<<endl;

   ofstream myfile;
   myfile.open ("class.txt");
   myfile << c1.getModel();
   myfile.close();

    case 7:
    break;

 case 8:
    cout << "Press enter to close"<<endl;
    return 0;

}

    }while(number != 7);





    cout << "\n\n\n\n\n\n\n\n\n" ;


    cout << "Your car model is: " << c1.getModel() <<endl;
    cout << "Your car colour is: " << c1.getColour() <<endl;
    cout << "Your car is " " years old"<< c1.getAge() <<endl;
    cout << "Your car is worth £" <<c1.getValue() <<endl;
    cout << "Your car does " "mpg" <<c1.getMPG()<<endl;
    return 0;
}

[Pax]

You're not breaking in case 6.

[rej3kt]

Just put a break in and recompiled.

||=== car project, Debug ===|
/home/rej3kt/Desktop/car_class/car project/main.cpp||In function ‘int main()’:|
/home/rej3kt/Desktop/car_class/car project/main.cpp|67|error: jump to case label|
/home/rej3kt/Desktop/car_class/car project/main.cpp|60|error:   crosses initialization of ‘std::ofstream myfile’|
/home/rej3kt/Desktop/car_class/car project/main.cpp|70|error: jump to case label|
/home/rej3kt/Desktop/car_class/car project/main.cpp|60|error:   crosses initialization of ‘std::ofstream myfile’|
||=== Build finished: 4 errors, 0 warnings ===|

[Pax]

Ah, yes, sorry.

switch doesn't like initialising (creating) variables. Move the ofstream myfile; line to above the switch statement. That might (should) would.

[rej3kt]

Getting closer =P, It's created the text file and it's wrote to it. But it's only wrote aload of garbage:

£¿-“$SO I'm assuming that's what's in the variable before you put something in it?

[Pax]

Not sure, if you post your car class that might help. At the moment you're not actually putting anything *into* cl, either. You instantiate your class with blank variables (which are copied into the class), then use those variables later for user input, but don't update anything with the class.

[rej3kt]

I had it all working before I added the switch statement and tried to write to a file:

this is my .cpp say if you need my head

#include "car.h"
#include <string.h>

Car::Car()
{
}

Car::Car(char model[], char colour[], int age, int value, float mpg)
{
    strcpy(car_model, model);
    strcpy(car_colour, colour);
    car_age = age;
    car_value = value;
    car_mpg = mpg;


}

void Car::setModel(char model[])
{
	strcpy(car_model, model);
}
char* Car::getModel()
{
	return car_model;
}



void Car::setColour(char colour[])
{
    strcpy(car_colour, colour);
}
char* Car::getColour()
{
    return car_colour;
}


void Car::setAge(int age)
{
    car_age = age;
}
int Car::getAge()
{
    return car_age;
}


void Car::setValue(int value)
{
    car_value = value;
}
int Car::getValue()
{
    return car_value;
}

void Car::setMPG(float mpg)
{
    car_mpg = mpg;
}
float Car::getMPG()
{
    return car_mpg;
}

[Pax]

Yeh, I think you need to go through your switch statement and add a call to the relevant Car::setX for each field.