www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - best way to interface D code to Excel

reply "Laeeth Isharc" <nospamlaeeth laeethnospam.laeeth.com> writes:
Hi.

Any thoughts on the best way to write D functions that I can call 
from Excel?  I am completely unfamiliar with Windows programming 
and COM (last time I wrote this kind of thing was in the mid-90s 
I think using xloper and C).

The easiest way for now seems to be via pyxll and pyd.  Wrap the 
D function using pyd and then call it from an annotated pyxll 
python function.  That's probably good enough for a start, but it 
requires python to be installed on each user machine which I 
would rather not have as the end game - just a single xll would 
be perfect.

I know D has support for COM - not sure of its status.  And there 
was a Microsoft chap who posted here a couple of years back - 
wonderful templated code that made it easy to write this kind of 
thing.  Unfortunately he wasn't able to share it publicly.

So I would like to write:
1. worksheet function that will function picker and have help 
text in the picker.  In general will take some combination of 
strings, doubles and arrays of doubles as arguments and return 
either string, double, or range/matrix of doubles.
2. vba function with similar kinds of arguments and return values.

I know it's easy to do this with double** and the like - at least 
on the VBA side.  I got a bit stuck navigating the headers when 
it comes to Excel strings.

If anyone has some source they could point me to, and some 
reading material then I would very much appreciate it.  This 
might help facilitate adoption of D within a large financial 
institution, but it is early days yet.  And I would guess this is 
a common sort of use for people operating in a financial 
environment, since people are still attached to spreadsheets.

Thanks.


Laeeth.
Jun 17 2015
next sibling parent "Kagamin" <spam here.lot> writes:
http://www.amazon.com/Inside-Com-Microsoft-Programming-Series/dp/1572313498
Jun 18 2015
prev sibling next sibling parent "John Colvin" <john.loughran.colvin gmail.com> writes:
On Wednesday, 17 June 2015 at 18:35:36 UTC, Laeeth Isharc wrote:

 Hi.

 Any thoughts on the best way to write D functions that I can 
 call from Excel?  I am completely unfamiliar with Windows 
 programming and COM (last time I wrote this kind of thing was 
 in the mid-90s I think using xloper and C).

 The easiest way for now seems to be via pyxll and pyd.  Wrap 
 the D function using pyd and then call it from an annotated 
 pyxll python function.  That's probably good enough for a 
 start, but it requires python to be installed on each user 
 machine which I would rather not have as the end game - just a 
 single xll would be perfect.

 I know D has support for COM - not sure of its status.  And 
 there was a Microsoft chap who posted here a couple of years 
 back - wonderful templated code that made it easy to write this 
 kind of thing.  Unfortunately he wasn't able to share it 
 publicly.

 So I would like to write:
 1. worksheet function that will function picker and have help 
 text in the picker.  In general will take some combination of 
 strings, doubles and arrays of doubles as arguments and return 
 either string, double, or range/matrix of doubles.
 2. vba function with similar kinds of arguments and return 
 values.

 I know it's easy to do this with double** and the like - at 
 least on the VBA side.  I got a bit stuck navigating the 
 headers when it comes to Excel strings.

 If anyone has some source they could point me to, and some 
 reading material then I would very much appreciate it.  This 
 might help facilitate adoption of D within a large financial 
 institution, but it is early days yet.  And I would guess this 
 is a common sort of use for people operating in a financial 
 environment, since people are still attached to spreadsheets.

 Thanks.


 Laeeth.


