def test_serialise(self, ColorScale, FormatObject): fo1 = FormatObject(type="min", val="0") fo2 = FormatObject(type="percent", val="50") fo3 = FormatObject(type="max", val="0") col1 = Color(rgb="FFFF0000") col2 = Color(rgb="FFFFFF00") col3 = Color(rgb="FF00B050") cs = ColorScale(cfvo=[fo1, fo2, fo3], color=[col1, col2, col3]) xml = tostring(cs.to_tree()) expected = """ <colorScale> <cfvo type="min" val="0"/> <cfvo type="percent" val="50"/> <cfvo type="max" val="0"/> <color rgb="FFFF0000"/> <color rgb="FFFFFF00"/> <color rgb="FF00B050"/> </colorScale> """ diff = compare_xml(xml, expected) assert diff is None, diff
def migrate_sheet(newsheet, outputwb, migratedsheet_name): col_map = map_headings(newsheet, heading_row=3) if NEWSHEET_NUMID in col_map: output_sheet = outputwb.create_sheet(migratedsheet_name) migrate_heading(newsheet.wsheet, output_sheet) fill_row(output_sheet, 3, ['logo', 'numid']) row = 4 while fill_row(output_sheet, row, migrate_row(newsheet, row, col_map)): rgb, tint = row_color(newsheet, col_map, row) if rgb is not None: fill_color = Color(rgb=rgb, tint=tint) output_sheet.cell(row, OUTPUT_START_COL).fill = \ PatternFill(start_color=fill_color, end_color=fill_color, fill_type='solid') row += 1
def fill_row(self, ws, cell_range, color="DDD9C4", set_border=True, alignment='center'): rows = ws[cell_range] for row in rows: for cell in row: cell.style = Style(fill=PatternFill(patternType='solid', fill_type='solid', fgColor=Color(color))) cell.border = self.border if set_border == True else print(".") cell.alignment = Alignment(horizontal=alignment)
def test_write_pattern_fill(): fill = PatternFill(fill_type='solid', start_color=Color(colors.DARKYELLOW)) writer = StyleWriter(DummyWorkbook()) writer._write_pattern_fill(writer._root, fill) xml = get_xml(writer._root) expected = """<?xml version="1.0" ?> <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <patternFill patternType="solid"> <fgColor rgb="00808000" /> </patternFill> </styleSheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def test_conditional_font(self): """Test to verify font style written correctly.""" ws = self.ws cf = ConditionalFormattingList() ws.conditional_formatting = cf # Create cf rule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF")) ws.conditional_formatting.add( 'A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) from openpyxl.writer.worksheet import write_conditional_formatting # First, verify conditional formatting xml cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml( xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule dxfId="0" operator="equal" priority="1" type="cellIs" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def make_excel_file(title, level_dict): if os.path.isfile(title): wb = load_workbook(title) ws = wb.active else: wb = Workbook() ws = wb.active header_list = [ '카메라 ID', '신뢰도 min', '신뢰도 max', 'O(맞음)', 'X(틀림)', 'q(오검지)', 'XX(인식불가)', '합계' ] for i in range(len(header_list)): ws.cell(row=1, column=i + 1).fill = PatternFill(patternType='solid', fgColor=Color('FFC000')) ws.cell(row=1, column=i + 1).value = header_list[i] key_cnt = 0 for key, val_dict in level_dict.items(): key_start = key_cnt + 2 for f in val_dict.values(): list_for_excel = [] list_for_excel.append(key) list_for_excel.extend(f) key_cnt += 1 ws.append(list_for_excel) key_end = key_cnt + 1 #merge camera ID_cells merge_start = 'A' + str(key_start) merge_end = 'A' + str(key_end) merge_range = ':'.join([merge_start, merge_end]) ws.merge_cells(merge_range) ws[merge_start] = key ws[merge_start].font = Font(size=13, bold=True) ws[merge_start].alignment = Alignment(horizontal='center', vertical='center') #merge confidence cells for i in range(key_start, key_end + 1): merge_start = 'B' + str(i) merge_end = 'C' + str(i) start_val = ws.cell(i, 2).value end_val = ws.cell(i, 3).value merge_range = ':'.join([merge_start, merge_end]) ws.merge_cells(merge_range) ws[merge_start] = '-'.join([str(start_val), str(end_val)]) ws[merge_start].alignment = Alignment(horizontal='center', vertical='center') wb.save(title)
def conditional_formatting(self, ws, conditional_format_dict): cond_list = list(conditional_format_dict.items()) self.worksheet = self.wb[ws] for col in self.worksheet.columns: for cell in col: try: # Necessary to avoid error on empty cells for condition in cond_list: if str(cell.value) == condition[0]: cell.style = Style( fill=PatternFill(patternType='solid', fill_type='solid', fgColor=Color(condition[1]))) cell.border = self.border except: pass
def format_column_titles(self): title_font = Font(size=12, bold=True) alignment = Alignment(horizontal='center', vertical='center') fill_color = PatternFill(patternType='solid', fgColor=Color(rgb="BBBBBB")) border = Border(left=Side('thin'), right=Side('thin'), top=Side('thin'), bottom=Side('thin')) for column in range(1, 9): self.active_sheet.cell(2, column).font = title_font self.active_sheet.cell(2, column).alignment = alignment self.active_sheet.cell(2, column).fill = fill_color self.active_sheet.cell(2, column).border = border
def test_write(self, ChartsheetProperties): from openpyxl.styles import Color chartsheetPr = ChartsheetProperties() chartsheetPr.codeName = "Chart Openpyxl" tabColor = Color(rgb="FFFFFFF4") chartsheetPr.tabColor = tabColor expected = """ <sheetPr codeName="Chart Openpyxl"> <tabColor rgb="FFFFFFF4" /> </sheetPr> """ xml = tostring(chartsheetPr.to_tree()) diff = compare_xml(xml, expected) assert diff is None, diff
def test_parse(DifferentialStyle, datadir): datadir.chdir() with open("dxf_style.xml") as content: src = content.read() xml = fromstring(src) formats = [] for node in xml.findall("{%s}dxfs/{%s}dxf" % (SHEET_MAIN_NS, SHEET_MAIN_NS)): formats.append(DifferentialStyle.from_tree(node)) assert len(formats) == 164 cond = formats[1] assert cond.font == Font(underline="double", b=False, color=Color(auto=1), strikethrough=True, italic=True) assert cond.fill == PatternFill(end_color='FFFFC7CE') assert cond.border == Border( left=Side(), right=Side(), top=Side(style="thin", color=Color(theme=4)), bottom=Side(style="thin", color=Color(theme=4)), diagonal=None, )
def open_py_excel_pass(sheet_index, index): wb = openpyxl.load_workbook("C:\Users\sagarkul\Desktop\TestCase.xlsx") ws = wb.worksheets[sheet_index] for i in range(2, 10): if ws.cell(row=i, column=index + 1).value == "Pass": continue elif ws.cell(row=i, column=index + 1).value == "Fail": continue else: ws.cell(row=i, column=index + 1).value = "Pass" ws.cell(row=i, column=index + 1).style = Style( fill=PatternFill(patternType='solid', fgColor=Color('009900'))) ws.cell(row=1, column=index + 1).value = index break wb.save("C:\Users\sagarkul\Desktop\TestCase.xlsx")
def generate_formatted_data(data_path, problems): wb = load_workbook(data_path) ws = wb.active for problem in problems: cell = ws[problem["cell"]] if problem["level"] == "error": cell.fill = PatternFill(patternType="lightUp", fgColor=Color(indexed=10), fill_type="solid") else: cell.fill = PatternFill(patternType="lightUp", fgColor=Color(indexed=52), fill_type="solid") if cell.comment: comment = Comment( cell.comment.text + "\n" + problem["message"], author="tetramer_validator", ) cell.comment = comment else: cell.comment = Comment(problem["message"], author="tetramer-validator") wb.save(data_path)
def _coloring_enrollment_state(ws, row_number, exam_enroll): """ Coloring of the non-editable columns """ enrollment_state_color = get_line_color(exam_enroll) if enrollment_state_color: pattern_fill_enrollment_state = PatternFill( patternType='solid', fgColor=Color(enrollment_state_color.lstrip("#"))) style_enrollment_state = Style(fill=pattern_fill_enrollment_state) column_number = 1 while column_number < 12: ws.cell(row=row_number, column=column_number).style = style_enrollment_state column_number += 1
def test_parse_dxfs(): reference_file = os.path.join(DATADIR, 'reader', 'conditional-formatting.xlsx') wb = load_workbook(reference_file) assert isinstance(wb, Workbook) archive = ZipFile(reference_file, 'r', ZIP_DEFLATED) read_xml = archive.read(ARC_STYLE) # Verify length assert '<dxfs count="164">' in str(read_xml) assert len(wb.style_properties['dxf_list']) == 164 # Verify first dxf style reference_file = os.path.join(DATADIR, 'writer', 'expected', 'dxf_style.xml') with open(reference_file) as expected: diff = compare_xml(read_xml, expected.read()) assert diff is None, diff cond_styles = wb.style_properties['dxf_list'][0] assert cond_styles['font'].color == Color('FF9C0006') assert not cond_styles['font'].bold assert not cond_styles['font'].italic f = Fill() f.end_color = Color('FFFFC7CE') assert cond_styles['fill'] == f # Verify that the dxf styles stay the same when they're written and read back in. w = StyleWriter(wb) w._write_dxfs() write_xml = get_xml(w._root) read_style_prop = read_style_table(write_xml) assert len(read_style_prop['dxf_list']) == len( wb.style_properties['dxf_list']) for i, dxf in enumerate(read_style_prop['dxf_list']): assert repr(wb.style_properties['dxf_list'][i] == dxf)
def __coloring_non_editable(ws, row_number, score, justification): """ Coloring of the non-editable columns """ pattern_fill_grey = PatternFill(patternType='solid', fgColor=Color('C1C1C1')) style_no_modification = Style(fill=pattern_fill_grey) column_number = 1 while column_number < 12: if column_number < 9 or column_number > 10: ws.cell(row=row_number, column=column_number).style = style_no_modification else: if not(score is None and justification is None): ws.cell(row=row_number, column=9).style = style_no_modification ws.cell(row=row_number, column=10).style = style_no_modification column_number += 1
def test_parse(DifferentialStyle, datadir): datadir.chdir() with open("dxf_style.xml") as content: src = content.read() xml = fromstring(src) formats = [] for node in xml.findall("{%s}dxfs/{%s}dxf" % (SHEET_MAIN_NS, SHEET_MAIN_NS)): formats.append(DifferentialStyle.from_tree(node)) assert len(formats) == 164 cond = formats[1] assert cond.font == Font(underline="double", color=Color(auto=1), strikethrough=True, italic=True) assert cond.fill == PatternFill(end_color='FFFFC7CE')
def __writeExcelLine(self, fieldList): line = [] cellToChangeDict = {} for i, field in enumerate(fieldList): if i < 26: column = chr(ord('A') + i) else: column = 'A' + chr(ord('A') + i % 26) if isinstance(field, ExcelCell): cellToChangeDict['%s%d' % (column, self.__lineNb)] = field.color, field.bgColor, field.style, field.alignment, field.otherParamDict field = field.value line.append(field) self.__fh.append(line) for cellKey, (color, bgColor, style, alignment, otherParamDict) in \ cellToChangeDict.iteritems(): if color or style: paramDict = {} if color: paramDict['color'] = color if style: if style == 'italic': paramDict['italic'] = True elif style == 'bold': paramDict['bold'] = True if otherParamDict: paramDict.update(otherParamDict) try: newFont = self.__fh[cellKey].font.copy(**paramDict) except: print paramDict, cellKey print len(fieldList), fieldList raise self.__fh[cellKey].font = newFont if bgColor: self.__fh[cellKey].fill = PatternFill("solid", fgColor=Color(bgColor)) if alignment or (self.__otherParamDict and 'alignment' in self.__otherParamDict): if not alignment: alignment = self.__otherParamDict['alignment'] self.__fh[cellKey].alignment = Alignment(**alignment) self.__lineNb += 1
def set_font_color(self, reference, font_color, selected_sheet, workbook): col, row = Base._get_coordinates_from_reference(reference) book = self._get_workbook(workbook) sheet = self._select_sheet(book, selected_sheet) fill_color = self._get_color(font_color) if fill_color is None: try: fill_color = Color(font_color) except ValueError as e: print("color argument must be aRGB hex value or color name!") font = sheet.cell(row=row, column=col).font sheet.cell(row=row, column=col).font = Font(size=font.size, color=fill_color, bold=font.b, italic=font.i, underline=font.u) book.wb_book.save(book.wb_path)
def create_style(self): side = Side(border_style=self.border_type, color=utils.colors.black) border = Border(left=side, right=side, top=side, bottom=side) return Style(font=Font(name=self.font, size=self.font_size, color=Color(self.font_color), bold=self.bold, underline=self.underline), fill=PatternFill(patternType='solid', fgColor=self.bg_color), alignment=Alignment(horizontal=self.horizontal_alignment, vertical=self.vertical_alignment, wrap_text=True, shrink_to_fit=True, indent=0), border=border, number_format=self.number_format, protection=Protection(locked=self.protection))
def format_data(writer, current_date): workbook = writer.book sheet = workbook[current_date] #sheet = workbook.active #Fill ("NEW") rule blue_bg = PatternFill(bgColor=Color(indexed=27)) diff_style = DifferentialStyle(fill=blue_bg) rule = Rule(type="expression", dxf=diff_style) rule.formula = ['$D2 = "NEW"'] sheet.conditional_formatting.add("D2:E201", rule) #Arrow rule rule_arrow = IconSetRule('3Arrows', 'num', [-200, 0, 1]) sheet.conditional_formatting.add("D2:E201", rule_arrow) write_further_info(sheet) workbook.save(FILENAME)
def test_xfs_fills(self): cell = self.worksheet.cell('A1') cell.fill = fill = PatternFill(fill_type='solid', start_color=Color(colors.DARKYELLOW)) _ = cell.style_id # update workbook styles w = StyleWriter(self.workbook) w._write_cell_styles() xml = tostring(w._root) expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <cellXfs count="2"> <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/> <xf borderId="0" fillId="2" fontId="0" numFmtId="0" xfId="0"/> </cellXfs> </styleSheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def set_border(self, ws, cell_range, color="DDD9C4"): tmp = cell_range.split(":") a = tmp[0][1:] b = tmp[1][0] + a header = tmp[0] + ":" + b rows = ws[cell_range] for row in rows: for cell in row: cell.border = self.border head = ws[header] for row in head: for cell in row: cell.style = Style(fill=PatternFill(patternType='solid', fill_type='solid', fgColor=Color(color))) cell.border = self.border cell.alignment = Alignment(horizontal='center')
def add_data(self, json_objects, field_names, date_from=None, date_to=None): if not self.touched: self.touched = True else: self.next_sheet() self.write_header(date_from, date_to, 1) current_row = 3 """ adds data to the current wb sheet """ ws = self.wb.active for i, field_name in enumerate(field_names): cell = ws.cell(row=current_row, column=i + 1) cell.value = field_name cell.fill = PatternFill(fgColor=Color('E7E6E6'), patternType='solid') cell.font = Font(bold=True, name='Arial') cell.border = thin_border current_row += 1 for row, (json_object, _) in enumerate(json_objects): for col, field in enumerate(field_names): tmp = json_object['result'].get(field) if tmp: if isinstance(tmp, list): tmp = unicode(','.join(tmp)) else: tmp = unicode(tmp) cell = ws.cell(row=current_row + row, column=col + 1) cell.value = self.cast_value(tmp) cell.border = thin_border if current_row + row >= MAX_ROWS: break self.set_filters() self.set_width()
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_xfs_borders(self): cell = self.worksheet.cell('A1') cell.border = Border(top=Side(border_style=borders.BORDER_THIN, color=Color(colors.DARKYELLOW))) _ = cell.style_id # update workbook styles w = StyleWriter(self.workbook) w._write_cell_styles() xml = tostring(w._root) expected = """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <cellXfs count="2"> <xf borderId="0" fillId="0" fontId="0" numFmtId="0" xfId="0"/> <xf borderId="1" fillId="0" fontId="0" numFmtId="0" xfId="0"/> </cellXfs> </styleSheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def image_to_xlsx(image_file, xlsx_file): img = Image.open(image_file) pixel = img.load() wb = Workbook() ws = wb.active for i in range(img.width): for j in range(img.height): ws.cell(row=j + 1, column=i + 1).fill = PatternFill(fgColor=Color( '{0:02x}{1:02x}{2:02x}'.format(pixel[i, j][0], pixel[i, j][1], pixel[i, j][2])), fill_type='solid') for c in range(ws.max_column): ws.column_dimensions[get_column_letter(c + 1)].width = float(1.89) for r in range(ws.max_row): ws.row_dimensions[r + 1].height = float(14.4) wb.save(xlsx_file)
def setup_sheet(category): sheet = file.create_sheet(category) # header 입력 sheet['B1'].value = '상품명' sheet['C1'].value = '개수' sheet['D1'].value = '재고' # header 스타일 지정 font = Font(size=12, bold=True) align_center = Alignment(horizontal='center', vertical='center') fill_lightgray = PatternFill(patternType='solid', fgColor=Color('D5D5D5')) border_thin = Border(left=Side(style='thin'), right=Side(style='thin'), bottom=Side(style='thin')) for index in range(2, 5): sheet.cell(row=1, column=index).font = font sheet.cell(row=1, column=index).alignment = align_center sheet.cell(row=1, column=index).fill = fill_lightgray sheet.cell(row=1, column=index).border = border_thin
def make_excel_data(self): count = 2 cell_proposed = { 'alignment': Alignment(horizontal='right'), 'font': Font(bold=True), 'fill': PatternFill(patternType='solid', fgColor=Color('90909000')) } for each_boiler_blowdown in self.boiler_blowdown: for each_column in self.data_column_existing: cell_val = '%s%s' % (each_column, count) column_val = self.data_column_existing[each_column] self.ws[cell_val] = each_boiler_blowdown.get(column_val) count += 1 for each_column in self.data_column_proposed: cell_val = '%s%s' % (each_column, count) column_val = self.data_column_proposed[each_column] self.ws[cell_val] = each_boiler_blowdown.get(column_val) self.ws['A%s' % (count, )] = "Proposed Blowdown" self.ws['A%s' % (count, )].style = Style(**cell_proposed) self.ws.merge_cells('A%s:B%s' % (count, count)) count += 1
def compare(s1, s2, w1n,w2n): print "hello nikhil" pass rowrange = max(s1.max_row, s2.max_row) colrange = max(s1.max_column, s2.max_column) cfill = Style(fill=PatternFill(patternType='solid', fgColor=Color('00ff00'))) for i in range(1,rowrange+1): for j in range(1,colrange+1): a = s1.cell(row=i,column=j).value b = s2.cell(row=i,column=j).value nt = None dt = datetime.datetime.today() tlist = [type(int()), type(float()),type(long()), type(nt)] numlist = [type(int()), type(float()),type(long())] if type(a) not in numlist and type(b) not in numlist: a = str(unicode(a)) b = str(unicode(b)) if a != b: #s2.cell(row=i,column=j).style = cfill if type(a) in numlist and type(b) == type(nt): # print "hi" + str(a) comtxt = str(w1n)+": " + str(a)+ ". diff: "+ str(a) elif type(b) in numlist and type(a) == type(nt): # print "hi" + str(b) comtxt = str(w1n)+": " + str(a)+ ". diff: "+ str(b) elif type(a) in numlist and type(b) in numlist: comtxt = str(w1n)+": " + str(a)+ ". diff: "+ str(b-a) elif type(a) == type(str()) and type(b) == type(str()): comtxt = str(w1n)+": " + str(a)+ "." elif type(a) != type(b): comtxt = str(w1n)+": " + str(a)+ ". \nIncompatible data" comment = Comment(comtxt, w2n) s2.cell(row=i,column=j).comment = comment
def __init__(self, filename): self.wb = Workbook() self.ws = self.wb.active self.filename = filename self.column_map = {} self.tier_1_header_row = 1 self.tier_2_header_row = self.tier_1_header_row + 1 self.current_row = self.tier_2_header_row + 1 self.next_column = 1 self.enumeration_properties = [] self.fill = PatternFill( patternType='solid', fill_type='solid', fgColor=Color('EBF1DE') # RGB: 235, 241, 222 ) self.thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) self.thin_border_left = Border( left=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) self.thin_border_right = Border( right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin') ) self.thin_border_center = Border( top=Side(style='thin'), bottom=Side(style='thin') )