RE: Frustration with Luacom and Excel

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

Hi Geoff,

You said:

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

Your code as posted worked for me. It didn't quite do what I think you would like to do but at least it doesn't error out.

One thing that I noticed when remotely driving Excel is that it sometimes gets "stuck". The COM automation happens in a secondary instance of Excel.exe, and depending on how you use/abuse it, it sometimes remains confused across sessions. So, when I have strange COM exceptions that shouldn't be, I always first check if there's an instance of Excel.exe still running and terminate it before trying again.


Since you started with my somewhat convoluted example (it was like that because of the somewhat unusual data content and arrangement I had when I wrote this code for a real application), I thought I'd give it a try to see if I can reduce it to a simple case from which you can extend.

The following code...
- generates three data series (3 columns, 100 rows each)
- gives the data series a name
- on a secondary sheet, adds a simple line chart
- associates the data with the chart

Note that I added the Excel enums again instead of redefining the used values again, mainly to avoid mistyping a definition and then chasing a non-bug because of that :)


-------- begin simplechart.lua
local luacom = require "luacom"

local function GetExcel()
	local excel = luacom.GetObject("Excel.Application")
	if not excel then
		excel = luacom.CreateObject("Excel.Application")
	end

	if not excel then
		return false, "Can't instantiate Excel"
	end
		
	local typeinfo = luacom.GetTypeInfo(excel)
	local typelib = typeinfo:GetTypeLib()
	local enums = typelib:ExportEnumerations()
	--[[ debugging - dump enumerations
	for k,v in pairs(enums) do  
		print(k,v)
		if type(v)=="table" then  
			for l,w in pairs(v) do  
				print("  ", l, w)
			end  
		end  
	end
	--]]
	return excel, enums
end

local excel, xlenums = assert(GetExcel())


-- add a simple line chart from the data in "datasheet",
-- place the chart in "graphsheet"
local function AddGraph(graphsheet, datasheet)

	local width, height = 640, 320
	local xOffset, yOffset = 32, 32
	-- add the chart at the specified location
	local chart = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height).Chart
	-- set the title
	chart.HasTitle = true
	chart.ChartTitle.Text = graphsheet.Name .. " for " .. datasheet.Name
	-- set the chart type
	chart.ChartType = xlenums.XlChartType.xlLine

	-- figure out how large our data set is
	local numRows = datasheet.Columns(1):End(xlenums.XlDirection.xlDown).Row
	local numColumns = datasheet.Rows(1):End(xlenums.XlDirection.xlToRight).Column
	
	-- this range describes the data set to add to the chart
	local sourceData = datasheet:Range(datasheet.Cells(1, 1), datasheet.Cells(numRows, numColumns))
	-- associate the source data with the chart
	chart:SetSourceData(sourceData)
	
	return chart
end
 
-- add a new workbook 
local book  = excel.Workbooks:Add()
-- use sheet 1 for the data
local sheet1 = book.Worksheets(1)
sheet1.Name = "Data"
-- and sheet 2 for the graph
local sheet2 = book.Worksheets(2)
sheet2.Name = "Chart"

excel.Visible = true
excel.DisplayAlerts = false
 
math.randomseed(os.time())

-- create three columns, 100 entries each, of data
for col=1, 3 do
	-- row 1: title
	sheet1.Cells(1, col).Value2 = ("Data series %d"):format(col)
	-- row 2-100: data
	for row=2, 100 do
		sheet1.Cells(row, col).Value2 = math.floor(math.random() * 100)
	end
end

-- add a graph on sheet 2, taking its data from sheet 1
AddGraph(sheet2, sheet1)

book:SaveAs("c:\\temp\\mygraph.xlsx")
book:Close()
-------- end simplechart.lua


Hope this helps,
Tom