Worksheet Class

A worksheet is a container for data and also hosts Range objects.

 

1) Creating & Closing

A worksheet is part of the workbook, therefore a statement like the following is not valid:

>> ws=Worksheet.new()

 

However, a worksheet in a workbook can be created using the appendworksheet function.

>>ws=std.appendworksheet()

where the variable wb is of type Workbook and ws is of type worksheet.

 

When a worksheet is closed:

 

 

2) Accessing Data

Understanding the location of the cells is important when accessing the cell in a worksheet . The topleft cell of the worksheet, A1, is (1,1). Similarly, A2 and B2 are (1,2) and (2,2), respectively.

2.1) Reading from Cells

There are two ways the content of a cell can be read:

  1. Array Access[][]:    ws[1][1] will access the content of A1, where ws is of type worksheet.
  2. Function call ():     ws(2,2) will read the content of B2, , where ws is of type worksheet.

 

Example: Consider the worksheet in the following image:

>>wb=std.activeworkbook() 
>>ws=wb:cur()
>>ws(1,1)
1

>>ws[1][1]
1

>>ws(2,2)
4

>>ws[2][2]
4

where wb is of type workbook and ws is of type worksheet.

 

2.2) Writing to Cells

Using the array access we can easily write to a cell. Let's work on the worksheet containing the data from A1 to B2 shown above. After executing the following commands, observe the difference:

>>ws[1][1]=10

>>ws[2][2]=40

>>ws[1][3]="C1"

>>ws[3][1]="A3"

It is seen that with the first command, A1 is changed from 1 to 10 and with the second command B2 is changed from 4 to 40. Third and fourth commands have added entries "C1" and "A3" to cells C1 and A3, respectively.

 

2.3) Formatted Output

Although in section 2.3 it was shown how to write to cells, using this style will use the formatting available in that particular cell. Therefore, when writing to the cells using the following style gives no control over format if you wish to have.

>>ws[1][1]="A1"

In order to use formatted output, instead of string or number, we use a Lua table on the right-hand side. The keys and values are as follows:

Key Value
value Sets the value of the cell, either string or number.
fgcolor, bgcolor (fgcolour, bgcolour) String in the format of "R G B". R, G and B must be in the range of [0, 255]. fgcolor sets the text color whereas bgcolor sets the background color of the cell.
style "italic" makes it italic, "normal" makes it non-italic.
weight "bold" makes it italic, "normal" makes it not-bold.
underline "single" draws a single underline, "none" removes any underlines.

 

>>ws=std.activeworkbook():cur()

>>ws[1][1]={value="A1", fgcolor="255 0 0", bgcolor="0 255 0"}

>>ws[1][2]={value="B1", weight="bold"}

>>ws[2][1]={value="A2", style="italic"}

>>ws[2][2]={value="B2", underline="single"}

The output of the above shown commands is shown below:

 

 

3) Member functions

 

connect

connect(func, func_arg1,...)

where func is a Lua function to be run when the selection has happened. arg1, arg2,..., argn are the arguments of the function func.

 

Connects a Lua function, func, to an event. Currently the only event being handled is the event that is happening when user is selecting cells on the worksheet. This function is especially useful when you want to change the text of a TextCtrl at the end of user's selection which emulates the selection in MS Excel or LibreOffice Calc.

local function GetVariable(txt)
    local ws=std.activeworkbook():cur()
    local range=ws:selection()

    if (range==nil) then
        return
    end

    txt.value=tostring(range)

    if(OwnerDialog~=nil) then
        OwnerDialog.topmost="no"
    end

    end

function txt:button_cb()

    if(OwnerDialog~=nil) then
        OwnerDialog.topmost="yes"
    end

    local ws=std.activeworkbook():cur()

    ws:connect(GetVariable, txt)
end

The above-code can easily become rather exhausting when more than one selection-event-aware text control is to be designed. std.gui.gridtext is designed just for this purpose.

 

 

name

name() → string

Returns the name of the worksheet as string.

 

For the above shown images:

>>ws:name()
Sheet 1

 

 

selection

selection() → Range

Creates a Range class from the current selection.

 

Consider the selection in the following image:

>>rng=ws:selection()

>>rng
Sheet 1!B3:C4

>>rng(1,1)
100