Python Openpyxl excel 根据已有规则填充excel

ps:
空单元格可以用如下进行判断

 str_reason = excel_set.get_cell_value( i+1, 6)      
  if (str_reason != None): 

规则模板

需要填充的excel (M列 N列)。 这个excel有上千列，如果一个一个粘的话…

import json
import requests
import openpyxl
import os
import ssl
import sys
import time

class ExcelOp(object):
    def __init__(self, file):
        self.file = file
        self.wb = openpyxl.load_workbook(self.file)
        sheets = self.wb.sheetnames
        self.sheet = sheets[0]
        self.ws = self.wb[self.sheet]

    def set_sheets(self,num):
        sheets = self.wb.sheetnames
        if(num < len(sheets)):
            self.sheet = sheets[num]
            self.ws = self.wb[self.sheet]
        else:
            print("the sheet num is out range ,MAX:%d"%(len(sheets)))
    def get_sheet_name(self):
        return self.ws.title
		
    # 获取表格的总行数和总列数
    def get_row_clo_num(self):
        rows = self.ws.max_row
        columns = self.ws.max_column
        return rows, columns

    # 获取某个单元格的值
    def get_cell_value(self, row, column):
        cell_value = self.ws.cell(row=row, column=column).value
        return cell_value

    # 获取某列的所有值
    def get_col_value(self, column):
        rows = self.ws.max_row
        column_data = []
        for i in range(1, rows + 1):
            cell_value = self.ws.cell(row=i, column=column).value
            column_data.append(cell_value)
        return column_data

    # 获取某行所有值
    def get_row_value(self, row):
        columns = self.ws.max_column
        row_data = []
        for i in range(1, columns + 1):
            cell_value = self.ws.cell(row=row, column=i).value
            row_data.append(cell_value)
        return row_data

    # 设置某个单元格的值
    def set_cell_value(self, row, colunm, cellvalue):
        try:
            self.ws.cell(row=row, column=colunm).value = cellvalue
            self.wb.save(self.file)
        except:
            self.ws.cell(row=row, column=colunm).value = "writefail"
            self.wb.save(self.file)
			
answer_list_youwu = []
answer_list_reason = []
answer_list_error = []

a = 0
if __name__ == "__main__":
    excel_ori= ExcelOp(file="template.xlsx")
   
     
    max_row,max_column = excel_ori.get_row_clo_num()
    #get the template list
    for j in range(max_row):
        str_ifnot = excel_ori.get_cell_value( j+1, 2)
        if(str_ifnot == 1):
            answer_list_youwu.append(excel_ori.get_cell_value( j+1, 3))
            answer_list_reason.append(excel_ori.get_cell_value( j+1, 4)) 
            str_reason = excel_ori.get_cell_value( j+1, 5)
            str_temp = str_reason[0:70]
            answer_list_error.append(str_temp) 
            #print("num :%d is exist  str_ifnot:%s "%(j,str_ifnot))
    
    #set the result to target excel
    excel_set= ExcelOp(file="20200508_Checkシート_2_0511test.xlsx")
    excel_set.set_sheets(7)
    max_row,max_column = excel_set.get_row_clo_num()
    print("max_row:%d max_column:%d"%(max_row,max_column))
    for i in range(max_row):
        str_reason = excel_set.get_cell_value( i+1, 14)
		
       

        #check the frame reason  if empty
        if (len(str(str_reason))<5): 
            sys.stdout.write("check sheet:%s  line:%d"%(excel_set.get_sheet_name(),i)+'\r')
            sys.stdout.flush()
            str_err_full = excel_set.get_cell_value( i+1, 15)
            str_temp = str_err_full[0:70]
            try:
                num_list = answer_list_error.index(str_temp)
            except ValueError:
                a = a+1
                #print("cant find it in template")
            else:
                #print("find it in template")
                excel_set.set_cell_value(i+1,13,answer_list_youwu[num_list])
                excel_set.set_cell_value(i+1,14,answer_list_reason[num_list])