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')
