Python自動(dòng)化，自動(dòng)下載數(shù)據(jù)+處理Excel表格+信息報(bào)送

前言

今年以來(lái)國(guó)內(nèi)疫情呈現(xiàn)反復(fù)性多點(diǎn)散發(fā)，公司每天有大量的人員來(lái)訪，也有相當(dāng)一部分的職工因私或因公外出，怎么一對(duì)多和多對(duì)一的及時(shí)溝通反饋信息成了落實(shí)疫情防控部署的關(guān)鍵點(diǎn)，作為公司疫情防控的主管單位，這一點(diǎn)我們責(zé)無(wú)旁貸，最終選擇了某星的問(wèn)卷調(diào)查模式，實(shí)時(shí)、簡(jiǎn)單、方便快捷。但隨之而來(lái)的就是我每天要去后臺(tái)下載統(tǒng)計(jì)數(shù)據(jù)，然后對(duì)著表格一通操作，把信息報(bào)出去。一次兩次還可以，但咱“能坐著不站著，能躺著不坐著”的優(yōu)秀發(fā)展理念不能丟，急需研究下怎么才能更好提升工作效率。

需求分析

針對(duì)存在的問(wèn)題，先分析下目前的工作流程：下載數(shù)據(jù)-處理表格-信息報(bào)送，共三大步。第一步受限于公司網(wǎng)絡(luò)物理隔離，暫無(wú)法實(shí)施；第三步微信發(fā)送，與第一步同樣的原因，只能放棄；總的來(lái)看，其中工作最重的第二步就成了本次實(shí)施的最終目標(biāo)。

先分析一下，目前第二步都要處理哪些事情：

1. 查找前一日最后一個(gè)行號(hào)；
2. 根據(jù)行號(hào)刪除不必要的行；
3. 因?yàn)閱?wèn)卷已經(jīng)發(fā)布，且用的人比較多，同時(shí)又有歷史數(shù)據(jù)在里面，不方便重新發(fā)問(wèn)卷，所以要?jiǎng)h除不必要的列；
4. 生成處理后的新的Excel
5. 對(duì)Excel表格的格式進(jìn)行處理

代碼實(shí)現(xiàn)

既然相關(guān)的需要實(shí)施的步驟都應(yīng)經(jīng)分析好了，話不多說(shuō)，上代碼：

1.找出需要進(jìn)行操作的文件

???def?get_fname(self):
????????'''
????????獲取目錄下所有文件
????????:return:?文件名列表的切片，最后兩個(gè)文件
????????'''
????????fnames?=?os.listdir(self.path)
????????return?fnames[-2:]

2.查找前一日最后一行的index

????def?get_last_index(self):
????????'''
????????獲取上一日匯總表總后一個(gè)index
????????:return:?返回上一日最后一個(gè)index
????????'''
????????fpath?=?self.path?+?"/"?+?self.get_fname()[0]
????????df?=?pd.read_excel(fpath,?index_col="序號(hào)")
????????last_num?=?[i?for?i?in?df.index][-1:]??#最后一個(gè)index，這里是列表
????????for?i?in?last_num:?#列表轉(zhuǎn)字符串
????????????#?print(i)
????????????return?i

3.清洗數(shù)據(jù)并返回新的Excel

未處理前的數(shù)據(jù)匯總，這里截取了部分。pd打開文件，得到一個(gè)Dataframe，對(duì)df進(jìn)行刪除行、刪除列、調(diào)整列、返回新的Excel。

????????def?get_excel(self):
????????????'''
????????????使用pandas對(duì)數(shù)據(jù)進(jìn)行清洗
????????????'''
????????????fpath?=?self.path?+?"/"?+?self.get_fname()[1]
????????????df?=?pd.read_excel(fpath)
????????????df.index?=df.index+1416???#賦值新的索引
????????????data_list?=?[i?for?i?in?df.index]???#將索引讀取為列表
????????????del_index_num?=?self.get_last_index()
????????????def_index_target?=?data_list.index(del_index_num)??#獲取前一日結(jié)束索引的下標(biāo)
????????????del_index_list?=?data_list[:def_index_target+1]?????#根據(jù)下標(biāo)切片
????????????df.drop([i?for?i?in?del_index_list],?inplace=True)??#刪除指定的行，drop只支持列表，不支持嵌套
????????????df_cols?=?df.columns.values??#獲取所有列名
????????????col_list?=?[df_col?for?df_col?in?df_cols]???#將所有列名存入列表
????????????col_list[9]?=?col_list[9][:4]?????#修改對(duì)應(yīng)的列名（字符串切片語(yǔ)法）
????????????col_list[14]?=?col_list[14][:16]??#修改對(duì)應(yīng)的列名（字符串切片語(yǔ)法）
????????????col_list[19]?=?col_list[19][:5]???#修改對(duì)應(yīng)的列名（字符串切片語(yǔ)法）
????????????df.columns?=?col_list???#datafram的新列名是列表值
????????????df.drop(labels=["提交答卷時(shí)間","所用時(shí)間","來(lái)源","來(lái)源詳情","來(lái)自IP","是否核酸檢測(cè)","核酸檢測(cè)日期","核酸檢測(cè)結(jié)果","是否接種疫苗","未接種原因"],axis=1,?inplace=True)??#刪除不需要的列,因?yàn)槭枪潭ǖ闹苯訉懥忻?/span>
????????????#?print(df)
????????????new_excel_name?=?"（整理后）"?+?self.get_fname()[1]
????????????df.to_excel(self.path+"/"+new_excel_name,?index=False)
????????????return?new_excel_name

