Memory error when executing WorkBook.save
Created originally on Bitbucket by huangchuchuan
errors info
Traceback (most recent call last):
File "C:/Users/pc/PycharmProjects/huangcc-demos/openpyxl_pandas.py", line 87, in <module>
openxlpy_to_xlsx()
File "C:/Users/pc/PycharmProjects/huangcc-demos/openpyxl_pandas.py", line 47, in wrapper
result = func(*args, **kwargs)
File "C:/Users/pc/PycharmProjects/huangcc-demos/openpyxl_pandas.py", line 66, in openxlpy_to_xlsx
wb.save(target_file)
File "C:\Python27\lib\site-packages\openpyxl\workbook\workbook.py", line 365, in save
save_dump(self, filename)
File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 313, in save_dump
writer.save(filename)
File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 266, in save
self.write_data()
File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 83, in write_data
self._write_worksheets()
File "C:\Python27\lib\site-packages\openpyxl\writer\excel.py", line 203, in _write_worksheets
xml = ws._write()
File "C:\Python27\lib\site-packages\openpyxl\worksheet\write_only.py", line 261, in _write
out = src.read()
MemoryError
scene
When I read a csv file(size 100Mb) and append the lines to workbook, it will exite with memory error in executing save function. I have already installed lxml library.
reasons
my codes
wb = Workbook(write_only=True)
sheet = wb.create_sheet()
for row in rows:
sheet.append(row)
wb.save(target_file)
codes in exception
# openpyxl\worksheet\write_only.py
def _write(self):
self._drawing = SpreadsheetDrawing()
self._drawing.charts = self._charts
self._drawing.images = self._images
self.close()
with open(self.filename) as src:
out = src.read()
self._cleanup()
return out
Here, _write function read all data in memory and the src file size is 1.8GB. Of course it will be in problem. What's more, when writing data to ZipFile, openpyxl use writestr function instead of ZipFile.write
# openpyxl/packaging/manifest.py
def _write_worksheets(self):
pivot_caches = set()
for idx, ws in enumerate(self.workbook.worksheets, 1):
ws._id = idx
xml = ws._write()
rels_path = get_rels_path(ws.path)[1:]
self._archive.writestr(ws.path[1:], xml)
self.manifest.append(ws)
my solution
Use ZipFile.write to write data from files