import openpyxl
import labelling

#list3，list4是子表对应的需要贴标签的数字。上下一一对应。
list3 = [1,1,2,2,3,3,4,4,5,5,6,6]
list4 = [8,10,7,12,9,11,7,10,8,12,9,11]
data = [1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1]
start=250
end=3130
defaultdata = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
# 判断属于哪个区间
def judge(data):
    start=250
    end=3130
    for i in range(0,int((end-start)/20)):
        if ((i*20+start)<=data) & (data<((i+1)*20+start)):
            return i*20+start,(i+1)*20+start

# judge(355)

#存储要贴0的标签
needsection = [start]

#测试找区间数组
def findSection(findPath,writePath,key,findwritesheet):
    findwb = openpyxl.load_workbook(findPath)
    findsh = findwb[findwritesheet]
    writewb=openpyxl.load_workbook(writePath)
    for cases in list(findsh.rows)[0:]:
        case_id =  cases[0].value
        case_excepted = cases[1].value
        if case_id==list3[key]:
            area = judge(case_excepted)
            #需要写进data表的标签的数字
            data = labelling.addlable(1)
            print(data,writePath,area[0],area[1],findwritesheet,list3[key])
            needsection.append(area[0])
            needsection.append(area[1])
            labelling.WriteExcel(data,writewb,area[0],area[1],findwritesheet)
            data.clear()
        if case_id==list4[key]:
            area = judge(case_excepted)
            #需要写进data表的标签的数字
            data = labelling.addlable(10)
            print(data,writewb,area[0],area[1],findwritesheet,list4[key])
            needsection.append(area[0])
            needsection.append(area[1])
            labelling.WriteExcel(data,writewb,area[0],area[1],findwritesheet)
            data.clear()
    needsection.append(end)
    # sorted(needsection)
    i=0
    print('------------------------------------------')
    print(needsection)
    print(len(needsection))
    print('------------------------------------------')
    while (i<(len(needsection)-1)):
        print(needsection[i],needsection[i+1])
        print(defaultdata,writePath,area[0],area[1],findwritesheet,list4[key],needsection)
        labelling.WriteExcel(defaultdata,writewb,needsection[i],needsection[i+1],findwritesheet)
        i=i+2
    findwb.close()
    writewb.save(writePath)
findSection('S1_train_event.xlsx','S1_train_data.xlsx',1,'char02(D)')


#测试循环添加需要贴标签的数字到列表
def addlable():
    data = []
    for i in range(0,20):
        data.append(1)
    print(data)
    print(len(data))

# addlable()


#测试写函数
#定义一个对excel表进行操作的函数
def WriteExcel(data,writePath,rangeA,rangeB,findwritesheet):
    wb=openpyxl.load_workbook(writePath)
    writesh = wb[findwritesheet]
    for i in range(rangeA,rangeB):
        for j in range(1,len(data)+1):
            distance=data[j-1]
            # 写入位置的行列号可以任意改变，这里我是从第2行开始按行依次插入第11列
            writesh.cell(row = i, column = j).value = distance
    wb.save(writePath)

# WriteExcel(data,'S1_train_data.xlsx',10,50+1,char01(B))


#纯找区间
def findSectionOnly():
    findwb = openpyxl.load_workbook('S2_train_event.xlsx')
    findsh = findwb['char01(B)']
    for cases in list(findsh.rows)[0:]:
        case_id =  cases[0].value
        case_excepted = cases[1].value
        if case_id==list3[1] or case_id==list4[1]:
            area = judge(case_excepted)
            print(area[0],area[1])
    findwb.close()
# findSectionOnly()