Python Excel : openpyxl - copy/select values in dropdown list

1
votes

I'm trying to automate an excel app using openpyxl in python.

Here is my problem: I'm copying a set of cells from source tab to another set of cells (same number) in destination tab. Destination tab is already present and populated with values, one of which is a drop-down list. And, when I paste the values, it's deleting the drop-down box and pasting the plain values.

Here is what I would like to achieve: When I paste, it should just pick a value in the drop-down. I am sure, the value picked up from source tab is present in the drop-down list.

Here is my code:

    # Copy from src excel to buffer
    for i in range(start_row, end_row + 1):
        for j in range(ord(start_col), ord(end_col)+1):
            cell = chr(j) + str(i)
            buffer[cell] = input_ws[cell].value

    # Find the sheet name from file name of input
    output_ws_name = os.path.basename(file_path).split('_')[0]

    # Output data from buffer to output worksheet for that file
    output_ws = output_wb[output_ws_name]
    for i in range(start_row, end_row + 1):
        for j in range(ord(start_col), ord(end_col)+1):
            cell = chr(j) + str(i)
            output_ws[cell] = buffer[cell]

Thanks for your help :-)

1
pythonexcelopenpyxl

1 Answers