chronossc / openpyxl Goto Github PK

View Code? Open in Web Editor NEW

73.0 12.0 18.0 1.69 MB

Go to original project. This fork isn't maintened.

Home Page: https://foss.heptapod.net/openpyxl/openpyxl

License: Other

Shell 1.39% Python 98.61%

openpyxl's People

Contributors

Stargazers

Watchers

Forkers

roxyboy fejsov michaellaurapro jayachandp zishendianxia hiepnguyen1101 pbsd zurgeg aldamico zhaoxun rams502 frstr5927 mfkiwl abdelwhab-1 ophers awamej python-repository-hub pukonu

openpyxl's Issues

Font size is not changed (chart title,x_axis.title,y_axis.title)?

Chart has been created on excel sheet with openpyxl (2.4.9).
Font size of chart title,x_axis.title and y_axis.title can not be changed....

Saving to io.BytesIO no longer available

The functionality to save to a BytesIO object for in-memory handling was removed at some point: I am unable to do

working_file = io.BytesIO()
workbook.save(working_file)

wb = load_workbook(filename=working_file)

in 3.0.2, but it works fine in 2.6.0

When was this functionality removed? What was the rationale for its removal?

Bug: DataFrame with NaTType throws an error

If I try to read a pandas dataframe with a NaT type into a worksheet, openpyxl throws an error:

import pandas as pd
import datetime
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl import Workbook

df = pd.DataFrame({'T': [datetime.datetime(2020,1,1), "nat"]})
wb = Workbook()
ws = wb.active
for r in dataframe_to_rows(df, index=False):
    ws.append(r)

The error:

Traceback (most recent call last):

  File "<ipython-input-47-2995a3c7758d>", line 6, in <module>
    ws.append(r)

  File "C:\Users\My.Name\AppData\Local\Continuum\miniconda3\envs\main\lib\site-packages\openpyxl\worksheet\worksheet.py", line 646, in append
    cell = Cell(self, row=row_idx, column=col_idx, value=content)

  File "C:\Users\My.Name\AppData\Local\Continuum\miniconda3\envs\main\lib\site-packages\openpyxl\cell\cell.py", line 113, in __init__
    self.value = value

  File "C:\Users\My.Name\AppData\Local\Continuum\miniconda3\envs\main\lib\site-packages\openpyxl\cell\cell.py", line 216, in value
    self._bind_value(value)

  File "C:\Users\My.Name\AppData\Local\Continuum\miniconda3\envs\main\lib\site-packages\openpyxl\cell\cell.py", line 188, in _bind_value
    self.number_format = TIME_FORMATS[t]

KeyError: <class 'pandas._libs.tslibs.nattype.NaTType'>

Openpyxl 3.0.3
Pandas 1.0.3
Numpy 1.18.1
Python 3.7.6

Inserting rows causes hyperlink targets to shift

Make an Excel file which has a column which contains hyperlinks to files (in my case .DOCX files). Let's pick column D since that is what I have. If you insert rows from the top of the file, you will notice that the hyperlinks no longer point to the correct matching files. If you have empty rows in-between then you may even notice some URLs stop pointing to anything at all. I am guessing the fix is not too bad and my client would greatly benefit from this fix. Thank you for your time and this is an awesome library!

openpyxl corrupts protected sheet

Using openpyxl 0.5.5 I am trying to modify a workbook with one unprotected sheet and on protected sheet. After opening the workbook and modifying the unprotected sheet then saving the workbook the saved workbook is corrupted. I took the output of the protected sheet and see the following has changed.

Original sheet:

Saved sheet:

Source code:
from openpyxl import load_workbook
import encodings

wb = load_workbook(filename='Test.xlsx')

ws = wb.worksheets[0]
lastRow = ws.max_row
lastCol = ws.max_column

order = 'M1234'

ws.cell(1,column=lastCol + 1).value = 'Order Number'
ws.cell(1,column=lastCol + 2).value = 'Status'
ws.cell(2,column=lastCol + 1).value = order.encode("utf-8")
ws.cell(2,column=lastCol + 2).value = 'good'

wb.save('test_out.xlsx')
wb.close()

Test.xlsx

test_out.xlsx

Error when installing: error: [Errno 2] No such file or directory: 'LICENCE.rst'

An odd message during pip installation of openpyxl in a fresh Anaconda environment on Windows Server 2012 R2.

(py36) D:\Program Files (x86)\kinverarity>conda create -n test2 python=3.7
WARNING: A space was detected in your requested environment path
'D:\Program Files (x86)\Anaconda3\envs\test2'
Spaces in paths can sometimes be problematic.
Solving environment: done

## Package Plan ##

  environment location: D:\Program Files (x86)\Anaconda3\envs\test2

  added / updated specs:
    - python=3.7


The following NEW packages will be INSTALLED:

    certifi:        2018.10.15-py37_0
    pip:            18.1-py37_0
    python:         3.7.1-h33f27b4_4
    setuptools:     40.5.0-py37_0
    vc:             14.1-h0510ff6_4
    vs2015_runtime: 14.15.26706-h3a45250_0
    wheel:          0.32.2-py37_0
    wincertstore:   0.2-py37_0

Proceed ([y]/n)? y

Preparing transaction: done
Verifying transaction: done
Executing transaction: done
#
# To activate this environment, use
#
#     $ conda activate test2
#
# To deactivate an active environment, use
#
#     $ conda deactivate


(py36) D:\Program Files (x86)\kinverarity>activate test2

(test2) D:\Program Files (x86)\kinverarity>pip install openpyxl
Collecting openpyxl
  Using cached https://files.pythonhosted.org/packages/57/12/65fc2f7309ad4a9e1b17239f0b9bffee3464eb8eb529dbe463af47d5b2a2/openpyxl-2.5.10.tar.gz
Collecting jdcal (from openpyxl)
  Using cached https://files.pythonhosted.org/packages/a0/38/dcf83532480f25284f3ef13f8ed63e03c58a65c9d3ba2a6a894ed9497207/jdcal-1.4-py2.py3-none-any.whl
