RE: Frustration with Luacom and Excel

Hi Thomas

Ah great, thanks for testing it with Excel 2010. Its good to know it works for both. I will cut Microsoft some slack then, at least their changes to the API from 2003 onwards didn't break backwards compatibility then.

I need a break from wrestling with this, I will do a bit more on it tomorrow, as I need the chart to appear in the data sheet rather than a separate sheet and need to add some other small things. Hopefully that wont be as fiddly.

Geoff

> From: Thomas.Buergel@varian.com
> To: lua-l@lists.lua.org
> Date: Mon, 4 May 2015 18:28:16 +0000
> Subject: RE: Frustration with Luacom and Excel
>
> 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: lua-l-bounces@lists.lua.org [mailto:lua-l-bounces@lists.lua.org] 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: Thomas.Buergel@varian.com
> > To: lua-l@lists.lua.org
> > 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
> >
> >
>