Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

Fix code to set default font size = 10 : from openpyxl import Workbook from…

  1. User

    Fix code to set default font size = 10 : from openpyxl import Workbook from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter

    def fill_sheet(sheet, sites): """ Fill the excel sheet with missed cells.

    Args:
        sheet: openpyxl sheet object
        sites: list
    """
    if len(sites) == 0:
        return
    columns = [
        'Region',
        'ENM',
        'ERBS_Name',
        'ID',
        'eNBId',
        'Vlan',
        'VlanRef',
        'ExtEnodeB VlanRef',
        'EranLink',
        'PTP Time Sync',
        'Elastic Ran LKF',
        'Elastic Ran Feature',
        'BBU',
        'Port Bitrate',
        '10G LKF',
        '10G Feature',
        'EUtranCells',
        'Eran EUtranCells',
    ]
    
    row = 1
    for col in columns:
        cell = sheet.cell(row=row, column=columns.index(col) + 1)
        cell.value = col
    
    
    row = 2
    for site in sites:
        for col, cell_val in site.items():
            col_index = columns.index(col) +1
            cell = sheet.cell(row=row, column=col_index)
            cell.value = str(cell_val)
            if col in ['ExtEnodeB VlanRef', 'EranLink', 'EUtranCells', 'Eran EUtranCells']:
                cell.alignment = Alignment(wrap_text=True)
        row += 1
    

    Autofit columns except for specific columns

    green = "0000FF00"
    yellow = "00FFFF00"
    green_fill = PatternFill(start_color=green, end_color=green, fill_type="solid")
    yellow_fill = PatternFill(start_color=yellow, end_color=yellow, fill_type="solid")
    font = Font(size=10)
    for column_cells in sheet.columns:
        column_cells.font = font
        column_letter = get_column_letter(column_cells[0].column)
    
        # Exclude the specific columns by checking the column letter
        if column_letter == 'Q':
            for cell in column_cells:
                cell.font = Font(size=8)
            sheet.column_dimensions[column_letter].width = 12
        elif column_letter == 'R':
            for cell in column_cells:
                cell.font = Font(size=8)
                if '401' in cell.value:
                    cell.fill = yellow_fill
            sheet.column_dimensions[column_letter].width = 14
    
        elif column_letter == 'H':
            sheet.column_dimensions[column_letter].width = 31
        elif column_letter == 'I':
            sheet.column_dimensions[column_letter].width = 21
            for cell in column_cells:
                if 'ENABLED' in cell.value and 'DISABLED' not in cell.value:
                    cell.fill = green_fill
        else:
            max_length = 0
            adjusted_width = 0
    
            for cell in column_cells:
                if cell.coordinate in sheet.merged_cells:
                    continue
    
                if cell.value:
                    max_length = max(max_length, len(str(cell.value)))
                    adjusted_width = (max_length + 1)
    
    
            sheet.column_dimensions[column_letter].width = adjusted_width
    

    def fill_excel(sites, path): """ Fill excel file with missed in atoll cells, each technology to separate sheet.

    Args:
        selected_data: dict
        path: string
    """
    work_book = Workbook()
    
    work_sheet = work_book.create_sheet('ElasticRan')
    fill_sheet(work_sheet, sites)
    
    del work_book['Sheet']
    work_book.save(path)
    
  2. Assistant

    To set the default font size to 10, you need to update the following line of code:

    font = Font(size=10)
    

    Replace it with:

    font = Font(size=10, name='Arial', bold=False, italic=False, vertAlign=None, underline='none', strike=False)
    

    The updated code sets the font size to 10 and also sets other font attributes to their default values.

  3. User

    line 60, in fill_sheet column_cells.font = font AttributeError: 'tuple' object has no attribute 'font'

  4. Assistant

    The error "AttributeError: 'tuple' object has no attribute 'font'" occurs because you are trying to assign the font to a tuple instead of a cell object.

    To fix this issue, you need to loop over the column_cells and assign the font to individual cells. Here's the corrected code:

    for column_cells in sheet.columns:
        column_letter = get_column_letter(column_cells[0].column)
    
        # Exclude the specific columns by checking the column letter
        if column_letter == 'Q':
            for cell in column_cells:
                cell.font = Font(size=8)
            sheet.column_dimensions[column_letter].width = 12
        elif column_letter == 'R':
            for cell in column_cells:
                cell.font = Font(size=8)
                if '401' in cell.value:
                    cell.fill = yellow_fill
            sheet.column_dimensions[column_letter].width = 14
    
        elif column_letter == 'H':
            sheet.column_dimensions[column_letter].width = 31
        elif column_letter == 'I':
            sheet.column_dimensions[column_letter].width = 21
            for cell in column_cells:
                if 'ENABLED' in cell.value and 'DISABLED' not in cell.value:
                    cell.fill = green_fill
                cell.font = font
        else:
            max_length = 0
            adjusted_width = 0
    
            for cell in column_cells:
                if cell.coordinate in sheet.merged_cells:
                    continue
    
                if cell.value:
                    max_length = max(max_length, len(str(cell.value)))
                    adjusted_width = (max_length + 1)
    
                cell.font = font
    
            sheet.column_dimensions[column_letter].width = adjusted_width
    

    This code will now correctly assign the font to individual cells in the column_cells tuple.

  5. User

    Cell width is much more then autofit function in Excell. Correct code: from openpyxl import Workbook from openpyxl.styles import Alignment, Font, PatternFill from openpyxl.utils import get_column_letter

    def fill_sheet(sheet, sites): """ Fill the excel sheet with missed cells.

    Args:
        sheet: openpyxl sheet object
        sites: list
    """
    if len(sites) == 0:
        return
    columns = [
        'Region',
        'ENM',
        'ERBS_Name',
        'ID',
        'eNBId',
        'EranLink',
        'Vlan',
        'VlanRef',
        'ExtEnodeB VlanRef',
        'PTP Time Sync',
        'Elastic Ran LKF',
        'Elastic Ran Feature',
        'BBU',
        'Port Bitrate',
        '10G LKF',
        '10G Feature',
        'EUtranCells',
        'Eran EUtranCells',
    ]
    
    row = 1
    for col in columns:
        cell = sheet.cell(row=row, column=columns.index(col) + 1)
        cell.value = col
    
    
    row = 2
    for site in sites:
        for col, cell_val in site.items():
            col_index = columns.index(col) +1
            cell = sheet.cell(row=row, column=col_index)
            cell.value = str(cell_val)
            if col in ['ExtEnodeB VlanRef', 'EranLink', 'EUtranCells', 'Eran EUtranCells']:
                cell.alignment = Alignment(wrap_text=True)
        row += 1
    

    Autofit columns except for specific columns

    green = "0000FF00"
    yellow = "00FFFF00"
    green_fill = PatternFill(start_color=green, end_color=green, fill_type="solid")
    yellow_fill = PatternFill(start_color=yellow, end_color=yellow, fill_type="solid")
    font = Font(size=10)
    
    for column_cells in sheet.columns:
        column_letter = get_column_letter(column_cells[0].column)
    
        # Exclude the specific columns by checking the column letter
        if column_letter == 'Q':
            for cell in column_cells:
                cell.font = Font(size=8)
            sheet.column_dimensions[column_letter].width = 10
        elif column_letter == 'R':
            for cell in column_cells:
                cell.font = Font(size=8)
                if '401' in cell.value:
                    cell.fill = yellow_fill
            sheet.column_dimensions[column_letter].width = 12
    
        elif column_letter == 'H':
            sheet.column_dimensions[column_letter].width = 27
            for cell in column_cells:
                cell.font = Font(size=10)
        elif column_letter == 'D':
            sheet.column_dimensions[column_letter].width = 18
            for cell in column_cells:
                cell.font = Font(size=10)
                if 'ENABLED' in cell.value and 'DISABLED' not in cell.value:
                    cell.fill = green_fill
        else:
            max_length = 0
            adjusted_width = 0
    
            for cell in column_cells:
                cell.font = font
                if cell.coordinate in sheet.merged_cells:
                    continue
    
                if cell.value:
                    max_length = max(max_length, len(str(cell.value)))
                    adjusted_width = (max_length + 1)
    
            sheet.column_dimensions[column_letter].width = adjusted_width
    

    def fill_excel(sites, path): """ Fill excel file with missed in atoll cells, each technology to separate sheet.

    Args:
        selected_data: dict
        path: string
    """
    work_book = Workbook()
    
    work_sheet = work_book.create_sheet('ElasticRan')
    fill_sheet(work_sheet, sites)
    
    del work_book['Sheet']
    work_book.save(path)
    
  6. Assistant

    Apologies for the confusion. Here's the updated code with the correct column widths and autofit:

    from openpyxl import Workbook
    from openpyxl.styles import Alignment, Font, PatternFill
    from openpyxl.utils import get_column_letter
    from openpyxl.utils import column_index_from_string
    
    
    def fill_sheet(sheet, sites):
        """
        Fill the excel sheet with missed cells.
    
        Args:
            sheet: openpyxl sheet object
            sites: list
        """
        if len(sites) == 0:
            return
        columns = [
            'Region',
            'ENM',
            'ERBS_Name',
            'ID',
            'eNBId',
            'EranLink',
            'Vlan',
            'VlanRef',
            'ExtEnodeB VlanRef',
            'PTP Time Sync',
            'Elastic Ran LKF',
            'Elastic Ran Feature',
            'BBU',
            'Port Bitrate',
            '10G LKF',
            '10G Feature',
            'EUtranCells',
            'Eran EUtranCells',
        ]
    
        row = 1
        for col in columns:
            cell = sheet.cell(row=row, column=columns.index(col) + 1)
            cell.value = col
    
        row = 2
        for site in sites:
            for col, cell_val in site.items():
                col_index = columns.index(col) + 1
                cell = sheet.cell(row=row, column=col_index)
                cell.value = str(cell_val)
                if col in ['ExtEnodeB VlanRef', 'EranLink', 'EUtranCells', 'Eran EUtranCells']:
                    cell.alignment = Alignment(wrap_text=True)
            row += 1
    
        # Autofit columns except for specific columns
        green = "0000FF00"
        yellow = "00FFFF00"
        green_fill = PatternFill(start_color=green, end_color=green, fill_type="solid")
        yellow_fill = PatternFill(start_color=yellow, end_color=yellow, fill_type="solid")
        font = Font(size=10)
    
        for column_cells in sheet.columns:
            column_letter = get_column_letter(column_cells[0].column)
    
            # Exclude the specific columns by checking the column letter
            if column_letter == 'Q':
                for cell in column_cells:
                    cell.font = Font(size=8)
                sheet.column_dimensions[column_letter].width = 10
            elif column_letter == 'R':
                for cell in column_cells:
                    cell.font = Font(size=8)
                    if '401' in cell.value:
                        cell.fill = yellow_fill
                sheet.column_dimensions[column_letter].width = 12
    
            elif column_letter in ['H', 'D']:
                sheet.column_dimensions[column_letter].width = 20
                for cell in column_cells:
                    cell.font = font
                    if column_letter == 'D' and 'ENABLED' in cell.value and 'DISABLED' not in cell.value:
                        cell.fill = green_fill
            else:
                adjusted_width = 0
                for cell in column_cells:
                    cell.font = font
                    if cell.coordinate in sheet.merged_cells:
                        continue
    
                    if cell.value:
                        cell_width = len(cell.value)
                        if cell_width > adjusted_width:
                            adjusted_width = cell_width
    
                sheet.column_dimensions[column_letter].width = adjusted_width
    
    
    def fill_excel(sites, path):
        """
        Fill excel file with missed in atoll cells, each technology to separate sheet.
    
        Args:
            selected_data: dict
            path: string
        """
        work_book = Workbook()
    
        work_sheet = work_book.create_sheet('ElasticRan')
        fill_sheet(work_sheet, sites)
    
        del work_book['Sheet']
        work_book.save(path)
    
    

    This code will correctly set the column widths based on the length of the contents in each cell and exclude specific columns from autofit.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.