效果
在excel日历模板的基础上，生成带有农历日期、节假日、休班等信息的日历，解决diy日历最大的技术难题。
图中日期，第一行为公历，第二行为节假日，第三行为农历，第四行是其他特别的日子，比如生日、纪念日等。
特点
- 使用门槛低
python + excel，会运行python脚本，会使用excel即可上手。
- 步骤简单
只需要修改excel的年份（在一月份表头修改），运行一次脚本
- 可扩展
可制作任意年份的日历（修改年份即可）
- 可定制
可以添加其他特殊日期
使用手册
第一步，修改日历年份及样式
打开calendar.xlsx文件，在一月份表头，”输入年份“位置，修改样式
第二步，添加自定义日期
calendar.xlsx文件的生日栏，添加需要标注的日期，并保存
第三部，运行脚本
主要代码
bddatafetcher.py
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
#!/usr/bin/python3# -*- coding: utf-8 -*-import datetimeimport loggingimport timeimport requestsimport reimport jsonclass bddatafetcher(object): def __init__(self): self.url = 'https://sp0.baidu.com/8aqdcjqpaav3otqbppnn2djv/api.php' self.request_session = requests.session() self.request_session.headers = { "user-agent": "mozilla/5.0 (macintosh; intel mac os x 10_14_4) applewebkit/537.36 (khtml, like gecko) chrome/84.0.4147.135 safari/537.36", "accept": "application/json, text/plain, */*", "accept-encoding": "gzip, deflate", "accept-language": "zh-cn,zh;q=0.9,en;q=0.8", "connection": "keep-alive" } def request(self, year_month): payload = { 'query': year_month, 'resource_id': 39043, 't': int(round(time.time() * 1000)), 'ie': 'utf8', 'oe': 'utf8', 'cb': 'op_aladdin_callback', 'format': 'json', 'tn': 'wisetpl', 'cb': 'jquery110206747607329442493_1606743811595', '_': 1606743811613 } resp = self.request_session.get(url=self.url, params=payload) logging.debug('data fetcher resp = {}'.format(resp.text)) bracket_pattern = re.compile(r'[(](.*?)[)]', re.s) valid_data = re.findall(bracket_pattern, resp.text) json_data = json.loads(valid_data[0]) almanac = json_data['data'][0]['almanac'] result = {} for day in almanac: key = '{}-{}-{}'.format(day['year'], day['month'],day['day']) result[key] = day return resultif __name__ == '__main__': logging.basicconfig(level=logging.debug, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', datefmt='%a, %d %b %y %h:%m:%s') bddatafetcher().request('2021年1月') |
exceldatefiller.py
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
|
#!/usr/bin/python3# -*- coding: utf-8 -*-import loggingimport osimport sysfrom copy import copyimport openpyxlimport pandas as pandasimport xlrdimport xlutilsimport yamlfrom pandas._libs.tslibs.timestamps import timestampfrom bddatafetcher import bddatafetcherclass config(object): def __init__(self, config_path): try: with open(config_path, "r", encoding="utf-8") as yaml_file: data = yaml.load(yaml_file) self.excel_path = data['excel_path'] self.sheet_special = data['sheet_special'] self.skip_row = data['date_skip_row'] self.skip_col = data['date_skip_col'] self.max_length = data['max_length'] self.holiday_color = data['holiday_color'] self.workday_color = data['workday_color'] logging.basicconfig(level=logging.debug, format='%(asctime)s %(filename)s[line:%(lineno)d] %(levelname)s %(message)s', datefmt='%a, %d %b %y %h:%m:%s') except exception as e: logging.error(repr(e)) sys.exit()class specialday(object): def __init__(self): self.is_lunar = false self.desc = ''class exceldatefiller(object): def __init__(self): self.data_fetcher = bddatafetcher() self.target = os.path.splitext(config.excel_path)[0] + '_out' + os.path.splitext(config.excel_path)[-1] # try: # shutil.copy(config.excel_path, self.target) # except ioerror as e: # print("unable to copy file. %s" % e) # except: # print("unexpected error:", sys.exc_info()) # self.target_workbook = openpyxl.load_workbook(self.target, data_only=true) def fill_date_with_openpyxl(self): for sheet in self.target_workbook.worksheets: for column_index in range(1, sheet.max_column): for row_index in range(1, sheet.max_row): data = sheet.cell(column=column_index, row=row_index) print(data.value) def read_with_xlrd(self): workbook = xlrd.open_workbook(self.target) for sheet in workbook.sheets(): for column_index in range(0, sheet.ncols): for row_index in range(0, sheet.nrows): data = sheet.cell(rowx=row_index, colx=column_index) logging.debug('ctype = {}, value = {}, xf_index = {}'.format(data.ctype, data.value, data.xf_index)) def write_with_openpyxl(self): target_workbook = openpyxl.load_workbook(self.target) sheet = target_workbook.get_sheet_by_name('sheet_name') sheet.cell(0, 0).value = 'value' target_workbook.save() def write_with_xlwt(self): workbook = xlrd.open_workbook(self.target) workbook = xlutils.copy(workbook) sheet = workbook.get_sheet(0) sheet.write(0, 0, 'value') workbook.save() def load_special_sheet(self): data = {} special_sheet = pandas.read_excel(config.excel_path, sheet_name=config.sheet_special, header=0) for row_index in range(special_sheet.shape[0]): key = special_sheet.iloc[row_index, 0] struct_time = pandas.to_datetime(key.timestamp(), unit='s').timetuple() key = '{}-{}'.format(struct_time.tm_mon, struct_time.tm_mday) value = specialday() value.desc = special_sheet.iloc[row_index, 1] value.is_lunar = special_sheet.iloc[row_index, 2] == '是' data[key] = value return data def fill_date(self): pandas_workbook = pandas.read_excel(config.excel_path, sheet_name=none, skiprows= config.skip_row, keep_default_na=false) out_workbook = openpyxl.load_workbook(config.excel_path) special_day = self.load_special_sheet() day_data = {} for sheet_name in pandas_workbook.keys(): if not sheet_name.endswith('月'): continue sheet = pandas_workbook.get(sheet_name) out_sheet = out_workbook.get_sheet_by_name(sheet_name) nrows = sheet.shape[0] ncols = sheet.shape[1] for row_index in range(nrows): for col_index in range(ncols): data = sheet.iloc[row_index, col_index] logging.debug('origin row = {}, col = {}, data = {}'.format(row_index, col_index, data)) if type(data) == timestamp: struct_time = pandas.to_datetime(data.timestamp(), unit='s').timetuple() date = '{}-{}-{}'.format(struct_time.tm_year, struct_time.tm_mon, struct_time.tm_mday) if not day_data.__contains__(date): request_data = self.data_fetcher.request(year_month='{}年{}月'.format(struct_time.tm_year, struct_time.tm_mon)) day_data.update(request_data) temp_row = row_index + 2 + config.skip_row temp_col = col_index + 1 # weekend color if day_data[date]['cnday'] == '六' or day_data[date]['cnday'] == '日': holiday_font = copy(out_sheet.cell(temp_row, temp_col).font) holiday_font.color = config.holiday_color out_sheet.cell(temp_row, temp_col).font = holiday_font # holiday color if day_data[date].__contains__('status'): if day_data[date]['status'] == '1': # 休假 holiday_font = copy(out_sheet.cell(temp_row, temp_col).font) holiday_font.color = config.holiday_color out_sheet.cell(temp_row, temp_col).font = holiday_font if day_data[date]['status'] == '2': #班 workday_font = copy(out_sheet.cell(temp_row, temp_col).font) workday_font.color = config.workday_color out_sheet.cell(temp_row, temp_col).font = workday_font lunar_date = day_data[date]['ldate'] if lunar_date == '初一': lunar_date = '{}月'.format(day_data[date]['lmonth']) # logging.debug('date = {}, value = {}'.format(str(date), lunar_date)) temp_content = '' if day_data[date].__contains__('value'): temp_content += day_data[date]['value'] if len(temp_content) > config.max_length: temp_content = temp_content[:config.max_length] temp_content += '\n' temp_content += lunar_date # spacial day month_day = day_data[date]['month'] + '-' + day_data[date]['day'] if special_day.__contains__(month_day): temp_special_day = special_day.get(month_day) if not temp_special_day.is_lunar: temp_content += '\n' temp_content += temp_special_day.desc lunar_month_day = day_data[date]['lunarmonth'] + '-' + day_data[date]['lunardate'] if special_day.__contains__(lunar_month_day): temp_special_day = special_day.get(lunar_month_day) if temp_special_day.is_lunar: temp_content += '\n' temp_content += temp_special_day.desc temp_row = row_index + 3 + config.skip_row temp_col = col_index + 1 out_sheet.cell(temp_row, temp_col).value = temp_content out_workbook.save(filename=self.target) out_workbook.close()if __name__ == '__main__': config = config(config_path='config.yaml') date_filler = exceldatefiller() date_filler.fill_date() |
完整项目地址
https://github.com/yongjiliu/diycalendar
calendar_out.xlsx为处理好的日历
以上就是用python自动生成日历的详细内容，更多关于python 生成日历的资料请关注服务器之家其它相关文章！
原文链接：https://github.com/yongjiliu/diycalendar
