Python openpyxl.styles.PatternFill() Examples

The following are 12 code examples for showing how to use openpyxl.styles.PatternFill(). These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example.

You may check out the related API usage on the sidebar.

You may also want to check out all available functions/classes of the module openpyxl.styles , or try the search function .

Example 1
Project: StyleFrame   Author: DeepSpace2   File: styler.py    License: MIT License 7 votes vote down vote up
def to_openpyxl_style(self):
        try:
            openpyxl_style = self.cache[self]
        except KeyError:
            side = Side(border_style=self.border_type, color=utils.colors.black)
            border = Border(left=side, right=side, top=side, bottom=side)
            openpyxl_style = self.cache[self] = NamedStyle(
                name=str(hash(self)),
                font=Font(name=self.font, size=self.font_size, color=OpenPyColor(self.font_color),
                          bold=self.bold, underline=self.underline),
                fill=PatternFill(patternType=self.fill_pattern_type, fgColor=self.bg_color),
                alignment=Alignment(horizontal=self.horizontal_alignment, vertical=self.vertical_alignment,
                                    wrap_text=self.wrap_text, shrink_to_fit=self.shrink_to_fit,
                                    indent=self.indent, text_rotation=self.text_rotation),
                border=border,
                number_format=self.number_format,
                protection=Protection(locked=self.protection)
            )
        return openpyxl_style 
Example 2
Project: esdc-ce   Author: erigones   File: excel.py    License: Apache License 2.0 6 votes vote down vote up
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
                     ) 
Example 3
Project: SEO-Analysis   Author: devharsh   File: SEO.py    License: GNU General Public License v3.0 5 votes vote down vote up
def changecolor(count,col,lnth):
	if count == 0:
		rgb=[255,0,0]
		color_string="".join([str(hex(i))[2:].upper().rjust(2, "0") for i in rgb])
		sheet[col + index].fill=PatternFill(fill_type="solid", start_color='FF' + color_string, end_color='FF' + color_string)
			
	elif count > 0 and count < lnth:
		rgb=[255,255,0]
		color_string="".join([str(hex(i))[2:].upper().rjust(2, "0") for i in rgb])
		sheet[col + index].fill=PatternFill(fill_type="solid", start_color='FF' + color_string, end_color='FF' + color_string)
			
	else:
		rgb=[255,255,255]
		color_string="".join([str(hex(i))[2:].upper().rjust(2, "0") for i in rgb])
		sheet[col + index].fill=PatternFill(fill_type="solid", start_color='FF' + color_string, end_color='FF' + color_string) 
Example 4
Project: SEO-Analysis   Author: devharsh   File: SEO.py    License: GNU General Public License v3.0 5 votes vote down vote up
def headercount(tag,col,lnth):
	countall = 0
	countfull = 0
	countpart = 0
	
	for h in soup.findAll(tag):
		countall += 1
		counttemp = 0
		for kw in keywords:
			if kw.lower() in h.get_text().lower():
				counttemp += 1
		
		if counttemp == lnth:
			countfull += 1
		elif counttemp > 0 and counttemp < lnth:
			countpart += 1
			
	sheet[col + index] = str(countall) + ' - ' + str(countfull) + ' - ' + str(countpart)
		
	if countall == 0:
		rgb=[255,0,0]
		color_string="".join([str(hex(i))[2:].upper().rjust(2, "0") for i in rgb])
		sheet[col + index].fill=PatternFill(fill_type="solid", start_color='FF' + color_string, end_color='FF' + color_string)
	else:
		rgb=[255,255,255]
		color_string="".join([str(hex(i))[2:].upper().rjust(2, "0") for i in rgb])
		sheet[col + index].fill=PatternFill(fill_type="solid", start_color='FF' + color_string, end_color='FF' + color_string) 
Example 5
Project: AUNets   Author: BCV-Uniandes   File: utils.py    License: MIT License 5 votes vote down vote up
def cell2Fcolor(cell, color='red'):
    fill = PatternFill("solid", fgColor=getattr(colors, color.upper()))
    cell.fill = fill
    return cell 
Example 6
Project: recruit   Author: Frank-qlu   File: test_excel.py    License: Apache License 2.0 4 votes vote down vote up
def test_to_excel_styleconverter(self, merge_cells, ext, engine):
        from openpyxl import styles

        hstyle = {
            "font": {
                "color": '00FF0000',
                "bold": True,
            },
            "borders": {
                "top": "thin",
                "right": "thin",
                "bottom": "thin",
                "left": "thin",
            },
            "alignment": {
                "horizontal": "center",
                "vertical": "top",
            },
            "fill": {
                "patternType": 'solid',
                'fgColor': {
                    'rgb': '006666FF',
                    'tint': 0.3,
                },
            },
            "number_format": {
                "format_code": "0.00"
            },
            "protection": {
                "locked": True,
                "hidden": False,
            },
        }

        font_color = styles.Color('00FF0000')
        font = styles.Font(bold=True, color=font_color)
        side = styles.Side(style=styles.borders.BORDER_THIN)
        border = styles.Border(top=side, right=side, bottom=side, left=side)
        alignment = styles.Alignment(horizontal='center', vertical='top')
        fill_color = styles.Color(rgb='006666FF', tint=0.3)
        fill = styles.PatternFill(patternType='solid', fgColor=fill_color)

        number_format = '0.00'

        protection = styles.Protection(locked=True, hidden=False)

        kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle)
        assert kw['font'] == font
        assert kw['border'] == border
        assert kw['alignment'] == alignment
        assert kw['fill'] == fill
        assert kw['number_format'] == number_format
        assert kw['protection'] == protection 
Example 7
Project: ur-analysis-tools   Author: actionml   File: report.py    License: Apache License 2.0 4 votes vote down vote up
def create_table(ws, column_names, columns, title=None, bold_first_column=True, selected_rows=None, selected_columns=None):
    if selected_rows is None:
        selected_rows = []

    if selected_columns is None:
        selected_columns = []

    bold_ft = Font(bold=True)
    fill = PatternFill(fill_type='solid',
                       start_color='FF27E85B',
                       end_color='FF27E85B')

    #prepare data
    formated_columns = []
    for column in columns:
        formated_column = []
        for value in column:
            if isinstance(value, int):
                formated_column.append("{:,}".format(value))
            elif isinstance(value, float):
                formated_column.append("%.4f" % value)
            else:
                formated_column.append(value)
        formated_columns.append(formated_column)

    if title:
        ws.append([title])
        ws.cell(row=ws.max_row, column=1).font = bold_ft

    ws.append(column_names)
    for i in range(len(column_names)):
        ws.cell(row=ws.max_row, column=i+1).font = bold_ft

    if not formated_columns:
        return

    for i in range(len(formated_columns[0])):
        ws.append([column[i] for column in formated_columns])

        if bold_first_column:
            ws.cell(row=ws.max_row, column=1).font = bold_ft

        if i in selected_rows:
            for j in range(len(formated_columns)):
                ws.cell(row=ws.max_row, column=j+1).fill = fill

        for column_ind in selected_columns:
            ws.cell(row=ws.max_row, column=column_ind+1).fill = fill

    ws.append([]) 
Example 8
Project: ur-analysis-tools   Author: actionml   File: excel.py    License: Apache License 2.0 4 votes vote down vote up
def create_table(ws, column_names, columns, title=None, bold_first_column=True, selected_rows=None, selected_columns=None):
    if selected_rows is None:
        selected_rows = []

    if selected_columns is None:
        selected_columns = []

    bold_ft = Font(bold=True)
    fill = PatternFill(fill_type='solid',
                       start_color='FF27E85B',
                       end_color='FF27E85B')

    #prepare data
    formated_columns = []
    for column in columns:
        formated_column = []
        for value in column:
            if isinstance(value, int):
                formated_column.append("{:,}".format(value))
            elif isinstance(value, float):
                formated_column.append("%.4f" % value)
            else:
                formated_column.append(value)
        formated_columns.append(formated_column)

    if title:
        ws.append([title])
        ws.cell(row=ws.max_row, column=1).font = bold_ft

    ws.append(column_names)
    for i in range(len(column_names)):
        ws.cell(row=ws.max_row, column=i+1).font = bold_ft

    if not formated_columns:
        return

    for i in range(len(formated_columns[0])):
        ws.append([column[i] for column in formated_columns])

        if bold_first_column:
            ws.cell(row=ws.max_row, column=1).font = bold_ft

        if i in selected_rows:
            for j in range(len(formated_columns)):
                ws.cell(row=ws.max_row, column=j+1).fill = fill

        for column_ind in selected_columns:
            ws.cell(row=ws.max_row, column=column_ind+1).fill = fill

    ws.append([]) 
Example 9
Project: vnpy_crypto   Author: birforce   File: test_excel.py    License: MIT License 4 votes vote down vote up
def test_to_excel_styleconverter(self, merge_cells, ext, engine):
        from openpyxl import styles

        hstyle = {
            "font": {
                "color": '00FF0000',
                "bold": True,
            },
            "borders": {
                "top": "thin",
                "right": "thin",
                "bottom": "thin",
                "left": "thin",
            },
            "alignment": {
                "horizontal": "center",
                "vertical": "top",
            },
            "fill": {
                "patternType": 'solid',
                'fgColor': {
                    'rgb': '006666FF',
                    'tint': 0.3,
                },
            },
            "number_format": {
                "format_code": "0.00"
            },
            "protection": {
                "locked": True,
                "hidden": False,
            },
        }

        font_color = styles.Color('00FF0000')
        font = styles.Font(bold=True, color=font_color)
        side = styles.Side(style=styles.borders.BORDER_THIN)
        border = styles.Border(top=side, right=side, bottom=side, left=side)
        alignment = styles.Alignment(horizontal='center', vertical='top')
        fill_color = styles.Color(rgb='006666FF', tint=0.3)
        fill = styles.PatternFill(patternType='solid', fgColor=fill_color)

        number_format = '0.00'

        protection = styles.Protection(locked=True, hidden=False)

        kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle)
        assert kw['font'] == font
        assert kw['border'] == border
        assert kw['alignment'] == alignment
        assert kw['fill'] == fill
        assert kw['number_format'] == number_format
        assert kw['protection'] == protection 
Example 10
def test_to_excel_styleconverter(self, merge_cells, ext, engine):
        from openpyxl import styles

        hstyle = {
            "font": {
                "color": '00FF0000',
                "bold": True,
            },
            "borders": {
                "top": "thin",
                "right": "thin",
                "bottom": "thin",
                "left": "thin",
            },
            "alignment": {
                "horizontal": "center",
                "vertical": "top",
            },
            "fill": {
                "patternType": 'solid',
                'fgColor': {
                    'rgb': '006666FF',
                    'tint': 0.3,
                },
            },
            "number_format": {
                "format_code": "0.00"
            },
            "protection": {
                "locked": True,
                "hidden": False,
            },
        }

        font_color = styles.Color('00FF0000')
        font = styles.Font(bold=True, color=font_color)
        side = styles.Side(style=styles.borders.BORDER_THIN)
        border = styles.Border(top=side, right=side, bottom=side, left=side)
        alignment = styles.Alignment(horizontal='center', vertical='top')
        fill_color = styles.Color(rgb='006666FF', tint=0.3)
        fill = styles.PatternFill(patternType='solid', fgColor=fill_color)

        number_format = '0.00'

        protection = styles.Protection(locked=True, hidden=False)

        kw = _OpenpyxlWriter._convert_to_style_kwargs(hstyle)
        assert kw['font'] == font
        assert kw['border'] == border
        assert kw['alignment'] == alignment
        assert kw['fill'] == fill
        assert kw['number_format'] == number_format
        assert kw['protection'] == protection 
