Frustration with Luacom and Excel

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Frustration with Luacom and Excel

Geoff Smith
 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
 
 
 
 
 
 
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
Its always guaranteed to work, post a question on the Internet and then you will stumble on the answer within 5 minutes.
 
I just got it to work with the following code
 
x = ws.Shapes.AddLine( 1, 200, 100, 50, 10 )
x:Select()
excel.Selection.ShapeRange.Line.EndArrowheadStyle = 2             -- msoArrowheadTriangle

 
This still leaves a bunch of unanswered questions though
 
1) Why are the AddLine params scrambled compared to MSDN docs ?
 
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 ?
 
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 ?
 
4) Does anyone have any general snippets of Lua/Excel code that they would care to donate by posting here?
 
Thanks
 
Geoff
 
 
 
 

From: [hidden email]
To: [hidden email]
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
 
 
 
 
 
 
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Thijs Schreijer

Does oleview provide some insights?

Op 27 apr. 2015 14:37 schreef Geoff Smith <[hidden email]>:
Its always guaranteed to work, post a question on the Internet and then you will stumble on the answer within 5 minutes.
 
I just got it to work with the following code
 
x = ws.Shapes.AddLine( 1, 200, 100, 50, 10 )
x:Select()
excel.Selection.ShapeRange.Line.EndArrowheadStyle = 2             -- msoArrowheadTriangle

 
This still leaves a bunch of unanswered questions though
 
1) Why are the AddLine params scrambled compared to MSDN docs ?
 
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 ?
 
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 ?
 
4) Does anyone have any general snippets of Lua/Excel code that they would care to donate by posting here?
 
Thanks
 
Geoff
 
 
 
 

From: [hidden email]
To: [hidden email]
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
 
 
 
 
 
 
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

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: [hidden email]
To: [hidden email]
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
 
 
 
 
 
 

Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
Hi
 
Thanks for the replies, and the code snippets.
 
On the subject of the mysterious first parameter of the AddLine () function, I had the same hunch that instead of calling with the . method I should be calling with the :  style. However at the time when I tried it I thought it produced a COM error.
In reality though some other issue caused the COM error, and I hadn't realised that at the time.
 
Now I have fixed the other issues, I can confirm that AddLine should be called using the :  object method style.
 
My next task is to generate a couple of graphs via Luacom, that's going to be a challenge, cant say I am greatly looking forward to it.  I will likely be back here soon, with more Luacom Excel questions :)
 
Geoff
 
 
 

 
> From: [hidden email]

> To: [hidden email]
> Date: Mon, 27 Apr 2015 16:20:26 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> > 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: [hidden email]
> To: [hidden email]
> 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
>  
>  
>  
>  
>  
>  
>
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
Hi
 
As predicted I got stuck even before I tackled adding graphs.
 
I am really stuck on this one. How the heck do you add a hyperlink via Luacom ?
 
The VBA macro code to do it is
 
   Range("L8").Select
   ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="http://www.bbc.co.uk/", TextToDisplay:="BBC"
 
 
I have been guessing how to translate this to a luacom call
 
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, {Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} ) -- put link
 
Of course total guesswork fails miserably !
 
Hopefully one of you Lua experts can solve this tricky one ? Any help would be greatly appreciated again, thanks
 
Geoff
 

From: [hidden email]
To: [hidden email]
Date: Tue, 28 Apr 2015 21:06:38 +0100
Subject: RE: Frustration with Luacom and Excel

Hi
 
Thanks for the replies, and the code snippets.
 
On the subject of the mysterious first parameter of the AddLine () function, I had the same hunch that instead of calling with the . method I should be calling with the :  style. However at the time when I tried it I thought it produced a COM error.
In reality though some other issue caused the COM error, and I hadn't realised that at the time.
 
Now I have fixed the other issues, I can confirm that AddLine should be called using the :  object method style.
 
My next task is to generate a couple of graphs via Luacom, that's going to be a challenge, cant say I am greatly looking forward to it.  I will likely be back here soon, with more Luacom Excel questions :)
 
Geoff
 
 
 

 
> From: [hidden email]

> To: [hidden email]
> Date: Mon, 27 Apr 2015 16:20:26 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> > 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: [hidden email]
> To: [hidden email]
> 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
>  
>  
>  
>  
>  
>  
>
Reply | Threaded
Open this post in threaded view
|

Re: Frustration with Luacom and Excel

Ignacio Burgueño-2
Have you tried with:

excel.Selection.Hyperlinks:Add({Anchor = excel.Selection, Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} )

Anyway, if that fails (which probably will), you can take a look at the definition of the Add method (ActiveSheed.Hyperlinks.Add) using OleView, as suggested earlier.

