RE: Frustration with Luacom and Excel

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.

I only needed to update 50 or so cells in my spreadsheet every 1 second, so I wasn't too worried about it running fast enough. That was until I tried it. The performance sucked really badly, so I wrote a simple benchmark script to try and understand the problem better and see what tricks I can do to improve the execution speed.

There are 5 benchmark tests, all doing the same thing, updating a 10 x 10 array of cells, repeated 15 times.

Test1
This updates the 100 cells but with Excel not visible

Test2
This updates the 100 cells but with Excel visible

Test3
This updates the 100 cells but with Excel visible but with screen updating and calculation updating both turned off while the cells are changing.

Test4
This updates the 100 cells but with Excel visible but with screen updating and calculation updating both turned off while the cells are changing.
This test selects the cell first and then writes to it using excel.Selection.Value2

Test5
This updates the 100 cells but with Excel visible but with screen updating and calculation updating both turned off while the cells are changing.
This test first creates a Lua Table of 100 elements and then writes the table to excel with a single sheet:Range(RangeRef).Value2 = testTab

Test Results
Test 1:              Plot Cells while Excel is not visible = 5.133 Secs
Test 2:                  Plot Cells while Excel is visible = 12.19 Secs
Test 3: Plot Cells while Excel is visible but not updating = 9.925 Secs
Test 4:   Plot Cells visible, not updating, using Select() = 9.635 Secs
Test 5:      Plot Cells visible, not updating, using Range = 0.122 Secs

100 fold difference, between best and worst !!

Testing Method
If you do try this bit of code the testing method is important, it makes a big difference to the numbers. Originally I thought I was seeing a random variation of test2 between 12 and 6 seconds. I then realised what was causing the difference. To run the test make sure that when the code is running, Excel is in the foreground Window. Make sure the mouse is over the Excel window (you should see the 12 Secs or so time).
Then run the same test again where Excel is the foreground window but move the mouse outside of the Excel Window (you should see the 6 seconds result). That was a surprising observation.

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.

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.

require "luacom"

local LOOPS = 15
excel = luacom.CreateObject("Excel.Application")
local book = excel.Workbooks:Add()
local sheet = book.Worksheets(1)

function clearAll ()
  sheet.Cells:Select()
  excel.Selection:Delete(-4162)  -- xlUp
  sheet:Range("A1"):Select()
end

print("Starting Excel BenchMarks\n")
local testNum = 1

excel.DisplayAlerts = false

-- ******************************* Test 1 **************************************
excel.Visible = false
local startTime = os.clock()
for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do
   sheet.Cells(row, col).Value2 = string.format("%d:%d,%d", i, row, col)
   sheet.Cells(row, col).HorizontalAlignment = -4108 -- align Center
   sheet.Cells(row, col).Font.ColorIndex = 11
    end
  end
end
excel.Visible = true
local endTime = os.clock()
print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells while Excel is not visible", endTime - startTime))
testNum = testNum + 1
-- ******************************* Test 2 **************************************
clearAll() -- Clear Previous Test Cells
local startTime = os.clock()
excel.Visible = true
for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do
   sheet.Cells(row, col).Value2 = string.format("%d:%d,%d", i, row, col)
   sheet.Cells(row, col).HorizontalAlignment = -4108 -- align Center
   sheet.Cells(row, col).Font.ColorIndex = 11
    end
  end
end
local endTime = os.clock()

print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells while Excel is visible", endTime - startTime))
testNum = testNum + 1
-- ******************************* Test 3 **************************************
clearAll() -- Clear Previous Test Cells
local startTime = os.clock()
excel.Application.ScreenUpdating = false
excel.ActiveSheet.EnableCalculation = false
for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do
   sheet.Cells(row, col).Value2 = string.format("%d:%d,%d", i, row, col)
   sheet.Cells(row, col).HorizontalAlignment = -4108 -- align Center
   sheet.Cells(row, col).Font.ColorIndex = 11
    end
  end
end
excel.Application.ScreenUpdating = true
excel.ActiveSheet.EnableCalculation = true
local endTime = os.clock()
print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells while Excel is visible but not updating", endTime - startTime))
testNum = testNum + 1

-- ******************************* Test 4 **************************************
clearAll() -- Clear Previous Test Cells
local startTime = os.clock()
excel.Application.ScreenUpdating = false
excel.ActiveSheet.EnableCalculation = false
for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do
   sheet.Cells(row, col):Select()
   excel.Selection.Value2 = string.format("%d:%d,%d", i, row, col)
   excel.Selection.HorizontalAlignment = -4108 -- align Center
   excel.Selection.Font.ColorIndex = 11
    end
  end
