A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
I have a MFC C++ program that I need to add reading and writing of name ranges from excel spreaddheet. I don't think that I can used ODBC as there could be many name ranges of differencd types on one sheet. The other problem is that I do not know what version of Excel will be on the system that the application will run on. I have been looking at COM Automation but there seem to be difference for each of the version of Excel. Is there some way I can do this without having to write code for 4 or 5 version of Excel interface?
Simply write your code to work with office 97 (excel 8???).
Then, after you get everything working, change your code to late binding, and you will have not trouble working with 97 to 2000, to 2002, to 2003…
I done this with outlook, and you should have the same success with Excel if you late bind. So, the critical step here is to late bind....
As a alternative, consider not even using Excel, and thus you would not NOT have Excel on the machine.
Consdier using the JET engine, as it is shipped with every copy of windows (xp)….
Albert D. Kallal
Edmonton, Alberta Canada
Albert D. Kallal
Saturday, April 08, 2006
From what I understand you are wanting to import and export data to Excel named ranges. For this OLE Automation is not required (although it does have its benefits). Both DAO and ADO provide mechanisms to retrieve (and write) data from closed Excel workbooks.
It’s fast (particularly when dealing with LARGE spreadsheets), it’s clean and you can work with the data as an DAO/ADO record set.
A couple of caveats I’ve found:
* When writing to a named range, you cannot expand the area defined by the Range Name when the workbook is closed. You’ll need to open the workbook (Automation) if you need to resize a named range.
* DAO/ADO ‘guesses’ the data type for each column by looking at the entries in the first 8 (or so) rows. It doesn’t always guess correctly.
I've used this technique to import data in batch format from dozens of spreadsheets daily into SQL Server & Access. Here’s a link which may serve as a starting point as to whether this is a suitable solution for you:
All the best
Marcus from Melbourne
Sunday, April 09, 2006
I've got another suggestion, made from a COM/OLE/automation-impaired point of view. If you're targeting reasonably recent versions of Excel you could try manipulating the XML format it saves out. This appears to contain all the spreadsheet info (unlike, say, CSV).
Getting it out could be a problem for less technical users, since they'd have to save in XML explicitly. (That said, automating this would presumably not be too time-consuming?) But you can generate your XML file with an XLS extension, double click it, and Excel will load it properly -- so that side of things is relatively simple.
I've had some success with this approach, for batch-processing localization spreadsheets. You don't need Excel either, just an XML library! But obviously it depends on exactly what you're doing, and how the workflow is supposed to go, so it may not be appropriate for your specific situation.
Sunday, April 09, 2006
I tried to find a library a while back that could read and write Excel binary format. I failed. Please let me kinow if you find one. Otherwise I will have to use the abomination that is OLE/ActiveX - which is ugly from C++/Qt (shudder) and will only work if they have Excel installed.
Wednesday, April 12, 2006
If you're working in Delphi or .NET, you can use XLSReadWriteII ( http://www.axolot.com/components/xlsrwii20.htm ).
Thursday, April 13, 2006
I using Visual C++ 6, so unfortunately that library is no use to me. Surely their must be a VC++ equivalent somewhere?
Monday, April 17, 2006
I have come across this kind of problem also.
2 years ago I had develop this app to export spreadsheets with charts and formatting ( since our company board would reject dull looking tables... )
I had to take this financial-accounting-voodoo-crap from a DB, XML, html, older XLS'... you wouldn't believe the "fun" i encountered with data validation and named ranges :)
both ActiveX and OLE Automation can be a real pain, believe me, i tried to find a solution for 4 months. eventually I realised I had no time, so ... we bought a component. there are a lot of great libraries out there...
we bought something called activeXLS ( www.activexls.com if i can recall) they made us a pretty good cut for multiple licenses. now, we used c#.net, but i am sure i saw support for c++...you'll have to check it out
Thursday, April 20, 2006
ActiveXLS does work with C++, as long as the .Net framework is installed on the machine ActiveXLS is running on.
In my experience most machines already have it installed either by the windows updater or by another program, but it is easy to either put in an installation program or add from the windows updater.
A quick google search returned this link to the redistributable portion of the .net framework.
Wednesday, April 26, 2006
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz