PYTHON_加工EXCEL

1. PYTHON加工EXCEL

import tkinter as tk
import pandas as pd

#功能表述：美国组，自动处理30-60报告
#作者：Bob Fu

# 设置打印宽度
pd.set_option('display.width', 300)
#显示所有列
pd.set_option('display.max_columns', 100)
#显示所有行
#pd.set_option('display.max_rows', None)
#设置value的显示长度为100，默认为50
from tkinter import *
import tkinter.filedialog
import datetime
import math
from dateutil.parser import parse



window=tk.Tk()
today = datetime.datetime.now()
start_date = today - datetime.timedelta(weeks = 20)

def choose_file_clicked():
    # 1.选择文件
    filename=tkinter.filedialog.askopenfilename()
    lb.config(text=filename)
    # 2.读取excel数据



def data_clean_clicked(filename):
    excel=pd.read_excel(filename)
    excel=excel.fillna('None')
    # 3.处理数据
    # 3.1筛选
    # for hanghao, hang in excel.iterrows():
    #     # data2 = [yuansu.split('\t') for yuansu in iter(data1)]
    #     if math.isnan(hang[3]):
    # data=excel[excel['RR'].isnull()]
    # data = excel.loc[excel['WW'].str.contains('TBN')]
    # data=[excel['WW']=1 | excel['WW']=2]
    # excel['AA']= pd.to_datetime(excel['AA'])
    # data=excel.loc[(excel['AA'] < today) & (excel['AA'] > start_date)]
    # data = excel.loc[excel['WW']==excel['EE']]
    # data = excel.loc[excel['WW'].str.contains('E E')]
    excel['IMP_VOY']=excel['IMP_VOY'].apply(str)

    excel=excel.drop(excel[excel['ETA'] > today].index)
    excel = excel.drop(excel[excel['ETA'] < start_date].index)
    excel = excel.drop(excel[(excel['IMP_VOY'].str.len()==9)
                             & (~excel['FPOD'].str.contains('CNSIA|CNCTU|CNWUX|CNCGO|CNSHA|CNTAO|CNTXG|CNNGB|CNDLC|None')
                                | (excel['FPOD'].str[0:2]!="KZ|KG|MN|UZ")) ].index)
    # excel=excel.drop(excel[excel['POINT_TO']!=excel['BKG_POD']].index)
    excel = excel.drop(excel[excel['POINT_TO'] != excel['BKG_POD']].index)
    excel = excel.drop(excel[excel['VSL_NAME'].str.contains('TBN')].index)
    excel = excel.drop(excel[excel['SVC']=='None'].index)
    excel = excel.drop(excel[excel['BKG_POD']=='CNAQG|CNCGU|CNCZ1|CNCZX|CNDFE|CNHFE|CNJIA|CNMAA|'
                                              'CNNKG|CNNTG|CNRUG|CNTOL|CNTZO|CNWHI|CNYZH|CNZHE|'
                                              'CNZJG|CNLZU|CNCKG|CNWUH|CNCLJ|CNJIU|CNTAG|CNYBN|'
                                              'CNCSX|CNHSI|CNYUY|CNKHN|CNCTU|CNYIC|CNCDE|CNJGZ|CNQZH|CNSHA|'].index)


    # excel = excel.drop([excel['IMP_VOY'].str.len()==9].index)
    # data=excel.loc[(excel['ETA'] < today) & (excel['ETA'] > start_date)
    #                # & (~excel['VSL_NAME'].str.contains('TBN'))
    #                & (excel['SVC'] != 'None')
    #                & (excel['POINT_TO'] == excel['BKG_POD'])]
    # & excel['POINT_TO'] == excel['BKG_POD']
    # & ~excel['IMP_VOY'].str.contains('TBN')
    # & excel['SVC'] == 'None'

    #         hang[12]=hang[13]
    #     if hang[2]+hang[3]!='E EM' and \
    #         start_date<parse(hang[14])<now and \
    #         hang[9]==hang[12] and \
    #         'TBN' in hang[6].upper() and \
    #         hang[0]!='NaN':
    print(excel)


         # print(hanghao)
        # print(hang[0])

    # 3.2加入pic
    # print(excel)

lb=Label(window)
lb.pack()
lb1=Label(window)
lb1.pack()

anniu=Button(window,text="选择文件",command=choose_file_clicked)
anniu.pack()
anniu1=Button(window,text="选择文件2",command=choose_file_clicked)
anniu1.pack()
window.mainloop()


# 4.输出到excel