CODEWITHSUNDEEP
pythonexcel
Vasko Vasilev
Vasko Vasilev

Reputation: 574

Lookup the id of an excel cell by checking if value exists using python

I'm trying to edit some information in an excel table using python. I have a list of registrations, pickup dates and delivery dates. I want to check if the registration exists in some of the cells of the excel document and then modify its corresponding pickup and delivery dates. I'm fairly new to python as a whole so this task, although at first seeming simple has proved to be quite challenging. How can I locate the specific table ID by checking if it holds a certain value?

Table for reference:

enter image description here

Upvotes: 0

Views: 1087

Answers (1)

Vasko Vasilev
Vasko Vasilev

Reputation: 574

Alright so I solved the problem myself, posting this answer in case somebody else needs to do something similar.

I used the openpyxl library.

//Create a workbook reference an load it using the openpyxl load_workbook()
  method by passing in the path to the excel workbook

workbook = openpyxl.load_workbook(workbook_path)

//Craete instance of a worksheet and pass the name of the worksheet you want to edit 

current_worksheet = workbook['Sheet1']

//Get the number of rows so that we know how many registrations we need to edit

row_count = current_worksheet.max_row

//Loop through the registrations on the excel sheet, starting from 2 since 1 is the cell
  containing "Car Registration"

for j in range(2, row_count):

//Use the worksheet instance and call the cell() method to point to a specific cell and
  fetch the registration from that cell 

registration_cell = current_worksheet.cell(row = j, column = 1)
current_registration_plate = registration_cell.value


//Compare the fetched registration with the registration we are trying to find. If so 
  create variables which point to two new cells using the same row where the id was found
  (j), add the needed values into them using again .value. and break the loop

if (current_registration_plate == registration_plate):

                pickup_cell = current_worksheet.cell(row = j, column = 2)                   
                pickup_cell.value = pickup_value

                dropoff_cell = current_worksheet.cell(row = j, column = 3)
                dropoff_cell.value = dropoff_value
                break

//Save changes to workbook after finishing the loop

  workbook.save(workbook_path)

Upvotes: 1

Related Questions