运维工具 – 异常数据分析实践，批量生成统计清单

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
pip install openpyxl==3.0.5



from openpyxl import load_workbook

workbook = load_workbook('\统计表.xlsx')

worksheet = workbook['Sheet1']

data = {}

for row in range(2, worksheet.max_row + 1):

    date = worksheet['B' + str(row)].value.date()

    customer = worksheet['C' + str(row)].value

    product = worksheet['D' + str(row)].value

    number = worksheet['E' + str(row)].value

    model = worksheet['G' + str(row)].value

    info_list = [customer, product, number, model]

    data.setdefault(date, [])

    data[date].append(info_list)





for key, value in data.items():

    print(key, value)

workbook_day = load_workbook('\清单模板.xlsx')

worksheet_day = workbook_day['清单模板']



for date in data.keys():

    worksheet_new = workbook_day.copy_worksheet(worksheet_day)

    worksheet_new.title = str(date)[-5:]

    worksheet_new.cell(row=2, column=5).value = date

    i = 4

    for product in data[date]:

        worksheet_new.cell(row=i, column=2).value = product[0]

        worksheet_new.cell(row=i, column=3).value = product[1]

        worksheet_new.cell(row=i, column=4).value = product[2]

        worksheet_new.cell(row=i, column=5).value = product[3]

        i += 1



workbook_day.save('目标清单.xlsx')

清单模板

统计表内容：

结果 ：