[python]获取一年日历数据并写入excel表格中
本帖最后由 wushaominkk 于 2019-1-11 16:36 编辑简单来说就是先爬取数据，然后把数据写入到excel中，做成日历表~~哈哈哈，经供参考和学习哈~~
下面是代码，写的不够规范，别见笑哈哈哈
# coding=gbk
import requests
from bs4 import BeautifulSoup
import xlwt
# 获取一年数据，以字典返回
def getYear():
yearDic = {}
week = 2 # 初始星期，2019年1月1日为星期二
year = 2019 # 年份 想要哪年的就改成那年的 记得修改上面的初始星期
urlList = []
uUrl = 'https://wannianrili.51240.com/ajax/?q={}-{}&v=18121803'
# 构造每个月的接口链接
for y in range(1,13):
if y<10:
rUrl = uUrl.format(year,'0'+str(y))
urlList.append(rUrl)
else:
rUrl = uUrl.format(year,y)
urlList.append(rUrl)
headers = {
'User-Agent': 'Mozilla / 5.0(Windows NT 10.0; Win64;x64) AppleWebKit / 537.36(KHTML, likeGecko) Chrome / 68.0.3440.106Safari / 537.36',
}
for i in range(0,len(urlList)):
monthDic = {}
html = requests.get(urlList, headers=headers)
soup = BeautifulSoup(html.content, 'lxml')
riqiList = soup.find_all(class_='wnrl_riqi')
for riqi in riqiList:
dayList = []
g = riqi.find_all(class_='wnrl_td_gl')
n = riqi.find_all(class_='wnrl_td_bzl')
gStr = g.get_text()
nStr = n.get_text()
# 找到该元素视为法定节假日
if riqi.find_all(class_='wnrl_riqi_xiu'):
nStr+='(休)'
dayList.append(week)
# 到星期日后重置为0
if week == 7:
week = 0
week+=1
dayList.append(gStr)
dayList.append(nStr)
monthDic = dayList
yearDic = monthDic
return yearDic,year
# 初始每个月的星期标题单元格坐标，页面3*4网格方式展示
def coordinates():
yearCoorDic = {}
x = 2 # 初始横坐标
y = 0 # 初始纵坐标
interval = 2 # 月份之间的纵坐标间距
for i in range(1,13):
monthList = []
# 月份为1,5,9 时num重新初始为0
if i == 1 or i == 5 or i == 9:
num = y
if i < 5:
# 循环7次，为星期一到星期天
for k in range(7):
tList = []
# cross =x # 横
# 每次纵坐标+1
column =k + num # 纵
tList.append(x)
tList.append(column)
# 记住最后一次（星期天）纵坐标+月份间隔，为下个月（星期一）的纵坐标值
if k == 6:
num = column+interval
monthList.append(tList)
if i>4 and i<9:
for k in range(7):
tList = []
# 横坐标方向的单元格数，计算得出日+农历最大占用12行。这里给14行算上横坐标间距
cross =x + 14 # 横
column =k + num # 纵
tList.append(cross)
tList.append(column)
if k == 6:
num = column+interval
monthList.append(tList)
if i>8 and i<13:
for k in range(7):
tList = []
cross =x + 14*2 # 横
column =k + num # 纵
tList.append(cross)
tList.append(column)
if k == 6:
num = column+interval
monthList.append(tList)
yearCoorDic = monthList
return yearCoorDic
def template():
book = xlwt.Workbook()# 新建一个excel对象
sheet = book.add_sheet('2019年日历表')# 添加一个sheet页
month_style = xlwt.easyxf('font: height 280;')# 定义月份标题单元格高度
week_style = xlwt.easyxf('font: height 340;')# 定义星期单元格高度
Content_style = xlwt.easyxf('font: height 280;') # 定义日期和农历单元格高度
styleRed = xlwt.XFStyle()# 创建一个样式对象，初始化样式适用于周末单元格
styleRed1 = xlwt.XFStyle()# 创建一个样式对象，初始化样式适用于周末单元格且节日名过长时
styleBlack = xlwt.XFStyle()# 创建一个样式对象，初始化样式 适用于工作日单元格
styleBlack_ = xlwt.XFStyle()# 创建一个样式对象，初始化样式 适用于农历单元格
styleBlack1_ = xlwt.XFStyle() # 创建一个样式对象，初始化样式 适用于农历单元格且节日名过长时
titleStyle = xlwt.XFStyle()# 创建一个样式对象，初始化样式适用于月份标题单元格
styleContent = xlwt.XFStyle() # 创建一个样式对象，初始化样式适用于日期和农历单元格
# 设置单元格样式通用
al = xlwt.Alignment()
al.horz = 0x02# 设置水平居中
al.vert = 0x01# 设置垂直居中
# 设置单元格样式适用于styleRed1 styleBlack1_
al1 = xlwt.Alignment()
al1.vert = 0x01# 设置垂直居中
# 设置单元格样式适用于周末和法定节假日
fnt = xlwt.Font()
fnt.bold = True# 字体加粗
fnt.name = u'微软雅黑'# 设置其字体为微软雅黑
fnt.colour_index = 2# 字体红色
# 设置单元格样式适用于工作日和星期
fnt1 = xlwt.Font()
fnt1.bold = True# 字体加粗
fnt1.name = u'微软雅黑'# 设置其字体为微软雅黑
fnt1.colour_index = 0# 字体黑色
# 设置单元格样式适用于农历
fnt1_ = xlwt.Font()
fnt1_.bold = True# 字体加粗
fnt1_.name = u'微软雅黑'# 设置其字体为微软雅黑
fnt1_.colour_index = 23# 字体灰色
# 设置单元格样式适用于月份标题显示
fnt2 = xlwt.Font()
fnt2.bold = True# 字体加粗
fnt2.name = u'微软雅黑'# 设置其字体为微软雅黑
fnt2.colour_index = 1# 字体黑色
pattern = xlwt.Pattern()# Create the pattern
pattern.pattern = xlwt.Pattern.SOLID_PATTERN# May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
pattern.pattern_fore_colour = 8# May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on..
# 应用单元格样式
styleRed.alignment = al
styleRed.font = fnt
styleRed1.alignment = al1
styleRed1.font = fnt
# 应用单元格样式
styleBlack.alignment = al
styleBlack.font = fnt1
# 应用单元格样式
styleBlack_.alignment = al
styleBlack_.font = fnt1_
styleBlack1_.alignment = al1
styleBlack1_.font = fnt1_
# 应用单元格样式
titleStyle.alignment = al
titleStyle.font = fnt2
titleStyle.pattern = pattern
styleContent.alignment = al
# 获取每个月星期标题坐标初始值
yearCoorDic = coordinates()
print('正在获取日历数据...')
# 获取一年的数据
yearDic,year = getYear()
titList = list(yearCoorDic.keys())
print('%s年数据获取成功,正在写入模板...'%year)
for i in range(len(titList)):# 12个月份
# 获取第一个月份的星期初始坐标
titcoorList = yearCoorDic]
# 设置应用月份标题的高度
first_row = sheet.row(titcoorList-1)
first_row.set_style(month_style)
sheet.write_merge(titcoorList-1, titcoorList-1, titcoorList, titcoorList[-1], '{}月'.format(i+1), titleStyle)# 合并单元格并写入月份
# 根据坐标写入星期标题
for j in range(0,len(titcoorList)):
# 设置应用星期标题宽度
first_col = sheet.col(titcoorList)
first_col.width = 95 * 25
# 设置应用星期标题的高度
first_row = sheet.row(titcoorList)
first_row.set_style(week_style)
if j+1 == 1:
title = '星期一'
elif j+1 == 2:
title = '星期二'
elif j+1 == 3:
title = '星期三'
elif j+1 == 4:
title = '星期四'
elif j+1 == 5:
title = '星期五'
elif j+1 == 6:
title = '星期六'
else:
title = '星期日'
sheet.write(titcoorList, titcoorList, title , styleBlack if j+1<6 else styleRed)
# 初始每个星期横坐标初始值
oneNum,twoNum,threeNum,fourNum,fiveNum,sixNum,sevenNum = 1,1,1,1,1,1,1 # 总觉得有简化的方法 这样写感觉好傻-.-
# 获取第一个月份数据的键值列表
daykeyList = list(yearDic].keys())
for k in range(len(daykeyList)): # 每个月的日期
dayList = yearDic]] # 获取每日的列表值['2','01','元旦']第一个值为星期几，第二个为日期，第三个为农历或节假日
# 判断每个月第一天为星期几，若为星期二，则把星期一的横坐标初始值初始为3，即星期一为空，以此类推
if k == 0:
if dayList==2:
oneNum = 3
if dayList==3:
oneNum,twoNum = 3,3
if dayList==4:
oneNum,twoNum,threeNum = 3,3,3
if dayList==5:
oneNum,twoNum,threeNum,fourNum= 3,3,3,3
if dayList==6:
oneNum,twoNum,threeNum,fourNum,fiveNum= 3,3,3,3,3
if dayList==7:
oneNum,twoNum,threeNum,fourNum,fiveNum,sixNum= 3,3,3,3,3,3
# 判断日期是星期几，执行对应语句
if 1 == dayList:
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + oneNum)
first_row.set_style(Content_style)
# 写入日期，日期横坐标的值为初始星期标题坐标值+oneNum初始值
sheet.write(titcoorList + oneNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack)
# 初始值+1 为下个农历节假日的初始值
oneNum+=1
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + oneNum)
first_row.set_style(Content_style)
# 写入农历或节假日 初始星期标题+oneNum初始值
sheet.write(titcoorList + oneNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack1_ if len(dayList)>4 else styleBlack_)
oneNum+=1
if 2 == dayList:
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + twoNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + twoNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack)
twoNum+=1
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + twoNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + twoNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack1_ if len(dayList)>4 else styleBlack_)
twoNum+=1
if 3 == dayList:
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + threeNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + threeNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack)
threeNum+=1
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + threeNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + threeNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack1_ if len(dayList)>4 else styleBlack_)
threeNum+=1
if 4 == dayList:
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + fourNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + fourNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack)
fourNum+=1
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + fourNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + fourNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack1_ if len(dayList)>4 else styleBlack_)
fourNum+=1
if 5 == dayList:
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + fiveNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + fiveNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack)
fiveNum+=1
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + fiveNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + fiveNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack1_ if len(dayList)>4 else styleBlack_)
fiveNum+=1
if 6 == dayList:
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + sixNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + sixNum, titcoorList, dayList, styleRed)
sixNum+=1
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + sixNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + sixNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack1_ if len(dayList)>4 else styleBlack_)
sixNum+=1
if 7 == dayList:
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + sevenNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + sevenNum, titcoorList, dayList, styleRed)
sevenNum+=1
# 设置应用单元格的高度
first_row = sheet.row(titcoorList + sevenNum)
first_row.set_style(Content_style)
sheet.write(titcoorList + sevenNum, titcoorList, dayList, styleRed if '休' in dayList elsestyleBlack1_ if len(dayList)>4 else styleBlack_)
sevenNum+=1
book.save('%s年日历表.xls'%year)
print('保存成功,程序执行完毕...')
template()
表格截图~
评论看到有人要成品的，我把成品上传上来了，有需要的下载解压就可以了
楼主，代码运行不了，帮看看
File "/Users/a.py", line 7
yearDic = {}
^
SyntaxError: invalid syntax 瑞浩娱乐 发表于 2019-1-11 11:57
大佬，能教下怎么弄吗？有代码不知道怎么弄出来，刚好公司还没发日历，一下没日历感觉很不习惯，需要这个
你要的话我可以把生成好的模板给你 此贴值得收藏，楼主辛苦了！！！！！ 大佬，能教下怎么弄吗？有代码不知道怎么弄出来，刚好公司还没发日历，一下没日历感觉很不习惯，需要这个 Python功能好强呀。 朝夕忆浅 发表于 2019-1-11 11:59
你要的话我可以把生成好的模板给你
你好，我需要一份，能不能也发给我一份成品啊，谢谢！！ xiuyuan1314 发表于 2019-1-11 12:19
你好，我需要一份，能不能也发给我一份成品啊，谢谢！！
成品上传到帖子里了，下载就好了 这个有点意思 这个就厉害了 辛苦了楼主。