RE: Frustration with Luacom and Excel

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

> This still leaves a bunch of unanswered questions though
> 
> 1) Why are the AddLine params scrambled compared to MSDN docs ?

Don't know, haven't seen that.

But yes, it is a bit frustrating. One thing I noticed is that in some cases one has to use the self-call (with object:method) while other times it's the regular table access (object.field). It mostly, but not always, corresponds to "method" vs. "property" in the COM world... if it doesn't behave properly, I try the other way around. Your fake first parameter would seem to indicate that it should be a colon-call, not a dot-call.
 
> 2) Luacom seems to have reference to the Excel Enums but I couldn't get
> it to work and had to use 2 rather than msoArrowheadTriangle.
> What I am doing wrong on the enums ?

I got /some/ enums with the following piece of code (uncomment the commented out block to see what I mean). msoXXX definitions are not in there, I suspect they may be defined in a different object than Excel.Application but never bothered to try to find them.

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

> 3) Writing Lua code like this, largely by trial and error of syntax sucks !
> Are there any tips for doing this more scientifically with less trial and error
> guessing ?

If you look into the LuaCOM source, there are some guesses as to how to map the COM world into/out of the Lua world. Sometimes it makes assumptions that don't quite work.

My experience has been that starting out with Excel/LuaCOM has a steep learning curve, but once you get into it it's not as bad.

Practice makes perfect...?

 
> 4) Does anyone have any general snippets of Lua/Excel code
> that they would care to donate by posting here?

I have some but it's too much/intertwined with application code to post. Maybe one of these days I'll get a moment to extract the relevant bits; if that happens, I'll post it.

Cheers,
Tom

 
Thanks 
 
Geoff
 
 
 
 
________________________________________
From: spammealot1@live.co.uk
To: lua-l@lists.lua.org
Date: Mon, 27 Apr 2015 12:58:36 +0100
Subject: Frustration with Luacom and Excel
 Hi
 
I have been trying to use Luacom to create a moderately complex Excel spreadsheet, its proving to be a very frustrating and difficult task largely due to  the lack of documentation and simple examples to follow. I have scoured the Internet and there only seems to be about 3 very simple snippets of lua code showing how to use it with Excel. I found that pretty surprising as I am guessing the main usage of Luacom is to be able to hook up Lua and Excel.
 
I made some progress largely by guesswork of the Luacom Excel API syntax. I am currently stuck on how to draw arrows on the Excel spreadsheet.  I can draw lines with the following code but cant figure out how to turn the lines into arrows

require "luacom"

 excel = luacom.CreateObject("Excel.Application")
 local wb  = excel.Workbooks:Add()
 local ws = wb.Worksheets(1)
 excel.Visible = true
 excel.DisplayAlerts = false
 
 -- MSDN Docs says  (BeginX, BeginY, EndX, EndY)
 -- For Luacom though its (unknown, xEnd, yEnd, xStart, yStart)
 x = ws.Shapes.AddLine( 1, 200, 100, 50, 10 )
 x:Select()
 
--  ws.Shapes.ShapeRange.Line.EndArrowheadStyle = 2 -- msoArrowheadTriangle
 x = ws.Shapes.AddLine( 1, 100, 100, 50, 10 )
 x:Select()

I cant figure out the syntax for setting the arrowheadstyle, it must be something vaguely like
 
ws.Shapes.ShapeRange.Line.EndArrowheadStyle = 2 -- msoArrowheadTriangle
 
For reference the vba macro code to do the same thing is
    ActiveSheet.Shapes.AddLine(271.2, 120.6, 423, 214.2).Select
    Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle
 
 
A secondary question. Can anyone explain why the params are different for the AddLine function compared to what the MSDN COM documentation ? From trial and error I figured out there is a mysterious unknown first param that doesn't seem to do anything and then the 4 remaining co-ords are juggled compared to MSDN docs.
 
How the heck am I supposed to know that for Lua usage you have to use 5 params and juggle em around ? It took me ages of trial and error to stumble on that solution !! 
 
Any help most appreciated, thanks.
 
P.S
I think it would be useful to create a new message thread here where folks could post some simple working snippets of Lua code showing some different Excel features. Are there enough people here that have used Luacom/Excel and would be willing to post a few snippets of code ? It would greatly add to the Internet Knowledge base on this topic even if it was only a handful of scripts
 
Geoff