Superset导出pivot_table到excel

最近工作上有个新需求就是按照交叉表在superset上显示的数据原样导出到Excel文档，目前最新版本的superset不支持导出交叉表格式的数据，任何导出的表都是基本的二维表格的csv,刚好最近在学习pandas这个工具。阅读文档可以看到导出提供的接口：
Pivot_table接口如下：

提供了to_excel接口可以导出支持二进制格式的xlsx文件：

 

 根据接口参数，很容易就导出交叉表原样的excel文件。

 

接着上文，优化导出的表格样式，就是调用python的openpyxl库，对表格的表头做冻结，还有对交叉表的列做冻结。
直接上代码：

#定义表格样式
fill_header = PatternFill(fill_type=fills.fills[0],
                         start_color = '0000BFFF',#'0066CDAA',#'0020B2AA',#colors.BLUE,
                         end_color = 'FF000000')

fill_body = PatternFill(fill_type=fills.fills[0],
                        start_color='00B0E0E6',
                        end_color='FF000000')

col_alignment = Alignment(horizontal='center',
                     vertical='center',
                     text_rotation=0,
                     wrap_text=True,
                     shrink_to_fit=True,
                     indent=0)

row_alignment = Alignment(horizontal='left',
                     vertical='center',
                     text_rotation=0,
                     wrap_text=False,
                     shrink_to_fit=True, #缩放文字内容
                     indent=0)

border = Border(left=Side(style='thin',color='FF0000FF'),
                right=Side(style='thin',color='FF000000'),
                top=Side(style='thin',color='FF000000'),
                bottom=Side(style='thin',color='FF000000'),
                )
def style_custom(filename, rows, columns, values='', sheet='Sheet1', come_from='pivot'):
#     print(rows, columns, values, come_from)
    if come_from == 'pivot':
        rows = len(rows)           #header列数
        columns = len(columns) + 1 #header行数,metrics加上去
        values = len(values)
    else:
        rows = columns= values = 1 
        
    wb = open_excel(filename)
    sheet = wb.get_sheet_by_name(sheet)
    print(sheet.max_row, sheet.max_column)
    max_row, max_column = sheet.max_row, sheet.max_column
     
    for col in range(rows, max_column+1):
        for row in range(1, columns+1):
            coordinate = '{0}{1}'.format(get_column_letter(col), row)
            sh = sheet[coordinate]
#             if sh.value:
            sh.fill = fill_header
            sh.alignment = col_alignment
            sh.border = border
                
    for row in range(columns, max_row+1):
        for col in range(1, rows+1):
            col = get_column_letter(col)
            coordinate = '{0}{1}'.format(col, row)
            sh = sheet[coordinate]
#             print(sh.value, type(sh.value))
#             if sh.value: #and isinstance(sh.value, (int,long, basestring)):
            sh.fill = fill_header
            sh.alignment = row_alignment
            sh.border = border
    
    for col in range(rows+1, max_column+1):
        col = get_column_letter(col)
        for row in range(columns+1, max_row+1):
            if row%2 == 0:
                coordinate = '{0}{1}'.format(col, row)
                sh = sheet[coordinate]
                sh.fill = fill_body
                pass
    try:
#         sheet.freeze_panes = '{}{}'.format(get_column_letter(columns), rows+2)
        if come_from == 'pivot':
            if values == 1 and columns == 2:
                A, B = get_column_letter(columns + rows - 1), rows + 2
            else:
                if rows == 1 and columns == 2:
                    A, B = get_column_letter(columns + rows - 1), rows + 2
                elif columns == 2:
                    A = get_column_letter(columns + rows - 1)
                    B = rows + 1
                else:
                    A, B = get_column_letter(columns + rows - 2), rows+1
                    if rows == 1:
                        B += 2
        else:
            A, B = "A", "2"
        sheet.freeze_panes = '{}{}'.format(A, B) #(columns， rows)做冻结表头用
        wb.save(filename)
    except IOError as e:
        print("IOError ******************** :%s"%e)

 来张pivot_table导出效果图：