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 styled_sheet(): from openpyxl import Workbook from openpyxl.styles import Font, Style, Fill, Color, colors wb = Workbook() ws = wb.active ws.title = 'Test 1' red_fill = Fill() red_fill.fill_type = 'solid' red_fill.start_color = Color(Color.RED), red_fill.end_color = Color(Color.RED) empty_fill = Fill() styles = [] # pregenerate relevant styles for row in range(ROWS): _row = [] for col in range(COLUMNS): cell = ws.cell(row=row + 1, column=col + 1) cell.value = 1 font = Font() fill = Fill() if formatData[row][col] & BOLD: font.bold = True if formatData[row][col] & ITALIC: font.italic = True if formatData[row][col] & UNDERLINE: font.underline = 'single' if formatData[row][col] & RED_BG: fill = red_fill style = Style() style.font = font style.fill = fill ws._styles[cell.address] = style
def _output_excel(): query_text = request.args.get('query_text') input_sort = request.args.get('input_sort') results = _get_data_from_db(query_text, input_sort) if results=='fetch_db_error': print('Fetching database fails. Please check the log...') return '' # *** Output to a Excel file from openpyxl import Workbook from openpyxl.styles import Font, Color wb = Workbook() # grab the active worksheet ws = wb.active ws.title = 'Yahoo Buy Search Results' # 設置worksheet的標題 # write header excelHeader = ['category', 'item_title', 'item_price', 'item_info', 'staying_period'] ws.append(excelHeader) ws.append(['-----', '-----', '-----', '-----', '-----']) ft = Font() ft.underline = 'single' # add single underline ft.color = Color(rgb='000000FF') # add blue color for one_result in results: item_info_list = ['‧' + each_line for each_line in one_result[3].split(';;;')] control_characters = dict.fromkeys(range(32)) remove_control = lambda x: x.translate(control_characters) ws.append([remove_control(one_result[0]), remove_control(one_result[1]), remove_control(one_result[2]), remove_control("\r\n".join(item_info_list)), one_result[5]]) ws['A' + str(ws._current_row)] = '=HYPERLINK("{}", "{}")'.format(one_result[4], one_result[0]) ws['A' + str(ws._current_row)].font = ft import datetime filename = 'yahoo_buy_search_result_' + datetime.datetime.now().strftime("%Y-%m-%d") + '.xlsx' wb.save('static/' + filename) # ___ Output to a Excel file from flask import send_from_directory return send_from_directory('static', filename)
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 test_write_dxf(self): redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID whiteFont = Font() whiteFont.color.index = "FFFFFFFF" whiteFont.bold = True whiteFont.italic = True whiteFont.underline = 'single' whiteFont.strikethrough = True blueBorder = Borders() blueBorder.left.border_style = 'medium' blueBorder.left.color = Color(Color.BLUE) blueBorder.right.border_style = 'medium' blueBorder.right.color = Color(Color.BLUE) blueBorder.top.border_style = 'medium' blueBorder.top.color = Color(Color.BLUE) blueBorder.bottom.border_style = 'medium' blueBorder.bottom.color = Color(Color.BLUE) cf = ConditionalFormatting() cf.add('A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml(xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="FF0000FF"></color> </left> <right style="medium"> <color rgb="FF0000FF"></color> </right> <top style="medium"> <color rgb="FF0000FF"></color> </top> <bottom style="medium"> <color rgb="FF0000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
def test_write_dxf(self): redFill = Fill() redFill.start_color.index = 'FFEE1111' redFill.end_color.index = 'FFEE1111' redFill.fill_type = Fill.FILL_SOLID whiteFont = Font() whiteFont.color.index = "FFFFFFFF" whiteFont.bold = True whiteFont.italic = True whiteFont.underline = 'single' whiteFont.strikethrough = True blueBorder = Borders() blueBorder.left.border_style = 'medium' blueBorder.left.color = Color(Color.BLUE) blueBorder.right.border_style = 'medium' blueBorder.right.color = Color(Color.BLUE) blueBorder.top.border_style = 'medium' blueBorder.top.color = Color(Color.BLUE) blueBorder.bottom.border_style = 'medium' blueBorder.bottom.color = Color(Color.BLUE) cf = ConditionalFormatting() cf.add( 'A1:A2', FormulaRule(formula="[A1=1]", font=whiteFont, border=blueBorder, fill=redFill)) cf.setDxfStyles(self.workbook) assert len(self.workbook.style_properties['dxf_list']) == 1 assert 'font' in self.workbook.style_properties['dxf_list'][0] assert 'border' in self.workbook.style_properties['dxf_list'][0] assert 'fill' in self.workbook.style_properties['dxf_list'][0] w = StyleWriter(self.workbook) w._write_dxfs() xml = get_xml(w._root) diff = compare_xml( xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> <b val="1" /> <i val="1" /> <u val="single" /> <strike /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> <border> <left style="medium"> <color rgb="FF0000FF"></color> </left> <right style="medium"> <color rgb="FF0000FF"></color> </right> <top style="medium"> <color rgb="FF0000FF"></color> </top> <bottom style="medium"> <color rgb="FF0000FF"></color> </bottom> </border> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff