使用openpyx对XLSX工作簿中的选项卡进行排序

Question

我不确定我是否应该或不应该创建一个新的问题，因为接受的答案不起作用。请告知。我是否只回答同一个问题作为答案？这是同一个问题：How can I sort excel sheets/tabs in workbook using openpyxl。我不确定是因为用户问题（通常是）还是因为openpyxl的旧/新版本而不能正常工作。在

这是我的代码：

from openpyxl import Workbook
wb = Workbook()

ws1 = wb.create_sheet("B")
ws2 = wb.create_sheet("A")
ws3 = wb.create_sheet("C")
ws4 = wb.create_sheet("D")

wb._sheets = sorted(wb._sheets) #Error comes up at this line

wb.save('test_tabs.xlsx')

我收到以下错误：

^{pr2}$

因此，我尝试手动对图纸列表进行排序：

>>> type(wb._sheets)
<class 'list'>

>>> wb._sheets
[<Worksheet "Sheet">, <Worksheet "B">, <Worksheet "A">, <Worksheet "C">, <Worksheet "D">]

>>> wb._sheets = [<Worksheet "A">, <Worksheet "B">, <Worksheet "C">, <Worksheet "D">, <Worksheet "Sheet">]
File "<stdin>", line 1

# Manual sort of sheets
wb._sheets = [<Worksheet "A">, <Worksheet "B">, <Worksheet "C">, <Worksheet "D">, <Worksheet "Sheet">]
              ^
SyntaxError: invalid syntax

我注意到列表项没有被'或"括起来，所以我想知道sort是否因此而失败。在

而且，我尝试在列表项中添加'：

wb._sheets = ['<Worksheet "A">', '<Worksheet "B">', '<Worksheet "C">', '<Worksheet "D">', '<Worksheet "Sheet">']

>>> wb._sheets
['<Worksheet "A">', '<Worksheet "B">', '<Worksheet "C">', '<Worksheet  "D">', '<Worksheet "Sheet">']

>>> wb.save('test_tabs2.xlsx')
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
....
AttributeError: 'str' object has no attribute 'sheet_state'

>>> openpyxl.__version__
'2.4.1'

Answer 1

下面的解决方案sorting on ws.title对我有效：

from openpyxl import Workbook

wb = Workbook()

ws1 = wb.create_sheet("B")
ws2 = wb.create_sheet("A")
ws3 = wb.create_sheet("C")
ws4 = wb.create_sheet("D")

wb._sheets.sort(key=lambda ws: ws.title)

wb.save('test_tabs.xlsx')

结果：