Python数据处理：读取Excel并计算监所工作量-优快云博客

本文链接：https://blog.youkuaiyun.com/l19901218/article/details/130647705

import pandas as pd
from PrisonsEntity import PrisonsEntity
from PrisonsElement import PrisonsElement

‘’’
Global Variable
‘’’

数据文件路径

Read_File_Path = r’C:\Users\lWX1168142\Desktop\work\test1.xlsx’
Write_File_Path = r’C:\Users\lWX1168142\Desktop\work\Work.xlsx’

Read_File_Data = pd.read_excel(Read_File_Path, header=None)
Write_File_Data = pd.read_excel(Write_File_Path, header=None, sheet_name=‘每日’)

数据文件的宽高

Read_File_RowLength = Read_File_Data.shape[0]
Read_File_ColLength = Read_File_Data.shape[1]

'监所’所在位置

Read_File_PrisonsPositionRow = 0 # 行坐标
Read_File_PrisonsPositionCol = 0 # 列坐标

用来存放PrisonsElement对象

PrisonsElementList = []

不同监所的工作量系数

PrisonsWorkValueDictionary = {
‘闵行’: 1,
‘金山’: 3,
‘光明’: 5,
‘虹口’: 1,
‘上海港’: 2,
‘嘉定’: 2
}

写入表中栏对应的位置

TitleIndexDictionary = {
‘人员’: 2,
‘驾驶(提)’: 3,
‘驾驶(还)’: 4,
‘提押’: 5,
‘还押’: 6
}

‘’’

    Common func

‘’’

判断 str 中是否包含 subStr, 包含返回 True，否则返回 False

def isSubStr(subStr, str):
if subStr in str:
return True
else:
return False

‘’’

    Read file func

‘’’

从ReadFile中读取

def getMonthValue():
UnFormatTimeData = getCellValue(0, 0)
return int(UnFormatTimeData.split(‘年’)[1].split(‘月’)[0], base=10)

从ReadFile中读取

def getDayValue():
UnFormatTimeData = getCellValue(0, 0)
return int(UnFormatTimeData.split(‘年’)[1].split(‘月’)[1].split(‘日’)[0], base=10)

获取 ReadFile 表格中指定单元格的数据

def getCellValue(row, col):
return Read_File_Data.iloc[row, col]

展示PrisonsElementList列表中的内容

def showPrisonsElementList():
for element in PrisonsElementList:
element.showPrisonsElementAttr()

读取文件然后往PrisonsElementList里面写内容

def getAllPrisonsElementList():
# 查看数据表最大有多少行多少列
# print(‘数据有%s行，%s列’%(RowLength,ColLength))
# 查找监所坐标位置
for i in range(Read_File_RowLength):
for j in range(Read_File_ColLength):
Cel_Data = getCellValue(i,j)
# print(‘第%s行，第%s列的数据是：%s’%(i,j,Cel_Data))
if Cel_Data == ‘监所’:
Read_File_PrisonsPositionRow = i
Read_File_PrisonsPositionCol = j
break;
# 测试是否拿到了’监所‘坐标
# print(‘找到了监所，数据位置在第%s行 %s列’%(PrisonsPositionRow,PrisonsPositionCol))
# 遍历有多少监所
# 起始位置坐标：
# StartRow = PrisonsPositionRow + 1 并且 StartRow 必须小于等于 RowLength
# StartCol = PrisonsPositionCol
# Row步长 Ste = 2

    StartRow = Read_File_PrisonsPositionRow + 1
    StartCol = Read_File_PrisonsPositionCol
    Step = 2

    while pd.notna(getCellValue(StartRow, StartCol)):              # 这里一直往下找 ，如果为空了就表示结束了，跳出循环
            PrisonsName = getCellValue(StartRow, StartCol)         # 监所名字，由于上一句不为空才进入循环，所以这里name
                                                                   # 一定不为空

            # 这里开始那第一条 entity 属性
            BackGet1 = getCellValue(StartRow, StartCol + 1)                  # 是'押' 还是 '还'
            DriverList1 = str(getCellValue(StartRow, StartCol + 4)).split("、")    # 这是一个驾驶员 List
            LeaderList1 = str(getCellValue(StartRow, StartCol + 5)).split("、")    # 这是一个负责人 List
            EscortList1 = str(getCellValue(StartRow, StartCol + 6)).split("、")    # 这是押解人员 List

            # 第一条 Entity
            PrisonsEntity1 = PrisonsEntity(BackGet1, DriverList1, LeaderList1, EscortList1)

            # 这里开始拿第二条 entity 属性
            BackGet2 = getCellValue(StartRow + 1, StartCol + 1)  # 是'押' 还是 '还'
            DriverList2 = str(getCellValue(StartRow + 1, StartCol + 4)).split("、")  # 这是一个驾驶员 List
            LeaderList2 = str(getCellValue(StartRow + 1, StartCol + 5)).split("、")  # 这是一个负责人 List
            EscortList2 = str(getCellValue(StartRow + 1, StartCol + 6)).split("、")  # 这是押解人员 List

            # 第二条 Entity
            PrisonsEntity2 = PrisonsEntity(BackGet2, DriverList2, LeaderList2, EscortList2)

            # 构建 PrisonsElement
            PrisonsElementEntity = PrisonsElement(PrisonsName, PrisonsEntity1, PrisonsEntity2)

            # 添加列表
            PrisonsElementList.append(PrisonsElementEntity)

            StartRow += Step
            if StartRow >= Read_File_RowLength:
                    break
    # 测试是否拿全了监所数据
    # print(PrisonsElementList)
    # showPrisonsElementList()

‘’’

    Write file func

‘’’

往WriteFile中指定row col单元格中写数据

