摘要
简单来说,人学习东西后能记住的内容会随时间衰减,为此诞生了“艾宾浩斯遗忘曲线”这一理论,为了记住所学知识,需要及时复习,本文将使用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
代码有很多可以改进的地方,懒得改了,满足使用即可。
转载请注明出处。