March 21, 2017
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.
March 22, 2017
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!
March 22, 2017
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 :)


April 13, 2017
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
April 24, 2017
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



April 25, 2017
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/
April 25, 2017
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];
    }


April 25, 2017
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.
April 25, 2017
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

April 25, 2017
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.