RE: Frustration with Luacom and Excel

[Thomas Buergel <Thomas.Buergel@...>]

> 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.

As Ignacio said, just pad with nils.

Named parameters are somewhat supported,  depending on LuaCOM version. The one I’m using, 1.4, mentions the following:
----
LuaCOM now handles correctly COM calls with named parameters 1). This caused problems
when receiving Microsoft ExcelR events;
----

and the footnote 1 says:

----
1) Notice that LuaCOM does not implement named parameters; it just takes them when called from a COM client and
puts them.
----

So your link example works if called like this:

local luacom = require 'luacom'

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

sheet.Cells(1, 1):Select()
 
-- just complete guess here
excel.Selection.Hyperlinks:Add(excel.Selection, "http://www.bbc.co.uk/";, nil, nil, "BBC" ) -- put link
book:SaveAs("c:\\temp\\hyperlink.xlsx")
book:Close()

The nil, nil are for the two optional named parameters for the HyperLinks.Add method (https://msdn.microsoft.com/en-us/library/office/ff822490.aspx)

Adding graphs isn't all that hard, I managed to get that to work with not too much trouble. Fiddling with the graph properties is a bit more involved as you have to look at them in Excel to see if they look right.

Some (incomplete) snippets are below, which might help.

Cheers,
Tom

The following assumes we have two sheets:
graphsheet: where the graph is added, and
datasheet: where the data for the graph is (organized in a particular manner)

local function AddGraph(graphsheet, datasheet, color)
	color = color or xlenums.XlRgbColor.rgbMediumSeaGreen
	local numMachines = datasheet.Rows(1):End(xlenums.XlDirection.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
	chart.HasTitle = true
	chart.ChartTitle.Text = graphsheet.Name .. " - " .. "the graph for the data"
	chart.ChartType = xlenums.XlChartType.xlXYScatter
	local axis = chart:Axes(xlenums.XlAxisType.xlValue)
	axis.ReversePlotOrder = true
	axis.MajorUnit = 1
	axis = chart:Axes(xlenums.XlAxisType.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