django 导出xls文件

django 导出xls文件

1.同目录下创建file_handle.py文件

file_handle.py

import xlwt, datetime
from xlwt import *
import xlsxwriter


# 写入excel文件函数
def wite_to_excel(n, head_data, records, download_url, table_name):
    # 获取时间戳
    timestr = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    # 工作表
    wbk = xlwt.Workbook(style_compression=2, encoding='utf-8')
    sheet1 = wbk.add_sheet('sheet1', cell_overwrite_ok=True)

    # 写入表头
    for filed in range(0, len(head_data)):
        sheet1.write(0, filed, head_data[filed], excel_head_style())

    # 写入数据记录
    for row in range(1, n + 1):
        for col in range(0, len(head_data)):
            sheet1.write(row, col, records[row - 1][col], excel_record_style())
            # 设置默认单元格宽度
            sheet1.col(col).width = 256 * 15

    wbk.save(download_url + table_name + '.xls')
    return timestr


# 写入excel文件函数
def wite_to_excel_text(n, head_data, records, download_url, table_name):
    # 获取时间戳
    timestr = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    # 工作表
    wbk = xlsxwriter.Workbook(download_url + table_name + '.xls')
    sheet1 = wbk.add_worksheet('sheet1')

    # 写入表头
    for filed in range(0, len(head_data)):
        sheet1.write(0, filed, head_data[filed])

    # 写入数据记录
    for row in range(1, n + 1):
        for col in range(0, len(head_data)):
            sheet1.write(row, col, records[row - 1][col])
            # 设置默认单元格宽度
            # sheet1.col(col).width = 256 * 15

    # wbk.save(download_url + table_name + '.xls')
    wbk.close()
    return timestr


# 定义导出文件表头格式
def excel_head_style():
    # 创建一个样式
    style = XFStyle()
    # 设置背景色
    pattern = Pattern()
    pattern.pattern = Pattern.SOLID_PATTERN
    pattern.pattern_fore_colour = Style.colour_map['light_green']  # 设置单元格背景色
    style.pattern = pattern
    # 设置字体
    font0 = xlwt.Font()
    font0.name = u'微软雅黑'
    font0.bold = True
    font0.colour_index = 0
    font0.height = 240
    style.font = font0
    # 设置文字位置
    alignment = xlwt.Alignment()  # 设置字体在单元格的位置
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
    alignment.vert = xlwt.Alignment.VERT_CENTER  # 竖直方向
    style.alignment = alignment
    # 设置边框
    borders = xlwt.Borders()  # Create borders
    borders.left = xlwt.Borders.THIN  # 添加边框-虚线边框
    borders.right = xlwt.Borders.THIN  # 添加边框-虚线边框
    borders.top = xlwt.Borders.THIN  # 添加边框-虚线边框
    borders.bottom = xlwt.Borders.THIN  # 添加边框-虚线边框
    style.borders = borders

    return style


# 定义导出文件记录格式
def excel_record_style():
    # 创建一个样式
    style = XFStyle()
    # 设置字体
    font0 = xlwt.Font()
    font0.name = u'微软雅黑'
    font0.bold = False
    font0.colour_index = 0
    font0.height = 200
    style.font = font0
    # 设置文字位置
    alignment = xlwt.Alignment()  # 设置字体在单元格的位置
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
    alignment.vert = xlwt.Alignment.VERT_CENTER  # 竖直方向
    style.alignment = alignment
    # 设置边框
    borders = xlwt.Borders()  # Create borders
    borders.left = xlwt.Borders.THIN  # 添加边框-虚线边框
    borders.right = xlwt.Borders.THIN  # 添加边框-虚线边框
    borders.top = xlwt.Borders.THIN  # 添加边框-虚线边框
    borders.bottom = xlwt.Borders.THIN  # 添加边框-虚线边框
    style.borders = borders

    return style

2.views.py

class ExportExelc(APIView):
    # authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication)
    # permission_classes = (IsAuthenticated,)
    def post(self, request, *args, **kwargs):
        '''导出数据'''
        table_name = request.query_params['table_name']
        # 表头字段
        # records表示要添加的数据
        # 写入备注
        head_data = []
        records = []
        record = []
        queryset = get_query_exprot(connection, table_name, DATABASES['default']['NAME'])
        for sql_info in queryset:
            head_data.append(sql_info['COLUMN_NAME'])  # 设置标题字段
            record.append(sql_info['COLUMN_COMMENT'])  # 设置标题字段
        records.append(record)
        cursor = connection.cursor()
        page = 0
        page_size = 1000
        while True:
            start = page * page_size
            page += 1
            num = cursor.execute(f'select * from {table_name} limit {start},{page_size};')
            if not num:
                break
            for i in cursor.fetchall():
                record = []
                for j in i:
                    record.append(str(j))
                records.append(record)
        n = len(records)
        # 设置生成文件所在路径
        download_url = os.path.dirname(os.path.abspath(__file__)) + '\\export_excel\\'
        # excel文件名称
        table_name = table_name.replace('database_data_', '')
        # 写入数据到excel中
        # wite_to_excel(n, head_data, records, download_url, table_name)
        wite_to_excel_text(n, head_data, records, download_url, table_name)
        from django.http import StreamingHttpResponse

        def file_iterator(file_name, chunk_size=512):
            with open(file_name, 'rb') as f:
                while True:
                    c = f.read(chunk_size)
                    if c:
                        yield c
                    else:
                        break

        # 显示在弹出对话框中的默认的下载文件名
        the_file_name = table_name + '.xls'

        # 设置生成文件所在路径
        download_url = download_url

        response = StreamingHttpResponse(file_iterator(download_url + the_file_name))
        response['Content-Type'] = 'application/octet-stream'
        response['Content-Disposition'] = 'attachment;filename="{0}"'.format(the_file_name)

        return response

3.query

def get_query_exprot(connection, table, key):
    """
    表的结构和数据
    :param connection:
    :param table:
    :return:
    """
    cursor = connection.cursor()
    cursor_columns = connection.cursor()
    sql = f"select * from information_schema.columns where table_schema ='{key}' and table_name = '{table}';"
    data_nums = cursor.execute(sql)
    if data_nums:
        cursor_columns.execute("show columns from information_schema.columns")
        columns = [i[0] for i in cursor_columns.fetchall()]
        queryset = [{c: str(j) for c, j in zip(columns, i)} for i in cursor.fetchall()]
        return queryset

4.在xls文件上追加数据

import xlrd
from xlutils.copy import copy
workbook = xlrd.open_workbook(file_name, formatting_info=True)  # 读取xls文件
write_data = copy(workbook)
sheet_data = write_data.get_sheet(0)
sheet_data.write(i, 12, 'succeed')   # i是行    12 是列   'succeed' 追加要写入的数据
write_data.save('.\\operations.xls')