That's because you are using named parameters, and I don't recall how LuaCom deals with those. 
Named parameters are like a "shortcut" were you don't need to provide all arguments to a function if some of them are optional.
For instance, this function (not related to Excel)

HRESULT StartRecording(
                        [in] BSTR Path, 
                        [in, optional, defaultvalue(0)] short Width, 
                        [in, optional, defaultvalue(0)] short Height, 
                        [in, optional, defaultvalue(0)] short FrameRate);

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.

StartRecording( "something", nil, nil, FrameRate=30 )


Hope that helps.
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

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


Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
In reply to this post by Ignacio Burgueño-2
Hi
 
Thanks for the suggestion as you predicted it didn't work. I was dumb enough to sit here for several hours just trying all the permutations of tables with named parameters and strings.
 
Oleview wouldn't help me, as I know what the parameters are for the Hyperlinks:Add() function are, I just don't know how Luacom is expecting them to be passed.
 
Eventually I stumbled on the correct way of passing the arguments.  It doesn't expect namedParams, or a table at all, it just has a predefined order of arguments
 
It is
 
excel.Selection.Hyperlinks:Add(cellLocation, urlRootStr, urlFragmentStr, ToolTipStr, HyperLinkTextStr)
 
where  urlRootStr, urlFragmentStr are appended together with a # for the final url string
 
Full working example
 
require "luacom"
 
excel = luacom.CreateObject("Excel.Application")
local book  = excel.Workbooks:Add()
local sheet = book.Worksheets(1)

excel.Visible = true

 sheet.Cells(1, 1):Select()
 excel.Selection.Hyperlinks:Add(excel.Selection, "http://www.bbc.co.uk/", "election-2015-32506490", "ToolTip", "BBC")

This should have been a 30 second exercise for me to look up the docs on the web, not the several hours it actually took.
 
IMHO this lack of good library documentation is a huge problem for Lua,  as this is not an isolated case.

 

There are a few notable exceptions like Steve Donovan, Leaf Corcoran,  Stefano Peluchetti etc, that produce excellent documentation, but generally the quality of documentation is poor.

 

Perhaps I should create a Hall of Fame and Shame for authors that produce good and bad documentation ? :)

 

Geoff
 


Date: Wed, 29 Apr 2015 08:53:58 -0300
From: [hidden email]
To: [hidden email]
Subject: Re: Frustration with Luacom and Excel

Have you tried with:

excel.Selection.Hyperlinks:Add({Anchor = excel.Selection, Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} )

Anyway, if that fails (which probably will), you can take a look at the definition of the Add method (ActiveSheed.Hyperlinks.Add) using OleView, as suggested earlier.

That's because you are using named parameters, and I don't recall how LuaCom deals with those. 
Named parameters are like a "shortcut" were you don't need to provide all arguments to a function if some of them are optional.
For instance, this function (not related to Excel)

HRESULT StartRecording(
                        [in] BSTR Path, 
                        [in, optional, defaultvalue(0)] short Width, 
                        [in, optional, defaultvalue(0)] short Height, 
                        [in, optional, defaultvalue(0)] short FrameRate);

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.

StartRecording( "something", nil, nil, FrameRate=30 )


Hope that helps.
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
Hi Thomas
 
Thanks for your message, our messages crossed in the post !
 
For the Excel hyperlink problem, our solutions agreed. Yay :)
 
Thanks for the graphing snippets, I am sure they will be very useful when I get around to tackling that one
 
Geoff
 

From: [hidden email]
To: [hidden email]
Date: Wed, 29 Apr 2015 13:59:59 +0100
Subject: RE: Frustration with Luacom and Excel

Hi
 
Thanks for the suggestion as you predicted it didn't work. I was dumb enough to sit here for several hours just trying all the permutations of tables with named parameters and strings.
 
Oleview wouldn't help me, as I know what the parameters are for the Hyperlinks:Add() function are, I just don't know how Luacom is expecting them to be passed.
 
Eventually I stumbled on the correct way of passing the arguments.  It doesn't expect namedParams, or a table at all, it just has a predefined order of arguments
 
It is
 
excel.Selection.Hyperlinks:Add(cellLocation, urlRootStr, urlFragmentStr, ToolTipStr, HyperLinkTextStr)
 
where  urlRootStr, urlFragmentStr are appended together with a # for the final url string
 
Full working example
 
require "luacom"
 
excel = luacom.CreateObject("Excel.Application")
local book  = excel.Workbooks:Add()
local sheet = book.Worksheets(1)

excel.Visible = true

 sheet.Cells(1, 1):Select()
 excel.Selection.Hyperlinks:Add(excel.Selection, "http://www.bbc.co.uk/", "election-2015-32506490", "ToolTip", "BBC")

This should have been a 30 second exercise for me to look up the docs on the web, not the several hours it actually took.
 
