Python openpyxl.styles.Alignment() Examples

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 

def test_to_excel_styleconverter(self, merge_cells, ext, engine):
        import xlwt

        hstyle = {"font": {"bold": True},
                  "borders": {"top": "thin",
                              "right": "thin",
                              "bottom": "thin",
                              "left": "thin"},
                  "alignment": {"horizontal": "center", "vertical": "top"}}

        xls_style = _XlwtWriter._convert_to_style(hstyle)
        assert xls_style.font.bold
        assert xlwt.Borders.THIN == xls_style.borders.top
        assert xlwt.Borders.THIN == xls_style.borders.right
        assert xlwt.Borders.THIN == xls_style.borders.bottom
        assert xlwt.Borders.THIN == xls_style.borders.left
        assert xlwt.Alignment.HORZ_CENTER == xls_style.alignment.horz
        assert xlwt.Alignment.VERT_TOP == xls_style.alignment.vert 

def _convert_to_alignment(cls, alignment_dict):
        """
        Convert ``alignment_dict`` to an openpyxl v2 Alignment object
        Parameters
        ----------
        alignment_dict : dict
            A dict with zero or more of the following keys (or their synonyms).
                'horizontal'
                'vertical'
                'text_rotation'
                'wrap_text'
                'shrink_to_fit'
                'indent'
        Returns
        -------
        alignment : openpyxl.styles.Alignment
        """

        from openpyxl.styles import Alignment

        return Alignment(**alignment_dict) 

def test_to_excel_styleconverter(self, merge_cells, ext, engine):
        import xlwt

        hstyle = {"font": {"bold": True},
                  "borders": {"top": "thin",
                              "right": "thin",
                              "bottom": "thin",
                              "left": "thin"},
                  "alignment": {"horizontal": "center", "vertical": "top"}}

        xls_style = _XlwtWriter._convert_to_style(hstyle)
        assert xls_style.font.bold
        assert xlwt.Borders.THIN == xls_style.borders.top
        assert xlwt.Borders.THIN == xls_style.borders.right
        assert xlwt.Borders.THIN == xls_style.borders.bottom
        assert xlwt.Borders.THIN == xls_style.borders.left
        assert xlwt.Alignment.HORZ_CENTER == xls_style.alignment.horz
        assert xlwt.Alignment.VERT_TOP == xls_style.alignment.vert 

def _convert_to_alignment(cls, alignment_dict):
        """
        Convert ``alignment_dict`` to an openpyxl v2 Alignment object
        Parameters
        ----------
        alignment_dict : dict
            A dict with zero or more of the following keys (or their synonyms).
                'horizontal'
                'vertical'
                'text_rotation'
                'wrap_text'
                'shrink_to_fit'
                'indent'
        Returns
        -------
        alignment : openpyxl.styles.Alignment
        """

        from openpyxl.styles import Alignment

        return Alignment(**alignment_dict) 

def test_to_excel_styleconverter(self, merge_cells, ext, engine):
        import xlwt

        hstyle = {"font": {"bold": True},
                  "borders": {"top": "thin",
                              "right": "thin",
                              "bottom": "thin",
                              "left": "thin"},
                  "alignment": {"horizontal": "center", "vertical": "top"}}

        xls_style = _XlwtWriter._convert_to_style(hstyle)
        assert xls_style.font.bold
        assert xlwt.Borders.THIN == xls_style.borders.top
        assert xlwt.Borders.THIN == xls_style.borders.right
        assert xlwt.Borders.THIN == xls_style.borders.bottom
        assert xlwt.Borders.THIN == xls_style.borders.left
        assert xlwt.Alignment.HORZ_CENTER == xls_style.alignment.horz
        assert xlwt.Alignment.VERT_TOP == xls_style.alignment.vert 

def _convert_to_alignment(cls, alignment_dict):
        """
        Convert ``alignment_dict`` to an openpyxl v2 Alignment object
        Parameters
        ----------
        alignment_dict : dict
            A dict with zero or more of the following keys (or their synonyms).
                'horizontal'
                'vertical'
                'text_rotation'
                'wrap_text'
                'shrink_to_fit'
                'indent'
        Returns
        -------
        alignment : openpyxl.styles.Alignment
        """

        from openpyxl.styles import Alignment

        return Alignment(**alignment_dict) 

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 _convert_to_alignment(cls, alignment_dict):
        """
        Convert ``alignment_dict`` to an openpyxl v2 Alignment object
        Parameters
        ----------
        alignment_dict : dict
            A dict with zero or more of the following keys (or their synonyms).
                'horizontal'
                'vertical'
                'text_rotation'
                'wrap_text'
                'shrink_to_fit'
                'indent'
        Returns
        -------
        alignment : openpyxl.styles.Alignment
        """

        from openpyxl.styles import Alignment

        return Alignment(**alignment_dict) 

