python 列表写入excel_[python]获取一年日历数据并写入excel表格中

[Asm] 纯文本查看 复制代码# 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[i], 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[0].get_text()

nStr = n[0].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[gStr] = dayList

yearDic[i+1] = 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[i] = 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[titList[i]]

# 设置应用月份标题的高度

first_row = sheet.row(titcoorList[0][0]-1)

first_row.set_style(month_style)

sheet.write_merge(titcoorList[0][0]-1, titcoorList[0][0]-1, titcoorList[0][1], titcoorList[-1][1], '{}月'.format(i+1), titleStyle) # 合并单元格并写入月份

# 根据坐标写入星期标题

for j in range(0,len(titcoorList)):

# 设置应用星期标题宽度

first_col = sheet.col(titcoorList[j][1])

first_col.width = 95 * 25

# 设置应用星期标题的高度

first_row = sheet.row(titcoorList[j][0])

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[j][0], titcoorList[j][1], 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[titList[i]].keys())

for k in range(len(daykeyList)): # 每个月的日期

dayList = yearDic[titList[i]][daykeyList[k]] # 获取每日的列表值['2','01','元旦'] 第一个值为星期几，第二个为日期，第三个为农历或节假日

# 判断每个月第一天为星期几，若为星期二，则把星期一的横坐标初始值初始为3，即星期一为空，以此类推

if k == 0:

if dayList[0]==2:

oneNum = 3

if dayList[0]==3:

oneNum,twoNum = 3,3

if dayList[0]==4:

oneNum,twoNum,threeNum = 3,3,3

if dayList[0]==5:

oneNum,twoNum,threeNum,fourNum= 3,3,3,3

if dayList[0]==6:

oneNum,twoNum,threeNum,fourNum,fiveNum= 3,3,3,3,3

if dayList[0]==7:

oneNum,twoNum,threeNum,fourNum,fiveNum,sixNum= 3,3,3,3,3,3

# 判断日期是星期几，执行对应语句

if 1 == dayList[0]:

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[0][0] + oneNum)

first_row.set_style(Content_style)

# 写入日期，日期横坐标的值为初始星期标题坐标值+oneNum初始值

sheet.write(titcoorList[0][0] + oneNum, titcoorList[0][1], dayList[1], styleRed if '休' in dayList[2] else styleBlack)

# 初始值+1 为下个农历节假日的初始值

oneNum+=1

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[0][0] + oneNum)

first_row.set_style(Content_style)

# 写入农历或节假日 初始星期标题+oneNum初始值

sheet.write(titcoorList[0][0] + oneNum, titcoorList[0][1], dayList[2], styleRed if '休' in dayList[2] else styleBlack1_ if len(dayList[2])>4 else styleBlack_)

oneNum+=1

if 2 == dayList[0]:

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[1][0] + twoNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[1][0] + twoNum, titcoorList[1][1], dayList[1], styleRed if '休' in dayList[2] else styleBlack)

twoNum+=1

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[1][0] + twoNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[1][0] + twoNum, titcoorList[1][1], dayList[2], styleRed if '休' in dayList[2] else styleBlack1_ if len(dayList[2])>4 else styleBlack_)

twoNum+=1

if 3 == dayList[0]:

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[2][0] + threeNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[2][0] + threeNum, titcoorList[2][1], dayList[1], styleRed if '休' in dayList[2] else styleBlack)

threeNum+=1

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[2][0] + threeNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[2][0] + threeNum, titcoorList[2][1], dayList[2], styleRed if '休' in dayList[2] else styleBlack1_ if len(dayList[2])>4 else styleBlack_)

threeNum+=1

if 4 == dayList[0]:

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[3][0] + fourNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[3][0] + fourNum, titcoorList[3][1], dayList[1], styleRed if '休' in dayList[2] else styleBlack)

fourNum+=1

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[3][0] + fourNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[3][0] + fourNum, titcoorList[3][1], dayList[2], styleRed if '休' in dayList[2] else styleBlack1_ if len(dayList[2])>4 else styleBlack_)

fourNum+=1

if 5 == dayList[0]:

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[4][0] + fiveNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[4][0] + fiveNum, titcoorList[4][1], dayList[1], styleRed if '休' in dayList[2] else styleBlack)

fiveNum+=1

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[4][0] + fiveNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[4][0] + fiveNum, titcoorList[4][1], dayList[2], styleRed if '休' in dayList[2] else styleBlack1_ if len(dayList[2])>4 else styleBlack_)

fiveNum+=1

if 6 == dayList[0]:

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[5][0] + sixNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[5][0] + sixNum, titcoorList[5][1], dayList[1], styleRed)

sixNum+=1

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[5][0] + sixNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[5][0] + sixNum, titcoorList[5][1], dayList[2], styleRed if '休' in dayList[2] else styleBlack1_ if len(dayList[2])>4 else styleBlack_)

sixNum+=1

if 7 == dayList[0]:

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[6][0] + sevenNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[6][0] + sevenNum, titcoorList[6][1], dayList[1], styleRed)

sevenNum+=1

# 设置应用单元格的高度

first_row = sheet.row(titcoorList[6][0] + sevenNum)

first_row.set_style(Content_style)

sheet.write(titcoorList[6][0] + sevenNum, titcoorList[6][1], dayList[2], styleRed if '休' in dayList[2] else styleBlack1_ if len(dayList[2])>4 else styleBlack_)

sevenNum+=1

book.save('%s年日历表.xls'%year)

print('保存成功,程序执行完毕...')

template()