end
excel.Application.ScreenUpdating = true
excel.ActiveSheet.EnableCalculation = true
local endTime = os.clock()
print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells visible, not updating, using Select()", endTime - startTime))
testNum = testNum + 1

-- ******************************* Test 5 **************************************
clearAll() -- Clear Previous Test Cells
local startTime = os.clock()
excel.Application.ScreenUpdating = false
excel.ActiveSheet.EnableCalculation = false
RangeRef = "A1:J10"
for i=1, LOOPS do
  local testTab = {}
  -- make a Lua table that we can pass into Excel as a 10 x 10 cell range
  for row=1, 10 do
   local innerTab = {}
   for col=1, 10 do
    innerTab[#innerTab+1] = string.format("%d:%d,%d", i, row, col)
   end
   testTab[#testTab+1] = innerTab
  end
  sheet:Range(RangeRef).Value2 = testTab
  sheet:Range(RangeRef).HorizontalAlignment = -4108 -- align Center
  sheet:Range(RangeRef).Font.ColorIndex = 11
end
excel.Application.ScreenUpdating = true
excel.ActiveSheet.EnableCalculation = true
local endTime = os.clock()
print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells visible, not updating, using Range", endTime - startTime))
testNum = testNum + 1

From: spammealot1@live.co.uk
To: lua-l@lists.lua.org
Date: Wed, 29 Apr 2015 14:07:51 +0100
Subject: RE: Frustration with Luacom and Excel

Hi Thomas

Thanks for your message, our messages crossed in the post !

For the Excel hyperlink problem, our solutions agreed. Yay :)

Thanks for the graphing snippets, I am sure they will be very useful when I get around to tackling that one

Geoff

From: spammealot1@live.co.uk
To: lua-l@lists.lua.org
Date: Wed, 29 Apr 2015 13:59:59 +0100
Subject: RE: Frustration with Luacom and Excel

Hi

Thanks for the suggestion as you predicted it didn't work. I was dumb enough to sit here for several hours just trying all the permutations of tables with named parameters and strings.

Oleview wouldn't help me, as I know what the parameters are for the Hyperlinks:Add() function are, I just don't know how Luacom is expecting them to be passed.

Eventually I stumbled on the correct way of passing the arguments. It doesn't expect namedParams, or a table at all, it just has a predefined order of arguments

It is

excel.Selection.Hyperlinks:Add(cellLocation, urlRootStr, urlFragmentStr, ToolTipStr, HyperLinkTextStr)

where urlRootStr, urlFragmentStr are appended together with a # for the final url string

Full working example

require "luacom"

excel = luacom.CreateObject("Excel.Application")
local book = excel.Workbooks:Add()
local sheet = book.Worksheets(1)

excel.Visible = true

sheet.Cells(1, 1):Select()
excel.Selection.Hyperlinks:Add(excel.Selection, "http://www.bbc.co.uk/", "election-2015-32506490", "ToolTip", "BBC")

This should have been a 30 second exercise for me to look up the docs on the web, not the several hours it actually took.

IMHO this lack of good library documentation is a huge problem for Lua, as this is not an isolated case.

There are a few notable exceptions like Steve Donovan, Leaf Corcoran, Stefano Peluchetti etc, that produce excellent documentation, but generally the quality of documentation is poor.

Perhaps I should create a Hall of Fame and Shame for authors that produce good and bad documentation ? :)

Geoff

Date: Wed, 29 Apr 2015 08:53:58 -0300
From: iburgueno@gmail.com
To: lua-l@lists.lua.org
Subject: Re: Frustration with Luacom and Excel

Have you tried with:

excel.Selection.Hyperlinks:Add({Anchor = excel.Selection, Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} )

Anyway, if that fails (which probably will), you can take a look at the definition of the Add method (ActiveSheed.Hyperlinks.Add) using OleView, as suggested earlier.

That's because you are using named parameters, and I don't recall how LuaCom deals with those.

Named parameters are like a "shortcut" were you don't need to provide all arguments to a function if some of them are optional.

For instance, this function (not related to Excel)

HRESULT StartRecording(

[in] BSTR Path,

[in, optional, defaultvalue(0)] short Width,

[in, optional, defaultvalue(0)] short Height,

[in, optional, defaultvalue(0)] short FrameRate);

Could be invoked as StartRecording( Path="something", FrameRate=30 )

But, with LuaCom, you might need to invoke it passing nils for the arguments not provided.

StartRecording( "something", nil, nil, FrameRate=30 )

Hope that helps.