报表处理

import pandas
import xlwt

class MuFm:

    def __init__(self):
        self.fm = []
        self.mu = []

    class OutItems:
        def __init__(self , OfficeID , NetBookings , TotalADA , tdivn , OfficeName  , CountryCode):
            self.OfficeID = OfficeID
            self.NetBookings = NetBookings
            self.TotalADA = TotalADA
            self.tdivn = tdivn
            self.OfficeName = OfficeName
            self.CountryCode = CountryCode

        def __lt__(self, other):
            return  self.TotalADA > other.TotalADA

        def __str__(self):
            return  str(self.OfficeID) + "," + str(self.NetBookings) + "," + str(self.TotalADA) + "," + str(self.tdivn)


    def sumMuInit(self , file_date , pre_path):
        mus = {}
        for fd in file_date:
            mu_path = pre_path + "/MU" + str(fd) + ".xlsx"
            mu = pandas.DataFrame(pandas.read_excel(mu_path, sheetname=0)[2:])
            x = mu.values
            head = x[0]
            idOfficeID = None
            idNetBookings = None
            idTotalADA = None
            idOfficeName = None
            idCountryCode = None
            for i in range(len(head)):
                if str(head[i]).replace(" ", "").lower() == "OfficeID".lower():
                    idOfficeID = i
                elif str(head[i]).replace(" ", "").lower() == "NetBookings".lower():
                    idNetBookings = i
                elif str(head[i]).replace(" ", "").lower() == "TotalADA".lower():
                    idTotalADA = i
                elif str(head[i]).replace(" ", "").lower() == "OfficeName".lower():
                    idOfficeName = i
                elif str(head[i]).replace(" ", "").lower() == "CountryCode".lower():
                    idCountryCode = i

            for r in x[1:-1]:
                _officeID = r[idOfficeID]
                _netBookings = int(r[idNetBookings])
                _totalADA = int(r[idTotalADA])
                _OfficeName = str(r[idOfficeName])
                _CountryCode = str(r[idCountryCode])
                if mus.get(_officeID):
                    mu = mus.get(_officeID)
                    mu.NetBookings = mu.NetBookings + _netBookings
                    mu.TotalADA = mu.TotalADA + _totalADA
                else:
                    mus[_officeID] = self.OutItems(_officeID , _netBookings , _totalADA , 0 , _OfficeName  , _CountryCode)

        mu_vs = mus.values()
        for mu in mu_vs:
            if mu.NetBookings != 0:
                mu.tdivn = round(float(mu.TotalADA) / float(mu.NetBookings) , 4)
        return  mu_vs


    def sumFmInit(self , file_date , pre_path):
        fms = {}
        for fd in file_date:
            fm_path = pre_path + "/FM" + str(fd) + ".xlsx"
            fm = pandas.DataFrame(pandas.read_excel(fm_path, sheetname=0)[2:])
            x = fm.values
            head = x[0]
            idOfficeID = None
            idNetBookings = None
            idTotalADA = None
            idOfficeName = None
            idCountryCode = None
            for i in range(len(head)):
                if str(head[i]).replace(" ", "").lower() == "OfficeID".lower():
                    idOfficeID = i
                elif str(head[i]).replace(" ", "").lower() == "NetBookings".lower():
                    idNetBookings = i
                elif str(head[i]).replace(" ", "").lower() == "TotalADA".lower():
                    idTotalADA = i
                elif str(head[i]).replace(" ", "").lower() == "OfficeName".lower():
                    idOfficeName = i
                elif str(head[i]).replace(" ", "").lower() == "CountryCode".lower():
                    idCountryCode = i

            for r in x[1:-1]:
                _officeID = r[idOfficeID]
                _netBookings = int(r[idNetBookings])
                _totalADA = int(r[idTotalADA])
                _OfficeName = str(r[idOfficeName])
                _CountryCode = str(r[idCountryCode])
                if fms.get(_officeID):
                    fm = fms.get(_officeID)
                    fm.NetBookings = fm.NetBookings + _netBookings
                    fm.TotalADA = fm.TotalADA + _totalADA
                else:
                    fms[_officeID] = self.OutItems(_officeID , _netBookings , _totalADA , 0 , _OfficeName  , _CountryCode)

        fm_vs = fms.values()
        for fm in fm_vs:
            if fm.NetBookings != 0:
                fm.tdivn = round(float(fm.TotalADA) / float(fm.NetBookings) , 4)
        return fm_vs


    def work(self, file_date , pre_path ,  fm_threshold , mu_threshold):
        del_path = pre_path + "/delOfiiceID.xlsx"
        fm = pandas.DataFrame(pandas.read_excel(del_path, sheetname=0)[:])
        toDel = []
        for r in fm.values:
           toDel.append(r[0])

        whitelist_sumNetBookings = 0
        whitelist_sumTotalADA = 0
        whitelist = []

        sumNetBookings = 0
        sumTotalADA = 0
        itms = []
        for it in self.sumMuInit(file_date , pre_path):
            itms.append(it)
        for itm in itms:
            sumNetBookings += itm.NetBookings
            sumTotalADA += itm.TotalADA

        selectMuIDs = []
        workbookOutMuFm = xlwt.Workbook()
        sheet_mu = workbookOutMuFm.add_sheet('mu', cell_overwrite_ok=True)
        sheet_mu.write(0, 0, "Office ID")
        sheet_mu.write(0, 1, "Office Name")
        sheet_mu.write(0, 2, "Country Code")
        sheet_mu.write(0, 3, "Net Bookings")
        sheet_mu.write(0, 4, "Total ADA")
        sheet_mu.write(0, 5, "Total ADA/Net Bookings")
        sheet_mu.write(0, 6, "after delete " + str(mu_threshold) + " Total ADA/Net Bookings")

        sheet_mu.write(1, 3, str(sumNetBookings))
        sheet_mu.write(1, 4, str(sumTotalADA))
        sheet_mu.write(1, 5, str(round(float(sumTotalADA) / float(sumNetBookings) , 4)))
        itms.sort()
        for i in range(len(itms)):
            itm = itms[i]
            sheet_mu.write(i+2, 0, itm.OfficeID)
            sheet_mu.write(i + 2, 1, itm.OfficeName)
            sheet_mu.write(i + 2, 2, itm.CountryCode)
            sheet_mu.write(i + 2, 3, itm.NetBookings)
            sheet_mu.write(i+2, 4, itm.TotalADA)
            sheet_mu.write(i+2, 5, itm.tdivn)

            if itm.tdivn != 0 :
                sheet_mu.write(i+2, 5, itm.tdivn)
            if toDel.count(str(itm.OfficeID)) > 0:
                sheet_mu.write(i + 2, 6, str(1))

            if (abs(int(itm.tdivn)) >= mu_threshold or (itm.tdivn == 0 and int(itm.TotalADA) >= mu_threshold)) \
                    and toDel.count(itm.OfficeID) == 0:
                selectMuIDs.append(itm)
                sumTotalADA = sumTotalADA - itm.TotalADA
                sumNetBookings = sumNetBookings - itm.NetBookings

            if toDel.count(itm.OfficeID) > 0:
                whitelist.append(itm)
                whitelist_sumTotalADA = whitelist_sumTotalADA + itm.TotalADA
                whitelist_sumNetBookings = whitelist_sumNetBookings + itm.NetBookings

        for i in range(len(toDel)) :
            sheet_mu.write(i+2, 8, toDel[i])
            sheet_mu.write(i+2, 9, str(1))

        sheet_mu.write(1, 6, str(round(float(sumTotalADA) / float(sumNetBookings) , 4)))


        sumNetBookings = 0
        sumTotalADA = 0
        itms = []
        for it in self.sumFmInit(file_date , pre_path):
            itms.append(it)
        for itm in itms:
            sumNetBookings += itm.NetBookings
            sumTotalADA += itm.TotalADA

        selectFmIDs = []
        sheet_fm = workbookOutMuFm.add_sheet('fm', cell_overwrite_ok=True)
        sheet_fm.write(0, 0, "Office ID")
        sheet_fm.write(0, 1, "Office Name")
        sheet_fm.write(0, 2, "Country Code")
        sheet_fm.write(0, 3, "Net Bookings")
        sheet_fm.write(0, 4, "Total ADA")
        sheet_fm.write(0, 5, "Total ADA/Net Bookings")
        sheet_fm.write(0, 6, "after delete " + str(mu_threshold) + " Total ADA/Net Bookings")

        sheet_fm.write(1, 3, str(sumNetBookings))
        sheet_fm.write(1, 4, str(sumTotalADA))
        sheet_fm.write(1, 5, str(round(float(sumTotalADA) / float(sumNetBookings) , 4)))

        itms.sort()
        for i in range(len(itms)):
            itm = itms[i]
            sheet_fm.write(i+2, 0, itm.OfficeID)
            sheet_fm.write(i + 2, 1, itm.OfficeName)
            sheet_fm.write(i + 2, 2, itm.CountryCode)
            sheet_fm.write(i + 2, 3, itm.NetBookings)
            sheet_fm.write(i+2, 4, itm.TotalADA)
            sheet_fm.write(i+2, 5, itm.tdivn)
            if itm.tdivn != 0:
                sheet_fm.write(i + 2, 5, itm.tdivn)
            if toDel.count(str(itm.OfficeID)) > 0:
                sheet_fm.write(i + 2, 6, str(1))

            if (abs(int(itm.tdivn))  >= fm_threshold or (itm.tdivn == 0 and int(itm.TotalADA) >= fm_threshold)) \
                    and toDel.count(itm.OfficeID) == 0:
                selectFmIDs.append(itm)
                sumTotalADA = sumTotalADA - itm.TotalADA
                sumNetBookings = sumNetBookings - itm.NetBookings

            if toDel.count(itm.OfficeID) > 0:
                whitelist.append(itm)
                whitelist_sumTotalADA = whitelist_sumTotalADA + itm.TotalADA
                whitelist_sumNetBookings = whitelist_sumNetBookings + itm.NetBookings

        for i in range(len(toDel)):
            sheet_fm.write(i + 2, 8, toDel[i])
            sheet_fm.write(i + 2, 9, str(1))

        sheet_fm.write(1, 6, str(round(float(sumTotalADA) / float(sumNetBookings), 4)))

        sheet_white = workbookOutMuFm.add_sheet('white_list', cell_overwrite_ok=True)
        sheet_white.write(0, 0, "Office ID")
        sheet_white.write(0, 1, "Net Bookings")
        sheet_white.write(0, 2, "Total ADA")
        sheet_white.write(0, 3, "Total ADA/Net Bookings")
        sheet_white.write(1, 1, str(whitelist_sumNetBookings))
        sheet_white.write(1, 2, str(whitelist_sumTotalADA))
        sheet_white.write(1, 3, str(round(float(whitelist_sumTotalADA) / float(whitelist_sumNetBookings) , 4)))
        for i in range(len(whitelist)):
            itm = whitelist[i]
            sheet_white.write(i + 2, 0, itm.OfficeID)
            sheet_white.write(i + 2, 1, itm.NetBookings)
            sheet_white.write(i + 2, 2, itm.TotalADA)

        workbookOutMuFm.save(pre_path + "/outMu"+str(mu_threshold) + "FM" + str(fm_threshold) + ".xlsx")

        workbook = xlwt.Workbook()
        sheet_mu = workbook.add_sheet('mu', cell_overwrite_ok=True)
        for i in range(len(selectMuIDs)):
            sheet_mu.write(i, 0, selectMuIDs[i].OfficeID)
            sheet_mu.write(i, 1, selectMuIDs[i].OfficeName)
            sheet_mu.write(i, 2, selectMuIDs[i].CountryCode)
            self.mu.append(selectMuIDs[i])
        sheet_fm = workbook.add_sheet('fm', cell_overwrite_ok=True)
        for i in range(len(selectFmIDs)):
            sheet_fm.write(i, 0, selectFmIDs[i].OfficeID)
            sheet_fm.write(i, 1, selectFmIDs[i].OfficeName)
            sheet_fm.write(i, 2, selectFmIDs[i].CountryCode)
            self.fm.append(selectFmIDs[i])
        workbook.save(pre_path + "/selectMu"+str(mu_threshold) + "FM" + str(fm_threshold)  + ".xlsx")



def doWork(file_date , pre_path , fm_threshold , mu_threshold):
    mu_fm = MuFm()
    mu_fm.work(file_date=file_date , pre_path=pre_path , fm_threshold = fm_threshold , mu_threshold = mu_threshold)

if __name__ == '__main__':
    #       操作描述：
    #   pre_path="您文件夹所在的路径"
    #   注意是左反斜杠，如pre_path="C:/Users/liyang/Desktop/ty"
    #   pre_path文件夹清单：
    #       1、 delOfiiceID.xlsx ，存放白单列表，注意保持一致 ;
    #       2、 FM日期.xlsx , MU日期.xlsx ;如FM1017.xlsx 、MU1017.xlsx 、FM1018.xlsx 、MU1018.xlsx ... ；
    #       3、 file_date = [日期] , 日期列表填写您需要统计的日期，与2中一致。
    #       4、 fm_threshold = int(筛选阈值) , mu_threshold = int(筛选阈值)
    doWork(file_date = ["1017" , "1018" , "1019" , "1020" , "1021" , "1022" , "1023"] ,
           pre_path = "C:/Users/Administrator/Desktop/OUT-MUFM" ,
           fm_threshold = int(500) ,
           mu_threshold = int(100)
           )