python处理微信消息导入excel_python+openpyxl读取excel写入到另一个excel

[Python] 纯文本查看 复制代码#!/usr/bin/python

# -*- coding: UTF-8 -*-

import os

import datetime

import numpy as np

import pandas as pd

from openpyxl import Workbook

from openpyxl import load_workbook

from collections import Counter

# 获取当前时间

timeNow = datetime.datetime.now()

# 获取今天零点

timeZeroToday = timeNow - datetime.timedelta(hours=timeNow.hour, minutes=timeNow.minute, seconds=timeNow.second,

microseconds=timeNow.microsecond)

# 获取今天23:59:59

timeLastToday = timeZeroToday + datetime.timedelta(hours=23, minutes=59, seconds=59)

# 声明源数据路径

pathSourceData = "源数据"

# 声明新派、在途和归档3个文件列表

listFilesNewDistribution = []

listFilesOnTheWay = []

listFilesClosedLoop = []

listOverNightOnTheWay = []

fileAttendance = ""

# 获得源数据文件夹的全部文件名称，并对CSV文件进行分类

dirSourceData = os.listdir(pathSourceData)

for file in dirSourceData:

if os.path.splitext(file)[1] == ".csv":

if "新派" in file:

listFilesNewDistribution.append(file)

elif "在途" in file:

listFilesOnTheWay.append(file)

elif "归档" in file:

listFilesClosedLoop.append(file)

elif "家客工单导出" in file:

listOverNightOnTheWay.append(file)

elif os.path.splitext(file)[1] == ".xlsx":

if "排班" in file:

fileAttendance = file

# 区域网格字典

dictRegionsGrids = {'茂南': ['开发区', '茂北', '西城', '油城北', '油城东', '油城南'],

'电白': ['滨海', '林头', '麻岗', '南海', '沙琅', '水东'],

'高州': ['大井', '高城北', '高城南', '根子', '石鼓', '长坡'],

'化州': ['合江', '河东', '化北', '化河西', '同庆', '杨梅'],

'信宜': ['北界', '城北', '城南', '合水', '钱排', '朱砂']}

# 获得区域函数

def getRegion(x):

for iRegion in dictRegionsGrids:

if str(x.装维组用户班).find(iRegion) >= 0:

return iRegion

for iRegion in dictRegionsGrids:

if str(x.标准地址).find(iRegion) >= 0:

return iRegion

for iRegion in dictRegionsGrids:

if str(x.五级地址名称).find(iRegion) >= 0:

return iRegion

for iRegion in dictRegionsGrids:

if str(x.二级地址名称).find(iRegion) >= 0:

return iRegion

return "未签收"

# 获得区域函数(仅装维组)

def getRegionOnGroup(x):

for iRegion in dictRegionsGrids:

if str(x.所属装维组).find(iRegion) >= 0:

return iRegion

return "未知"

# 获得网格函数

def getGrid(x):

for iRegion in dictRegionsGrids:

if iRegion == str(x.区域):

for iGrid in dictRegionsGrids[iRegion]:

if str(x.装维组用户班).find(iGrid) >= 0:

return iGrid

return "未签收"

# 获得产品名称函数

listOriginalProductName = ['宽带', '手机宽带基础产品', '手机宽带', '宽带电视基础产品', '智能组网及增值收费服务基础产品', '智慧管家基础安装服务']

listFinalProductName = ['宽带', '宽带', '宽带', '电视', '组网', '安防']

def getProductName(x):

if x.操作类型 == '业务移机' or ():

return '移机'

else:

for iProductName in range(len(listOriginalProductName)):

if listOriginalProductName[iProductName] == x.产品名称:

if listFinalProductName[iProductName] == '宽带' and x.操作类型 == '预勘查' and x.产品业务属性 == '存量业务':

return '移机'

else:

return listFinalProductName[iProductName]

return "未知"

# =====隔夜在途工单=====

if len(listOverNightOnTheWay) < 1:

print("[错误] 未找到隔夜在途源数据(家客工单导出)")

else:

print("[操作] 输入隔夜在途数据")

count = 0

for i in listOverNightOnTheWay:

if count == 0:

sheetOverNightOnTheWay = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk', usecols=['工单号', '标准地址'])

count += 1

else:

sheetTemp = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk', usecols=['工单号', '标准地址'])

sheetOverNightOnTheWay = pd.concat([sheetOverNightOnTheWay, sheetTemp])

# 重置索引(index)

sheetOverNightOnTheWay = sheetOverNightOnTheWay.reset_index(drop=True)

# =====人员出勤情况=====

print('[操作] 输入人员出勤数据')

# 输入Excel文件

xlsxAttendance = load_workbook(pathSourceData + "\\" + fileAttendance, read_only=False)

sheetAttendance = xlsxAttendance.active

# 转换为DataFrame

dataFrameAttendance = pd.DataFrame(sheetAttendance.values)

# 设置字段名

dataFrameAttendance.columns = dataFrameAttendance.iloc[0]

dataFrameAttendance = dataFrameAttendance.drop(0)

# 新增[区域]列，并根据getRegionOnGroup函数规则获得区域

dataFrameAttendance['区域'] = dataFrameAttendance.apply(lambda x: getRegionOnGroup(x), axis=1)

# 清洗上班人员

dataFrameAttendance = dataFrameAttendance.drop(

dataFrameAttendance[dataFrameAttendance[datetime.datetime.now().strftime('%Y-%m-%d')] == '上班'].index)

# =====新派=====

# 如果有新派数据，就先输入第一个，再循环输入剩余的并合并

if len(listFilesNewDistribution) < 1:

print("[错误] 未找到新派源数据")

else:

print("[操作] 输入新派数据")

count = 0

for i in listFilesNewDistribution:

if count == 0:

sheetNewDistribution = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk')

count += 1

else:

sheetTemp = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk')

sheetNewDistribution = pd.concat([sheetNewDistribution, sheetTemp])

print("[操作] 处理新派数据")

# 重置索引(index)

sheetNewDistribution = sheetNewDistribution.reset_index(drop=True)

# 格式化日期

sheetNewDistribution['派单日期'] = pd.to_datetime(sheetNewDistribution['派单日期'])

sheetNewDistribution['勘察单到单时间'] = pd.to_datetime(sheetNewDistribution['勘察单到单时间'])

# 账号转换为字符串

sheetNewDistribution['宽带帐号'] = sheetNewDistribution['宽带帐号'].apply(str)

# 清洗不需要的行

# 产品名称=家客试开通

sheetNewDistribution = sheetNewDistribution.drop(

sheetNewDistribution[sheetNewDistribution['产品名称'] == '家客试开通'].index)

# 备注包含测试且派单日期在2020年前

sheetNewDistribution = sheetNewDistribution.drop(

sheetNewDistribution[

(sheetNewDistribution['勘察单到单时间'].notnull()) & (sheetNewDistribution['操作类型'] == '业务开通')].index)

# 备注包含测试且派单日期在2020年前

sheetNewDistribution = sheetNewDistribution.drop(

sheetNewDistribution[

(sheetNewDistribution['备注'].str.contains('测试')) & (sheetNewDistribution['派单日期'].dt.year < 2020)].index)

# 资源类型等于自建宽带、自建铁通融合宽带,或包含三个指定地址的，保留，其他清洗

sheetNewDistribution['是否纳入统计'] = sheetNewDistribution.apply(

lambda x: "是" if x.资源类型 == '自建宽带' or x.资源类型 == '自建铁通融合宽带' or str(x.五级地址名称).find('海心路茂名职业技术学院') >= 0 or str(

x.五级地址名称).find('海城路五路1号茂名职业技术学院') >= 0 or str(x.五级地址名称).find('官渡街道文明路茂名职业技术') >= 0 else "否", axis=1)

# 开始清洗资源类型

sheetNewDistribution = sheetNewDistribution.drop(sheetNewDistribution[sheetNewDistribution['是否纳入统计'] == '否'].index)

sheetNewDistribution = sheetNewDistribution.drop(['是否纳入统计'], axis=1)

# 修改不合法的字段名

sheetNewDistribution = sheetNewDistribution.rename(columns={'装维组/用户班': '装维组用户班'})

sheetNewDistribution = sheetNewDistribution.rename(columns={'装维人员/用户班人员': '装维人员用户班人员'})

# 新增标准地址列，并匹配标准地址

sheetNewDistribution = pd.merge(sheetNewDistribution, sheetOverNightOnTheWay, on=['工单号', '工单号'], how='left')

# 新增[区域]列，并根据getRegion函数规则获得区域

sheetNewDistribution['区域'] = sheetNewDistribution.apply(lambda x: getRegion(x), axis=1)

# 新增[网格]列，并根据getGrid函数规则获得网格

sheetNewDistribution['网格'] = sheetNewDistribution.apply(lambda x: getGrid(x), axis=1)

