Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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.
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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.
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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];
}
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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/
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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 :)
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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!
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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.
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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...!
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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.
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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/
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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.
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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
Re: excel-d v0.0.1 - D API to write functions callable from Excel
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.
