python: 读取EXCEL的请假数据

 

https://www.oreilly.com/library/view/programming-python-4th/9781449398712/
https://www.oreilly.com/library/view/learning-python-5th/9781449355722/

 

# encoding: utf-8
# 版权所有 2023 涂聚文有限公司
# 许可信息查看：
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 311
# Datetime  : 2023/9/2 21:31
# User      : geovindu
# Product   : PyCharm
# Project   : LukfookLeaveCalculation
# File      : Holiday.py
# explain   : 学习

class Holiday(object):
    """
    假期实体类

    """
    def __init__(self):
        self.hName=""
        self.hId=0
        self.workTime=0

    @property
    def HolidayId(self):
        """
        假期ID 获取
        :return:
        """
        return self.hId

    @HolidayId.setter
    def HolidayId(self,hId:int):
        """
        假期ID  设置
        :param hId:
        :return:
        """
        self.hId=hId


    @property
    def HolidayName(self):
        """
        假期名称
        :return:
        """
        return self.hName
    @HolidayName.setter
    def HolidayName(self,hName):
        """
        假期名称
        :param hName:
        :return:
        """
        self.hName=hName

    @property
    def WorkTime(self):
        """
        工作/请假/休假时间（小时制）
        :return:
        """
        return self.workTime

    @WorkTime.setter
    def WorkTime(self,workTime):
        """
        工作/请假/休假时间（小时制）
        :param workTime:
        :return:
        """
        self.workTime=workTime


# encoding: utf-8
# 版权所有 2023 涂聚文有限公司
# 许可信息查看：
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 311
# Datetime  : 2023/9/2 20:20
# User      : geovindu
# Product   : PyCharm
# Project   : LukfookLeaveCalculation
# File      : Employee.py
# explain   : 学习

import sys
import os

class employee(object):
    """
    职员实体类
    """
    def __init__(self):
        self.empId=0
        self.empNO=""
        self.empName=""
        self.empDep=""
        self.empHolidays=[]
        self.activeDate=""


    @property
    def EmployeeId(self):
        """
        ID
        :return:
        """
        return self.empId

    @EmployeeId.setter
    def EmployeeId(self,empId):
        """
        id
        :param empId:
        :return:
        """
        self.empId=empId

    @property
    def EmployeeNo(self):
        """
        工号
        :return:
        """
        return self.empNO

    @EmployeeNo.setter
    def EmployeeNo(self,empNo):
        """
        工号
        :param empNo:
        :return:
        """
        self.empNO=empNo
    @property
    def EmployeeDep(self):
        """

        :return:
        """
        return  self.empDep;

    @EmployeeDep.setter
    def EmployeeDep(self,empDep):
        """

        :param empDep:
        :return:
        """
        self.empDep=empDep


    @property
    def EmployeeName(self):
        """
        姓名
        :return:
        """
        return  self.empName

    @EmployeeName.setter
    def EmployeeName(self,empName):
        """
        姓名
        :param empName:
        :return:
        """
        self.empName=empName

    @property
    def EmployeeHolidays(self):
        """
        假期
        :return:
        """
        return self.empHolidays


    @EmployeeHolidays.setter
    def EmployeeHolidays(self,empHolidays)->list:
        """
        假期
        :param empHolidays:
        :return:
        """
        self.empHolidays=empHolidays

    @property
    def EmployeeActiveDate(self):
        """
        发生时间
        :return:
        """
        return self.activeDate

    @EmployeeActiveDate.setter
    def EmployeeActiveDate(self,activeDate):
        """
        发生时间
        :param activeDate:
        :return:
        """
        self.activeDate=activeDate







# encoding: utf-8
# 版权所有 2023 涂聚文有限公司
# 许可信息查看：
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 311
# Datetime  : 2023/9/3 7:04
# User      : geovindu
# Product   : PyCharm
# Project   : LukfookLeaveCalculation
# File      : EmpLoyeeHolidaysGet.py
# explain   : 学习


