Using Document Properties to Track Your Excel Reports

March 29, 2025

Adding Properties

Using pandas and https://xlsxwriter.readthedocs.io/example_doc_properties.html do the following:

with pd.ExcelWriter(report_file,
            engine='xlsxwriter',
            date_format='mmm-yyyy',
            datetime_format='mmm-yyyy') as writer:
sales_summary.to_excel(writer, sheet_name='2018-sales')
workbook = writer.book
workbook.set_properties({
    'category': r'c:\Users\cmoffitt\Documents\notebooks\customer_analysis',
    'title' : '2018 Sales Summary',
    'subject': 'Analysis for Anne Analyst',
    'author': '1-Excel-Properties.ipynb',
    'status': 'Initial draft',
    'comments': 'src_dir: customer_analysis',
    'keywords': 'notebook-generated'
})

Now I will know which project and file created the spreadsheet as well as some other useful data. When I check the properties of the Excel file, I will see all this info under Details.

Using VS Code Snippets

For more details look at https://pbpython.com/vscode-notebooks.html and do the following:

"Write Excel": {
"prefix": "we",
"body": [
"# Excelwriter",
"with pd.ExcelWriter(report_file, engine='xlsxwriter', date_format='mmm-yyyy', datetime_format='mmm-yyyy') as writer:",
"\t$1.to_excel(writer, sheet_name='$2')",
"\tworkbook = writer.book",
"\tworkbook.set_properties({'category': r'$TM_DIRECTORY', 'author': '$TM_FILENAME'})",
],
"description": "Write Excel file"
}

By using the snippet is that I can access VS Code variables such as $TM_DIRECTORY and $TM_FILENAME to pre-populate the current path and name.

How to Generate a list of Tables to Drop List of Named Color for Pandas