Flip Calc Sheet Rows

[postrot]

How can I change the following python code to flip all rows except the header row on the current calc sheet without selecting it? I understand the last row is at gotoEndOfUsedArea:

def reverseDataArray():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.getCurrentSelection()
    a = sel.getDataArray()
    sel.setDataArray(a[::-1])

[karolus]

def flip_Data(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cursor = sheet.createCursorByRange(sheet["A2"])
    cursor.gotoEndOfUsedArea(True)
    cursor.DataArray = reversed(cursor.DataArray)

[postrot]

A couple of queries:

1. Your approach defines the cursor by range then extends it toEndOfUsedArea. Is there a better way of defining used range when combining with existing CurrentSelection functionality so sheet.createCursorByRange(range) can be called once?

def flip_Data(*_, header_rows=1):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    range = doc.CurrentSelection
    if len(range.DataArray) > 1:
        cursor = sheet.createCursorByRange(range)
    else:
        cursor = sheet.createCursorByRange(sheet["A" + str(header_rows + 1)])
        cursor.gotoEndOfUsedArea(True)
    cursor.DataArray = reversed(cursor.DataArray)

2. Can you link DataArray documentation?

[karolus]

@ 1. I would hope $User is clever enough to do select A1 OR A2 (dependend on first row should included or not ) and hit ctrl ⇑ END
And then:

def flip_Data(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    selection = doc.CurrentSelection
    selection.DataArray = reversed( selection.DataArray)

@ 2. mri.oxt is your guide.

[postrot]

Thanks. Unfortunately, I don’t share your hope but I expect $User could select the tool. The query was just about tidying up the code:

# coding: utf-8
from __future__ import unicode_literals
import re


def flip_Data(*_, header_rows=1):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    range = doc.CurrentSelection
    if len(range.DataArray) <= 1:
        cursor = sheet.createCursor()
        cursor.gotoEndOfUsedArea(True)
        range = sheet[
            re.sub(r"\.\$?[a-zA-Z]{1,3}\$?\d{1,7}:", r".A2:", cursor.AbsoluteName)
        ]
    cursor = sheet.createCursorByRange(range)
    cursor.DataArray = reversed(cursor.DataArray)

[karolus]

»range« is a python-builtin, dont use it otherwise!
there is also no need for:

    cursor = sheet.createCursorByRange(range)
    cursor.DataArray = reversed(cursor.DataArray)

just use:

    range.DataArray = reversed( range.DataArray)

I would replace your »creative« solution with:

def flip_Data(*_, header_rows=1):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cell_range = doc.CurrentSelection
    if len(cell_range.DataArray) <= 1:
        cursor = sheet.createCursor()
        cursor.gotoEndOfUsedArea(True)
        cell_range = sheet[ f"A2:{cursor.AbsoluteName.rsplit(':',1)[1]}"]    
    cell_range.DataArray = reversed(cell_range.DataArray)

or just slice cursor :

def flip_Data(*_, header_rows=1):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cell_range = doc.CurrentSelection
    if len(cell_range.DataArray) <= 1:
        cursor = sheet.createCursor()
        cursor.gotoEndOfUsedArea(True)
        cursor[1:,:].DataArray = reversed(cursor[1:,:].DataArray)
        return
    cell_range.DataArray = reversed(cell_range.DataArray)

[postrot]

lol, I could work for ClownStrike! Thank you for those comments, that has tidied it up:

def flip_Data(*_, header_rows=1):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentController.ActiveSheet
    cell_range = doc.CurrentSelection
    if len(cell_range.DataArray) > 1:
        cursor = sheet.createCursorByRange(cell_range)
    else:
        cursor = sheet.createCursorByRange(sheet[f"A{str(header_rows + 1)}"])
        cursor.gotoEndOfUsedArea(True)
    cursor.setDataArray(reversed(cursor.DataArray))

Note: This fails ungracefully with multiple selections