首页 > 编程语言 >PYTHON openpyxl 读取课程表，轮值排班表，输出每日班级简报

PYTHON openpyxl 读取课程表，轮值排班表，输出每日班级简报

时间：2024-03-13 22:13:43浏览次数：27  
标签：11 sheet openpyxl PYTHON value datacellsheet print 课程表 bookcellsheet

源文件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")

标签：11,sheet,openpyxl,PYTHON,value,datacellsheet,print,课程表,bookcellsheet
From： https://www.cnblogs.com/logicexpression/p/18071656

相关文章

  • Python实战：元组一次性数据的处理利器
    Python元组是一种不可变的序列类型，非常适合用于存储不应该被修改的数据。本文将深入探讨Python元组在处理一次性数据方面的优势和应用场景，包括元组的定义、操作、函数和方法，以及如何利用元组提高代码的可读性和性能。1.Python元组简介Python元组（tuple）是一种内置的数据类......
  • Python实战：Python集合的常见操作
    Python集合（set）是一种无序且元素唯一的容器，它是Python中一种基本的数据结构。本文将详细介绍Python集合的常见操作，包括创建集合、添加和删除元素、集合运算、集合推导式等。1.创建集合Python集合可以通过多种方式创建，包括直接赋值、使用内置函数set()和集合推导式等。示......
  • 【Python从入门到精通】函数详解
     【上图来源于网络图片】WhydoPythonprogrammerspreferdarkmode? Becauselightattractsbugs.Python的简洁性和易读性，认为这是吸引Python程序员的原因。【Python从入门到精通】专栏课程：1、【Python从入门到精通】认识Python2、【Python从入门到精通】变量&......
  • python环境变量问题备忘
    写了一个py脚本，手动在容器里能执行，但是配置了定时任务怎么都不执行，但是其他shell、js脚本却都能执行，没办法，既然shell能执行，然后就写了一个shell脚本间接调用py脚本，好了，现在到是能执行了，没想竟然执行报错了“YoumayneedtoaddPYTHONIOENCODING=utf-8toyourenvironment”......
  • PYTHON openpyxl 读写表格，输出幼儿园课程表，值班表
    #frompickleimportAPPENDimportopenpyxlimportdatetimefromtimeimportstrftime#importre#fromdatetimeimportdatetime#fromdateutil.parserimportparse#importpandasaspdimportoperatorwb=openpyxl.load_workbook(r'1_7date.xlsx')......
  • Python学习 第0天
    传送门前言Python太主流了，连普通大众都听说过Python了，小蟒蛇还是得要学一下的，而且跨平台特性是块宝这里采用的是B站的课程+菜鸟教程Python3菜鸟教程20分钟学完一遍python基础【零基础首选】2022最新版Python3.11全套视频教程，告别劣质教程目录Pyhton基础Python3基础语......
  • python代码小题（2）
    #引入常规库importmathprint(math.fabs(-5.5))print(math.floor(5.5))print(math.ceil(5.5))#随机数importrandoma=random.randint(1,10)print(a)#找钱问题  ##随机输入一个钱数，可以由几个50元和几个5元和几个1元组成a=int(input("收到的钱="))b=a//50c=a%......
  • 基于Django高校学校校园在线外卖订餐系统设计与实现（Pycharm+Python+Mysql）
     博主介绍：黄菊华老师《Vue.js入门与商城开发实战》《微信小程序商城开发》图书作者，CSDN博客专家，在线教育专家，CSDN钻石讲师；专注大学生毕业设计教育和辅导。所有项目都配有从入门到精通的基础知识视频课程，学习后应对毕业设计答辩。项目配有对应开发文档、开题报告、任务书、P......
  • Python File(文件) 方法
    在Python中，文件是一种常见的数据存储和交换方式。Python提供了丰富的文件方法来操作和处理文件。以下是关于Python文件方法的详细介绍，并附带多个案例说明：###`open()``open()`方法用于打开文件并返回一个文件对象。语法如下：```pythonfile=open(file_path,mode)``......
  • Python 异常处理
    在Python中，异常处理是一种机制，用于有效地处理程序运行时可能出现的错误情况，如除零错误、文件不存在等。通过异常处理，可以使程序在遇到异常时不会立即终止，而是能够捕获异常并进行适当的处理。以下是关于Python异常处理的详细介绍，并附带多个案例说明：###`try-except`语句`......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99