IMHO this lack of good library documentation is a huge problem for Lua,  as this is not an isolated case.

 

There are a few notable exceptions like Steve Donovan, Leaf Corcoran,  Stefano Peluchetti etc, that produce excellent documentation, but generally the quality of documentation is poor.

 

Perhaps I should create a Hall of Fame and Shame for authors that produce good and bad documentation ? :)

 

Geoff
 


Date: Wed, 29 Apr 2015 08:53:58 -0300
From: [hidden email]
To: [hidden email]
Subject: Re: Frustration with Luacom and Excel

Have you tried with:

excel.Selection.Hyperlinks:Add({Anchor = excel.Selection, Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} )

Anyway, if that fails (which probably will), you can take a look at the definition of the Add method (ActiveSheed.Hyperlinks.Add) using OleView, as suggested earlier.

That's because you are using named parameters, and I don't recall how LuaCom deals with those. 
Named parameters are like a "shortcut" were you don't need to provide all arguments to a function if some of them are optional.
For instance, this function (not related to Excel)

HRESULT StartRecording(
                        [in] BSTR Path, 
                        [in, optional, defaultvalue(0)] short Width, 
                        [in, optional, defaultvalue(0)] short Height, 
                        [in, optional, defaultvalue(0)] short FrameRate);

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.

StartRecording( "something", nil, nil, FrameRate=30 )


Hope that helps.
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
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.
 
I only needed to update 50 or so cells in my spreadsheet every 1 second, so I wasn't too worried about it running fast enough.  That was until I tried it. The performance sucked really badly, so I wrote a simple benchmark script to try and understand the problem better and see what tricks I can do to  improve the execution speed.
 
There are 5 benchmark tests, all doing the same thing, updating a 10 x 10 array of cells, repeated 15 times.
 
Test1
This updates the 100 cells but with Excel not visible
 
Test2
This updates the 100 cells but with Excel visible
 
Test3
This updates the 100 cells but with Excel  visible but with screen updating and calculation updating both turned off while the cells are changing.

Test4
This updates the 100 cells but with Excel  visible but with screen updating and calculation updating both turned off while the cells are changing.
This test selects the cell first and then writes to it using excel.Selection.Value2
 
Test5
This updates the 100 cells but with Excel  visible but with screen updating and calculation updating both turned off while the cells are changing.
This test first creates a Lua Table of 100 elements and then writes the table to excel with a single  sheet:Range(RangeRef).Value2 = testTab

Test Results
Test 1:              Plot Cells while Excel is not visible = 5.133 Secs
Test 2:                  Plot Cells while Excel is visible = 12.19 Secs
Test 3: Plot Cells while Excel is visible but not updating = 9.925 Secs
Test 4:   Plot Cells visible, not updating, using Select() = 9.635 Secs
Test 5:      Plot Cells visible, not updating, using Range = 0.122 Secs


100 fold difference, between best and worst !!
 
Testing Method
If you do try this bit of code the testing method is important, it makes a big difference to the numbers. Originally I thought I was seeing a random variation of test2 between 12 and 6 seconds.  I then realised what was causing the difference. To run the test make sure that when the code is running, Excel is in the foreground Window. Make sure the mouse is over the Excel window (you should see the 12 Secs or so time).
Then run the same test again where Excel is the foreground window but move the mouse outside of the Excel Window (you should see the 6 seconds result).  That was a surprising observation.
 
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.
 
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.
 
