www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.announce - excel-d v0.0.1 - D API to write functions callable from Excel

reply Atila Neves <atila.neves gmail.com> writes:
http://code.dlang.org/packages/excel-d

This dub package allows D code to be called from Excel. It uses 
compile-time reflection to register the user's code in an XLL (a 
DLL loaded by Excel) so no boilerplate is necessary. Not even 
`DllMain`! It works like this:

main.d:

import xlld;
mixin(wrapAll!(__MODULE__, "funcs"));

funcs.d:

import xlld;

 Register(ArgumentText("Array to add"),
           HelpTopic("Adds all cells in an array"),
           FunctionHelp("Adds all cells in an array"),
           ArgumentHelp(["The array to add"]))
double FuncAddEverything(double[][] args) nothrow  nogc {
     import std.algorithm: fold;
     import std.math: isNaN;

     double ret = 0;
     foreach(row; args)
         ret += row.fold!((a, b) => b.isNaN ? 0.0 : a + b)(0.0);
     return ret;
}

This code, once compiled to an XLL (see the example in the 
repository) and loaded in Excel, will permit a user to write 
`=FuncAddEverything(B1:D6)` and have the cell populated with the 
sum of all arguments in that range.

There's a lot going on behind the scenes, and that's the point. 
For instance, the function above takes a 2d array of doubles and 
returns a double, but those aren't Excel types. The wrapper code 
writes out an Excel-compatible type signature at compile-time, 
does all the conversions, calls the user's code then converts 
back to types Excel can understand.

The user functions have to be `nothrow`. This is guaranteed at 
compile-time and the user gets a warning message about the 
function not being considered. ` nogc` is optional but won't work 
if returning a string due to allocations. The code is compatible 
with std.experimental.allocator internally but there's no way to 
specify an allocator currently for the registration.

I can make the registration mixin easier to use but haven't 
gotten around to it yet. I thought it was better to put the code 
out there as is than wait.

This is another one of those "only in D" packages due to the 
metaprogramming, which is always nice.

Atila
Mar 20
next sibling parent reply Stefan Koch <uplink.coder googlemail.com> writes:
On Monday, 20 March 2017 at 20:09:58 UTC, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses 
 compile-time reflection to register the user's code in an XLL 
 (a DLL loaded by Excel) so no boilerplate is necessary. Not 
 even `DllMain`! It works like this:

 [...]
Ah Interesting to see how this turned out.
Mar 20
parent Laeeth Isharc <laeethnospam nospam.laeeth.com> writes:
On Monday, 20 March 2017 at 20:32:20 UTC, Stefan Koch wrote:
 On Monday, 20 March 2017 at 20:09:58 UTC, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It 
 uses compile-time reflection to register the user's code in an 
 XLL (a DLL loaded by Excel) so no boilerplate is necessary. 
 Not even `DllMain`! It works like this:

 [...]
Ah Interesting to see how this turned out.
Thanks for your help on this, Stefan. I'll publish Bloomberg API too when we have had time to tidy it up a bit more. Reddit link here: https://www.reddit.com/r/programming/comments/60koa6/dlang_wrapper_to_write_excel_functions_ctfe_magic/
Mar 20
prev sibling next sibling parent reply Steven Schveighoffer <schveiguy yahoo.com> writes:
On 3/20/17 4:09 PM, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses
 compile-time reflection to register the user's code in an XLL (a DLL
 loaded by Excel) so no boilerplate is necessary. Not even `DllMain`! It
 works like this:
If I wanted to replace our horrifying web queries from Excel with this, I'm assuming I could? That would be awesome. -Steve
Mar 20
next sibling parent reply Laeeth Isharc <laeethnospam nospam.laeeth.com> writes:
On Tuesday, 21 March 2017 at 00:25:46 UTC, Steven Schveighoffer 
wrote:
 On 3/20/17 4:09 PM, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses
 compile-time reflection to register the user's code in an XLL 
 (a DLL
 loaded by Excel) so no boilerplate is necessary. Not even 
 `DllMain`! It
 works like this:
If I wanted to replace our horrifying web queries from Excel with this, I'm assuming I could? That would be awesome. -Steve
Yes - you could, provided the function is nogc. Have done some work on using std.experimental.allocator (well I had a working version, but was trimmed a bit), and will publish a revised version when we get time, but it wouldn't be hard to add directly yourself. PRs welcome. If you allocate memory just make sure that you hook into the xlautofree callback (or whatever it's called - long time since I touched this) to free it. I try to keep the excel functions as simple as possible, because I don't want to crash a trader's spreadsheet. So we end up with a little local server running as a windows service that does the work. (We use nanomsg tcp/ip on localhost as struggled with IPC on windows, but you could talk to it however is convenient). Look forward to catching up at dconf in Berlin. Atila will be there too (and Stefan of course). I'm staying at the Beethoven too. Laeeth.
Mar 20
parent Atila Neves <atila.neves gmail.com> writes:
On Tuesday, 21 March 2017 at 01:12:45 UTC, Laeeth Isharc wrote:
 On Tuesday, 21 March 2017 at 00:25:46 UTC, Steven Schveighoffer 
 wrote:
 On 3/20/17 4:09 PM, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It 
 uses
 compile-time reflection to register the user's code in an XLL 
 (a DLL
 loaded by Excel) so no boilerplate is necessary. Not even 
 `DllMain`! It
 works like this:
