有没有一种使用Python Openpyxl在Excel中编写数据的巧妙方法

Question

我之前问这个问题的目的不同。从那时起，我修改了这个代码。我想把数据写入Excel文件中是分开的行。行数和每行的数据都是用户输入。收割台很好。数据被写入，但格式很尴尬。从A2到D2，第一行数据打印良好。第二行从B3打印到E3。同样，第三行从B4打印到F4，依此类推。我想把第二行从A3打印到D3，而不是从B3打印到E3。我可以对此代码做些什么来获得最佳解决方案？谢谢

from openpyxl import Workbook
#Creates an Excel file from the scratch
wb = Workbook() #object of Workbook type
print(wb.active.title)
print(wb.sheetnames)
wb['Sheet'].title="Report_Amount"
sh1 = wb.active  #Activate the sheet
sh1['A1'].value = "Item" #Writing into the cell
sh1['B1'].value = "Quantity"
sh1['C1'].value = "Price($)"
sh1['D1'].value = "Amount($)"
askrow = input("how many rows of data do you want to add: ")
askrow = int(askrow)
column1 = sh1.max_column
row1 = askrow
print(f'no. of columns : {column1}')
print(f'no. of rows : {row1}')
for k in range (2,row1+1):
inputItem = input("Enter Item Name: ")
sh1.cell(row=k, column=k - 1).value = inputItem
inputQuantity = input("Enter Quantity: ")
inputQuantity = int(inputQuantity)
sh1.cell(row=k, column=k).value = inputQuantity
inputPrice = input("Enter Price: ")
inputPrice = int(inputPrice)
sh1.cell(row=k, column=k + 1).value = inputPrice
sh1.cell(row=k, column=k + 2).value = ((sh1.cell(row=k, column=k).value) * sh1.cell(row=k, column=k + 1).value)
print("file saved")
wb.save("C:\Users\Ricky\Desktop\FirstCreatedPythonExcel1.xlsx")

Answer 1

您的代码中有两个问题一个是for循环中的Column变量，它是一个常量，但您使用了行变量k，另一个是项目数比用户输入少一个。

我已经修复了这两个问题，并试图简化你的代码

from openpyxl import Workbook
wb = Workbook() #object of Workbook type
print(wb.sheetnames)
wb['Sheet'].title="Report_Amount"
sh1 = wb.active  #Activate the sheet
sh1['A1'].value = "Item" #Writing into the cell
sh1['B1'].value = "Quantity"
sh1['C1'].value = "Price($)"
sh1['D1'].value = "Amount($)"
askrow = int(input("how many rows of data do you want to add: "))
for k in range (2,askrow+2):
sh1.cell(row=k, column=1).value = input("Enter Item Name: ")
inputQuantity = int(input("Enter Quantity: "))
sh1.cell(row=k, column=2).value = inputQuantity
inputPrice = float(input("Enter Price: "))
sh1.cell(row=k, column=3).value = inputPrice
sh1.cell(row=k, column=4).value = inputPrice * inputQuantity
wb.save("FirstCreatedPythonExcel1.xlsx")

请将文件的路径改回，现在您还可以为产品价格提供浮点值。