# 新增[装维人员]列，获得装维人员

sheetNewDistribution['装维人员'] = sheetNewDistribution['装维人员用户班人员'].apply(lambda x: '未签收' if pd.isnull(x) else x)

# 新增[产品名称分类]列，并根据getProductName函数规则获得产品名称分类

sheetNewDistribution['产品名称分类'] = sheetNewDistribution.apply(lambda x: getProductName(x), axis=1)

# 新增[是否集约环节]列，获得判断工单是否在集约环节

sheetNewDistribution['是否集约环节'] = sheetNewDistribution['当前环节'].apply(lambda x: "集约环节" if x == "集中预约" else "非集约环节")

# 新增[实际到单时间]列，获得实际到单时间

sheetNewDistribution['实际到单时间'] = sheetNewDistribution.apply(lambda x: x.派单日期 if pd.isnull(x.勘察单到单时间) else x.勘察单到单时间,

axis=1)

# 宽带存量剔除

sheetNewDistribution = sheetNewDistribution.drop(

sheetNewDistribution[

(sheetNewDistribution['产品名称分类'] == '宽带') & (sheetNewDistribution['产品业务属性'] == '存量业务')].index)

# 重置索引(index)

sheetNewDistribution = sheetNewDistribution.reset_index(drop=True)

# =====在途=====

# 如果有在途数据，就先输入第一个，再循环输入剩余的并合并

if len(listFilesOnTheWay) < 1:

print("[错误] 未找到在途源数据")

else:

print("[操作] 输入在途数据")

count = 0

for i in listFilesOnTheWay:

if count == 0:

sheetOnTheWay = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk')

count += 1

else:

sheetTemp = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk')

sheetOnTheWay = pd.concat([sheetOnTheWay, sheetTemp])

print("[操作] 处理在途数据")

# 重置索引(index)

sheetOnTheWay = sheetOnTheWay.reset_index(drop=True)

# 格式化日期

sheetOnTheWay['派单日期'] = pd.to_datetime(sheetOnTheWay['派单日期'])

sheetOnTheWay['勘察单到单时间'] = pd.to_datetime(sheetOnTheWay['勘察单到单时间'])

# 账号转换为字符串

sheetOnTheWay['宽带帐号'] = sheetOnTheWay['宽带帐号'].apply(str)

# 清洗不需要的行

# 产品名称=家客试开通

sheetOnTheWay = sheetOnTheWay.drop(sheetOnTheWay[sheetOnTheWay['产品名称'] == '家客试开通'].index)

# 备注包含测试且派单日期在2020年前

sheetOnTheWay = sheetOnTheWay.drop(

sheetOnTheWay[(sheetOnTheWay['备注'].str.contains('测试')) & (sheetOnTheWay['派单日期'].dt.year < 2020)].index)

# 资源类型等于自建宽带、自建铁通融合宽带、产品名称为组网,或包含三个指定地址的，保留，其他清洗

sheetOnTheWay['是否纳入统计'] = sheetOnTheWay.apply(

lambda x: "是" if x.资源类型 == '自建宽带' or x.资源类型 == '自建铁通融合宽带' or str(x.五级地址名称).find('海心路茂名职业技术学院') >= 0 or str(

x.五级地址名称).find('海城路五路1号茂名职业技术学院') >= 0 or str(x.五级地址名称).find(

'官渡街道文明路茂名职业技术') >= 0 or x.产品名称 == '智能组网及增值收费服务基础产品' else "否", axis=1)

# 开始清洗资源类型

sheetOnTheWay = sheetOnTheWay.drop(sheetOnTheWay[sheetOnTheWay['是否纳入统计'] == '否'].index)

sheetOnTheWay = sheetOnTheWay.drop(['是否纳入统计'], axis=1)

# 修改不合法的字段名

sheetOnTheWay = sheetOnTheWay.rename(columns={'装维组/用户班': '装维组用户班'})

sheetOnTheWay = sheetOnTheWay.rename(columns={'装维人员/用户班人员': '装维人员用户班人员'})

# 新增标准地址列，并匹配标准地址

sheetOnTheWay = pd.merge(sheetOnTheWay, sheetOverNightOnTheWay, on=['工单号', '工单号'], how='left')

# 新增[区域]列，并根据getRegion函数规则获得区域

sheetOnTheWay['区域'] = sheetOnTheWay.apply(lambda x: getRegion(x), axis=1)

