Using Lua to work with excel

Question

I plan to learn Lua for my desktop scripts. I want to know if there is any documentation, as well as if the Standard Lib has everything you need.

Answer 1

You should check Lua for Windows - the "included battery" for the Lua scripting language on Windows

http://luaforwindows.luaforge.net/

It includes the LuaCOM library from which you can access the Excel COM object.

Try looking at the LuaCOM documentation, there are Excel examples:

http://www.tecgraf.puc-rio.br/~rcerq/luacom/pub/1.3/luacom-htmldoc/

I just used this for very simplified things. Here is an example to get you started:

-- test.lua require('luacom') excel = luacom.CreateObject("Excel.Application") excel.Visible = true wb = excel.Workbooks:Add() ws = wb.Worksheets(1) for i=1, 20 do ws.Cells(i,1).Value2 = i end 

Answer 2

More sophisticated example code for lua working with excel:

 require "luacom" excel = luacom.CreateObject("Excel.Application") local book = excel.Workbooks:Add() local sheet = book.Worksheets(1) excel.Visible = true for row=1, 30 do for col=1, 30 do sheet.Cells(row, col).Value2 = math.floor(math.random() * 100) end end local range = sheet:Range("A1") for row=1, 30 do for col=1, 30 do local v = sheet.Cells(row, col).Value2 if v > 50 then local cell = range:Offset(row-1, col-1) cell:Select() excel.Selection.Interior.Color = 65535 end end end excel.DisplayAlerts = false excel:Quit() excel = nil 

Another example would be a chart diagram.

 require "luacom" excel = luacom.CreateObject("Excel.Application") local book = excel.Workbooks:Add() local sheet = book.Worksheets(1) excel.Visible = true for row=1, 30 do sheet.Cells(row, 1).Value2 = math.floor(math.random() * 100) end local chart = excel.Charts:Add() chart.ChartType = 4 — xlLine local range = sheet:Range("A1:A30") chart:SetSourceData(range)