shubo的博客

gopher/全干工程师

0%

Python实现Excel工作表的分割

发表于 更新于
本文字数： 1.5k 阅读时长 ≈ 1 分钟 
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
import openpyxl

workbook = openpyxl.load_workbook("test.xlsx")

sheetnames = workbook.sheetnames

# print(sheetnames)

worksheet = workbook["总表"]

# 获取该表相应的行数和列数
CountRows = worksheet.max_row
CountColumns = worksheet.max_column

# print(columns)

worksheetnamelist = []

# 保存大表 数据清单标题
AllTitleList = []
for cell in worksheet.columns:
    # print(cell[0].value)
    AllTitleList.append(cell[0].value)

# 保存大表 第一列（学号）、第二列（姓名）数据
NumList = []
NameList = []
for i in range(2, 31):
    NumList.append(worksheet.cell(row=i, column=1).value)
    NameList.append(worksheet.cell(row=i, column=2).value)

# print(NameList[28])
# 生成工作表计数
count = 0
for start_column in range(3, CountColumns + 1, 14):
    count += 1
    # print(worksheet.cell(row=1, column=start_column).value)
    current_worksheet = workbook.create_sheet()
    current_worksheet.title = worksheet.cell(row=1, column=start_column).value 
    + "-" + worksheet.cell(row=1,column=start_column + 13).value
    current_worksheet.cell(1, 1, "学号")
    current_worksheet.cell(1, 2, "姓名")
    # 填充前两列学号姓名
    for i in range(2, 31):
        current_worksheet.cell(i, 1, NumList[i - 2])
        current_worksheet.cell(i, 2, NameList[i - 2])
    # 遍历日期列名
    for i in range(start_column, start_column + 14):
        print(AllTitleList[i - 1])
        # print(count)
        current_worksheet.cell(1, i - (count - 1) * 14, AllTitleList[i - 1])
        # 填充每日体温数据区域
        for j in range(2, 31):
            current_worksheet.cell(j, i - (count - 1) * 14, worksheet.cell(row=j, column=i).value)

    print("----")
workbook.save(filename="test.xlsx")