[PYTHON] I want to save a file with "Do not compress images in file" set in OpenPyXL

Introduction

When opening an xlsx file saved using OpenPyXL in the desktop version of Excel, the "Do not compress images in file" checkbox is cleared. If you open a file with the check box selected and save it, it will be turned off without any questions. You can check it from the detailed settings when you open it in Excel, but since it is automatically generated in Python, I would like to omit the manual work. let's do it.

Confirmed environment

How to deal with it for the time being

You can create an xlsx file with no compression settings by writing as follows.

create_xlsx.py

#!/usr/bin/env python

import openpyxl

from openpyxl.workbook.properties import WorkbookProperties
from openpyxl.utils.datetime import CALENDAR_MAC_1904
from openpyxl.workbook._writer import WorkbookWriter

def write_properties_custom(self):
    props = WorkbookProperties()
    if self.wb.code_name is not None:
        props.codeName = self.wb.code_name
    if self.wb.excel_base_date == CALENDAR_MAC_1904:
        props.date1904 = True
    props.autoCompressPictures = False
    self.package.workbookPr = props

WorkbookWriter.write_properties = write_properties_custom


wb = openpyxl.Workbook()
wb.save("Sample.xlsx")

The important thing is where props.autoCompressPictures = False. If you look at the detailed settings of the xlsx file created with this code, you can see that it is checked properly.

I'm forcibly rewriting the library, so it may behave unexpectedly. Please use it with caution, just in case.

Survey result memorandum

Below, I will write the contents and details of the investigation as sloppy. You don't have to read it.

In the first place

OpnePyXL has a module called Workbook Properties (https://openpyxl.readthedocs.io/en/stable/api/openpyxl.workbook.properties.html#openpyxl.workbook.properties.WorkbookProperties). There was a variable called autoCompressPictures here, so I thought it would be reflected if I set True/False to this and save it. However, there is no setting in the properties of the workbook object. However, it was present in the previous module, so I thought it wasn't unconsidered and took a look.

About save operation of OpenPyXL

Save uses the save method of the Workbook class,

• writer/excel.py
• workbook/_writer.py

I am calling write () of the WorkbookProperties class via the two files in. The property is set by write_properties () called from this write (). This time, we are adding processing to this method. I think it's okay to set it to the initial value of WorkbookProperties ().

About xlsx files

More information on workbook properties can be found here (https://docs.microsoft.com/ja-jp/dotnet/api/documentformat.openxml.spreadsheet.workbookproperties?view=openxml-2.8.1). Since autoCompressPictures compresses when it is 1 or True (default is True), Flase is set. By the way, other items in Workbook Properties of OpenPyXL are also described in detail.

About overwriting the library

I referred to this article. Overwrite library processing in Python (https://qiita.com/Asayu123/items/8d8da9911dd0c3296a81)

However, as the original form, I think that it should be set after confirmation with self.wb. *** like the two types of items described. This time it was troublesome, so I forced it to True.