Python3 文件处理

import pandas
import xlwt

class MuFm:

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

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

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

    def work(self, mu_path, fm_path , outMuFM_path , selectMuFM_path):
        toDel = ["HKG3838BE",
                 "YTOC421CT",
                 "BERAE22PS",
                 "HKGYC21AA",
                 "HKGH23721",
                 "DUBAE2804",
                 "SELK138HB",
                 "AMSAE2060",
                 "PMIAE2011",
                 "TPEW125WJ",
                 "LONS23201",
                 "MANWD31CT",
                 "FLL1S2147",
                 "DFWG32127",
                 "DCA1S214T",
                 "HKGH23631",
                 "BKKOK21ZG"]

        mu = pandas.DataFrame(pandas.read_excel(mu_path, sheetname=0)[2:])
        x = mu.values
        head = x[0]
        idOfficeID = None
        idNetBookings = None
        idTotalADA = 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

        sumNetBookings = 0
        sumTotalADA = 0
        itms = []
        for r in x[1:-1]:
            tdivn = 0
            if r[idNetBookings] != 0:
                tdivn = round(r[idTotalADA]/r[idNetBookings] , 4)
            itm = self.OutItems(r[idOfficeID] , r[idNetBookings] , r[idTotalADA] , tdivn)
            itms.append(itm)
            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, "Net Bookings")
        sheet_mu.write(0, 2, "Total ADA")
        sheet_mu.write(1, 1, str(sumNetBookings))
        sheet_mu.write(1, 2, str(sumTotalADA))
        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.NetBookings)
            sheet_mu.write(i+2, 2, itm.TotalADA)
            if itm.tdivn > 0 :
                sheet_mu.write(i+2, 3, itm.tdivn)
            if toDel.count(str(itm.OfficeID)) > 0:
                sheet_mu.write(i + 2, 4, str(1))

            if (int(itm.tdivn) >= 300 or (itm.tdivn == 0 and int(itm.TotalADA) >= 300)) and toDel.count(itm.OfficeID) == 0:
                selectMuIDs.append(itm.OfficeID)

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



        fm = pandas.DataFrame(pandas.read_excel(fm_path, sheetname=0)[2:])
        x = fm.values
        head = x[0]
        idOfficeID = None
        idNetBookings = None
        idTotalADA = 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

        sumNetBookings = 0
        sumTotalADA = 0
        itms = []
        for r in x[1:-1]:
            tdivn = 0
            if r[idNetBookings] != 0:
                tdivn = round(r[idTotalADA] / r[idNetBookings], 4)
            itm = self.OutItems(r[idOfficeID], r[idNetBookings], r[idTotalADA], tdivn)
            itms.append(itm)
            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, "Net Bookings")
        sheet_fm.write(0, 2, "Total ADA")
        sheet_fm.write(1, 1, str(sumNetBookings))
        sheet_fm.write(1, 2, str(sumTotalADA))
        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.NetBookings)
            sheet_fm.write(i + 2, 2, itm.TotalADA)
            if itm.tdivn > 0:
                sheet_fm.write(i + 2, 3, itm.tdivn)
            if toDel.count(str(itm.OfficeID)) > 0:
                sheet_fm.write(i + 2, 4, str(1))

            if (int(itm.tdivn) >= 300 or (itm.tdivn == 0 and int(itm.TotalADA) >= 300)) and toDel.count(
                    itm.OfficeID) == 0:
                selectFmIDs.append(itm.OfficeID)

        for i in range(len(toDel)):
            sheet_fm.write(i + 2, 6, toDel[i])
            sheet_fm.write(i + 2, 7, str(1))
        workbookOutMuFm.save(outMuFM_path)


        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])
            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])
            self.fm.append(selectFmIDs[i])
        workbook.save(selectMuFM_path)

def doWork(file_date):
    mu_fm = MuFm()
    for fd in file_date:
        print(fd)
        mu_fm.work("C:/Users/liyang/Desktop/MUFM/mu" + str(fd) + ".xlsx",
                   "C:/Users/liyang/Desktop/MUFM/fm" + str(fd) + ".xlsx",
                   "C:/Users/liyang/Desktop/OUT-MUFM/outMuFM" + str(fd) + ".xls",
                   "C:/Users/liyang/Desktop/OUT-MUFM/selectMuFM" + str(fd) + ".xls")
    mu = list(set(mu_fm.mu))
    fm = list(set(mu_fm.fm))
    workbook = xlwt.Workbook()
    sheet_mu = workbook.add_sheet('mu', cell_overwrite_ok=True)
    for i in range(len(mu)):
        sheet_mu.write(i, 0, mu[i])
    sheet_fm = workbook.add_sheet('fm', cell_overwrite_ok=True)
    for i in range(len(fm)):
        sheet_fm.write(i, 0, fm[i])
    workbook.save("C:/Users/liyang/Desktop/OUT-MUFM/selectMuFM-ALL.xls")



if __name__ == '__main__':
    file_date = [2407 , 2307, 2207 , 2107 , 2007 , 1907 ,1807,1707,
                 1607,1507,1407,1307,1207,1107,1007]
    doWork(file_date)