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 next 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
prev sibling parent reply 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:

 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
Now with more ` nogc`. Before, this worked fine: double func(double d) nogc nothrow { return d * 2; } The function is ` nogc`, the wrapper function (i.e. the function that Excel actually calls) is also ` nogc` via the magic of compile-time reflection. So far, so good. But what if you want to return a string or an array back to Excel. Oh, oh... Enter the ` Dispose` UDA: // Dispose is used to tell the framework how to free memory that is dynamically // allocated by the D function. After returning, the value is converted to an // Excel type sand the D value is freed using the lambda defined here. Dispose!((ret) { import std.experimental.allocator.mallocator: Mallocator; import std.experimental.allocator: dispose; Mallocator.instance.dispose(ret); }) double[] FuncReturnArrayNoGc(double[] numbers) nogc safe nothrow { import std.experimental.allocator.mallocator: Mallocator; import std.experimental.allocator: makeArray; import std.algorithm: map; try { // Allocate memory here in order to return an array of doubles. // The memory will be freed after the call by calling the // function in ` Dispose` above return Mallocator.instance.makeArray(numbers.map!(a => a * 2)); } catch(Exception _) { return []; } } And Bob's your uncle. Atila
Apr 24
next sibling parent reply Laeeth Isharc <laeethnospam nospam.laeeth.com> writes:
On Monday, 24 April 2017 at 21:59:34 UTC, Atila Neves wrote:
 Now with more ` nogc`. Before, this worked fine:

 double func(double d)  nogc nothrow { return d * 2; }

 The function is ` nogc`, the wrapper function (i.e. the 
 function that Excel actually calls) is also ` nogc` via the 
 magic of compile-time reflection. So far, so good. But what if 
 you want to return a string or an array back to Excel. Oh, oh...

 Enter the ` Dispose` UDA:

 And Bob's your uncle.

 Atila
Very nice. On reddit here since it's a pretty nice example of how you don't need to use dark magic to write code in D without depending on the GC: https://www.reddit.com/r/programming/comments/67dogy/writing_excel_addins_in_d_without_using_the/
Apr 24
parent Laeeth Isharc <laeethnospam nospam.laeeth.com> writes:
C++ example for XLW:

  LPXLFOPER EXCEL_EXPORT xlStats(LPXLFOPER inTargetRange) {
         EXCEL_BEGIN;
         XlfOper xlTargetRange(inTargetRange);

         // Temporary variables.
         double averageTmp = 0.0;
         double varianceTmp = 0.0;

         // Iterate over the cells in the incoming matrix.
         for (RW i = 0; i < xlTargetRange.rows(); ++i)
         {
             for (RW j = 0; j < xlTargetRange.columns(); ++j)
             {
                 // sums the values.
                 double value(xlTargetRange(i,j).AsDouble());
                 averageTmp += value;
                 // sums the squared values.
                 varianceTmp += value * value;
             }
         }
         size_t popSize = xlTargetRange.rows() * 
xlTargetRange.columns();

         // avoid divide by zero
         if(popSize == 0)
         {
             THROW_XLW("Can't calculate stats on empty range");
         }

         // Initialization of the results Array oper.
         XlfOper result(1, 2);
         // compute average.
         double average = averageTmp / popSize;
         result(0, 0) = average;
         // compute variance
         result(0, 1) = varianceTmp / popSize - average * average;
         return result;
         EXCEL_END;
     }

