def parse_font(self, font_node): """Read individual font""" font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') bold = font_node.find('{%s}b' % SHEET_MAIN_NS) if bold is not None: font.bold = bool(bold.get('val', True)) italic = font_node.find('{%s}i' % SHEET_MAIN_NS) if italic is not None: font.italic = bool(italic.get('val', True)) underline = font_node.find('{%s}u' % SHEET_MAIN_NS) if underline is not None: font.underline = underline.get('val', 'single') strikethrough = font_node.find('{%s}strike' % SHEET_MAIN_NS) if strikethrough is not None: font.strikethrough = True color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: font.color.index = self._get_relevant_color(color) return font
def parse_fonts(root, color_index): """Read in the fonts""" font_list = [] fonts = root.find('{%s}fonts' % SHEET_MAIN_NS) if fonts is not None: font_nodes = fonts.findall('{%s}font' % SHEET_MAIN_NS) for font_node in font_nodes: font = Font() fontSizeEl = font_node.find('{%s}sz' % SHEET_MAIN_NS) if fontSizeEl is not None: font.size = fontSizeEl.get('val') fontNameEl = font_node.find('{%s}name' % SHEET_MAIN_NS) if fontNameEl is not None: font.name = fontNameEl.get('val') font.bold = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False font.italic = True if len(font_node.findall('{%s}i' % SHEET_MAIN_NS)) else False if len(font_node.findall('{%s}u' % SHEET_MAIN_NS)): underline = font_node.find('{%s}u' % SHEET_MAIN_NS).get('val') font.underline = underline if underline else 'single' color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index): font.color.index = color_index[int(color.get('indexed'))] elif color.get('theme') is not None: if color.get('tint') is not None: font.color.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint')) else: font.color.index = 'theme:%s:' % color.get('theme') # prefix color with theme elif color.get('rgb'): font.color.index = color.get('rgb') font_list.append(font) return font_list
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == { 'font': font, 'border': borders, 'fill': fill } assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def test_conditional_formatting_addDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK dxfId = cf.addDxfStyle(self.workbook, font, borders, fill) assert dxfId == 0 dxfId = cf.addDxfStyle(self.workbook, None, None, fill) assert dxfId == 1 assert len(self.workbook.style_properties['dxf_list']) == 2
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = Fill() fill.start_color.index = 'FFEE1111' fill.end_color.index = 'FFEE1111' fill.fill_type = Fill.FILL_SOLID font = Font() font.name = 'Arial' font.size = 12 font.bold = True font.underline = Font.UNDERLINE_SINGLE borders = Borders() borders.top.border_style = Border.BORDER_THIN borders.top.color.index = Color.DARKYELLOW borders.bottom.border_style = Border.BORDER_THIN borders.bottom.color.index = Color.BLACK cf.add('C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=borders, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 2 assert self.workbook.style_properties['dxf_list'][0] == {'font': font, 'border': borders, 'fill': fill} assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
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)