Range Class

A range is a user specified region within a worksheet. It does not have a reserved internal data storage; only reads from and writes to the specified region by the user.

 

Creating Range

Unlike Worksheet, where indices (1,1) refers to A1, for a Range, (1,1) refers to the topleft corner of the specified region.

>> range=Range.new(activeworkbook(), "Sheet1!B1:B5")  -- (1,1) refers to B1

>> range=ws:selection() --Create a range from the selection of the user

 

Important note: If the Worksheet owning the range(s) is closed, the range(s) becomes invalid.

--create a new workbook
>>wb=Workbook.new()

--Create a range using the workbook
>>r=Range.new(wb, "Sheet 1!A1:B5")

--check the range
>>r
Sheet 1!A1:B5

--close the workbook (naturally, Sheet 1 is also closed)
>>wb:close()

--check the range
>>r
userdata --no more access to the range

 

 

Accessing cells

You can read from and write to the region specified by the range inside the Worksheet.

>>wb=std.activeworkbook()

--create a range [region is 2x2, from B2 to C3]
>>r=Range.new(wb, "Sheet 1!B2:C3")

--set B2 value as 10
>>r:set(1, 1, 10)

--read from B2
>>r(1, 1)
10

--set C3 value as "a"
>>r:set(2, 2, "a")

--read from C3
>>r(2, 2)
a


--Attempt to write at index (3,3)
>>r:set(3, 3, "c")
ERROR: Requested row is out of boundaries of the range

 

 

Member functions

 

col

col(i) → Array

Returns the ith column as an Array object.

 

coords

coords() → Lua table, Lua table

Returns the top-left and bottom-right coordinates, with respect to the worksheet. The Lua table has keys r and c to show the location of row and column as integers, respectively.

>>r=Range.new(std.activeworkbook(), "Sheet 1!B2:C5")

>>t1, t2=r:coords()

>>t1
c=2    r=2

>>t2
c=3    r=5

 

get

get(i, j) → number/string

Returns the element at ith row and jth column. If range is a Range variable, then instead of range:get(i,j), range(i,j) can be used as well.

 

parent

parent() → Worksheet

Returns the Worksheet which owns the range.

 

select

select()

Selects the range on the worksheet.

 

set

set(i, j, val)

Sets the value of the cell at ith row and jth column with the value, val.

 

sort

sort(pos, order, casesensitive=false)

Sorts the whole Range according to the selected column position, pos, by given order. If the parameter order="A"  or order="D" then the Range is sorted in ascending or descending order, respectively. By default, the sorting is case-sensitive. Note that, numbers are considered to be smaller than letters in a sort order.

 

tostring

tostring() → string

Returns information on the selection as string, such as in the first example, range:tostring() will return "Sheet1!B1:B5".