Collecting et_xmlfile (from openpyxl)
Building wheels for collected packages: openpyxl
  Running setup.py bdist_wheel for openpyxl ... error
  Complete output from command "D:\Program Files (x86)\Anaconda3\envs\test2\python.exe" -u -c "import setuptools, tokenize;__file__='C:\\Users\\KINVER~1\\AppData\\Local\\Temp\\3\\pip-install-_vk99gvj\\openpyxl\\setup.py';f=getattr(tokenize, 'open', open)(__file__);code=f.read().replace('\r\n', '\n');f.close();exec(compile(code, __file__, 'exec'))" bdist_wheel -d C:\Users\KINVER~1\AppData\Local\Temp\3\pip-wheel-11igj1b5 --python-tag cp37:
  running bdist_wheel
  running build
  running build_py
  creating build
  creating build\lib
  creating build\lib\openpyxl
  copying openpyxl\conftest.py -> build\lib\openpyxl
  copying openpyxl\_constants.py -> build\lib\openpyxl
  copying openpyxl\__init__.py -> build\lib\openpyxl
  creating build\lib\openpyxl\cell
  copying openpyxl\cell\cell.py -> build\lib\openpyxl\cell
  copying openpyxl\cell\interface.py -> build\lib\openpyxl\cell
  copying openpyxl\cell\read_only.py -> build\lib\openpyxl\cell
  copying openpyxl\cell\text.py -> build\lib\openpyxl\cell
  copying openpyxl\cell\__init__.py -> build\lib\openpyxl\cell
  creating build\lib\openpyxl\chart
  copying openpyxl\chart\area_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\axis.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\bar_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\bubble_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\chartspace.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\data_source.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\descriptors.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\error_bar.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\label.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\layout.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\legend.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\line_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\marker.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\picture.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\pie_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\plotarea.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\print_settings.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\radar_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\reader.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\reference.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\scatter_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\series.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\series_factory.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\shapes.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\stock_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\surface_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\text.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\title.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\trendline.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\updown_bars.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\_3d.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\_chart.py -> build\lib\openpyxl\chart
  copying openpyxl\chart\__init__.py -> build\lib\openpyxl\chart
  creating build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\chartsheet.py -> build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\custom.py -> build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\properties.py -> build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\protection.py -> build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\publish.py -> build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\relation.py -> build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\views.py -> build\lib\openpyxl\chartsheet
  copying openpyxl\chartsheet\__init__.py -> build\lib\openpyxl\chartsheet
  creating build\lib\openpyxl\comments
  copying openpyxl\comments\author.py -> build\lib\openpyxl\comments
  copying openpyxl\comments\comments.py -> build\lib\openpyxl\comments
  copying openpyxl\comments\comment_sheet.py -> build\lib\openpyxl\comments
  copying openpyxl\comments\shape_writer.py -> build\lib\openpyxl\comments
  copying openpyxl\comments\__init__.py -> build\lib\openpyxl\comments
  creating build\lib\openpyxl\compat
  copying openpyxl\compat\abc.py -> build\lib\openpyxl\compat
  copying openpyxl\compat\accumulate.py -> build\lib\openpyxl\compat
  copying openpyxl\compat\numbers.py -> build\lib\openpyxl\compat
  copying openpyxl\compat\singleton.py -> build\lib\openpyxl\compat
  copying openpyxl\compat\strings.py -> build\lib\openpyxl\compat
  copying openpyxl\compat\__init__.py -> build\lib\openpyxl\compat
  creating build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\base.py -> build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\excel.py -> build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\namespace.py -> build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\nested.py -> build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\sequence.py -> build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\serialisable.py -> build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\slots.py -> build\lib\openpyxl\descriptors
  copying openpyxl\descriptors\__init__.py -> build\lib\openpyxl\descriptors
  creating build\lib\openpyxl\drawing
  copying openpyxl\drawing\colors.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\connector.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\drawing.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\effect.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\fill.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\geometry.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\graphic.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\image.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\line.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\picture.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\properties.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\relation.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\shape.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\spreadsheet_drawing.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\text.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\xdr.py -> build\lib\openpyxl\drawing
  copying openpyxl\drawing\__init__.py -> build\lib\openpyxl\drawing
  creating build\lib\openpyxl\formatting
  copying openpyxl\formatting\formatting.py -> build\lib\openpyxl\formatting
  copying openpyxl\formatting\rule.py -> build\lib\openpyxl\formatting
  copying openpyxl\formatting\__init__.py -> build\lib\openpyxl\formatting
  creating build\lib\openpyxl\formula
  copying openpyxl\formula\tokenizer.py -> build\lib\openpyxl\formula
  copying openpyxl\formula\translate.py -> build\lib\openpyxl\formula
  copying openpyxl\formula\__init__.py -> build\lib\openpyxl\formula
  creating build\lib\openpyxl\packaging
  copying openpyxl\packaging\core.py -> build\lib\openpyxl\packaging
  copying openpyxl\packaging\extended.py -> build\lib\openpyxl\packaging
  copying openpyxl\packaging\interface.py -> build\lib\openpyxl\packaging
  copying openpyxl\packaging\manifest.py -> build\lib\openpyxl\packaging
  copying openpyxl\packaging\relationship.py -> build\lib\openpyxl\packaging
  copying openpyxl\packaging\workbook.py -> build\lib\openpyxl\packaging
  copying openpyxl\packaging\__init__.py -> build\lib\openpyxl\packaging
  creating build\lib\openpyxl\pivot
  copying openpyxl\pivot\cache.py -> build\lib\openpyxl\pivot
  copying openpyxl\pivot\fields.py -> build\lib\openpyxl\pivot
  copying openpyxl\pivot\record.py -> build\lib\openpyxl\pivot
  copying openpyxl\pivot\table.py -> build\lib\openpyxl\pivot
  copying openpyxl\pivot\__init__.py -> build\lib\openpyxl\pivot
  creating build\lib\openpyxl\reader
  copying openpyxl\reader\drawings.py -> build\lib\openpyxl\reader
  copying openpyxl\reader\excel.py -> build\lib\openpyxl\reader
  copying openpyxl\reader\strings.py -> build\lib\openpyxl\reader
  copying openpyxl\reader\worksheet.py -> build\lib\openpyxl\reader
  copying openpyxl\reader\__init__.py -> build\lib\openpyxl\reader
  creating build\lib\openpyxl\styles
  copying openpyxl\styles\alignment.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\borders.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\builtins.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\cell_style.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\colors.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\differential.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\fills.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\fonts.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\named_styles.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\numbers.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\protection.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\proxy.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\styleable.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\stylesheet.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\table.py -> build\lib\openpyxl\styles
  copying openpyxl\styles\__init__.py -> build\lib\openpyxl\styles
  creating build\lib\openpyxl\utils
  copying openpyxl\utils\bound_dictionary.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\cell.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\dataframe.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\datetime.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\escape.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\exceptions.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\formulas.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\indexed_list.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\protection.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\units.py -> build\lib\openpyxl\utils
  copying openpyxl\utils\__init__.py -> build\lib\openpyxl\utils
  creating build\lib\openpyxl\workbook
  copying openpyxl\workbook\child.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\defined_name.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\external_reference.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\function_group.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\parser.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\properties.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\protection.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\smart_tags.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\views.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\web.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\workbook.py -> build\lib\openpyxl\workbook
  copying openpyxl\workbook\__init__.py -> build\lib\openpyxl\workbook
  creating build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\cell_range.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\copier.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\datavalidation.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\dimensions.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\drawing.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\filters.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\header_footer.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\hyperlink.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\merge.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\page.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\pagebreak.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\properties.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\protection.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\read_only.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\related.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\table.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\views.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\worksheet.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\write_only.py -> build\lib\openpyxl\worksheet
  copying openpyxl\worksheet\__init__.py -> build\lib\openpyxl\worksheet
  creating build\lib\openpyxl\writer
  copying openpyxl\writer\etree_worksheet.py -> build\lib\openpyxl\writer
  copying openpyxl\writer\excel.py -> build\lib\openpyxl\writer
  copying openpyxl\writer\strings.py -> build\lib\openpyxl\writer
  copying openpyxl\writer\theme.py -> build\lib\openpyxl\writer
  copying openpyxl\writer\workbook.py -> build\lib\openpyxl\writer
  copying openpyxl\writer\worksheet.py -> build\lib\openpyxl\writer
  copying openpyxl\writer\__init__.py -> build\lib\openpyxl\writer
  creating build\lib\openpyxl\xml
  copying openpyxl\xml\constants.py -> build\lib\openpyxl\xml
  copying openpyxl\xml\functions.py -> build\lib\openpyxl\xml
  copying openpyxl\xml\__init__.py -> build\lib\openpyxl\xml
  creating build\lib\openpyxl\workbook\external_link
  copying openpyxl\workbook\external_link\external.py -> build\lib\openpyxl\workbook\external_link
  copying openpyxl\workbook\external_link\__init__.py -> build\lib\openpyxl\workbook\external_link
  installing to build\bdist.win32\wheel
  running install
  running install_lib
  creating build\bdist.win32
  creating build\bdist.win32\wheel
  creating build\bdist.win32\wheel\openpyxl
  creating build\bdist.win32\wheel\openpyxl\cell
  copying build\lib\openpyxl\cell\cell.py -> build\bdist.win32\wheel\.\openpyxl\cell
  copying build\lib\openpyxl\cell\interface.py -> build\bdist.win32\wheel\.\openpyxl\cell
  copying build\lib\openpyxl\cell\read_only.py -> build\bdist.win32\wheel\.\openpyxl\cell
  copying build\lib\openpyxl\cell\text.py -> build\bdist.win32\wheel\.\openpyxl\cell
  copying build\lib\openpyxl\cell\__init__.py -> build\bdist.win32\wheel\.\openpyxl\cell
  creating build\bdist.win32\wheel\openpyxl\chart
  copying build\lib\openpyxl\chart\area_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\axis.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\bar_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\bubble_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\chartspace.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\data_source.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\descriptors.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\error_bar.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\label.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\layout.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\legend.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\line_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\marker.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\picture.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\pie_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\plotarea.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\print_settings.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\radar_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\reader.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\reference.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\scatter_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\series.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\series_factory.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\shapes.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\stock_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\surface_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\text.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\title.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\trendline.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\updown_bars.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\_3d.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\_chart.py -> build\bdist.win32\wheel\.\openpyxl\chart
  copying build\lib\openpyxl\chart\__init__.py -> build\bdist.win32\wheel\.\openpyxl\chart
  creating build\bdist.win32\wheel\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\chartsheet.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\custom.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\properties.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\protection.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\publish.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\relation.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\views.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  copying build\lib\openpyxl\chartsheet\__init__.py -> build\bdist.win32\wheel\.\openpyxl\chartsheet
  creating build\bdist.win32\wheel\openpyxl\comments
  copying build\lib\openpyxl\comments\author.py -> build\bdist.win32\wheel\.\openpyxl\comments
  copying build\lib\openpyxl\comments\comments.py -> build\bdist.win32\wheel\.\openpyxl\comments
  copying build\lib\openpyxl\comments\comment_sheet.py -> build\bdist.win32\wheel\.\openpyxl\comments
  copying build\lib\openpyxl\comments\shape_writer.py -> build\bdist.win32\wheel\.\openpyxl\comments
  copying build\lib\openpyxl\comments\__init__.py -> build\bdist.win32\wheel\.\openpyxl\comments
  creating build\bdist.win32\wheel\openpyxl\compat
  copying build\lib\openpyxl\compat\abc.py -> build\bdist.win32\wheel\.\openpyxl\compat
  copying build\lib\openpyxl\compat\accumulate.py -> build\bdist.win32\wheel\.\openpyxl\compat
  copying build\lib\openpyxl\compat\numbers.py -> build\bdist.win32\wheel\.\openpyxl\compat
  copying build\lib\openpyxl\compat\singleton.py -> build\bdist.win32\wheel\.\openpyxl\compat
  copying build\lib\openpyxl\compat\strings.py -> build\bdist.win32\wheel\.\openpyxl\compat
  copying build\lib\openpyxl\compat\__init__.py -> build\bdist.win32\wheel\.\openpyxl\compat
  copying build\lib\openpyxl\conftest.py -> build\bdist.win32\wheel\.\openpyxl
  creating build\bdist.win32\wheel\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\base.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\excel.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\namespace.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\nested.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\sequence.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\serialisable.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\slots.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  copying build\lib\openpyxl\descriptors\__init__.py -> build\bdist.win32\wheel\.\openpyxl\descriptors
  creating build\bdist.win32\wheel\openpyxl\drawing
  copying build\lib\openpyxl\drawing\colors.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\connector.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\drawing.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\effect.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\fill.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\geometry.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\graphic.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\image.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\line.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\picture.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\properties.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\relation.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\shape.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\spreadsheet_drawing.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\text.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\xdr.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  copying build\lib\openpyxl\drawing\__init__.py -> build\bdist.win32\wheel\.\openpyxl\drawing
  creating build\bdist.win32\wheel\openpyxl\formatting
  copying build\lib\openpyxl\formatting\formatting.py -> build\bdist.win32\wheel\.\openpyxl\formatting
  copying build\lib\openpyxl\formatting\rule.py -> build\bdist.win32\wheel\.\openpyxl\formatting
  copying build\lib\openpyxl\formatting\__init__.py -> build\bdist.win32\wheel\.\openpyxl\formatting
  creating build\bdist.win32\wheel\openpyxl\formula
  copying build\lib\openpyxl\formula\tokenizer.py -> build\bdist.win32\wheel\.\openpyxl\formula
  copying build\lib\openpyxl\formula\translate.py -> build\bdist.win32\wheel\.\openpyxl\formula
  copying build\lib\openpyxl\formula\__init__.py -> build\bdist.win32\wheel\.\openpyxl\formula
  creating build\bdist.win32\wheel\openpyxl\packaging
  copying build\lib\openpyxl\packaging\core.py -> build\bdist.win32\wheel\.\openpyxl\packaging
  copying build\lib\openpyxl\packaging\extended.py -> build\bdist.win32\wheel\.\openpyxl\packaging
  copying build\lib\openpyxl\packaging\interface.py -> build\bdist.win32\wheel\.\openpyxl\packaging
  copying build\lib\openpyxl\packaging\manifest.py -> build\bdist.win32\wheel\.\openpyxl\packaging
  copying build\lib\openpyxl\packaging\relationship.py -> build\bdist.win32\wheel\.\openpyxl\packaging
  copying build\lib\openpyxl\packaging\workbook.py -> build\bdist.win32\wheel\.\openpyxl\packaging
  copying build\lib\openpyxl\packaging\__init__.py -> build\bdist.win32\wheel\.\openpyxl\packaging
  creating build\bdist.win32\wheel\openpyxl\pivot
  copying build\lib\openpyxl\pivot\cache.py -> build\bdist.win32\wheel\.\openpyxl\pivot
  copying build\lib\openpyxl\pivot\fields.py -> build\bdist.win32\wheel\.\openpyxl\pivot
  copying build\lib\openpyxl\pivot\record.py -> build\bdist.win32\wheel\.\openpyxl\pivot
  copying build\lib\openpyxl\pivot\table.py -> build\bdist.win32\wheel\.\openpyxl\pivot
  copying build\lib\openpyxl\pivot\__init__.py -> build\bdist.win32\wheel\.\openpyxl\pivot
  creating build\bdist.win32\wheel\openpyxl\reader
  copying build\lib\openpyxl\reader\drawings.py -> build\bdist.win32\wheel\.\openpyxl\reader
  copying build\lib\openpyxl\reader\excel.py -> build\bdist.win32\wheel\.\openpyxl\reader
  copying build\lib\openpyxl\reader\strings.py -> build\bdist.win32\wheel\.\openpyxl\reader
  copying build\lib\openpyxl\reader\worksheet.py -> build\bdist.win32\wheel\.\openpyxl\reader
  copying build\lib\openpyxl\reader\__init__.py -> build\bdist.win32\wheel\.\openpyxl\reader
  creating build\bdist.win32\wheel\openpyxl\styles
  copying build\lib\openpyxl\styles\alignment.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\borders.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\builtins.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\cell_style.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\colors.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\differential.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\fills.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\fonts.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\named_styles.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\numbers.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\protection.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\proxy.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\styleable.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\stylesheet.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\table.py -> build\bdist.win32\wheel\.\openpyxl\styles
  copying build\lib\openpyxl\styles\__init__.py -> build\bdist.win32\wheel\.\openpyxl\styles
  creating build\bdist.win32\wheel\openpyxl\utils
  copying build\lib\openpyxl\utils\bound_dictionary.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\cell.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\dataframe.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\datetime.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\escape.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\exceptions.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\formulas.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\indexed_list.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\protection.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\units.py -> build\bdist.win32\wheel\.\openpyxl\utils
  copying build\lib\openpyxl\utils\__init__.py -> build\bdist.win32\wheel\.\openpyxl\utils
  creating build\bdist.win32\wheel\openpyxl\workbook
  copying build\lib\openpyxl\workbook\child.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\defined_name.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  creating build\bdist.win32\wheel\openpyxl\workbook\external_link
  copying build\lib\openpyxl\workbook\external_link\external.py -> build\bdist.win32\wheel\.\openpyxl\workbook\external_link
  copying build\lib\openpyxl\workbook\external_link\__init__.py -> build\bdist.win32\wheel\.\openpyxl\workbook\external_link
  copying build\lib\openpyxl\workbook\external_reference.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\function_group.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\parser.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\properties.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\protection.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\smart_tags.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\views.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\web.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\workbook.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  copying build\lib\openpyxl\workbook\__init__.py -> build\bdist.win32\wheel\.\openpyxl\workbook
  creating build\bdist.win32\wheel\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\cell_range.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\copier.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\datavalidation.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\dimensions.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\drawing.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\filters.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\header_footer.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\hyperlink.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\merge.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\page.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\pagebreak.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\properties.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\protection.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\read_only.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\related.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\table.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\views.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\worksheet.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\write_only.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  copying build\lib\openpyxl\worksheet\__init__.py -> build\bdist.win32\wheel\.\openpyxl\worksheet
  creating build\bdist.win32\wheel\openpyxl\writer
  copying build\lib\openpyxl\writer\etree_worksheet.py -> build\bdist.win32\wheel\.\openpyxl\writer
  copying build\lib\openpyxl\writer\excel.py -> build\bdist.win32\wheel\.\openpyxl\writer
  copying build\lib\openpyxl\writer\strings.py -> build\bdist.win32\wheel\.\openpyxl\writer
  copying build\lib\openpyxl\writer\theme.py -> build\bdist.win32\wheel\.\openpyxl\writer
  copying build\lib\openpyxl\writer\workbook.py -> build\bdist.win32\wheel\.\openpyxl\writer
  copying build\lib\openpyxl\writer\worksheet.py -> build\bdist.win32\wheel\.\openpyxl\writer
  copying build\lib\openpyxl\writer\__init__.py -> build\bdist.win32\wheel\.\openpyxl\writer
  creating build\bdist.win32\wheel\openpyxl\xml
  copying build\lib\openpyxl\xml\constants.py -> build\bdist.win32\wheel\.\openpyxl\xml
  copying build\lib\openpyxl\xml\functions.py -> build\bdist.win32\wheel\.\openpyxl\xml
  copying build\lib\openpyxl\xml\__init__.py -> build\bdist.win32\wheel\.\openpyxl\xml
  copying build\lib\openpyxl\_constants.py -> build\bdist.win32\wheel\.\openpyxl
  copying build\lib\openpyxl\__init__.py -> build\bdist.win32\wheel\.\openpyxl
  running install_egg_info
  running egg_info
  writing openpyxl.egg-info\PKG-INFO
  writing dependency_links to openpyxl.egg-info\dependency_links.txt
  writing requirements to openpyxl.egg-info\requires.txt
  writing top-level names to openpyxl.egg-info\top_level.txt
  reading manifest file 'openpyxl.egg-info\SOURCES.txt'
  writing manifest file 'openpyxl.egg-info\SOURCES.txt'
  Copying openpyxl.egg-info to build\bdist.win32\wheel\.\openpyxl-2.5.10-py3.7.egg-info
  running install_scripts
  error: [Errno 2] No such file or directory: 'LICENCE.rst'

  ----------------------------------------
  Failed building wheel for openpyxl
  Running setup.py clean for openpyxl