處理后的數(shù)據(jù)，這里截取了部分。

4.處理Excel

上一步處理完后，存在不美觀不方便閱讀的情況，這一步繼續(xù)對(duì)Excel的格式進(jìn)行操作，比如添加邊框，調(diào)整列寬等，使其更方便發(fā)送。

??????def?read_excel(self):
????????'''對(duì)Excel進(jìn)行操作'''
????????app?=?xw.App(visible=False,?add_book=False)
????????app.display_alerts?=?False
????????app.screen_updating?=?False
????????fpath?=?self.path+"/"+self.get_excel()
????????wb?=?app.books.open(fpath)
????????sht?=?wb.sheets[0]
????????sht.range(1,10).column_width?=?45??#設(shè)置第1行第10列列寬
????????sht.range(1,8).column_width?=?21???#設(shè)置第1行第8列列寬
????????sht.range(1,4).column_width?=?16
????????sht.range(1,5).column_width?=?14
????????sht.range(1,7).column_width?=?21
????????sht.range(1,9).column_width?=?12
????????sht.range(1,11).column_width?=10
????????sht.range(1,6).column_width?=?13
????????last_column?=?sht.range(1,1).end("right").get_address(0,0)[0]??#獲取最后一列
????????last_row?=?sht.range(1,1).end("down").row??#獲取最后一行
????????a_range?=?f"A1:{last_column}{last_row}"??#生成表格的數(shù)據(jù)范圍
????????sht.range(a_range).api.HorizontalAlignment?=?-4131
????????sht.range(a_range).api.VerticalAlignment?=?-4130
????????sht.range(a_range).api.Borders(8).LineStyle?=?1???#上邊框
????????sht.range(a_range).api.Borders(9).LineStyle?=?1???#下邊框
????????sht.range(a_range).api.Borders(7).LineStyle?=?1???#左邊框
????????sht.range(a_range).api.Borders(10).LineStyle?=?1??#右邊框
????????sht.range(a_range).api.Borders(11).LineStyle?=?1??#內(nèi)縱邊框
????????sht.range(a_range).api.Borders(12).LineStyle?=?1??#內(nèi)橫邊框
????????data_name?=?datetime.datetime.today()
????????excel_name?=?str(data_name)[5:10]?+?"??重點(diǎn)地區(qū)返回職工、外來(lái)人員登記"??#?獲取文件名
????????wb.save(self.path+"/"+excel_name+".xlsx")???#保存文件
????????wb.close()??#關(guān)閉
????????app.quit()??#退出
????????print("Success")

最終結(jié)果，如愿以償，再也不用每天打開表格一通操作，也不用回想昨天是怎么寫的名稱，調(diào)的多大的間距，昨天的最后一行序號(hào)是多少，所有一切盡在彈指一揮間。

全部代碼

最后，奉上全部代碼

