Django1.1 + xlwt(Workbook) 实现数据库导出excel

1、自定义类，实现多个表导出(后续有时间再优化)

import time

import xlwt
from xlwt import Workbook
from io import BytesIO

from utils.constant import Flag_Order1_Code, Flag_Order2_Code


class ExportBase(object):
    """ Excel 表头字段 """
    config_excel_title = {
        'Order1': (u'Create Time', u'OrderNo', u'Name', u'Operator'),
        'Order2': (u'Create Time', u'OrderNo', u'Name', u'Operator')
    }

    """ Excel 样式 """
    width = 256 * 25  # 行宽
    height = xlwt.easyxf('font:height 500')  # 行高

    # 设置居中
    alignment = xlwt.Alignment()
    # alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
    alignment.vert = xlwt.Alignment.VERT_CENTER  # 垂直方向

    # 定义不同的excel style
    align_style = xlwt.XFStyle()
    align_style.alignment = alignment
    """ Excel 样式 """

    def __init__(self, obj, code, time_diff=0, sort_field=None, base_iso_code=None, **kwargs):
        self.obj = obj  # model class
        self.code = code  # model class 配置代码
        self.time_diff = time_diff  # 与 UTC 时间差
        self.sort_field = sort_field  # 排序字段
        self.base_iso_code = base_iso_code  # users currency
        self.kwargs = kwargs  # 查询字段

    @staticmethod
    def set_align():
        # 设置居中
        alignment = xlwt.Alignment()
        # alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
        alignment.vert = xlwt.Alignment.VERT_CENTER  # 垂直方向

        # 定义不同的excel style
        style = xlwt.XFStyle()
        style.alignment = alignment
        return style

    def datetime_to_str(self, date, date_format='%Y%m%d'):
        try:
            time_stamp = int(time.mktime(date.timetuple())) - self.time_diff
            date_str = time.strftime(date_format, time.localtime(time_stamp))  # date.strftime(date_format)
        except Exception as e:
            date_str = str(e)
        return date_str

    def filter_and(self):
        """ 返回 符合查询条件的 model 的所有数据 """
        results = self.obj.objects.filter(**self.kwargs)
        return results.order_by(self.sort_field) if self.sort_field else results

    def get_output(self):

        work_book = Workbook(encoding='utf-8')  # 创建工作簿

        # 添加第一页数据表
        sheet = work_book.add_sheet(u"Sheet1")

        excel_title = self.config_excel_title[self.code]
        # 写入表头
        for i, title in enumerate(excel_title):
            sheet.col(i).width = self.width  # 行宽
            sheet.row(i).set_style(self.height)  # 行高
            sheet.write(0, i, title, style=self.align_style)
        if len(excel_title) > 0:
            work_book = self.write(work_book, sheet)

        # 实现下载
        output = BytesIO()
        work_book.save(output)
        output.seek(0)
        return output

    def write(self, work_book, sheet):

        obj_list = self.filter_and()
        # 写入数据
        excel_row = 1
        for obj in obj_list:
            if self.code == Flag_Order1_Code:
                """ Order1 表 """
                # 写入每一行对应的数据
                sheet.write(excel_row, 0, self.datetime_to_str(obj.create_time, '%Y-%m-%d %H:%M:%S'),
                            style=self.align_style)
                sheet.write(excel_row, 1, obj.order_no, style=self.align_style)
                sheet.write(excel_row, 2, obj.name, style=self.align_style)
                sheet.write(excel_row, 3, obj.operator, style=self.align_style)

            elif self.code == Flag_Order2_Code:
                """ Order2 表 """
                # 写入每一行对应的数据
                sheet.write(excel_row, 0, self.datetime_to_str(obj.create_time, '%Y-%m-%d %H:%M:%S'),
                            style=self.align_style)
                sheet.write(excel_row, 1, obj.order_no, style=self.align_style)
                sheet.write(excel_row, 2, obj.name, style=self.align_style)
                sheet.write(excel_row, 3, obj.operator, style=self.align_style)

            sheet.row(excel_row).set_style(self.height)  # 设置行高
            excel_row += 1

        return work_book

2、调用

def get(self, request):
    """
        1、前端发送get请求，参数 code, 和上面提到的 Order1， Order2 对应
    """
    time_diff = int(request.GET.get('time_diff', '0'))
    date_from = str_to_datetime(request.GET.get('from'), time_diff)  # 把 前端传过来的时间转换成 datetime
    date_to = str_to_datetime(request.GET.get('to'), time_diff)
    code = request.GET.get('code')

    config_query = {
        'Order1': {
            'model': Order1,
            'field': {
                'user_id': request.user.pk,
                'category_id': 23,
            },
            'order_by': 'create_time'
        },
        'Order2': {
            'model': Order2,
            'field': {
                'user_id': request.user.pk,
                'category_id': 32,
            },
            'order_by': '-order__create_time'
        }
    }

    if date_from and date_to:
        config_query['Order1']['field']['create_time__range'] = date_from, date_to + timedelta(days=1)
        config_query['Order2']['field']['create_time__range'] = date_from, date_to + timedelta(days=1)


    export_obj = ExportBase(config_query[code]['model'], code, time_diff, config_query[code]['order_by'],
                            base_iso_code='', **config_query[code]['field'])
    output = export_obj.get_output()

    response = StreamingHttpResponse(output)
    response['content_type'] = 'application/vnd.ms-excel'
    response['charset'] = 'utf-8'
    response['Content-Disposition'] = 'attachment; filename="{0}.xls"'.format(
        timezone.datetime.now().strftime('%Y%m%d%H%M'))
    return response