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("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 range
>>r=Range.new("Sheet 1!A1:B5")

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

--close the worksheet manually and 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.

--create a range [region is 2x2, from B2 to C3]
>>r=Range.new("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.

 

subrange

subrange({row=, col=}, nrows=, ncols=) → Range

Returns a range defined within the current range object. If the defined range exceeds the boundaries of the current range, then a nil value is returned. (ScienceSuit v1.8.7)

Arguments row and col are top-left coordinates of the defined range and must be greater than zero and must be within the boundaries of the current range. The top-left coordinates of current range is (1, 1). nrows is the number of rows included in the defined range and similarly ncols is the number of columns included in the defined range. If nrows or ncols is assigned a negative number, then all the rows or columns of the current range as of the defined top-left coordinate is included in the defined sub-range.

--rng is the current range object (already has been created)
>>rng
Sheet 1!B4:D10

--We are defining a range within rng
>>rng:subrange({row=1, col=1}, 2, -1)
Sheet 1!B4:D5

 

tostring

tostring() → string

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