Using Lua for working with excel.

Question

I am planning to learn Lua for my desktop scripting needs. I want to know if there is any documentation available and also if there are all the things needed in the Standard Lib.

Thanks a lot in Advance, Animesh

Answer 1

You should check out Lua for Windows -- a 'batteries included environment' 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 some Excel examples in that:

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

I've only ever used this for very simplistic things. Here is a sample 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 complex code example 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, could add a graph chart.

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)