python向excel写入数据openpyxl_使用openpyxl将数据写入现有的Excel工作表

一般来说,我对编码很不满意.

我想要实现的是制作一个脚本,该脚本运行到excel中的雇主列表,每周生成一个新的小时表.通过为每个雇主生成一个空的小时表并重命名它,并在新制作的副本中更改周数和雇主名称.

我没有开始循环,因为我首先想要改变雇主姓名和周数的部分.我已经在互联网上搜索了一些答案,但我无法让代码工作,不断收到错误消息.

所以这是我的代码到目前为止：

import os

import shutil

import time

from openpyxl import load_workbook

#calculate the year and week number

from time import strftime

year = (time.strftime("%Y"))

week = str(int(time.strftime("%W"))+1)

year_week = year + "_" + week

#create weekly houresheets per employer

employer = "Adam"

hsheets_dir = "C:\\test\\"

old_file_name = "blanco.xlsx"

new_file_name = employer + "_" + year_week + ".xlsx"

dest_filename = (hsheets_dir + new_file_name)

shutil.copy2((hsheets_dir + old_file_name), dest_filename)

#change employer name and weeknumber

def insert_xlsx(dest, empl, wk):

#Open an xlsx for reading

print (dest)

wb = load_workbook(filename = dest)

#Get the current Active Sheet

ws = wb.get_sheet_by_name("Auto")

ws.cell(row=1,column=2).value = empl

ws.cell(row=2,column=2).value = wk

wb.save(dest)

insert_xlsx(dest_filename, employer, week_str)

以下是我不断收到的错误消息：

Traceback (most recent call last):

File "G:\ALL\Urenverantwoording\Wekelijks\Genereer_weekstaten.py", line 46, in

insert_xlsx(dest_filename, employer, week)

File "G:\ALL\Urenverantwoording\Wekelijks\Genereer_weekstaten.py", line 44, in insert_xlsx

wb.save(dest)

File "C:\Python34\lib\site-packages\openpyxl\workbook\workbook.py", line 298, in save

save_workbook(self, filename)

File "C:\Python34\lib\site-packages\openpyxl\writer\excel.py", line 198, in save_workbook

writer.save(filename, as_template=as_template)

File "C:\Python34\lib\site-packages\openpyxl\writer\excel.py", line 181, in save

self.write_data(archive, as_template=as_template)

File "C:\Python34\lib\site-packages\openpyxl\writer\excel.py", line 87, in write_data

self._write_worksheets(archive)

File "C:\Python34\lib\site-packages\openpyxl\writer\excel.py", line 114, in _write_worksheets

write_worksheet(sheet, self.workbook.shared_strings,

File "C:\Python34\lib\site-packages\openpyxl\writer\worksheet.py", line 302, in write_worksheet

xf.write(comments)

File "C:\Python34\lib\contextlib.py", line 66, in __exit__

next(self.gen)

File "C:\Python34\lib\site-packages\openpyxl\xml\xmlfile.py", line 51, in element

self._write_element(el)

File "C:\Python34\lib\site-packages\openpyxl\xml\xmlfile.py", line 78, in _write_element

xml = tostring(element)

File "C:\Python34\lib\xml\etree\ElementTree.py", line 1126, in tostring

short_empty_elements=short_empty_elements)

File "C:\Python34\lib\xml\etree\ElementTree.py", line 778, in write

short_empty_elements=short_empty_elements)

File "C:\Python34\lib\xml\etree\ElementTree.py", line 943, in _serialize_xml

short_empty_elements=short_empty_elements)

File "C:\Python34\lib\xml\etree\ElementTree.py", line 943, in _serialize_xml

short_empty_elements=short_empty_elements)

File "C:\Python34\lib\xml\etree\ElementTree.py", line 935, in _serialize_xml

v = _escape_attrib(v)

File "C:\Python34\lib\xml\etree\ElementTree.py", line 1093, in _escape_attrib

_raise_serialization_error(text)

File "C:\Python34\lib\xml\etree\ElementTree.py", line 1059, in _raise_serialization_error

"cannot serialize %r (type %s)" % (text, type(text).__name__)

TypeError: cannot serialize 3 (type int)

有人会把我放在正确的方向吗？

非常感谢