Failed to build openpyxl
Installing collected packages: jdcal, et-xmlfile, openpyxl
  Running setup.py install for openpyxl ... done
Successfully installed et-xmlfile-1.0.1 jdcal-1.4 openpyxl-2.5.10

Method iter_cols not available for a read-only worksheet?

It seems like the iter_rows method is there but the iter_cols is missing since it's not set like the iter_rows method:

https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/worksheet/read_only.html#ReadOnlyWorksheet

It should not be very different than iter_rows so it seems odd that it's missing.

Specify row or column to get?

I couldn't find this functionality but if it exists please enlighten me. I want to get all cell values from a specific row or set of rows. For example, all cells in row 1 (without knowing how many cells there are beforehand). Another example, all cells in rows 3 - last, without knowing how many cells in each row or how many rows to the end.

Thanks!

Opening and saving this workbook results in corrupt file

example.xlsx

With this code:

workbook = openpyxl.load_workbook(workbook_file)
workbook.save(workbook_file.replace('.xlsx', '-test.xlsx'))

The resulting file is corrupt. When I click repair, the drawings are lost and all defined names are disabled.

I've found if I edit in workbook.xml and remove all printAreas and printTitles, it will open without showing an error but the drawings are still broken.

openpyxl doesn't accept native python cell alignment formatting

Native python alignment formatting, documentation here, doesn't impact output workbook.

This:
ws['A3'] = '{:>}'.format('comments:')

And this:
ws['A3'] = '{:<}'.format('comments:')

Both result in left aligned output when assigned and then saved to a LibreOffice document.

The only remedy is applying a second set of assignments for the formatting, using the alignment cell attribute, and using the Alignment method, as such:
ws.cell(row, col).alignment = xl.styles.Alignment(horizontal='right')

Read all values from a column

Using iter_rows we can get all values from a specified range but what about something like iter_rows('A:A') that would return all values in column A?

I can submit a PR if this would be helpful.

calculate_dimension() shows wrong end, but I can't set attribute!?

ws.calculate_dimension(). this returns a range that I know is incorrect.

I tried setting it as suggested in the docs[1].
ws.max_row = ws.max_column = None

Returns:
AttributeError: can't set attribute

[1] http://openpyxl.readthedocs.io/en/default/optimized.html?highlight=dimension#worksheet-dimensions

Adding a worksheet table to a write-only Workbook fails

I want to add a table to a write-only workbook, but upon saving the workbook, I get this error:

TypeError: 'WriteOnlyWorksheet' object is not subscriptable

The bug happens in _writer.py in line 269 in the method write_tables(self):

                    row = self.ws[table.ref][0]

The docs don't state that it wouldn't be possible to add tables to a write-only workbook, so I guess it's a bug then.

This is the code I'm using:

wb = openpyxl.Workbook(write_only=True)
ws: openpyxl.worksheet.worksheet.Worksheet = wb.create_sheet()
df = pandas.DataFrame(data)

for row in openpyxl.utils.dataframe.dataframe_to_rows(df, index=False, header=True):
    ws.append(row)

ref = _get_ref(df) # some function to obtain a valid excel reference
table = openpyxl.worksheet.table.Table(displayName='tbl', ref=ref)
table.tableStyleInfo = openpyxl.worksheet.table.TableStyleInfo(name="TableStyleLight1", showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False)
ws.add_table(table)

wb.save('test.xlsx')

Is there any way to work around this?
I'm using openpyxl v3.0.3

how do we change the cell to formatting = text (instead of general or number or dates)

I cant seem to figure out how to apply cell formatting
I need to make some cells in a sheet be text format
(like right clicking on the cell, selecting formatting and then selecting text as the style)

but I cant seem to figure it out from your documentation

I believe its excel built in style # 49??

I'd like to just format an entire column if possible
but I can iterate thru all the cells in the column one at a time if I need to do it per each cell
I just dont know what scripting to use to apply that function.

Write Only Mode Speedup?

Should write only mode be "faster"?

I had an issue with a normal workbook where writing a 20k row 200 column sheet is taking about 20 seconds. About 7 seconds to loop through my data and do the inserts (with ws.cell(xxx)), and about 7 seconds to call save_virtual_workbook to get ready to stream the result in Django.

