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 parse_dxfs(root, color_index): """Read in the dxfs effects - used by conditional formatting.""" dxf_list = [] dxfs = root.find('{%s}dxfs' % SHEET_MAIN_NS) if dxfs is not None: nodes = dxfs.findall('{%s}dxf' % SHEET_MAIN_NS) for dxf in nodes: dxf_item = {} font_node = dxf.find('{%s}font' % SHEET_MAIN_NS) if font_node is not None: dxf_item['font'] = {} dxf_item['font']['bold'] = True if len(font_node.findall('{%s}b' % SHEET_MAIN_NS)) else False dxf_item['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') dxf_item['font']['underline'] = underline if underline else 'single' color = font_node.find('{%s}color' % SHEET_MAIN_NS) if color is not None: dxf_item['font']['color'] = Color(Color.BLACK) if color.get('indexed') is not None and 0 <= int(color.get('indexed')) < len(color_index): dxf_item['font']['color'].index = color_index[int(color.get('indexed'))] elif color.get('theme') is not None: if color.get('tint') is not None: dxf_item['font']['color'] .index = 'theme:%s:%s' % (color.get('theme'), color.get('tint')) else: dxf_item['font']['color'] .index = 'theme:%s:' % color.get('theme') # prefix color with theme elif color.get('rgb'): dxf_item['font']['color'] .index = color.get('rgb') fill_node = dxf.find('{%s}fill' % SHEET_MAIN_NS) if fill_node is not None: dxf_item['fill'] = parse_fills(dxf, color_index, True) dxf_item['border'] = parse_borders(dxf, color_index, True) dxf_list.append(dxf_item) return dxf_list
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) font = Font(name='Arial', size=12, bold=True, underline=Font.UNDERLINE_SINGLE) border = Border(top=Side(border_style=borders.BORDER_THIN, color=Color(colors.DARKYELLOW)), bottom=Side(border_style=borders.BORDER_THIN, color=Color(colors.BLACK))) cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=border, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) cf._save_styles(self.workbook) assert len(self.workbook._differential_styles) == 2 ft1, ft2 = self.workbook._differential_styles assert ft1.font == font assert ft1.border == border assert ft1.fill == fill assert ft2.fill == fill
def test_notEqual(self): cf = ConditionalFormatting() redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) cf.add( 'U10:U18', CellIsRule(operator='notEqual', formula=['U$7'], stopIfTrue=True, fill=redFill)) cf.add( 'V10:V18', CellIsRule(operator='!=', formula=['V$7'], stopIfTrue=True, fill=redFill)) cf.setDxfStyles(self.workbook) rules = cf.cf_rules assert 'U10:U18' in rules assert len(cf.cf_rules['U10:U18']) == 1 assert rules['U10:U18'][0]['priority'] == 1 assert rules['U10:U18'][0]['type'] == 'cellIs' assert rules['U10:U18'][0]['dxfId'] == 0 assert rules['U10:U18'][0]['operator'] == 'notEqual' assert rules['U10:U18'][0]['formula'][0] == 'U$7' assert rules['U10:U18'][0]['stopIfTrue'] == '1' assert 'V10:V18' in rules assert len(cf.cf_rules['V10:V18']) == 1 assert rules['V10:V18'][0]['priority'] == 2 assert rules['V10:V18'][0]['type'] == 'cellIs' assert rules['V10:V18'][0]['dxfId'] == 1 assert rules['V10:V18'][0]['operator'] == 'notEqual' assert rules['V10:V18'][0]['formula'][0] == 'V$7' assert rules['V10:V18'][0]['stopIfTrue'] == '1'
def styled_sheet(): from openpyxl import Workbook from openpyxl.styles import Font, Style, PatternFill, Color, colors wb = Workbook() ws = wb.active ws.title = 'Test 1' red_fill = PatternFill(fill_type='solid', fgColor=Color(colors.RED), bgColor=Color(colors.RED)) empty_fill = PatternFill() 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 = {} fill = PatternFill() 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 cell.style = Style(font=Font(**font), fill=fill)
def setCellColorByCharAndRow(self, stringColumn, row, R=255, G=255, B=255): if (utils.column_index_from_string(stringColumn.strip()) < 0): raise ColumnIsNotVaild("列 不能小于0") if (row < 0): raise ColumnIsNotVaild(str(row) + "不能小于0行") if (R == 255 and G == 255 and B == 255): fill = PatternFill( patternType=fills.FILL_NONE, fgColor=Color(rgb=self.rgbConvertToHex(R, G, B))) else: fill = PatternFill( patternType=fills.FILL_SOLID, fgColor=Color(rgb=self.rgbConvertToHex(R, G, B))) stringColumn = str(stringColumn).upper().strip() # print("hdsjafaksldf"); # print(self.mergeCellsColumnAndRow); for i in self.mergeCellsCharAndIndex: # print("stringcolumn "+str(stringColumn)); if stringColumn >= str(i[0]).upper() and stringColumn <= str( i[2]).upper() and row >= i[1] and row <= i[3]: for neiHang in range((i[1]), i[3] + 1): mybegin = utils.column_index_from_string(i[0]) myend = utils.column_index_from_string(i[2]) for neiCol in range(mybegin, myend + 1): self.sheet.cell(row=neiHang, column=neiCol).fill = fill self.wb.save(self.path) return self.sheet.cell(str(stringColumn) + str(row)).fill = fill self.wb.save(self.path)
def setCellColorByColumnAndRow(self, hang, lie, R=255, G=255, B=255): if (lie < 0): raise ColumnIsNotVaild("列 不能小于0") if (hang < 0): raise ColumnIsNotVaild(str(hang) + "不能小于0行") if (R == 255 and G == 255 and B == 255): fill = PatternFill( patternType=fills.FILL_NONE, fgColor=Color(rgb=self.rgbConvertToHex(R, G, B))) else: fill = PatternFill( patternType=fills.FILL_SOLID, fgColor=Color(rgb=self.rgbConvertToHex(R, G, B))) for i in self.mergeCellsColumnAndRow: if hang >= i[1] and hang <= i[3] and lie >= i[0] and lie <= i[2]: for neiHang in range(i[1], i[3] + 1): for neiCol in range(i[0], i[2] + 1): self.sheet.cell(row=neiHang, column=neiCol).fill = fill self.wb.save(self.path) return self.sheet.cell(row=hang, column=lie).fill = fill self.wb.save(self.path)
def test_conditional_font(self): """Test to verify font style written correctly.""" class WS(): conditional_formatting = ConditionalFormatting() worksheet = WS() # Create cf rule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) whiteFont = Font(color=Color("FFFFFFFF")) worksheet.conditional_formatting.add( 'A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) worksheet.conditional_formatting.setDxfStyles(self.workbook) # First, verify conditional formatting xml cfs = write_conditional_formatting(worksheet) 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"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff # Second, verify conditional formatting dxf styles w = StyleWriter(self.workbook) w._write_dxfs() xml = tostring(w._root) diff = compare_xml( xml, """ <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <dxfs count="1"> <dxf> <font> <color rgb="FFFFFFFF" /> </font> <fill> <patternFill patternType="solid"> <fgColor rgb="FFEE1111" /> <bgColor rgb="FFEE1111" /> </patternFill> </fill> </dxf> </dxfs> </styleSheet> """) assert diff is None, diff
def test_conditional_font(worksheet_with_cf, write_conditional_formatting): """Test to verify font style written correctly.""" # Create cf rule from openpyxl.styles import PatternFill, Font, Color from openpyxl.formatting.rule import CellIsRule redFill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType='solid') whiteFont = Font(color=Color("FFFFFFFF")) ws = worksheet_with_cf ws.conditional_formatting.add('A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill)) cfs = write_conditional_formatting(ws) xml = b"" for cf in cfs: xml += tostring(cf) diff = compare_xml(xml, """ <conditionalFormatting sqref="A1:A3"> <cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> """) assert diff is None, diff
def write(self, first_row, query, query_index, columns, sheet_name, table_names): self.cursor.execute(query) data = self.cursor.fetchall() last_row = first_row + len(data) + 1 # Create Pandas dataframe from the data df = pd.DataFrame(data, columns=columns) worksheet = self.book[sheet_name] df[columns[1]] = df[columns[1]].astype(float) current_row = first_row for r in dataframe_to_rows(df, index=False, header=True): worksheet.cell(row=current_row, column=1).value = r[0] worksheet.cell(row=current_row, column=2).value = r[1] current_row += 1 # Add table title worksheet.cell(row=first_row - 1, column=1, value=table_names[query_index]) # Add colouring to values worksheet.conditional_formatting.add( 'B{}:B{}'.format(first_row, last_row), ColorScaleRule(start_type='min', start_color=Color('AA0000'), end_type='max', end_color=Color('00AA00'))) return last_row
def dos_header_excel_color(self): t_dos_excel = pyxl.load_workbook( "C:/Users/sleep/Desktop/Reversing/dos_header.xlsx") ws = t_dos_excel.sheetnames ws = t_dos_excel[ws[1]] INDX = ['C', 3] c = 0 for v in self.dos_header_dict.values(): indx, size = int(v['Offset'], 16), v['SIZE'] for a in range(0, size): t_data, t_mov = (indx + a) // 16, (indx + a) % 16 if t_data == 0: ws[chr(ord(INDX[0]) + t_mov) + str(INDX[1] + t_data)].fill = PatternFill( patternType='solid', fgColor=Color(self.color_list[c])) elif t_data == 1: ws[chr(ord(INDX[0]) + t_mov) + str(INDX[1] + t_data)].fill = PatternFill( patternType='solid', fgColor=Color(self.color_list[c])) elif t_data == 2: ws[chr(ord(INDX[0]) + t_mov) + str(INDX[1] + t_data)].fill = PatternFill( patternType='solid', fgColor=Color(self.color_list[c])) else: ws[chr(ord(INDX[0]) + t_mov) + str(INDX[1] + t_data)].fill = PatternFill( patternType='solid', fgColor=Color(self.color_list[c])) c = (c + 1) % len(self.color_list) t_dos_excel.save("C:/Users/sleep/Desktop/Reversing/dos_header.xlsx") print("success save")
def export_excel(): # data = load_data() wb = Workbook() ws = wb.active cell = ws['A1'] cell.value = 12 thin = Side(border_style="thin", color="000000") dark_red = PatternFill("solid", fgColor=Color(theme=5, tint=0.4)) light_red = PatternFill("solid", fgColor=Color(theme=5, tint=0.6)) bold = Font(b=True, color="000000") regular = Font(b=False, color="000000") styles = { 'fill': PatternFill("solid", fgColor=Color(theme=5, tint=0.4)), 'border': Border(top=thin, left=thin, right=thin, bottom=thin), 'font': Font(b=True, color="000000") } style_range(ws, 'A1:C3', **styles) wb.save(sys.argv[1] + '.xlsx')
def setFontAndColour(self): cellFont = Font(name='Times New Roman', size=12) cellFontBold = Font(name='Times New Roman', size=12, bold=True) headerFillcolor = Color(rgb='00C4D79B') oddFillcolor = Color(rgb='00E9E17F') cellHeaderFill = PatternFill(patternType='solid', fgColor=headerFillcolor) cellOddFill = PatternFill(patternType='solid', fgColor=oddFillcolor) for r in range(1, self.ws.max_row + 1): for c in range(1, self.ws.max_column + 1): # 1) font , size if self.headerRow and r == 1: self.ws.cell(row=r, column=c).font = cellFontBold else: self.ws.cell(row=r, column=c).font = cellFont # 2) color : header row b/g colour , odd row b/g colour if r == 1: if self.headerRow: self.ws.cell(row=r, column=c).fill = cellHeaderFill else: self.ws.cell(row=r, column=c).fill = cellOddFill else: if r & 1: self.ws.cell(row=r, column=c).fill = cellOddFill
def test_conditional_formatting_setDxfStyle(self): cf = ConditionalFormatting() fill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) font = Font(name='Arial', size=12, bold=True, underline=Font.UNDERLINE_SINGLE) border = Border(top=Side(border_style=borders.BORDER_THIN, color=Color(colors.DARKYELLOW)), bottom=Side(border_style=borders.BORDER_THIN, color=Color(colors.BLACK))) cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=border, 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': border, 'fill': fill } assert self.workbook.style_properties['dxf_list'][1] == {'fill': fill}
def make_excel_headers(self): cell_bold = {'font': Font(bold=True)} # Cell Properties cell_existing = { 'alignment': Alignment(horizontal='center'), 'font': Font(bold=True), 'fill': PatternFill(patternType='solid', fgColor=Color('90909000')) } self.ws['E1'] = "Existing Motor" self.ws['E1'].style = Style(**cell_existing) # Mergeing Cells self.ws.merge_cells('E1:K1') # Cell Properties cell_proposed = { 'alignment': Alignment(horizontal='center'), 'font': Font(bold=True), 'fill': PatternFill(patternType='solid', fgColor=Color('90909099')) } self.ws['L1'] = "Proposed Premium Efficiency Motor" self.ws['L1'].style = Style(**cell_proposed) # Mergeing Cells self.ws.merge_cells('L1:R1') for each_cell in self.headers_dict: self.ws[each_cell] = self.headers_dict[each_cell] self.ws[each_cell].style = Style(**cell_bold) column = each_cell.replace('2', '') self.ws.column_dimensions[column].width = len( self.headers_dict[each_cell])
def process_item(self, item, spider): wb = Workbook() ws = wb.active ws.title = "Cars" with open(self.csvfilename, 'r') as f: reader = csv.reader(f) for r, row in enumerate(reader, start=1): for c, val in enumerate(row, start=1): # r is row number, c, is column number (both 1-indexed) ws.cell(row=r, column=c).value = val if r == 1: # headers = blue ws.cell(row=r, column=c).font = Font(size=16) ws.cell(row=r, column=c).fill = PatternFill( fgColor=Color('0087BD'), fill_type='solid') else: # odd rows = gray ws.cell(row=r, column=c).font = Font(size=14) if r % 2 == 1: # row is odd but not equal to 1 ws.cell(row=r, column=c).fill = PatternFill( fgColor=Color('BFC1C2'), fill_type='solid') # adjust column lengths in worksheet based on widest cells per column for col in ws.columns: max_length = 0 column = col[0].column # Get the column name for cell in col: try: # Necessary to avoid error on empty cells if len(str(cell.value)) > max_length: max_length = len(cell.value) except: pass adjusted_width = (max_length + 2.0) * 1.1 ws.column_dimensions[column].width = adjusted_width wb.save(self.spreadsheetfilename) return item
def test_formatting(self, writer): redFill = PatternFill( start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType='solid' ) whiteFont = Font(color=Color("FFFFFFFF")) ws = writer.ws ws.conditional_formatting.add('A1:A3', CellIsRule(operator='equal', formula=['"Fail"'], stopIfTrue=False, font=whiteFont, fill=redFill) ) writer.write_formatting() xml = writer.read() expected = """ <worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"> <conditionalFormatting sqref="A1:A3"> <cfRule operator="equal" priority="1" type="cellIs" dxfId="0" stopIfTrue="0"> <formula>"Fail"</formula> </cfRule> </conditionalFormatting> </worksheet> """ diff = compare_xml(xml, expected) assert diff is None, diff
def _convert_to_color(cls, color_spec): """ Convert ``color_spec`` to an openpyxl v2 Color object. Parameters ---------- color_spec : str, dict A 32-bit ARGB hex string, or a dict with zero or more of the following keys. 'rgb' 'indexed' 'auto' 'theme' 'tint' 'index' 'type' Returns ------- color : openpyxl.styles.Color """ from openpyxl.styles import Color if isinstance(color_spec, str): return Color(color_spec) else: return Color(**color_spec)
def test_write_conditional_formatting(self): ws = self.ws cf = ConditionalFormattingList() ws.conditional_formatting = cf fill = PatternFill(start_color=Color('FFEE1111'), end_color=Color('FFEE1111'), patternType=fills.FILL_SOLID) font = Font(name='Arial', size=12, bold=True, underline=Font.UNDERLINE_SINGLE) border = Border(top=Side(border_style=borders.BORDER_THIN, color=Color(colors.DARKYELLOW)), bottom=Side(border_style=borders.BORDER_THIN, color=Color(colors.BLACK))) cf.add( 'C1:C10', FormulaRule(formula=['ISBLANK(C1)'], font=font, border=border, fill=fill)) cf.add('D1:D10', FormulaRule(formula=['ISBLANK(D1)'], fill=fill)) from openpyxl.writer.worksheet import write_conditional_formatting for _ in write_conditional_formatting(ws): pass # exhaust generator wb = ws.parent assert len(wb._differential_styles.styles) == 2 ft1, ft2 = wb._differential_styles.styles assert ft1.font == font assert ft1.border == border assert ft1.fill == fill assert ft2.fill == fill
def test_parse_dxfs(datadir): datadir.chdir() reference_file = '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 = '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 = PatternFill(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 = tostring(w._root) read_style_prop = read_style_table(write_xml) assert len(read_style_prop[2]) == len(wb.style_properties['dxf_list']) for i, dxf in enumerate(read_style_prop[2]): assert repr(wb.style_properties['dxf_list'][i] == dxf)
def test_read_complex_style(datadir): datadir.chdir() wb = load_workbook("complex-styles.xlsx") ws = wb.active assert ws.column_dimensions['A'].width == 31.1640625 #assert ws.column_dimensions['I'].font == Font(sz=12.0, color='FF3300FF', scheme='minor') assert ws.column_dimensions['I'].fill == PatternFill( patternType='solid', fgColor='FF006600', bgColor=Color(indexed=64)) assert ws['A2'].font == Font(sz=10, name='Arial', color=Color(theme=1)) assert ws['A3'].font == Font(sz=12, name='Arial', bold=True, color=Color(theme=1)) assert ws['A4'].font == Font(sz=14, name='Arial', italic=True, color=Color(theme=1)) assert ws['A5'].font.color.value == 'FF3300FF' assert ws['A6'].font.color.value == 9 assert ws['A7'].fill.start_color.value == 'FFFFFF66' assert ws['A8'].fill.start_color.value == 8 assert ws['A9'].alignment.horizontal == 'left' assert ws['A10'].alignment.horizontal == 'right' assert ws['A11'].alignment.horizontal == 'center' assert ws['A12'].alignment.vertical == 'top' assert ws['A13'].alignment.vertical == 'center' assert ws['A15'].number_format == '0.00' assert ws['A16'].number_format == 'mm-dd-yy' assert ws['A17'].number_format == '0.00%' assert 'A18:B18' in ws._merged_cells assert ws['A19'].border == Border( left=Side(style='thin', color='FF006600'), top=Side(style='thin', color='FF006600'), right=Side(style='thin', color='FF006600'), bottom=Side(style='thin', color='FF006600'), ) assert ws['A21'].border == Border( left=Side(style='double', color=Color(theme=7)), top=Side(style='double', color=Color(theme=7)), right=Side(style='double', color=Color(theme=7)), bottom=Side(style='double', color=Color(theme=7)), ) assert ws['A23'].fill == PatternFill(patternType='solid', start_color='FFCCCCFF', end_color=(Color(indexed=64))) assert ws['A23'].border.top == Side(style='mediumDashed', color=Color(theme=6)) assert 'A23:B24' in ws._merged_cells assert ws['A25'].alignment == Alignment(wrapText=True) assert ws['A26'].alignment == Alignment(shrinkToFit=True)
def bg_color(self, cell, color = None): if color: a_fill = PatternFill(start_color=Color(color), end_color=Color(color), fill_type='solid') cell.fill = a_fill else: return cell.font.size
def test_read_gradient_fill(StyleReader, datadir): datadir.chdir() expected = [ GradientFill(degree=90, stop=[Color(theme=0), Color(theme=4)]) ] with open("bug284-styles.xml") as src: reader = StyleReader(src.read()) assert list(reader.parse_fills()) == expected
def cwt_term_excel_write(processing_df): yearly_n_niin_grouped = processing_df.groupby( ['PLAN_YRMON', 'LOINO', 'N_NIIN']).mean() yearly_n_niin_grouped_in_10days = yearly_n_niin_grouped[ yearly_n_niin_grouped['CWT'] <= 10]['CWT'] yearly_n_niin_grouped_in_30days = \ yearly_n_niin_grouped[(yearly_n_niin_grouped['CWT'] > 10) & (yearly_n_niin_grouped['CWT'] <= 30)]['CWT'] yearly_n_niin_grouped_in_50days = \ yearly_n_niin_grouped[(yearly_n_niin_grouped['CWT'] > 30) & (yearly_n_niin_grouped['CWT'] <= 50)]['CWT'] yearly_n_niin_grouped_up_50days = yearly_n_niin_grouped[ yearly_n_niin_grouped['CWT'] > 50]['CWT'] p_yearly_n_niin_grouped = yearly_n_niin_grouped['CWT'] yearly_n_niin_grouped_df = \ pd.DataFrame(columns=[2013, 2014, 2015, 2016, 2017, 2018], index=['총 개수(개)', '10일 이내(%)', '30일 이내(%)', '50일 이내(%)', '50일 초과(%)']) yearly_n_niin_grouped_df.fillna(0, inplace=True) count_grouping_yearly(yearly_n_niin_grouped_df, p_yearly_n_niin_grouped, "총 개수(개)", 0) count_grouping_yearly(yearly_n_niin_grouped_df, yearly_n_niin_grouped_in_10days, "10일 이내(%)", 1) count_grouping_yearly(yearly_n_niin_grouped_df, yearly_n_niin_grouped_in_30days, "30일 이내(%)", 1) count_grouping_yearly(yearly_n_niin_grouped_df, yearly_n_niin_grouped_in_50days, "50일 이내(%)", 1) count_grouping_yearly(yearly_n_niin_grouped_df, yearly_n_niin_grouped_up_50days, "50일 초과(%)", 1) writer = pd.ExcelWriter('./ResultFile/cwt_by_period.xlsx', engine='openpyxl') if len(yearly_n_niin_grouped_in_10days) > 0: yearly_n_niin_grouped_in_10days.to_excel(writer, sheet_name='10일이내') if len(yearly_n_niin_grouped_in_30days) > 0: yearly_n_niin_grouped_in_30days.to_excel(writer, sheet_name='30일이내') if len(yearly_n_niin_grouped_in_50days) > 0: yearly_n_niin_grouped_in_50days.to_excel(writer, sheet_name='50일이내') if len(yearly_n_niin_grouped_up_50days) > 0: yearly_n_niin_grouped_up_50days.to_excel(writer, sheet_name='50일초과') if len(yearly_n_niin_grouped_df) > 0: yearly_n_niin_grouped_df.to_excel(writer, sheet_name='결과현황') writer.save() book = load_workbook('./ResultFile/cwt_by_period.xlsx') sheets = book.get_sheet_names() sheets.remove('결과현황') for sheetname in sheets: ws = book[sheetname] for cellname in ['A', 'B', 'C', 'D']: cell = ws[cellname + '1'] cell.fill = PatternFill(patternType='solid', fgColor=Color('cccccc')) ws.column_dimensions['A'].width = 14.5 ws.column_dimensions['B'].width = 16.5 ws.column_dimensions['C'].width = 11.75 ws.column_dimensions['D'].width = 12.13 wsr = book['결과현황'] for cellnamer in ['A', 'B', 'C', 'D', 'E', 'F', 'G']: cell = wsr[cellnamer + '1'] cell.fill = PatternFill(patternType='solid', fgColor=Color('cccccc')) wsr.column_dimensions['A'].width = 12.75 book.save('./ResultFile/cwt_by_period.xlsx')
def setStyles(cell, bgColor): cell.font = Font(bold=False, color='000000') bd = Side(style='thin', color=Color('FFFFFF')) cell.border = Border(left=bd, top=bd, right=bd, bottom=bd) cell.fill = PatternFill(fill_type='solid', fgColor=Color(bgColor)) cell.alignment = Alignment(shrinkToFit=True, horizontal='left', wrapText=True, vertical='center')
def test_color_legend(self): _color_legend(self.worksheet) self.assertEqual( self.worksheet.cell(row=FIRST_ROW_LEGEND_ENROLLMENT_STATUS, column=FIRST_COL_LEGEND_ENROLLMENT_STATUS).style.fill.fgColor, Color(rgb=ENROLLED_LATE_COLOR.lstrip('#'))) self.assertEqual( self.worksheet.cell(row=FIRST_ROW_LEGEND_ENROLLMENT_STATUS+1, column=FIRST_COL_LEGEND_ENROLLMENT_STATUS).style.fill.fgColor, Color(rgb=NOT_ENROLLED_COLOR.lstrip("#")))
def add_header(cell, value): gray = Color(rgb='00C2C2C2') black = Color(rgb='000000') border = Border(left=Side(border_style='thin', color=black), right=Side(border_style='thin', color=black), top=Side(border_style='thin', color=black), bottom=Side(border_style='thin', color=black)) cell.fill = PatternFill(patternType='solid', fgColor=gray) cell.alignment = Alignment(horizontal="center") cell.border = border cell.value = value
def write_totalrow(r, _r, ws): ws.cell(row=r, column=1, value="Total") _a = ws.cell(row=r, column=7, value="=SUBTOTAL(109,G{0}:G{1})".format(_r, r - 2)) _a.number_format = FORMAT_CURRENCY_USD_SIMPLE for c in range(1, 8): _a = ws.cell(row=r, column=c) _a.font = Font(b=True, color=Color(rgb="00FFFFFF")) _a.fill = PatternFill(fill_type='solid', patternType='solid', fgColor=Color(rgb="00000000")) r += 1 return (r)
def wrapper(ws, number_of_row): # 设置标题样式 ws.merge_cells('a1:f1') color1 = Color(rgb=title_color) font = Font(name="Microsoft YaHei UI", size=34, b=True, color=color1) a1 = ws['a1'] alignment = Alignment(horizontal='center', vertical='center') a1.font = font a1.alignment = alignment # 设置表头样式 color2 = Color(rgb=header_color) style_for_row2 = NamedStyle(name='header') style_for_row2.font = Font(name='Calibri', size=16, color='FFFFFF') style_for_row2.alignment = Alignment(horizontal='center', vertical='center') style_for_row2.fill = PatternFill('solid', fgColor=color2) for each_cell in ws[2]: each_cell.style = style_for_row2 # 设置表格样式 for i in range(1, number_of_row + 3): ws.row_dimensions[i].height = 49.5 for i in range(1, 7): ws.column_dimensions[chr(64 + i)].width = 15 color3 = Color(rgb=body_color) style_for_body = NamedStyle(name='body') style_for_body.font = Font(name='Calibri') style_for_body.alignment = Alignment(horizontal='center', vertical='center') style_for_body.fill = PatternFill("solid", fgColor=color3) style_for_body.border = Border(left=Side(border_style='thin', color='FF000000'), right=Side(border_style='thin', color='FF000000'), bottom=Side(border_style='thin', color='FF000000'), top=Side(border_style='thin', color='FF000000')) for i in range(3, number_of_row + 3): for j in range(1, 7): ws.cell(row=i, column=j).style = style_for_body return function(ws, number_of_row)
def get_cell_style(color='FF000000', bgcolor='FFFFFFFF', font='Calibri', size=11, bold=False, italic=False, underline='none', strike=False, border=None, border_style=BORDER_THIN, border_bottom=None, border_bottom_style=None, horizontal='general', vertical='bottom', number_format=None): if not border: border = 'FFB6B6B4' if not border_bottom: border_bottom = border if not border_bottom_style: border_bottom_style = border_style return Style(font=Font(name=font, size=size, bold=bold, italic=italic, vertAlign=None, underline=underline, strike=strike, color=color), fill=PatternFill(patternType='solid', fgColor=Color(bgcolor)), border=Border( left=Side(border_style=border_style, color=Color(border)), right=Side(border_style=border_style, color=Color(border)), top=Side(border_style=border_style, color=Color(border)), bottom=Side(border_style=border_bottom_style, color=Color(border_bottom)), ), alignment=Alignment(horizontal=horizontal, vertical=vertical, text_rotation=0, wrap_text=False, shrink_to_fit=False, indent=0), number_format=number_format)
def set_title(ws): # sheet 에서 특정 셀을 선택해서 병합 정렬을 수행합니다. ws.merge_cells('A1:E1') # sheet 에서 A1 셀에 'Report' 라는 값을 입력합니다. ws['A1'] = 'Report' title = ws['A1'] # sheet 의 font 설정을 Font 모듈을 사용해서 설정합니다. title.font = Font(name='nanum gothic', size=18, bold=True) # sheet 의 정렬 설정을 Alignment 모듈을 사용해서 설정합니다. title.alignment = Alignment(horizontal='center', vertical='center') # sheet 의 fill 설정을 PatternFill 모듈을 사용해서 설정합니다. # 셀의 뒷 배경의 색상을 변경할 수 있습니다. title.fill = PatternFill(patternType='solid', fgColor=Color('808080')) # Border 모듈을 사용해서, 셀의 테두리 설정을 할 수 있습니다. # 셀의 왼쪽, 오른쪽, 윗쪽, 아래쪽에 대한 설정을 개별적으로 할 수 있습니다. box = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'), top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000')) # 설정한 테두리 값을 border 설정에 적용합니다. ws['A1'].border = box ws['B1'].border = box ws['C1'].border = box ws['D1'].border = box ws['E1'].border = box
def parser_conditional_formatting(self, element): rules = {} for cf in safe_iterator(element, '{%s}conditionalFormatting' % SHEET_MAIN_NS): if not cf.get('sqref'): # Potentially flag - this attribute should always be present. continue range_string = cf.get('sqref') cfRules = cf.findall('{%s}cfRule' % SHEET_MAIN_NS) rules[range_string] = [] for cfRule in cfRules: if not cfRule.get('type') or cfRule.get('type') == 'dataBar': # dataBar conditional formatting isn't supported, as it relies on the complex <extLst> tag continue rule = {'type': cfRule.get('type')} for attr in ConditionalFormatting.rule_attributes: if cfRule.get(attr) is not None: rule[attr] = cfRule.get(attr) formula = cfRule.findall('{%s}formula' % SHEET_MAIN_NS) for f in formula: if 'formula' not in rule: rule['formula'] = [] rule['formula'].append(f.text) colorScale = cfRule.find('{%s}colorScale' % SHEET_MAIN_NS) if colorScale is not None: rule['colorScale'] = {'cfvo': [], 'color': []} cfvoNodes = colorScale.findall('{%s}cfvo' % SHEET_MAIN_NS) for node in cfvoNodes: cfvo = {} if node.get('type') is not None: cfvo['type'] = node.get('type') if node.get('val') is not None: cfvo['val'] = node.get('val') rule['colorScale']['cfvo'].append(cfvo) colorNodes = colorScale.findall('{%s}color' % SHEET_MAIN_NS) for color in colorNodes: c = Color(Color.BLACK) if self.color_index\ and color.get('indexed') is not None\ and 0 <= int(color.get('indexed')) < len(self.color_index): c.index = self.color_index[int(color.get('indexed'))] if color.get('theme') is not None: if color.get('tint') is not None: c.index = 'theme:%s:%s' % (color.get('theme'), color.get('tint')) else: c.index = 'theme:%s:' % color.get('theme') # prefix color with theme elif color.get('rgb'): c.index = color.get('rgb') rule['colorScale']['color'].append(c) iconSet = cfRule.find('{%s}iconSet' % SHEET_MAIN_NS) if iconSet is not None: rule['iconSet'] = {'cfvo': []} for iconAttr in ConditionalFormatting.icon_attributes: if iconSet.get(iconAttr) is not None: rule['iconSet'][iconAttr] = iconSet.get(iconAttr) cfvoNodes = iconSet.findall('{%s}cfvo' % SHEET_MAIN_NS) for node in cfvoNodes: cfvo = {} if node.get('type') is not None: cfvo['type'] = node.get('type') if node.get('val') is not None: cfvo['val'] = node.get('val') rule['iconSet']['cfvo'].append(cfvo) rules[range_string].append(rule) if len(rules): self.ws.conditional_formatting.setRules(rules)