If I wanted to replace our horrifying web queries from Excel with this, I'm assuming I could? That would be awesome. -Steve
Yes - you could, provided the function is nogc.
It doesn't _have_ to be nogc. The bindings are all allocator aware but the wrapper doesn't currently have a way to communicate that via the API. It's not a lot of work to add that though. i.e. The pumbling uses allocators for everything but the porcelain doesn't have a place where you can specify which one you want. Atila
Mar 21
prev sibling next sibling parent Laeeth Isharc <laeethnospam nospam.laeeth.com> writes:
On Tuesday, 21 March 2017 at 00:25:46 UTC, Steven Schveighoffer 
wrote:
 On 3/20/17 4:09 PM, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses
 compile-time reflection to register the user's code in an XLL 
 (a DLL
 loaded by Excel) so no boilerplate is necessary. Not even 
 `DllMain`! It
 works like this:
If I wanted to replace our horrifying web queries from Excel with this, I'm assuming I could? That would be awesome. -Steve
so calling curl c bindings should definitely work, or there might be some nogc curl type library on code.dlang.org.
Mar 20
prev sibling parent Atila Neves <atila.neves gmail.com> writes:
On Tuesday, 21 March 2017 at 00:25:46 UTC, Steven Schveighoffer 
wrote:
 On 3/20/17 4:09 PM, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses
 compile-time reflection to register the user's code in an XLL 
 (a DLL
 loaded by Excel) so no boilerplate is necessary. Not even 
 `DllMain`! It
 works like this:
If I wanted to replace our horrifying web queries from Excel with this, I'm assuming I could? That would be awesome. -Steve
Sure, you can do whatever you want as long as you can write it in D ;) The only thing to really watch out for is blocking the UI thread if your function takes too long. And spinning up new threads in the XLL itself didn't work out for me in the slightest. Atila
Mar 21
prev sibling next sibling parent reply Jacob Carlborg <doob me.com> writes:
On 2017-03-20 21:09, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses
 compile-time reflection to register the user's code in an XLL (a DLL
 loaded by Excel) so no boilerplate is necessary. Not even `DllMain`! It
 works like this:
It's cool that this is possible to do in D, but I feel sorry for anyone that has a reason :) -- /Jacob Carlborg
Mar 21
next sibling parent Laeeth Isharc <laeethnospam nospam.laeeth.com> writes:
On Tuesday, 21 March 2017 at 13:59:56 UTC, Jacob Carlborg wrote:
 On 2017-03-20 21:09, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses
 compile-time reflection to register the user's code in an XLL 
 (a DLL
 loaded by Excel) so no boilerplate is necessary. Not even 
 `DllMain`! It
 works like this:
It's cool that this is possible to do in D, but I feel sorry for anyone that has a reason :)
It's the current year, but for the time being for many practitioners in the investment world there is no better alternative to using a spreadsheet as one window onto server side processes used for certain things. Not the only window. I'd personally be thrilled to say goodbye both to spreadsheets and to Windows, but one also has to be somewhat pragmatic. And actually it's by far better to be able to write things for spreadsheets in D quickly versus the conceivable alternatives...!
Mar 21
prev sibling parent bachmeier <no spam.net> writes:
On Tuesday, 21 March 2017 at 13:59:56 UTC, Jacob Carlborg wrote:
 It's cool that this is possible to do in D, but I feel sorry 
 for anyone that has a reason :)
In my discussions with people doing real world data analysis, making decisions in meetings, by email, and such, spreadsheets are an important tool because of the interface. I've never talked to anyone that used Excel for any other reason.
Mar 21
prev sibling next sibling parent reply Saurabh Das <saurabh.das gmail.com> writes:
On Monday, 20 March 2017 at 20:09:58 UTC, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses 
 compile-time reflection to register the user's code in an XLL 
 (a DLL loaded by Excel) so no boilerplate is necessary. Not 
 even `DllMain`! It works like this:

 [...]
This is too good. We already use a cobbled-together Excel runner type thing. It will be so much better to use excel-d. Thanks so much Atila!
Mar 21
parent Laeeth Isharc <laeethnospam nospam.laeeth.com> writes:
On Wednesday, 22 March 2017 at 02:30:50 UTC, Saurabh Das wrote:
 On Monday, 20 March 2017 at 20:09:58 UTC, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It 
 uses compile-time reflection to register the user's code in an 
 XLL (a DLL loaded by Excel) so no boilerplate is necessary. 
 Not even `DllMain`! It works like this:

 [...]
This is too good. We already use a cobbled-together Excel runner type thing. It will be so much better to use excel-d. Thanks so much Atila!
Glad it's helpful. Pull requests welcomed :)
Mar 22
prev sibling parent Atila Neves <atila.neves gmail.com> writes:
On Monday, 20 March 2017 at 20:09:58 UTC, Atila Neves wrote:
 http://code.dlang.org/packages/excel-d

 This dub package allows D code to be called from Excel. It uses 
 compile-time reflection to register the user's code in an XLL 
 (a DLL loaded by Excel) so no boilerplate is necessary. Not 
 even `DllMain`! It works like this:

 [...]
Now tested and working with 64-bit Excel as well. Atila
Apr 13