I switched to write only mode. Looping through my data creation is now slower. It goes from 7 seconds to 13 seconds . save_virtual_workbook is now very fast, only .2 seconds.

But the overall time is basically unchanged. Is there something I am missing, or does it seem like write only mode just shifts performance slowness to a different part of my loop?

New version breaks compatibility

Hi,

I'm trying to read a large excel file and to do it in human time, I need openpyxl to be in read-only mode.

I also need to determine the visibility state of a sheet. Before I used to do something like shee.sheet_state == 'visible' but with the new version of the library it does not work:

AttributeError: 'ReadOnlyWorksheet' object has no attribute 'sheet_state'

I have version 2.6 installed. Version 2.5.14 worked like a charm.

How do I read the excel value instead of formula.

I want to read the actual calculated value from .xlsx file but get the formula instead of value.
#For getting value i used.
cell.value

Throw warning upon reaching Excel row limit

If you make an Excel file containing more than 1048576 rows and then open it in Excel, Excel will show a warning about "corrupted data" and "fix" the file, namely by only showing the first 1048576 rows. Information in later rows is hidden, and if the user saves the "fixed" version, that information is lost. Currently, openpyxl writes these not-technically-compatible large files silently; I think issuing a warning would help prevent confusion. Thanks!

("Why are you making these monstrously large spreadsheets?" you ask; I confess I don't have a really good answer, but I'm probably not the only one.)

Syntax error on new version

C(o35) [phas@revenge backend]$ bpython
bpython version 0.18 on top of Python 3.5.9 /home/phas/virtualenvs/o35/bin/python3.5
>>> import openpyxl
Traceback (most recent call last):
  File "<input>", line 1, in <module>
    import openpyxl
  File "<frozen importlib._bootstrap>", line 968, in _find_and_load
  File "<frozen importlib._bootstrap>", line 957, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 664, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 634, in _load_backward_compatible
  File "/home/phas/virtualenvs/o35/lib/python3.5/site-packages/openpyxl-3.0.2-py3.5.egg/openpyxl/__init__.py", line 6, in <module>
    from openpyxl.workbook import Workbook
  File "<frozen importlib._bootstrap>", line 968, in _find_and_load
  File "<frozen importlib._bootstrap>", line 957, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 664, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 634, in _load_backward_compatible
  File "/home/phas/virtualenvs/o35/lib/python3.5/site-packages/openpyxl-3.0.2-py3.5.egg/openpyxl/workbook/__init__.py", line 4, in <module>
    from .workbook import Workbook
  File "<frozen importlib._bootstrap>", line 968, in _find_and_load
  File "<frozen importlib._bootstrap>", line 957, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 664, in _load_unlocked
  File "<frozen importlib._bootstrap>", line 634, in _load_backward_compatible
  File "/home/phas/virtualenvs/o35/lib/python3.5/site-packages/openpyxl-3.0.2-py3.5.egg/openpyxl/workbook/workbook.py", line 7, in <module>
    from openpyxl.worksheet.worksheet import Worksheet
  File "<frozen importlib._bootstrap>", line 968, in _find_and_load
  File "<frozen importlib._bootstrap>", line 953, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 896, in _find_spec
  File "<frozen importlib._bootstrap_external>", line 1171, in find_spec
  File "<frozen importlib._bootstrap_external>", line 1147, in _get_spec
  File "<frozen importlib._bootstrap_external>", line 1128, in _legacy_get_spec
  File "<frozen importlib._bootstrap>", line 444, in spec_from_loader
  File "<frozen importlib._bootstrap_external>", line 565, in spec_from_file_location
  File "/home/phas/virtualenvs/o35/lib/python3.5/site-packages/openpyxl-3.0.2-py3.5.egg/openpyxl/worksheet/worksheet.py", line 392
    return f"{get_column_letter(min_col)}{min_row}:{get_column_letter(max_col)}{max_row}"
                                                                                        ^
SyntaxError: invalid syntax

openpyxl: function CellIsRule not found

I am opening an Excel file and applying some conditional formatting:

from openpyxl import *
from openpyxl.formatting.rule import CellIsRule

os.chdir(r'C:\Users\myfolder')

wb= load_workbook('myfile.xlsx')
ws = wb.active

#Conditional formatting
ws.conditional_formatting.add('S3:S89', formatting.CellIsRule(operator='equal', formula=['1'], font='#FF0000')) #Red

However, this generates an error:

AttributeError                            Traceback (most recent call last)
<ipython-input-190-fb94e6065444> in <module>()
---> 10 ws.conditional_formatting.add('S3:S89', formatting.CellIsRule(operator='equal', formula=['1'], font='#FF0000')) #Red

AttributeError: module 'openpyxl.formatting' has no attribute 'CellIsRule'

I am using openpyxl version 2.4.8 with Anaconda:
3.6.3 |Anaconda, Inc.| (default, Oct 15 2017, 03:27:45) [MSC v.1900 64 bit (AMD64)]

save_workbook raises TypeError in 3.0.2

This code works without error in 2.6.2. In 3.0.2 it raises TypeError. (it fails in the same way with a real Workbook, the empty Workbook is just the simplest way to repro).

import tempfile
from openpyxl.workbook.workbook import Workbook
from openpyxl.writer.excel import save_workbook


with tempfile.NamedTemporaryFile() as tf:
    save_workbook(Workbook(), tf.name)

$ python save.py
Traceback (most recent call last):
  File "save.py", line 7, in <module>
    save_workbook(Workbook(), tf.name)
  File "/site/venv/lib/python3.7/site-packages/openpyxl/writer/excel.py", line 293, in save_workbook
    writer.save()
  File "/site/venv/lib/python3.7/site-packages/openpyxl/writer/excel.py", line 275, in save
    self.write_data()
  File "/site/venv/lib/python3.7/site-packages/openpyxl/writer/excel.py", line 75, in write_data
    self._write_worksheets()
  File "/site/venv/lib/python3.7/site-packages/openpyxl/writer/excel.py", line 215, in _write_worksheets
    self.write_worksheet(ws)
  File "/site/venv/lib/python3.7/site-packages/openpyxl/writer/excel.py", line 200, in write_worksheet
    writer.write()
  File "/site/venv/lib/python3.7/site-packages/openpyxl/worksheet/_writer.py", line 354, in write
    self.write_top()
  File "/site/venv/lib/python3.7/site-packages/openpyxl/worksheet/_writer.py", line 98, in write_top
    self.write_properties()
  File "/site/venv/lib/python3.7/site-packages/openpyxl/worksheet/_writer.py", line 60, in write_properties
    self.xf.send(props.to_tree())
  File "/site/venv/lib/python3.7/site-packages/openpyxl/worksheet/_writer.py", line 294, in get_stream
    xf.write(el)
  File "src/lxml/serializer.pxi", line 1652, in lxml.etree._IncrementalFileWriter.write
TypeError: got invalid input value of type <class 'xml.etree.ElementTree.Element'>, expected string or Element

python 3.7.5 on Ubuntu 18.04 with the following installed:

et-xmlfile==1.0.1
jdcal==1.4.1
openpyxl==3.0.2

unhandled exception using load_workbook: negative seek value in zipfile

It would be nice if we could do this, so for example we can use argparse to handle the opening of the file.

can't open excel file with openpyxl.

Am trying to create an instance where I send grouped messages to a group of numbers using python. Am getting the error below.

here is the code that gives me the error:

#######################################
from ipaddress import IPv4Address
from pyairmore.request import AirmoreSession
from pyairmore.services.device import DeviceService
from pyairmore.services.messaging import MessagingService
from openpyxl import load_workbook

ip = IPv4Address("192.168.0.3")
session = AirmoreSession(ip)
service = DeviceService(session)
details = service.fetch_device_details()

