Serving the Quantitative Finance Community

 
User avatar
stas
Topic Author
Posts: 3
Joined: June 17th, 2009, 6:07 pm

XLW5 design/implementation question

December 1st, 2013, 1:55 pm

Hi All,I posted this on the XLW mailing list but wanted to ask here if people have any input on the below question Here is the post:I'm trying to implement a SQL query feature. What I mean by that is a function that will take a query string and return a result set. It has to be a function because it needs to refresh every time the sheet is refreshed with a f9 (I know ho to do this with a menu command).The problem is due to the dynamic number of rows and columns. As far as I see it there are two theoretical options though neither may work:1) If what was asked in the question linked below is possible that wouldbe great:http://sourceforge.net/mailarchive/foru ... usersSince no one replied I don't think its possible. Please correct me if I'm wrong.2) Use a Macro Sheet Function rougly speaking as described here:http://sourceforge.net/mailarchive/foru ... ersProblem is I cant get it to work. I have the following mock up code that if I can get to run correctly I can take care of the rest but it does not work for me. Nothing appears in cell A1: extern "C" { LPXLFOPER EXCEL_EXPORT xlTestFunc() { EXCEL_BEGIN; XlfOper topRight(xlw::XlfServices.Information.GetCellRefA1("!A1")); xlw::XlfServices.Cell.SetContents(topRight, "Hello Xlw User"); return XlfOper("OK"); EXCEL_END; } } namespace { XLRegistration::XLFunctionRegistrationHelper registerTestFunc( "xlTestFunc", "TestFunc", "First Test", "xlw Example", 0, 0, true, false, "", "", false, true); } This is an amalgamation of code from the hand written examples project and I believe I'm flagging the function as a Macro sheet function correctly. Could someone point out where the issue is?I have seen John mention in several places that Macros are problematic and error prone and should be avoided at all costs. Is there some other away to achieve this dynamic behavior?Thanks, Stas
 
User avatar
MattF
Posts: 6
Joined: March 14th, 2003, 7:15 pm

XLW5 design/implementation question

December 2nd, 2013, 9:41 am

Try xlw::XlfServices.Cell.SetContents(topRight, XlfOper("Hello Xlw User"));If that doesn't work then has topRight been initialised correctly as far as you can tell?
 
User avatar
DominicConnor
Posts: 41
Joined: July 14th, 2002, 3:00 am

XLW5 design/implementation question

December 2nd, 2013, 1:26 pm

What are you trying to do beyond the query support already in Excel ?
 
User avatar
stas
Topic Author
Posts: 3
Joined: June 17th, 2009, 6:07 pm

XLW5 design/implementation question

December 2nd, 2013, 2:26 pm

Do you mean using something like Microsoft Query? I played around with it but it is at best clunky and I don't think it can do what I need to do. I need to be able to take any valid sql statement and execute against the DB. No restrictions what so ever so stuff like temp tables, table variables, CTEs and so on. And I need to queries to except parameters from the sheet. And to refresh in a controlled manner (From a shift - f9 or just f9, not from clicking a refresh button). If there is something that can do all this please let me know.Thanks, Stan
Last edited by stas on December 1st, 2013, 11:00 pm, edited 1 time in total.
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

XLW5 design/implementation question

December 2nd, 2013, 6:09 pm

Don't take offence but why in heaven's name are you using C to access a database? This is like late 80's Oracle Pro-C. Meshuggah.C/C++ are the wrong technologies for DB access, big-time.For database access to Excel there are many better solutions (just think of ADO.NET) etc.If you want stay in XLL land one workaround is SQL DB <-> Managed C++ <-> Native C++ <-> XLL.Here is an example in Managed C++ and C# to show how to view Excel via OLEdb.// I wonder how many manhours get used up by the solution in C.
Last edited by Cuchulainn on December 1st, 2013, 11:00 pm, edited 1 time in total.
 
User avatar
Polter
Posts: 1
Joined: April 29th, 2008, 4:55 pm

XLW5 design/implementation question

December 2nd, 2013, 6:42 pm

Cuch: nah, C++ libs like SOCI are completely fine for DB access: http://soci.sourceforge.net/The "SQL DB <-> Managed C++ <-> Native C++ <-> XLL" solution looks waaay too complex/error-prone/fragile/maintenance-nightmare.Including .NET in the solution-chain for the sake of including .NET in the solution-chain -- sooo last decade! ;]
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

XLW5 design/implementation question

December 2nd, 2013, 7:26 pm

QuoteOriginally posted by: PolterCuch: nah, C++ libs like SOCI are completely fine for DB access: http://soci.sourceforge.net/The "SQL DB <-> Managed C++ <-> Native C++ <-> XLL" solution looks waaay too complex/error-prone/fragile/maintenance-nightmare.Including .NET in the solution-chain for the sake of including .NET in the solution-chain -- sooo last decade! ;]nah. Maybe I drew too many arrows..NET products work well with .NET products.SOCI is the answer, then what is the question? The business model might be too lite for many organisations, maybe.BTW have you ever created Excel addins in C++ and/or C#? C++ difficult.// Another issue: people are moving from C++ to C#.
Last edited by Cuchulainn on December 1st, 2013, 11:00 pm, edited 1 time in total.
 
