Python(openpyxl) + Excel 管理艾宾浩斯复习计划

摘要

    简单来说，人学习东西后能记住的内容会随时间衰减，为此诞生了“艾宾浩斯遗忘曲线”这一理论，为了记住所学知识，需要及时复习，本文将使用Python读写Excel来管理艾宾浩斯复习计划。

使用环境

• Python版本：Python3
• 必要模块：openpyxl
• 支持.xlsx的Excel软件

    openpyxl版本：2.5.4，安装方法：pip install openpyxl，如果安装失败，请用管理员身份运行。

使用效果

代码展示

def add_plan(wsheet):
    plan = []
    name = input("输入计划名: ")
    plan.append(name)
    print("输入开始日期: ")
    year = int(input("年: "))
    month = int(input("月: "))
    day = int(input("日: "))
    start_date = datetime.datetime(year, month, day).date()
    #print(start_date, type(start_date), start_date.toordinal() - datetime.datetime(1900, 1, 1).date().toordinal())
    #print(start_date.toordinal(), datetime.datetime(1900, 1, 1).date().toordinal())
    plan.append(start_date)
    interval = [1, 1, 2, 3, 5, 8, 15, 30, 60, 120, 240, 365]
    col = bytearray('B', 'ascii')
    new_row = wsheet.max_row + 1
    for i in interval:        
        plan.append("=%s%d+%d" % (str(col, 'ascii'), new_row, i))  #添加算式，e.g. =B2+1
        col[0] = col[0] + 1

    wsheet.append(plan)  #添加1行

    wsheet['A%d'%new_row].border = aborder()  #修改边框
    wsheet['A%d'%new_row].alignment = Alignment(wrap_text = True)  #修改对齐方式
    cell_start_date = wsheet['B%d'%new_row]
    #cell_start_date.font = Font(color=colors.BLUE)
    cell_start_date.fill = PatternFill("solid", fgColor=colors.BLUE)  #修改填充内容
    #print(cell_start_date.fill.fgColor.rgb, colors.BLUE, cell_start_date.fill.fgColor.rgb == colors.BLUE)
    cell_start_date.number_format = 'yy/mm/dd'  #修改数字格式
    cell_start_date.border = aborder()
    #print(cell_start_date.number_format)
    wsheet.column_dimensions['A'].width = 40.0  #修改列宽
    wsheet.column_dimensions['B'].width = 9.3 
    wsheet.row_dimensions[new_row].height = 32  #修改行高

    col[0] = 66  #'B'
    for i in range(3, (len(interval) + 3)):
        cell = wsheet.cell(row = new_row, column = i)
        cell.number_format = cell_start_date.number_format
        #cell.font = Font(color=colors.RED)
        cell.fill = PatternFill("solid", fgColor=colors.RED)
        cell.border = aborder()
        col[0] = col[0] + 1
        col_str = str(col, 'ascii')
        wsheet.column_dimensions[col_str].width = 9.3

def create_plans():
    print("Creat plans\r\n")
    wb = Workbook()
    ws = wb.active  #当前sheet
    ws.sheet_properties.tabColor = "DA1A0A"  #修改属性，标题颜色
    ws.title = "today"  #修改标题    
    return wb

def create_plan_info(wsheet):
    wsheet['A1'] = "            计划"
    wsheet['B1'] = "            日程"
    wsheet.merge_cells('B1:N1')  #合并单元格
    wsheet['A1'].border = aborder()
    fill = PatternFill("solid", fgColor=colors.DARKYELLOW)
    wsheet['A1'].fill = fill
    style_range(wsheet, 'B1:N1', border = aborder(), fill = fill) #填充样式，合并的单元格

def add_course(wbook, title = ""):   
    ws = wbook.create_sheet(title)
    create_plan_info(ws)
    while(True):
        option = input("是否添加计划？（1-是 0-否）")
        if option == '1':
            add_plan(ws)
        elif option == '0':
            break

