【实战】Python 读写 Excel 实例应用

#coding:utf-8
import xlrd
import xlwt
import re
import sys
import string
from datetime import datetime
 
#read the ad play time data
programFile = r'ad.xlsx'
#读取广告播放时间数据文件
adFile = r'ad.xlsx'
 
#打开文件并获取数据
adWb = xlrd.open_workbook(adFile)
adSheet = adWb.sheet_by_index(0)
 
#获取目标时间列(在第3列，由日期和时间拼接而成)
adTime = adSheet.col_values(2)
 
lenth_of_time = len(adTime)
 
#读取uv数据
uvFile = r'uv.xlsx'
 
uvWb = xlrd.open_workbook(uvFile)
#数据存储在一张表时，如果有多个sheet，要分开赋值
uvSheet = uvWb.sheet_by_index(0)
 
uvTime = uvSheet.col_values(0)
length = len(uvTime)
 
#初始化写操作
resultFile = xlwt.Workbook()
#在生成表的时候添加了两张sheet
resultMobile = resultFile.add_sheet('mobile',cell_overwrite_ok=True)
resultPC = resultFile.add_sheet('PC',cell_overwrite_ok=True)
 
i=1
while(i<lenth_of_time):
   print (i)
   playTime = xlrd.xldate_as_tuple(adTime[i],adWb.datemode)
   print (playTime)
    j= 1
    while(j<length):
       uvRow = uvSheet.row_values(j)
       uvTime = xlrd.xldate_as_tuple(uvRow[0],uvWb.datemode)
       #时间匹配
       if((playTime[0]==uvTime[0] and playTime[1]==uvTime[1] andplayTime[2]==uvTime[2] and ((playTime[3]==uvTime[3] and uvTime[4]-playTime[4]>-2and uvTime[4]-playTime[4]<6) or (uvTime[3]==playTime[3]+1 anduvTime[4]+60-playTime[4]>-2 and uvTime[4]+60-playTime[4]<6) or(uvTime[3]==playTime[3]-1 and uvTime[4]-59==0 and playTime[4]-0==0))) or(playTime[0]==uvTime[0] and playTime[1]==uvTime[1] and playTime[2]==uvTime[2]-1and uvTime[3]==0 and playTime[3]==23 and uvTime[4]+60-playTime[4]<6)):
           gap = uvTime[4]-playTime[4]
 
           if(gap<-50):
                gap=gap+60
           if(gap==59):
                gap=-1
 
           if(uvRow[1]=='mo'):
               resultMobile.write(i,gap+1,uvRow[2])
           if(uvRow[1]=='pc'):
               resultPC.write(i,gap+1,uvRow[2])
       j = j + 1
    i= i + 1
 
#保存结果
resultFile.save(r'result.xls')