我希望能够使用python更新excel文件中的状态,它完成了该过程,但是,当我想再次运行它时,它会恢复到给定的初始信息
是否有办法使excel文件能够在每次运行程序时运行并使用新信息进行更新
现在,程序将恢复为初始导入的NewExcel.py数据帧格式。所有的日期也恢复到原来的日期
当我将新数据添加到数据框中时,它将保存新信息,但当我想一次更新一个单元格时,它不会保存新信息
下面是我正在使用的代码,非常感谢您的帮助
import pandas as pd
from openpyxl import Workbook, load_workbook
import datetime
import NewExcel
#gets the dataframe from the NewExcel file.
Workbook_from_file = load_workbook("Canidate2.xlsx")
file = pd.ExcelFile("CanidateForm2.xlsx")
msg = ''
while msg != 'quit':
Option_Chart = input ("Welcome! Which agency would you like to see?: \n1: View all canidates\n2: View a specific Canidate Form\nQuit\n")
#Shows Every canidate as if it was in the excel file.
test_number = int(Option_Chart)
if test_number == 1:
sheet = file.parse('Sheet1')
print(sheet)
continue
elif test_number == 2:
Option = input("Which sheet would you like to view? \n1: Sheet1 \n2: Sheet2 etc...")
test = int(Option)
if test == 1:
sheet = file.parse('Sheet1')
print (sheet)
inputs = input ("Would you wish to \n1. Create a new Canidate\n2. View all statuses\n3.Change a canidates status\n4.Change the stage of a canidate\n")
test_number3 = int(inputs)
if test_number3 == 1:
i = input ("How many peopole are already in the sheet?")
num = int(i)
num = num + 1
Name = input("What is the canidates name?\n")
JobPosition = input("What is their job position?\n")
PhoneScreen = input("What date was the phone screen taken place?\n")
Status = input("What status is the client on? either in process or closed\n")
Rejected = input("Has the client been rejected?\n")
Stage = input("What stage is the client on? \nClient interview \nPhone Screen \nOnsite Interview \nRejected\n")
SalaryRange = input("What is the salary range the client desires?\n")
ProvidedDocuments = input("What documentation has been provided?\n")
Comments = input("Any comments?\n")
LND = input("Last notification Date?\n")
df2 = {'#': num, 'Canidate': Name, 'Job Position': JobPosition, 'Phone Screen': PhoneScreen, 'Status': Status, 'Rejected': Rejected, 'Stage': Stage, 'Salary Range': SalaryRange, 'Provided Documents': ProvidedDocuments, 'Comments': Comments, 'Last notification Date': LND}
df = df.append(df2, ignore_index=True)
df.to_excel('CanidateForm2.xlsx')
break
elif test_number3 == 2:
print (sheet.Name + " Status: " + sheet.Status)
break
#Changing a status from IN PROCESS to CLOSED and updates the last notification date
elif test_number3 == 3:
df = pd.concat([NewExcel.df], ignore_index=True)
print (sheet.Name + " Status: " + sheet.Status)
num = input("Which name do you want to change?(choose a number)\n")
number = int(num)
writer = pd.ExcelWriter('CanidateForm2.xlsx', engine='openpyxl')
#Need to load the workbook, or it overwrites all data on the sheet.
writer.book = load_workbook('CanidateForm2.xlsx')
#Evidently need to load the sheet names in order to write to an existing sheet
writer.sheets = dict((ws.title, ws) for ws in writer.book.worksheets)
if number in [1, 2, 3, 4, 5, 6, 7, 8, 9, 10 ,11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21]:
nStatus = input("What is the new Status\n")
df.loc[df['#']==number, 'Status'] = nStatus
df.loc[df['#']==number, 'Status'].to_excel(writer, sheet_name='Sheet1', index=False, header=None, startrow=number-1, startcol=8)
writer.save()
#updates the Last notification date when the status is updated as well to current date
nDate = datetime.datetime.now()
df.loc[df['#']==number, 'LastNotificationDate'] = nDate
df.loc[df['#']==number, 'LastNotificationDate'].to_excel(writer, sheet_name='Sheet1', index=False, header=None, startrow=number-1, startcol=13)
writer.save()
print (sheet.Name + " Status: " + sheet.Status)
print (sheet.LastNotificationDate)
break
else:
print("Wrong input please try again")
breakwriter.save()
continue
writer.save()
continue
writer.save()
msg.lower = input("Would you wish to continue?")
您是否尝试过使用以下方法
wb=工作簿() wb.save('balances.xlsx')
我看到您正在使用save(),但没有工作簿/工作表作为参数。 见:https://openpyxl.readthedocs.io/en/stable/tutorial.html
相关问题 更多 >
编程相关推荐