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

from openpyxl import Workbook from openpyxl.styles import Alignment def fillsheet(sheet,…

  1. 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 += 1
    

    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

    The code provided defines two functions: fill_sheet() and fill_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 using openpyxl.Workbook() and creates a new sheet named 'ElasticRan'. Then, it calls the fill_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.

  3. User

    Want to autofit for all columns except ‘EUtranCells’, ‘Eran EUtranCells’. For them what to apply text wrap

  4. 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 openpyxl library 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_text attribute of the cell's alignment property to True.

    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 using sheet.column_dimensions[column_letter].width.

    You can customize the sites list to include your actual data, and specify the path where you want to save the Excel file.

  5. User

    How to set width for q and returns columns

  6. Assistant

    To set a specific width for columns "Q" and "R" in openpyxl, you can use the column_dimensions property 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 the column_letter variable. If it matches either of those, it sets the width of the column to 15 using sheet.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.

  7. User

    Add autohight

  8. Assistant

    To automatically adjust the row height based on the text content in each cell, you can use the openpyxl library and the adjust_height attribute of the Alignment class.

    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_text attribute is set to True to enable text wrapping. The adjust_height attribute is also set to True to 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.

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