Is there a neat way to write data in Excel using Python Openpyxl?

Question

I asked this question earlier for a different purpose. Since then I modified this code. I want to write data into an Excel file is separate rows. No. of rows and the data for each row are user inputs. Header is fine. Data gets written but the format is awkward. First row of data gets printed well from A2 to D2. Second row gets printed from B3 to E3. Likewise third row gets printed from B4 to F4 so on. I would like to get the second row printed from A3 to D3 and not from B3 to E3. What can I do to this code to get an optimal solution ? Thanks.

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

There are two problems in your code One is the Column variable inside the for loop, it's a constant but you used the row variable k and the other one is the number of items is one lesser than user input.

I have fixed both and tried to simplify your code

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")

Please change the path to the file back and now you can also give floating-point to the product price.