openpyxl issueshttps://foss.heptapod.net/openpyxl/openpyxl/-/issues2023-01-30T08:48:59Zhttps://foss.heptapod.net/openpyxl/openpyxl/-/issues/1945The openpyxl module has problems when writing data to a sheet if there are pi...2023-01-30T08:48:59ZNegmat MeThe openpyxl module has problems when writing data to a sheet if there are pivot tables in the documentHi! I use this code to write data to an existing xls file. Generated a text dataset
```import openpyxl
from urllib.parse import urlencode
import requests
import pandas as pd
l1 = [1,2,3,4]
l2 = [1,2,3,4]
df = DataFrame({'Stimulus Ti...Hi! I use this code to write data to an existing xls file. Generated a text dataset
```import openpyxl
from urllib.parse import urlencode
import requests
import pandas as pd
l1 = [1,2,3,4]
l2 = [1,2,3,4]
df = DataFrame({'Stimulus Time': l1, 'Reaction Time': l2})
df
w= pd.ExcelWriter('data.xlsx', mode= 'a', if_sheet_exists= 'overlay', engine= 'openpyxl')
df.to_excel(w, sheet_name= 'data', index= False)
w.save()
```
[data.xlsx](/uploads/f7f455b7bfd349197220c5372cd9e838/data.xlsx)
The data should be recorded in the data sheet, on the other sheets there are pivot tables that are built on the basis of the data sheet. If you run the code, then at the data recording stage, the code will be executed indefinitely.
But if you fill in the data sheet with terms from 50 and update the pivot tables, then the data will be recorded.
Tell me what is the problem here and how can it be solved? I didn't find any information anywhereNegmat MeNegmat Mehttps://foss.heptapod.net/openpyxl/openpyxl/-/issues/1943Bug: Data Segmentations is lost after saving2023-01-27T14:04:32ZJoaquin CaballeroBug: Data Segmentations is lost after saving## Data Segmentations is lost after saving
I have an excel that include some data segmentation for filtering. When I only open and saved, not even editing anything, the new file lost the data segmentation filtering
Test code
```
wb= l...## Data Segmentations is lost after saving
I have an excel that include some data segmentation for filtering. When I only open and saved, not even editing anything, the new file lost the data segmentation filtering
Test code
```
wb= load_workbook("file1.xlsx")
wb.save("file2.xlsx")
```
file1.xlsx
![image](/uploads/d03cf882378ff743fa891f3cc01ba41a/image.png)
file2.xlsx
![image](/uploads/5dc424a7a48fd4ba4b6f919e9329b02c/image.png)
Versions:
* Python 3.10.4
* openpyxl 3.0.10
* Windows 10https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1932Defined Name RANGE for a table column2023-06-28T12:58:44ZEdificomSADefined Name RANGE for a table columnWith a defined name range with value : `=table[column]` ; a reference to a column of a table
```python
dn = wb.defined_names["dn"]
print(dn.type)
RANGE
for title, coord in dn.destinations :
ws = wb[title]
```
```
Traceback (most r...With a defined name range with value : `=table[column]` ; a reference to a column of a table
```python
dn = wb.defined_names["dn"]
print(dn.type)
RANGE
for title, coord in dn.destinations :
ws = wb[title]
```
```
Traceback (most recent call last):
File "/home/test.py", line 10, in <module>
for title, coord in dn.destinations :
File "/home/py3.10/lib/python3.10/site-packages/openpyxl/workbook/defined_name.py", line 140, in destinations
sheetname = m.group('notquoted') or m.group('quoted')
AttributeError: 'NoneType' object has no attribute 'group'
```
Openpyxl 3.0.10https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1927Access to NumberFormatLocal2023-02-01T10:35:28ZMartin StrandAccess to NumberFormatLocalFeature request. I *think* I need access to NumberFormatLocal in addition to just NumberFormat.
Background: I'm using worksheet.cell.number_format to set format to `_-kr * # ##0,00_-;[...]`. However, when I open it in Excel, it has gott...Feature request. I *think* I need access to NumberFormatLocal in addition to just NumberFormat.
Background: I'm using worksheet.cell.number_format to set format to `_-kr * # ##0,00_-;[...]`. However, when I open it in Excel, it has gotten escaped as `_-k\r * #\ ##0,00_-;[...]`. At first, I wanted to blame Openpyxl, but then I realised that this was probably on Excel. [One workaround](https://stackoverflow.com/questions/26650219/excel-interop-adding-slashes-in-my-number-formatting-string) seems to be to set the format using NumberFormatLocal instead of NumberFormat.
I might very well be mistaken, and that my particular problem can be resolved in a different manner.https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1916worksheet.delete_cols() broken in releases >= 3.0.42023-01-31T14:21:58ZDaniel Hunterworksheet.delete_cols() broken in releases >= 3.0.4System:
Ubuntu 20.04.5 LTS
Python 3.8.10
LibreCalc 6.4.7.2
openpyxl 3.0.4 - 3.0.10
When attempting to delete columns using the following code, an error occurs:
```
wb = load_workbook(filename = /home/path/to/file.xlsx')
ws = wb['Data']...System:
Ubuntu 20.04.5 LTS
Python 3.8.10
LibreCalc 6.4.7.2
openpyxl 3.0.4 - 3.0.10
When attempting to delete columns using the following code, an error occurs:
```
wb = load_workbook(filename = /home/path/to/file.xlsx')
ws = wb['Data']
ws.delete_cols(idx=3, amount=3)
```
Here's the error:
```
Traceback (most recent call last):
File "/home/path/to/file.py", line 347, in <module>
ws.delete_cols(idx=3, amount=3)
File "/home/user/.local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py", line 751, in delete_cols
self._move_cells(min_col=idx+amount, offset=-amount, row_or_col="column")
File "/home/user/.local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py", line 705, in _move_cells
self._move_cell(row, column, row_offset, col_offset)
File "/home/user/.local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py", line 798, in _move_cell
cell = self._get_cell(row, column)
File "/home/user/.local/lib/python3.8/site-packages/openpyxl/worksheet/worksheet.py", line 253, in _get_cell
raise ValueError("Row numbers must be between 1 and 1048576")
ValueError: Row numbers must be between 1 and 1048576
```
Reverting to openpyxl 3.0.3 resolves the problem. Note that this error occurs whether using the offset (amount) or not.https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1903plot_area layout is not written to resulting chart.xml2022-10-26T16:21:35ZRichard Hartplot_area layout is not written to resulting chart.xmlHello run the following code, which I borrowed from issue 523:
```
from openpyxl import Workbook, load_workbook`
from openpyxl import chart
wb = Workbook()
ws = wb.active
rows = [
['Size', 'Batch 1', 'Batch 2'],
[2, 40, 30],
...Hello run the following code, which I borrowed from issue 523:
```
from openpyxl import Workbook, load_workbook`
from openpyxl import chart
wb = Workbook()
ws = wb.active
rows = [
['Size', 'Batch 1', 'Batch 2'],
[2, 40, 30],
[3, 40, 25],
[4, 50, 30],
[5, 30, 25],
[6, 25, 35],
[7, 20, 40],
]
for row in rows:
ws.append(row)
ch = chart.ScatterChart()
ch.scatterStyle = "marker"
ch.title = "Scatter Chart"
ch.style = 13
ch.x_axis.title = 'Size'
ch.y_axis.title = 'Percentage'
xvalues = chart.Reference(ws, min_col=1, min_row=2, max_row=7)
for i in range(2, 4):
values = chart.Reference(ws, min_col=i, min_row=1, max_row=7)
series = chart.Series(values, xvalues, title_from_data=True)
ch.series.append(series)
ch.legend.layout=chart.layout.Layout(manualLayout=chart.layout.ManualLayout(yMode='edge', xMode='edge',x=0.1,y=1, h=0.2, w=0.2))
ch.plot_area.layout=chart.layout.Layout(manualLayout=chart.layout.ManualLayout(yMode='edge', xMode='edge',x=0.1,y=0.1, h=0.65, w=0.85))
ws.add_chart(ch, "A10")
wb.save("scatter.xlsx")
```
After running this code, change scatter.xlsx to scatter.zip. Then inspect the chart1.xml. You'll see that there is no layout specified for the plotarea.
Is this correct way to specify the layout for the plotarea? One thing that I did notice is that the layout for the legend is written to chart1.xml.https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1900Feature: use PyFilesystem zipfile instead of zipfile2022-10-24T08:46:44ZCharlieCFeature: use PyFilesystem zipfile instead of zipfileReading and writing workbooks uses a lot of CPU and, with a lot of globals bound to the workbook object, it's not really possible to do any parallelisation.
But the [PyFilesystem Zipfile](https://docs.pyfilesystem.org/en/latest/referenc...Reading and writing workbooks uses a lot of CPU and, with a lot of globals bound to the workbook object, it's not really possible to do any parallelisation.
But the [PyFilesystem Zipfile](https://docs.pyfilesystem.org/en/latest/reference/zipfs.html) implementation could simplify a lot of read/write code and might also make it possible to at least use async in some options, though a separate process would be even better: this could make writing very large workbooks noticeably faster.CharlieCCharlieChttps://foss.heptapod.net/openpyxl/openpyxl/-/issues/1898Feature request: Cell Metadata2024-03-03T23:03:54ZRudolf BykerFeature request: Cell MetadataIn the ECMA spec ( https://www.ecma-international.org/publications-and-standards/standards/ecma-376/ ), we have this:
![image](/uploads/c78eb26220ff76a8a52cc17732fd4111/image.png)
When writing some formulas using openpyxl 3.1 with `Ar...In the ECMA spec ( https://www.ecma-international.org/publications-and-standards/standards/ecma-376/ ), we have this:
![image](/uploads/c78eb26220ff76a8a52cc17732fd4111/image.png)
When writing some formulas using openpyxl 3.1 with `ArrayFormula`, it ends up as [CSE](https://exceljet.net/glossary/cse) formulas, which is sometimes what I want:
![image](/uploads/e6c874980e928c4afde98a768322e079/image.png)
However, sometimes I just want this, while still using `ArrayFormula` (to prevent unwanted `@` from being added):
![image](/uploads/71e5534759676fda5a1e2509adcace9e/image.png)
The difference in the XML is as follows:
![image](/uploads/01e24a285d4dc8cf45e522e74a2caa37/image.png)
The attribute `cm=1` probably refers to this bit in `metadata.xml`
![image](/uploads/489d58d5d22c4ba073023716b64493ef/image.png)
In summary, I would like to somehow write this metadata using openpyxl, so that I can have array formulas without [CSE](https://exceljet.net/glossary/cse).https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1866Support strict namespace2022-08-05T15:56:10ZCharlieCSupport strict namespaceOOXML files are a mess. The specification was ratified after Microsoft released Office 2007 and contain subtle differences. Complicating matters was the decision to use a different namespace once the specification had been ratified. We i...OOXML files are a mess. The specification was ratified after Microsoft released Office 2007 and contain subtle differences. Complicating matters was the decision to use a different namespace once the specification had been ratified. We implement the specification wherever we can, because it is at least documented, but follow the general practice of using the 2006 transitional namespaces. As supporting different namespaces for the same things isn't trivial in Python we can't read or write using the "strict" namespaces.https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1843add image from other worksheet error:2024-02-07T17:18:34Zxtanuihaadd image from other worksheet error:I need to copy a sheet with images,using follow code:
```
from openpyxl import load_workbook
from copy import copy
wb = load_workbook('demo.xlsx')
ws = wb.worksheets[0]
ws_copy = wb.copy_worksheet(wb.worksheets[0])
for img in ws._image...I need to copy a sheet with images,using follow code:
```
from openpyxl import load_workbook
from copy import copy
wb = load_workbook('demo.xlsx')
ws = wb.worksheets[0]
ws_copy = wb.copy_worksheet(wb.worksheets[0])
for img in ws._images:
ws_copy.add_image(copy(img))
wb.save('save.xlsx')
```
Error:
```
Traceback (most recent call last):
File "test.py", line 8, in <module>
wb.save('save.xlsx')
File "/usr/local/lib/python3.6/dist-packages/openpyxl/workbook/workbook.py", line 407, in save
save_workbook(self, filename)
File "/usr/local/lib/python3.6/dist-packages/openpyxl/writer/excel.py", line 293, in save_workbook
writer.save()
File "/usr/local/lib/python3.6/dist-packages/openpyxl/writer/excel.py", line 275, in save
self.write_data()
File "/usr/local/lib/python3.6/dist-packages/openpyxl/writer/excel.py", line 77, in write_data
self._write_images()
File "/usr/local/lib/python3.6/dist-packages/openpyxl/writer/excel.py", line 116, in _write_images
self._archive.writestr(img.path[1:], img._data())
File "/usr/local/lib/python3.6/dist-packages/openpyxl/drawing/image.py", line 48, in _data
img = _import_image(self.ref)
File "/usr/local/lib/python3.6/dist-packages/openpyxl/drawing/image.py", line 16, in _import_image
img = PILImage.open(img)
File "/usr/local/lib/python3.6/dist-packages/PIL/Image.py", line 2315, in open
fp.seek(0)
ValueError: I/O operation on closed file.
```
By modify **\openpyxl\drawing\image.py** can fix this problem.
```
def _data(self):
"""
Return image data, convert to supported types if necessary
"""
img = _import_image(self.ref)
# don't convert these file formats
if self.format in ['gif', 'jpeg', 'png']:
img.fp.seek(0)
fp = img.fp
data = fp.read()
# fp.close()
else:
fp = BytesIO()
img.save(fp, format="png")
fp.seek(0)
data = fp.read()
fp.close()
return data
```
[demo.xlsx](/uploads/dacf2be32518c1d8b528c12907979f20/demo.xlsx)https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1839Tag namespace is not passed down to extLst2024-01-15T14:09:52ZAlexander SedovTag namespace is not passed down to extLst`Serializable` does not pass namespace down to __elements__ objects. As a result, 'extLst' and 'ext' end up namespace-less, while they should have the containing namespace.
Seems to me that correctly passing namespace=namespace to child...`Serializable` does not pass namespace down to __elements__ objects. As a result, 'extLst' and 'ext' end up namespace-less, while they should have the containing namespace.
Seems to me that correctly passing namespace=namespace to child should fix this issue. I'd do a PR but apparent;y can't fork hg projects here :disappointed:https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1827Tag for 3.0.10 missing2022-05-20T07:41:23ZMichał GórnyTag for 3.0.10 missingPlease push the tag for 3.0.10 release. We're using tags for packaging in Gentoo.Please push the tag for 3.0.10 release. We're using tags for packaging in Gentoo.https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1825definedName with no value causes unhandled TypeError2022-05-11T15:27:48ZBradley GoodyeardefinedName with no value causes unhandled TypeErrorIn some cases there can be invalid definedName elements in an xlsx file. In this particular case I converted an xlsb using libreoffice, and it produced the following <definedNames> tag in the workbook.xml. Which is missing the actual ran...In some cases there can be invalid definedName elements in an xlsx file. In this particular case I converted an xlsb using libreoffice, and it produced the following <definedNames> tag in the workbook.xml. Which is missing the actual range for the element. I'm not sure how forgiving openpyxl should be in this case, but it seems like the easiest fix would be to just ignore definedName tags that have no inner text.
<details><summary>Broken example of definedNames</summary>
```
<definedNames>
<definedName function="false" hidden="false" localSheetId="0" name="ID" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Area" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Titles" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="2" name="ID" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="2" name="_xlnm.Print_Area" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="2" name="_xlnm.Print_Titles" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="3" name="ID" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="3" name="_xlnm.Print_Area" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="3" name="_xlnm.Print_Titles" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="4" name="ID" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="4" name="_xlnm.Print_Area" vbProcedure="false"/>
<definedName function="false" hidden="false" localSheetId="4" name="_xlnm.Print_Titles" vbProcedure="false"/>
</definedNames>
```
</details>
<details><summary>Working definedNames</summary>
```
<definedNames>
<definedName function="false" hidden="false" localSheetId="0" name="_xlnm.Print_Area" vbProcedure="false">Sheet1!$D$8:$F$13</definedName>
</definedNames>
```
</details>
<details><summary>Stack trace</summary>
```
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
/tmp/ipykernel_1324209/96275939.py in <module>
----> 1 wb = openpyxl.load_workbook("xlsb_conversion.xlsx", data_only=True, keep_links=False)
~/.local/lib/python3.9/site-packages/openpyxl/reader/excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
315 reader = ExcelReader(filename, read_only, keep_vba,
316 data_only, keep_links)
--> 317 reader.read()
318 return reader.wb
~/.local/lib/python3.9/site-packages/openpyxl/reader/excel.py in read(self)
281 apply_stylesheet(self.archive, self.wb)
282 self.read_worksheets()
--> 283 self.parser.assign_names()
284 if not self.read_only:
285 self.archive.close()
~/.local/lib/python3.9/site-packages/openpyxl/reader/workbook.py in assign_names(self)
106 sheet.print_title_cols = cols
107 elif reserved == "Print_Area":
--> 108 sheet.print_area = _unpack_print_area(defn)
109 else:
110 defns.append(defn)
~/.local/lib/python3.9/site-packages/openpyxl/workbook/defined_name.py in _unpack_print_area(defn)
56 """
57 new = []
---> 58 for m in SHEETRANGE_RE.finditer(defn.value): # can be multiple
59 coord = m.group("cells")
60 if coord:
TypeError: expected string or bytes-like object
```
</details>https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1798sheet.insert_rows leaves hyperlink unchanged2022-10-06T14:08:03ZLouis Wangsheet.insert_rows leaves hyperlink unchangedif use the insert_rows function, finally the _move_cell will be invoked which just moved the cell to the target row/col, but if this cell has a hyperlink property, then the hyperlink.ref will be unchanged
i can fix this issue by simply ...if use the insert_rows function, finally the _move_cell will be invoked which just moved the cell to the target row/col, but if this cell has a hyperlink property, then the hyperlink.ref will be unchanged
i can fix this issue by simply adding following codes in _move_cell function
```python
if cell.hyperlink:
cell.hyperlink.ref=cell.coordinate
```https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1796TypeError: __init__() got an unexpected keyword argument 'id' (case 2)2024-01-18T19:12:08ZJonas HörschTypeError: __init__() got an unexpected keyword argument 'id' (case 2)Reading the file [case.xlsx](/uploads/4796b25eb36fd7a97ee3824023948bc6/case.xlsx) with
```python
from openpyxl import load_workbook
wb = load_workbook("case.xlsx")
```
fails with
```python
In [7]: wb = openpyxl.load_workbook("case.xlsx")...Reading the file [case.xlsx](/uploads/4796b25eb36fd7a97ee3824023948bc6/case.xlsx) with
```python
from openpyxl import load_workbook
wb = load_workbook("case.xlsx")
```
fails with
```python
In [7]: wb = openpyxl.load_workbook("case.xlsx")
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
<ipython-input-7-7e36d52af696> in <module>
----> 1 wb = openpyxl.load_workbook("case.xlsx")
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/reader/excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
315 reader = ExcelReader(filename, read_only, keep_vba,
316 data_only, keep_links)
--> 317 reader.read()
318 return reader.wb
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/reader/excel.py in read(self)
276 self.read_manifest()
277 self.read_strings()
--> 278 self.read_workbook()
279 self.read_properties()
280 self.read_theme()
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/reader/excel.py in read_workbook(self)
148 wb_part = _find_workbook_part(self.package)
149 self.parser = WorkbookParser(self.archive, wb_part.PartName[1:], keep_links=self.keep_links)
--> 150 self.parser.parse()
151 wb = self.parser.wb
152 wb._sheets = []
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/reader/workbook.py in parse(self)
47 src = self.archive.read(self.workbook_part_name)
48 node = fromstring(src)
---> 49 package = WorkbookPackage.from_tree(node)
50 if package.properties.date1904:
51 self.wb.epoch = CALENDAR_MAC_1904
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/descriptors/serialisable.py in from_tree(cls, node)
81 if hasattr(desc, 'from_tree'):
82 #descriptor manages conversion
---> 83 obj = desc.from_tree(el)
84 else:
85 if hasattr(desc.expected_type, "from_tree"):
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/descriptors/sequence.py in from_tree(self, node)
83
84 def from_tree(self, node):
---> 85 return [self.expected_type.from_tree(el) for el in node]
86
87
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/descriptors/sequence.py in <listcomp>(.0)
83
84 def from_tree(self, node):
---> 85 return [self.expected_type.from_tree(el) for el in node]
86
87
~/.local/conda/envs/1o5/lib/python3.8/site-packages/openpyxl/descriptors/serialisable.py in from_tree(cls, node)
101 attrib[tag] = obj
102
--> 103 return cls(**attrib)
104
105
TypeError: __init__() missing 1 required positional argument: 'id'
```
Previously reported in now deleted comment in #1783 .
Thankshttps://foss.heptapod.net/openpyxl/openpyxl/-/issues/1790Merged Cell protection2022-03-28T16:23:38ZArchon2101Merged Cell protectionHello, I am having an issue with merged cell protection, it may already be known, I have seen a few posts floating around with similarish issues.
I have a protected sheet with merged cells that are unlocked. I removed the protection on...Hello, I am having an issue with merged cell protection, it may already be known, I have seen a few posts floating around with similarish issues.
I have a protected sheet with merged cells that are unlocked. I removed the protection on the sheet to edit a few other cells, then when I re-protected the sheet, the merged cells that were unlocked in excel are now locked.
I'm not sure if its because the merged cell range A7:B7 A7 is specified as unlocked and unhidden but B7 is locked so it takes the locked precedent. if I manually specify Protected(locked=False) for every cell in the range the merged cell covers, it will stay unlocked.
kinda weirdhttps://foss.heptapod.net/openpyxl/openpyxl/-/issues/1788Worksheet.row_dimensions.group() does not collapse multi-level outlines2022-02-01T21:28:22ZPieFaceThrowsPieWorksheet.row_dimensions.group() does not collapse multi-level outlinesMy exported worksheet has a multi-level outline, i.e. groups of rows nested inside a larger group of rows.
I used `hidden=True` but when I open the file in Excel, only the top outline level is collapsed. When I open Level 1, Levels 2, 3...My exported worksheet has a multi-level outline, i.e. groups of rows nested inside a larger group of rows.
I used `hidden=True` but when I open the file in Excel, only the top outline level is collapsed. When I open Level 1, Levels 2, 3, etc. inside it are expanded by default.
Adding this line fixes my issue:
```python
# dimensions.py line 222 (DimensionHolder.group() function)
...
new_dim.outline_level = outline_level
new_dim.hidden = hidden
# Added
new_dim.collapsed = hidden
```
The `.hidden` line seems to still be needed, in addition to `.collapsed`
The column outline code just above this may need a similar addition. Haven't tested that.https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1777Gradient Fill does not work properly in some charts2022-06-09T13:18:31ZKeiji MashimoGradient Fill does not work properly in some charts(1)Chart plot area
The degree(direction of gradation) parameter cannot be changed from default value(=0).
For example, following does not work.
gfProp = GradientFillProperties()
gfProp.degree = 90
In the case of 'cell' parameters, ...(1)Chart plot area
The degree(direction of gradation) parameter cannot be changed from default value(=0).
For example, following does not work.
gfProp = GradientFillProperties()
gfProp.degree = 90
In the case of 'cell' parameters, it seems no problem.
![2021-12-27_10.45.23_AM](/uploads/afa4d073e91409080058f94c362cc9dd/2021-12-27_10.45.23_AM.png)
The default degree = 0, and it cannot be changed.
![2021-12-27_10.38.12_AM](/uploads/9d5f1043d42797d0a07594755927df32/2021-12-27_10.38.12_AM.png)
(2)Border area of chart
Any parameter of 'Fill' does not work in this case.
If 'cs.spPr = self.graphical_properties' is added, in def _write(self) of _chart.py,
it works. However, 'degree' parameter as mentioned above is also neglected.
An example of 'chart.graphical_properties = GraphicalProperties(noFill=True)' is following.
before
![2021-12-23_11.45.46_PM](/uploads/2f87ce38c0c9ff5c5e198f7e5e7592cb/2021-12-23_11.45.46_PM.png)
after
![2021-12-23_11.44.34_PM](/uploads/213a3e989efb0f6332684640c330d076/2021-12-23_11.44.34_PM.png)https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1775Named Range to Line Chart2021-12-21T16:00:38Zarisgeo96Named Range to Line ChartI have been trying to add a named range as data to a line chart. The named range includes a range in a formula format, meaning:
=XLOOKUP(...):XLOOKUP(...)
Although the named range appears in the Excel output and is also able to be adde...I have been trying to add a named range as data to a line chart. The named range includes a range in a formula format, meaning:
=XLOOKUP(...):XLOOKUP(...)
Although the named range appears in the Excel output and is also able to be added as Series to a chart manually, I cannot do it directly from openpyxl. Is this feasible at all?https://foss.heptapod.net/openpyxl/openpyxl/-/issues/1771Can’t set chart fill using RGBPercent2021-12-09T09:11:04ZKari DelmarCan’t set chart fill using RGBPercentHello,
I’m using openpyxl 3.0.9 with python 3.8. It looks like the existing handler for the RGB Color Model, Percentage Variant isn’t producing the expected results. Here’s a basic example to demonstrate the issue:
The code below is me...Hello,
I’m using openpyxl 3.0.9 with python 3.8. It looks like the existing handler for the RGB Color Model, Percentage Variant isn’t producing the expected results. Here’s a basic example to demonstrate the issue:
The code below is meant to generate a chart, then set the chart series fill color using the `RGBPercent` object. The expected result is a chart with bars having 100% red fill. However, the actual result is a bar chart with blue (probably the default color?) filled bars.
```
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.colors import ColorChoice, RGBPercent
wb = Workbook()
ws = wb.active
for i in range(10):
ws.append([i])
values = Reference(ws, min_col=1, min_row=1, max_col=1, max_row=10)
chart = BarChart()
chart.add_data(values)
red = RGBPercent(r=100, g=0, b=0)
series = chart.series[0]
series.graphicalProperties.solidFill = ColorChoice(scrgbClr=red)
ws.add_chart(chart)
wb.save('test_charts.xlsx')
```
Here’s the chart:
![the_chart](/uploads/b8e17b7c04dfb240e4ad804fc1d7c635/the_chart.jpg)
And here’s the XML generated for the `<scrgbClr>` child of the chart series `<spPr>` tag:
`<scrgbClr r="100" g="0" b="0" />`
I think the issue is that the XML doesn’t conform to spec as described in ECMA-376 §20.1.2.3.30. The definition for the RGB Percent color model states that the red, green and blue components should be expressed as percentages (ie, ST_Percentage) from 0 to 100% with the trailing percent sign. (More specifically, §22.9.2.9 indicates that this type’s contents should match regex pattern `-?[0-9]+(\.[0-9]+)?%`).
So the generated XML should probably be something like this (adding the namespace too):
`<a:scrgbClr r="100%" g="0%" b="0%" />`
Oddly, the examples in other sections of the spec don’t conform to the definition in §20.1.2.3.30 and leave off the trailing percent sign (e.g §20.1.4.2.11, among others). My guess is that the spec was revised, but the examples in other parts of the document were never updated.
Is it okay to change the data type of the `RGBPercent` constructor’s parameters? If so, I can submit a pull request that fixes this issue by changing `r`, `g` and `b` from `MinMax` to `MatchPattern` types. This would require the consumer to pass strings with a trailing percent to the constructor, thus matching the spec:
`RGBPercent(r="100%", g="0%", b="0%")`