I want to open with Python an .xlsx file that has several columns with Data Validation activated, change a few cells of columns without that attribute and save the file again (or copy it to another) keeping the Data Validation functionality as in the file Of start. openpyxl tells me to discard that function as soon as I open the source file. I can not find any Python package that does it. Do you know any that are useful for this?
Thanks in advance.
There is a library that allows us to do what you expose xlutils :
documentation , download (the xlutils-2.0.0.tar.gz file), here is a tutorial on how to install it
Use the following example.xlsx
Then create a file actualizar.py within the folder is also ejemplo.xlsx - it could be another, but then you have to specify the path in code -
import xlwt
import xlrd
from xlutils.copy import copy
rb = xlrd.open_workbook('ejemplo.xlsx') # abrir archivo excel en este caso está
# en la misma carpeta si no especificar
# la ruta completa
wb = copy(rb) # hacer un copia temporal
# editable del archivo excel abierto
w_sheet = wb.get_sheet(0) # leer la primera dentro de la copia
# temporal editable
w_sheet.write(0, 1, 'miValor') # escribir o modficar el valor de la
# primera fila y segunda columna (B1)
wb.save('ejemplo.xls') # salvar y correr el código
# en macOS Sierra no funciona la extensión xlsx
# por eso lo grabé en extensión xls
The result after running the code is as follows, cell B1 is modified:
Note that the modified file I saved with the xls extension because in the case of the MacOS Sierra and excel 2010 for Mac, it does not allow me to open it as xlsx once modified, please verify this in your operating system and in the excel version that you are using. Do not forget to indicate it in the comments please, because in the Sierra macOS, you do not have all the office services provided by MS and this includes VBA-Macros.