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, Fontdest_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=fmtBft.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 savesave_workbook(self, filename)File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 239, in save_workbookwriter.save(filename, as_template=as_template)File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 222, in saveself.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_dataself._write_worksheets(archive)File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\excel.py", line 163, in _write_worksheetsxml = sheet._write(self.workbook.shared_strings)File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\worksheet\worksheet.py", line 776, in _writereturn write_worksheet(self, shared_strings)File "C:\Users\abdulr06\AppData\Local\Continuum\Anaconda3\lib\site-packages\openpyxl\writer\worksheet.py", line 263, in write_worksheetxf.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

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)