Add custom properties to Excel workbook with openpyxl

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

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