Programatically creating a XLS with a drop down cell in Python

Question

I am currently creating XLS files programatically using Python and the XLWT/XLRD libraries.

I've been able to fill the cells with values and insert simple formulas. My challenge is that I want to create a drop down for my users (http://office.microsoft.com/en-us/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx)

Yes, I can easily do this in EXCEL, but I need to have this process automated - I'm using Python and the XLWT/XLRD libraries - I'd rather stick with them, but I'll switch to something else if necessary (I'm in a Ubuntu Linux Server).

score 8 · Answer 1 · answered Feb 23 '15 at 20:48

8

openpyxl also supports drop down cell in XLSX files via data validation: http://openpyxl.readthedocs.org/en/latest/validation.html

answered Feb 23 '15 at 20:48

Karthic Raghupathi

2,011
5
41
57

score 4 · Answer 2 · answered Sep 17 '13 at 15:19

4

Just for the record, xlsxwriter can handle drop down lists on XLSX:

https://xlsxwriter.readthedocs.org/en/latest/example_data_validate.html

answered Sep 17 '13 at 15:19

GermanK

1,676
2
14
21

score 3 · Accepted Answer · answered Aug 13 '12 at 15:55

3

Short answer: you can not - unsupported by XLWT/XLRD.

answered Aug 13 '12 at 15:55

Do you know a library that would support it? I can't use .NEt, but anything Python, Perl, PHP, or Ruby is valid game. – pirhac Aug 13 '12 at 16:05
A `.xlsx` file is just a bunch of `.xml` files zipped together, it is not that hard to generate. Rename a `.xlsx` file to `.zip` and see for yourself. – Paulo Scardine Aug 13 '12 at 16:11
Good idea Paulo - but I have to read the Spreadsheet back in, and XLRD does NOT read XLSX :( – pirhac Aug 13 '12 at 16:26
openpyxl can read and write xlsx: http://packages.python.org/openpyxl/ . I don't know if it supports making dropdown though. – Wichert Akkerman Aug 13 '12 at 20:57
Nothing in Python seems to do what I need - gonna use Perl, it seems to have a more mature XLS plugin. – pirhac Aug 16 '12 at 14:48
@andreas how did you come to that conclusion? – Wolfpack'08 Jan 08 '16 at 18:37

score 0 · Answer 4 · edited Oct 10 '22 at 11:16

worksheet = workbook['SheetName']
dv = DataValidation(type="list", formula1=formula, allow_blank=False, showDropDown=False)
worksheet.add_data_validation(dv)
dv.add(worksheet["D7"]) # cell in worksheet where drop-down will be added

important: set showDropDown=False in DataValidation function formula1 - must have str type with "" added so the following formatting can be used: formula = '"' + formula + '"'

Regards

score -1 · Answer 5 · answered Aug 13 '12 at 20:30

-1

Your best bet is going through the COM.

http://oreilly.com/catalog/pythonwin32/chapter/ch12.html

From there, you have all the options available to you in Office's native Visual Basic -- including (at the very least) the ability to send clicks and keypresses to various objects in the window.

answered Aug 13 '12 at 20:30

Ishpeck

2,001
1
19
21

COM needs windows, I'm using Linux – pirhac Aug 16 '12 at 14:49

Programatically creating a XLS with a drop down cell in Python

5 Answers5