import sys
import os
import Common.Utils
import Model.Holiday


class EmpLoyeeHolidaysGet(object):
    """
    抽取需要的数据
    """

    def getYear(self,strsource):
        """

        :param strsource:
        :return:
        """
        refloat = 0
        if (strsource != None):
            if ("年" in strsource):
                refloat=Common.Utils.Utils.getYear(strsource)
        return refloat

    def getMonth(self, strsource):
        """

        :param strsource:
        :return:
        """
        refloat = 0
        if (strsource != None):
            if ("月" in strsource):
                refloat = Common.Utils.Utils.getMonth(strsource)
        return refloat

    def getDay(self, strsource):
        """

        :param strsource:
        :return:
        """
        refloat = 0
        if (strsource != None):
            if ("日" in strsource):
                refloat = Common.Utils.Utils.getDay(strsource)
        return refloat

    def getHolidays(self,strsource):
        """
        獲得假期限列表
        :param strsource:
        :return:
        """
        hday=[]
        if (strsource != None):
            if("星期" not in strsource):
                if("nan" not in strsource):
                    spstr = strsource.split(' ')  # 有两个，有换行符，两个假期，一定要这换行符  NaN
                    if (len(spstr) > 1):
                        for i in range(len(spstr)):
                            sk = spstr[i]
                            #print("for sk", sk)
                            hd = Model.Holiday.Holiday()
                            hd=EmpLoyeeHolidaysGet.checkHoliday(self,sk)
                            hday.append(hd)

                    elif (len(spstr) == 1):
                        sk = spstr[0]
                        #print("sk",sk)
                        hd = Model.Holiday.Holiday()
                        hd = EmpLoyeeHolidaysGet.checkHoliday(self,sk)
                        hday.append(hd)

                    else:
                        hday = []

        else:
            hday = []

        return hday


    def checkHoliday(self,strsource):
        """
        检测是什么假期
        :param strsource:
        :return:
        """
        hd=Model.Holiday.Holiday()
        print("chek")
        if ("例休" in strsource):
            #print("cehck例休")
            hd.HolidayId=1
            hd.HolidayName="例休"
            hd.WorkTime=Common.Utils.Utils.getRegularHoliday(strsource)

        if ("特假" in strsource):
            hd.HolidayId=2
            hd.HolidayName="特假"
            hd.workTime=Common.Utils.Utils.getSpecialLeave(strsource)

        if ("補休" in strsource):
            hd.HolidayId=3
            hd.HolidayName="補休"
            hd.workTime = Common.Utils.Utils.getDeferredHoliday(strsource)


        if ("加班" in strsource):
            hd.HolidayId=4
            hd.HolidayName="加班"
            hd.workTime = Common.Utils.Utils.getOvertime(strsource)

        if ("事假" in strsource):
            hd.HolidayId=5
            hd.HolidayName="事假"
            hd.workTime = Common.Utils.Utils.getPersonalLeave(strsource)

        if ("年假" in strsource):
            hd.HolidayId = 6
            hd.HolidayName = "年假"
            hd.workTime = Common.Utils.Utils.getAnnualLeave(strsource)


        if ("病假" in strsource):
            hd.HolidayId = 7
            hd.HolidayName = "病假"
            hd.workTime = Common.Utils.Utils.getSickLeave(strsource)


        if ("育兒假" in strsource):
            hd.HolidayId = 8
            hd.HolidayName = "育兒假"
            hd.workTime = Common.Utils.Utils.getChildcareLeave(strsource)

        if ("遲到" in strsource):
            hd.HolidayId = 9
            hd.HolidayName = "遲到"
            hd.workTime = Common.Utils.Utils.getLateTime(strsource)


        return  hd



