Hi, I was wondering if any of you know about commercial or open source container manipulation addins for excel? Let me explain. Many shops have developed excel addins with functionality to manipulate arrays and more general multi-dimensional data table like structures. Even more powerful would be a relational algebra addin. Obviously, in order to be useful, that kind of software needs to be delivered with an object handling mechanism.Thank You for your input. I am curious if this exists somewhere out there.nkpth

I seem to recall a discussion on the topic here or at NP a while back when I was searching for something else related to Excel.IIRC it mentioned using map objects within the add-in to link Excel info to objects within the add-in. I don't remember the details but I think it should be easy to find it again if you check the search here or at NP.

I am not sure I made myself clear.One thing you want to avoid doing in excel (maybe somewhat paradoxically) is to explode data into arrays and perform computation on cells, either columnwise or rowwise. That just doesn't work very well for large apps. The powerful aspects of excel many professionals like to leverage are the born-with GUI as well as the dependency graph intrinsic to the program. Therefore most players who make serious usage of excel write fairly elaborate addin libraries, which allow to perform all sorts of array manipulations. By array manipulations I mean manipulations of database type (relational manipulationson relations) or of applied math type (linear algebra for example provided you have exposed some vector/matrix/tensorcontainer library as addins).All of this necessarily relies on an object handling mechanism that will let you create, store and reference the underlyingobjects you want to manipulate fomr excel. BTW, I have always wondered why people are so excited about xlw. Without any object handling mechanism this piece of software is completely useless in my oppinion.So back to my question. Is there any commercial or open source software out there that will provide that sort of functionality?

ObjectHandler

yes I am aware of that one. I guess you are suggesting that I take that and start building my own addin library that will meet my needs. Do you know if object handler is an integrated solution? So, if you have it building then can you just go in and start writing addins?

I know exactly what you are talking about, unkpath. You can find some limited examples of this at http://test.tukhi.com/. After installing you can run (Alt-F8) the macro SIEVE.DEMO to compute the Sieve of Eratosthenes. Change B5 to 30,000 and hit the two pip die to compute primes up to 30,000. Right click in B14 and chose Adjust from the cell menu.To get documentation, choose Tukhi|Help from the Excel menu and see the Array section in the help file.I spent 3 years at Morgan Stanley writing A+ code and have implemented many of the array functions in that language. The latest evolution of A+ is kdb by kx systems. If you know C++ you can roll your own using the library for creating xll's at http://kalx.net/xll. See ARRAY.UNIQUE in example.cpp in the example project for the basic technique.

kimosabe, can you elaborate on what you say? I am not understanding exactly what you propose. I went to your site, but it is not clear to me exactly what tukhi does and how it is related to what I was asking. Also, thank you for pointing out http://kalx.net/xll/. Is that the toolkit you recommend for writing xl addins? I wonder ow easy that is to make work with something like ObjectHandler?I appreciate your help, Thanks.

Tukhi has array manipulation functions. Most array functions take either an array or a handle to an array. If the argument is a handle, the array function operates on the in-memory array. Here is the money shot from SIEVE.DEMO:=IF(TUKHI.COUNT()>0, ARRAY.MASK(Sieve, MOD(ARRAY.GET(Sieve), CurPrime) + (ARRAY.GET(Sieve) = CurPrime)))When running a simulation (TUKHI.COUNT()>0) mask the sieve using the remainders when dividing by the current prime, but don't throw out the current prime. Here Sieve is a handle to an in-memory array. After the simulation is done, use ARRAY.GET(Sieve) to retrieve the prime numbers. Note that when using an Excel function (like MOD) we need to call ARRAY.GET on the handle since Excel does not know about Tukhi in-memory arrays.Try things out and see for yourself. Let me know if you run into any problems with the xll library or Tukhi.BTW, it is not clear to me how you would use ObjectHandler in Excel. E.g., if you delete a cell in Excel how would ObjectHandler know?

QuoteBTW, it is not clear to me how you would use ObjectHandler in Excel. E.g., if you delete a cell in Excel how would ObjectHandler know?What about using OLEDb and Jet db to Excel as data source? And with a bit of mazzel it might be possible to use LINQ and disconnected recordsets.

Overkill. I just put a condom on the C++ pointer to an object and pass that around. When a 'constuctor' is called it deletes the old object and returns a new handle. All 'member' functions take the handle as an arg so they get called when appropriate. It is simple and respects the Zen of Excel.

QuoteOriginally posted by: kimosabeOverkill [...]One of the requirements was also the support for relational (SQL), yes?. How will this be accomodated with a C++ pointer.

You mean I'm supposed to read what other people write, Cuchulainn? That's too much like work. Maybe unkpath can give us some examples of what he is after. Excel already has a lot of built-in database functions.

NIH?Well, it was an idea, seeing that Microsoft has all the interfaces to Excel via Oledb; if it satisfies the requirements it is certainly worth looking at. However, personally I would use C# because C++ and databases are more painful. I googled and found a bunch; here is one simple example of updating Excel from Oledb http://www.java-samples.com/showtutoria ... lid=1051my 2 cents