D example (didn't get time to test, but something like this) is a 
little bit more concise! :
     import std.algorithm:map,sum;
     import std.range:front;

      Register(ArgumentText("input range to calculate statistics 
for"),
         HelpTopic("excel-d"),
         FunctionHelp("calculates mean and variance for input 
array"),
         ArgumentHelp(["input range to calculate statistics for"]))
     auto xlStats(double[][] inTargetRange)
     {
         auto numCells = (inTargetRange.length > 0) ?
                                      inTargetRange.length * 
inTargetRange.front.length : 0;
         auto means = inTargetRange.map!(row => row.sum).sum / 
numCells;
         auto sumSquares = inTargetRange.map!( row => 
row.map!(cell => cell*cell).sum).sum;
         return [means, sumSquares / numCells - means];
     }
Apr 24
prev sibling parent reply jmh530 <john.michael.hall gmail.com> writes:
On Monday, 24 April 2017 at 21:59:34 UTC, Atila Neves wrote:
 Enter the ` Dispose` UDA:
I found this really interesting. Am I understanding the process correctly: apply map to numbers, allocate and return a new array in D, copy it to Excel, call dispose to free the D memory. So if you instead used something like scope(exit) Mallocator.instance.dispose(dArg); then Excel wouldn't be able to use it because it would be freed by D already? It seems like your Dispose UDA could have many uses outside of just the excel-d package. If I understand correctly, it's similar to C++ smart pointers, but not exactly the same. I'm still not sure I totally grok it though.
Apr 25
next sibling parent reply =?UTF-8?Q?Ali_=c3=87ehreli?= <acehreli yahoo.com> writes:
On 04/25/2017 01:20 PM, jmh530 wrote:
 On Monday, 24 April 2017 at 21:59:34 UTC, Atila Neves wrote:
 Enter the ` Dispose` UDA:
I found this really interesting. Am I understanding the process correctly: apply map to numbers, allocate and return a new array in D, copy it to Excel, call dispose to free the D memory.
Yes.
 So if you instead used something like
 scope(exit) Mallocator.instance.dispose(dArg);
 then Excel wouldn't be able to use it because it would be freed by D
 already?
Correct.
 It seems like your  Dispose UDA could have many uses outside of just the
 excel-d package. If I understand correctly, it's similar to C++ smart
 pointers, but not exactly the same. I'm still not sure I totally grok it
 though.
Just by guessing, what we don't see here is that there is a wrapping layer that does the copying. Disposal logic is called by that layer. So, your scope(exit) takes place at a higher layer. Here is made-up-pseudo-code by me. :) TypeForExcel wrapper_actually_called_by_excel(/* ... */) { /* ... */ double[] ret_from_your_function = FuncReturnArrayNoGc(/* ... */); scope (exit) disposal_function(ret_from_your_function); TypeForExcel arg_to_excel = copy_for_excel(ret_from_your_function); return arg_to_excel; } Ali
Apr 25
next sibling parent jmh530 <john.michael.hall gmail.com> writes:
On Tuesday, 25 April 2017 at 22:21:33 UTC, Ali Çehreli wrote:
 Just by guessing, what we don't see here is that there is a 
 wrapping layer that does the copying. Disposal logic is called 
 by that layer. So, your scope(exit) takes place at a higher 
 layer. Here is made-up-pseudo-code by me. :)

 TypeForExcel wrapper_actually_called_by_excel(/* ... */) {
     /* ... */

     double[] ret_from_your_function = FuncReturnArrayNoGc(/* 
 ... */);
     scope (exit) disposal_function(ret_from_your_function);

     TypeForExcel arg_to_excel = 
 copy_for_excel(ret_from_your_function);
     return arg_to_excel;
 }
That makes it more obvious. So it still is really like a scope(exit), just some other function is managing when that occurs.
Apr 25
prev sibling parent Atila Neves <atila.neves gmail.com> writes:
On Tuesday, 25 April 2017 at 22:21:33 UTC, Ali Çehreli wrote:
 On 04/25/2017 01:20 PM, jmh530 wrote:
 [...]
numbers, allocate
 [...]
to free the
 [...]
Yes.
 [...]
freed by D
 [...]
Correct.
 [...]
of just the
 [...]
C++ smart
 [...]
totally grok it
 [...]
Just by guessing, what we don't see here is that there is a wrapping layer that does the copying. Disposal logic is called by that layer. So, your scope(exit) takes place at a higher layer. Here is made-up-pseudo-code by me. :) TypeForExcel wrapper_actually_called_by_excel(/* ... */) { /* ... */ double[] ret_from_your_function = FuncReturnArrayNoGc(/* ... */); scope (exit) disposal_function(ret_from_your_function); TypeForExcel arg_to_excel = copy_for_excel(ret_from_your_function); return arg_to_excel; } Ali
Yep, that's pretty much what happens, except that the `scope(exit)` is inside a `static if` that checks for the presence of ` Dispose`. Atila
Apr 26
prev sibling parent Atila Neves <atila.neves gmail.com> writes:
On Tuesday, 25 April 2017 at 20:20:08 UTC, jmh530 wrote:
 On Monday, 24 April 2017 at 21:59:34 UTC, Atila Neves wrote:
 Enter the ` Dispose` UDA:
I found this really interesting. Am I understanding the process correctly: apply map to numbers, allocate and return a new array in D, copy it to Excel, call dispose to free the D memory. So if you instead used something like scope(exit) Mallocator.instance.dispose(dArg); then Excel wouldn't be able to use it because it would be freed by D already? It seems like your Dispose UDA could have many uses outside of just the excel-d package. If I understand correctly, it's similar to C++ smart pointers, but not exactly the same. I'm still not sure I totally grok it though.
I considered using the smart pointers from my automem package but didn't want to add a dependency. I'm thinking now I could make it optional and if it detects that your function returns Unique!T it can convert the wrapped T to an Excel type. Atila
Apr 27