You want to write extensions to Excel, in D? It's not that 
pretty, but there's always the Excel C-API 
(https://msdn.microsoft.com/en-us/library/office/bb687829.aspx)

I don't know whether D's DLL support is up to the task though, 
but that's beyond my knowledge.
Jun 18 2015
prev sibling next sibling parent reply "Arjan" <arjan ask.me.to> writes:
On Wednesday, 17 June 2015 at 18:35:36 UTC, Laeeth Isharc wrote:

 Hi.

 Any thoughts on the best way to write D functions that I can 
 call from Excel?  I am completely unfamiliar with Windows 
 programming and COM (last time I wrote this kind of thing was 
 in the mid-90s I think using xloper and C).

 The easiest way for now seems to be via pyxll and pyd.  Wrap 
 the D function using pyd and then call it from an annotated 
 pyxll python function.  That's probably good enough for a 
 start, but it requires python to be installed on each user 
 machine which I would rather not have as the end game - just a 
 single xll would be perfect.

 I know D has support for COM - not sure of its status.  And 
 there was a Microsoft chap who posted here a couple of years 
 back - wonderful templated code that made it easy to write this 
 kind of thing.  Unfortunately he wasn't able to share it 
 publicly.

 So I would like to write:
 1. worksheet function that will function picker and have help 
 text in the picker.  In general will take some combination of 
 strings, doubles and arrays of doubles as arguments and return 
 either string, double, or range/matrix of doubles.
 2. vba function with similar kinds of arguments and return 
 values.

 I know it's easy to do this with double** and the like - at 
 least on the VBA side.  I got a bit stuck navigating the 
 headers when it comes to Excel strings.

 If anyone has some source they could point me to, and some 
 reading material then I would very much appreciate it.  This 
 might help facilitate adoption of D within a large financial 
 institution, but it is early days yet.  And I would guess this 
 is a common sort of use for people operating in a financial 
 environment, since people are still attached to spreadsheets.

 Thanks.


 Laeeth.


You actually want to create ActiveX/COM components in D?
See the 'COM in plain C' articles by Jeff Glatt which demonstrate 
how to do it using C. This might give you the information needed 
to do it using D.
http://www.codeproject.com/Articles/Jeff-Glatt#articles.

An idea to accelerate a little might be to use Visual Studio with 
C++ to create ActiveX/COM interfaces, wrappers, etc of the 
components and do the actual implementation of the interfaces in 
D.

Books (old): "Inside COM" and "Essential COM" might be handy to 
get hold of.

HTH.
Arjan
Jun 18 2015
next sibling parent "Laeeth Isharc" <nospamlaeeth laeethnospam.laeeth.com> writes:
Thanks to Kagamin, John, and Arjan.  Decided I need to upgrade my 
machine to run a more usable Windows VM.

Looks like Adam Ruppe has a working COM example in his github 
repo, so I will start with that.  And I have ordered the book and 
will read the COM in C stuff too.

Appreciate the help.


Laeeth.
Jun 18 2015
prev sibling parent "Kagamin" <spam here.lot> writes:
On Thursday, 18 June 2015 at 11:36:50 UTC, Arjan wrote:

 See the 'COM in plain C' articles by Jeff Glatt which 
 demonstrate how to do it using C. This might give you the 
 information needed to do it using D.
 http://www.codeproject.com/Articles/Jeff-Glatt#articles.


It's a little misleading though. It starts with complaining that 
existing examples hide details and then uses all sorts of macros 
which have the same issue.

HRESULT PASCAL DllGetClassObject


Hehe, this one doesn't use pascal convention of course :)
Jun 19 2015
prev sibling parent reply "Jesse Phillips" <Jesse.K.Phillips+D gmail.com> writes:
On Wednesday, 17 June 2015 at 18:35:36 UTC, Laeeth Isharc wrote:

 Hi.

 I know D has support for COM - not sure of its status.  And 
 there was a Microsoft chap who posted here a couple of years 
 back - wonderful templated code that made it easy to write this 
 kind of thing.  Unfortunately he wasn't able to share it 
 publicly.

 Laeeth.


I haven't ever done any real work with it, and certainly nothing 
with Excel. But Juno provides similarities for COM to what the 
Microsoft guy demonstrated.

I made some changes so it would compile in dmd 2.070, didn't test 
though so it is still it's own branch.

https://github.com/JesseKPhillips/Juno-Windows-Class-Library/tree/dmd6.070
Jun 19 2015
parent "Laeeth Isharc" <nospamlaeeth nospam.laeeth.com> writes:
On Friday, 19 June 2015 at 21:06:31 UTC, Jesse Phillips wrote:

 On Wednesday, 17 June 2015 at 18:35:36 UTC, Laeeth Isharc wrote:

 Hi.

 I know D has support for COM - not sure of its status.  And 
 there was a Microsoft chap who posted here a couple of years 
 back - wonderful templated code that made it easy to write 
 this kind of thing.  Unfortunately he wasn't able to share it 
 publicly.

 Laeeth.


 I haven't ever done any real work with it, and certainly 
 nothing with Excel. But Juno provides similarities for COM to 
 what the Microsoft guy demonstrated.

 I made some changes so it would compile in dmd 2.070, didn't 
 test though so it is still it's own branch.

 https://github.com/JesseKPhillips/Juno-Windows-Class-Library/tree/dmd6.070


Thanks Jesse - I did look at this, but I think I couldn't make it 
compile when I tried (or at least some of the unit tests fail, 
from what I recall).  But I appreciate your updating and will 
take a look now.

Somebody should write an article for the wiki on the various 
options needed for COM.  I know it's less in fashion, but it's 
still very useful.  I don't yet have the expertise, and I am only 
looking into this reluctantly for pragmatic reasons.  There is an 
empty placeholder linked from early in the Wiki here:

http://wiki.dlang.org/COM_Programming

Does it make sense to keep a register of all unfilled out such 
pages, so people who are looking for something to do can 
contribute?


Laeeth.
Jun 20 2015