Log in | Register



Worksheet slicing

This sample is saved as Worksheet slicing in the Samples subfolder of the Resolver One folder on your Start Menu. It consists of a table with stock data, yellow area, where you specify how to slice the table, and two tables of which one is created from Post-formulae user code and the other is defined by the formulae in cells.

Slicing

This chapter covers basic information about slicing. You probably want to omit it if you are a pythonista.

Consider lst = [1, 2, 3, 4]. A slice containing items [2, 3] can be obtained with lst[1:3] (slicing is right-exclusive). Omitting first argument means 'from the beginning' and omitting the second argument means 'to the end'. Negative numbers mean counting from the right, so lst[-3:] is last three elements of lst. The third argument that slice takes is the step: range(20)[1::2] returns odd numbers from 1 up to 19 (which is the same as range(1, 20, 2)). lst[::-1] is the same as list(reversed(lst)).

Slicing in Resolver One

Worksheets can be sliced as if they were collections. You can use numbers and the names of header rows and cols in slices. You cannot use negative numbers as either the start or end of a slice (they don't make sense since worksheets are infinite). In formulae, the colons are replaced by arrows, because colons are already used to specify cell ranges, eg. =lst[1 -> 4]. Worksheet slicing is right-exclusive. It may seem little counter-intuitive, especially when you use names from headers, but it's consistent with Python slicing.

Example

Post-formulae user code reads as follows:

start = sheet.B11 or None
end = sheet.B12 or None
step = sheet.B13 or None

for i, col in enumerate(sheet.Cols[start:end:step]):
    source = list(col)[:9]
    destination = (
        pasteInto.Left + i,
        pasteInto.Top,
        pasteInto.Left + i,
        pasteInto.Bottom)
    sheet.FillRange(source, *destination)

Formulae in cells look like =[col[A29 + 1] for i, col in enumerate(sheet.Cols[$B$11->$B$12->$B$13 or None])] and was created using drag-fill.

Back to Sample Spreadsheets.

Comments