Saving an excel using openpyxl

Question

I am trying to parse a file and then save the processed data into excel using openpyxl. When the file to be parsed is small i am able to save successfully. But when the file to be parsed is big i get following error:

Traceback (most recent call last):
      File "C:\Users\522094\Desktop\vdbench\vdbench_new - Copy.py", line 195, in <module>
        a.generate_report()
      File "C:\Users\522094\Desktop\vdbench\vdbench_new - Copy.py", line 185, in generate_report
        self.wb.save(filename=report_path)
      File "C:\Python27\lib\site-packages\openpyxl\workbook\workbook.py", line 339, in save
        save_workbook(self, filename)
      File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 268, in save_workbook
        writer.save(filename)
      File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 250, in save
        self.write_data()
      File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 81, in write_data
        self._write_worksheets()
      File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 199, in _write_worksheets
        xml = ws._write()
      File "C:\Python27\lib\site-packages\openpyxl\worksheet\worksheet.py", line 866, in _write
        return write_worksheet(self)
      File "C:\Python27\lib\site-packages\openpyxl\writer\worksheet.py", line 177, in write_worksheet
        xf.write(tables.to_tree())
      File "C:\Python27\lib\contextlib.py", line 24, in __exit__
        self.gen.next()
      File "C:\Python27\lib\site-packages\et_xmlfile\xmlfile.py", line 50, in element
        self._write_element(el)
      File "C:\Python27\lib\site-packages\et_xmlfile\xmlfile.py", line 77, in _write_element
        xml = tostring(element)
      File "C:\Python27\lib\xml\etree\ElementTree.py", line 1126, in tostring
        ElementTree(element).write(file, encoding, method=method)
      File "C:\Python27\lib\xml\etree\ElementTree.py", line 820, in write
        serialize(write, self._root, encoding, qnames, namespaces)
      File "C:\Python27\lib\xml\etree\ElementTree.py", line 939, in _serialize_xml
        _serialize_xml(write, e, encoding, qnames, None)
      File "C:\Python27\lib\xml\etree\ElementTree.py", line 939, in _serialize_xml
        _serialize_xml(write, e, encoding, qnames, None)
      File "C:\Python27\lib\xml\etree\ElementTree.py", line 939, in _serialize_xml
        _serialize_xml(write, e, encoding, qnames, None)
      File "C:\Python27\lib\xml\etree\ElementTree.py", line 940, in _serialize_xml
        write("</" + tag + ">")
    MemoryError

Please suggest how to resolve.

Answer 1

You don't specify what kind of workbook you're using but accord to the official documentation a faster alternative is use the write-only mode of a workbook, then, you could set your workbook in write only mode turning on the flag in the instantiation:

>>> from openpyxl import Workbook
>>> wb = Workbook(write_only=True)
>>> ws = wb.create_sheet()
>>>
>>> # now we'll fill it with 100 rows x 200 columns
>>>
>>> for irow in range(100):
...     ws.append(['%d' % i for i in range(200)])
>>> # save the file
>>> wb.save('new_big_file.xlsx') 

the official documentation if you need more info:

https://openpyxl.readthedocs.io/en/default/optimized.html#write-only-mode