??import?pandas?as?pd
import?xlwings?as?xw
import?os
import?datetime
class?Core_excel():
????def?__init__(self,path):
????????self.path?=?path
????def?get_fname(self):
????????'''
????????獲取目錄下所有文件
????????:return:?文件名列表的切片，最后兩個(gè)文件
????????'''
????????fnames?=?os.listdir(self.path)
????????return?fnames[-2:]
????def?get_last_index(self):
????????'''
????????獲取上一日匯總表總后一個(gè)index
????????:return:?返回上一日最后一個(gè)index
????????'''
????????fpath?=?self.path?+?"/"?+?self.get_fname()[0]
????????df?=?pd.read_excel(fpath,?index_col="序號(hào)")
????????last_num?=?[i?for?i?in?df.index][-1:]??#最后一個(gè)index，這里是列表
????????for?i?in?last_num:?#列表轉(zhuǎn)字符串
????????????#?print(i)
????????????return?i
????def?get_excel(self):
????????????'''
????????????使用pandas對(duì)數(shù)據(jù)進(jìn)行清洗
????????????'''
????????????fpath?=?self.path?+?"/"?+?self.get_fname()[1]
????????????df?=?pd.read_excel(fpath)
????????????df.index?=df.index+1416???#賦值新的索引
????????????data_list?=?[i?for?i?in?df.index]???#將索引讀取為列表
????????????del_index_num?=?self.get_last_index()
????????????def_index_target?=?data_list.index(del_index_num)??#獲取前一日結(jié)束索引的下標(biāo)
????????????del_index_list?=?data_list[:def_index_target+1]?????#根據(jù)下標(biāo)切片
????????????df.drop([i?for?i?in?del_index_list],?inplace=True)??#刪除指定的行，drop只支持列表，不支持嵌套
????????????df_cols?=?df.columns.values??#獲取所有列名
????????????col_list?=?[df_col?for?df_col?in?df_cols]???#將所有列名存入列表
????????????col_list[9]?=?col_list[9][:4]?????#修改對(duì)應(yīng)的列名（字符串切片語(yǔ)法）
????????????col_list[14]?=?col_list[14][:16]??#修改對(duì)應(yīng)的列名（字符串切片語(yǔ)法）
????????????col_list[19]?=?col_list[19][:5]???#修改對(duì)應(yīng)的列名（字符串切片語(yǔ)法）
????????????df.columns?=?col_list???#datafram的新列名是列表值
????????????df.drop(labels=["提交答卷時(shí)間","所用時(shí)間","來(lái)源","來(lái)源詳情","來(lái)自IP","是否核酸檢測(cè)","核酸檢測(cè)日期","核酸檢測(cè)結(jié)果","是否接種疫苗","未接種原因"],axis=1,?inplace=True)??#刪除不需要的列,因?yàn)槭枪潭ǖ闹苯訉懥忻?/span>
????????????#?print(df)
????????????new_excel_name?=?"（整理后）"?+?self.get_fname()[1]
????????????df.to_excel(self.path+"/"+new_excel_name,?index=False)
????????????return?new_excel_name
????def?read_excel(self):
????????'''對(duì)Excel進(jìn)行操作'''
????????app?=?xw.App(visible=False,?add_book=False)
????????app.display_alerts?=?False
????????app.screen_updating?=?False
????????fpath?=?self.path+"/"+self.get_excel()
????????wb?=?app.books.open(fpath)
????????sht?=?wb.sheets[0]
????????sht.range(1,10).column_width?=?45??#設(shè)置第1行第10列列寬
????????sht.range(1,8).column_width?=?21???#設(shè)置第1行第8列列寬
????????sht.range(1,4).column_width?=?16
????????sht.range(1,5).column_width?=?14
????????sht.range(1,7).column_width?=?21
????????sht.range(1,9).column_width?=?12
????????sht.range(1,11).column_width?=10
????????sht.range(1,6).column_width?=?13
????????last_column?=?sht.range(1,1).end("right").get_address(0,0)[0]??#獲取最后一列
????????last_row?=?sht.range(1,1).end("down").row??#獲取最后一行
????????a_range?=?f"A1:{last_column}{last_row}"??#生成表格的數(shù)據(jù)范圍
????????sht.range(a_range).api.HorizontalAlignment?=?-4131
????????sht.range(a_range).api.VerticalAlignment?=?-4130
????????sht.range(a_range).api.Borders(8).LineStyle?=?1???#上邊框
????????sht.range(a_range).api.Borders(9).LineStyle?=?1???#下邊框
????????sht.range(a_range).api.Borders(7).LineStyle?=?1???#左邊框
????????sht.range(a_range).api.Borders(10).LineStyle?=?1??#右邊框
????????sht.range(a_range).api.Borders(11).LineStyle?=?1??#內(nèi)縱邊框
????????sht.range(a_range).api.Borders(12).LineStyle?=?1??#內(nèi)橫邊框
????????data_name?=?datetime.datetime.today()
????????excel_name?=?str(data_name)[5:10]?+?"??重點(diǎn)地區(qū)返回職工、外來(lái)人員登記"??#?獲取文件名
????????wb.save(self.path+"/"+excel_name+".xlsx")???#保存文件
????????wb.close()??#關(guān)閉
????????app.quit()??#退出
????????print("Success")
if?__name__?==?'__main__':
????path?=?"./2021年10月22日起重點(diǎn)地區(qū)來(lái)人登記"
????core_excel?=?Core_excel(path)
????core_excel.read_excel()