def SetCell(ws,wsRow,wsCol,val,fmt): align = None fill = None numFmt = None border = None c = ws.cell(row=wsRow,column=wsCol) for i in fmt: #logging.debug(i) if (i == 'hAlign'): if (not align): align = Alignment() align.horizontal = alignType[fmt[i]] if (i == 'vAlign'): if (not align): align = Alignment() align.vertical = alignType[fmt[i]] if (i == 'wrap'): if (not align): align = Alignment() if (fmt[i] == '1'): align.wrap_text = 1 else: align.wrap_text = 0 if (i == 'border'): if (not align): align = Alignment() side = Side(style='thin') border = Border(left=side,right=side,top=side,bottom=side) if (i == 'fill'): color = ColorTable[fmt[i]] fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid') if (i == 'orient'): pass if (i == 'bg'): fill = PatternFill(start_color='FFEE1111',end_color='FFEE1111',fill_type='solid') if (i == 'numFmt'): numFmt = fmt[i] if (align): c.alignment = align.copy() if (border): c.border = border.copy() if (fill): c.fill = fill.copy() if (numFmt): c.number_format = numFmt c.value = val
def get_sheet_alignment(align_dict: Box): align = Alignment( horizontal=TableFormat.DEFAULT_HORIZONTAL_ALIGNMENT, wrap_text=TableFormat.DEFAULT_WRAP_TEXT) if align_dict: align.horizontal = align_dict.get( 'horizontal', TableFormat.DEFAULT_HORIZONTAL_ALIGNMENT) align.wrap_text = align_dict.get('wrap_text', TableFormat.DEFAULT_WRAP_TEXT) return align
def xls_style_to_xlsx(self, xf_ndx): """Convert an xls xf_ndx into a 6-tuple of styles for xlsx""" font = Font() fill = PatternFill() border = Border() alignment = Alignment() number_format = 'General' protection = Protection(locked=False, hidden=False) if xf_ndx < len(self.book.xf_list): xf = self.book.xf_list[xf_ndx] xls_font = self.book.font_list[xf.font_index] # Font object font.b = xls_font.bold font.i = xls_font.italic if xls_font.character_set: font.charset = xls_font.character_set font.color = self.xls_color_to_xlsx(xls_font.colour_index) escapement = xls_font.escapement # 1=Superscript, 2=Subscript family = xls_font.family # FIXME: 0=Any, 1=Roman, 2=Sans, 3=monospace, 4=Script, 5=Old English/Franktur font.name = xls_font.name font.sz = self.xls_height_to_xlsx(xls_font.height) # A twip = 1/20 of a pt if xls_font.struck_out: font.strike = xls_font.struck_out if xls_font.underline_type: font.u = ('single', 'double')[(xls_font.underline_type&3)-1] xls_format = self.book.format_map[xf.format_key] # Format object number_format = xls_format.format_str if False: # xlrd says all cells are locked even if the sheet isn't protected! protection.locked = xf.protection.cell_locked protection.hidden = xf.protection.formula_hidden fill_patterns = {0x00:'none', 0x01:'solid', 0x02:'mediumGray', 0x03:'darkGray', 0x04:'lightGray', 0x05:'darkHorizontal', 0x06:'darkVertical', 0x07:'darkDown', 0x08:'darkUp', 0x09:'darkGrid', 0x0A:'darkTrellis', 0x0B:'lightHorizontal', 0x0C:'lightVertical', 0x0D:'lightDown', 0x0E:'lightUp', 0x0F:'lightGrid', 0x10:'lightTrellis', 0x11:'gray125', 0x12:'gray0625' } fill_pattern = xf.background.fill_pattern fill_background_color = self.xls_color_to_xlsx(xf.background.background_colour_index) fill_pattern_color = self.xls_color_to_xlsx(xf.background.pattern_colour_index) fill.patternType = fill_patterns.get(fill_pattern, 'none') fill.bgColor = fill_background_color fill.fgColor = fill_pattern_color horizontal = {0:'general', 1:'left', 2:'center', 3:'right', 4:'fill', 5:'justify', 6:'centerContinuous', 7:'distributed'} vertical = {0:'top', 1:'center', 2:'bottom', 3:'justify', 4:'distributed'} hor_align = horizontal.get(xf.alignment.hor_align, None) if hor_align: alignment.horizontal = hor_align vert_align = vertical.get(xf.alignment.vert_align, None) if vert_align: alignment.vertical = vert_align alignment.textRotation = xf.alignment.rotation alignment.wrap_text = xf.alignment.text_wrapped alignment.indent = xf.alignment.indent_level alignment.shrink_to_fit = xf.alignment.shrink_to_fit border_styles = {0: None, 1:'thin', 2:'medium', 3:'dashed', 4:'dotted', 5:'thick', 6:'double', 7:'hair', 8:'mediumDashed', 9:'dashDot', 10:'mediumDashDot', 11:'dashDotDot', 12:'mediumDashDotDot', 13:'slantDashDot',} xls_border = xf.border top = Side(style=border_styles.get(xls_border.top_line_style), color=self.xls_color_to_xlsx(xls_border.top_colour_index)) bottom = Side(style=border_styles.get(xls_border.bottom_line_style), color=self.xls_color_to_xlsx(xls_border.bottom_colour_index)) left = Side(style=border_styles.get(xls_border.left_line_style), color=self.xls_color_to_xlsx(xls_border.left_colour_index)) right = Side(style=border_styles.get(xls_border.right_line_style), color=self.xls_color_to_xlsx(xls_border.right_colour_index)) diag = Side(style=border_styles.get(xls_border.diag_line_style), color=self.xls_color_to_xlsx(xls_border.diag_colour_index)) border.top = top border.bottom = bottom border.left = left border.right = right border.diagonal = diag border.diagonalDown = xls_border.diag_down border.diagonalUp = xls_border.diag_up return (font, fill, border, alignment, number_format, protection)
def SetFormat(self,row,col,fmt): pyws = self.pyws font = None color = None align = None fill = None numFmt = None border = None c = pyws.cell(row=row,column=col) #------------------------------------------------------------------------- for i in fmt: if (i == 'hAlign'): if (not align): align = Alignment() align.horizontal = alignType[fmt[i]] elif (i == 'vAlign'): if (not align): align = Alignment() align.vertical = alignType[fmt[i]] elif (i == 'tAlign'): if (not align): align = Alignment() align.text_rotation = fmt[i] elif (i == 'wrap'): if (not align): align = Alignment() align.wrap_text = fmt[i] elif (i == 'font'): name = 'Calibri' bold = False size = 11 dict = fmt[i] if ('emph' in dict): if (dict['emph'] == 'B'): bold = True if ('size' in dict): size = dict['size'] if (not font): font = Font(name=name,size=size,bold=bold) elif (i == 'border'): dict = fmt[i] color = None style = None if ('Color' in dict): color = ColorTable[dict['Color']] else: color = ColorTable['Black'] if ('Style' in dict): color = dict['Style'] if (c.border.top.style == None): tSide = Side(color=color) else: tSide = c.border.top.copy() if (c.border.bottom.style == None): bSide = Side(color=color) else: bSide = c.border.bottom.copy() if (c.border.left.style == None): lSide = Side(color=color) else: lSide = c.border.left.copy() if (c.border.right.style == None): rSide = Side(color=color) else: rSide = c.border.right.copy() if ((len(dict) ==1) and ('A' in dict)): tSide.style = dict['A'] bSide.style = dict['A'] lSide.style = dict['A'] rSide.style = dict['A'] else: for j in dict: if (j == 'T'): tSide.style = dict[j] if (j == 'B'): bSide.style = dict[j] if (j == 'L'): lSide.style = dict[j] if (j == 'R'): rSide.style = dict[j] border = Border(left=lSide,right=rSide,top=tSide,bottom=bSide) elif (i == 'fill'): color = ColorTable[fmt[i]] fill = PatternFill(start_color=color,end_color='FFFFFFFF',fill_type='solid') elif (i == 'orient'): pass elif (i == 'numFmt'): numFmt = fmt[i] #------------------------------------------------------------------------- if (font): c.font = font.copy() if (align): c.alignment = align.copy() if (border): c.border = border.copy() if (fill): c.fill = fill.copy() if (numFmt): c.number_format = numFmt
def __init__(self, filename, sheet_name= None, font_name = "Calibri", font_size = 10, header_font_size = 14, header_bold = True, froze_first_row=True, add_fliter=True, header_height=25, add_border=True, alignment_center=True, specific_alignment_left = [], auto_fit_column_width=True, wrap_width=[], fill_color_header='95B3DF', hide_columns=[], column_width= {}, output_filename=None, home_page_bottom= None, whiten_nontable_area= False, zoom_level = 85, alignment_center_horizon = [], alignment_center_vertical = [] ): # load excel file and focus on one sheet if column_width is None: ## if sheet name is not defined, the app is default to apply format function to all sheets column_width = {} wb = load_workbook(filename) if sheet_name is None: sheet_name = wb.sheetnames elif type(sheet_name) == str: sheet_name = [sheet_name] # defind table, header, body for sheet_name_item in sheet_name: try: ws = wb[sheet_name_item] table = ws[ws.min_row:ws.max_row] header = ws[ws.min_row] if table == header: ## if there is only header in that sheet table = (table,) body = ws[ws.min_row + 1:ws.max_row] #font and font size font = Font(name=font_name, size=font_size, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') for tr in table: for td in tr: td.font = font font = Font(name=font_name, size=header_font_size, bold=False, italic=False, vertAlign=None, underline='none', strike=False, color='FF000000') #font for header for td in header: td.font = font if header_bold: for td in header: font = copy(td.font) font.bold = True td.font = font ## frozen first row if froze_first_row: freeze_position = ws['A2'] ws.freeze_panes = freeze_position ## add filter on header if add_fliter: ws.auto_filter.ref = ws.dimensions ## set header height header_obj = ws.row_dimensions[1] header_obj.height = header_height ## add_border if add_border: border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000'), top=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000'), diagonal=Side(border_style=None, color='FF000000'), diagonal_direction=0, outline=Side(border_style=None, color='FF000000'), vertical=Side(border_style=None, color='FF000000'), horizontal=Side(border_style=None, color='FF000000')) for tr in table: for td in tr: td.border = border ## alignment_center if alignment_center: alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0) for tr in table: for td in tr: td.alignment = alignment ## for those who have left top alignment for col in specific_alignment_left: column = ws[col] for td in column: alignment = copy(td.alignment) alignment.horizontal = 'left' alignment.vertical = 'top' td.alignment = alignment ## for those who have horizontal center alignment for col in alignment_center_horizon: column = ws[col] for td in column: alignment = copy(td.alignment) alignment.horizontal = 'center' td.alignment = alignment ## for those who have vertical center alignment for col in alignment_center_vertical: column = ws[col] for td in column: alignment = copy(td.alignment) alignment.vertical = 'center' td.alignment = alignment ###header do not change if alignment_center: for cell in header: alignment = copy(cell.alignment) alignment.horizontal = 'center' alignment.vertical = 'center' cell.alignment = alignment ## auto fit column width if auto_fit_column_width: column_widths = [] for i, cell in enumerate(header): try: column_widths[i] = len(cell.value) except IndexError: column_widths.append(len(cell.value)) for i, width in enumerate(column_widths): ws.column_dimensions[get_column_letter(i + 1)].width = width * 1.3 + 5 ##choose column width (specific) for col in column_width: ws.column_dimensions[col].width = column_width[col] ## wrap for col in wrap_width: column = ws[col] for td in column: alignment = copy(td.alignment) #alignment.horizontal = 'left' #alignment.vertical = 'top' alignment.wrap_text = True td.alignment = alignment #ws.column_dimensions[col].width = 55 ## header do not change alignment.horizontal ='center' alignment.vertical = 'center' ws[col + "1"].alignment=alignment ##fill header color if fill_color_header is not None: fill = PatternFill(start_color=fill_color_header, end_color=fill_color_header, fill_type="solid") for cell in header: cell.fill = fill ##hide columns for col in hide_columns: ws.column_dimensions[col].hidden = True if home_page_bottom is not None: offset = 4 No_row = ws.max_row + offset Home_Bottom = ws[get_column_letter(1) + str(No_row - 1)] Home_Bottom.value = f'=HYPERLINK("#{home_page_bottom}!A1","Home Page")' Home_Bottom.font = Font(size=15, bold=True, color='95B3DF') ##whiten_nontable_area if whiten_nontable_area: ### define area to whiten whiten_col = ws.max_column + 2 whiten_row = ws.max_row + 2 whiten_area1 = ws[get_column_letter(whiten_col) + str(1):"AJ200"] whiten_area2 = ws['A' + str(whiten_row): get_column_letter(whiten_col - 1) + str(200)] whiten_area_col = ws[get_column_letter(whiten_col - 1) + str(1):get_column_letter(whiten_col - 1) + str(200)] whiten_area_row = ws['A' + str(whiten_row - 1): get_column_letter(whiten_col - 1) + str(whiten_row - 1)] color = 'FFFFFFFF' font = Font(bold=False, italic=False, vertAlign=None, underline='none', strike=False, color=color) fill = PatternFill(start_color=color, end_color=color, fill_type="solid") for tr in whiten_area1: for td in tr: td.font = font td.border = None td.fill = fill for tr in whiten_area2: for td in tr: td.font = font td.border = None td.fill = fill for tr in whiten_area_col: for td in tr: td.font = font # td.border = None td.fill = fill for tr in whiten_area_row: for td in tr: td.font = font # td.border = None td.fill = fill ## adjust zoom level for ws in wb.worksheets: ws.sheet_view.zoomScale = zoom_level except: pass if output_filename is None: wb.save(filename=filename) else: wb.save(filename=output_filename) #formatter('test.xlsx', sheet_name='raw_data', wrap_width=['J'],column_width={'J':50},header_height=36,output_filename='dsad.xlsx',home_page_bottom='Summary',whiten_nontable_area=True ) #formatter(filename="test.xlsx", hide_columns=["C","D","F","D","S","U","T","W"]) #excel_file_name = 'test.xlsx' #formatter('test.xlsx',sheet_name = ['Sheet1','Sheet2','Sheet3'],output_filename='dsad.xlsx') #formatter('test.xlsx',output_filename='dsad.xlsx') #formatter('test.xlsx',sheet_name = 'Sheet1',output_filename='dsad1.xlsx')