DataValidation Drop down list in Excel using Python Scrpt node

KNIME Extensions
1

Hello,

I’m trying to code a Drop down list in Excel through a Python Script node 1=>1, when I run it, it says “Execution succesful” but it doesn’t do anything on the excel file.

Here is the script:

import xlsxwriter
import openpyxl

workbook = xlsxwriter.Workbook(‘Excel file.xlsx’)
worksheet = workbook.add_worksheet()

txt = ‘ACTION’
worksheet.write(‘B1’, txt)
worksheet.data_validation(‘B2’, {‘validate’: ‘list’,
‘source’: [‘Action1’, ‘Action2’]})

Is here somebody with experience with this that can help me?

Thanks in advance,

Alejandro.

2

My guess would be you might have to close the workbook in order to actually write it to disk

workbook.close()

https://xlsxwriter.readthedocs.io/example_demo.html

3

Hi @sanchezea,

you could do the same with KNIME nodes. An Excel Reader a Value Selection Quickform and wrapped in a wrapped metanode this would do the same.

Would this be an option for you as well?
Cheers, Iris

4

I thought about it as well but I added it to the script and it didn’t work

5

Honestly I haven’t used Value Selection node, I tried it but it didn’t make any difference.

Is too much to ask if you post an example of this?

Thanks

6

I attached a workflow that uses your example to create an Excel file via Python. And also one from the example from before. I think you have to provide the Python Script with a valid path (in this case I used the path of the workflow) and you have to close the file at the end.

image

kn_example_python_excel_drop_down.knwf (21.4 KB)

2 Likes
7

This is pretty good! Thank you so much for your example.

Based on this, is it possible to add the drop down list in an Excel file that already exists? For example, an Excel file that comes from another workflow and the final step for having it finished is to add this drop down list at the entire column B.

Thanks in advance.

8

Sorry for the late reply. Yes that should be possible with the openpyxl package.

9

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.