def update_today(wbook):
    def calculate_days(wsheet, cell):
        #递归计算
        if isinstance(cell.value, datetime.datetime):
            return 0
        index = cell.value.index("+") + 1
        days = int(cell.value[index:len(cell.value)])
        nextcell = cell.value[1:index-1]
        return days + calculate_days(wsheet, wsheet[nextcell])

    today = datetime.datetime.today().date().toordinal()
    print(today, type(today))
    print(wbook.sheetnames)    
    wbook.remove(wbook["today"])  #删除sheet
    wbook.create_sheet("today", 0)   #插入sheet 开头位置
    for ws in wbook.worksheets:
        if ws.title == "today":
            wstoday = ws
            #ws.delete_cols(ws.min_column, ws.max_column)  #删除列，只删除内容，不完全删除
            ws.column_dimensions['A'].width = 80.0
            ws.column_dimensions['B'].width = 14.0
            ws.sheet_properties.tabColor = "DA1A0A"
            continue
        #print(ws.title) 
        if wstoday['A1'].value == None:
            wstoday['A1'].value = "点击文字（超链接）跳转，手动填充绿色表示复习完成，蓝色表示建档日"
        for row in ws.iter_rows(min_row = 2, max_row = ws.max_row, max_col = ws.max_column):
            for cell in row:
                #print(cell.fill.fgColor.rgb)
                if cell.fill.fgColor.rgb[2:8] == colors.BLUE[2:8]:  #xlsx读出来的是蓝色
                    start_day = cell.value.date().toordinal()                    
                    continue
                if cell.fill.fgColor.rgb[2:8] != colors.RED[2:8]:  #xlsx读出来的不是红色
                    continue
                if isinstance(cell.value, str) and '=' in cell.value and '+' in cell.value:
                    days = calculate_days(ws, cell)
                    #print(days, today, start_day)
                    if today - start_day >= days:                        
                        plan = []
                        plan.append(ws["A%d"%cell.row].value)
                        plan.append(datetime.date.fromordinal(start_day + days))
                        wstoday.append(plan)     
                        hlink = Hyperlink(ref = "B%d"%wstoday.max_row, 
                                          location = "%s!%s"%(ws.title, cell.coordinate))                                               
                        #hlink = Hyperlink(ref = "A%d"%wstoday.max_row, location = "%s!A%d"%(ws.title, cell.row))
                        cell_td = wstoday["A%d"%wstoday.max_row]
                        cell_td.alignment = Alignment(horizontal = "center")
                        cell_td.hyperlink = hlink                                              
                        cell_td.style = "Hyperlink"  #修改样式    
                        '''
                        其他样式参考：https://openpyxl.readthedocs.io/en/stable/styles.html#styling-merged-cells
                        '''                                                                   
                #print(cell.value, type(cell.value), cell.row, cell.column, cell.col_idx)
                #if cell.hyperlink:
                #    print(cell.hyperlink.target, cell.hyperlink.location, cell.hyperlink.ref)

def option(wbook):
    print("选项:\r\n \
        1.刷新今天的复习计划\r\n \
        2.添加科目\r\n \
        3.添加计划\r\n \
        4.退出")

    tip = "输入科目名称: "

    option = input("输入数字选择: ")
    if option == '1':
        update_today(wbook)
    elif option == '2':        
        add_course(wbook, input(tip))
        while(True):
            option = input("是否继续添加科目？（1-是 0-否）")
            if option == '1':
                add_course(wbook, input(tip))                
            elif option == '0':
                break
    elif option == '3':        
        sheetnames = wbook.sheetnames
        print(sheetnames)
        course = input(tip)
        if course in sheetnames and course != "today":
            add_plan(wbook[course])
            while(True):
                option = input("是否继续添加计划？（1-是 0-否）")
                if option == '1':
                    add_plan(wbook[course])
                elif option == '0':
                    break
        else:
            print("请输入正确的科目名称")
    elif option == '4':
        pass
    else:
        print("错误输入")
    try:
        wbook.save("plans.xlsx")        
    except Exception:
        print("\r\n!!出错了!!\r\n请先关闭plans.xlsx\r\n")    
    return option

注：以上代码不完整，不能直接运行，下载链接：https://download.csdn.net/download/tttwister/10539160

代码有很多可以改进的地方，懒得改了，满足使用即可。

转载请注明出处。