我尝试使用python openpyxl在excel中创建多个工作表以在循环中存储不同的值，但它只创建一个工作表.<

Question

我尝试使用python openpyxl在excel中创建多个工作表以在循环中存储不同的值，但它只创建一个工作表。

from datetime import datetime
import openpyxl
from openpyxl.styles import Font
table_name = ["M-1", "M-2", "M-3"]
xltitle = datetime.now()
tit_head = "ALL_MACHINE" + xltitle.strftime("%d_%m_%Y_%H_%M_%S")+".xlsx"
for tab_nam in table_name:
filepath = tit_head
headings = ("NAME", "ID", "EMPLOYEE NAME",
"NUMBER", "START TIME", "STOP TIME")
wb = openpyxl.Workbook()
sheet = wb.create_sheet()
sheet.title = tab_nam
sheet.row_dimensions[1].font = Font(bold=True)
for colno, heading in enumerate(headings, start=1):
sheet.cell(row=1, column=colno).value = heading
wb.save(filepath)

Answer 1

在循环的每次迭代中创建一个新的工作簿，然后保存它，覆盖前一个工作簿。您需要将工作簿创建和文件写入移到循环之外。您还可以移动headings的创建，这样就不需要每次都重新创建它。

像这样:

filepath = tit_head
headings = ("NAME", "ID", "EMPLOYEE NAME",
"NUMBER", "START TIME", "STOP TIME")
wb = openpyxl.Workbook()
for tab_nam in table_name:
sheet = wb.create_sheet()
sheet.title = tab_nam
sheet.row_dimensions[1].font = Font(bold=True)
for colno, heading in enumerate(headings, start=1):
sheet.cell(row=1, column=colno).value = heading
wb.save(filepath)