mport sys
import os
from PyQt5 import QtWidgets
from PyQt5.QtGui import QIcon
import xlrd
import datetime
import time
from openpyxl import Workbook
from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
class MainGUI(QtWidgets.QMainWindow):
def __init__(self):
super().__init__()
self.setWindowTitle("領(lǐng)料明細(xì)匯總")
self.resize(800, 400)
self.main_widget = QtWidgets.QWidget()
self.main_widget_layout = QtWidgets.QGridLayout()
self.main_widget.setLayout(self.main_widget_layout)
self.input = QtWidgets.QLineEdit()
self.input_btn = QtWidgets.QPushButton("選擇輸入文件夾")
self.output = QtWidgets.QLineEdit()
self.output_btn = QtWidgets.QPushButton("選擇輸出文件夾")
self.show_result = QtWidgets.QListWidget()
self.run = QtWidgets.QPushButton("執(zhí)行匯總")
self.main_widget_layout.addWidget(self.input,0,0,1,2)
self.main_widget_layout.addWidget(self.input_btn, 0, 2, 1, 1)
self.main_widget_layout.addWidget(self.output,1,0,1,2)
self.main_widget_layout.addWidget(self.output_btn, 1, 2, 1, 1)
self.main_widget_layout.addWidget(self.run, 2, 2, 1, 1)
self.main_widget_layout.addWidget(self.show_result, 3, 0, 3, 3)
self.setCentralWidget(self.main_widget)
self.input_btn.clicked.connect(self.Choice_dir_input) #將"選擇輸入文件夾"按鈕綁定Choice_dir_input函數(shù)
self.output_btn.clicked.connect(self.Choice_dir_output) #將"選擇輸出文件夾"按鈕綁定Choice_dir_output函數(shù)
self.run.clicked.connect(self.Summary_data) #“執(zhí)行匯總”按鈕綁定Summary_data函數(shù)
def Choice_dir_input(self):
#選擇目錄操作
dir_path = QtWidgets.QFileDialog.getExistingDirectory(self, "請選擇文件夾路徑", "D:\")
#將選擇的目錄顯示在文本編輯框中
self.input.setText(dir_path)
def Choice_dir_output(self):
dir_path = QtWidgets.QFileDialog.getExistingDirectory(self, "請選擇文件夾路徑", "D:\")
self.output.setText(dir_path)
def Get_data(self, file):
'''獲取單個Excel文件中的資料'''
wb = xlrd.open_workbook(file)
ws = wb.sheets()[0]
data = {}
for row in range(7, ws.nrows-2):
card_id = ws.cell(2, 16).value
car = ws.cell(3, 16).value
dt = ws.cell(row, 0).value
if type(dt) is float:
date_time = xlrd.xldate.xldate_as_datetime(dt, 0)
else:
date_time = datetime.datetime.strptime(dt,'%Y-%m-%d %H:%M:%S')
business = ws.cell(row, 2).value
model = ws.cell(row, 3).value
qty = ws.cell(row, 4).value
unit_price = ws.cell(row, 6).value
price = ws.cell(row, 8).value
reward = ws.cell(row, 9).value
discount = ws.cell(row, 11).value
balance = ws.cell(row, 13).value
location = str(ws.cell(row, 15).value).strip()
operator = ws.cell(row, 17).value
date = date_time.date()
time = date_time.time()
info_list=[card_id,car,date_time,business,model,qty,unit_price,price,reward,discount,
balance,location,operator,date,time]
data.setdefault(date,[])
if info_list[3] != "備注":
data[date].Append(info_list)
#增加當(dāng)日加油次數(shù)
for key in data.keys():
for i in data[key]:
i.append(len(data[key]))
return data
def Get_file_path(self,path):
files=[]
for file in os.listdir(path):
if file.endswith(".xls"): #排除文件夾內(nèi)的其它干擾文件
files.append(path+"\"+file)
return files
def Get_current_time(self):
time_stamp = time.time() # 當(dāng)前時間的時間戳
local_time = time.localtime(time_stamp) #
str_time = time.strftime('%Y-%m-%d %H.%M.%S', local_time)
return str_time
def Summary_data(self,files):
thin = Side(border_style="thin", color="000000")#定義邊框粗細(xì)及顏色
title = ['部門', '部門編號', '時間', '業(yè)務(wù)類型', '品種', '數(shù)量', '單價', '金額', '額外值',
'調(diào)整', '剩余', '庫位', '操作員', '領(lǐng)取日期', '領(lǐng)取時間', '領(lǐng)取次數(shù)']
wb = Workbook()
ws = wb.active
ws.merge_cells("A1:P1")
ws.cell(1,1).value = "領(lǐng)料明細(xì)匯總表"
ws.cell(1,1).font = Font(name=u'黑體',bold=True,size=18)
ws.row_dimensions[1].height = 22.2
ws.cell(1,1).alignment = Alignment(horizontal="center", vertical="center")
ws.append(title)
#插入數(shù)據(jù)
files = self.Get_file_path(self.input.text()) #獲取文本編輯框中的輸入文件目錄，并獲取目錄下的xls文件
for file in files:
data = self.Get_data(file)
for key in data.keys():
for i in data[key]:
ws.append(i)
f = QtWidgets.QListWidgetItem(f"{file} 的內(nèi)容已加入總表.") # 創(chuàng)建一個顯示項
self.show_result.addItem(f) # 將結(jié)果添加到部件中
#設(shè)置字號，對齊，縮小字體填充，加邊框
#Font(bold=True)可加粗字體
for row_number in range(2, ws.max_row+1):
for col_number in range(1,17):
c = ws.cell(row=row_number,column=col_number)
c.font = Font(size=9)
c.border = Border(top=thin, left=thin, right=thin, bottom=thin)
c.alignment = Alignment(horizontal="left", vertical="center")
col_name= list("ABCDEFGHIJKLMNOP")
col_width = [8, 8, 16, 8, 16, 8, 8, 9.8, 8, 8, 8, 11, 8.3, 9, 8, 8]
for i in range(len(col_name)):
ws.column_dimensions[col_name[i]].width = col_width[i]
ws.column_dimensions.group('I','K',hidden=True)
ws.column_dimensions.group('N','O',hidden=True)
wb.save(f"{self.output.text()}\領(lǐng)料明細(xì)匯總表{self.Get_current_time()}.xlsx")
f = QtWidgets.QListWidgetItem(f"n領(lǐng)料明細(xì)匯總表{self.Get_current_time()}.xlsx 已生成，請去輸出文件夾查看.") # 創(chuàng)建一個顯示項
self.show_result.addItem(f) # 將結(jié)果添加到部件中
def main():
app = QtWidgets.QApplication(sys.argv)
app.setWindowIcon(QIcon("PO.ico"))#設(shè)置界面左上角圖標(biāo)
gui = MainGUI()
gui.show()
sys.exit(app.exec_())
if __name__ == '__main__':
main()