def _convert_to_alignment(cls, alignment_dict):
        """
        Convert ``alignment_dict`` to an openpyxl v2 Alignment object
        Parameters
        ----------
        alignment_dict : dict
            A dict with zero or more of the following keys (or their synonyms).
                'horizontal'
                'vertical'
                'text_rotation'
                'wrap_text'
                'shrink_to_fit'
                'indent'
        Returns
        -------
        alignment : openpyxl.styles.Alignment
        """

        from openpyxl.styles import Alignment

        return Alignment(**alignment_dict) 

def test_to_excel_styleconverter(self):
        _skip_if_no_xlwt()

        import xlwt

        hstyle = {"font": {"bold": True},
                  "borders": {"top": "thin",
                              "right": "thin",
                              "bottom": "thin",
                              "left": "thin"},
                  "alignment": {"horizontal": "center", "vertical": "top"}}

        xls_style = _XlwtWriter._convert_to_style(hstyle)
        assert xls_style.font.bold
        assert xlwt.Borders.THIN == xls_style.borders.top
        assert xlwt.Borders.THIN == xls_style.borders.right
        assert xlwt.Borders.THIN == xls_style.borders.bottom
        assert xlwt.Borders.THIN == xls_style.borders.left
        assert xlwt.Alignment.HORZ_CENTER == xls_style.alignment.horz
        assert xlwt.Alignment.VERT_TOP == xls_style.alignment.vert 

def write_structure_node_for_excel(api, sheet, level, start_column, index, entity, add_link, linked_cells):
    row = construct_structure_row(0, entity)
    write_row_for_excel(sheet, start_column, index, row)
    cell = sheet.cell(row=index, column=column_index_from_string(start_column))
    cell.alignment = Alignment(indent=level)
    if entity.get_type() == Entity.IP4Network and True == add_link:
        cell.hyperlink = "#'" + get_sheet_name(entity) + "'!" + col_config['excel']['title_cell_index']
        cell.font = LINK_FONT
        linked_cells[str(entity.get_id())] = "#'" + sheet.title + "'!" + start_column + str(index) 

def output_courses_into_xlsx(courses, filepath):
   if not courses:
       raise ValueError('There must be at least one course to save')
   workbook = Workbook()
   worksheet = workbook.active
   worksheet.title = 'Coursera courses info'
   worksheet.merge_cells('A1:E1')
   worksheet.append([worksheet.title])
   worksheet['A1'].alignment = Alignment(horizontal='center')
   worksheet.append(list(courses[0].keys()))
   for course in courses:
       worksheet.append(list(course.values()))
   workbook.save(filepath) 

def write_structure_node_for_excel(api, sheet, level, start_column, index, entity, add_link, linked_cells):
    row = construct_structure_row(0, entity)
    write_row_for_excel(sheet, start_column, index, row)
    cell = sheet.cell(row=index, column=column_index_from_string(start_column))
    cell.alignment = Alignment(indent=level)
    if entity.get_type() == Entity.Zone and True == add_link:
        cell.hyperlink = "#'" + get_sheet_name(entity) + "'!" + col_config['excel']['title_cell_index']
        cell.font = LINK_FONT
        linked_cells[str(entity.get_id())] = "#'" + sheet.title + "'!" + start_column + str(index) 

def dataPlacer(data,sheet, startingRow, column, columnKey,
    columnValue, horizonalAlignmentKey=False, horizonalAlignmentValue=False,
    changeKeyDimensions=True, changeValueDimensions=True):

    maxLengthKey = 0
    maxLengthValue = 0

    for key, value in data.items():
        keyPosition = sheet.cell(column=column, row=startingRow, value=key)
        valuePosition = sheet.cell(column=column+1, row=startingRow, value=value)
        startingRow += 1

        if horizonalAlignmentKey:
            keyPosition.alignment = Alignment(horizontal=horizonalAlignmentKey) 
        
        if horizonalAlignmentValue:
            valuePosition.alignment = Alignment(horizontal=horizonalAlignmentValue)

        lengthKey = len(str(keyPosition.value))
        lengthValue = len(str(valuePosition.value))

        if lengthKey > maxLengthKey:
            maxLengthKey = lengthKey

        if lengthValue > maxLengthValue:
            maxLengthValue = lengthValue

    if changeKeyDimensions:
        sheet.column_dimensions[columnKey].width    = maxLengthKey * 1.2
    
    if changeValueDimensions:
        sheet.column_dimensions[columnValue].width  = maxLengthValue * 1.2 