require "luacom"

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

 

 function clearAll ()
  sheet.Cells:Select()
  excel.Selection:Delete(-4162)  -- xlUp
  sheet:Range("A1"):Select()
 end


 print("Starting Excel BenchMarks\n")
 local testNum = 1


 excel.DisplayAlerts = false
 
 -- ******************************* Test 1 **************************************
 excel.Visible = false

 local startTime = os.clock()
 for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do

   sheet.Cells(row, col).Value2 = string.format("%d:%d,%d", i, row, col)
   sheet.Cells(row, col).HorizontalAlignment = -4108 -- align Center
   sheet.Cells(row, col).Font.ColorIndex = 11

    end
  end
 end

 excel.Visible = true
 local endTime = os.clock()
 print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells while Excel is not visible", endTime - startTime))
 testNum = testNum + 1
 -- ******************************* Test 2 **************************************
 clearAll()  -- Clear Previous Test Cells
 local startTime = os.clock()
 excel.Visible = true
 for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do

   sheet.Cells(row, col).Value2 = string.format("%d:%d,%d", i, row, col)
   sheet.Cells(row, col).HorizontalAlignment = -4108 -- align Center
   sheet.Cells(row, col).Font.ColorIndex = 11

    end
  end
 end

 local endTime = os.clock()

 print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells while Excel is visible", endTime - startTime))
 testNum = testNum + 1
 -- ******************************* Test 3 **************************************
 clearAll()  -- Clear Previous Test Cells
 local startTime = os.clock()
 excel.Application.ScreenUpdating = false
 excel.ActiveSheet.EnableCalculation = false

 for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do

   sheet.Cells(row, col).Value2 = string.format("%d:%d,%d", i, row, col)
   sheet.Cells(row, col).HorizontalAlignment = -4108 -- align Center
   sheet.Cells(row, col).Font.ColorIndex = 11

    end
  end
 end

 excel.Application.ScreenUpdating = true
 excel.ActiveSheet.EnableCalculation = true

 local endTime = os.clock()
 print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells while Excel is visible but not updating", endTime - startTime))
 testNum = testNum + 1

 -- ******************************* Test 4 **************************************
 clearAll()  -- Clear Previous Test Cells
 local startTime = os.clock()
 excel.Application.ScreenUpdating = false
 excel.ActiveSheet.EnableCalculation = false

 for i=1, LOOPS do
  for row=1, 10 do
    for col=1, 10 do

   sheet.Cells(row, col):Select()
   excel.Selection.Value2 = string.format("%d:%d,%d", i, row, col)
   excel.Selection.HorizontalAlignment = -4108 -- align Center
   excel.Selection.Font.ColorIndex = 11

    end
  end
 end

 excel.Application.ScreenUpdating = true
 excel.ActiveSheet.EnableCalculation = true

 local endTime = os.clock()
 print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells visible, not updating, using Select()", endTime - startTime))
 testNum = testNum + 1
 
 -- ******************************* Test 5 **************************************
 clearAll()  -- Clear Previous Test Cells
 local startTime = os.clock()
 excel.Application.ScreenUpdating = false
 excel.ActiveSheet.EnableCalculation = false

 RangeRef = "A1:J10"
 for i=1, LOOPS do
  local testTab = {}
  -- make a Lua table that we can pass into Excel as a 10 x 10 cell range
  for row=1, 10 do
   local innerTab = {}
   for col=1, 10 do
    innerTab[#innerTab+1] = string.format("%d:%d,%d", i, row, col)
   end
   testTab[#testTab+1] = innerTab
  end

  sheet:Range(RangeRef).Value2 = testTab
  sheet:Range(RangeRef).HorizontalAlignment = -4108 -- align Center
  sheet:Range(RangeRef).Font.ColorIndex = 11

 end
 excel.Application.ScreenUpdating = true
 excel.ActiveSheet.EnableCalculation = true

 local endTime = os.clock()
 print(string.format("Test %d: %50s = %3.3f Secs", testNum, "Plot Cells visible, not updating, using Range", endTime - startTime))
 testNum = testNum + 1
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

From: [hidden email]
To: [hidden email]
Date: Wed, 29 Apr 2015 14:07:51 +0100
Subject: RE: Frustration with Luacom and Excel

Hi Thomas
 
Thanks for your message, our messages crossed in the post !
 
For the Excel hyperlink problem, our solutions agreed. Yay :)
 
Thanks for the graphing snippets, I am sure they will be very useful when I get around to tackling that one
 
Geoff
 

From: [hidden email]
To: [hidden email]
Date: Wed, 29 Apr 2015 13:59:59 +0100
Subject: RE: Frustration with Luacom and Excel

Hi
 
Thanks for the suggestion as you predicted it didn't work. I was dumb enough to sit here for several hours just trying all the permutations of tables with named parameters and strings.
 
Oleview wouldn't help me, as I know what the parameters are for the Hyperlinks:Add() function are, I just don't know how Luacom is expecting them to be passed.
 
Eventually I stumbled on the correct way of passing the arguments.  It doesn't expect namedParams, or a table at all, it just has a predefined order of arguments
 
It is
 
excel.Selection.Hyperlinks:Add(cellLocation, urlRootStr, urlFragmentStr, ToolTipStr, HyperLinkTextStr)
 
where  urlRootStr, urlFragmentStr are appended together with a # for the final url string
 
Full working example
 
require "luacom"
 
excel = luacom.CreateObject("Excel.Application")
local book  = excel.Workbooks:Add()
local sheet = book.Worksheets(1)

excel.Visible = true

 sheet.Cells(1, 1):Select()
 excel.Selection.Hyperlinks:Add(excel.Selection, "http://www.bbc.co.uk/", "election-2015-32506490", "ToolTip", "BBC")

This should have been a 30 second exercise for me to look up the docs on the web, not the several hours it actually took.
 
IMHO this lack of good library documentation is a huge problem for Lua,  as this is not an isolated case.

 

There are a few notable exceptions like Steve Donovan, Leaf Corcoran,  Stefano Peluchetti etc, that produce excellent documentation, but generally the quality of documentation is poor.

 

Perhaps I should create a Hall of Fame and Shame for authors that produce good and bad documentation ? :)

 

