如何在csv或xlsx电子表格中通过input()将用户输入excel电子表格

Question

到目前为止，我已经能够访问python中的csv和xlsx文件，但我不确定如何输入用户输入input()以将数据添加到电子表格中。

我还希望这个input()每天只能输入一次，但适用于我的电子表格中的不同列。(这是一个单独的问题(

这是我到目前为止的代码，第一个是csv，第二个是xlsx，我不需要两者，只要两者都可以：

# writing to a CSV file
import csv
def main():
filename = "EdProjDBeg.csv"
header = ("Ans1", "Ans2", "Ans3")
data = [(0, 0, 0)]
writer(header, data, filename, "write")
updater(filename)

def writer(header, data, filename, option):
with open(filename, "w", newline = "") as csvfile:
if option == "write":
clidata = csv.writer(csvfile)
clidata.writerow(header)
for x in data:
clidata.writerow(x)
elif option == "update":
writer = csv.DictWriter(csvfile, fieldnames = header)
writer.writeheader()
writer.writerows(data)
else:
print("Option is not known")

# Updating the CSV files with new data
def updater(filename):
with open(filename, newline= "") as file:
readData = [row for row in csv.DictReader(file)]
readData[0]['Ans2'] = 0
readHeader = readData[0].keys()
writer(readHeader, readData, filename, "update")

# Reading and updating xlsx files
import openpyxl
theFile = openpyxl.load_workbook(r'C:Usersjoe_hOneDriveDocumentsData Analysis STUDYINGExcelEdProjDBeg.xlsx')
print(theFile.sheetnames)
currentsheet = theFile['Customer1']
print(currentsheet['B3'].value)

wb = openpyxl.load_workbook(r'C:Usersjoe_hOneDriveDocumentsData Analysis STUDYINGExcelEdProjDBeg.xlsx')
ws = wb.active
i = 0
cell_val = ''
# Finds which row is blank first
while cell_val != '':
cell_val = ws['A' + i].value
i += 1
# Modify Sheet, Starting With Row i
wb.save(r'C:Usersjoe_hOneDriveDocumentsData Analysis STUDYINGExcelEdProjDBeg.xlsx')
x = input('Prompt: ')

Answer 1

这适用于将数据输入xlsx文件。

只需使用：

ws['A1'] = "data"

输入单元格A1

请参阅下面的代码，例如使用您的原始代码：

wb = openpyxl.load_workbook('sample.xlsx')
print(wb.sheetnames)
currentsheet = wb['Sheet']

ws = currentsheet
#ws = wb.active <-- defaults to first sheet
i = 0
cell_val = ''
# Finds which row is blank first
while cell_val != None:
i += 1
cell_val = ws['A' + str(i)].value
print(cell_val)
x = input('Prompt: ')
#sets A column of first blank row to be user input
ws['A' + str(i)] = x
#saves spreadsheet
wb.save("sample.xlsx")

此外，刚刚在上面的代码中对您的原始while循环进行了一些编辑：

1. 当单元格为空时，返回"None">
2. A1是左边的第一个单元格，而不是A0(移动到单元格的查找值上方i+=1(
3. 访问单元格时已将变量"i"转换为字符串

请参阅https://openpyxl.readthedocs.io/en/stable/完整的文件