Read in/write out to/from Micrsoft excel 2010

[motleyCode]

I need to know what to include, as well as the operable commands I can use, to have my c++ program read data in from an excel file as well as write data to that excel file. I know I need to include fstream and open the input and output files. What I don't know are the commands to do this for an excel document. I need to know how to tell the program to go to the next row or column when reading in or writing to the file. Any help would be greatly appreciated. Thank you!

[Aramil of Elixia]

just fstream u just have know the file format excel uses

[motleyCode]

I know i'll save the excel file as .xlsx, but what I can't figure out is how to tell the computer to advance from one cell to the next. Everytime I write to the file it only puts data in cell A1. I have tried using the tab command, but to no avail :(

[Disch]

.xlsx is an extremely complicated format. You will not be able to read/write that unless you read a fat technical doc describing the format (could be easily 30+ pages) -- if you can even find such a document.

Another alternative is to look for a library which reads/writes them. But I wouldn't hold my breath there either.


The easiest way to I/O with excel is with .csv files. Very simply, a comma separates the columns, and a newline separates the row:

A1,B1,C1
A2,B2,C2
A3,B3,C3

Excel will read those files and build a spreadsheet out of them appropriately. But note that .csv is not .xlsx... and if you save a spreadsheet as .xlsx and try to read it this way you will fail horribly.

[motleyCode]

Thank you for this. I'm still a little fuzzy on how to implement this now though.

[Disch]

yourfile << "cell A1,cell B1,cell C1" << endl;
yourfile << "cell A2,B2,C2" << endl;
... etc

[majidkamali1370]

A big disadvantage to csv files is that you can not format cells and text. (e.g create borders etc)

[motleyCode]

Thank you, and all I need to include is fstream and make it a .csv file?

[Aramil of Elixia]

yep

[motleyCode]

This is wonderful, I just put a simple program together to create a .csv file like you taught me and it works. Now wht if I wanted to read in from this file, how would I access certain cells?

[Aramil of Elixia]

one of two ways yourfile >> var or getline(yourfile, string)

[motleyCode]

#include <iostream>
#include <fstream>
#include<string>
#include <iomanip>
using namespace std;
int main()
{
	ifstream indata;
	ofstream outdata;
	outdata.open("aaron.csv", ios::app);
	outdata << "a1,b1,c1" << endl;
	outdata << "a2,b2,c2" << endl;

	indata.open("aaron.csv");
	cout << indata << "\n\n";//This gives me a memory address

	cout << getline(indata,"a1");//this tells me I have too few agruments for the function call

	system("pause");
	return 0;
}

What am I doing wrong?

[Aramil of Elixia]

ok first indata is an object not a variable so you cant put it in the output stream: it doesnt have a value;
second do getline above the cout. and i didnt mean a literal string i meant a varaible of type string and then print that

[motleyCode]

#include <iostream>
#include <fstream>
#include<string>
#include <iomanip>
using namespace std;
int main()
{
	ifstream indata;
	ofstream outdata;
	outdata.open("aaron.csv", ios::app);
	outdata << "a1,b1,c1" << endl;
	outdata << "a2,b2,c2" << endl;

	indata.open("aaron.csv");
	string cell1;

	indata >> cell1;

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

	system("pause");
	return 0;
}

THis gives me the first row of the first three columns. How can I select to only output a specific cell in any row and/or column? For example the code above prints a1,b1,c1 with the commas. I need to be able to print the data in any given cell without the commas.

[Aramil of Elixia]

dont know. wait for someone else to wake up and see this. im only good with whole lines and txt files

[Computergeek01]

I'm going to be TFG for a moment, if you are working with any applications from the MS Office suite then VBS is the language you want to know. This is a scripting language but it is designed to interface with MS products on pretty much any level you could want to.

Disch is right in that xlsx is an extremely complicated format and it would be many pages of documentation, however since MS actually WANTS you to be able to use their product they have given us ole2.h. Now these are COM objects but don't be intimidated by that, it's complex but now complicated if that makes any sense and if your carrier revolves around automating\interfacing with MS Office then it is well worth your time to learn it. Last time I checked Mingw did not support COM %100 (although some functionality was present), some of the header files are missing and a few of the libraries are not compiled for it so I suggest using the MS-VS IDE if you are not already. Here is a tutorial from MSDN: http://support.microsoft.com/kb/216686

You could store the entire document in a 2D array of strings, but depending on the size this may not be an option. If I'm right and you're a beginner this would be the best option. In this case you set the "getline()" delimiter to a comma "," and use that to designate the data's position in 'X'. Then when you see a newline you promote the data's position in 'Y'.

[motleyCode]

Thank you all for the responses. I have learned from each one of them and I am truly greatful. Let me explain exactly what I am doing in order to give you al la better idea of what I need to know. I'm tasked with simulating elevators. Basically I need to determine how many elevators a building needs in order to service at least 95% of customers in one minute or less. There are five floors, floors 2-5 each have 100 employees, 200 of those employees leave for lunch between 12 and 12:15 and return between 12:45 and 1:00. the customers arrive in a random fashion at a rate of about 1 every five minutes. The employees arrive between 7:30 and 8:00 and leave between 5:00 and 5:30. no one works on the first floor. The simulation must output data to a .csv file.

The issue I need help with is this: I have to count each customer as they enter, log when they press the elevator call button, log the floor they go to, log how long they stay on that floor conducting business(which is set to be between 15 and 45 minutes) log when they press the call button again and log what time they left the building. I also must log the time it took the elevator to service that particular customer.

I would like for each customer to have his/her own row in the csv file where the columns are the various logs I need to keep for them. I would also like to know how to access a specific row or column at any given time to edit it, for example the service time. I have to log the customer's service time the first time he/she presses the call button. other things take place and then I have to add the service time from the second call to the file and access the first service time in order to compute the total service time.

I'm being forced to use MSVS 2010 and code in C++.

[Disch]

Since this is a relatively small data set, accessing individual rows/columns should not be done during the file I/O process. You should read the entire file into an easier to manipulate runtime structure (like a map), then make your modifications, then write the structure back to the file in its entirety.

[oxod]

If your topic is still urgent and the amount of data you have can be accumulated inside the process, you can generate output report in .xslx format using this library - http://sourceforge.net/projects/simplexlsx/