Python - create dependent dropdown lists with xlsxwriter

user_14844677 0
Viewed 1485

I am trying to create dependent dropdown lists with the package 'xlsxwriter'.

As an example, let's say that I created an excel file with xlsxwriter for which I expect all entries from column 'B' to be within the following list : ['a', 'b']. I would then want the entries from column 'C' to be within:

  • ['a1', 'a2'] if the corresponding value in column 'B' is 'a'
  • ['b1', 'b2'] if the corresponding value in column 'B' is 'b'

Here is what I have tried:

import numpy as np
import pandas as pd

vals = np.array([['a','a1'], ['b','b2'], ['a','a2']])
df = pd.DataFrame(vals)
n_rows = df.shape[0]

# Write to xlsx file 
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
# Assign workbook and worksheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Creation of unlocked format
unlocked = workbook.add_format({'locked': False})
worksheet.set_column('B:C', None, unlocked)
# Creation of the dropdown menus
worksheet.data_validation('B2:B'+str(1+n_rows), {'validate' : 'list', 'source': ['a', 'b']})
for i in range(n_rows):
    if df.loc[i, 0] == 'a':
        worksheet.data_validation('C'+str(2+i), {'validate' : 'list', 'source': ['a1', 'a2']})
    if df.loc[i, 0] == 'b':
        worksheet.data_validation('C'+str(2+i), {'validate' : 'list', 'source': ['b1', 'b2']})

worksheet.protect()
# Close the workbook
workbook.close()

The problem with this is that whenever I open the generated 'test.xlsx' file and change for example the value of cell 'B2' from 'a' to 'b', the dropdown menu of cell 'C2' stays ['a1', 'a2'].

Any help to solve this problem will be fully appreciated.

Best, clank

xlsxwriter pandas python
1 Answers

You can do it by creating list of choices in the excel, and then create a list by matching 1st element. You can read it more about it here: Excel: Dropdown list dependant on other dropdown list

Column D has choices for a and column B has choices for b.

Your excel will look like this: You can move it to other sheet.

enter image description here

import numpy as np
import pandas as pd

vals = np.array([['a','a1'], ['b','b2'], ['a','a2']])
df = pd.DataFrame(vals)
n_rows = df.shape[0]

# Write to xlsx file 
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
# Assign workbook and worksheet
workbook = writer.book
worksheet = writer.sheets['Sheet1']

# Creation of unlocked format
unlocked = workbook.add_format({'locked': False})
worksheet.set_column('B:C', None, unlocked)
worksheet.write('D1', 'a')
worksheet.write('D2', 'a1')
worksheet.write('D3', 'a2')

worksheet.write('E1', 'b')
worksheet.write('E2', 'b1')
worksheet.write('E3', 'b2')

# Creation of the dropdown menus
worksheet.data_validation('B2:B'+str(1+n_rows), {'validate' : 'list', 'source': ['a', 'b']})
for i in range(n_rows):
    worksheet.data_validation('C'+str(2+i), {'validate' : 'list', 'source': '=INDEX($D$2:$E$3, 0, MATCH($B$'+str(2+i)+', $D$1:$E$1, 0))'})

worksheet.protect()
# Close the workbook
workbook.close()
user_68311090
Related