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

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.

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

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.

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/

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.

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

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

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

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...!