Python 导出 excel 表格

Python 导出 excel 表格

import xlwt
import io
def write_data_to_excel(sql_data):
    # 实例化一个Workbook()对象(即 excel 文件)
    wbk = xlwt.Workbook()
    # 由于输入数据为无序字典，需要指定导出表格的列顺序
    directory_deepth = max(map(lambda x: len(x.get('full_path')), sql_data))
    column_list = ['the 1st directory', 'the 2nd directory', 'the 3rd directory']
    if directory_deepth <=3:
        column_format_list = column_list[:directory_deepth]
    else:
        column_format_list = column_list+[("the %sth directory" % s) for s in range(4, directory_deepth+1)]
    column_format_list += ['aw_name', 'remark', 'aw_owner', 'created_time', 'models']
    for i in range(len(sql_data)):
        sql_data[i]['full_path'] += ['']*(directory_deepth-len(sql_data[i]['full_path']))
    # 新建一个名为Sheet1的excel sheet。此处的cell_overwrite_ok =True是为了能对同一个单元格重复操作。
    sheet = wbk.add_sheet('Sheet1', cell_overwrite_ok=True)
    for title_num in xrange(len(column_format_list)):
        sheet.write(0, title_num, column_format_list[title_num])
    for i in xrange(1,len(sql_data)+1):
        for j in xrange(len(column_format_list)):
            if j < directory_deepth:
                sheet.write(i, j, sql_data[i-1]['full_path'][j])
            else:
                sheet.write(i, j, sql_data[i-1][column_format_list[j]])
    bytes_data = io.BytesIO()
    #将excel文件对象写入一个ByteIO对象，然后读取二进制流返回给handler
    wbk.save(bytes_data)
    bytes_data.seek(0)
    return bytes_data.read()

response = write_data_to_excel(export_all_aw_data)
self.set_header('Content-Type', 'application/vnd.ms-excel')
self.write(response)