# 新增[网格]列，并根据getGrid函数规则获得网格

sheetOnTheWay['网格'] = sheetOnTheWay.apply(lambda x: getGrid(x), axis=1)

# 新增[装维人员]列，获得装维人员

sheetOnTheWay['装维人员'] = sheetOnTheWay['装维人员用户班人员'].apply(lambda x: '未签收' if pd.isnull(x) else x)

# 新增[产品名称分类]列，并根据getProductName函数规则获得产品名称分类

sheetOnTheWay['产品名称分类'] = sheetOnTheWay.apply(lambda x: getProductName(x), axis=1)

# 新增[是否集约环节]列，获得判断工单是否在集约环节

sheetOnTheWay['是否集约环节'] = sheetOnTheWay['当前环节'].apply(lambda x: "集约环节" if x == "集中预约" else "非集约环节")

# 新增[实际到单时间]列，获得实际到单时间

sheetOnTheWay['实际到单时间'] = sheetOnTheWay.apply(lambda x: x.派单日期 if pd.isnull(x.勘察单到单时间) else x.勘察单到单时间, axis=1)

# 新增[自然时间]列，获得当前自然时间

sheetOnTheWay['自然时间'] = timeNow

# 新增[跨日时间]列，获得当前跨日时间

sheetOnTheWay['跨日时间'] = timeLastToday

# 新增[工单自然时长]列，计算工单自然时长

sheetOnTheWay['工单自然时长'] = sheetOnTheWay['实际到单时间'].apply(lambda x: int((timeNow - x).total_seconds()) / 3600)

# 新增[工单跨日时长]列，计算工单跨日时长

sheetOnTheWay['工单跨日时长'] = sheetOnTheWay['实际到单时间'].apply(lambda x: int((timeLastToday - x).total_seconds()) / 3600)

# 新增[是否超7天]列，计算是否超7天(大于168小时)

sheetOnTheWay['是否超7天'] = sheetOnTheWay['工单跨日时长'].apply(lambda x: "是" if x > 168 else "否")

# 存量剔除

sheetOnTheWay = sheetOnTheWay.drop(

sheetOnTheWay[((sheetOnTheWay['产品名称分类'] == '宽带') | (sheetOnTheWay['产品名称分类'] == '电视')) & (

sheetOnTheWay['产品业务属性'] == '存量业务')].index)

# 新增[是否同装]列，根据账号是否重复，判断是否为同装业务

sheetTemp = sheetOnTheWay[sheetOnTheWay.产品名称分类 == '宽带']

listTemp = sheetTemp['宽带帐号'].tolist()

sheetOnTheWay['是否与宽带同装'] = sheetOnTheWay.apply(lambda x: '是' if x.宽带帐号 in listTemp and x.产品名称分类 != '宽带' else '否',

axis=1)

# 重置索引(index)

sheetOnTheWay = sheetOnTheWay.reset_index(drop=True)

# =====归档=====

# 如果有归档数据，就先输入第一个，再循环输入剩余的并合并

if len(listFilesClosedLoop) < 1:

print("[错误] 未找到归档源数据")

else:

print("[操作] 输入归档数据")

count = 0

for i in listFilesClosedLoop:

if count == 0:

sheetClosedLoop = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk')

count += 1

else:

sheetTemp = pd.read_csv(pathSourceData + "\\" + i, encoding='gbk')

sheetClosedLoop = pd.concat([sheetClosedLoop, sheetTemp])

print("[操作] 处理归档数据")

# 重置索引(index)

sheetClosedLoop = sheetClosedLoop.reset_index(drop=True)

# 格式化日期

sheetClosedLoop['派单日期'] = pd.to_datetime(sheetClosedLoop['派单日期'])

sheetClosedLoop['勘察单到单时间'] = pd.to_datetime(sheetClosedLoop['勘察单到单时间'])

sheetClosedLoop['归档时间'] = pd.to_datetime(sheetClosedLoop['归档时间'])

# 清洗不需要的行

# 产品名称=家客试开通

sheetClosedLoop = sheetClosedLoop.drop(sheetClosedLoop[sheetClosedLoop['产品名称'] == '家客试开通'].index)

# 备注包含测试且派单日期在2020年前

sheetClosedLoop = sheetClosedLoop.drop(

sheetClosedLoop[(sheetClosedLoop['备注'].str.contains('测试')) & (sheetClosedLoop['派单日期'].dt.year < 2020)].index)

