With VBA, I can edit arbitrary workbook metadata like so, and it will be reflected on SharePoint:
With ThisWorkbook .ContentTypeProperties("Property A") = 1 .ContentTypeProperties("Prop B") = “Something” End With
Now, I am hoping to do the same with openpyxl
I can do this for properties without spaces:
wb.properties.title = 'test'
but properties with spaces won’t work–I try this and the script runs, but nothing shows on SharePoint:
setattr(wb.properties, 'Project Title', 'hello') wb.properties.__dict__['Project Number'] = '12'
This can be done with xlsxwriter
import xlsxwriter workbook = xlsxwriter.Workbook(wb_path) workbook.set_custom_property('Project Title', 'hello') workbook.close()
but it will create a new workbook…
According to this https://foss.heptapod.net/openpyxl/openpyxl/-/merge_requests/384/diffs?commit_id=e00ce36aa92ae4fffa7014e460a8999681d73b8b
I could simply do
wb.custom_doc_props.add(k, v)
, but I’m getting no attribute custom_doc_props
with the latest version (I believe), 3.0.10
.
I installed the 3.2.0b1 version with pip, and now get AttributeError: 'CustomDocumentPropertyList' object has no attribute 'add'
. I guess the method isn’t fully implemented yet
Advertisement
Answer
This works with version 3.1.0 of openpyxl
. You can download via pip
with
python -m pip install https://foss.heptapod.net/openpyxl/openpyxl/-/archive/branch/3.1/openpyxl-branch-3.1.zip
and assign properties like so
from openpyxl.packaging.custom import ( BoolProperty, DateTimeProperty, FloatProperty, IntProperty, LinkProperty, StringProperty, CustomPropertyList, ) props = CustomPropertyList() props.append(StringProperty(name='hello world', value='foo bar')) wb.custom_doc_props = props wb.save(...)
Data is preserved on SharePoint. More info here: https://foss.heptapod.net/openpyxl/openpyxl/-/blob/branch/3.1/doc/workbook_custom_doc_props.rst