lua-users home
lua-l archive

[Date Prev][Date Next][Thread Prev][Thread Next] [Date Index] [Thread Index]

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 )
--  ws.Shapes.ShapeRange.Line.EndArrowheadStyle = 2 -- msoArrowheadTriangle
 x = ws.Shapes.AddLine( 1, 100, 100, 50, 10 )

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