python處理excel的一個樣本指令碼

標籤：上下   建立   中文   %s   filename   根據   col   add   get   

###此指令碼為有需要購買車票的指令碼，根據起始讀取對應excel表中對應的資料，再根據此指令碼中對應的規則計算出需要的資料輸出兩個excel

import xlrdimport osimport timeimport random###################基礎資訊填寫#def mk_file(mounth):mounth = ‘5‘###########定義檔案名稱和表頭filenameout = ((mounth) + ‘月份車票.xlsx‘)  #給發票賣家的文檔filenamein = (‘工單服務組_莊豔彬‘ + ‘%s‘ % (mounth) + ‘月份打車記錄.xlsx‘)    ###上傳km的文檔mesout = (‘日期‘ + ‘\t‘ + ‘打車日期‘ + ‘\t‘ + ‘起始時間‘ + ‘\t‘ + ‘到達時間‘ + ‘\t‘ + ‘金額‘ + ‘\n‘)mesin = (‘中文姓名‘ + ‘\t‘ + ‘打車日期‘ + ‘\t‘ + ‘事由‘ + ‘\t‘ + ‘實際上下班時間‘ + ‘\t‘ + ‘上車地點‘ + ‘\t‘ + ‘下車地點‘ + ‘\t‘ + ‘金額‘ + ‘\n‘)file_dir = os.getcwd()mkdir_now = (‘%s‘ % (file_dir) + "\\%s" % (mounth) + ‘月‘)folder = os.path.exists(mkdir_now)if not folder:  # 判斷是否存在檔案夾如果不存在則建立為檔案夾    os.makedirs(mkdir_now)  # makedirs 建立檔案時如果路徑不存在會建立這個路徑    print("--- CREATE DIRECTORY...  ---")    print("---  CREATE SUCCESS!!  ---")else:    print("---  DIRECTORY IS EXIST!  ---")fileout=(‘%s‘ % (file_dir) + "\\%s" % (mounth) + ‘月‘+"\\%s"%(filenameout))filein=(‘%s‘ % (file_dir) + "\\%s" % (mounth) + ‘月‘+"\\%s"%(filenamein))with open(fileout, ‘w‘)as f:    f.write(mesout)    f.close()with open(filein, ‘w‘)as f:    f.write(mesin)    f.close()#####將整理後的資料寫入檔案out、indef record_out(date, classes_1, StartTime, EndTime, money):    date=str(date)    classes_1=str(classes_1)    StartTime=str(StartTime)    EndTime=str(EndTime)    money=str(money)    #filenameout = ((mounth) + ‘月份車票.xlsx‘)    mesout = (date + ‘\t‘ + classes_1 + ‘\t‘ + StartTime + ‘\t‘ + EndTime + ‘\t‘ + money + ‘\n‘)    with open(fileout, ‘a‘)as outself:        outself.write(mesout)        outself.flush()####將整理後的資料寫入檔案out、indef record_in(name, date, classes_1, on_time, on_address, live_address, money):    name=str(name)    date = str(date)    classes_1 = str(classes_1)    on_time = str(on_time)    on_address = str(on_address)    money = str(money)    #filenamein = (‘工單服務組_莊豔彬‘ + ‘%s‘ % (mounth) + ‘月份打車記錄.xlsx‘)    mesin = str(name + ‘\t‘ + date + ‘\t‘ + classes_1 + ‘\t‘ + on_time + ‘\t‘ + on_address + ‘\t‘ + live_address + ‘\t‘ + money + ‘\n‘)    with open(filein, ‘a‘)as inself:        inself.write(mesin)def write_data(filename):    ###################拿源檔案中的資料##############    N3_time = ‘16:00-00:00‘    N9_time = ‘22:30-次日9:00‘    workbook = xlrd.open_workbook(u‘%s‘ % (filename))    sheet_names = workbook.sheet_names()    sheetN = sheet_names[0]    sheet1 = workbook.sheet_by_name(sheetN)    colsNum = sheet1.col_values(2)    data_Num = colsNum.__len__()    for i in range(0, data_Num):        rows = sheet1.row_values(i)        date_classes = int(rows[0])        date_classes3=(date_classes+1)        #print(date3)        classes = int(rows[1])        money = int(rows[2])        date = (‘2018/‘ + ‘%s/‘ % (mounth) + ‘%s‘ % (date_classes))        date3 = (‘2018/‘ + ‘%s/‘ % (mounth) + ‘%s‘ % (date_classes3))        if classes == 3:            on_address = company_address            live_address = home_address            on_time = N3_time            Land = int((money - 13) / 2.3)            LandTime_minute = int((Land / random.randint(60, 80)) * 60)            StartTime_hour = 00  # N3時起始時間（小時)            StartTime_minute = (random.randint(30, 50))  # N3時起始時間（分鐘)            EndTime_minute = StartTime_minute + LandTime_minute  # 到達時間:分鐘(起始時間分鐘數+路上時間分鐘數)            if EndTime_minute >= 60:  # 如果路上總時間大於60分鐘，起始時間小時+1，分鐘等於總時間分鐘數-60                endtime_hour = int(StartTime_hour) + 1                endtime_minute = (EndTime_minute - 60)            elif EndTime_minute < 60:  # 如果路上總時間小於60分鐘，到達時間_小時=起始時間_小時，到達時間_分鐘=總時間分鐘數                endtime_hour = StartTime_hour                endtime_minute = EndTime_minute            StartTime_hour = str(StartTime_hour).zfill(2)            StartTime_minute = str(StartTime_minute).zfill(2)            StartTime = (‘%s:‘ % (StartTime_hour) + ‘%s‘ % (StartTime_minute))            EndTime = (‘%s:‘ % (endtime_hour) + ‘%s‘ % (endtime_minute))  # 輸出此次總資訊            classes_1 = str(‘N‘ + ‘%s‘ % (classes))            #print(date, classes_1, StartTime, EndTime, money)            record_out(date3, classes_1, StartTime, EndTime, money)            record_in(name, date, classes_1, on_time, on_address, live_address, money)        elif classes == 9:            on_address = home_address            live_address = company_address            on_time = N9_time            Land = int((money - 13) / 2.3)            LandTime_minute = int((Land / random.randint(60, 80)) * 60)            StartTime_hour = 21  # N3時起始時間（小時)            StartTime_minute = (random.randint(0, 15))  # N3時起始時間（分鐘)            EndTime_minute = StartTime_minute + LandTime_minute  # 到達時間:分鐘(起始時間分鐘數+路上時間分鐘數)            if EndTime_minute >= 60:  # 如果路上總時間大於60分鐘，起始時間小時+1，分鐘等於總時間分鐘數-60                endtime_hour = int(StartTime_hour) + 1                endtime_minute = (EndTime_minute - 60)            elif EndTime_minute < 60:  # 如果路上總時間小於60分鐘，到達時間_小時=起始時間_小時，到達時間_分鐘=總時間分鐘數                endtime_hour = StartTime_hour                endtime_minute = EndTime_minute            StartTime_hour = str(StartTime_hour).zfill(2)            StartTime_minute = str(StartTime_minute).zfill(2)            StartTime = (‘%s:‘ % (StartTime_hour) + ‘%s‘ % (StartTime_minute))            EndTime = (‘%s:‘ % (endtime_hour) + ‘%s‘ % (endtime_minute))  # 輸出此次總資訊            classes_1 = str(‘N‘ + ‘%s‘ % (classes))            #print(date,classes_1,StartTime,EndTime,money)            record_out(date, classes_1, StartTime, EndTime, money)            record_in(name, date, classes_1, on_time, on_address, live_address, money)#建立月份檔案夾if __name__==‘__main__‘:    name = ‘gary‘    mounth = ‘5‘    filename = ‘5.xls‘    home_address = ‘ad1‘    company_address = ‘ad2‘#    mk_file(mounth)#    print(fileout)    write_data(filename)

　　

python處理excel的一個樣本指令碼