service = MessagingService(session)

filepath = r"C:\Users\User\PycharmProjects\bulk\contacts.xlsx"

column = "A"
length = 3
workbook = load_workbook(filename="contacts.xlsx", read_only=True)
worksheet = wb.active
phone_numbers = []
for i in range(length):
cell = "{}{}".format(column, i + 1)
number = worksheet[cell].value
if number != "" or number is not None:
phone_numbers.append(str(number))
message = "50% discount at Lorem Ipsum Co. tomorrow."
for number in phone_numbers:
service.send_message(number, message)
################################################

C:\Users\User\PycharmProjects\bulk\venv\Scripts\python.exe C:/Users/User/PycharmProjects/bulk/bulk.py
Traceback (most recent call last):
File "C:/Users/User/PycharmProjects/bulk/bulk.py", line 18, in
workbook = load_workbook(filename="contacts.xlsx", read_only=True)
File "C:\Users\User\PycharmProjects\bulk\venv\lib\site-packages\openpyxl\reader\excel.py", line 311, in load_workbook
data_only, keep_links)
File "C:\Users\User\PycharmProjects\bulk\venv\lib\site-packages\openpyxl\reader\excel.py", line 126, in init
self.archive = _validate_archive(fn)
File "C:\Users\User\PycharmProjects\bulk\venv\lib\site-packages\openpyxl\reader\excel.py", line 98, in _validate_archive
archive = ZipFile(filename, 'r')
File "C:\Users\User\AppData\Local\Programs\Python\Python37-32\lib\zipfile.py", line 1222, in init
self._RealGetContents()
File "C:\Users\User\AppData\Local\Programs\Python\Python37-32\lib\zipfile.py", line 1289, in _RealGetContents
raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

Deprecation warning: defusedxml.lxml is no longer supported ...

Hello,

It seems that openpyxl uses deprecated defusedxml.lxml.

