import pandas as pd
import tkinter.filedialog
import tkinter.simpledialog
import shutil
import xlwings as xw
app = xw.App(visible= False,add_book= False)
sques = ("一病区","二病区")
#病区信息
months = input("输入生成月份") + "月"
#输入需要创建的月份
path = tkinter.filedialog.askopenfilename()
#弹出选择原始文件路径
df_gr = pd.read_excel(path,sheet_name="当前数据库")
#读取原始文件中名为“当前数据库”的表中的信息
#savepath = tkinter.filedialog.askdirectory()
#这里准备自定义存储位置
# 引入循环,创建各个科室的excel文件
for sque in sques:
tar_excel = f"{sque}{months}重点监控指标分析.xlsx"
shutil.copy("下发模板.xlsx", tar_excel) #模板文件确定样式
workbook = app.books.open(tar_excel)
worksheet = workbook.sheets[0]
worksheet['A2'].value = worksheet['A2'].value.replace("m", months) #将模板文件中的字母代替为相应的月份
worksheet['C2'].value = worksheet['C2'].value.replace("s", sque) #将模板文件中的字母代替为相应的科室
workbook.save()
workbook.close()
#将数据库中属于现在科室的数据全部提取出来
df_c = df_gr["病区"] == sque
count = 0
for tiaojian in df_gr["病区"]:
count = count + 1
#提取数据的行数,因为head()函数默认返回值为5,而我们的数据条数大于5,甚至可能是变量
df_copy = df_gr[df_c].head(count)
#将符合要求的数值提取出来
#此处原意是想在sheet3中写入上面取出的数据,但是如果存在sheet3(模板文件中存在sheet3),代码就会报错,求解答
with pd.ExcelWriter(tar_excel, mode='a', engine='openpyxl') as writer:
writer.sheets = {ws.title: ws for ws in writer.book.worksheets}
df_copy.to_excel(writer, sheet_name="Sheet3", index=False)
app.quit()
#错误代码:输入生成月份1
Traceback (most recent call last):
File "D:/xx/xx/xx/Python/newpython/zidingyihanshu.py", line 41, in
df_copy.to_excel(writer, sheet_name="Sheet3", index=False)
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\core\generic.py", line 2291, in to_excel
storage_options=storage_options,
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\formats\excel.py", line 845, in write
freeze_panes=freeze_panes,
File "C:\Users\Administrator\AppData\Local\Programs\Python\Python37\lib\site-packages\pandas\io\excel_openpyxl.py", line 437, in write_cells
f"Sheet '{sheet_name}' already exists and "
ValueError: Sheet 'Sheet3' already exists and if_sheet_exists is set to 'error'.
Process finished with exit code 1