lua-users home
lua-l archive

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

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
 -- associate the source data with the chart
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
local luacom = require "luacom"

local function GetExcel()
 local excel = luacom.GetObject("Excel.Application")
 if not excel then
  excel = luacom.CreateObject("Excel.Application")
 if not excel then
  return false, "Can't instantiate Excel"
 local typeinfo = luacom.GetTypeInfo(excel)
 local typelib = typeinfo:GetTypeLib()
 local enums = typelib:ExportEnumerations()
 return excel, enums

 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))
 -- associate the source data with the chart
 excel.ActiveChart.HasTitle = true
 excel.ActiveChart.ChartTitle.Text = graphsheet.Name .. " for " .. datasheet.Name
 return chart

 ----------------------------  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
 -- 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)
 -- add a graph on sheet 2, taking its data from sheet 1
 AddGraph(sheet2, sheet1)
--~  book:SaveAs([[c:\mygraph.xls]])
 --~ book:Close()

> From:
> To:
> 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:
> How-to for charts (VBA):
> 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