import os

cmd = 'pip install openpyxl -i https://pypi.doubanio.com/simple'
os.system(cmd)

from openpyxl import load_workbook

# 源文件
filename = 'students_multisheets.xlsx'
# 目标文件
target = 'students_cellsrange.xlsx'
# 开始行
x1 = 2
# 结束行
x2 = 13
# 开始列
y1 = 2
# 结束列
y2 = 8

if os.path.exists(filename):
    wb = load_workbook(filename)
    ws = wb['第一学期']
    third_ws = wb.create_sheet()
    third_ws.title = '第三学期'

    for i in range(x1, x2 + 1):
        for j in range(y1, y2 + 1):
            if j == 6:
                continue
            nx = i - x1 + 1
            ny = j - y1 + 1

            if ws.cell(i, j).value is None:
                # 向上查找第一个不空的值进行填充
                # 当前行 i,当前列 j
                k = i
                while ws.cell(k, j).value is None:
                    k = k - 1
                v = ws.cell(k, j).value
            else:
                v = ws.cell(i, j).value

            third_ws.cell(nx, ny).value = v
    wb.save(target)