def _insert_compare_string_results(string_results, ws, row_height): """ Insert string result into a worksheet Parameters ---------- string_results: dict result to insert ws: Excel worksheet instance row_height: float Height of the row """ # construct thick border thin = Side(border_style="thin", color="000000") border = Border(top=thin, left=thin, right=thin, bottom=thin) row_heights = {} # loop and output result for result in string_results: column = result['column'] if not 'result_df' in result.keys(): ws.append([column, result['error_msg']]) for col in ['A', 'B']: ws['%s%d' %(col, ws.max_row)].style = 'Bad' ws.append(['']) continue result_df = result['result_df'][0][['feature', 'value', 'graph']] value_counts_df = result['result_df'][1] head_row = _insert_df(result_df, ws) # if there is value counts result if len(value_counts_df) > 0: value_counts_df = value_counts_df.rename(columns={'value': 'top 10 values', 'count_x': 'count_1', 'count_y': 'count_2'}) databar_head = _insert_df(value_counts_df, ws, header=True, head_style='60 % - Accent5') for row_idx in range(databar_head, databar_head+value_counts_df.shape[0]+1): row_heights[row_idx] = 25 # add conditional formatting: data bar first = FormatObject(type='num', val=0) second = FormatObject(type='num', val=np.max([value_counts_df['count_1'].max(), value_counts_df['count_2'].max()])) data_bar1 = DataBar(cfvo=[first, second], color=TABLE1_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None) data_bar2 = DataBar(cfvo=[first, second], color=TABLE2_DARK.replace('#', ''), showValue=True, minLength=None, maxLength=None) # assign the data bar to a rule rule1 = Rule(type='dataBar', dataBar=data_bar1) ws.conditional_formatting.add('B%d:B%d' %(databar_head+1, databar_head+len(value_counts_df)), rule1) rule2 = Rule(type='dataBar', dataBar=data_bar2) ws.conditional_formatting.add('C%d:C%d' %(databar_head+1, databar_head+len(value_counts_df)), rule2) # draw the thick outline border _style_range(ws, 'A%d:C%d'%(head_row, databar_head+len(value_counts_df)), border=border) else: _style_range(ws, 'A%d:C%d'%(head_row, head_row+result_df.shape[0]-1), border=border) # add gap ws.append(['']) _adjust_ws(ws, row_height=row_height, row_heights=row_heights, adjust_type='str')
def _insert_string_results(string_results, ws, row_height): """ Insert result of a string type column into a worksheet Parameters ---------- string_results: dict The result dictionary ws: Excel worksheet instance row_height: float Height of the rows """ # construct thin border thin = Side(border_style="thin", color="000000") border = Border(top=thin, left=thin, right=thin, bottom=thin) # loop and output result for result in string_results: column = result['column'] if 'result_df' not in result.keys(): ws.append([column, result['error_msg']]) for col in ['A', 'B']: ws['%s%d' %(col, ws.max_row)].style = 'Bad' ws.append(['']) continue result_df = result['result_df'][0] value_counts_df = result['result_df'][1] head_row = _insert_df(result_df, ws) # if there is value counts result if len(value_counts_df) > 0: value_counts_df = value_counts_df.rename(columns={column: 'top 10 values'}) databar_head = _insert_df(value_counts_df, ws, header=True, head_style='60 % - Accent5') # add conditional formatting: data bar first = FormatObject(type='num', val=0) second = FormatObject(type='num', val=value_counts_df['count'].max()) data_bar = DataBar(cfvo=[first, second], color=DIS_LINE.replace('#', ''), showValue=True, minLength=None, maxLength=None) # assign the data bar to a rule rule = Rule(type='dataBar', dataBar=data_bar) ws.conditional_formatting.add('B%d:B%d' %(databar_head+1, databar_head+len(value_counts_df)), rule) # draw the thick outline border _style_range(ws, 'A%d:B%d'%(head_row, databar_head+len(value_counts_df)), border=border) else: _style_range(ws, 'A%d:B%d'%(head_row, head_row+result_df.shape[0]-1), border=border) # add gap ws.append(['']) # adjust the worksheet _adjust_ws(ws=ws, row_height=row_height)