import pandas as pd
import tkinter as tk
from tkinter import filedialog
import pathlib
import os
import xlrd

# 選擇文件夾對話框，窗口交互，打開選擇窗口
filedirectory = filedialog.askdirectory()
p1 = pathlib.Path(filedirectory) # 該部分主要為了獲取目標路徑下的文件名
print(p1)
bookname1 = os.listdir(p1) # 返回目標文件夾下的所有文件名
if ".DS_Store" in bookname1:
 bookname1.remove('.DS_Store')

# 刪除文件名里的.xlsx
bookname = []
for n in bookname1:
 n1 = list(n) # 把字符變成列表
 for i in range(5): # 因為去除的是.XSLX，5個字符，可以根據實際需要修改
  n1.pop() # 依次刪除最后一個元素
 n2 = ''.join(n1) # 把列表變成字符
 bookname.append(n2)

excles = p1.rglob('*.xlsx') # 類似于os.work，能夠返回目標路徑下的文件路徑，并且可以添加條件
excelarr = []
for eachexcel in excles:
 excelarr.append(eachexcel) # 創建目標路徑下特定文件名的列表

# 建立一個交互窗口
windows = tk.Tk()
windows.title('請提供如下信息') # 設置文本框的標題
windows.geometry('1000x300') # 設置界面的大小
# tk.Label(windows, text='你好！this is Tkinter', bg='green', font=('Arial', 12), width=30, height=2)
# 說明：bg為背景，font為字體，width為長，height為高，這里的長和高是字符的長和高，比如height=2,就是標簽有2個字符這么高)
tk.Label(windows, text='請輸入想要合并的sheet名稱：').grid(row=0, column=0) # label用來顯示不可編輯的文本和圖標（提示性文字）
tk.Label(windows, text='請輸入想要確定的表頭行數：').grid(row=1, column=0)
tk.Label(windows, text='請輸入您想實現的功能，1為按行合并sheet，2為按列合并sheet，3為sheet匯總：').grid(row=2, column=0)
# Listbox(dict={}) # 創建可選下拉框
e1 = tk.Entry(windows) # 創建輸入框
e2 = tk.Entry(windows) # 創建第2個輸入框
e3 = tk.Entry(windows)
e1.grid(row=0, column=1, padx=10, pady=5)
e2.grid(row=1, column=1, padx=10, pady=5)
e3.grid(row=2, column=1, padx=10, pady=5)
tk.Button(windows, text='點擊繼續', width=10, command=windows.quit) \
   .grid(row=4, column=1, sticky=tk.E, padx=10, pady=5) # sticky表示方位，NSWE為上下左右
tk.mainloop() # 結束循環
# 為變量賦值
word = str(e1.get())
number = int(e2.get()) - 1
choice = int(e3.get())

# 檢查想要處理的sheet是否在所有的目標文件中
file_list = os.listdir(p1)
file_list.remove('.DS_Store') # 移除Mac系統自動生成的文件
for file in file_list: # 循環遍歷列出所有文件名稱
 file_name = os.path.join(p1, file) # 因os.listdir工具返回的是目標文件夾里文件的名字，然而打開文件需要文件路徑+名字，故通過此工具獲取完整的文件名
 workbook = xlrd.open_workbook(file_name) # 打開遍歷的文件
 if word in workbook.sheet_names():
  continue
 else:
  print(str(file) + '中不存在想要合并的sheet')

if choice == 2:
 p1 = pd.ExcelFile(excelarr[0]) # 讀取獲取到的第一個文件名對應的文件
 mergedata1 = p1.parse(header=number, sheet_name=word).iloc[:, 0] # 設置索引為第一行，如果為index_col，則索引為第一列 iloc為取特定的列
 p2 = []
 for i in range(0, len(excelarr)):
  tmp = pd.ExcelFile(excelarr[i]) # 讀取文件
  currentdata = tmp.parse(header=number, sheet_name=word).iloc[:, [1, 2, 3, 4]] # 讀取特定的列
  mergedata1 = pd.concat([mergedata1, currentdata], axis=1) # concat連接函數，唯一必須的參數是參與連接的對象的列表或字典。axis=1，表示可以按照縱軸來合并
  n = currentdata.shape[1] # 返回Dataframe的行數，1為返回列數
  p2.append(n) # 獲取讀取文件的行數列表
 name_list = []
 i = 0
 # 獲取應插入的文件名的列表
 for a1 in bookname:
  for x in range(p2[i]):
   name_list.append(a1)
  i = i + 1
 name_list.insert(0, '來自表格')
 mergedata1.loc[-1] = name_list # 按行插入

