python 脚本导出excel

涉及以下功能：

1. 使用python脚本单独操作Django的ORM

2. 使用xlwt将导出excel

3. 设置表格样式

4. 一次性创建多个sheet表

5. xlwt合并单元格，并居中

# -*- coding: utf-8 -*-
"""
将course下的课时全部导出到excel表中， 每一个分类导出一个sheet, 包含是否有讲义， 视频， pdf，附件，

sheet 表头包含字段：
课程方向（课程类别）    课程名称    课程章节  章节类型    讲义-html     讲义-markdown     视频  pdf 附件
"""

import os
import sys
from django.core.wsgi import get_wsgi_application

sys.path.extend(['/home/zhan/youpath', ])
os.environ.setdefault("DJANGO_SETTINGS_MODULE", "oj.settings")
application = get_wsgi_application()
# 以上代码可以让python直接接入django的ORM进行操作，修改为自己的django项目地址路径， 有虚拟环境的需要在虚拟环境下面执行脚本

#以下代码是使用django数据库进行操作导出excel，导出的文件在当前执行代码下面的文件夹下



import xlwt
from course.models import Course, Lesson


class ExportCourse(object):
    ishas = u'有'
    SUCCESS = public = status = 1

    def __init__(self, excel_name=None):
        # 初始化
        self.excel = xlwt.Workbook()
        if not excel_name:
            excel_name = 'export_course.xls'
        self.excel_name = excel_name
        self.header = [u'课程方向', u'课程名称', u'课程章节', u'章节类型', u'讲义-html', u'讲义-markdown', u'视频', u'pdf', u'附件']

    def sheet_head(self, sheet):
        # 添加sheet表头
        for index, each_header in enumerate(self.header):
            sheet.write(0, index, each_header)
        return sheet

    def sheet_body(self, sheet, course_direct_id):
        # 添加sheet表数据
        lessons = Lesson.objects.filter(course__sub_direction_id=course_direct_id,
                                        course__public=self.public,
                                        course__status=self.status,
                                        public=self.public,
                                        status=self.status).order_by('course_id')
        print(lessons.count())
        if not lessons.count() > 0:
            print('can not find lesson!!!')
            return sheet

        row = 1
        direct_name = lessons[0].course.direction.cn_name
        style = self.sheet_merge_style()
        merge_cell_first = {}
        merge_cell_last = {}
        for lesson in lessons:
            lesson_dict = self.get_lesson_detail(lesson)
            for index, header in enumerate(self.header):
                lesson_value = lesson_dict.get(self.header[index])
                sheet.write(row, index, lesson_value)
                if index == 1:
                    if not merge_cell_first.has_key(lesson_value):
                        merge_cell_first[lesson_value] = row
                    merge_cell_last[lesson_value] = row

            row += 1

        # 合并单元格
        for course_name, value in merge_cell_first.items():
            from_row = merge_cell_first.get(course_name)
            to_row = merge_cell_last.get(course_name)
            sheet = self.sheet_merge_cell(sheet, from_row, to_row, 1, course_name, direct_name,  style)

        return sheet

    def sheet_merge_cell(self, sheet, from_row, to_row, col, course_name, direct_name, style):
        # 合并单元格， 我这里和合并了两列， 并居中
        sheet.write_merge(from_row, to_row, col, col, course_name, style)
        sheet.write_merge(from_row, to_row, col-1, col-1, direct_name, style)
        return sheet

    def create_sheet(self, name):
        # 创建一个sheet表
        sheet = self.excel.add_sheet(name, cell_overwrite_ok=True)
        return sheet

    def sheet_col_width(self, sheet, index=3, width=256 * 25):
        # 设置前几列的宽度
        for i in range(index):
            sheet.col(i).width = width
        return sheet

    def sheet_merge_style(self):
        # 设置sheet表样式
        style = xlwt.XFStyle()
        alignment = xlwt.Alignment()
        alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平居中
        alignment.vert = xlwt.Alignment.VERT_CENTER  #垂直居中
        style.alignment =alignment
        return style

    def get_lesson_detail(self, lesson):
        # 获取lesson对象详情
        lesson_dict = {}
        lesson_dict[self.header[0]] = lesson.course.direction.cn_name
        lesson_dict[self.header[1]] = lesson.course.name
        lesson_dict[self.header[2]] = lesson.name
        lesson_dict[self.header[3]] = self.get_lesson_type(lesson)
        lesson_dict[self.header[4]] = self.get_lesson_key(lesson, 'html')
        lesson_dict[self.header[5]] = self.get_lesson_key(lesson, 'markdown')
        lesson_dict[self.header[6]] = self.get_lesson_video(lesson)
        lesson_dict[self.header[7]] = self.get_lesson_key(lesson, 'pdf')
        lesson_dict[self.header[8]] = self.get_lesson_key(lesson, 'attachment')
        return lesson_dict

    def get_lesson_type(self, lesson):
        if lesson.type == 0:
            return u'理论'
        elif lesson.type == 1:
            return u'实验'
        else:
            return ''

    def get_lesson_video(self, lesson):
        if lesson.video_state == self.SUCCESS:
            return self.ishas
        elif lesson.video:
            return self.ishas
        return ''

    def get_lesson_key(self, lesson, key):
        value = hasattr(lesson, key)
        return value and self.ishas or ''

    def get_course_direct(self):
        # 获取Course详情， 分类ids
        sub_direction_ids = Course.objects.filter(status=self.status).values_list('sub_direction', 'sub_direction__cn_name')
        sub_direction_ids = dict(sub_direction_ids)
        return sub_direction_ids

    def run(self):
        # 执行逻辑
        direct_dict = self.get_course_direct()
        for k, v in direct_dict.items():
            if not k:
                continue

            create_sheet = self.create_sheet(v)
            sheet_add_width = self.sheet_col_width(create_sheet)
            sheet_head = self.sheet_head(sheet_add_width)
            self.sheet_body(sheet_head, k)

            print('sheet {}-{} is done'.format(k, v))
        # 保存
        self.excel.save(self.excel_name)


if __name__ == '__main__':
    export_course = ExportCourse(u'自定命名.xls')
    export_course.run()