原始檔excel及python原始檔
連結：https://pan.baidu.com/s/1uswO_33jrgE3nvaJv47wGw
提取碼：clnw
#from pickle import APPEND import openpyxl import datetime from time import strftime #import re #from datetime import datetime #from dateutil.parser import parse #import pandas as pd import operator wb = openpyxl.load_workbook(r'1_7date.xlsx') now = datetime.datetime.now() #print(now.strftime("%Y-%m-%d %H:%M:%S")); print(now.strftime("%Y-%m-%d")) #計算單雙週 sheet = wb['班級日報-模板'] B4 = sheet['B4'].value date1 = datetime.date(now.year,now.month,now.day) date2 = datetime.date(now.year,2,25) delta = date1-date2 numweekend = (int)(delta.days/7)+1 numweekend = f"第{numweekend}周" print(numweekend) weekend = (int)(delta.days/7)%2 weekend_name=['雙週','單週'][weekend] print(weekend_name) print('*'*70) #計算日期和星期 #print(B4) sheet.cell(4,2,value= now.strftime("%Y年%m月%d日")) A4_2 = sheet.cell(4,2).value print(A4_2) #print(weekday) weekday = now.weekday() weekday_name = ['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期天'][weekday] print(weekday_name) #讀取表格sheet sheet1 = wb['值日表'] sheet2 = wb['返程排班表'] sheet3 = wb['課程表'] print('最小行：',sheet2.min_row) print('最大行：',sheet2.max_row) print('最小列：',sheet2.min_column) print('最大列：',sheet2.max_column) #擷取年份，並判斷是否相等 print('*'*70) B4= sheet2['B4'].value print(B4) print(type(B4)) B4_date = B4[:5] B4_YEAR = B4[:5] print(B4_date) print(type(B4_date)) k = operator.eq(B4,B4_date) print(operator.eq(B4,B4_date)) print(operator.eq(B4_date,B4_YEAR)) print(type(k)) if k == 0 : print('true') else : print('flase') #功能完善 #查詢日期 print('查詢日期，星期幾，單雙週，第幾周') wb = openpyxl.load_workbook(r'1_7date20240312.xlsx') sheet = wb['班級日報-模板'] datacellsheet = wb['值日表'] datacell = sheet['B4'] #print(datacell) sheet.cell(4,2,value= now.strftime("%Y年%m月%d日")) A4_2 = sheet.cell(4,2).value print(A4_2) datacell.value = A4_2 #星期幾 weekdaycell = sheet['C4'] weekdaycell.value = weekday_name #單雙週 weekendcell = sheet['C5'] weekendcell.value = weekend_name #第幾周 numweekendcell = sheet['B5'] numweekendcell.value = numweekend #值班表 print('值班表') if(operator.eq(weekend_name,'雙週')): print('ture') if(operator.eq(weekday_name,'星期一')): print('星期一') sb_9 = datacellsheet['B9'].value print(sb_9) sb_10 = datacellsheet['B10'].value sb_11 = datacellsheet['B11'].value sb_12 = datacellsheet['B12'].value b_10 = sheet['B10'] b_10.value =sb_9 b_11 = sheet['B11'] b_11.value =sb_10 b_12 = sheet['B12'] b_12.value =sb_11 b_13 = sheet['B13'] b_13.value =sb_12 elif(operator.eq(weekday_name,'星期二')): print('星期二') c_9 = datacellsheet['C9'].value print(c_9) c_10 = datacellsheet['C10'].value c_11 = datacellsheet['C11'].value c_12 = datacellsheet['C12'].value #b10 = sheet['B10'].value #print(b10) b_10 = sheet['B10'] b_10.value = c_9 b_11 = sheet['B11'] b_11.value = c_10 b_12 = sheet['B12'] b_12.value = c_11 b_13 = sheet['B13'] b_13.value = c_12 elif(operator.eq(weekday_name,'星期三')): print('星期三') d_9 = datacellsheet['D9'].value print(d_9) d_10 = datacellsheet['D10'].value d_11 = datacellsheet['D11'].value d_12 = datacellsheet['D12'].value b_10 = sheet['B10'] b_10.value = d_9 b_11 = sheet['B11'] b_11.value = d_10 b_12 = sheet['B12'] b_12.value = d_11 b_13 = sheet['B13'] b_13.value = d_12 elif(operator.eq(weekday_name,'星期四')): print('星期四') e_9 = datacellsheet['E9'].value print(e_9) e_10 = datacellsheet['E10'].value e_11 = datacellsheet['E11'].value e_12 = datacellsheet['E12'].value b_10 = sheet['B10'] b_10.value = e_9 b_11 = sheet['B11'] b_11.value = e_10 b_12 = sheet['B12'] b_12.value = e_11 b_13 = sheet['B13'] b_13.value = e_12 elif(operator.eq(weekday_name,'星期五')): print('星期五') f_9 = datacellsheet['F9'].value print(f_9) f_10 = datacellsheet['F10'].value f_11 = datacellsheet['F11'].value f_12 = datacellsheet['F12'].value b_10 = sheet['B10'] b_10.value = f_9 b_11 = sheet['B11'] b_11.value = f_10 b_12 = sheet['B12'] b_12.value = f_11 b_13 = sheet['B13'] b_13.value = f_12 else: print('false') if(operator.eq(weekday_name,'星期一')): print('星期一') sb_2 = datacellsheet['B2'].value print(sb_2) sb_3 = datacellsheet['B3'].value sb_4 = datacellsheet['B4'].value sb_5 = datacellsheet['B5'].value b_10 = sheet['B10'] b_10.value =sb_2 b_11 = sheet['B11'] b_11.value =sb_3 b_12 = sheet['B12'] b_12.value =sb_4 b_13 = sheet['B13'] b_13.value =sb_5 elif(operator.eq(weekday_name,'星期二')): print('星期二') c_2 = datacellsheet['C2'].value print(c_2) c_3 = datacellsheet['C3'].value c_4 = datacellsheet['C4'].value c_5 = datacellsheet['C5'].value #b10 = sheet['B10'].value #print(b10) b_10 = sheet['B10'] b_10.value = c_2 b_11 = sheet['B11'] b_11.value = c_3 b_12 = sheet['B12'] b_12.value = c_4 b_13 = sheet['B13'] b_13.value = c_5 elif(operator.eq(weekday_name,'星期三')): print('星期三') d_2 = datacellsheet['D2'].value print(d_2) d_3 = datacellsheet['D3'].value d_4 = datacellsheet['D4'].value d_5 = datacellsheet['D5'].value b_10 = sheet['B10'] b_10.value = d_2 b_11 = sheet['B11'] b_11.value = d_3 b_12 = sheet['B12'] b_12.value = d_4 b_13 = sheet['B13'] b_13.value = d_5 elif(operator.eq(weekday_name,'星期四')): print('星期四') e_2 = datacellsheet['E2'].value print(e_2) e_3 = datacellsheet['E3'].value e_4 = datacellsheet['E4'].value e_5 = datacellsheet['E5'].value b_10 = sheet['B10'] b_10.value = e_2 b_11 = sheet['B11'] b_11.value = e_3 b_12 = sheet['B12'] b_12.value = e_4 b_13 = sheet['B13'] b_13.value = e_5 elif(operator.eq(weekday_name,'星期五')): print('星期五') f_2 = datacellsheet['F2'].value print(f_2) f_3 = datacellsheet['F3'].value f_4 = datacellsheet['F4'].value f_5 = datacellsheet['F5'].value b_10 = sheet['B10'] b_10.value = f_2 b_11 = sheet['B11'] b_11.value = f_3 b_12 = sheet['B12'] b_12.value = f_4 b_13 = sheet['B13'] b_13.value = f_5 #放學輪值家長 returncellsheet = wb['返程排班表'] print('放學輪值家長') dd = '2023年9月11日 星期一' #dd = now.strftime("%Y年%m月%d日") print(dd) returncell=returncellsheet['B4'] print(returncell.value) print(returncellsheet.cell(4,2).value) for i in range(1,53,3): print(i) for j in range(2,9): #print(j) if(operator.eq(returncellsheet.cell(i,j).value,dd)): print(i) print(j) break if(operator.eq(returncellsheet.cell(i,j).value,dd)): #print(i) #print(j) break morn = returncellsheet.cell(i+1,j).value after = returncellsheet.cell(i+2,j).value print(morn) print(after) b_20 = sheet['B20'] b_20.value = morn c_20 = sheet['C20'] c_20.value = after #課程表 print('課程表') bookcellsheet = wb['課程表'] if(operator.eq(weekday_name,'星期一')): b_3= bookcellsheet['B3'].value b_4= bookcellsheet['B4'].value b_5= bookcellsheet['B5'].value b_6= bookcellsheet['B6'].value b_8= bookcellsheet['B8'].value b_9= bookcellsheet['B9'].value b_10= bookcellsheet['B10'].value b_11= bookcellsheet['B11'].value b_26 = sheet['B26'] b_26.value = b_3 b_27 = sheet['B27'] b_27.value = b_4 b_28 = sheet['B28'] b_28.value = b_5 b_29 = sheet['B29'] b_29.value = b_6 b_30 = sheet['B30'] b_30.value = b_8 b_31 = sheet['B31'] b_31.value = b_9 b_32 = sheet['B32'] b_32.value = b_10 b_33 = sheet['B33'] b_33.value = b_11 elif(operator.eq(weekday_name,'星期二')): c_3= bookcellsheet['C3'].value c_4= bookcellsheet['C4'].value c_5= bookcellsheet['C5'].value c_6= bookcellsheet['C6'].value c_8= bookcellsheet['C8'].value c_9= bookcellsheet['C9'].value c_10= bookcellsheet['C10'].value c_11= bookcellsheet['C11'].value b_26 = sheet['B26'] b_26.value = c_3 b_27 = sheet['B27'] b_27.value = c_4 b_28 = sheet['B28'] b_28.value = c_5 b_29 = sheet['B29'] b_29.value = c_6 b_30 = sheet['B30'] b_30.value = c_8 b_31 = sheet['B31'] b_31.value = c_9 b_32 = sheet['B32'] b_32.value = c_10 b_33 = sheet['B33'] b_33.value = c_11 elif(operator.eq(weekday_name,'星期三')): d_3= bookcellsheet['D3'].value d_4= bookcellsheet['D4'].value d_5= bookcellsheet['D5'].value d_6= bookcellsheet['D6'].value d_8= bookcellsheet['D8'].value d_9= bookcellsheet['D9'].value d_10= bookcellsheet['D10'].value d_11= bookcellsheet['D11'].value b_26 = sheet['B26'] b_26.value = d_3 b_27 = sheet['B27'] b_27.value = d_4 b_28 = sheet['B28'] b_28.value = d_5 b_29 = sheet['B29'] b_29.value = d_6 b_30 = sheet['B30'] b_30.value = d_8 b_31 = sheet['B31'] b_31.value = d_9 b_32 = sheet['B32'] b_32.value = d_10 b_33 = sheet['B33'] b_33.value = d_11 elif(operator.eq(weekday_name,'星期四')): e_3= bookcellsheet['E3'].value e_4= bookcellsheet['E4'].value e_5= bookcellsheet['E5'].value e_6= bookcellsheet['E6'].value e_8= bookcellsheet['E8'].value e_9= bookcellsheet['E9'].value e_10= bookcellsheet['E10'].value e_11= bookcellsheet['E11'].value b_26 = sheet['B26'] b_26.value = e_3 b_27 = sheet['B27'] b_27.value = e_4 b_28 = sheet['B28'] b_28.value = e_5 b_29 = sheet['B29'] b_29.value = e_6 b_30 = sheet['B30'] b_30.value = e_8 b_31 = sheet['B31'] b_31.value = e_9 b_32 = sheet['B32'] b_32.value = e_10 b_33 = sheet['B33'] b_33.value = e_11 elif(operator.eq(weekday_name,'星期五')): f_3= bookcellsheet['F3'].value f_4= bookcellsheet['F4'].value f_5= bookcellsheet['F5'].value f_6= bookcellsheet['F6'].value f_8= bookcellsheet['F8'].value f_9= bookcellsheet['F9'].value f_10= bookcellsheet['F10'].value f_11= bookcellsheet['F11'].value b_26 = sheet['B26'] b_26.value = f_3 b_27 = sheet['B27'] b_27.value = f_4 b_28 = sheet['B28'] b_28.value = f_5 b_29 = sheet['B29'] b_29.value = f_6 b_30 = sheet['B30'] b_30.value = f_8 b_31 = sheet['B31'] b_31.value = f_9 b_32 = sheet['B32'] b_32.value = f_10 b_33 = sheet['B33'] b_33.value = f_11 wb.save(filename="./test.xlsx")