def _query_results_to_xlsx(query_results: DocumentQueryResults) -> HttpResponse:
    from openpyxl import Workbook
    from openpyxl.writer.excel import save_virtual_workbook
    from openpyxl.styles import Font, Alignment

    wb = Workbook()
    ws = wb.active
    ws.append(query_results.column_titles)
    for cells in ws.rows:
        for cell in cells:
            cell.font = Font(name=cell.font.name, bold=True)
            cell.alignment = Alignment(horizontal='center')
        break

    for row in query_results.fetch():
        ws.append(row)

    def str_len(value):
        return len(str(value)) if value is not None else 0

    for column_cells in ws.columns:
        length = min(max(str_len(cell.value) for cell in column_cells), 100) + 1
        ws.column_dimensions[column_cells[0].column_letter].width = length

    response = HttpResponse(save_virtual_workbook(wb),
                            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    response['Content-Disposition'] = 'attachment; filename=export.xlsx'
    return response 

def test_to_excel_styleconverter(self):
        _skip_if_no_openpyxl()
        if not openpyxl_compat.is_compat(major_ver=1):
            pytest.skip('incompatible openpyxl version')

        import openpyxl

        hstyle = {"font": {"bold": True},
                  "borders": {"top": "thin",
                              "right": "thin",
                              "bottom": "thin",
                              "left": "thin"},
                  "alignment": {"horizontal": "center", "vertical": "top"}}

        xlsx_style = _Openpyxl1Writer._convert_to_style(hstyle)
        assert xlsx_style.font.bold
        assert (openpyxl.style.Border.BORDER_THIN ==
                xlsx_style.borders.top.border_style)
        assert (openpyxl.style.Border.BORDER_THIN ==
                xlsx_style.borders.right.border_style)
        assert (openpyxl.style.Border.BORDER_THIN ==
                xlsx_style.borders.bottom.border_style)
        assert (openpyxl.style.Border.BORDER_THIN ==
                xlsx_style.borders.left.border_style)
        assert (openpyxl.style.Alignment.HORIZONTAL_CENTER ==
                xlsx_style.alignment.horizontal)
        assert (openpyxl.style.Alignment.VERTICAL_TOP ==
                xlsx_style.alignment.vertical) 

def export_xlsx(modeladmin, request, queryset):
    wb = Workbook()
    ws = wb.active
    ws.title = "Products"

    number_alignment = Alignment(horizontal="right")
    wb.add_named_style(
        NamedStyle(
            "Identifier", alignment=number_alignment, number_format=FORMAT_NUMBER
        )
    )
    wb.add_named_style(
        NamedStyle("Normal Wrapped", alignment=Alignment(wrap_text=True))
    )

    column_config = {
        "A": ColumnConfig("ID", width=10, style="Identifier"),
        "B": ColumnConfig("Title", width=30),
        "C": ColumnConfig("Description", width=60),
        "D": ColumnConfig("Price", width=15, style="Currency", number_format="#,##0.00 €"),
        "E": ColumnConfig("Preview", width=100, style="Hyperlink"),
    }

    # Set up column widths, header values and styles
    for col, conf in column_config.items():
        ws.column_dimensions[col].width = conf.width

        column = ws[f"{col}1"]
        column.value = conf.heading
        column.style = conf.heading_style

    # Add products
    for obj in queryset.order_by("pk"):
        project_photos = obj.productphoto_set.all()[:1]
        url = ""
        if project_photos:
            url = project_photos[0].photo.url

        data = [obj.pk, obj.title, obj.description, obj.price, url]
        ws.append(data)

        row = ws.max_row
        for row_cells in ws.iter_cols(min_row=row, max_row=row):
            for cell in row_cells:
                conf = column_config[cell.column_letter]
                cell.style = conf.style
                if conf.number_format:
                    cell.number_format = conf.number_format

    mimetype = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    charset = "utf-8"
    response = HttpResponse(
        content=save_virtual_workbook(wb),
        content_type=f"{mimetype}; charset={charset}",
        charset=charset,
    )
    response["Content-Disposition"] = "attachment; filename=products.xlsx"
    return response 

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 

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 

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 

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 

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