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