excelmore2one

#! /usr/bin/python3
# -*- coding: utf-8 -*-
# @Time : 2019/1/30 15:53
# @File : wx5Case.py
# @Software: PyCharm


import wx
import os


#-=================================
import os
import xlrd
import xlsxwriter
import datetime
import time

now = datetime.datetime.now()
delta = datetime.timedelta(days=1)
n_days = now - delta
date = n_days.strftime('%Y_%m_%d_%H_%M')

#========================================


class MyWin(wx.Frame):

    def __init__(self,parent,title):
        super(MyWin,self).__init__(parent,title=title,size=(600,400))

        self.InitUI()


    def InitUI(self):
        self.count = 0
        pnl = wx.Panel(self)

        self.openBtn = wx.Button(pnl,label="选择文件")
        self.Bind(wx.EVT_BUTTON,self.chose_file,self.openBtn)

        self.execBtn = wx.Button(pnl,label="执行文件")
        self.Bind(wx.EVT_BUTTON,self.do_exec,self.execBtn)

        self.filename = wx.TextCtrl(pnl,style=wx.TE_READONLY)
        self.contents = wx.TextCtrl(pnl,style=wx.TE_MULTILINE | wx.TE_READONLY)

        hbox = wx.BoxSizer()
        hbox.Add(self.openBtn,proportion=0,flag=wx.LEFT | wx.ALL,border = 5)
        hbox.Add(self.filename,proportion=1,flag=wx.EXPAND | wx.TOP | wx.BOTTOM,border = 5)
        hbox.Add(self.execBtn,proportion=0,flag=wx.LEFT | wx.ALL,border = 5)

        vbox = wx.BoxSizer(wx.VERTICAL)
        vbox.Add(hbox, proportion=0, flag=wx.EXPAND | wx.ALL)

        vbox.Add(self.contents,proportion=1,flag=wx.EXPAND | wx.LEFT | wx.BOTTOM | wx.RIGHT,border=5)

        pnl.SetSizer(vbox)
        self.Center()
        self.Show(True)

    def chose_file(self,event):
        wildcard = "Text Files(*.txt)|*.txt"
        # dlg = wx.FileDialog(self,u"选择文件",os.getcwd(),"",wildcard,wx.FD_MULTIPLE )
        dlg = wx.DirDialog(self,u'选择文件夹',style=wx.DD_DEFAULT_STYLE)
        file_path = ''
        if dlg.ShowModal() == wx.ID_OK:
            file_path=dlg.GetPath()
        else:
            return

        self.filename.SetValue(file_path)
        # self.contents.SetValue(file_path)


    #==================================================================================
    # 创建一个列表用来保存所有需要合并的excel文件
    excel_files = []

    # 表头文件
    records_head = []

    # 写入文件存储
    records = []

    # 目标文件名
    output_filename = date + '_' + "outfile.xlsx"

    # 2、获取目录下的所有文件名
    def get_filename(self,tar_path):

        for currentDir, includedDirs, includedFiles in os.walk(tar_path):
            if (currentDir.endswith(tar_path.split('\\')[-1])):
                # print('currentDir:',currentDir)
                # print('includedDirs:',includedDirs)
                # print('includedFiles:',includedFiles)
                for i in includedFiles:
                    if i.endswith(".xls") or i.endswith(".xlsx"):
                        # print(i)
                        self.excel_files.append(currentDir + '\\' + i)  # 把excel文件保存到列表中
                    else:
                        continue

    # 3、读取excel文件的内容数据
    def concat_and_insert(self,fdir, sheet_name="Sheet1", n=2):
        if len(fdir) > 0:
            a = 0
            for file in fdir:
                # 读文件
                data = xlrd.open_workbook(file)
                # 获取所有sheet的名称
                sheets_name = data.sheet_names()
                a += 1
                print(len(fdir), ":", a, "==>", file)  # 1月9日添加
                # self.contents.SetLabel(str(len(fdir)) + ":" + str(a) + "==>" + file)
                # 获取指定sheet的名称；
                tar_sheet = data.sheet_by_name(sheet_name)
                # print(tar_sheet)
                self.records_head.append(tar_sheet.row_values(0))
                # 获取表的行数：
                nrows = tar_sheet.nrows
                for i in range(nrows):
                    # 跳过第一行：
                    if i < 1:
                        continue
                    self.records.append(tar_sheet.row_values(i))

    # 3、将结果数据写入到一个汇总后的excel中
    def insert_total(self,alist, tarfile, output_filename="output.xlsx"):
        os.mkdir(tarfile)
        wh = xlsxwriter.Workbook(tarfile + '/' + output_filename)
        wadd = wh.add_worksheet("TOTAL")
        if len(alist) > 0:
            for row_num, row_data in enumerate(alist):
                wadd.write_row(row_num, 0, row_data)
        wh.close()

    def main(self):
        output_file = self.filename.GetValue() + "\\output"
        strat = datetime.datetime.now()
        self.contents.SetLabel(str(strat))
        self.get_filename(self.filename.GetValue() )
        self.concat_and_insert(self.excel_files, sheet_name='线路主任务信息')
        # concat_and_insert(excel_files,sheet_name='计划需求')
        # concat_and_insert(excel_files,sheet_name='Sheet0')
        # concat_and_insert(excel_files,sheet_name='计划需求')
        self.insert_total([self.records_head[0]] + self.records, output_file, output_filename=self.output_filename)
        end = datetime.datetime.now()
        self.contents.SetLabel(str(end))
        # print(end)
        # print("持续时间{}".format(end - strat))
        self.contents.SetLabel("持续时间{}".format(end - strat))
        # print('ok')
        self.contents.SetLabel("execute successful!")

    def do_exec(self,event):
        self.main()

ex = wx.App()
MyWin(None,'Excel_more_2_one_tool')
ex.MainLoop()