Re: Frustration with Luacom and Excel

On Thu, Apr 30, 2015 at 8:51 AM, Geoff Smith <spammealot1@live.co.uk> wrote:

I have been tinkering some more with Lua and Excel so I thought I would post my tests and conclusions in case anyone finds them interesting or useful in future. I will paste some benchmarking code I have written at the end of this message, if anyone has a couple of minutes free to run my snippet I would be interested to see what numbers you get.

...

Conclusions
1) If you want any half decent performance out of Luacom and Excel, then you must use the range command to reduce the number of Lua to Excel COM calls to an absolute minimum.
In my use case, the cells are dotted all over the place and cant be pasted with a contiguous range. A workaround to this should be easy though, I can paste all the cells I need to update as a contiguous range 100 cells to the right so that they aren't really noticeable. Then at initialisation time for the Spreadsheet I can put in a bunch of cross links from the pasted area to the cells where the data really needs to appear.
I haven't tried this yet, but it should be a good improvement over my current rubbish performance.

That is indeed the case. The cost of an out-of-process call in COM is way bigger that an in-process call. You can try a different thing. LuaCOM can act as a COM component, registering a COM interface so that other processes can consume it. So you can split your code in two. One part, opens Excel, and adds a macro to it. That macro (VBA code) will create an object of this new interface and call it. So you will have your Lua code running within the Excel process. All your calls will now be in-process and you get a huge speedup.

However, as you can see, the setup process is quite convoluted.

2) Using Select as per test4 doesn't give great results anyway, and it has got a secondary problem that I noticed in my real code. If someone clicks on a random cell in the spreadsheet while your Lua script is running, then the selection is moved and your code fails/crashes. I could probably figure out a way of preventing user selection during update, but it is better just to not use the test4 method at all.

Code

I wish the Lua Community had a decent message board where you could post code snippets that don't get messed up. Sorry this will probably get its formatting screwed up.

You can post this samples in the wiki: http://lua-users.org/wiki/