Python version: 3.7.4
defusedxml version: 0.6.0
openpyxl version: 2.6.3

  File "{myvenvlocation}/lib/python3.7/site-packages/pandas/io/excel/_openpyxl.py", line 19, in __init__
    from openpyxl.workbook import Workbook
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/__init__.py", line 6, in <module>
    from openpyxl.workbook import Workbook
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/workbook/__init__.py", line 5, in <module>
    from .workbook import Workbook
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/workbook/workbook.py", line 8, in <module>
    from openpyxl.worksheet.worksheet import Worksheet
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/worksheet/worksheet.py", line 30, in <module>
    from openpyxl.cell import Cell, MergedCell
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/cell/__init__.py", line 4, in <module>
    from .cell import Cell, WriteOnlyCell, MergedCell
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/cell/cell.py", line 46, in <module>
    from openpyxl.utils.inference import (
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/utils/inference.py", line 10, in <module>
    from openpyxl.styles import numbers
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/styles/__init__.py", line 5, in <module>
    from .alignment import Alignment
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/styles/alignment.py", line 6, in <module>
    from openpyxl.descriptors import Bool, MinMax, Min, Alias, NoneSet
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/descriptors/__init__.py", line 5, in <module>
    from .sequence import Sequence
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/descriptors/sequence.py", line 5, in <module>
    from openpyxl.xml.functions import Element
  File "{myvenvlocation}/lib/python3.7/site-packages/openpyxl/xml/functions.py", line 31, in <module>
    from defusedxml.lxml import fromstring as _fromstring, tostring
  File "{myvenvlocation}/lib/python3.7/site-packages/defusedxml/lxml.py", line 29, in <module>
    stacklevel=2,
DeprecationWarning: defusedxml.lxml is no longer supported and will be removed in a future release.

Best regards

Width and height units are not documented

I wasn't able to find any clue as to what values are expected in openpyxl.worksheet.dimensions.ColumnDimension. Inches? Pixels? mm? Would you please add a note to the docs?

Partial Superscript

Is there a way to superscript certain characters of a cell

XLSM Macro Button Design

Hi, when you have a button linked to a macro, when you save, the file comes with the macro but the button along with the link disappears.

After researching I came across with a solved problem. I adapted the original code to what I think it's the answer. Cheers

#Open the xlsm template for reading
workbook = openpyxl.load_workbook(filename='/home/xxx/templates/template.xlsm', read_only=False, keep_vba=True)
worksheet = workbook.get_sheet_by_name('Sheet1')

#Writing values
worksheet['C6'] = 1
worksheet['C7'] = 2
worksheet['C8'] = 3
worksheet['C9'] = 4

#Saving the output file as XLSX not XLSM (yet)
workbook.save(path_project + '/' + client + '.xlsx')

PAD = os.getcwd()

#Unzip the XLSM and XLSX to a temp folder. Did you know that?
with zipfile.ZipFile('/home/xxx/templates/template.xlsm', 'r') as z:
     z.extractall('./xlsm/')

with zipfile.ZipFile(path_project + '/' + client + '.xlsx', 'r') as z:
     z.extractall('./xlsx/')

#Copying the button style from the template to the output file.
copyfile('./xlsm/xl/drawings/drawing1.xml','./xlsx/xl/drawings/drawing1.xml')

#Zipping the whole thing as .zip
z = zipfile.ZipFile(path_project + '/' + client + '.zip', 'w')

#Directory change
os.chdir('./xlsx')

for root, dirs, files in os.walk('./'):
       for file in files:
            z.write(os.path.join(root, file))

z.close()

#clean and rename extension
os.chdir(PAD)
rmtree('./xlsm/')
rmtree('./xlsx/')
os.remove(path_project + '/' + client + '.xlsx')
os.rename(path_project + '/' + client + '.zip', path_project + '/' + client + '.xlsm')

Excel slicers support?

Hi,
I have an Excel file which is a PivotTable. The problem is when I open xlts file and add data and save it as a new xlsx file, the library is removing all the slicers from the file. Is there any way to fix this issue and have the sliders?

Comment width and height is not user

Attributes width and height are not used when the style for the Comment is created and defaults are used.

I will provide a pull-request with a fix for it.

openpyxl using too much memory

Hi
I am aware of read_only and write_only mode so wanted suggestions on how to reduce memory footprint for the library.

I have a 30-35 MB file, which I need to read and process and write back the results in same excel.
For doing this task, I cannot use any of read_only or write_only mode; It seems pre 2.4.0 versions had use_iterators=True option, to optimize this.

Do you have any suggestion to reduce memory footprint when I need to read+process+write the same file?

Old dates shifting a day?

With Openpyxl 2.4.2 and Python 3.5.2, pre-1900 dates shift a day:

>>> import openpyxl
>>> from datetime import datetime
>>> sheet = openpyxl.Workbook().active
>>> sheet.append([datetime(1756, 1, 27)]) # Mozart's birthday
>>> next(sheet.rows)[0].value
datetime.datetime(1756, 1, 26, 0, 0)

Notice how the date went from the 27th to the 26th.

Timezone awareness doesn't seem to make any difference:

>>> from datetime import timezone
>>> sheet = openpyxl.Workbook().active
>>> sheet.append([datetime(1756, 1, 27, tzinfo=timezone.utc)])
>>> next(sheet.rows)[0].value
datetime.datetime(1756, 1, 26, 0, 0)

This only happens for pre-1990 dates:

>>> sheet = openpyxl.Workbook().active
>>> sheet.append([datetime(1899, 1, 27)])
>>> next(sheet.rows)[0].value
datetime.datetime(1899, 1, 26, 0, 0)
>>> sheet = openpyxl.Workbook().active
>>> sheet.append([datetime(1900, 1, 27)])
>>> next(sheet.rows)[0].value
datetime.datetime(1900, 1, 27, 0, 0)

Help? Any suggestions? Any hints on how to correct this?

Date Time Picker ActiveX control lost after using openpyxl

Hi, I noticed that Microsoft Date Time Picker Control will disappeared after editing a existing file using openpyxl.
My file has a few columns that use Date time picker control to enable manual date selection, and the corresponding vba code is in link below: DatePickerToCells

After insert a new line into the file with openpyxl, I found DTPicker1 control disappeared.
It appears that it does not matter whatever content you insert, DTPicker1 control will always disappear.
The edited file will restore to normal if I manually insert Date Time Picker again.
So i'm pretty sure somehow openpyxl have removed the DTPicker1 ActiveX control.

problem about row auto_size

Dear author:
recently i have problem about how to auto rows' height.
At first, i try to calulate the size of string content,textwrap this and set fixed height, it is not a good way to come true my target.
Then, I use following code try to come true.

   self.ws.row_dimensions[int(row)].auto_size = True
   self.ws.row_dimensions[int(row)].bestFit = True
   self.ws.row_dimensions[int(row)].collapsed = False

as following i did, insert a row list data(long data) in excel and the result was unexpected, i found that there are three rows in auto_size and two rows not in auto_size, i did not found the problem.
could you help me to find out? thank you

Hidden Columns not hiding

When inserting a column, hidden columns do not shift and will stay static therefore I created a method which would shift all the hidden columns to the right when a column is inserted.

The problem is that inside IPython it states that the shifted column are still hidden but when saving the workbook the column is not actual hidden and in fact all the columns become unhidden.

def fixHidden(startCol):
    for x in range(ws.max_column, startCol, -1):
        ws.column_dimensions[get_column_letter(x)].hidden = ws.column_dimensions[get_column_letter(x - 1)].hidden
    ws.column_dimensions[get_column_letter(startCol)].hidden = False

Delete rows corrupts the file

I have an extract from SAP file in excel and I'm simply trying to delete few initial rows, here's the script-

import openpyxl as xl
wb = xl.load_workbook("/path/to/file")
ws = wb.worksheets[0]
ws.delete_rows(0, 3)
wb.save("/path/to/file")

Here's the error thrown by excel-

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error042400_03.xml</logFileName><summary>Errors were detected in file 'C:\Users\sjariwala\Documents\ABB\data\preprocessedviews\INABB PG view Jun18.xlsx'</summary><removedParts><removedPart>Replaced Part: /xl/worksheets/sheet1.xml part with XML error.  Load error. Line 1, column 0.</removedPart></removedParts></recoveryLog>

something lost when save .xlsx file

When I open .xlsx file with openpyxl and save it. I found some data lost, such as enum

In python using openpyxl i need to copy a row without format and formulas loss

need to copy a row values with the format, formulas, etc,. and then update the row with new text without any loss of validation, formulas, format and etc,...

on excel have three sheet: employee_list salary personal_details

first sheet have the employee list based on the employee_list other sheet have the drop_down. example i have a sheet to save the salary details. at the sheet i just copy last month salary and update the new month salary with old data.

|=======|=========|==========|
|name |position | YOJ |
|=======|=========|==========|
|manoj |Engineer | 2015 |
|mathan |Manager | 2000 |
|=======|=========|==========|
copy a row with the old row formulas and formats and update the new month salary.

|=======|=========|==========|
|name |month | salary |
|=======|=========|==========|
|manoj |may | 25000 |
|mathan |june | 50000 |
|=======|=========|==========|

Confusing name

Is this the source code of this library?

XML (Styles) Catastrophic failure after modifying XLSX

Adding a couple of simple new workbook summaries to a simple excel file and try to open it. Getting this error:

Removed Part: /xl/styles.xml part with XML error. (Styles) Catastrophic failure Line 1, column 457.

Unfortunately the file contains proprietary information so I can't share it. I'm not sure how to see what's in col 457 of line 1.

load workbook ValueError Duplicate position 0.0

Hi
I'm trying to open a xlsx file but I've this error :
raise ValueError("Duplicate position {0}".format(stop.position))
ValueError: Duplicate position 0.0
I don't understand what does it mean and I haven't found an issue explained for this error. Here is my code in file gestion_fichiers_excel :

**from openpyxl import load_workbook
from openpyxl import Workbook

def loadfile():
workbook = load_workbook(filename="Feuille_decisions.xlsx")
return workbook**
def get_decisions(workbook):
num_entreprise=workbook.active["B4"].value
prix_prod1=workbook.active["B7"].value
prix_prod2=workbook.active["C7"].value
list_decisions=[num_entreprise,prix_prod1,prix_prod2]
return list_decisions

And another file which calls gestion_fichiers_excel :

**import os
import tkinter as tk
import tkinter.ttk
from gestion_base import *
from gestion_fichiers_excel import ***

class PageDecisions(tk.Frame):
def init(self, master):
self.workbook=loadfile()
self.list_decisions=get_decisions(self.workbook)

Thanks for your help

Mocking of timestamps

I have asked this question also on Stack Overflow, but perhaps this is a better location to ask this.

I have created a function that writes a list of lists of strings to an Excel file, and now I want to write a unittest for it, in which I compare the output file with a reference output file. However, when I save it, the real timestamp is stored in the file, so when I compare the two files (which are binary), they are not the same. How can I mock the timestamp in the Excel file creation, such that I can compare a written Excel file with a reference file?

This testcase indicates what I want to achieve. Without the sleep(2), it works. When I open the generated Excel file using TextWrangle, I can see in docProps/core.xml the following tag: <dcterms:modified xsi:type="dcterms:W3CDTF">2018-09-12T13:44:57Z</dcterms:modified>. This is the timestamp I want to mock.

def test_that_same_input_leads_to_same_file(self):
    def write_xlsx(filename: str):
        from openpyxl import Workbook
        workbook = Workbook()
        worksheet = workbook.active
        worksheet.title = 'Matrix'
        for line in content:
            worksheet.append(line)
        workbook.save(filename)
        return filename
    content = [['a', 'b', 'c', 'd'], [None, 1, 2, 3], [1, 2, 3, 4]]
    file_1 = write_xlsx('file_1.xlsx')
    sleep(2)
    file_2 = write_xlsx('file_2.xlsx')
    import filecmp
    self.assertTrue(filecmp.cmp(file_1, file_2))

If I perform the function in a patch wrapper, then this function passes. However, when I then don't create file_2, and compare it from a previous run, it still fails. It takes one timestamp for both files in a single run, but another timestamp in another run.

from django.utils import timezone
MY_DATE = timezone.datetime(year=2017, month=1, day=2, hour=3, minute=4, second=5, tzinfo=timezone.utc)
with patch('time.time', return_value=MY_DATE.timestamp()):
    # Create Excel file here

open and save excel file(.xlxs) removing data validation (type list )

When I open an excel file with write mode and save . It is removing the existing data validation (Type list data validation) from the file and I am getting warning Data Validation extension is not supported and will be removed warn(msg). Actually I wanted to append new row to the file as a list. I don't want to change or add new validation to existing file. Other than type list data validation every other validations it maintains after save

Error ： All strings must be XML compatible: Unicode or ASCII when saving excel

Hello, I added and wrote tens of sheets into excel with openpyxl, when the number of sheets exceeded certain number, like 20, I got error listed as follow:
File "F:\work\R practice\python trainning\BeautifulSoup-GeshangRead.py", line 148, in
excelworkbook.save(unicode('F:/work/pobo/trading NG/SettleStatementReading/licairead/HedgeFundNetvalues.xlsx','utf-8'))
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\openpyxl\workbook\workbook.py", line 263, in save
save_workbook(self, filename)
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\openpyxl\writer\excel.py", line 239, in save_workbook
writer.save(filename, as_template=as_template)
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\openpyxl\writer\excel.py", line 222, in save
self.write_data(archive, as_template=as_template)
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\openpyxl\writer\excel.py", line 70, in write_data
archive.writestr(ARC_APP, write_properties_app(self.workbook))
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\openpyxl\writer\workbook.py", line 66, in write_properties_app
SubElement(vector, '{%s}lpstr' % VTYPES_NS).text = '%s' % ws.title
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\lxml\etree.pyd", line 1031, in lxml.etree._Element.text.set (src\lxml\lxml.etree.c:51616)
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\lxml\etree.pyd", line 711, in lxml.etree._setNodeText (src\lxml\lxml.etree.c:22990)
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\lxml\etree.pyd", line 699, in lxml.etree._createTextNode (src\lxml\lxml.etree.c:22850)
File "F:\Program Files (x86)\anaconda4.0\Lib\site-packages\lxml\etree.pyd", line 1439, in lxml.etree._utf8 (src\lxml\lxml.etree.c:30138)

ValueError: All strings must be XML compatible: Unicode or ASCII, no NULL bytes or control characters

2.6 Breaks reading files?

Just calling load_workbook vs a workbook made in 2.5...


Error
Traceback (most recent call last):
  File "/usr/lib/python3.6/zipfile.py", line 1194, in _RealGetContents
    endrec = _EndRecData(fp)
  File "/usr/lib/python3.6/zipfile.py", line 264, in _EndRecData
    fpin.seek(0, 2)
io.UnsupportedOperation: seek

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/utils.py", line 1706, in dictify_file
    wb = load_workbook(file)
  File "/env/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 311, in load_workbook
    data_only, keep_links)
  File "/env/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 126, in __init__
    self.archive = _validate_archive(fn)
  File "/env/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 98, in _validate_archive
    archive = ZipFile(filename, 'r')
  File "/usr/lib/python3.6/zipfile.py", line 1131, in __init__
    self._RealGetContents()
  File "/usr/lib/python3.6/zipfile.py", line 1196, in _RealGetContents
    raise BadZipFile("File is not a zip file")
