saving existing workbook w/ openpyxl resets existing chart formats

Question

I've created a short python script with the openpyxl library to add some data to an existing workbook and then save the changes. The existing workbook has a number of charts, some of which require transparent chart area backgrounds and for all of them the borders have been turned off for design reasons.

When I open the edited file, all the chart styles with respect to borders and fill pattern of the chart area are reset to 'Automatic'. Is there a way to prevent this from happening? Or perhaps a way to access the collection of charts in the workbook object prior to saving and set those properties for each chart programmatically?

Answer 1

OpenPyxl will not work since it will removed chart formatting before saving the file. The reason why existing chart formatting is not kept is related to OpenPyxl's design. OpenPyxl is akin to a duplicate implementation of Excel for Linux and Mac OSes. It is not just calling existing COM and Excel-VBA features. Therefore, it will be difficult to add Excel's extensive chart formatting features to this library in a future update.

Here are some Linux libraries that might help.

xlutils.copy https://xlutils.readthedocs.io/en/latest/copy.html

pyexcelerator (older library)
https://sourceforge.net/projects/pyexcelerator/

See How to programmatically edit Excel sheets?

Yes, pywin32 requires Windows OS or a Windows emulation. It is the best way to work with Excel files since you get full access to all Windows COM objects and all Excel-VBA Worksheets objects (with slight modification at times).

Perhaps you can use pywin32 in a Windows Emulation to separate out the Chart Sheet objects, then use openpyxl to edit the worksheets in Linux, then finally re-assemble the workbook using pywin32? See sample pywin32 code below.

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb0 = excel.Workbooks.Open(r'C:\path\source.xlsx')
excel.Visible = True

# Move Chart 1 to a new Workbook, which becomes the ActiveWorkbook.
wb0.Charts('Chart1').Move()
excel.ActiveWorkbook.SaveAs(r'C:\path\chart1.xlsx')
excel.ActiveWorkbook.Close()

wb0.SaveAs(r'C:\path\no_charts.xlsx')
wb0.Close()
excel.Application.Quit()

Answer 2

Unfortunately openpyxl doesn't edit a file, it reads and re-creates the whole the xlsx file again. If you compare the two files you will see a huge difference

Original File with Charts and No Fill style

$ tree
.
├── [Content_Types].xml
├── _rels
├── docProps
│   ├── app.xml
│   └── core.xml
└── xl
    ├── _rels
    │   └── workbook.xml.rels
    ├── charts
    │   ├── _rels
    │   │   └── chart1.xml.rels
    │   ├── chart1.xml
    │   ├── colors1.xml
    │   └── style1.xml
    ├── drawings
    │   ├── _rels
    │   │   └── drawing1.xml.rels
    │   └── drawing1.xml
    ├── sharedStrings.xml
    ├── styles.xml
    ├── theme
    │   └── theme1.xml
    ├── workbook.xml
    └── worksheets
        ├── _rels
        │   └── sheet1.xml.rels
        └── sheet1.xml

11 directories, 16 files

After being re-written by openpyxl without any changes

$ tree
.
├── [Content_Types].xml
├── _rels
├── docProps
│   ├── app.xml
│   └── core.xml
└── xl
    ├── _rels
    │   └── workbook.xml.rels
    ├── charts
    │   └── chart1.xml
    ├── drawings
    │   ├── _rels
    │   │   └── drawing1.xml.rels
    │   └── drawing1.xml
    ├── styles.xml
    ├── theme
    │   └── theme1.xml
    ├── workbook.xml
    └── worksheets
        ├── _rels
        │   └── sheet1.xml.rels
        └── sheet1.xml

10 directories, 12 files

As you can see colors1.xml and style1.xml doesn't get persisted. This is because openpyxl is not a 100% implementation of the xlsx format

So your options are to use COM Objects (in case of Windows)

Use openpyxl to edit a Excel2007 file (.xlsx) without changing its own styles?