openpyxl具体需求操作

openpyxl具体需求操作

涉及公司业务，数据不展示，自存学习使用

'''
# 1test.xlsx
SELECT t.req_id,wm_concat(p.file_list) file_list FROM t_req_list T,t_req_listplus p where t.req_sdpid=p.req_sdpid  and t.req_id  like 'R%'
and p.file_list is not null  and p.REQ_SDPID in (数据)
group by t.req_id

# 2.xlsx
select a.*,rowid FROM uop_oa.t_req_listplus a where REQ_SDPID in (数据)
and a.file_list is not null
'''
from openpyxl import load_workbook
from openpyxl import Workbook
import os

# 文件存在则删除
if os.path.exists('test_out.xlsx'):
    os.remove('test_out.xlsx')
# 读表
i1 = load_workbook('1test.xlsx')
i2 = load_workbook('2.xlsx')
i3 = load_workbook('2021需求周报.xlsx')
i4 = load_workbook('BOSS 天津省上线评审记录表@202108.xlsx')
print('读表结束')
# 创建新输出表
o = Workbook()
# 创建sheet
o1 = o.create_sheet('数据汇总',0)
o2 = o.create_sheet('问题汇总',1)
wo1 = o.create_sheet('常规版8月11日',2)
wo2 = o.create_sheet('常规版8月25日',3)
wo3 = o.create_sheet('紧急版8月31日',4)
# 删除默认sheet
r = o['Sheet']
o.remove(r)
def out(o,i1,i2,i3,i4,st,data):
    # 写入数据
    wo1s = o[st]
    i = i3['上线需求明细']
    la = []
    lb = []
    lc = []
    ld = []
    le = []
    lf = []
    lg = []
    # i3转二维数组
    for h in range(1,i.max_row):
        lie = []
        n = 0
        for l in i[h]:
            lie.append(l.value) 
            n += 1
            if n == 15:
                break  
        if lie[1] == None:
            break
        # 核心过滤日期
        if lie[7] == data:      
            # print(lie[1])
            la.append(lie[1])
            lb.append(lie[2])
            lc.append(lie[5])
            ld.append(lie[4])
    # i1转二维数组
    for h in range(1,177):
        lie = []
        n = 0
        for l in i1['Sheet1'][h]:
            lie.append(l.value)
            n += 1
            if n == 2:
                break  
        if lie[0] == None:
            break
        le.append([lie[0],lie[1]])
    # i2转二维数组
    for h in range(1,205):
        lie = []
        n = 0
        for l in i2['Sheet1'][h]:
            lie.append(l.value)
            n += 1
            if n == 55:
                break  
        if lie[0] == None:
            break
        # REQ_ID 代码评审分类 评审时间 评审意见	代码评审人员 抽查人	评审结果
        lf.append([lie[0],lie[20],lie[35],lie[45],lie[47],lie[48],lie[51]])
    # i4 转数组
    for h in range(1,205):
        lie = []
        n = 0
        for l in i4['上线需求评审列表'][h]:
            lie.append(l.value)
            n += 1
            if n == 25:
                break  
        if lie[0] == None:
            break
        lg.append([lie[0],lie[4]])
    # A列 需求编码
    wo1s.cell(1,1).value = '需求编码'
    num = 2
    for l in la:
        # cell(行,列)
        wo1s.cell(num,1).value = l
        num += 1
    all_num = num
    print('日期过滤后数据条数：',all_num-2)
    # B列 需求名称
    wo1s.cell(1,2).value = '需求名称'
    num = 2
    for l in lb:
        # cell(行,列)
        wo1s.cell(num,2).value = l
        num += 1
    # C列 产品线
    wo1s.cell(1,3).value = '产品线'
    num = 1
    for h in range(1,all_num):
        for l in o[st][h]:
            for ll in lg:
                if l.value == ll[0]:
                    wo1s.cell(num,3).value = ll[1]
                    break
            break
        num += 1
    # D列 需求开发人
    wo1s.cell(1,4).value = '需求开发人'
    num = 2
    for l in ld:
        # cell(行,列)
        wo1s.cell(num,4).value = l
        num += 1
    # E列 代码列表
    wo1s.cell(1,5).value = '代码列表'
    num = 1
    for h in range(1,all_num):
        for l in o[st][h]:
            for ll in le:
                if l.value == ll[0]:
                    wo1s.cell(num,5).value = ll[1]
                    break
            break
        num += 1
    # F列 代码评审人员
    wo1s.cell(1,6).value = '代码评审人员'
    num = 1
    for h in range(1,all_num):
        for l in o[st][h]:
            for ll in lf:
                if l.value == ll[0]:
                    wo1s.cell(num,6).value = ll[4]
                    break
            break
        num += 1
    # G列 代码评审分类
    wo1s.cell(1,7).value = '代码评审分类'
    num = 1
    for h in range(1,all_num):
        for l in o[st][h]:
            for ll in lf:
                if l.value == ll[0]:
                    wo1s.cell(num,7).value = ll[1]
                    break
            break
        num += 1
    # H列 评审结果
    wo1s.cell(1,8).value = '评审结果'
    num = 1
    for h in range(1,all_num):
        for l in o[st][h]:
            for ll in lf:
                if l.value == ll[0]:
                    wo1s.cell(num,8).value = ll[6]
                    break
            break
        num += 1
    # I列 评审意见
    wo1s.cell(1,9).value = '评审意见'
    num = 1
    for h in range(1,all_num):
        for l in o[st][h]:
            for ll in lf:
                if l.value == ll[0]:
                    wo1s.cell(num,9).value = ll[3]
                    break
            break
        num += 1
    # J列 评审时间
    wo1s.cell(1,10).value = '评审时间'
    # =TEXT(RAND()*("2021-08-02"-"2021-08-06")+"2021-08-06","yyyy-mm-dd")
    # K列 抽查人
    wo1s.cell(1,11).value = '抽查人'
    num = 1
    for h in range(1,all_num):
        for l in o[st][h]:
            for ll in lf:
                if l.value == ll[0]:
                    wo1s.cell(num,11).value = ll[5]
                    break
            break
        num += 1
    # L列 抽查时间
    wo1s.cell(1,12).value = '抽查时间'
    # M列 抽查结论
    wo1s.cell(1,13).value = '抽查结论'
    # N列 备注
    wo1s.cell(1,14).value = '备注'
# 写数据函数
st1 = '常规版8月11日'
data1 = '2021-08-11'
print(st1,data1)
out(o,i1,i2,i3,i4,st1,data1)

st2 = '常规版8月25日'
data2 = '2021-08-25'
print(st2,data2)
out(o,i1,i2,i3,i4,st2,data2)

st3 = '紧急版8月31日'
data3 = '2021-08-31'
print(st3,data3)
out(o,i1,i2,i3,i4,st3,data3)
# 保存
o.save('test_out.xlsx')