【Schedule轮班排班】08.值班表导出功能

这个主要是python操作Excel表格，写入数据的使用 ## 创建导出excel视图 按照月份导出excel，也可以清空导出的文件 ```python def write_schedule_excel(filename, schedule, year, month): """ 将值班表写入Excel表格 :param filename: 带路径表格名称 :param schedule: 值班查询集 :param year: 导出的年份 :param month: 导出的月份 :return: 无返回值，直接写入就结束 """ if os.path.exists(filename): os.remove(filename) workbook = xlwt.Workbook(encoding='utf-8') sheet = workbook.add_sheet('排班表', cell_overwrite_ok=True) # 对一个单元格重复操作，会引发Attempt to overwrite cell: sheetname='排班表' rowx=5 colx=6，打开时加cell_overwrite_ok=True解决 sheet.col(0).width = 256 * 20 # 256为衡量单位，20表示20个字符宽度 sheet.col(1).width = 256 * 12 sheet.col(2).width = 256 * 20 sheet.col(3).width = 256 * 20 sheet.col(4).width = 256 * 20 head_style = xlwt.easyxf('font:height 220, bold on; align: wrap on, vert centre, horiz center;') # font:height 220, bold on; # 字体大小，粗体开 # align: wrap on, vert centre, horiz center; # 位置：自动换行， 水平居中，上下居中 text_style = xlwt.easyxf('font:height 220; align: wrap on, vert centre, horiz center;') saturday_style = xlwt.easyxf('font:height 220; align: wrap on, vert centre, horiz center; pattern: pattern solid, fore_colour gray25') head = ['日期', '星期', '工作日', '周末', '换班记录'] for i in range(len(head)): sheet.write(0, i, head[i], head_style) count = schedule.count() date_row = 1 for item in schedule: from .templatetags.duty_template_tags import get_week_day week = get_week_day(item.date) if item.duty_type == 2: sheet.write(date_row, 0, str(item.date), saturday_style) sheet.write(date_row, 1, str(week), saturday_style) else: sheet.write(date_row, 0, str(item.date), text_style) sheet.write(date_row, 1, str(week), text_style) if item.duty_type == 1 and item.staff: sheet.write(date_row, 2, str(item.staff), text_style) if item.duty_type == 2 and item.staff: sheet.write(date_row, 2, None, saturday_style) sheet.write(date_row, 3, str(item.staff), saturday_style) if item.real_staff: sheet.write(date_row, 4, str(item.real_staff), text_style) date_row += 1 # 右边显示换休日期 sheet.col(5).width = 256 * 1 # 分割线 sheet.col(6).width = 256 * 10 # 名字 sheet.col(7).width = 256 * 15 # 值班日期 sheet.col(8).width = 256 * 15 # 值班日期 sheet.col(9).width = 256 * 15 # 值班日期 sheet.col(10).width = 256 * 15 # 调休日期 sheet.col(11).width = 256 * 15 # 调休日期 sheet.col(12).width = 256 * 10 # 可调天数 # 中间的分割线 div_style = xlwt.easyxf('pattern: pattern solid, fore_colour gray80') for m in range(count + 1): sheet.write(m, 5, None, div_style) # 设置下边框 borders_bottom = xlwt.Borders() # 创建边框 # borders.left = xlwt.Borders.DASHED # DASHED虚线 # NO_LINE没有 # THIN实线 # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D. # borders.right = xlwt.Borders.DASHED # borders.top = xlwt.Borders.DASHED borders_bottom.bottom = xlwt.Borders.THIN # borders.left_colour = 0x40 # borders.right_colour = 0x40 # borders.top_colour = 0x40 borders_bottom.bottom_colour = 0x40 border_bottom_style = xlwt.XFStyle() # 创建风格 border_bottom_style.borders = borders_bottom # 添加边框到风格中 sheet.write_merge(12, 12, 6, 12, None, border_bottom_style) # 记录值班日期换休 ot_row = 13 overtime = ['名字', '值班日期', '值班日期', '值班日期', '调休日期', '调休日期', '可调天数'] for ot in range(len(overtime)): sheet.write(ot_row, 6 + ot, overtime[ot], head_style) ot_row += 1 for staff in Employee.objects.filter(available=True): name_col = 6 # 名字开始列 onduty_col = 7 # 值班日期开始列 tuneoff_col = 10 # 调休日期开始列 tuneoffnum_col = 12 # 可调天数开始列 sheet.write(ot_row, name_col, staff.name, text_style) # 填写人名 # 筛选出本月值班人员信息 filter_weenkend = Schedule.objects.filter(duty_type=2, date__year=year, date__month=month, staff__contains=staff.name) # 写入值班日期 for date_num in range(filter_weenkend.count()): # print(filter_weenkend.order_by('date')[date_num].date) sheet.write(ot_row, onduty_col, str(filter_weenkend.order_by('date')[date_num].date), text_style) onduty_col += 1 date_num += 1 # 写入可调休天数 sheet.write(ot_row, tuneoffnum_col, str(filter_weenkend.count() / 2) + '天', text_style) # 写入完成一个名字后，跳到下一行 ot_row += 1 # 设置上边框 borders_top = xlwt.Borders() # 创建边框 borders_top.top = xlwt.Borders.THIN borders_top.top_colour = 0x40 border_top_style = xlwt.XFStyle() # 创建风格 border_top_style.borders = borders_top # 添加边框到风格中 sheet.write_merge(ot_row, ot_row, 6, 12, None, border_top_style) # 写入说明 info = '''说明： 周例会两周一次，自行排序。 工作日值班同学需提前到公司，检查设备状态。 周末值班，每值一天班，换休半天，换休当月有效，过期作废。 ''' info_style = xlwt.easyxf('font:height 220; align: wrap on, vert centre, horiz left; pattern: pattern solid, fore_colour gray25') # 设置页尾 footer_style = xlwt.XFStyle() # 初始化样式 font = xlwt.Font() # 为样式创建字体 font.height = 300 font.name = '华文行楷' font.bold = True # 黑体 alignment = xlwt.Alignment() alignment.horz = xlwt.Alignment.HORZ_RIGHT # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED alignment.vert = xlwt.Alignment.VERT_CENTER # May be: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED footer_style.font = font footer_style.alignment = alignment sheet.write_merge(count + 2, count + 2, 0, 12, '由PXE提供导出', footer_style) sheet.write_merge(3, 7, 7, 11, info, info_style) # 合并单元格，3,7表示第4行到第8行，7,11表示第8列到第12列，区间表格合并 workbook.save(filename) def export_schedule(request): """ 执行导出的视图 :param request: :return: """ export_date = request.GET.get('export_date') year = export_date.split('-')[0] month = export_date.split('-')[1] month_schedule = Schedule.objects.filter(date__year=int(year), date__month=int(month)).order_by('date') # 执行导出函数 print(schedule_path) filename = schedule_path + year + '年' + month + '月' + '值班表（成都）' + '.xls' write_schedule_excel(filename, month_schedule, int(year), int(month)) return redirect(reverse('duty:schedule')) def empty_schedule_excel(request): """ 清空导出的文件夹 :param request: :return: """ schedule_excel = os.listdir(schedule_path) for excel in schedule_excel: print(excel) if os.path.exists(schedule_path + excel): os.remove(schedule_path + excel) # 删除该文件 return redirect(reverse('duty:schedule')) ``` ## 导出功能url ```python from .views import get_last_duty, export_schedule, empty_schedule_excel urlpatterns = [ path('export_schedule/', permission_required('usercenter.permission_user_manage', raise_exception=True)(export_schedule), name='export_schedule'), # 导出值班表 path('empty_schedule_excel/', permission_required('usercenter.permission_user_manage', raise_exception=True)(empty_schedule_excel), name='empty_schedule_excel'), # 清空导出的文件 ] ``` ## 主页导出按钮 ```html <div> <a class="btn btn-default btn-sm" href="{% url 'duty:export_schedule' %}?export_date={{ date0 }}">{{ date0v }}</a> <a class="btn btn-primary btn-sm" href="{% url 'duty:export_schedule' %}?export_date={{ date1 }}">{{ date1v }}</a> <a class="btn btn-default btn-sm" href="{% url 'duty:export_schedule' %}?export_date={{ date2 }}">{{ date2v }}</a> <a class="btn btn-default btn-sm" href="{% url 'duty:export_schedule' %}?export_date={{ date3 }}">{{ date3v }}</a> <a class="btn btn-warning btn-sm" href="{% url 'duty:empty_schedule_excel' %}">清空导出</a> </div> <div> {% for excel in schedule_excel %} <a href="{{ schedule_excel_url }}{{ excel }}" download="{{ excel }}" class="link_button btn btn-info">下载{{ excel }}</a> {% empty %} {% endfor %} </div> ```  选择一个月份导出，例如12月  下载使用excel打开如下 

明人不说暗话 领红包就是支持！！！ 来都来了，再到页面底部看下呗~ ↓ ↓ ↓

本文地址： http://blog.starmeow.cn/detail/6974ce5ac660610b44d9b9fed0ff9548/

很赞哦！ (2)