zipfile.BadZipFile: File is not a zip file

UserWarning: Slicer List extension is not supported and will be removed

openpyxl\worksheet_reader.py:296: UserWarning: Slicer List extension is not supported and will be removed message when reading the file

BadZipFile when Pillow is installed

When I use openpyxl to open a Workbook it throws BadZipFile: Bad CRC-32 for file 'xl/media/image2.jpg' if Pillow is installed in the same environment. It works if I uninstall Pillow.

BadZipFile                                Traceback (most recent call last)
<ipython-input-2-f0a0b38a8395> in <module>
      1 workbook_path = "scripts/Elaboração de Orçamento.xlsm"
----> 2 wb = openpyxl.load_workbook(workbook_path)
      3 wb.guess_types = False
      4 
      5 sheet = wb.get_sheet_by_name("Base Orçamentos")

~\.virtualenvs\project-nNknfD7x\lib\site-packages\openpyxl\reader\excel.py in load_workbook(filename, read_only, keep_vba, data_only, keep_links)
    312     reader = ExcelReader(filename, read_only, keep_vba,
    313                         data_only, keep_links)
--> 314     reader.read()
    315     return reader.wb

~\.virtualenvs\project-nNknfD7x\lib\site-packages\openpyxl\reader\excel.py in read(self)
    277         self.read_theme()
    278         apply_stylesheet(self.archive, self.wb)
--> 279         self.read_worksheets()
    280         self.parser.assign_names()
    281         if not self.read_only:

~\.virtualenvs\project-nNknfD7x\lib\site-packages\openpyxl\reader\excel.py in read_worksheets(self)
    252             drawings = rels.find(SpreadsheetDrawing._rel_type)
    253             for rel in drawings:
--> 254                 charts, images = find_images(self.archive, rel.target)
    255                 for c in charts:
    256                     ws.add_chart(c, c.anchor)

~\.virtualenvs\project-nNknfD7x\lib\site-packages\openpyxl\reader\drawings.py in find_images(archive, path)
     50         if dep.Type == IMAGE_NS:
     51             try:
---> 52                 image = Image(BytesIO(archive.read(dep.target)))
     53             except OSError:
     54                 msg = "The image {0} will be removed because it cannot be read".format(dep.target)

C:\Python37\Lib\zipfile.py in read(self, name, pwd)
   1427         """Return file bytes for name."""
   1428         with self.open(name, "r", pwd) as fp:
-> 1429             return fp.read()
   1430 
   1431     def open(self, name, mode="r", pwd=None, *, force_zip64=False):

C:\Python37\Lib\zipfile.py in read(self, n)
    883             self._offset = 0
    884             while not self._eof:
--> 885                 buf += self._read1(self.MAX_N)
    886             return buf
    887 

C:\Python37\Lib\zipfile.py in _read1(self, n)
    987         if self._left <= 0:
    988             self._eof = True
--> 989         self._update_crc(data)
    990         return data
    991 

C:\Python37\Lib\zipfile.py in _update_crc(self, newdata)
    915         # Check the CRC if we're at the end of the file
    916         if self._eof and self._running_crc != self._expected_crc:
--> 917             raise BadZipFile("Bad CRC-32 for file %r" % self.name)
    918 
    919     def read1(self, n):

BadZipFile: Bad CRC-32 for file 'xl/media/image2.jpg'

Altering a table heading results in corrupted file

When loading an existing file with a table and header already defined. Altering one of the heading cells will break the file and require repair.

I have reproduced it using a simple test here:

from openpyxl import load_workbook
# load workbook with a table already defined
wb = load_workbook(r"C:\scripts\table_fail.xlsx")
ws = wb.active
# change a table heading
ws["B1"] = "new heading"
# save new file that is now corrupted
wb.save(r"c:\scripts\table_fail.out.xlsx")

table_fail.out.xlsx
table_fail.xlsx

TypeError: init() got an unexpected keyword argument 'id'

Workbook is not opening in Newer Version throwing an error "TypeError: init() got an unexpected keyword argument 'id' ", although when I downgraded the version, it worked fine. Look into the issue.

Hope it helps.

"\x0b" character not handled in ws.append()

Hi there.

openpyxl.__version__ == '2.5.9'

In:

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

row = ["test data", "\x0b"]
ws.append(row)

Out:

IllegalCharacterError

Alternatively: openpyxl.utils.exceptions.IllegalCharacterError

This case is present on:

Python 3.6.0 Windows 7 Professional
Python 3.6.0 Windows Server 2012
Python 3.6.5 Windows 10 Home

Thanks,
-Em

close() of read only workbook doesn't allow me to delete the file

When I run this code:

import openpyxl
import os
import shutil
import tempfile

old_path = "C:\\Users\\x\\spectral_work\\2019-GRESB-Asset-Spreadsheet.xlsx"
excel_path = "C:\\Users\\x\\spectral_work\\my_book.xlsx"

try:
    shutil.copy2(old_path, excel_path)
    gresb_book = openpyxl.load_workbook(excel_path, read_only=True)

    building_characteristics_sheet = gresb_book.worksheets[3]
    row = 8
    while building_characteristics_sheet.cell(row, 3).value is not None:
        row += 1

    gresb_book.close()
    
finally:
    os.remove(excel_path)

I get this error:

PermissionError: [WinError 32] The process cannot access the file because it is being used by another process: 'C:\\Users\\x\\spectral_work\\my_book.xlsx'

Do you have any ideas of how I could fix this?

If I remove the read_only=True argument and the .close() statement it works. Furthermore if I remove the for loop it runs fine without errors. However after accessing the workbook it doesn't allow me to delete the copy.

Cheers,
Dylan.