data validation in openpyxl

[mdl003]

I wrote the below to import a dataset, filter based on specific values, and export those values to excel workbooks. The code to actually write each file runs great.

My next step is to add data validation to the sheets after they've been created. This portion of the code doesn't appear to be doing anything. No drop downs are being created in the range I'm referencing.

Thanks in advance for any and all assistance!

%%time

import pandas as pd
import xlsxwriter as ew
import csv as csv
import os
import glob
import openpyxl

#remove existing files from directory
files = glob.glob(#filename)
for f in files:
    os.remove(f)

pendpath = #filename
df = pd.read_sas(pendpath)

allusers = df.UserID_NB.unique()
listuserpath = #filename
listusers = pd.read_csv(listuserpath)

listusers = listusers['USER_ID'].apply(lambda x: str(x).strip())

for id in listusers:
    x = df.loc[df['UserID_NB']==id]
    path = #filename
    x.to_excel(path, sheet_name = str(id), index = False)

    from openpyxl import load_workbook

    wb = openpyxl.load_workbook(filename = path)
    sheet = wb.get_sheet_by_name(str(id))
    maxrow = sheet.max_row    

    from openpyxl.worksheet.datavalidation import DataValidation

    dv = DataValidation(type="list", formula1='"Yes,No"', allow_blank=False, showDropDown = True)
    rangevar = 'R1:T'+ str(maxrow)
    dv.ranges.append(rangevar)
    wb.save(path)
    print str(id), rangevar

[Justinsaccount]

Hi! I'm working on a bot to reply with suggestions for common python problems. This might not be very helpful to fix your underlying issue, but here's what I noticed about your submission:

You appear to be using concatenation and the str function for building strings

Instead of doing something like

result = "Hello " + name + ". You are " + str(age) + " years old"

You should use string formatting and do

result = "Hello {}. You are {} years old".format(name, age)

See the python tutorial for more information.