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

这个主要是python操作Excel表格，写入数据的使用

创建导出excel视图

按照月份导出excel，也可以清空导出的文件

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

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'),  # 清空导出的文件
]

主页导出按钮

            <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)