Problems with charts in openpyxl 3.1.4 and later
John Skilleter
- Title is too small and is overlaid on the chart instead of being above it
- X and Y axes are missing as are the axis data labels
- X and Y axis labels are overlaid on the chart instead of being to the side/below
- Chart frame has rounded corners rather than square
- Python 3.10.12
- Code run in a virtual environment with openpyxl the only thing in the requirements.txt file with version set to either '==3.1.3 ' or '==3.1.4'
- Excel from Microsoft 365 Enterprise v2404
Charlie Clark
On 5 Jul 2024, at 12:47, John Skilleter wrote:
A number of things seem to have broken with regard to chart layout in
version 3.1.4 (and later).
There are no relevant changes in the code, as you can confirm by comparing the generated XML.
Charlie
--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226
John Skilleter
< <Application>Microsoft Excel</Application>
< <AppVersion>3.1</AppVersion>
---
> <Application>Microsoft Excel Compatible / Openpyxl</Application>
> <AppVersion>3.1.4</AppVersion>
If I unpack the xlsx file generated by 3.1.4 and just remove " Compatible / Openpyxl" from app.xml then re-pack it to an xlsx file Excel displays the charts correctly. The 'AppVersion' change seems to be irrelevant.
Charlie Clark
> If I unpack the xlsx file generated by 3.1.4 and just remove " Compatible /
> Openpyxl" from app.xml then re-pack it to an xlsx file Excel displays the
> charts correctly. The 'AppVersion' change seems to be irrelevant.
This element conforms to the specification, so it sounds like a bug in MS Excel.
John Skilleter
Charlie Clark
On 5 Jul 2024, at 16:29, John Skilleter wrote:
I agree that it is an Excel bug - LibreOffice Calc is happy with either
file.Unfortunately, my managers like Excel, warts and all.
If it's still the case with 3.1.5, I'd suggest you monkey patch the code. LibreOffice does something similar, so it should be possible to include something with the full version number, which is supposed to make debugging easier. But at the moment I don't the time to try out all the possibilities and I'd rather pull all my teeth out. But it is possible that 3.1.5 actually fixes it. If not, maybe you're
I seem to remember seeing something like this years ago, which is why I left things as they were. But it really is useful to be able know exactly which version is in use. And it really is a completely stupid bug by Microsoft. Fortunately, the Office team has actually been fairly receptive to bug reports: I have the impression that those working there now hate some of the design and implementation decisions taken years ago. Overloading CustomFilter's operators with a secret code springs to mind and we know they have no formal validators for the various parts of the specification done in BNF.
I'd, and I think the world, would be much happier if we had a clear definition of the application name, though it's actually irrelevant as the stuff in [ContentTypes].xml specifies what the documents are. The AppVersion could, in theory, denote a specific version of Excel, except that, of course, OOXML shouldn't differentiate between MS Excel, OpenOffice, Openpyxl or John's Funky Spreadsheet…
Charlie Clark
On 5 Jul 2024, at 17:27, Charlie Clark wrote:
I seem to remember seeing something like this years ago, which is why I left things as they were.
I've just spent some time playing with various combinations here.
LibreOffice uses the following:
<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">
<Template />
<TotalTime>0</TotalTime>
<Application>LibreOffice/7.5.8.2$MacOSX_X86_64 LibreOffice_project/f718d63693263970429a68f568db6046aaa9df01</Application>
<AppVersion>15.0000</AppVersion>
</Properties>
It would be great if we could use this. But you can't. Comparison with the charts is difficult because of the different namespace prefixing.
But things get more interesting if you play around with combinations with Microsoft Excel.
The following works fine for me on Excel for Mac 2019 and Excel for Windows 2016
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties">
<Application>Microsoft Excel</Application>
<AppVersion>3.1.6</AppVersion>
</Properties>
This causes problems as soon as the Application name changes and we dropped the ".6" recently because this was apparently causing problems with some version of Excel.
However, things get even more interesting once you start incrementing the version number. I can go all the way up to 12.X.Y and Excel is happy but anything beyond 13 and the labelling in charts goes to shit..
You can do this easily with the xml by unpacking and playing around with the source, zipping the resource and opening Excel.
Eg.
zip ../fixed.xlsx -r * && open ../fixed.xlsx
This suggests two things: Microsoft keeps a database of Office releases in the executable; or it has some heuristics; or both. Anyone who's done any hacking will know that this sounds like an excellent place to start looking for an unhandled error… though I'd hoped they'd be dealt with by something else by now.
In any case, it suggests that the specification is simply incorrect and should be amended. And Excel's inconsistent behaviour is obviously buggy. I could take this up with the Working Group but they're not very happy with me at the moment with my unfinished bug reports: these are managed using word files… To their credit Microsoft does continue to engage in the process but it is extremely slow.
If people could do some more testing as to what works with particular Office versions, I'd be happy to live with X.Y.Z in the version as I don't envisage going all the way up to eleven, let alone thirteen… even if there is a Spinal Tap sequel! :-D
Charlie Clark
On 9 Jul 2024, at 18:25, Charlie Clark wrote:
If people could do some more testing as to what works with particular Office versions, I'd be happy to live with X.Y.Z in the version as I don't envisage going all the way up to eleven, let alone thirteen… even if there is a Spinal Tap sequel! :-D
I've spent some more time looking at the chart stuff in detail and have come across some interesting defaults which indicate that the problem really doesn't have anything to do with the version but some obviously very shitty coding in MS Office.
The most important thing is that the version seems to affect how Excel handles the namespace in the chart: as long as the c:prefix is used, everything remains as expected. This is insane, because, in XML, there's no difference between the default namespace and a prefixed one!
This seems to have some additional side-effects:
- the size of text in the title: Excel defaults to 18pt (at least on Mac)
- the style used: Excel likes to use later extensions that allow for stylesheets for charts with 118 from Office 14
- overlay explicitly set to False for the title and the legend
It's easy to fix the code for charts so that what Openpyxl generates looks like Excel's defaults, and we can then keep the important meta-information, as we like.
I'll see if I can get a bug report submitted to Microsoft as there really is quite a lot for them to think about.
Paul Wormer
antoine jarrige
or you patch the package itself, edit file "...\Lib\site-packages\openpyxl\packaging\extended.py" line ~129:
replace:
self.Application = f"Microsoft Excel Compatible / Openpyxl {_version_}"with:
self.Application = "Microsoft Excel"Paul Wormer
I took the second option suggested by Antoine (edit of "extended.py") and it solved my problem. Thank you.
Yaakov W.
#!/usr/bin/env python3
# create_revu_model.py
import openpyxl
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
def main():
wb = Workbook()
# --------------------------------------------------------------------------------------
# SHEET 1: Assumptions
# --------------------------------------------------------------------------------------
ws1 = wb.active
ws1.title = "Assumptions"
assumptions_data = [
["PARAMETER", "VALUE", "NOTES"],
["Seed Funding", "$500,000", "Raised in Year 1 for platform build-out"],
["Year 1 Revenue", "$150,000", "From brand deals and data insights"],
["Year 2 Revenue", "$1,000,000", "Scaling brand, affiliate, data revenues"],
["Year 3 Revenue", "$5,000,000", "Rapid growth from multiple streams"],
["Year 4 Revenue", "$15,000,000", "Accelerated adoption and brand trust"],
["Year 5 Revenue", "$45,000,000", "Global expansion, strong pipeline"],
["COGS % (Year 1)", "25%", "High initial costs for hosting & AI"],
["COGS % (Year 5)", "15%", "Economies of scale reduce % over time"],
["S&M Growth Focus", "High early", "Heavier marketing in first 2-3 years"],
["R&D Focus", "Core AI", "Continuous investment, % of rev declines over time"],
["G&A Growth", "Gradual", "Modest expansions + overhead"],
["Affiliate Commission Rate", "5%", "Baseline commission from brand links"],
["Conversion Rate Increase", "1% -> 2%", "Over time, user trust & community adoption grow"],
]
for row in assumptions_data:
ws1.append(row)
# Auto-size columns
for col in ws1.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
ws1.column_dimensions[column].width = max_length + 2
# --------------------------------------------------------------------------------------
# SHEET 2: Detailed Drivers (Key Growth Table)
# --------------------------------------------------------------------------------------
ws2 = wb.create_sheet("Detailed Drivers")
headers = [
"Year",
"Brand Partners",
"Avg. Annual Brand Contract",
"Brand Revenue",
"Data Clients",
"Avg. Annual Data Subscription",
"Data Insights Revenue",
"Monthly Active Users (MAU)",
"Affiliate Conversion Rate",
"Avg. Order Value",
"Affiliate Commission Rate",
"Annual Affiliate Revenue",
"Other Revenue",
"Total Revenue"
]
ws2.append(headers)
growth_table = [
[1, 5, 20000, 100000, 2, 15000, 30000, 50000, "1%", 30, "5%", 15000, 5000, 150000],
[2, 20, 30000, 600000, 10, 20000, 200000, 200000, "1.5%",35, "5%", 150000, 50000, 1000000],
[3, 60, 50000, 3000000, 50, 20000, 1000000, 1000000, "2%", 40, "5%", 500000, 500000,5000000],
[4, 100, 90000, 9000000, 100, 30000, 3000000, 3000000, "2%", 45, "5%", 1500000,1500000,15000000],
[5, 180, 150000,27000000, 300, 30000, 9000000, 7500000, "2%", 50, "5%", 4500000,1500000,45000000],
]
for row in growth_table:
ws2.append(row)
for col in ws2.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
try:
if len(str(cell.value)) > max_length:
max_length = len(str(cell.value))
except:
pass
ws2.column_dimensions[column].width = max_length + 2
# --------------------------------------------------------------------------------------
# SHEET 3: RevenueForecast (by Category)
# --------------------------------------------------------------------------------------
ws3 = wb.create_sheet("RevenueForecast")
revenue_header = [
"Year",
"Brand Revenue",
"Data Insights Revenue",
"Affiliate Revenue",
"Other Revenue",
"Total Revenue"
]
ws3.append(revenue_header)
for i in range(1, 6):
row_idx = i + 1
year_val = i
brand_rev_formula = f"='Detailed Drivers'!D{row_idx}"
data_rev_formula = f"='Detailed Drivers'!G{row_idx}"
affiliate_formula = f"='Detailed Drivers'!L{row_idx}"
other_formula = f"='Detailed Drivers'!M{row_idx}"
total_formula = f"='Detailed Drivers'!N{row_idx}"
ws3.append([year_val, brand_rev_formula, data_rev_formula, affiliate_formula, other_formula, total_formula])
for col in ws3.columns:
max_length = 0
column = col[0].column_letter
for cell in col:
val = str(cell.value)
if len(val) > max_length:
max_length = len(val)
ws3.column_dimensions[column].width = max_length + 2
# --------------------------------------------------------------------------------------
# SAVE THE WORKBOOK
# --------------------------------------------------------------------------------------
filename = "Revu_Detailed_Model.xlsx"
wb.save(filename)
print(f"Successfully created '{filename}' with a detailed financial model for Revu!")
if __name__ == "__main__":
main()
Marwin Ewert
chart = LineChart()
chart.style = 2
chart.legend.overlay = False
chart.layout = Layout()
chart.x_axis.delete = False
chart.y_axis.delete = False
chart.width = 21
chart.height = 9
chart.layout = Layout(
manualLayout=ManualLayout(
x=0.005, y=0.05,
w=0.75, h=0.8,
xMode="factor",
yMode="factor"
)
)
chart.layout.layoutTarget = "inner"
