实习的时候调整excel文件写的脚本

m0_51436392

已于 2024-07-11 20:35:45 修改

阅读量212

点赞数 3

文章标签： excel

于 2024-07-11 20:26:03 首次发布

本文链接：https://blog.csdn.net/m0_51436392/article/details/140361270

版权

就是一些数据分析和用户运营需要的数据，当时用的是影刀这款软件，然后有四个店铺账号，每个账号都要点击相同的地方，来获取excel文件。这些脚本是后续一些调整的动作。

//就是这些都是我从网上看别人怎么写的，然后自己改了一下，其实就是有很不规范的地方，比如说这些文件打开了我都没有close，那时候不知道还要close，所以发生错误了只能手动去任务管理器把进程关掉

import os
import pandas as pd
from datetime import datetime
import win32com.client as win32
from datetime import datetime,timedelta
riqi=(datetime.now()+timedelta(days=-2)).strftime('%m-%#d')
folder_path ="C:/Users/yauklt/Desktop/会员运营/天猫客服/"+riqi
# 遍历每个Excel文件
files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]
for file in files:
    file_path = os.path.join(folder_path, file)
    df = pd.read_excel(file_path,converters={"商品编号":str})
    # 读取Excel文件
    now=datetime.now()
    year=now.year
    str2=folder_path
    str2=str(year)+"-"+str2.replace("\\","/").split('/')[-1]
    print(str2)
    object=datetime.strptime(str2,"%Y-%m-%d")
    riqi=object.strftime("%Y/%#m/%#d")
    print(riqi)
    month=object.month
   
    file_name = os.path.splitext(file)[0]
    print(file_name)
    if "集团" in file_name:
        dianpu="tcl集团官方旗舰店"
    elif "电视" in file_name:
        dianpu="tcl电视官方旗舰店"
    elif "空调" in file_name:
        dianpu="tcl空调官方旗舰店"
    else:
        dianpu="tcl冰箱旗舰店"
        
    
    df.insert(df.columns.get_loc("旺旺"),"月份格式",value=str(month)+"月")
    df.insert(df.columns.get_loc("旺旺"),"日期格式",value=riqi)
    df.insert(df.columns.get_loc("旺旺"),"店铺",value=dianpu) 
    column=column=df.iloc[:,df.columns.get_loc("旺旺")].tolist()
    df.drop(df.index[len(column)-6:len(column)],inplace=True) 
    column=column=df.iloc[:,df.columns.get_loc("商品编号")].tolist()
    for i in column:
        i=str(i)+'\t'
        print(i)
    
    pd.set_option("display.max_colwidth",1000)




    df.to_excel(file_path, index=False)
    

#文件路径

#构建新的表格名称
new_filename = folder_path + '\\天猫客服.xlsx'
#找到文件路径下的所有表格名称，返回列表
file_list = os.listdir(folder_path)
new_list = []

for file in file_list:
    #重构文件路径
    file_path = os.path.join(folder_path,file)
    #将excel转换成DataFrame
    dataframe = pd.read_excel(file_path)
    #保存到新列表中
    new_list.append(dataframe)

#多个DataFrame合并为一个
df = pd.concat(new_list)
#写入到一个新excel表中
df.to_excel(new_filename,index=False)
df = pd.read_excel(new_filename,converters={"商品编号":str})
column=column=df.iloc[:,df.columns.get_loc("商品编号")].tolist()
for i in column:
    i=str(i)+'\t'
    print(i)
pd.set_option("display.max_colwidth",1000)


df.to_excel(new_filename, index=False)

#python3
import xlwings as xw
import datetime
from datetime import datetime,timedelta
riqi=(datetime.now()+timedelta(days=-1)).strftime('%m-%#d')
print(riqi)
path = "C:/Users/yauklt/Desktop/会员运营/京东/京东"+riqi+".xls"

app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False  # 是否实时刷新excel程序的显示内容
wb = app.books.open(path)
sht = wb.sheets['群转化数据']
for i in sht.range('c2:f9'):
        i.value=float(i.value)
