I have an excel sheet. This sheet has questions separated by column, from Column A to Column DW. The sheet is 2 rows long.
Ex:
Row 1 : A | B
Row 2 : How are you? | Who are you?
What I want: Row 3: Good | Guy
Some of the rows have data validation drop-down lists to select from. I want to run a Python Script to choose from this validation list and auto-populate the answers for these questions.
I have reached a point where I can get the drop-down list for each question. Now, all that's left is posting the answer to the cell. How can I do this?
from openpyxl import load_workbook
import random
wb = load_workbook("questions.xlsx")
def get_selections():
list_choices = []
selections = []
for ws in wb:
for dv in ws.data_validations.dataValidation:
list_choices = dv.formula1.split(',')
selections.append(random.choice(list_choices))
return selections
def post_data(selections):
for ws in wb:
for index, value in enumerate(selections):
print(index, value)
ws.cell(row=2, column=1+index, value=value)
wb.save('testing.xlsx')
selections = get_selections()
post = post_data(selections)
Quick run-down of the code:
In get_selections, all data validation type cells are iterated over and their options are read. Ex: Column E, Row 2 would return ['Yes', 'No'].
In post_data, the options are being pasted in the fashion I am expecting, HOWEVER, they are not being pasted underneath the proper column. I discovered that this happens because in get_selections, I am returning selections. selections only got the index for those cells which had data validation.
How can I get all cells in my list of selections?
I don't care what the answer is for those cells which don't have validation.