Fix code to set default font size = 10 : from openpyxl import Workbook from…
- 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 += 1Autofit 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_widthdef 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) - 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.
- User
line 60, in fill_sheet column_cells.font = font AttributeError: 'tuple' object has no attribute 'font'
- 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_cellsand 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_widthThis code will now correctly assign the font to individual cells in the
column_cellstuple. - 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 += 1Autofit 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_widthdef 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) - 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.