for i in sht.range('g2:g9'):
        #print(i.value.strip('%'))
        i.value="{}%".format(float(i.value.strip('%')))
for i in sht.range('h2:h9'):
        #i.value=i.value.strptime(i.value, '%Y-%m-%d')
        i.value_object=datetime.strptime(i.value,"%Y-%m-%d")
        i.value=i.value_object.strftime("%Y/%m/%d")

sht2 = wb.sheets['群活跃数据']
for i in sht2.range('f2:g9'):
        #print(i.value.strip('%'))
        i.value="{}%".format(float(i.value.strip('%')))
for i in sht2.range('h2:h9'):
        #i.value=i.value.strptime(i.value, '%Y-%m-%d')
        i.value_object=datetime.strptime(i.value,"%Y-%m-%d")
        i.value=i.value_object.strftime("%Y/%m/%d")

sht3 = wb.sheets['群基础数据']
for i in sht3.range('f2:f9'):
        #i.value=i.value.strptime(i.value, '%Y-%m-%d')
        i.value_object=datetime.strptime(i.value,"%Y-%m-%d")
        i.value=i.value_object.strftime("%Y/%m/%d")

for i in 0,1:    
    sheet = wb.sheets[i]
    column=sheet.range('A:A')
    column.insert()
    column.insert()
    # 获取第一列和第二列的范围
    column1 = sheet.range('D2:D9')
    column2 = sheet.range('C2:C9')
    # 将第二列的值赋值给第一列
    row=2
    for i in column2.value:
        #print(i)
        if 'TCL'in i:
            sheet['B'+str(row)].value="非私域群"
        else:
            sheet['B'+str(row)].value="私域群"
        row=row+1
    sheet['A2:A9'].value=str(sheet['J2'].value.month)+"月"
    sheet.range('A2:B9').color='#F8CBAD'

    sheet.range('A2:B9').api.Borders.LineStyle=1
    sheet.range('A2:B9').font.name='宋体'
    sheet.range('A2:B9').font.size=11
    
sheet=wb.sheets[2]
column=sheet.range('A:A')
column.insert()
column.insert()
    # 获取第一列和第二列的范围
column2 = sheet.range('C2:C9')
    # 将第二列的值赋值给第一列
row=2
for i in column2.value:
    #print(i)
    if 'TCL'in i:
        sheet['B'+str(row)].value="非私域群"
    else:
        sheet['B'+str(row)].value="私域群"
    row=row+1
sheet['A2:A9'].value=str(sheet['H2'].value.month)+"月"
sheet.range('A2:B9').color='#F8CBAD'

sheet.range('A2:B9').api.Borders.LineStyle=1
sheet.range('A2:B9').font.name='宋体'
sheet.range('A2:B9').font.size=11
    # 保存并关闭Excel文件
wb.save()
wb.close()

import os
import pandas as pd
import snownlp
from snownlp import SnowNLP
from datetime import datetime,timedelta
riqi=(datetime.now()).strftime('%Y-%m-%d')
#today=2023-10-23
today=riqi
yestoday=(datetime.now()+timedelta(days=-1)).strftime('%Y-%m-%d')
yestoday1=(datetime.now()+timedelta(days=-1)).strftime('%m-%d')
# 获取文件夹中的所有Excel文件
folder_path = "C:/Users/yauklt/Desktop/天猫评论数据/"+yestoday1
#folder_path = 'C:/Users/yauklt/Desktop/天猫评论数据/10-21'
files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]
new_column_header='是否差评'
True1="是"
False1=""
# 遍历每个Excel文件
for file in files:
    file_path = os.path.join(folder_path, file)
    
    # 读取Excel文件
    df = pd.read_excel(file_path)
    
    # 获取文件名
    file_name = os.path.splitext(file)[0]
    
    # 替换有内容的行的第一列为文件名
    df.iloc[0:, 0] = file_name
    
    specific_column='首次评价'
    df.insert(df.columns.get_loc(specific_column),new_column_header,' ')
    #df.columns.get_loc(time_column)
    time_column='首评时间'
    row=1 
    time_column_n=df.iloc[:,df.columns.get_loc(time_column)].tolist()
    #del(column_n[0])
    for riqi in time_column_n:
        row=row+1
        if(riqi!=yestoday and riqi!=today):
            break;
    print(row)
    #print(column_n)
    #print(len(column_n))
    #df.delete_rows(idx=row,amount={len(column_n-row)})
    df.drop(df.index[row-1:len(time_column_n)],inplace=True)       
    
    
    column_n1=df.iloc[:,df.columns.get_loc(specific_column)].tolist()
    
    def is_negative_review(value):
        text=value
        blob = SnowNLP(text)
        
        sentiment = blob.sentiments  # 这个库的情绪分 0 ~ 1,越低越消极
        if(text=="此用户没有填写评价。"):
            return False1
        elif(sentiment<0.12): 
            return True1
        else:
            return False1

    result = [is_negative_review(value) for value in column_n1]    
    df.iloc[:, df.columns.get_loc(specific_column)-1] = result
    
    
    # 保存修改后的Excel文件
    df.to_excel(file_path, index=False)

import os
import pandas as pd
from datetime import datetime
import win32com.client as win32
from datetime import datetime,timedelta
riqi=(datetime.now()+timedelta(days=-1)).strftime('%m-%#d')
issiyu="私域群"
notsiyu="非私域群"
folder_path ="C:/Users/yauklt/Desktop/会员运营/天猫私域/"+riqi
files = [f for f in os.listdir(folder_path) if f.endswith('.xls')]
# 遍历每个Excel文件
for file in files:
    file_path = os.path.join(folder_path, file)
    excel = win32.gencache.EnsureDispatch('Excel.Application')
    wb = excel.Workbooks.Open(file_path)
    # 51是.xlsx格式
    # 56是.xls格式
    print(file_path)
    print(file)
    wb.SaveAs(file_path + "x", FileFormat=51)
    wb.Close()
    excel.Application.Quit()
    os.remove(file_path)

files = [f for f in os.listdir(folder_path) if f.endswith('.xlsx')]
for file in files:
    file_path = os.path.join(folder_path, file)
    df = pd.read_excel(file_path)
    # 读取Excel文件
    column=df.iloc[:,df.columns.get_loc("日期")].tolist()
    column_object=datetime.strptime(str(column[1]),"%Y%m%d")
    riqi=column_object.strftime("%Y/%#m/%d")
    month=column_object.month
    file_name = os.path.splitext(file)[0]
    print(file_name)
    if "集团" in file_name:
        dianpu="tcl集团官方旗舰店"
    elif "电视" in file_name:
        dianpu="tcl电视官方旗舰店"
    elif "空调" in file_name:
        dianpu="tcl空调官方旗舰店"
    else:
        dianpu="tcl冰箱旗舰店"
        
    
    df.insert(df.columns.get_loc("日期"),"月份格式",value=str(month)+"月")
    df.insert(df.columns.get_loc("日期"),"日期格式",value=riqi)
    df.insert(df.columns.get_loc("日期"),"类型",'')
    df.insert(df.columns.get_loc("日期"),"店铺",value=dianpu)
    column1=df.iloc[:,df.columns.get_loc("群组名称")].tolist()
        
        
        
        
        
    def is_siyuqun(value):
        if("VIP专属群" in value):
            return issiyu
        else:
            return notsiyu

    result = [is_siyuqun(value) for value in column1]    
    df.iloc[:, df.columns.get_loc("类型")] = result
    #print(type(df.["日期"].value))
    #df.insert(0,"月份格式",value=)
    df.to_excel(file_path, index=False)
    

#文件路径

#构建新的表格名称
new_filename = folder_path + '\\天猫私域.xlsx'
#找到文件路径下的所有表格名称，返回列表
file_list = os.listdir(folder_path)
new_list = []

for file in file_list:
    #重构文件路径
    file_path = os.path.join(folder_path,file)
    #将excel转换成DataFrame
    dataframe = pd.read_excel(file_path)
    #保存到新列表中
    new_list.append(dataframe)

#多个DataFrame合并为一个
df = pd.concat(new_list)
#写入到一个新excel表中
df.to_excel(new_filename,index=False)