# This is a sample Python script.
# encoding: utf-8
# 版权所有 2023 涂聚文有限公司
# 许可信息查看：
# 描述：
# Author    : geovindu,Geovin Du 涂聚文.
# IDE       : PyCharm 2023.1 python 311
# Datetime  : 2023/9/2 20:20
# User      : geovindu
# Product   : PyCharm
# Project   : LukfookLeaveCalculation
# File      : main.py
# explain   : 学习
# Press Shift+F10 to execute it or replace it with your code.
# Press Double Shift to search everywhere for classes, files, tool windows, actions, and settings.

import xlrd
import xlwt
import xlwings as xw
import xlsxwriter

import openpyxl as openws
import pandas as pd
import numpy as np
import pandasql
import os
import sys
import Common.Utils
import BLL.EmpLoyeeHolidaysGet
import Model.Employee



def print_hi(name):
    # Use a breakpoint in the code line below to debug your script.
    print(f'Hi, {name}')  # Press Ctrl+F8 to toggle the breakpoint.


# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    print_hi('PyCharm')
    unstr=Common.Utils.Utils()
    strtest="年假2小時 事假0.5小時"  # 换行符
    #strtest-strtest.replace('_x000D_', ' ', regex=True)
    #strtest = "NaN"
    spstr = strtest.split(" ")
    thbll = BLL.EmpLoyeeHolidaysGet.EmpLoyeeHolidaysGet()
    tt=thbll.getHolidays(spstr[0])
    print("tt",tt[0].WorkTime,spstr)

    dataframe1 = pd.read_excel('2023年7月.xlsx')

    #print(dataframe1)
    #for idx, data in dataframe1.iterrows():
        #print("[{}]: {}".format(idx, data))
    # 第1列为日期，其他列是员工对应的列的考勤
    print("*************")
    print(dataframe1.index)
    print("*************")
    print(dataframe1.shape) #行列
    datarowcol=dataframe1.shape
    print("行：",datarowcol[0],"，列：",datarowcol[1])
    excelRows=datarowcol[0]
    excelColumns=datarowcol[1]
    print("*********列标题****")
    print(dataframe1.columns) #第一行标题   pandas.core.indexes.base.Index
    titls=dataframe1.columns.to_list()
    print(titls)
    print("标题：",titls[1])
    year=thbll.getYear(titls[1])
    print("*************",year)
    dmps = []
    # 姓名，工号，序号，部门 0行至3行为员工资料  第一行为标题，不算行的内容 可以切片方式GET数据
    #序号
    idlist =[]
    dataId = dataframe1.loc[0:0]
    for idx, datadd in dataId.iterrows():
        print("[{}]: {}".format(idx, datadd))
        idlist=datadd.to_list()
        for idd in range(1,len(idlist)):
            #employvee=Model.Employee.employee()
            #employvee.EmployeeId=idd
            #dmps.append(employvee)
            print(idd)
    print("*******序号******")
    #工号
    nolist=[]
    dataNo = dataframe1.loc[1:1]
    for idx, dataoo in dataNo.iterrows():
        print("[{}]: {}".format(idx, dataoo))
        nolist=dataoo.to_list()

    print("*******工号******")
    #部门
    deplist=[]
    dataDep = dataframe1.loc[2:2]
    for idx, datapp in dataDep.iterrows():
        print("[{}]: {}".format(idx, datapp))
        deplist=datapp.to_list()
    print("*******部门******")

    #姓名
    namelist=[]
    dataName = dataframe1.loc[3:3]
    for idx, datann in dataName.iterrows():
        print("[{}]: {}".format(idx, datann))
        namelist=datann.to_list()

    print("*******姓名******")

    for i in range(1,len(namelist)):
        employvee = Model.Employee.employee()
        employvee.EmployeeId=idlist[i]
        employvee.EmployeeNo=nolist[i]
        employvee.EmployeeName=namelist[i]
        employvee.EmployeeDep=deplist[i]
        dmps.append(employvee)




    for ob in dmps:
         print(ob.EmployeeId,ob.EmployeeNo,ob.EmployeeName,ob.EmployeeDep)


    #data1 = dataframe1.loc[0:3]
    #print(type(data1))
    #print(data1)
    #for idx, data in data1.iterrows():
        #print("[{}]: {}".format(idx, data))
    #print("*************")

    AnnualLeave=0
    Overtime=0
    DeferredHoliday=0
    SpecialLeave=0
    PersonalLeave=0
    SickLeave=0
    ChildcareLeave=0
    LateTime=0
    # 内容
    readrows=(excelRows-1)-3  #索此值从零开始，所以总数减一，再减三行统计的
    data4 = dataframe1.loc[4:readrows]  #第四行开始内空
    for idx, datavalue in data4.iterrows():
        #strnum=Common.Utils.Utils.getAnnualLeave(data)
        print("[{}]: {}".format(idx, datavalue))
        slist=datavalue.tolist()
        hbll=BLL.EmpLoyeeHolidaysGet.EmpLoyeeHolidaysGet() #病假4小時_x000D_事假0.5小時 未处理
        #print(slist)
        for i in range(0, 1):
            strvalue = str(slist[i])
            print("get value:", strvalue.replace('nan', ''), type(strvalue))
            mon=hbll.getMonth(strvalue)
            day=hbll.getDay(strvalue)
            print("*******时间************",mon,day)
        for i in range(1,len(slist)):
            #strvalue=str(slist[i]).replace(r'\s+|\\n', ' ', regex=True)  _x000D_
            #isnan=np.isnan(slist[i])
            #if(isnan):
            strvalue = str(slist[i]).replace('_x000D_', ' ')  # 规换单元格的换行符，否则处理不了正确数据
            #strvalue = str(slist[i]).replace('nan', ' ')
            print("get value:",strvalue.replace('nan',''),type(strvalue))
            strnums = hbll.getHolidays(strvalue)  # float str
            if(len(strnums)>0):
                for sn in range(len(strnums)):
                    print("str:",strnums[sn].HolidayId,strnums[sn].HolidayName,strnums[sn].WorkTime)
                    if strnums[sn].HolidayId==6:
                        AnnualLeave=AnnualLeave+strnums[sn].WorkTime
                    if strnums[sn].HolidayId == 4:
                        Overtime=Overtime+strnums[sn].WorkTime
                    if strnums[sn].HolidayId == 3:
                        DeferredHoliday = DeferredHoliday + strnums[sn].WorkTime
                    if strnums[sn].HolidayId == 2:
                        SpecialLeave=SpecialLeave+strnums[sn].WorkTime
                    if strnums[sn].HolidayId == 5:
                        PersonalLeave=PersonalLeave+strnums[sn].WorkTime
                    if strnums[sn].HolidayId == 7:
                        SickLeave=SickLeave+strnums[sn].WorkTime
                    if strnums[sn].HolidayId == 8:
                        ChildcareLeave=ChildcareLeave+strnums[sn].WorkTime
                    if strnums[sn].HolidayId == 9:
                        LateTime=LateTime+strnums[sn].WorkTime


            else:
                print("0")

        print("类型：",type(datavalue))
    print("*************")
    print("年假合计：",AnnualLeave,"加班合计：",Overtime,"补休合计：",DeferredHoliday,"特假合计：",SpecialLeave)
    print("事假合计",PersonalLeave,"病假合计：",SickLeave,"迟到合计：",LateTime,"育儿假合计：",ChildcareLeave)
    print("*************")

    dataframe2 = openws.load_workbook("2023年7月.xlsx")
    # Define variable to read sheet
    dataframe2 = dataframe2.active
    # Iterate the loop to read the cell values
    #for row in range(0, dataframe2.max_row):
        #for col in dataframe2.iter_cols(1, dataframe2.max_column):
           #print(col[row].value)

    # a single cell
    #v1 = xw.range("A1:A7").value
    #v2 = xw.range("F5").value
    #print("Result:", v1)


# See PyCharm help at https://www.jetbrains.com/help/pycharm/