Example 11
Project: elasticintel   Author: securityclippy   File: test_excel.py    License: GNU General Public License v3.0 4 votes vote down vote up
def test_to_excel_styleconverter(self):
        import openpyxl
        from openpyxl import styles

        hstyle = {
            "font": {
                "color": '00FF0000',
                "bold": True,
            },
            "borders": {
                "top": "thin",
                "right": "thin",
                "bottom": "thin",
                "left": "thin",
            },
            "alignment": {
                "horizontal": "center",
                "vertical": "top",
            },
            "fill": {
                "patternType": 'solid',
                'fgColor': {
                    'rgb': '006666FF',
                    'tint': 0.3,
                },
            },
            "number_format": {
                "format_code": "0.00"
            },
            "protection": {
                "locked": True,
                "hidden": False,
            },
        }

        font_color = styles.Color('00FF0000')
        font = styles.Font(bold=True, color=font_color)
        side = styles.Side(style=styles.borders.BORDER_THIN)
        border = styles.Border(top=side, right=side, bottom=side, left=side)
        alignment = styles.Alignment(horizontal='center', vertical='top')
        fill_color = styles.Color(rgb='006666FF', tint=0.3)
        fill = styles.PatternFill(patternType='solid', fgColor=fill_color)

        # ahh openpyxl API changes
        ver = openpyxl.__version__
        if ver >= LooseVersion('2.0.0') and ver < LooseVersion('2.1.0'):
            number_format = styles.NumberFormat(format_code='0.00')
        else:
            number_format = '0.00'  # XXX: Only works with openpyxl-2.1.0

        protection = styles.Protection(locked=True, hidden=False)

        kw = _Openpyxl20Writer._convert_to_style_kwargs(hstyle)
        assert kw['font'] == font
        assert kw['border'] == border
        assert kw['alignment'] == alignment
        assert kw['fill'] == fill
        assert kw['number_format'] == number_format
        assert kw['protection'] == protection 
Example 12
Project: elasticintel   Author: securityclippy   File: test_excel.py    License: GNU General Public License v3.0 4 votes vote down vote up
def test_to_excel_styleconverter(self):
        from openpyxl import styles

        hstyle = {
            "font": {
                "color": '00FF0000',
                "bold": True,
            },
            "borders": {
                "top": "thin",
                "right": "thin",
                "bottom": "thin",
                "left": "thin",
            },
            "alignment": {
                "horizontal": "center",
                "vertical": "top",
            },
            "fill": {
                "patternType": 'solid',
                'fgColor': {
                    'rgb': '006666FF',
                    'tint': 0.3,
                },
            },
            "number_format": {
                "format_code": "0.00"
            },
            "protection": {
                "locked": True,
                "hidden": False,
            },
        }

        font_color = styles.Color('00FF0000')
        font = styles.Font(bold=True, color=font_color)
        side = styles.Side(style=styles.borders.BORDER_THIN)
        border = styles.Border(top=side, right=side, bottom=side, left=side)
        alignment = styles.Alignment(horizontal='center', vertical='top')
        fill_color = styles.Color(rgb='006666FF', tint=0.3)
        fill = styles.PatternFill(patternType='solid', fgColor=fill_color)

        number_format = '0.00'

        protection = styles.Protection(locked=True, hidden=False)

        kw = _Openpyxl22Writer._convert_to_style_kwargs(hstyle)
        assert kw['font'] == font
        assert kw['border'] == border
        assert kw['alignment'] == alignment
        assert kw['fill'] == fill
        assert kw['number_format'] == number_format
        assert kw['protection'] == protection