Excel automation

steve64

Does anybody have a simple working example using the LuaRT API to create an Excel sheet, populate some cells and save the file with a specified pathname ? Just to have a nicer alternative to CSV...

ruby

app = sys.COM("excel.application")
-- app.visible = true

book = app.workbooks:add()
-- book = app.workbooks:item(1)

sheet = book.worksheets:add()
-- sheet = book.worksheets:item(1)

sheet:range('A1').value = 2
sheet:range('B2:D6').value = "test"

sheet:range('C6').Formula = '= A1 + 6'

book:SaveAs(sys.currentdir.."\\test.xlsx")

book:close()
app = nil

ruby

excel sheet has two useful properties, cells and range:
Worksheet.Cells property (Excel) [Login to see the link]
Worksheet.Range property (Excel) [Login to see the link]

and, actually, they are both functions,

so in excel VBA you can use it like this:

Sub tee()
    Set Sheet = ActiveSheet
     Sheet.Cells(1, 1).Value = 54
     Sheet.Range("C3").Value = "c2"
End Sub

but I do not know how to use cells in luart. only range works.

luart recognize Range as a function, and take Cells as COM constructor.

► sheet.cells
{
  constructor           = method
}

► sheet.range
function

►  sheet:cells(1,2)
>>> attempt to call a COM value (method 'cells')

if cells is recognized as a function, then I can do this:

for row = 1, 4 do
   for column = 2, 5 do
      sheet:cells(row, column).value = 'cell_'..row..'_'..column end end

in Excel Object model
[Login to see the link]

Cells is not an object.

it is a function, which returns a Range object that represent ...

why type of it is COM?

► type(sheet.cells)
"COM"

steve64

thanks for the useful example!

Samir

Ruby,

Try this to understand what's going on :

► print(sheet.Cells)
"COM: <Range>"

This means that the Worksheet.Cells property returns a COM "Range" instance (that's why the type is "COM" for Lua)

The error message explains that we try to call the instance constructor. It's a LuaRT bug that you have found.
I will see what can be done to correctly handle those kind of situations

ruby

ok, never noticed that print() can show real type name.

so cells is in fact a Range object. this is ok

► sheet.cells.address
"$1:$1048576"

► sheet.cells.rows.count
1048576

► sheet.cells.columns.count
16384

this is same as microsoft says at [Login to see the link],
that cells is a Range object that represents all the cells on the worksheet

as microsoft says at [Login to see the link],
if using cells as function, then parameters are used by function item()

Because the default member of Range forwards calls with parameters to the Item property, you can specify the row and column index immediately after the Cells keyword instead of an explicit call to Item.

[Login to see the link]
Syntax:

expression.Item (RowIndex Required, ColumnIndex Optional)

now the problem is at item() function.

it has two parameters, second parameter is optional

but in luart I can only use one parameter, why?
maybe there is a bug in detecting Optional parameter

► sheet.cells:item(2, 3)
>>> COM error: unknown error

► sheet.cells:item(2, 3).address
>>> COM error: unknown error

► sheet.cells:item(2, 3, 4)
>>> COM error: bad parameter count


► sheet.cells:item(16384 + 3).address
"$C$2"

in Excel VBA:

Sub tee()
    Set Sheet = ActiveSheet
    Debug.Print Sheet.Cells(16384 + 3).Address
    Debug.Print Sheet.Cells(2, 3).Address
End Sub

got:
$1:$1048576
$C$2
$C$2

now I can define a similar function:

function cells(range, row, column)    
   if column == nil then return range:item(row) else
   row = row + range.row - 1
   column = column + range.column - 1
   return range.parent.cells:item(range.parent.cells.columns.count * (row - 1)   +  column)       
 end end



► cells(sheet.cells,2,3).address
"$C$2"

► cells(sheet.cells,1,1).address
"$A$1"

► cells(sheet.cells,1,2).address
"$B$1"

► cells(sheet.cells,2,1).address
"$A$2"


► cells(sheet.cells, 2*16384+4).address
"$D$3"

   
► cells(sheet:range("B2:C6"), 1, 1).address
"$B$2"

► cells(sheet:range("B2:C6"), 2, 1).address
"$B$3"

►  cells(sheet:range("B2:C6"), 2, 6).address
"$G$3"

same as VBA:

Sub tee()
    Set Sheet = ActiveSheet
    Debug.Print Sheet.Range("B2:C6").Cells(2, 6).Address
End Sub

get: $G$3

Samir

Great found Ruby !
Still have to find why the optional argument in Item() call is not used

Samir

OK I have found the bug, I can now call Cells:Item(row, column) or Range:Item(row, column) without any error.
It will be available in next LuaRT release.

Thank you Ruby for reporting this

Samir

I have added the possibility, like in VBA, to call items like property to access sub items (for Range, Cells and so on...)

ruby

it is ok now