将特定学校课表转换为wakeup转换为可接受的格式

最新推荐文章于 2024-11-14 13:48:50 发布

fafafat

最新推荐文章于 2024-11-14 13:48:50 发布

阅读量190

点赞数

文章标签： python 开发语言

本文链接：https://blog.csdn.net/fafafat/article/details/127123380

版权

该博客介绍了一个Python脚本，用于读取Excel文件中特定区域的数据，处理合并单元格并提取关键信息，如课程名称、星期、开始节数、结束节数、老师和地点。脚本将数据清洗后输出到CSV文件，便于进一步分析和处理。

摘要由CSDN通过智能技术生成

import openpyxl
import re
import csv

def cell_to_value_and_range(cell, sheet):
    mercells =  sheet.merged_cells
    for ran in mercells:
        if cell.coordinate in ran:
            min_row, min_col = ran.min_row, ran.min_col
            class_range = {'old':(min_row, min_col), 'new':(min_row + ran.size['rows']-1, min_col + ran.size['columns']-1)}
            return {'size':ran.size, 'value':sheet.cell(min_row, min_col).value, 'range':class_range}
    
    class_range = {'old':(cell.row, cell.column), 'new':(cell.row, cell.column)}
    return {'size':1, 'value':cell.value, 'range':class_range}


def single_process(var):
    result = []
    results = re.findall(r'(\w+)\s*\(.*\)\s*\((\w*)\)\s*\((\S+)\s*(\S*)\)', var['value'])
    for each in results:
        each = list(each)
        each[2] = each[2].replace(',', '、')
        l = []
        class_name, teacher, class_week, room = each
        start = var['range']['old'][0]-4
        end = var['range']['new'][0]-4
        week = var['range']['old'][1]-1
        l = [class_name, week, start, end, teacher, room, class_week]
        result.append(l)
    
    return result

def remove_repeat(l):
    l1 = []
    for each in l:
        if(each not in l1):
            l1.append(each)
    return l1

def output(origin_path='kebiao.xlsx'):
    wb = openpyxl.load_workbook(origin_path)
    sheet = wb[wb.sheetnames[0]]
    blocks = sheet['B5':'H14']
    temp = [['课程名称','星期','开始节数','结束节数','老师','地点','周数']]
    for ncol in range(len(blocks[0])):
        for nrow in range(len(blocks)):
            var = cell_to_value_and_range(blocks[nrow][ncol], sheet)

            if isinstance (var['value'], str):
                single_block_out = single_process(var)
                temp.extend(single_block_out)
    
    result = remove_repeat(temp)

    f = open('out.csv', 'w', newline='')
    writer = csv.writer(f)
    writer.writerows(result)
    f.close()

output()