User avatar
Govert
Posts: 0
Joined: January 28th, 2006, 10:02 am

XLW5 design/implementation question

December 2nd, 2013, 7:26 pm

My experience is from working on Excel-DNA, which is a .NET library for making Excel add-ins that also talks to Excel. Since Excel-DNA is also based on the Excel C API, the interaction with Excel is the same as from a native C/C++ add-in. The discussion here is also related to a blog post from a few years ago about making array functions that automatically resize to the right result size.Normally, an Excel function called from a worksheet calculation will not be able to alter the values on the sheet (Excel prevents this completely). So this is why SetContents cannot work from inside a function called from a worksheet formula. If your query might return different sizes of resultsets, and you want to return these from your function, then you are going beyond the standard Excel function behaviour. You need to run a macro to set values on the sheet.One approach to get the hybrid function/macro behaviour you ask about might be this:* When the function gets called, get the resultset, and then check the caller (using xlfCaller). If the caller is a reference of a size matching the resultset, just return the results as an array.* If the caller is not sized correctly, store the caller information, result size and optionally a cached version of the results in some internal structure. * Then enqueue some 'fix-up' macro to run after calculation is complete.* In the 'fix-up' macro, check the mismatched size, and change the formula to be an array formula of the right size. Setting the formula will force a recalculation.* The recalculation will now be from an array formula of the right size, so the fix-up macro need not run again.The main problem is to get the fix-up macro to run reliably. In the Excel-DNA example, I call the COM API function Application.Run(...) to run the fix-up macro. This needs to be done carefully (with good error handling) to be reliable. Recent Excel-DNA versions actually install a dedicated message window on the main thread to ensure that this kind of call happens on the main thread. But it's still tricky.There are some reasons to update the formula instead of just dumping the results to the sheet. Having a proper array formula leaves the dependency graph in a consistent state, so you can see which cells were populated by your query function, and hence how they might depend on other cells. Also, the query function will update only when it's inputs change (unless you make it volatile). And one can't accidentally delete, modify or leave behind some data that depends on the function when casually editing the sheet.Changing the formula to an array formula of the right size from that fix-up macro is also a bit tricky. Using the C API there are some issues with setting a formula - you need to select the right sheet, and take care of A1/R1C1 naming styles etc. But the Excel-DNA show how you might go about that. This aspect works one once one gets past all the Excel quirks.If you have to do this kind of thing, which clearly goes against the functionality Excel supports, it can be done. And I think the above is the only safe and sensible approach.But Excel certainly does not make it easy.All of that said - I know of at least one user who has made a general query function like this, and he swears by it. You can have the SQL string and parameters clearly visible inside your sheet, and as you change things that query is rerun and you can based further calculations on the results. He has a whole library of 'SQL' sheets with different examples of queries that he just modifies and combines as he needs to. Instead of the SQL being hidden in some dialog box, it's all on the sheet. So it's a style that works for some people, and not an entirely crazy idea :-)-GovertExcel-DNA - Free and easy .NET for Excel
Last edited by Govert on December 1st, 2013, 11:00 pm, edited 1 time in total.
 
User avatar
stas
Topic Author
Posts: 3
Joined: June 17th, 2009, 6:07 pm

XLW5 design/implementation question

December 3rd, 2013, 1:11 am

Cuchulainn and Polter, the DB component is completely tangential here. That is set up and works flawlessly. At issue is the dynamic re sizing of Excel output. Such a need can come up without any connection to a DB. Govert,Thank you very much for your detailed answer. I was kind of hopping that with the expanded access to the C API they opened up in XLW 5 it will be possible. On the mailing list people suggest that by registering the function as a macro sheet function it would work but I cant get it to.I will did around in Excel_DNA but I really want to avoid getting another technology involved here but maybe its not possible.Again, Thanks
 
User avatar
Govert
Posts: 0
Joined: January 28th, 2006, 10:02 am

XLW5 design/implementation question

December 3rd, 2013, 8:13 am

Registering a function as a macro- sheet function does indeed give it additional power - for example you are able to call the XlfGetXXX information functions.However, it still does not allow the function to call xlSet, which is the API to set cell values, or other functions that modify the sheet.The whole discussion is orthogonal to whether you use C/C++ with XLW to develop your add-in, or .NET and Excel-DNA. Both options give you the access to the full Excel API and are subject to the same constraints from Excel.-GovertExcel-DNA - Free and easy .NET for Excel
 
User avatar
Cuchulainn
Posts: 20253
Joined: July 16th, 2004, 7:38 am
Location: 20, 000

XLW5 design/implementation question

December 3rd, 2013, 8:24 am

QuoteOriginally posted by: stasCuchulainn and Polter, the DB component is completely tangential here. That is set up and works flawlessly. At issue is the dynamic re sizing of Excel output. Such a need can come up without any connection to a DB. I see. But you can _use_ Excel as a database with query possibility using Ole-DB. Is that not the same? QuoteI'm trying to implement a SQL query feature. What I mean by that is a function that will take a query string and return a result set. It has to be a function because it needs to refresh every time the sheet is refreshed with a f9 (I know ho to do this with a menu command).Sounds easy in ADO.NET with OLE-db as connection.
Last edited by Cuchulainn on December 2nd, 2013, 11:00 pm, edited 1 time in total.