if choice == 1:
 p1 = pd.ExcelFile(excelarr[0]) # 讀取獲取到的第一個文件名對應的文件
 mergedata1 = p1.parse(header=number, sheet_name=word) # 設置索引為第一行，如果為index_col，則索引為第一列
 p2 = []
 p2.append(mergedata1.shape[0])
 for i in range(1, len(excelarr)):
  tmp = pd.ExcelFile(excelarr[i]) # 讀取文件
  currentdata = tmp.parse(header=number, sheet_name=word) # 讀取特定的表
  mergedata1 = pd.concat([mergedata1, currentdata]) # concat連接函數，唯一必須的參數是參與連接的對象的列表或字典。axis=1，表示可以按照縱軸來合并
  n = currentdata.shape[0] # 返回Dataframe的行數，1為返回列數
  p2.append(n) # 獲取讀取文件的行數列表
 name_list = []
 i = 0
 # 獲取應插入的文件名的列表
 for a1 in bookname:
  for x in range(p2[i]):
   name_list.append(a1)
  i = i + 1
 mergedata1.insert(0, '來自表格', name_list) # 插入第一列，作為表格數據來源的注釋

if choice == 3:
 i = 0
 fname = tk.filedialog.asksaveasfilename(title=u'保存文件', filetypes=[("excel", ".xlsx")])
 # fideialog的一個方法，可以實現數據儲存是要保存的名字
 writerExcel = pd.ExcelWriter(fname+'.xlsx') # 寫入到一個新的Excel，并且命名為上一步驟確認的名字
 for name in bookname:
  p1 = pd.ExcelFile(excelarr[i])
  mergedata1 = p1.parse(header=number, sheet_name=word) # 設置索引為第一行，如果為index_col，則索引為第一列
  # mergedata1.dropna(thresh=4, inplace=True) # 必須使用inplace才可以使數據庫記住刪除的單元格
  # mergedata1.dropna(axis='columns', how='all') # 刪除全是缺失值的列
  mergedata1.fillna('0') # 將所有缺失值填充為0
  mergedata1.to_excel(writerExcel, sheet_name=name, index=False) # 新建一個sheet儲存信息
  i = i + 1

if choice != 3:
 # 數據清理
 mergedata1.dropna(thresh=4, inplace=True) # 必須使用inplace才可以使數據庫記住刪除的單元格
 mergedata1.dropna(axis='columns', how='all') # 刪除全是缺失值的列
 mergedata1.fillna('0') # 將所有缺失值填充為0
 # 保存至excel
 fname = tk.filedialog.asksaveasfilename(title=u'保存文件',
           filetypes=[("excel", ".xlsx")]) # fideialog的一個方法，可以實現數據儲存是要保存的名字
 writerExcel = pd.ExcelWriter(fname + '.xlsx') # 寫入到一個新的Excel，并且命名為上一步驟確認的名字
 mergedata1.to_excel(writerExcel, sheet_name='匯總表', index=False) # 將之前匯總的farmdate數據通過to excel寫入到Excel中

# 設置格式
sheetname = writerExcel.sheets
workbook = writerExcel.book
for sheets in sheetname:
 worksheet = writerExcel.sheets[sheets]
 format1 = workbook.add_format({'num_format': '###,##0.00', })
 # 通過xlsxwriter模塊命名format1的格式，對于數字內容，每三位進行一個分隔符，并且保留兩位小數。#.00%為保留兩位小數的百分數.border為邊框。最后為文本換行和居中
 # format2 = workbook.add_format({'bold': True, 'italic': True}) # 加粗、斜體
 worksheet.set_column('A:ZZ', 16, format1) # 將上述定義的格式應用到具體的單元格
 # worksheet.set_row(0, 16, format2) # 將特定格式用于表頭

writerExcel.save() # 保存Excel
print('success')