使用openpyxl或xl *或xlsxwriter在工作簿中移动工作表? [英] Move a worksheet in a workbook using openpyxl or xl* or xlsxwriter?
问题描述
我已阅读 openpyxl , xlwt , xlutils , xlsxwriter .我找不到在 Excel工作簿中移动工作表的方法.测试在末尾添加了一个工作表.
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.
具体来说,我有各种各样的日历,['JAN','FEB',...,'DEC']
,需要时我需要替换月份.
Concretely, I have a calendar of sorts, ['JAN','FEB',...,'DEC']
and I need to replace months as the need arises.
如果不移动,如何在Excel工作簿中订购工作表?您可以在指定工作表的之后或 之前插入工作表吗?
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?
只有另外一个我可以在SO上找到的帖子使用了win32com
和Book.Worksheets.Add(After=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)
推荐答案
我遇到了两个问题.第一个问题是在给定位置插入一张纸.第二个问题是到处移动一张纸.由于我主要处理较新的Excel文件xlsx
,因此我将使用openpyxl.
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.
最终,第一个问题很容易,一旦我终于找到一个示例,该示例显示workbook.create_sheet()
方法采用可选的index
参数在给定的零索引位置插入新工作表. (我真的必须学习查看代码,因为答案在这里):
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)
[...]
下一步.原来,您可以通过重新排序工作簿容器_sheets
来移动工作表.因此,我制作了一个辅助函数来测试该想法:
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]
一旦意识到它的作用,在openpyxl文档中不难发现第一个答案.令我感到有些惊讶的是,更多博客都没有提到移动工作表.
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.
这篇关于使用openpyxl或xl *或xlsxwriter在工作簿中移动工作表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!