read excel cells via C++

[chtsolak]

Hi, i am relatively new to C++, and i know that it this question is asked many many times, but i did not find any simple solution...



so i have an excel with 7 sheets and each sheet has about 100 numbers and words but i only need the numbers.

The excel file doesn't change and the positions (coordinates) of the numbers are constant.



So how can i read, for example, the numbers of the cells (4,5) (5,5) of sheet2 which is named "power"?

and put them into 2 variables?



i searched a lot in the internet but i did not find anything specific for my simple program...



I am using windows 7 64bit and visual studio 2010



Thank you

Your help will be much appreciated.

[LB]

This is an extremely difficult task because Excel documents are stored in a variety of complex formats. You're better off with plain-text files.

If you really want to use Excel, you're going to have to do a LOT of digging around to figure out how to either communicate with it when it is open or how to parse an Excel file.

[bartoli]

On windows i think there is an ODBC driver to access excel files by SQL statements. Then wyou can use the ODBC library for C. But that will require some additionnal setup on each computer the program will be used.
If you excel file doesn't change regularly, i'd seriously advise converting it to CSV or something simpler to parse.

[Tresky]

Here is a library for just that.
http://www.libxl.com/

[chtsolak]

yes but libxl costs 200$
and the free demo has a limit 100 cells which are not enough...

Is there any library like this (not need to have all these fancy features) free, or at a lower price?

[LB]

Considering that people have to pay for Excel anyway, I'd say the chances are quite low...

[moorecm]

Export the spreadsheet as a .csv, which is just a comma-delimited text file. Then you can parse it fairly easily.

[ceruleus]

csv yo

[Tresky]

If you do find something for this it will end up being something like this:

The code will be hidden within the deep depths of Google's endless pages. It was written by dungeon_conquerer69 and the only existing documentation is scribbled in crayon on a piece of construction paper that is being held down onto his desk by a peanut butter sculpture of a pterodactyl. When you try to download this library he will infest your computer with many a virus and you won't be able to use the library because he fooled you and there is no library. This is going to force you to use a .csv file.

Save yourself the trouble and just use a .csv file. ;D

Cheers,
Tresky!

[webJose]

Much of the world uses Excel and Office in general. Chances are your customers have it installed. Learn COM and OLE Automation and automate Excel.

And yes, I do realize this is advanced stuff. Bummer. It is what it is.

[chtsolak]

Yes, i want to use csv me too, but

I want my program to use this excel and not csv because people (many) who have it and use it with other programs are used to it and it will be difficult for them to make a series of changes before running my program.

The file has 9 sheets. so only one sheet will be converted to csv...

So you will tell me to make 9 different files.
Yes but then people who already use the specific excel file will now have 9 different files and they will have to save them as csvs first...

My work has to make their lives simpler not more complicated...

IMPORTANT: i want the program can be used in any computer with same excel ending(xls, xlsx)
If this cannot be done i will go straight for csv.


@webJose

Where can find some documentation to try?


Thank you all for the responses, your help is much appreciated.

[SIK]

If you are using the same format excell spread sheet would it not be possible to add a button (or something) to the excel spreadsheet and write some vbs code to export all sheets to a set of files matching each sheet.

Your user will then only have to click this button once and this should generate the required files. You may even be able to take it further by executing your program from within the vbs on click method such that it operates on the csv files generated - this would then almost fully automate your users experience.

[webJose]

I agree with SIK. This task could be completed more easily within MS Excel and Visual Basic for Applications.

Having said that, I would start here for COM: http://msdn.microsoft.com/en-us/library/windows/desktop/ms680573(v=vs.85).aspx . Actually, I think I did start there many years ago.

But I can tell you up front: Nothing like a good book about COM. Don Box is probably the absolute best but it requires that you are GOOD in C++. You have to be a C++ expert in order to go through the samples smoothly. The book's name is Essential COM: http://www.amazon.com/Essential-COM-Don-Box/dp/0201634465 .

[SIK]

I concur with webJose - com will give you the best level of controll if you follow this path, but if you don't want to tackle com right now and don't mind producing something more clunky, then can you try the following:

Create an excel vba macro triggered either by a button click or a keyboard shortcut.
The macro should save all the sheets of the current spreadsheet into respective csv files each refelcting the name of the sheet they come from.

Then create your c++ console application that will simply load the respective csv sheet files from a given folder and do your processing on them. Once processing is done, your program could even delete or archive these csv files.

I think you can call an exe from the vba macro interface within excel.
I went on an excel vba course about 10 years back, and if I remeber correctly, you can fairly easily accomplish the first step as well (saving all sheets of spreadsheet to their respective csv files).

The only drawback with this solution, is that you won't be able to automaticaly update your original spreadsheet from the processing of your c++ exe. This all depends on what your business process needs to do with the data from the spreadsheet in the first place.

As said, this is clunky, but may work if your users only need to hit a keyboard shortcut to get the spreadsheets processed.

[chtsolak]

Finally i used FormatExcel code and i succeded in reading and wrirting xls files.

(http://www.codeproject.com/KB/office/ExcelFormat.aspx)