Geoff
 


Date: Wed, 29 Apr 2015 08:53:58 -0300
From: [hidden email]
To: [hidden email]
Subject: Re: Frustration with Luacom and Excel

Have you tried with:

excel.Selection.Hyperlinks:Add({Anchor = excel.Selection, Address="http://www.bbc.co.uk/", TextToDisplay="BBC"} )

Anyway, if that fails (which probably will), you can take a look at the definition of the Add method (ActiveSheed.Hyperlinks.Add) using OleView, as suggested earlier.

That's because you are using named parameters, and I don't recall how LuaCom deals with those. 
Named parameters are like a "shortcut" were you don't need to provide all arguments to a function if some of them are optional.
For instance, this function (not related to Excel)

HRESULT StartRecording(
                        [in] BSTR Path, 
                        [in, optional, defaultvalue(0)] short Width, 
                        [in, optional, defaultvalue(0)] short Height, 
                        [in, optional, defaultvalue(0)] short FrameRate);

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.

StartRecording( "something", nil, nil, FrameRate=30 )


Hope that helps.
Reply | Threaded
Open this post in threaded view
|

Re: Frustration with Luacom and Excel

Ignacio Burgueño-2


On Thu, Apr 30, 2015 at 8:51 AM, Geoff Smith <[hidden email]> 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/


Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
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: [hidden email]
To: [hidden email]
Subject: Re: Frustration with Luacom and Excel



On Thu, Apr 30, 2015 at 8:51 AM, Geoff Smith <[hidden email]> 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/


Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

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


Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
Hello Thomas
 
Firstly I wanted to say thanks very much for trying to help me out with this problem. Even if I cant solve my specific problem, this chart example will help other people in the future, its the only sample code on the web !
 
I still get the same error(s)
 
lua: COM exception:(.\src\library\tLuaCOM.cpp,394):Unable to set the HasTitle property of the Chart class
 
OK so this must be some sort of version discrepancy between my setup and yours. Lets list the versions I am using here see if anything obvious jumps out.
 
I am using Windows 7, Lua 5.1, Excel 2003
 
I just downloaded and installed Lua For windows the very latest version so that should mean I am using Luacom V1.4.
 
Tried again, still same error unknown HasTitle property. I renamed luacom.dll to junk name, and ran again, as expected it complained it couldn't find luacom. So proving I haven't got a pathing problem and using some old out of date luacom I didn't know I had.
 
OK so that points maybe at my ancient version of Excel2003 being the culprit. Maybe that property wasn't defined for Excel2003 ? Did a bit of digging around and found this link
 
https://msdn.microsoft.com/en-us/library/office/aa173287(v=office.11).aspx
 
That seems to indicate in Excel 2003 there is definitely a chart "HasTitle" property.
 
At which point, I am stumped again. I cant see what the difference is between our setups that causes it to work for you but not for me ?  Could you list your version numbers please ?
 
Incidentally, did you define anything to get the enums to work,  for example  xlenums.XlChartType.xlLine.  Is it extracting that value from Excel via Luacom ?  The enums are all unknown for my setup here.
 
Thanks again, any clues on what might be causing this annoying problem ?
 
Geoff
 
 
 
 
 
 

 
> From: [hidden email]

> To: [hidden email]
> Date: Mon, 4 May 2015 10:00:28 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> 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
>
>
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Thomas Buergel
Hi Geoff,

My setup is mostly the same (LuaForWindows 5.1.46, which has LuaCOM 1.4 on Windows 7). The major difference between our setups is the version of Excel, which, in my case, is 2003. That would explain the different behaviors between our setups, as we're remote-controlling features/functionality in a COM server, which has a different version.

Documentation on Excel 2003 does indeed seem very hard to come by. I found a copy of a scanned book (using Google search terms "excel 2002 vba programmer's reference pdf"), which had some VBA examples for charts - similar to the one you linked. Apparently Excel 2003 and 2002 are very similar.

Looking at that, it seems like there's one indirection less in the Excel 2003 code. So if I'd had to wager a guess, I would translate it to LuaCOM as:

local chart2003 = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height) -- note, no trailing ".Chart"
local chart2010 = chart2003.Chart
-- set the title
chart2003.HasTitle = true
chart2003.ChartTitle.Text= graphsheet.Name .. " for " .. datasheet.Name

Others seem to have noticed differences between Excel 2003 and later versions, for example:
http://stackoverflow.com/questions/2828722/exception-in-creating-excel-chart-by-c-sharp

Also, it seems in the samples that I have seen that the chart is always "Activate()"ed. I don't know if this is just necessary in Excel 2003; a similar problem (albeit described for VBA) is here:

http://www.mrexcel.com/forum/excel-questions/81168-chart-question-excel-2003-a.html

Maybe that's the key?

Sorry, I can't try that, I don't have Excel 2003 handy. In general I look at the VBA samples, squint my eyes and try to translate it to Lua, which mostly works almost right away...

As for the enums:

> Incidentally, did you define anything to get the enums to work,  for example
> xlenums.XlChartType.xlLine.  Is it extracting that value from Excel via Luacom ?
> The enums are all unknown for my setup here.

Yes, they are created from the type library inside the GetExcel function:

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

At least with Excel 2003's automation interface, I get all the xlXXX and rgbXXX enums defined. Not sure where the msoXXX ones are hiding, but they aren't exposed this way.

Cheers,
Tom

---

From: [hidden email] [mailto:[hidden email]] On Behalf Of Geoff Smith
Sent: Monday, May 04, 2015 1:06 PM
To: Lua mailing list
Subject: RE: Frustration with Luacom and Excel

Hello Thomas
 
Firstly I wanted to say thanks very much for trying to help me out with this problem. Even if I cant solve my specific problem, this chart example will help other people in the future, its the only sample code on the web !
 
I still get the same error(s)
 
lua: COM exception:(.\src\library\tLuaCOM.cpp,394):Unable to set the HasTitle property of the Chart class
 
OK so this must be some sort of version discrepancy between my setup and yours. Lets list the versions I am using here see if anything obvious jumps out.
 
I am using Windows 7, Lua 5.1, Excel 2003
 
I just downloaded and installed Lua For windows the very latest version so that should mean I am using Luacom V1.4.
 
Tried again, still same error unknown HasTitle property. I renamed luacom.dll to junk name, and ran again, as expected it complained it couldn't find luacom. So proving I haven't got a pathing problem and using some old out of date luacom I didn't know I had.
 
OK so that points maybe at my ancient version of Excel2003 being the culprit. Maybe that property wasn't defined for Excel2003 ? Did a bit of digging around and found this link
 
https://msdn.microsoft.com/en-us/library/office/aa173287(v=office.11).aspx
 
That seems to indicate in Excel 2003 there is definitely a chart "HasTitle" property.
 
At which point, I am stumped again. I cant see what the difference is between our setups that causes it to work for you but not for me ?  Could you list your version numbers please ?
 
Incidentally, did you define anything to get the enums to work,  for example  xlenums.XlChartType.xlLine.  Is it extracting that value from Excel via Luacom ?  The enums are all unknown for my setup here.
 
Thanks again, any clues on what might be causing this annoying problem ?
 
Geoff
 
 
 
 
 
 

 

> From: [hidden email]
> To: [hidden email]
> Date: Mon, 4 May 2015 10:00:28 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> 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
>
>

Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
Hi Thomas
 
Was that a typo in your reply ? you said "version of Excel, which, in my case, is 2003"
 
I said I am also using 2003 in my earlier email.
 
I hadn't realised MS had changed the API so fundamentally across Excel versions.  That was a bright move on their part Sighhhhhhhh.
 
Geoff
 

 
> From: [hidden email]

