I have an excel file with 4.95 MB. I am trying to read this file with openpyxl and perform some excel formula in this file. It is working great with small files. But as Soon as I started working with the large file. It takes too much time as if seems like it moves to an infinite Condition. Kindly let me know with some approach or what modification should I do in this piece of code.

import openpyxl
import os
import xlrd
import xlsxwriter
#reading the file i.e. 4.5 Mb
wb = openpyxl.load_workbook(r"type_example.xlsx")
Sheet = wb.get_sheet_by_name('sheet1')


for row,cellObj in enumerate(Sheet["C"],1):
    cellObj.value='=IF(OR(B{0}="J", B{0} ="U", B{0} ="s"),"s", IF(OR(B{0} ="I", B{0} ="M", B{0} ="N", B{0}="O", B{0} ="Q", B{0} ="U", B{0} ="V"), "J", "des"))'.format(row)
    Sheet.cell(row=1, column=3).value = 'Dt'
    #path to save file 
    wb.save(r'\Documents\test_1.xlsx')

1 Answers

0
Community On

I had a quick look around and found that the issue may be because you're going cell-by-cell.

It seems that openpyxl will basically do a linear search through the entire document to find your desired cell if you do things like that (which is not ideal), and you need to use a generator if you want to speed it up.

It doesn't look too hard, but I'm not particularly skilled in python. So, rather than try to explain the concept myself, I'll leave you this:

https://blog.davep.org/2018/06/02/a_little_speed_issue_with_openpyxl.html

Hope it helps.