Ask Your Question
0

Sorting the Sheet tabs in Spreadsheet in Numerical Order

asked 2017-09-12 22:41:06 +0200

Siskar63 gravatar image

How can I sort sheets (tabs) in numerical order.

edit retag flag offensive close merge delete

Comments

Hello @Siskar63,

The Sheets are already sorted in numerical order, ranging from index=0 to the total amount of Sheets minus 1.

Do you want to sort the Sheets based on a number which occurs in their names ?

librebel gravatar imagelibrebel ( 2017-09-13 00:12:42 +0200 )edit

2 Answers

Sort by » oldest newest most voted
1

answered 2017-09-15 16:11:04 +0200

karolus gravatar image

updated 2017-09-15 16:16:44 +0200

Hallo

In Addition to the solutions in the link a solution to sort Sheet-tabs in Natural_sort_order

import re
rex = re.compile(r'(\d+|\D+)')

def natsort(stext):
    return [int(token) if token.isdigit()
            else token
            for token in rex.findall(stext)]

def sort_sheets_by_Name():
    doc = XSCRIPTCONTEXT.getDocument()
    sheets = doc.Sheets
    sheetnames = sheets.ElementNames
    for i, sheetname in enumerate(sorted(sheetnames, key=natsort)):
        sheets.moveByName(sheetname, i)
edit flag offensive delete link more

Comments

This looks even better than the solutions in the link. Can't upvote though -- not enough rep.

Jim K gravatar imageJim K ( 2017-09-15 19:50:57 +0200 )edit
0

answered 2017-09-14 00:13:43 +0200

Jim K gravatar image

updated 2017-09-14 07:32:18 +0200

pierre-yves samyn gravatar image

To sort sheets by name, run this macro from https://forum.openoffice.org/en/forum....

If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
   GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If
Sheets = ThisComponent.Sheets
Names = Sheets.ElementNames
SortedList = BubbleSortList(Names) 'BubbleSortList is a macro in the Tools Library'
for i = 0 to UBound(SortedList)
   Sheets.MoveByName(SortedList(i), i)
next i
edit flag offensive delete link more

Comments

Hi @Jim K my edit is only to add ' (end of comment : no need in basic of course, only here...)

Regards

pierre-yves samyn gravatar imagepierre-yves samyn ( 2017-09-14 07:33:34 +0200 )edit

@pierre-yves samyn: Thank you; I couldn't figure out how to make it look right.

Jim K gravatar imageJim K ( 2017-09-14 11:07:40 +0200 )edit

@Jim K :why not quote the python-solutions from the same Thread

karolus gravatar imagekarolus ( 2017-09-14 11:43:25 +0200 )edit

@karolus: Because it's quicker for a typical user to copy and paste a Basic macro into the IDE than to set up a Python script. If the question was about macro programming, then I may have suggested Python, which is what I use most.

Jim K gravatar imageJim K ( 2017-09-15 19:33:00 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-09-12 22:41:06 +0200

Seen: 46 times

Last updated: Sep 15