CODEWITHSUNDEEP
pythonthread-safetyopenpyxl
Jacob
Jacob

Reputation: 1553

openpyxl thread safe?

Is openpyxl thread safe? I'm hoping to have one thread changing the worksheet while another thread saves at regular intervals. I was wondering if I needed to add a lock object around the changing and saving operations or whether that is already built in to openpyxl. I did not see anything in the documentation nor any previous questions about threads in openpyxl.

After reading your answer I ran the following test:

import threading 
import random 
import time 
from openpyxl import Workbook

wb = Workbook() 

class openpyxlwriting ( threading.Thread): 

    def run ( self ): 

        global wb
        ws = wb.get_active_sheet()
        c = 1 
        for a in range(100):
            for b in range(10000):
                ws.cell(column = a,row=b).value = c
                c += 1
            print "row ",a

class openpyxlsaving ( threading.Thread): 

    def run ( self ): 

        global wb

        for a in range(1000): 
            wb.save('test.xlsx')
            print "saved"

openpyxlwriting().start()
time.sleep(1)
openpyxlsaving ().start()

which gave me the following trasback: trasback.png

Upvotes: 5

Views: 4244

Answers (2)

Jacob
Jacob

Reputation: 1553

"OpenPyXl is thread safe; Workbooks can be created concurrently by different threads. However, each workbook should only be modified by one thread at a time."

Form https://bitbucket.org/openpyxl/openpyxl/issues/228/

Upvotes: 5

David
David

Reputation: 6571

I decided to see if I could test this, so I created the simple program below that generates a bunch of threads that attempt to read and write the same cell, with random sleeps thrown in.

I didn't see any OS errors that might indicate a problem. On the other hand, I did see some behavior that I didn't quite understand. As the thread count increased, the number of loops completed decreased. So, for example, thread 100 only completed 8 passes through the loop. This might be some sort of an error on my part, or it might indicate an issue with thread safety.

Anyway, this should give you a start in testing it yourself, if you feel so inclined.

import threading
import random
import time
from openpyxl import Workbook

wb = Workbook()
ws = wb.get_active_sheet()
testcell = ws.cell('B9')
counter = 1

class openpyxlworker ( threading.Thread):

    def run ( self ):

        global wb     
        global testcell
        global counter

        for a in range(1000):
            time.sleep(random.random()/100)
            writing = random.randrange(1, 1000)
            testcell.value = writing
            time.sleep(random.random()/100)
            reading = testcell.value          
            print "Thread " + str(counter) + " wrote " + str(writing) + " and read " + str(reading)
            time.sleep(random.random()/100)
            wb.save('test.xlsx')

        counter = counter + 1

for b in range(100):
    openpyxlworker().start()

Upvotes: 2

Related Questions