from PyQt5 import QtWidgets
from excelui import Ui_mainWindow # 导入ui文件转换后的py文件
from PyQt5.QtWidgets import QFileDialog, QMessageBox
from pandas import DataFrame as pd_DataFrame, concat as pd_concat, read_excel as pd_read_excel
from os import path as os_path, makedirs as os_makedirs, listdir as os_listdir
import threading

class mywindow(QtWidgets.QMainWindow, Ui_mainWindow):
    def  __init__ (self):
        super(mywindow, self).__init__()
        self.setupUi(self)
        self.active()

    def active(self):
        #读取文件
        self.readfileBtn.clicked.connect(self.read_file)

        #读取文件夹
        self.readdirBtn.clicked.connect(self.read_dir)

        #开始处理
        self.excelrunBtn.clicked.connect(self.excel_cut)

        #退出
        self.exitMenu.triggered.connect(self.close)

        #关于
        self.aboutMenu.triggered.connect(self.aboutsoft)

    def aboutsoft(self):
        QMessageBox.information(self, '关于',  '本软件主要用于把Excel表格切分为复数个文件\n软件名称：表格分割器 (Excel Cuter)\n版本: 2.0\n作者：ovsexia\n更新时间: 2020-06-20\n')

    def read_file(self):
        #选取文件
        filename, filetype =QFileDialog.getOpenFileName(self, '选取文件', 'C:/', 'All Files(*);;Text Files(*.csv)')
        self.readdirText.setText('')
        self.readfileText.setText(filename)

    def read_dir(self):
        #选取文件夹
        foldername = QFileDialog.getExistingDirectory(self, '选取文件夹', 'C:/')
        self.readfileText.setText('')
        self.readdirText.setText(foldername)

    #创建文件夹
    def mkdir(self, pathstr):
        # 去除首位空格
        pathstr = pathstr.strip()
        # 去除尾部 \ 符号
        pathstr = pathstr.rstrip("\\")

        # 判断路径是否存在
        # 存在     True
        # 不存在   False
        isExists = os_path.exists(pathstr)

        # 判断结果
        if not isExists:
            # 如果不存在则创建目录
            # 创建目录操作函数
            os_makedirs(pathstr)
            print(pathstr+' 创建成功')
            return True
        else:
            # 如果目录存在则不创建，并提示目录已存在
            print(pathstr+' 目录已存在')
            return False

    def save_excel(self, arr, start, end, fname, ifhead=True):
        new_df = pd_DataFrame()
        for i in range(start, end):
            new_df = pd_concat([new_df, arr.iloc[[i],:]], axis = 0, ignore_index = True)
        try:
            new_df.to_excel('{fname}.xls'.format(fname=fname), index = False, header=ifhead)
        except:
            print(self.error('{fname}.xls'.format(fname=fname)+'文件被占用，请先关闭文件'))

    def success(self, msg):
        QMessageBox.information(self, '提示', msg)

    def error(self, msg):
        QMessageBox.warning(self, '错误', msg)

    def excel_cut(self):
        threads = []
        threads.append(threading.Thread(target=self.excel_cut1))
        threads.append(threading.Thread(target=self.excel_cut2))
        i = 0
        for t in threads:
            t.setDaemon(True)
            t.start()
            if i==1:
                self.excelrunBtn.setText('开始处理')
                self.success('处理成功')
            i += 1

    def excel_cut1(self):
        self.excelrunBtn.setText('处理中')

    def excel_cut2(self):
        fnum_index = self.fnumSelect.currentIndex() #获取选中下标
        fnum = self.fnumSelect.itemText(fnum_index) #获取选中值
        fnum = int(fnum)

        #优先读取选择文件框
        ffile = self.readfileText.text()
        if ffile:
            ddir = os_path.split(ffile)[0]
        else:
            ddir = self.readdirText.text()

        if ddir:
            pass
        else:
            self.error('未选择文件')
            return False

        if ffile:
            files = [os_path.split(ffile)[1]]
        else:
            files = os_listdir(ddir)

        extarr = []
        for i in files:
            if os_path.splitext(i)[1] == '.xls' or os_path.splitext(i)[1] == '.xlsx':
                extarr.append('{a}\\{b}'.format(a=ddir, b=i))

        if extarr:
            self.mkdir('{a}{b}'.format(a=ddir, b='/cut'))

        for excel_file in extarr:
            data = pd_read_excel(excel_file)
            rows = data.shape[0]  #获取行数 shape[1]获取列数

            if fnum == 1:
                half1 = rows
            elif fnum == 2:
                half1 = int(rows/fnum)
                half2 = rows - half1
            elif fnum == 3:
                half1 = half2 = int(rows/fnum)
                half3 = rows - half1 - half2

            #文件名前缀
            excel_dir = os_path.split(excel_file)
            excel_file = os_path.splitext(excel_dir[1])[0]
            excel_dir = excel_dir[0]

            self.save_excel(data, 0, half1, '{d}/cut/{f}_1'.format(d=excel_dir, f=excel_file))
            if fnum>=2 and half2:
                self.save_excel(data, half1, half1+half2, '{d}/cut/{f}_2'.format(d=excel_dir, f=excel_file), False)
            if fnum==3 and half3:
                self.save_excel(data, half1+half2, half1+half2+half3, '{d}/cut/{f}_3'.format(d=excel_dir, f=excel_file), False)

if __name__=='__main__':
    import sys
    app=QtWidgets.QApplication(sys.argv)
    ui = mywindow()
    ui.show()
    sys.exit(app.exec_())