问带xlwing的嵌套下降

EN

import xlsxwriter

# open workbook
workbook = xlsxwriter.Workbook('nested_drop_downs_with_xlsxwriter.xlsx')

# data
countries = ['Mexico', 'USA', 'Canada']
mexican_cities = ['Morelia', 'Cancun', 'Puebla']
usa_cities = ['Chicago', 'Florida', 'Boston']
canada_cities = ['Montreal', 'Toronto', 'Vancouver']

# add data to workbook
worksheet = workbook.add_worksheet("sheet1")
worksheet.write_column(0, 0, countries)
worksheet.write(0, 1, countries[0])
worksheet.write_column(1, 1, mexican_cities)
worksheet.write(0, 2, countries[1])
worksheet.write_column(1, 2, usa_cities)
worksheet.write(0, 3, countries[2])
worksheet.write_column(1, 3, canada_cities)

# name regions
workbook.define_name('Mexico', '=sheet1!$B2:$B4')
workbook.define_name('USA', '=sheet1!$C2:$C4')
workbook.define_name('Canada', '=sheet1!$D2:$D4')

# 
worksheet.data_validation('A10', {'validate': 'list', 'source': '=sheet1!$A$1:$A$3'})
worksheet.data_validation('B10', {'validate': 'list', 'source': '=INDIRECT($A$10)'})


workbook.close()

def main():
    wb = xw.Book.caller()
    sheet = wb.sheets('Sheet1')

    # data
    countries = ['Mexico', 'USA', 'Canada']
    mexican_cities = ['Morelia', 'Cancun', 'Puebla']
    usa_cities = ['Chicago', 'Florida', 'Boston']
    canada_cities = ['Montreal', 'Toronto', 'Vancouver']

    # add data to workbook
    sheet.range('A1:A3').options(transpose=True).value = countries
    sheet.range('B1').value = countries[0]
    sheet.range('B2').options(transpose=True).value= mexican_cities
    sheet.range('C1').value = countries[1]
    sheet.range('C2').options(transpose=True).value = usa_cities
    sheet.range('D1').value = countries[2]
    sheet.range('D2').options(transpose=True).value = canada_cities

    # name regions   <-------- naming regions with a dollar sign was the fix!
    sheet.range('$B$2:$B$4').api.name.set('Mexico')
    sheet.range('$C$2:$C$4').api.name.set('USA')
    sheet.range('$D$2:$D$4').api.name.set('Canada')

    sheet.range('A10').api.validation.delete()
    sheet.range('A10').api.validation.add_data_validation(type=3, formula1='=Sheet1!$A$1:$A$3') 
    sheet.range('B10').api.validation.delete()
    sheet.range('B10').api.validation.add_data_validation(type=3, formula1='=INDIRECT($A$10)') 

if __name__ == "__main__":
    xw.Book("demo1.xlsm").set_mock_caller()
    main()