def writeValue(sheetName, row, col, value):
Write_File_Data.iloc[row,col] = value
Write_File_Data.to_excel(Write_File_Path, sheet_name=sheetName, header=None, index=False)

从WriteFile中读取指定单元格数据

def getWriteFileCellValue(row, col):
return Write_File_Data.iloc[row, col]

返回WriteFile数据的其实和终止行数的下标，从0开始。如果要对应Excel表格中的行数要+1

def getWriteFileRowRange():
TargetMonth = getMonthValue()
TargetDay = getDayValue()

    # 获取写入文件的行宽长度
    Write_File_RowLength = Write_File_Data.shape[0]
    Write_File_ColLength = Write_File_Data.shape[1]

    MonthBeginIndex = -1
    DayBeginIndex = -1
    DayEndIndex = -1

    for row in range(Write_File_RowLength):
            if getWriteFileCellValue(row, 0) == getMonthValue():
                    MonthBeginIndex = row
                    break;
    for row in range(MonthBeginIndex, Write_File_RowLength):
            if getWriteFileCellValue(row, 1) == getDayValue():
                    DayBeginIndex = row
                    break;
    for row in range(DayBeginIndex,Write_File_RowLength):
            if getWriteFileCellValue(row, 1) != getDayValue():
                    DayEndIndex = row - 1
                    break;
            DayEndIndex = Write_File_RowLength - 1
    return DayBeginIndex, DayEndIndex

获得在对应人名在一个范围内的具体坐标 (如果有多个，返回第一个)

def getPersonPosition(BeginIndex, EndIndex, PersonName):
for row in range(BeginIndex,EndIndex + 1):
name = getWriteFileCellValue(row, TitleIndexDictionary[‘人员’]).replace(" “,”")
if name == PersonName:
return row
print(‘人名:%s’%(PersonName))
raise Exception(‘范围内未找到该人物.’)

解析单个 PrisonsEntity

def analysisPrisonsEntity(prisonsEntity):
# 将属性返回
return prisonsEntity.getBackOrGet(), prisonsEntity.getDriver(), prisonsEntity.getLeader(), prisonsEntity.getEscort()

处理DriverList

def writeDriverList(BeginRowIndex, EndRowIndex, BackOrGet, DriverList, WorkValue):
DriverIndex = -1;
if BackOrGet == ‘提’:
DriverIndex = 3
else:
DriverIndex = 4
for person in DriverList:
personRow = getPersonPosition(BeginRowIndex, EndRowIndex, person)
# 获得原来的值
originValue = getWriteFileCellValue(personRow, DriverIndex)
# 如果原值为空或者为0,，直接写 WorkValue
# 如果不为空，写 originValue + WorkValue
if pd.isna(originValue) or originValue == 0:
writeValue(‘每日’, personRow, DriverIndex, int(WorkValue))
else:
writeValue(‘每日’, personRow, DriverIndex,int(originValue) + int(WorkValue))

def writeEscortList(BeginRowIndex, EndRowIndex, BackOrGet, EscortList, WorkValue):
WriteCol = -1
if BackOrGet == ‘提’:
WriteCol = 5
else:
WriteCol = 6
for person in EscortList:
personRow = getPersonPosition(BeginRowIndex, EndRowIndex, person)
# 获得原来的值
originValue = getWriteFileCellValue(personRow, WriteCol)
# 如果原值为空或者为0,，直接写 WorkValue
# 如果不为空，写 originValue + WorkValue
if pd.isna(originValue) or originValue == 0:
writeValue(‘每日’, personRow, WriteCol, int(WorkValue))
else:
writeValue(‘每日’, personRow, WriteCol, int(originValue) + WorkValue)

解析单个 PrisonsElement 并且

入WriteFile中

def analysisPrisonsElement(prisonsElement):
# 分别获得 PrisonsEntity1 和 PrisonsEntity2
PrisonsEntity1 = prisonsElement.getPrisonsEntity1()
PrisonsEntity2 = prisonsElement.getPrisonsEntity2()

    # 获得监所名称，用来进一步获得 监所 工作量系数。
    PrisonsName = prisonsElement.getPrisonsName()

    # 根据 监所名称，获得工作量系数
    PrisonsWorkValue = 0
    for prisonName in PrisonsWorkValueDictionary:
            # 比叡 '闵行' 是否是 ‘闵行1男’ 子字符串
            if isSubStr(prisonName, PrisonsName):
                    PrisonsWorkValue = PrisonsWorkValueDictionary[prisonName]
    # 如果 PrisonsWorkValue = 0说明，当前的 监所名称 不包含在 监所工作量字典 中
    if PrisonsWorkValue == 0 :
            print('监所名称:%s'%(PrisonsName));
            raise Exception('当前的监所 在 "监所名称字典"中未找到!')

    # 分析 PrisonsEntity
    BackOrGet1, DriverList1, LeaderList1, EscortList1 = analysisPrisonsEntity(PrisonsEntity1)
    BackOrGet2, DriverList2, LeaderList2, EscortList2 = analysisPrisonsEntity(PrisonsEntity2)

    # 向 Write_File 表中写数据
    # 先获得写入Row的范围
    BeginRowIndex, EndRowIndex = getWriteFileRowRange()

    # 写 Driver 的工作量
    writeDriverList(BeginRowIndex, EndRowIndex, BackOrGet1, DriverList1, int(PrisonsWorkValue))
    writeDriverList(BeginRowIndex, EndRowIndex, BackOrGet2, DriverList2, int(PrisonsWorkValue))

    # 写 Escort 的工作量
    writeEscortList(BeginRowIndex, EndRowIndex, BackOrGet1, EscortList1, int(PrisonsWorkValue))
    writeEscortList(BeginRowIndex, EndRowIndex, BackOrGet2, EscortList2, int(PrisonsWorkValue))