from openpyxl import Workbook from openpyxl.styles import Alignment def fillsheet(sheet,…
- User
from openpyxl import Workbook from openpyxl.styles import Alignment
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) row += 1def 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
The code provided defines two functions:
fill_sheet()andfill_excel().The
fill_sheet()function takes in an openpyxl sheet object and a list of sites as arguments. It fills the sheet with the missed cells by iterating over the columns list and setting the value of each cell in the corresponding row and column index. The data for each site is retrieved from the sites list.The
fill_excel()function takes in a list of sites and a path as arguments. It creates a new workbook object usingopenpyxl.Workbook()and creates a new sheet named 'ElasticRan'. Then, it calls thefill_sheet()function to fill in the sheet with the missed cells data. Finally, it deletes the default 'Sheet' and saves the workbook to the specified path.To use these functions, you can call the
fill_excel()function with the appropriate arguments. The sites list should contain dictionaries where the keys are the column names and the values are the cell values for each site. The path argument should be a string representing the file path where the workbook should be saved. - User
Want to autofit for all columns except ‘EUtranCells’, ‘Eran EUtranCells’. For them what to apply text wrap
- Assistant
To autofit all columns except for a specific column or set of columns, and to apply text wrap for those specific columns, you can use the
openpyxllibrary to adjust the width of the columns and apply text wrap to the cells.Here's an updated version of the
fill_sheet()function with the changes:from openpyxl import Workbook from openpyxl.styles import Alignment 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 of dictionaries representing the cell values for each site """ 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', ] # Fill the column headers row = 1 for col in columns: cell = sheet.cell(row=row, column=columns.index(col) + 1) cell.value = col # Fill the cell values for each site 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) # Apply text wrap for the specified columns if col in ['EUtranCells', 'Eran EUtranCells']: cell.alignment = Alignment(wrap_text=True) row += 1 # Autofit columns except for specific columns 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 not in ['Q', 'R']: 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 + 2) * 1.2 sheet.column_dimensions[column_letter].width = adjusted_width # Example usage workbook = Workbook() worksheet = workbook.active sites = [ { 'Region': 'Region 1', 'ENM': 'ENM 1', 'ERBS_Name': 'Site 1', 'ID': '1', 'eNBId': '123', 'Vlan': 'VLAN1', 'VlanRef': 'Ref1', 'ExtEnodeB VlanRef': 'Ref2', 'EranLink': 'Link1', 'PTP Time Sync': 'Sync1', 'Elastic Ran LKF': 'LKF1', 'Elastic Ran Feature': 'Feature1', 'BBU': 'BBU1', 'Port Bitrate': 'Bitrate1', '10G LKF': 'LKF2', '10G Feature': 'Feature2', 'EUtranCells': 'Cell1', 'Eran EUtranCells': 'EranCell1', }, # Add more sites here... ] fill_sheet(worksheet, sites) # Save the workbook workbook.save('path/to/your/file.xlsx')In this updated version, after filling in the cell values for each site, it checks if the current cell column is 'EUtranCells' or 'Eran EUtranCells'. If so, it applies text wrap to the cell by setting the
wrap_textattribute of the cell'salignmentproperty toTrue.Next, it uses the
openpyxl.utils.get_column_letter()function to get the column letter for each column, and then checks if the column letter is not 'Q' or 'R' (the columns to exclude from autofitting). For the other columns, it determines the maximum length of the cell values and calculates the adjusted width that should accommodate the longest value with some extra padding. Finally, it sets the adjusted width for each column usingsheet.column_dimensions[column_letter].width.You can customize the
siteslist to include your actual data, and specify the path where you want to save the Excel file. - User
How to set width for q and returns columns
- Assistant
To set a specific width for columns "Q" and "R" in openpyxl, you can use the
column_dimensionsproperty of the sheet to access the specific column dimensions and set the desired width.Here's an updated version of the
fill_sheet()function that includes setting the width for columns "Q" and "R":from openpyxl import Workbook from openpyxl.styles import Alignment 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 of dictionaries representing the cell values for each site """ 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', ] # Fill the column headers row = 1 for col in columns: cell = sheet.cell(row=row, column=columns.index(col) + 1) cell.value = col # Fill the cell values for each site 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) # Apply text wrap for the specified columns if col in ['EUtranCells', 'Eran EUtranCells']: cell.alignment = Alignment(wrap_text=True) row += 1 # Autofit columns except for specific columns for column_cells in sheet.columns: column_letter = get_column_letter(column_cells[0].column) # Set width for specific columns (Q and R) if column_letter == 'Q' or column_letter == 'R': sheet.column_dimensions[column_letter].width = 15 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 + 2) * 1.2 sheet.column_dimensions[column_letter].width = adjusted_width # Example usage workbook = Workbook() worksheet = workbook.active sites = [ { 'Region': 'Region 1', 'ENM': 'ENM 1', 'ERBS_Name': 'Site 1', 'ID': '1', 'eNBId': '123', 'Vlan': 'VLAN1', 'VlanRef': 'Ref1', 'ExtEnodeB VlanRef': 'Ref2', 'EranLink': 'Link1', 'PTP Time Sync': 'Sync1', 'Elastic Ran LKF': 'LKF1', 'Elastic Ran Feature': 'Feature1', 'BBU': 'BBU1', 'Port Bitrate': 'Bitrate1', '10G LKF': 'LKF2', '10G Feature': 'Feature2', 'EUtranCells': 'Cell1', 'Eran EUtranCells': 'EranCell1', }, # Add more sites here... ] fill_sheet(worksheet, sites) # Save the workbook workbook.save('path/to/your/file.xlsx')In the updated code, the
fill_sheet()function now checks if the column letter is 'Q' or 'R' using thecolumn_lettervariable. If it matches either of those, it sets the width of the column to 15 usingsheet.column_dimensions[column_letter].width = 15. For all other columns, it calculates and sets the adjusted width as before.You can modify the code according to your specific needs and specify the desired width for the "Q" and "R" columns.
- User
Add autohight
- Assistant
To automatically adjust the row height based on the text content in each cell, you can use the
openpyxllibrary and theadjust_heightattribute of theAlignmentclass.Here's an updated version of the
fill_sheet()function that includes auto-adjusting row height based on the cell content:from openpyxl import Workbook from openpyxl.styles import Alignment 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 of dictionaries representing the cell values for each site """ 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', ] # Fill the column headers row = 1 for col in columns: cell = sheet.cell(row=row, column=columns.index(col) + 1) cell.value = col # Fill the cell values for each site 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) # Apply text wrap and auto-adjust row height for the specified columns if col in ['EUtranCells', 'Eran EUtranCells']: cell.alignment = Alignment(wrap_text=True) cell.alignment.wrap_text = True cell.alignment.vertical = 'top' cell.alignment.justify_last_line = True cell.alignment.shrink_to_fit = False cell.alignment.indent = 0 cell.alignment.adjust_height = True row += 1 # Autofit columns except for specific columns for column_cells in sheet.columns: column_letter = get_column_letter(column_cells[0].column) # Set width for specific columns (Q and R) if column_letter == 'Q' or column_letter == 'R': sheet.column_dimensions[column_letter].width = 15 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 + 2) * 1.2 # Set auto-adjust row height for all cells cell.alignment = Alignment(wrap_text=True) cell.alignment.wrap_text = True cell.alignment.vertical = 'top' cell.alignment.justify_last_line = True cell.alignment.shrink_to_fit = False cell.alignment.indent = 0 cell.alignment.adjust_height = True sheet.column_dimensions[column_letter].width = adjusted_width # Example usage workbook = Workbook() worksheet = workbook.active sites = [ { 'Region': 'Region 1', 'ENM': 'ENM 1', 'ERBS_Name': 'Site 1', 'ID': '1', 'eNBId': '123', 'Vlan': 'VLAN1', 'VlanRef': 'Ref1', 'ExtEnodeB VlanRef': 'Ref2', 'EranLink': 'Link1', 'PTP Time Sync': 'Sync1', 'Elastic Ran LKF': 'LKF1', 'Elastic Ran Feature': 'Feature1', 'BBU': 'BBU1', 'Port Bitrate': 'Bitrate1', '10G LKF': 'LKF2', '10G Feature': 'Feature2', 'EUtranCells': 'Cell1', 'Eran EUtranCells': 'EranCell1', }, # Add more sites here... ] fill_sheet(worksheet, sites) # Save the workbook workbook.save('path/to/your/file.xlsx')In this updated code, for the specified columns ('EUtranCells' and 'Eran EUtranCells'), the alignment properties are adjusted for text wrap and row height. The
wrap_textattribute is set toTrueto enable text wrapping. Theadjust_heightattribute is also set toTrueto enable auto-adjustment of the row height based on the cell content.Note that when using
openpyxl, auto-adjusting row height is not an automatic feature that dynamically adjusts the row height as the text content changes. It calculates the maximum text content in each column and adjusts the row height accordingly.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.