CODEWITHSUNDEEP
pythonxlsxopenpyxlimport-from-excel
Praveen kumar
Praveen kumar

Reputation: 607

workbook.save() of python's openpyxl module is not working

I was trying to open a XLSX file using openpyxl module. Following is my script:-

from openpyxl import load_workbook

print "Going to execute the script"
workbook = load_workbook("FileName.xlsx")
worksheet = workbook.get_sheet_by_name("Sheet01")
worksheet['B8'] = "Customer07"
workbook.save("FileName.xlsx")
print "End of script execution"

When I execute this script I get the following error:-

/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/xml/__init__.py:15: UserWarning: The installed version of lxml is too old to be used with openpyxl
  warnings.warn("The installed version of lxml is too old to be used with openpyxl")
Going to execute the script
/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/workbook/names/named_range.py:121: UserWarning: Discarded range with reserved name
  warnings.warn("Discarded range with reserved name")
Traceback (most recent call last):
  File "check.py", line 11, in <module>
    workbook.save("FileName.xlsx")
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/workbook/workbook.py", line 296, in save
    save_workbook(self, filename)
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/writer/excel.py", line 191, in save_workbook
    writer.save(filename, as_template=as_template)
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/writer/excel.py", line 174, in save
    self.write_data(archive, as_template=as_template)
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/writer/excel.py", line 85, in write_data
    self._write_worksheets(archive)
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/writer/excel.py", line 111, in _write_worksheets
    write_worksheet(sheet, self.workbook.shared_strings,
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/writer/worksheet.py", line 299, in write_worksheet
    xf.write(comments)
  File "/usr/lib/python2.6/contextlib.py", line 34, in __exit__
    self.gen.throw(type, value, traceback)
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/xml/xmlfile.py", line 42, in element
    yield
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/writer/worksheet.py", line 276, in write_worksheet
    hf = write_header_footer(worksheet)
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/writer/worksheet.py", line 172, in write_header_footer
    header = worksheet.header_footer.getHeader()
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/worksheet/header_footer.py", line 150, in getHeader
    t.append(self.left_header.get())
  File "/usr/lib/python2.6/site-packages/openpyxl-2.2.0-py2.6.egg/openpyxl/worksheet/header_footer.py", line 90, in get
    t.append('&%d' % self.font_size)
TypeError: %d format: a number is required, not str

And after this when I open "FileName.xlsx", pop box appears with message "Excel found unreadable comment in 'FileName.xlsx'. Do you want recover the contents of this workbook? If you trust the source of this workbook, click yes". Regardless of whatever button I click, the workbook is empty.

If I comment the following line in script,

workbook.save("FileName.xlsx")

the script runs without any error but modifications are not saved in workbook. Please help me out with this.

Upvotes: 0

Views: 4469

Answers (2)

mkultra
mkultra

Reputation: 321

I had this same problem last night, I thought that my Excel file did not have a header but in fact it did have a header and a footer (which you may be able to see when you push "Print Preview")

Anyways, this is my hacky fix:

I commented out this line of code and added the pass line

"<path-to-library>/openpyxl/worksheet/header_footer.py", line 90, in get
Line 89-91 below

if self.font_size:
    # t.append('&%d' % self.font_size) # commented out this line
    pass # added this line "pass"

This "fix" will only affect the font_size of your headers and footers (not a particularly huge deal if you ask me). But you may find that you would like to just have these headers and footers store to a variable so that you can use them in some other way in the future of your program.

If that is the case, I have provided some code below to extract these headers and footers into respective dictionaries (if they exist).

NOTE: This might also be helpful in determining whether your document has header and footers.

After wb.save(<path/to/file>):

header_dict = {}
if ws.header_footer.hasHeader():
    header_dict['center'] = ws.header_footer.center_header.text
    header_dict['left'] = ws.header_footer.left_header.text
    header_dict['right'] = ws.header_footer.right_header.text

footer_dict = {}
if ws.header_footer.hasFooter():
    footer_dict['center'] = ws.header_footer.center_footer.text
    footer_dict['left'] = ws.header_footer.left_footer.text
    footer_dict['right'] = ws.header_footer.right_footer.text

return header_dict, footer_dict

And this header_footer module may provide a way to remove these from the Excel file but more help can be found in the documentation OpenPyXL - Headers and Footers

Upvotes: 0

user3228763
user3228763

Reputation: 11

Try putting worksheet['B8'].value = "Customer07" instead worksheet['B8'] = "Customer07"

Upvotes: 0

Related Questions