Python 导出 excel 表格2024-06-14 10:26:15
import xlwtimport iodef 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)
标签: excel如何导出表格