I/O Error while saving Excel file - Python

Question

Im using python to open an existing excel file and do some formatting and save and close the file. My code is working good when the file size is small but when excel size is big (apprx. 40MB) I'm getting Serialization I/O error and Im sure it due to memory problem or due to my code. Kindly help.

System Config:

RAM - 8 GB 32 - bit operation Windows 7

Code:

import numpy as np
from openpyxl import load_workbook
from openpyxl.styles import colors, Font


dest_loc='/Users/abdulr06/Documents/Python Scripts/'

np.seterr(divide='ignore', invalid='ignore')

SRC='TSYS'
YM1='201707'

dest_file=dest_loc+SRC+'_'+''+YM1+'.xlsx'  

sheetname = [SRC+''+' GL-Recon']   


#Following code is common for rest of the sourc systems 
wb=load_workbook(dest_file)

fmtB=Font(color=colors.BLUE) 
fmtR=Font(color=colors.RED)

for i in range(len(sheetname)):             

  sheet1=wb.get_sheet_by_name(sheetname[i])             
  print(sheetname[i])  

  last_record=sheet1.max_row+1 

  for m in range(2,last_record):
      if -30 <= sheet1.cell(row=m,column=5).value <=30:          
          ft=sheet1.cell(row=m,column=5)
          ft.font=fmtB
          ft.number_format = '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)'
          ft1=sheet1.cell(row=m,column=6)
          ft1.number_format = '0.00%'
      else:          
          ft=sheet1.cell(row=m,column=5)  
          ft.font=fmtR  
          ft.number_format = '_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)'
          ft1=sheet1.cell(row=m,column=6)
          ft1.number_format = '0.00%' 
wb.save(filename=dest_file)  

Exception:

Traceback (most recent call last):

  File "<ipython-input-17-fc16d9a46046>", line 6, in <module>
    wb.save(filename=dest_file)

  File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\workbook\workbook.py", line 263, in save
    save_workbook(self, filename)

  File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 239, in save_workbook
    writer.save(filename, as_template=as_template)

  File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 222, in save
    self.write_data(archive, as_template=as_template)

  File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 80, in write_data
    self._write_worksheets(archive)

  File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 163, in _write_worksheets
    xml = sheet._write(self.workbook.shared_strings)

  File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\worksheet\worksheet.py", line 776, in _write
    return write_worksheet(self, shared_strings)

  File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\worksheet.py", line 263, in write_worksheet
    xf.write(worksheet.page_breaks.to_tree())

  File "serializer.pxi", line 1016, in lxml.etree._FileWriterElement.__exit__ (src\lxml\lxml.etree.c:141944)

  File "serializer.pxi", line 904, in lxml.etree._IncrementalFileWriter._write_end_element (src\lxml\lxml.etree.c:140137)

  File "serializer.pxi", line 999, in lxml.etree._IncrementalFileWriter._handle_error (src\lxml\lxml.etree.c:141630)

  File "serializer.pxi", line 195, in lxml.etree._raiseSerialisationError (src\lxml\lxml.etree.c:131006)

SerialisationError: IO_WRITE

Answer 1

Why do you allocate font at each loop?

fmt=Font(color=colors.BLUE)

Or red, create two fonts red and blue, once and then use it, each time you are allocating Font, you are using more memory.

Optimise your code at first. Less code -> less errors, for example:

mycell = sheet1.cell(row=m,column=5)       
if -30 <= mycell.value <=30: 
    mycell.font = redfont 

This should ensure that you do not have the issue again (hopefully)