RE: Frustration with Luacom and Excel

[Thomas Buergel <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: 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
> 
>