> To: [hidden email]
> Date: Mon, 4 May 2015 13:59:35 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> Hi Geoff,
>
> My setup is mostly the same (LuaForWindows 5.1.46, which has LuaCOM 1.4 on Windows 7). The major difference between our setups is the version of Excel, which, in my case, is 2003. That would explain the different behaviors between our setups, as we're remote-controlling features/functionality in a COM server, which has a different version.
>
> Documentation on Excel 2003 does indeed seem very hard to come by. I found a copy of a scanned book (using Google search terms "excel 2002 vba programmer's reference pdf"), which had some VBA examples for charts - similar to the one you linked. Apparently Excel 2003 and 2002 are very similar.
>
> Looking at that, it seems like there's one indirection less in the Excel 2003 code. So if I'd had to wager a guess, I would translate it to LuaCOM as:
>
> local chart2003 = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height) -- note, no trailing ".Chart"
> local chart2010 = chart2003.Chart
> -- set the title
> chart2003.HasTitle = true
> chart2003.ChartTitle.Text= graphsheet.Name .. " for " .. datasheet.Name
>
> Others seem to have noticed differences between Excel 2003 and later versions, for example:
> http://stackoverflow.com/questions/2828722/exception-in-creating-excel-chart-by-c-sharp
>
> Also, it seems in the samples that I have seen that the chart is always "Activate()"ed. I don't know if this is just necessary in Excel 2003; a similar problem (albeit described for VBA) is here:
>
> http://www.mrexcel.com/forum/excel-questions/81168-chart-question-excel-2003-a.html
>
> Maybe that's the key?
>
> Sorry, I can't try that, I don't have Excel 2003 handy. In general I look at the VBA samples, squint my eyes and try to translate it to Lua, which mostly works almost right away...
>
> As for the enums:
>
> > Incidentally, did you define anything to get the enums to work, for example
> > xlenums.XlChartType.xlLine. Is it extracting that value from Excel via Luacom ?
> > The enums are all unknown for my setup here.
>
> Yes, they are created from the type library inside the GetExcel function:
>
> > 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
> > --]]
>
> At least with Excel 2003's automation interface, I get all the xlXXX and rgbXXX enums defined. Not sure where the msoXXX ones are hiding, but they aren't exposed this way.
>
> Cheers,
> Tom
>
> ---
>
> From: [hidden email] [mailto:[hidden email]] On Behalf Of Geoff Smith
> Sent: Monday, May 04, 2015 1:06 PM
> To: Lua mailing list
> Subject: RE: Frustration with Luacom and Excel
>
> Hello Thomas
>  
> Firstly I wanted to say thanks very much for trying to help me out with this problem. Even if I cant solve my specific problem, this chart example will help other people in the future, its the only sample code on the web !
>  
> I still get the same error(s)
>  
> lua: COM exception:(.\src\library\tLuaCOM.cpp,394):Unable to set the HasTitle property of the Chart class
>  
> OK so this must be some sort of version discrepancy between my setup and yours. Lets list the versions I am using here see if anything obvious jumps out.
>  
> I am using Windows 7, Lua 5.1, Excel 2003
>  
> I just downloaded and installed Lua For windows the very latest version so that should mean I am using Luacom V1.4.
>  
> Tried again, still same error unknown HasTitle property. I renamed luacom.dll to junk name, and ran again, as expected it complained it couldn't find luacom. So proving I haven't got a pathing problem and using some old out of date luacom I didn't know I had.
>  
> OK so that points maybe at my ancient version of Excel2003 being the culprit. Maybe that property wasn't defined for Excel2003 ? Did a bit of digging around and found this link
>  
> https://msdn.microsoft.com/en-us/library/office/aa173287(v=office.11).aspx
>  
> That seems to indicate in Excel 2003 there is definitely a chart "HasTitle" property.
>  
> At which point, I am stumped again. I cant see what the difference is between our setups that causes it to work for you but not for me ?  Could you list your version numbers please ?
>  
> Incidentally, did you define anything to get the enums to work,  for example  xlenums.XlChartType.xlLine.  Is it extracting that value from Excel via Luacom ?  The enums are all unknown for my setup here.
>  
> Thanks again, any clues on what might be causing this annoying problem ?
>  
> Geoff
>  
>  
>  
>  
>  
>  
>
>  
> > From: [hidden email]
> > To: [hidden email]
> > Date: Mon, 4 May 2015 10:00:28 +0000
> > Subject: RE: Frustration with Luacom and Excel
> >
> > 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
> >
> >
>
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Thomas Buergel
Hi Geoff,

> Was that a typo in your reply ? you said "version of Excel, which, in my case, is 2003"

Yes, sorry, you're absolutely right - the version on my end is *2010*. :)

BTW, after lots of digging I seem to have found the Excel 2003 official documentation. It seems somewhat complete but not quite...

Entry point: https://msdn.microsoft.com/en-us/library/aa220733%28v=office.11%29.aspx
How-to for charts (VBA): https://msdn.microsoft.com/en-us/library/aa203725%28v=office.11%29.aspx

Maybe the changes to the Excel API weren't so large, but my example is just using it in a 2010-only way. I don't know enough about Excel to say what's the case, though setting a title of a chart seems like something that must have been there from day 1.

Good luck with getting it running the way you want.

Tom
 

Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Geoff Smith
Hi Thomas
 
Yay,  cracked it !!! . With your help I managed to get it working now with Excel 2003
 
Lots more trial and error and persistence, this is what I discovered
 
You were right , for Excel 2003 you need 1 less level of indirection. I.e
 
local chart = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height)
 
But that still didn't work, it wouldn't let me do the setSourceData() on the chart object.
 
I changed that slightly to be
 
chart:Activate()
 
 -- associate the source data with the chart
 excel.ActiveChart:SetSourceData(sourceData) 
 
It was happy to do it that way, must just be differences between 2003 and 2010
 
It still didn't work though, as it wouldn't set the Title properties. The reason for this is that its very fussy about ordering of lines.
 
The order must be   xxx.ChartTitle.Text must be after xxx.HasTitle, and both of these must be after the SetSourceData, else it doesn't work.
 
 
Full working code below.
Note for future "Googlers", this sample works for Excel 2003 but almost certainly wont work for Excel 2010. (Just use Thomas's earlier snippet for later Excel versions)
 
Thanks again
 
Geoff
 
 
 
-----------------------------------------------------------------------
 
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()
 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)
 -- 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))
 chart:Activate()
 -- associate the source data with the chart
 excel.ActiveChart:SetSourceData(sourceData)
 excel.ActiveChart.HasTitle = true
 excel.ActiveChart.ChartTitle.Text = graphsheet.Name .. " for " .. datasheet.Name
 return chart