# 资源类型等于自建宽带、自建铁通融合宽带、产品名称为组网,或包含三个指定地址的，保留，其他清洗

sheetClosedLoop['是否纳入统计'] = sheetClosedLoop.apply(

lambda x: "是" if x.资源类型 == '自建宽带' or x.资源类型 == '自建铁通融合宽带' or str(x.五级地址名称).find('海心路茂名职业技术学院') >= 0 or str(

x.五级地址名称).find('海城路五路1号茂名职业技术学院') >= 0 or str(x.五级地址名称).find(

'官渡街道文明路茂名职业技术') >= 0 or x.产品名称 == '智能组网及增值收费服务基础产品' else "否", axis=1)

# 开始清洗资源类型

sheetClosedLoop = sheetClosedLoop.drop(sheetClosedLoop[sheetClosedLoop['是否纳入统计'] == '否'].index)

sheetClosedLoop = sheetClosedLoop.drop(['是否纳入统计'], axis=1)

# 修改不合法的字段名

sheetClosedLoop = sheetClosedLoop.rename(columns={'装维组/用户班': '装维组用户班'})

sheetClosedLoop = sheetClosedLoop.rename(columns={'装维人员/用户班人员': '装维人员用户班人员'})

# 新增标准地址列，并匹配标准地址

sheetClosedLoop = pd.merge(sheetClosedLoop, sheetOverNightOnTheWay, on=['工单号', '工单号'], how='left')

# 新增[区域]列，并根据getRegion函数规则获得区域

sheetClosedLoop['区域'] = sheetClosedLoop.apply(lambda x: getRegion(x), axis=1)

# 新增[网格]列，并根据getGrid函数规则获得网格

sheetClosedLoop['网格'] = sheetClosedLoop.apply(lambda x: getGrid(x), axis=1)

# 新增[装维人员]列，获得装维人员

sheetClosedLoop['装维人员'] = sheetClosedLoop['装维人员用户班人员'].apply(lambda x: '未签收' if pd.isnull(x) else x)

# 新增[产品名称分类]列，并根据getProductName函数规则获得产品名称分类

sheetClosedLoop['产品名称分类'] = sheetClosedLoop.apply(lambda x: getProductName(x), axis=1)

# 新增[归档时段]列，根据归档时间获得工单归档时段

sheetClosedLoop['归档时段'] = sheetClosedLoop['归档时间'].apply(lambda x: x.hour)

# 宽带存量剔除

sheetClosedLoop = sheetClosedLoop.drop(

sheetClosedLoop[(sheetClosedLoop['产品名称分类'] == '宽带') & (sheetClosedLoop['产品业务属性'] == '存量业务')].index)

# 新增[是否同装]列，根据账号是否重复，判断是否为同装业务

sheetTemp = sheetClosedLoop[sheetClosedLoop.产品名称分类 == '宽带']

listTemp = sheetTemp['宽带帐号'].tolist()

sheetClosedLoop['是否与宽带同装'] = sheetClosedLoop.apply(

lambda x: '是' if x.宽带帐号 in listTemp and x.产品名称分类 != '宽带' else '否',

axis=1)

# 重置索引(index)

sheetClosedLoop = sheetClosedLoop.reset_index(drop=True)

# =====通报模板=====

print("[操作] 输入通报模板")

xlsxNotification = load_workbook(filename=pathSourceData + "\\" + '通报模板.xlsx')

sheetNotification = xlsxNotification['sheet']

# 输出Excel文件，判断表是否存在，如果存在则写入Excel。

print("[操作] 输出EXCEL表")

with pd.ExcelWriter('日通报源数据' + datetime.datetime.now().strftime('%Y年%m月%d日%H时') + '.xlsx') as writer:

# 写入通报sheet

sheetNotification.to_excel(writer, sheet_name='通报', index=False)

#再写入源数据数据

if "sheetNewDistribution" in locals().keys():

sheetNewDistribution.to_excel(writer, sheet_name='新派', index=False)

if "sheetOnTheWay" in locals().keys():

sheetOnTheWay.to_excel(writer, sheet_name='在途', index=False)

if "sheetClosedLoop" in locals().keys():

sheetClosedLoop.to_excel(writer, sheet_name='归档', index=False)

if "dataFrameAttendance" in locals().keys():

dataFrameAttendance.to_excel(writer, sheet_name='休假', index=False, columns=['区域', '所属装维组', '装维姓名', '装维账号',

datetime.datetime.now().strftime(

'%Y-%m-%d')])