Move a worksheet in a workbook using openpyxl or xl* or xlsxwriter?

Question

I've read the docs for, openpyxl, xlwt, xlrd, xlutils, xlsxwriter. I don't find a way to move a sheet in an Excel workbook. Tests added a worksheet to the ends.

Concretely, I have a calendar of sorts, ['JAN','FEB',...,'DEC'] and I need to replace months as the need arises.

How do you order the sheets in an Excel workbook if you don't move them? Can you insert a sheet after or before a specified sheet?

Only one other post I can find on SO uses win32com and Book.Worksheets.Add(After=Sheet); seems strange none of these modules would have this method.

Default seems to add sheet at end of workbook. I could copy the destination file, until the updated sheet is reached, insert new sheet, and continue original copy to the end. (inspired by this post)

Answer 1

I had two problems. The first problem was inserting a sheet at a given position. The second problem was moving a sheet around. Since I mostly deal with the newer Excel file xlsx, then I would be using openpyxl.

Various sources indicate new sheets are added to the end. I expected I would need to do this each time, and then move the sheet. I asked the question "(How to) move a worksheet..." thinking this was would solve both problems.

Ultimately, the first problem was easy once I finally found an example which showed workbook.create_sheet() method takes an optional index argument to insert a new sheet at a given zero-indexed position. (I really have to learn to look at the code, because the answer was here):

 def create_sheet(self, title=None, index=None):
        """Create a worksheet (at an optional index)
        [...]

Next. Turns out you can move a sheet by reordering the Workbook container _sheets. So I made a little helper func to test the idea:

def neworder(shlist, tpos = 3):
    """Takes a list of ints, and inserts the last int, to tpos location (0-index)"""
    lst = []
    lpos = (len(shlist) - 1)
    print("Before:", [x for x in range(len(shlist))])
    # Just a counter
    for x in range(len(shlist)):
        if x > (tpos - 1) and x != tpos:
            lst.append(x-1)
        elif x == tpos:
            lst.append(lpos)
        else:
            lst.append(x)

    return lst

# Get the sheets in workbook
currentorder = wb.sheetnames
# move the last sheet to location `tpos`
myorder = neworder(currentorder)

>>>Before: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17]
>>>After : [0, 1, 2, 17, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16]

# get each object instance from  wb._sheets, and replace
wb._sheets = [wb._sheets[i] for i in myorder]

The first answer was not hard to spot in the openpyxl documentation once I realized what it did. Just a bit surprised more blogs didn't mention moving sheets around.

Answer 2

Here's what I came up with (using openpyxl)

def move_sheet(wb, from_loc=None, to_loc=None):
    sheets=wb._sheets

    # if no from_loc given, assume last sheet
    if from_loc is None:
        from_loc = len(sheets) - 1

    #if no to_loc given, assume first
    if to_loc is None:
        to_loc = 0

    sheet = sheets.pop(from_loc)
    sheets.insert(to_loc, sheet)

Answer 3

Complement to xtian solution, with option to move the worksheet from any position to any place forward or backwards.

from pathlib import Path
from openpyxl import load_workbook


def neworder(file, fpos, tpos):
    """Takes a list of ints, and inserts the fpos (from position) int, to tpos (to position)"""
    wb = load_workbook(filename=file)
    shlist = wb.sheetnames  # get current order sheets in workbook
    lst = []
    lpos = (len(shlist) - 1) # last position
    if lpos >= fpos > tpos >= 0:  # move from a high to low position
        for x in range(lpos+1):
            if x == tpos:
                lst.append(fpos)
            elif tpos < x <= fpos:
                lst.append(x-1)
            else:
                lst.append(x)
    if lpos >= tpos > fpos >= 0:  # move from a low to high position
        for x in range(lpos+1):
            if x == tpos:
                lst.append(fpos)
            elif fpos <= x < tpos:
                lst.append(x+1)
            else:
                lst.append(x)
    wb._sheets = [wb._sheets[i] for i in lst]  # get each object instance from  wb._sheets, and replace
    wb.save(filename=file)
    return


filex = Path('C:/file.xlsx')
neworder(filex, 83, 12)  # move worksheet in 83rd position to 12th position