end

 ----------------------------  Main code starts here ------------------------------
 
 -- 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:\mygraph.xls]])
 --~ book:Close()
 
 
 
 
 
 
 
 
 

 
> From: [hidden email]

> To: [hidden email]
> Date: Mon, 4 May 2015 15:45:50 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> Hi Geoff,
>
> > Was that a typo in your reply ? you said "version of Excel, which, in my case, is 2003"
>
> Yes, sorry, you're absolutely right - the version on my end is *2010*. :)
>
> BTW, after lots of digging I seem to have found the Excel 2003 official documentation. It seems somewhat complete but not quite...
>
> Entry point: https://msdn.microsoft.com/en-us/library/aa220733%28v=office.11%29.aspx
> How-to for charts (VBA): https://msdn.microsoft.com/en-us/library/aa203725%28v=office.11%29.aspx
>
> Maybe the changes to the Excel API weren't so large, but my example is just using it in a 2010-only way. I don't know enough about Excel to say what's the case, though setting a title of a chart seems like something that must have been there from day 1.
>
> Good luck with getting it running the way you want.
>
> Tom
>
>
Reply | Threaded
Open this post in threaded view
|

RE: Frustration with Luacom and Excel

Thomas Buergel
Hi Geoff,

Nice :) You get used to the peculiarities after a while.

> Full working code below.
> Note for future "Googlers", this sample works for Excel 2003 but almost certainly
> wont work for Excel 2010. (Just use Thomas's earlier snippet for later Excel versions)

I just tried it with Excel 2010. It works there, too, so yours is the more compatible/generic version.

Cheers,
Tom

---
From: [hidden email] [mailto:[hidden email]] On Behalf Of Geoff Smith
Sent: Monday, May 04, 2015 8:18 PM
To: Lua mailing list
Subject: RE: Frustration with Luacom and Excel

Hi Thomas
 
Yay,  cracked it !!! . With your help I managed to get it working now with Excel 2003
 
Lots more trial and error and persistence, this is what I discovered
 
You were right , for Excel 2003 you need 1 less level of indirection. I.e
 
local chart = graphsheet:ChartObjects():Add(xOffset, yOffset, width, height)
 
But that still didn't work, it wouldn't let me do the setSourceData() on the chart object.
 
I changed that slightly to be
 
chart:Activate()
 
 -- associate the source data with the chart
 excel.ActiveChart:SetSourceData(sourceData) 
 
It was happy to do it that way, must just be differences between 2003 and 2010
 
It still didn't work though, as it wouldn't set the Title properties. The reason for this is that its very fussy about ordering of lines.
 
The order must be   xxx.ChartTitle.Text must be after xxx.HasTitle, and both of these must be after the SetSourceData, else it doesn't work.
 
 
Full working code below.
Note for future "Googlers", this sample works for Excel 2003 but almost certainly wont work for Excel 2010. (Just use Thomas's earlier snippet for later Excel versions)
 
Thanks again
 
Geoff
 
 
 
-----------------------------------------------------------------------
 
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()
 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)
 -- 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))
 chart:Activate()
 -- associate the source data with the chart
 excel.ActiveChart:SetSourceData(sourceData)
 excel.ActiveChart.HasTitle = true
 excel.ActiveChart.ChartTitle.Text = graphsheet.Name .. " for " .. datasheet.Name
 return chart
end

 ----------------------------  Main code starts here ------------------------------
 
 -- 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:\mygraph.xls]])
 --~ book:Close()
 
 
 
 
 
 
 
 
 

 

> From: [hidden email]
> To: [hidden email]
> Date: Mon, 4 May 2015 15:45:50 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> Hi Geoff,
>
> > Was that a typo in your reply ? you said "version of Excel, which, in my case, is 2003"
>
> Yes, sorry, you're absolutely right - the version on my end is *2010*. :)
>
> BTW, after lots of digging I seem to have found the Excel 2003 official documentation. It seems somewhat complete but not quite...
>
> Entry point: https://msdn.microsoft.com/en-us/library/aa220733%28v=office.11%29.aspx
> How-to for charts (VBA): https://msdn.microsoft.com/en-us/library/aa203725%28v=office.11%29.aspx
>
> Maybe the changes to the Excel API weren't so large, but my example is just using it in a 2010-only way. I don't know enough about Excel to say what's the case, though setting a title of a chart seems like something that must have been there from day 1.
>
> Good luck with getting it running the way you want.
>
> Tom
>
>

12