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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017
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 2017