pythonでthreadを使ってエクセルにデータを読み込みたいです．

Question

pythonでthreadを使い，エクセルにデータを書き込むプログラムを書いたのですが，threadの利用前のプログラムではエクセルにデータを読み込めるのですが，threadの利用後のプログラムではエクセルに読み込むデータが飛び飛びで全てのデータを読み込むことが出来ません．

threadの利用前

import serial
import time
import openpyxl

PORT1 = 'COM3'
PORT2 = 'COM5'

com1 = serial.Serial(PORT1, timeout = 0.1)
com2 = serial.Serial(PORT2, timeout = 0.1)


raw_acc_1 = [0, 0, 0]


raw_acc_2 = [0, 0, 0]


wb = openpyxl.load_workbook('test.xlsx')
sheet = wb.get_sheet_by_name('test_serial2')


for i in range(2,31):
  while True:
    if com1.read() == 'B':
      data = com1.read(17)


      time_stamp_1 = 
      raw_acc_1[0] = 
      raw_acc_1[1] = 
      raw_acc_1[2] = 
      break
    else:
      time.sleep(0.001)

  while True:
    if com2.read() == 'B':
      data = com2.read(17)


      time_stamp_2 = 
      raw_acc_2[0] = 
      raw_acc_2[1] = 
      raw_acc_2[2] =  
      break
    else:
      time.sleep(0.001)
      
  sheet.cell(row =i,column =1).value = str(time_stamp_1)
  sheet.cell(row =i,column =2).value = str(raw_acc_1[0])
  sheet.cell(row =i,column =3).value = str(raw_acc_1[1])
  sheet.cell(row =i,column =4).value = str(raw_acc_1[2])
  sheet.cell(row =i,column =5).value = str(raw_gyro_1[0])
  sheet.cell(row =i,column =6).value = str(raw_gyro_1[1])
  sheet.cell(row =i,column =7).value = str(raw_gyro_1[2])
  sheet.cell(row =i,column =8).value = str(raw_comp_1[0])
  sheet.cell(row =i,column =9).value = str(raw_comp_1[1])
  sheet.cell(row =i,column =10).value = str(raw_comp_1[2])
  sheet.cell(row =i,column =11).value = str(time_stamp_2)
  sheet.cell(row =i,column =12).value = str(raw_acc_2[0])
  sheet.cell(row =i,column =13).value = str(raw_acc_2[1])
  sheet.cell(row =i,column =14).value = str(raw_acc_2[2])
  sheet.cell(row =i,column =15).value = str(raw_gyro_2[0])
  sheet.cell(row =i,column =16).value = str(raw_gyro_2[1])
  sheet.cell(row =i,column =17).value = str(raw_gyro_2[2])
  sheet.cell(row =i,column =18).value = str(raw_comp_2[0])
  sheet.cell(row =i,column =19).value = str(raw_comp_2[1])
  sheet.cell(row =i,column =20).value = str(raw_comp_2[2])
  wb.save('test.xlsx')



print 'fin'

com1.close()
com2.close()

threadの利用後

import time
import threading
import serial
import openpyxl

PORT1 = 'COM3'
PORT2 = 'COM5'

com1 = serial.Serial(PORT1, timeout = 0.1)
com2 = serial.Serial(PORT2, timeout = 0.1)

raw_acc_1 = [0, 0, 0]


raw_acc_2 = [0, 0, 0]


wb = openpyxl.load_workbook('test.xlsx')
sheet = wb.get_sheet_by_name('test_serial2')

def foo():
    for i in range(2,31):
    	while True:
    		if com1.read() == 'B':
    			data = com1.read(17)
    			time_stamp_1 = 
      			raw_acc_1[0] = 
      			raw_acc_1[1] = 
      			raw_acc_1[2] = 
      			raw_gyro_1[0] = 
      			raw_gyro_1[1] = 
      			raw_gyro_1[2] = 
      			raw_comp_1[0] = 
      			raw_comp_1[1] = 
      			raw_comp_1[2] = 

     	 		break
    		else:
      			time.sleep(0.001)

  		sheet.cell(row =i,column =1).value = str(time_stamp_1)
  		sheet.cell(row =i,column =2).value = str(raw_acc_1[0])
  		sheet.cell(row =i,column =3).value = str(raw_acc_1[1])
  		sheet.cell(row =i,column =4).value = str(raw_acc_1[2])
  		sheet.cell(row =i,column =5).value = str(raw_gyro_1[0])
  		sheet.cell(row =i,column =6).value = str(raw_gyro_1[1])
  		sheet.cell(row =i,column =7).value = str(raw_gyro_1[2])
  		sheet.cell(row =i,column =8).value = str(raw_comp_1[0])
  		sheet.cell(row =i,column =9).value = str(raw_comp_1[1])
  		sheet.cell(row =i,column =10).value = str(raw_comp_1[2])
  		wb.save('test.xlsx')

def foo2():
    for i in range(2,31):
    	while True:
    		if com2.read() == 'B':
    			data = com2.read(17)
    			time_stamp_2 = 
      			raw_acc_2[0] = 
      			raw_acc_2[1] = 
      			raw_acc_2[2] = 
      			raw_gyro_2[0] = 
      			raw_gyro_2[1] = 
      			raw_gyro_2[2] = 
      			raw_comp_2[0] = 
      			raw_comp_2[1] = 
      			raw_comp_2[2] = 

     	 		break
    		else:
      			time.sleep(0.001)
      			
  		sheet.cell(row =i,column =12).value = str(raw_acc_2[0])
  		sheet.cell(row =i,column =13).value = str(raw_acc_2[1])
  		sheet.cell(row =i,column =14).value = str(raw_acc_2[2])
  		sheet.cell(row =i,column =15).value = str(raw_gyro_2[0])
  		sheet.cell(row =i,column =16).value = str(raw_gyro_2[1])
  		sheet.cell(row =i,column =17).value = str(raw_gyro_2[2])
  		sheet.cell(row =i,column =18).value = str(raw_comp_2[0])
  		sheet.cell(row =i,column =19).value = str(raw_comp_2[1])
  		sheet.cell(row =i,column =20).value = str(raw_comp_2[2])
  		wb.save('test.xlsx')
      		
th=threading.Thread(target=foo)
th2=threading.Thread(target=foo2)

th.start()
th2.start()

Answer 1

openpyxl thread safe?
workbook modification is not thread safe
によると、同一ワークブックに対する変更はスレッドセーフではないそうです。
よって、意図しない結果になっているものと思われます。
別ワークブックへ書き込むなど別の手段をとるしかないようです。