RE: Frustration with Luacom and Excel

Hi

I am getting nowhere fast trying to figure out how to make a chart in Excel.

I took Thomas's code snippet and started to try and adapt it to be a standalone small chart demo in Lua. I cant see what I am doing wrong so if anyone could assist I would be most grateful. The Internet is completely devoid of any example Lua scripts for making charts.

I am getting lots of errors when trying to set properties in the graph function. One example error

lua: COM exception:(.\src\library\tLuaCOM.cpp,394):Unable to set the HasTitle property of the Chart class
stack traceback:

Thanks for any help

Geoff

For reference here is my Lua example code so far

require "luacom"

local rgbMediumSeaGreen = 7451452
local xlToRight = -4161
local xlXYScatter = -4169
local xlValue = 2
local xlCategory = 1

function AddGraph(graphsheet, datasheet, color)
color = color or rgbMediumSeaGreen

local numMachines = datasheet.Rows(1):End(xlToRight).Column / 5
-- 10 pixel per machine creates a dense graph that's good for overview,
-- though the individual labels aren't visible anymore
local width, height = 500, numMachines * 10
local xOffset, yOffset = 32, 32
local chart = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height).Chart
--#ERROR
chart.HasTitle = true
--#ERROR
--~ chart.ChartTitle.Text = graphsheet.Name .. " - " .. "the graph for the data"
chart.ChartType = xlXYScatter
local axis = chart:Axes(xlValue)
--#ERROR
--~ axis.ReversePlotOrder = true
--#ERROR
--~ axis.MajorUnit = 1
axis = chart:Axes(xlCategory)
axis.TickLabels.NumberFormatLinked = false -- to prevent "scientific" tick labels
axis.TickLabels.NumberFormat = "General" -- auto-size

-- this is now highly dependent on the data sheet layout, nevermind the details
-- it's about adding new series to the newly created chart, specifying the ranges
-- of data in 'data'sheet' to use
local startingColumn = 1
local xValuesOffset = 1 -- column offset 1: measured data
local valuesOffset = 4 -- column offset 4: ordinal number
local seriesNumber = 1
local series = "MyDataSeries"
while datasheet.Cells(2, startingColumn).Formula ~= "" do
  print((" adding series %d (%s)"):format(seriesNumber, series))
  -- next machine data set, find out how far down it goes
  local lastRow = datasheet.Columns(startingColumn + xValuesOffset):End(xlenums.XlDirection.xlDown).Row
  local series = chart:SeriesCollection():NewSeries()
  series.Name = datasheet.Cells(2, startingColumn)
  series.XValues = datasheet:Range(datasheet.Cells(2, startingColumn + xValuesOffset), datasheet.Cells(lastRow, startingColumn + xValuesOffset))
  series.Values = datasheet:Range(datasheet.Cells(2, startingColumn + valuesOffset), datasheet.Cells(lastRow, startingColumn + valuesOffset))
  series.MarkerSize = 2
  series.MarkerStyle = xlenums.XlMarkerStyle.xlMarkerStyleX
  series.MarkerForegroundColor = color
  seriesNumber = seriesNumber + 1
  startingColumn = startingColumn + 5
end
return chart
end

excel = luacom.CreateObject("Excel.Application")
local book = excel.Workbooks:Add()
local sheet1 = book.Worksheets(1)
local sheet2 = book.Worksheets(2)
excel.Visible = true
excel.DisplayAlerts = false

math.randomseed(1234)

for row=1, 10 do
for col=1, 10 do
    sheet1.Cells(row, col).Value2 = math.floor(math.random() * 100)
end
end

AddGraph(sheet1, sheet2)

Date: Thu, 30 Apr 2015 10:38:14 -0300
From: iburgueno@gmail.com
To: lua-l@lists.lua.org
Subject: 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/