I can't seem to get my openpyxl code working

Question

My current challenge is to write some code which does the following:

Takes user input which will be designated as batchNumber
Takes user input which will be designated as batchLocation
Look through each row in a .xlsx file named customerData
Match the user input batchNumber to data in the first column
Write the batchLocation to a cell in the 3rd column adjacent to batchNumber

I think this is a lot easier than I'm making it out to be but I seem to be going round in circles.

The excel document is structured as...

Batch Number Product Type Location

1234 Guitar *Blank*

2345 Drums *Blank*

I've tried altering the ATBS code from the excel section but can't seem to get it to work with user input instead of data from a dictionary. The code runs without errors but the changes aren't made unless the data appears in the very first row? I've also tried using iter_rows() but haven't had much success.

import openpyxl

wb = openpyxl.load_workbook('customerData.xlsx')
sheet = wb['SalesOrders']

# User Input of data 
CUSTOMER_UPDATE = {}
batchNumber = str(input('Please scan the batch number: '))
batchLocation = input('Please scan the location: ')
CUSTOMER_UPDATE[batchNumber] = batchLocation

# Loop through the rows and update the location
for rowNum in range(2, sheet.max_row): #Used to skip the first row as it is only a header.
    productName = sheet.cell(row=rowNum, column=1).value

if productName in CUSTOMER_UPDATE:
    sheet.cell(row=rowNum, column=3).value = CUSTOMER_UPDATE[productName]

wb.save('customerDataCopy.xlsx')

Haven't looked much into your code yet, but it looks like the `if` statement should be inside the `for` loop? Otherwise it is only going to act on your last row. — Pablo, Feb 11 '20 at 21:57
Dammit! You're right, it's now working. I feel like an idiot. — Norcofox, Feb 11 '20 at 22:03

score 0 · Answer 1 · edited Feb 12 '20 at 00:24

I think you want to test the productName for every row, so you want to indent the if under the for loop, like this:

# Loop through the rows and update the location
for rowNum in range(2, sheet.max_row): #Used to skip the first row as it is only a header.
    productName = sheet.cell(row=rowNum, column=1).value
    if productName in CUSTOMER_UPDATE:
        sheet.cell(row=rowNum, column=3).value = CUSTOMER_UPDATE[productName]

I can't seem to get my openpyxl code working

1 Answers1