如何使用给定的新信息保存和更新excel文件

2024-03-19 07:01:15 发布

您现在位置:Python中文网/ 问答频道 /正文

我希望能够使用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?")

Tags: thetointestnumberdfinputis