i want to make my excel file read and write fast with openpyxl

Question

my code is to slow I want to make is more faster for example: the code is supposed to take the value of in cell a1 and change its value and rewrite again in the same cell can you help me with it ?

import openpyxl

row = 1
counter = 0
while row <= 20980:
    book = openpyxl.load_workbook('semsar_full.xlsx')
    sheet = book.active
    a3 = sheet.cell(row=row, column=1)
    a4 = a3.value + ':::'
    sheet.cell(row=row, column=1, value=a4)
    counter += 1
    row += 1
    print(counter)
    book.save('semsar_full.xlsx')

Answer 1

I think the biggest issue while it is very slow is that you are loading + saving the file for each row contained. If you put the load + save outside of the loop the code should be much faster.

import openpyxl

book = openpyxl.load_workbook('semsar_full.xlsx')
sheet = book.active
row = 1
counter = 0
while row <= 20980:

    a3 = sheet.cell(row=row, column=1)
    a4 = a3.value + ':::'
    sheet.cell(row=row, column=1, value=a4)
    counter += 1
    row += 1
    print(counter)

book.save('semsar_full.xlsx')