工作需要，需要做下载excel的功能，同时根据数据库的某个字段完成多sheet的功能。
由于用处较多，封装了一个函数。

add_worksheet: 为生成多个sheet的方法

from io import BytesIOimport xlsxwriterdef download_excel(cursor, sql_field_index=False, custom_headers=False, sheet_title="worksheet_1"): """ 表格下载 :param cursor: :param custom_headers: 自定义表头 :param sql_field_index: 把该字段数据分成多个sheet :param sheet_title: 单 sheet表，sheet名字 :return: """ x_io = BytesIO() work_book = xlsxwriter.Workbook(x_io) worksheet_dict = dict() worksheet_col_row = {} result_data = cursor.fetchall() field_names = custom_headers if custom_headers else [item.name for item in cursor.description] if sql_field_index: for row in result_data: file_name = row[sql_field_index] if file_name not in worksheet_dict.keys(): worksheet_dict[file_name] = work_book.add_worksheet(file_name) col = 0 for desc in field_names: for current_work_sheet in worksheet_dict.keys(): worksheet_dict[current_work_sheet].write(0, col, desc) worksheet_col_row[current_work_sheet] = [1, 0] col += 1 for colums in result_data: file_value = colums[sql_field_index] work_sheet = worksheet_dict[file_value] for index in range(0, col): current_row, current_col = worksheet_col_row[file_value] work_sheet.write(current_row, current_col, colums[index]) worksheet_col_row[file_value] = [current_row, current_col + 1] worksheet_col_row[file_value] = [current_row + 1, 0] else: worksheet = work_book.add_worksheet(sheet_title) col, row = 0, 0 for desc in field_names: worksheet.write(0, col, desc) col += 1 row, col = 1, 0 for temp in result_data: for value in temp: worksheet.write(row, col, value) col += 1 row += 1 col = 0 work_book.close() return x_io.getvalue()

views:

def write_excel(request): ...... precur = presto.connect(‘...‘).cursor() # 根据explain 字段生成多个worksheet，首选获取是sql查询参数中的第几个 sql_field_index = len(csv_hive_sql[title].split(‘explain‘)[0].split(‘,‘)) precur.execute(sql, (now_dt,)) custom_field_names = [csv_head_map.get(item[0]) for item in precur.description] result = download_excel(precur, sql_field_index, custom_field_names) res = HttpResponse() res["Content-Type"] = "application/octet-stream" res["Content-Disposition"] = ‘filename="userinfos.xlsx"‘ res.write(result) precur.close() return res

根据数据库某个字段生成了3个sheet: