How do I append rows to an excel spreadsheet with openpyxl and then save the excel file?

Question

I'm writing a script that adds new data to an existing excel spreadsheet. Currently, the spreadsheet is 500k+ rows long. I've been using openpyxl to open the spreadsheet as xlsxwriter doesn't currently have any editing capabilities. However, when I use the provided append() method as explained in this answer to a similar problem.

I'm currently running Python 3.7.3 with openpyxl 2.6.2 on a Windows 7 computer.

from openpyxl import load_workbook, Workbook

records = [object list] #this is just a list of objects

file_name = 'existing_excel_file.xlsx'

excel_workbook = load_workbook(file_name, read_only=False) 
worksheet = excel_workbook.active

row_list = []
for record in records:
    row_number += 1
    row_list.append([
        str(record.weekno),
        str(record.date1),
        str(record.code),
        str(record.customer),
        str(record.date2
    ])

for row in row_list:
   worksheet.append(row)

excel_workbook.save(file_name )

Obviously, it's supposed to save the file with the appended lines.

append() is working alright, but when I try to execute the save() method, I receive this error:

ValueError: I/O operation on closed file.

EDIT: At the suggestion of @CharlieClark, I grabbed the full traceback. I also noticed that there is a MemoryError that I simply didn't notice before (careless, I know) which might be the source of my issue; until this is resolved, I'm researching how to increase the memory being used in openpyxl as I'm sure that's probably the key. Regardless, here's the dump. Warning: it's a big hairy traceback.

Traceback (most recent call last):
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 836, in _get_writer
    yield file.write
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 777, in write
    short_empty_elements=short_empty_elements)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 942, in _serialize_xml
    short_empty_elements=short_empty_elements)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 942, in _serialize_xml
    short_empty_elements=short_empty_elements)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 942, in _serialize_xml
    short_empty_elements=short_empty_elements)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 935, in _serialize_xml
    write(" %s=\"%s\"" % (qnames[k], v))
MemoryError

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "manage.py", line 21, in <module>
    main()
  File "manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "C:\Users\davidm\projects\web_admin\venv\lib\site-packages\django\core\management\__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "C:\Users\davidm\projects\web_admin\venv\lib\site-packages\django\core\management\__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "C:\Users\davidm\projects\web_admin\venv\lib\site-packages\django\core\management\base.py", line 316, in run_from_argv
    self.execute(*args, **cmd_options)
  File "C:\Users\davidm\projects\web_admin\venv\lib\site-packages\django\core\management\base.py", line 353, in execute
    output = self.handle(*args, **options)
  File "C:\Users\davidm\projects\web_admin\web_admin\ezcorp\management\commands\codes.py", line 183, in handle

  File "C:\Users\davidm\projects\web_admin\venv\lib\site-packages\openpyxl\workbook\workbook.py", line 397, in save
    save_workbook(self, filename)
  File "C:\Users\davidm\projects\web_admin\venv\lib\site-packages\openpyxl\writer\excel.py", line 294, in save_workbook
    writer.save()
  File "C:\Users\davidm\projects\web_admin\venv\lib\site-packages\openpyxl\writer\excel.py", line 276, in save
    self.write_data()
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\openpyxl\writer\excel.py", line 76, in write_data
    self._write_worksheets()
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\openpyxl\writer\excel.py", line 216, in _write_worksheets
    self.write_worksheet(ws)
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\openpyxl\writer\excel.py", line 201, in write_worksheet
    writer.write()
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\openpyxl\worksheet\_writer.py", line 358, in write
    self.close()
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\openpyxl\worksheet\_writer.py", line 366, in close
    self.xf.close()
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\openpyxl\worksheet\_writer.py", line 297, in get_stream
    pass
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\contextlib.py", line 119, in __exit__
    next(self.gen)
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\et_xmlfile\xmlfile.py", line 50, in element
    self._write_element(el)
  File "C:\Users\davidm\projects\rdm_admin\venv\lib\site-packages\et_xmlfile\xmlfile.py", line 77, in _write_element
    xml = tostring(element)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 1136, in tostring
    short_empty_elements=short_empty_elements)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 777, in write
    short_empty_elements=short_empty_elements)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\contextlib.py", line 130, in __exit__
    self.gen.throw(type, value, traceback)
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\xml\etree\ElementTree.py", line 836, in _get_writer
    yield file.write
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\contextlib.py", line 511, in __exit__
    raise exc_details[1]
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\contextlib.py", line 496, in __exit__
    if cb(*exc_details):
  File "C:\Users\davidm\AppData\Local\Programs\Python\Python37-32\Lib\contextlib.py", line 383, in _exit_wrapper
    callback(*args, **kwds)
ValueError: I/O operation on closed file.

offtopic: you could just `append` each row directly to the worksheet instead of to a list and then iterate again. Ontopic: make sure the file is in such a location that windows allow you to write to and that it has no special flags like Read-Only — Tomerikoo, Jul 22 '19 at 22:31
The error looks odd but without the full traceback it's difficult to say. — Charlie Clark, Jul 23 '19 at 07:38
@Tomerikoo thanks for the suggestion; the file is currently sitting on my desktop and the file is not read-only. — dmcoding, Jul 23 '19 at 13:29
@CharlieClark fair point, I'll grab the full traceback and put it into the main question — dmcoding, Jul 23 '19 at 13:29
I think the file is closed because of the memory error. If this is a large file then et_xmlfile could have problems. Please install lxml and try again. — Charlie Clark, Jul 23 '19 at 17:17
lmxl is installed, still isn't working, unfortunately. (same error) — dmcoding, Jul 23 '19 at 19:46

How do I append rows to an excel spreadsheet with openpyxl and then save the excel file?

0 Answers0