公司部分业务仍采用在线文档的方式记录生产数据，为了实现在线文档-金山文档/群辉空间文档自动上传数据库制作数据面板，开发python自动化脚本。
# -*- codeing = utf-8 -*-
# @Time : 2023-10-21 14:13
# @Author : L
# @File : 群辉文档通用函数.py
# @Software : PyCharm
import time
import os
import mysql.connector
from selenium import webdriver
from selenium.webdriver.common.by import By
import smtplib
from email.header import Header
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from apscheduler.schedulers.blocking import BlockingScheduler
from selenium.webdriver.common.action_chains import ActionChains
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
import mysql.connector
import numpy as np
import pandas as pd
import win32com.client as win32
import pythoncom
import pyautogui
from send2trash import send2trash
import xlwings as xw
''''--------------------------------------------自动控制功能----------------------------------------------'''
#下载群辉文档，file_name填对应的'文件名.xlsx'
def login_sy(username,password,url,file_name):
driver = webdriver.Chrome()
driver.set_window_position(-1200, 0)
driver.maximize_window()
driver.get(url=url)
wait = WebDriverWait(driver, 100) #设置元素出现的最长等待时间100秒
wait.until(EC.presence_of_element_located((By.ID, 'ext-gen53'))).click()
wait.until(EC.presence_of_element_located((By.NAME, 'username'))).send_keys(username)
wait.until(EC.element_to_be_clickable((By.CSS_SELECTOR, '.login-btn'))).click()
wait.until(EC.presence_of_element_located((By.NAME, 'current-password'))).send_keys(password)
checkbox = driver.find_element(by=By.ID, value='32')
if checkbox.is_selected():
checkbox.click()
else:
driver.find_element(by=By.CSS_SELECTOR, value='.login-btn').click()
# 等待页面加载完成
wait.until(EC.visibility_of_element_located((By.XPATH, '//*[@id="ext-comp-1620"]/div[2]/span')))
wait.until(EC.presence_of_element_located((By.ID, 'ext-gen72'))).click()
ActionChains(driver).move_to_element(wait.until(EC.presence_of_element_located((By.ID, 'ext-comp-1093')))).perform()
wait.until(EC.presence_of_element_located((By.XPATH, "//div[@id='ext-comp-1072']//span[contains(text(), 'Microsoft Excel')]"))).click() #等待下载响应，最少要两分钟
#等待下载
download_path = r"C:\Users\huangweiling\Downloads"
while not os.path.exists(os.path.join(download_path, file_name)):
time.sleep(1)
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())), "下载完成")
driver.quit()
#file_name示例：9月开发进度表.xlsx
#在本地打开金山文档
def login_wps(url,wait_time,login=True):
driver = webdriver.Chrome()
driver.get(url=url)
driver.set_window_position(-1200, 0)
driver.maximize_window()
wait = WebDriverWait(driver, 100) # 设置最大等待时间为100秒
if login:
wait.until(EC.element_to_be_clickable((By.LINK_TEXT, '立即登录'))).click()
wait.until(EC.element_to_be_clickable((By.ID, 'pcAvatar'))).click()
time.sleep(3)
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="component-header-left"]/div[3]'))).click()
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="util-popup"]/div/div/div/div[4]/div[12]/div/span'))).click()
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="kma-popper__container"]/div[3]/div/div[3]/div/button[1]'))).click()
# wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="kma-popper__container"]/div[4]/div/div[2]/div/div/div/button[1]'))).click()
else:
time.sleep(3)
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="component-header-left"]/div[2]'))).click()
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="util-popup"]/div/div/div/div[4]/div[11]/div/span'))).click()
wait.until(EC.element_to_be_clickable((By.ID, 'pcAvatar'))).click()
time.sleep(3)
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="component-header-left"]/div[3]'))).click()
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="util-popup"]/div/div/div/div[4]/div[12]/div/span'))).click()
# wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="kma-popper__container"]/div[4]/div/div[2]/div/div/div[2]/div/div[2]/div[2]'))).click()
wait.until(EC.element_to_be_clickable((By.XPATH, '//*[@id="kma-popper__container"]/div[3]/div/div[3]/div/button[1]'))).click()
time.sleep(wait_time)
driver.quit()
#将已打开的金山文档读取为df
def load_wps(sheet_name):
screen_width, screen_height = pyautogui.size()
pyautogui.FAILSAFE = False
pyautogui.click(screen_width / 2, screen_height / 2)
time.sleep(2)
excel = win32.gencache.EnsureDispatch('Excel.Application') # 获取Excel应用程序
workbook = excel.ActiveWorkbook
filename = workbook.Name # 获取当前活动工作表表名
filedir = workbook.Path
filepath = filedir + '\\' + filename
df = pd.DataFrame(pd.read_excel(filepath, sheet_name=sheet_name, na_values=['#N/A', '#DIV/0!', '#VALUE']))
return df
#关闭文件
def close_wps():
win32.gencache.EnsureDispatch('Excel.Application').DisplayAlerts = False # 关闭文件时不保存
win32.gencache.EnsureDispatch('Excel.Application').ActiveWorkbook.Close(SaveChanges=False) # 关闭文件时不保存
win32.gencache.EnsureDispatch('Excel.Application').Quit()
''' # 关闭WPS软件
os.system('taskkill /f /im et.exe') # 关闭WPS表格进程
os.system('taskkill /f /im wps.exe') # 关闭WPS文字进程
os.system('taskkill /f /im wpp.exe') # 关闭WPS演示进程
'''
#连接数据库
def connect_dba():
mydb = mysql.connector.connect(
host='XXXXXXXXXXXXXXXXXXXX',
port=3306,
user='XXXXXXXXXXXX',
passwd='XXXXXXXXXXXX',
db='XXXXXXXXXXXXX',
charset="utf8",
)
return mydb
#需要在使用文件中，mydb=synology_function.connect_dba(),然后pythoncom.CoInitialize()，最后mydb.close()
#删除文件
def delete_file(filepath,file_name):
if os.path.isfile(filepath+'\\'+file_name):
#send2trash(filepath) #放入回收站
os.remove(filepath+'\\'+file_name) #直接删除
print(f'文件 {file_name} 已经删除')
else:
print(f'文件 {file_name} 不存在')
#发送检测邮件
def send_email(title):
mail_sender = "XXXXXXXX@qq.com" # 发送者邮箱名
mail_license = 'XXXXXXXXXXX' # 发送者邮箱授权码，即开启POP3/SMTP服务获取的token
mail_host = "smtp.qq.com" # SMTP服务器,这里为qq邮箱，若为163邮箱请用163替换qq
mail_receivers = ["XXXXXXX@qq.com"] # 收件人邮箱
mail = MIMEMultipart('related') # 设置邮件主体
mail["From"] = "business_computer<XXXXXXXX@qq.com>" # 设置发送者邮箱
mail["To"] = "mailbox<XXXXXXXX@qq.com>" # 设置接受者邮箱
subject_content = title # 设置邮件主题
mail["Subject"] = Header(subject_content, 'utf-8') # 添加邮件主题
body_content = """今天--运行成功！ """
message_text = MIMEText(body_content, "plain", "utf-8") # 设置正文内容、文本格式、编码方式
mail.attach(message_text) # 向MIMEMultipart对象中添加文本对象
smtp = smtplib.SMTP() # 创建SMTP对象
smtp.connect(mail_host, 25) # 设置发件人邮箱的域名和端口，端口地址为25
smtp.set_debuglevel(0) # 打印和SMTP服务器交互的所有信息
smtp.login(mail_sender, mail_license) # 根据邮箱地址和邮箱收起码登录邮箱
smtp.sendmail(mail_sender, mail_receivers, mail.as_string()) # 发送邮件，并设置邮件内容格式为str
smtp.quit()
def my_job():
print('写入任务清单')
#需要在使用的文件里面重写再调用定时任务，重写用 synology_function.my_job=my_job
def auto_task(update_times=1,hour=None,mintue=None,second=None):
sched = BlockingScheduler()
if update_times==1:
sched.add_job(my_job, 'cron', day_of_week='mon-sun', hour=hour, minute=mintue, second=second,misfire_grace_time=3600)
elif update_times==3:
sched.add_job(my_job, 'cron', day_of_week='mon-sun', hour='8,12,22', minute='30', second=0, misfire_grace_time=3600)
elif update_times==48:
sched.add_job(my_job, 'cron', day_of_week='mon-sun', hour='*', minute='0,30', second=0, misfire_grace_time=3600)
elif update_times == 24:
sched.add_job(my_job, 'cron', day_of_week='mon-sun', hour='*', minute='0', second=0, misfire_grace_time=3600)
sched.start()
sched.shutdown(wait=False)
#输出excel表头
def print_excel_header(filepath,excel_name,sheet_name,excel_skiprows=0):
df=pd.read_excel(filepath+'\\'+excel_name,sheet_name,skiprows=excel_skiprows)
print('excel表头：','\n',df.columns,'得加上"统计日期"')
return df
#输出sql表字段
def print_sql_header(sql_table_name):
mydb = connect_dba()
cursor = mydb.cursor()
# 查询字段名和字段注释
query = f"SELECT COLUMN_NAME, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '{mydb.database}' AND TABLE_NAME = '{sql_table_name}';"
# 执行查询
cursor.execute(query)
# 获取查询结果
results = cursor.fetchall()
columns = [f"'{column[0]}'" for column in results]
print('sql表字段：')
print(', '.join(columns), '\n')
print('sql表字段注释')
# 输出字段名和字段注释
for column in results:
column_name = column[0]
column_comment = column[1]
print(f"{column_name:<35}\t{column_comment}")
# 关闭游标和数据库连接
cursor.close()
mydb.close()
'''------------------------------------df处理功能----------------------------------------'''
#返回当前日期（字符串）
def clock():
return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
#读取excel数据
def load_excel(filepath,sheet_name=0,excel_skiprows=0):
df = pd.DataFrame(pd.read_excel(filepath, sheet_name=sheet_name, skiprows=excel_skiprows, engine='openpyxl',na_values=['#N/A', '#DIV/0!', '#VALUE'],keep_default_na=False))
return df
#剔除符合条件值的行记录
def fliter_data(df_name,filter_column_name,filier_condition):
df_name = df_name[df_name[filter_column_name] != filier_condition]
return df_name
#替换df字段名中的换行符
def clean_newline(df_name):
df_name.columns = [col.replace('\n', '') for col in df_name.columns]
return df_name
#清除字段前后空格
def clean_space(df_name,remove_space_column_list):
df_name[remove_space_column_list] = df_name[remove_space_column_list].applymap(lambda x: str(x).strip())
return df_name
#添加统计日期
def add_create_date_time(df_name,yyyymmddhhssmm=None):
if yyyymmddhhssmm:
df_name.insert(loc=2, column='统计日期',value=time.strftime('%Y-%m-%d %H:%M:%S', time.strptime(yyyymmddhhssmm, '%Y-%m-%d %H:%M:%S')))
else:
df_name.insert(loc=2, column='统计日期', value=time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
return df_name
#日期字段转为datetime类型
def convert_date_columns_to_datetime(df_name):
columns = df_name.columns # 获取所有列名
date_columns = [col for col in columns if '日期' in col or '时间' in col] # 需要转换为Datetime类型的列名列表
for column in date_columns:
df_name[column] = pd.to_datetime(df_name[column],format='%Y-%m-%d %H:%M:%S', errors='coerce') #errors='coerce'参数表示如果日期格式不正确，则会将其转换为空值
return df_name
#统一项目名称
def apply_conditions(df_name, unify_name_column_name):
conditions = [df_name[unify_name_column_name].str.contains('穗城', na=False),
df_name[unify_name_column_name].str.contains('云裳', na=False),
df_name[unify_name_column_name].str.contains('焕新', na=False),
df_name[unify_name_column_name].str.contains('美学', na=False),
df_name[unify_name_column_name].str.contains('悦己', na=False),
df_name[unify_name_column_name].str.contains('设计', na=False),
df_name[unify_name_column_name].str.contains('衣品', na=False),
df_name[unify_name_column_name].str.contains('大师', na=False),
df_name[unify_name_column_name].str.contains('衣语', na=False),
df_name[unify_name_column_name].str.contains('霓裳', na=False),
df_name[unify_name_column_name].str.contains('传承', na=False),
df_name[unify_name_column_name].str.contains('优选', na=False),
df_name[unify_name_column_name].str.contains('优选', na=False)]
values = ['面料美学', '面料悦己', '面料设计', '面料美学', '面料悦己', '面料设计', '时尚衣品', '时尚大师', '素订衣语', '素订霓裳', '素订传承', '蚕素优选','素订微衣']
df_name[unify_name_column_name] = np.select(conditions, values, default=np.nan)
return df_name
#调用完要存储为df_name
# 将空值替换为None
def replace_empty_with_none(df_name):
df_name.replace('', np.nan, inplace=True) # 将空字符串替换为NaN
df_name.replace({np.nan: None}, inplace=True) # 将NaN替换为None
return df_name
def development_progress(df_name,unify_name_column_name):
conditions = [df_name['项目'].str.contains('时尚', na=False),
df_name['项目'].str.contains('素订', na=False),
df_name['项目'].str.contains('蚕素', na=False),
df_name['项目'].str.contains('面料', na=False)]
values = ['时尚大师', '素订', '蚕素', '面料故事']
df_name['是否上架'] = df_name.apply(lambda row: np.select(conditions, values, default=np.nan)+'前置' if row['上架时间'] == '前置开发' else '已上架款', axis=1)
#上传df到数据库
def insert_dataframe_to_mysql(df_name,excel_header_list,sql_table_name, sql_header_list,delete_data_sql=None,truncate_table=False):
# 创建MySQL连接
mydb = connect_dba()
pythoncom.CoInitialize()
cursor = mydb.cursor()
batch_size = len(df_name) # 批量插入的记录数
if delete_data_sql:
sql=delete_data_sql
cursor.execute(sql)
deleted_rows = cursor.rowcount # 最近一次执行的SQL语句受影响的行数
mydb.commit()
print(f"删除 {deleted_rows} 行")
elif truncate_table:
sql = f"truncate table `{sql_table_name}`"
cursor.execute(sql)
mydb.commit()
else:
print('插入前不需要删除数据')
# 循环插入数据
for i in range(0, len(df_name) + 1, batch_size):
sub_df = df_name.loc[i:i + batch_size, excel_header_list]
data = [tuple(x) for x in sub_df.values]
# 构建SQL语句
placeholders = ', '.join(['%s'] * len(sql_header_list))
columns = ', '.join(sql_header_list)
sql = f"INSERT INTO `{sql_table_name}` ({columns}) VALUES ({placeholders})"
cursor.executemany(sql, data)
insert_rows = cursor.rowcount
mydb.commit()
print(f"插入 {insert_rows} 行")
# 关闭数据库连接
cursor.close()
mydb.close()
'''------------------------------------更新数据模板----------------------------------------'''
'''
def create_sql_table(filepath,excel_name,sheet_name,column_list,sql_table_name,excel_skiprows=0):
df = pd.DataFrame(pd.read_excel(filepath+'\\'+excel_name,sheet_name,skiprows=excel_skiprows))
df= df[column_list]
mydb=connect_dba()
cursor = mydb.cursor()
'''
def print_syoffice_note(filepath,excel_name,sheet_name,sql_table_name,excle_skiprows=0):
print('''
参数设置格式示例：
[1、群晖登录下载文件]
username：
password:
url:
filepath:'C:\\Users\\L\\Downloads'
file_name：9月产品开发需求进度表（开发板块总表）.xlsx
[2、读取文件]
excle_skiprows:0 #excel跳过的行数
sheet_name: 产品开发进度表
[3、df处理]
df_name: df_FM #df最终命名，可缺省，命名是为了多个df时能区分开来
filter_column_name:'样衣状态' #剔除行记录的字段名和条件值，填None或不填表示不需要剔除
filier_condition:'已取消'
remove_space_column_list：['项目','商品编码'] #需清除汇总维度字段的前后空格的列
colunm_name:'项目' #统一项目名称的字段名
yyyymmddhhssmm:'2023-10-25 23:30:00' #添加统计时间字段，填None或不填表示取现在的时间
additional_operation:custom_additional_operation #custom_additional_operation是函数名，函数内部封装了df自定义操作，填None表示不需要额外操作
[4、上传数据库]
excel_header_list:['项目', '商品编码',...'统计日期'] #要加list里面加一个统计日期字段
sql_table_name:development_progress
sql_header_list:['team','product_alias',...'data_create_time'] #要加list里面加一个date_create_time字段
delete_data_sql:"delete from development_progress
where date(data_create_time)=date(now())
and date(data_create_time) not in
('2023-1-31','2023-2-28','2023-3-31',
'2023-4-30','2023-5-31','2023-6-30',
'2023-7-31','2023-8-31','2023-9-30',
'2023-10-31','2023-11-30','2023-12-31')" #插入数据前需要删除表记录的sql语句
truncate_table:True #默认False,True则在插入数据前清空表
[5、发送邮件]
send_message：False #默认True,False则不发送
''')
print('需要确定的参数有：filepath,file_name,sheet_name,remove_space_column_list,unify_name_column_name,excel_header_list, sql_table_name, sql_header_list,delete_data_sql(非必须),truncate_table(非必须),yyyymmddhhssmm(非必须),filter_column_name(非必须),filier_condition(非必须),additional_operation=None(非必须),excel_skiprows(非必须),username(不填表示在本地), password(不填表示在本地), url(不填表示在本地),send_message(默认True)')
print('-------------------------------------------------------------------------------------------------------------')
print_excel_header(filepath,excel_name,sheet_name,excel_skiprows=excle_skiprows)
print_sql_header(sql_table_name)
def syoffice_to_sql(filepath,file_name,sheet_name,remove_space_column_list,excel_header_list, sql_table_name, sql_header_list,unify_name_column_name=None,delete_data_sql=None,truncate_table=False,yyyymmddhhssmm=None,filter_column_name=None,filier_condition=None,additional_operation=None,excel_skiprows=0,username=None, password=None, url=None,if_delete=None,send_message=True):
print(clock(),'%s开始更新'%file_name)
if username and password and url: #下载群辉文件到本地
login_sy(username, password, url, file_name)
else:
print('无需下载')
#df读取文件
df=pd.read_excel(filepath+'\\'+file_name,sheet_name,skiprows=excel_skiprows, engine='openpyxl',keep_default_na=False,na_values=None) #df读取文件
print('读取成功')
if filter_column_name and filier_condition: #剔除符合条件的行记录
df=fliter_data(df,filter_column_name,filier_condition)
elif filter_column_name or filier_condition:
print('错误,filter_column_name或condition漏填了')
else:
print('没有要剔除的行记录')
df=clean_newline(df) #清除表头中的换行符
print('清除表头中的换行符成功')
df=clean_space(df,remove_space_column_list) #清除汇总维度字段的前后空格
print('清除汇总维度字段的前后空格成功')
df=add_create_date_time(df,yyyymmddhhssmm) #添加统计时间列
print('清添加时间列成功')
df=convert_date_columns_to_datetime(df) #将excel里面带有'日期'或'时间'字眼的字段转为df的dateframe
print('带有日期或时间字眼的字段转为df的dateframe')
if additional_operation is not None: # 执行额外操作
df = additional_operation(df)
if unify_name_column_name is not None:
df=apply_conditions(df,unify_name_column_name) #统一项目名称
print('项目名称统一成功')
else:
print('不需要统一项目名称')
df=replace_empty_with_none(df) #空值替换为None
print('空值替换为None成功')
insert_dataframe_to_mysql(df,excel_header_list, sql_table_name, sql_header_list,delete_data_sql,truncate_table) #上传数据库
print('上传数据库成功')
if if_delete:
delete_file(filepath,file_name) #删除本地文件
print('本地文件已经删除')
if send_message:
send_email(file_name) #发送邮件
print('邮件发送成功')
else:
print('不需要发送邮件')
扫一扫
2497
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
