Explain python code: """Fill excel file with cells missing in atoll.""" from openpyxl…
- User
Explain python code: """Fill excel file with cells missing in atoll."""
from openpyxl import Workbook
def fill_sheet(sheet, missed_cells): """ Fill the excel sheet with missed cells.
Args: sheet: openpyxl sheet object missed_cells: list """ if len(missed_cells) == 0: return columns = missed_cells[0]._fields row = 1 for col in columns: sheet.cell( row=row, column=columns.index(col) + 1, value=col, ) row = 2 for missed_cell in missed_cells: for cell_val in missed_cell: sheet.cell( row=row, column=missed_cell.index(cell_val) + 1, value=cell_val, ) row += 1def fill_excel(selected_data, path): """ Fill excel file with missed in atoll cells, each technology to separate sheet.
Args: selected_data: dict path: string """ work_book = Workbook() for tech, missed_cells in selected_data.items(): work_sheet = work_book.create_sheet(tech) fill_sheet(work_sheet, missed_cells) del work_book['Sheet'] work_book.save(path) - Assistant
This Python code is used to fill an Excel file with cells that are missing in the atoll. The code uses the openpyxl library for working with Excel files.
The code consists of three functions:
-
fill_sheet: This function fills an Excel sheet with missed cells. It takes two arguments,sheetwhich is an openpyxl sheet object, andmissed_cellswhich is a list containing the missed cells.- First, it checks if there are any missed cells. If the length of
missed_cellsis 0, it returns. - Then, it gets the column names from the
fieldsattribute of the first element inmissed_cells. - It initializes the
rowvariable to 1 and iterates over the column names. - For each column, it sets the value of the corresponding cell in the first row to the column name.
- After that, it initializes the
rowvariable to 2 and iterates over themissed_cells. - For each
missed_cell, it sets the value of each cell in the row to the corresponding value inmissed_cell. The column index is determined using theindexfunction ofmissed_celland adding 1. - Finally, it increments the
rowvariable.
- First, it checks if there are any missed cells. If the length of
-
fill_excel: This function fills an Excel file with missed cells for each technology in a separate sheet. It takes two arguments,selected_datawhich is a dictionary containing the selected data, andpathwhich is the path to save the Excel file.- It initializes a new Workbook object using
openpyxl. - Then, it iterates over the technologies and the missed cells in
selected_data. - For each technology, it creates a new sheet in the workbook using
create_sheetmethod and assigns it towork_sheet. - It calls the
fill_sheetfunction to fill thework_sheetwith the corresponding missed cells. - After iterating over all the technologies, it deletes the default 'Sheet' which is created automatically by
openpyxl. - Finally, it saves the workbook to the provided
path.
- It initializes a new Workbook object using
The complete code fills an Excel file with the missed cells for each technology in separate sheets and saves it to the specified path.
-
- User
Change columns by this list: 'Region' 'ENM' 'ERBS_Name' 'ID' 'eNBId' 'Vlan' 'VlaRef' 'ExtEnodeB VlanRef' 'EranLink' 'Port Bitrate' 'PTP Time Sync' 'BBU' '10G LKF' '10G Feature' 'Elastic Ran LKF' 'Elastic Ran Feature' 'EUtranCells' 'Eran EUtranCells'
- Assistant
To change the columns in the
fill_sheetfunction, you can modify the line where thecolumnsvariable is assigned. Replace the line:columns = missed_cells[0]._fieldswith:
columns = ['Region', 'ENM', 'ERBS_Name', 'ID', 'eNBId', 'Vlan', 'VlaRef', 'ExtEnodeB VlanRef', 'EranLink', 'Port Bitrate', 'PTP Time Sync', 'BBU', '10G LKF', '10G Feature', 'Elastic Ran LKF', 'Elastic Ran Feature', 'EUtranCells', 'Eran EUtranCells']Make sure to include all the column names in the desired order within the list.
- User
column=site.index(cell_val) + 1, AttributeError: 'dict' object has no attribute 'index'
- Assistant
Based on the error message, it seems like the
missed_cellvariable is a dictionary instead of a list. To fix this, you need to update the code in thefill_sheetfunction. Replace the line:for cell_val in missed_cell:with:
for cell_val in columns:This change will iterate over the
columnslist instead of themissed_celldictionary. It will set the value of each cell in the row based on the corresponding column value. - User
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', 'VlaRef', 'ExtEnodeB VlanRef', 'EranLink', 'Port Bitrate', 'PTP Time Sync', 'BBU', '10G LKF', '10G Feature', 'Elastic Ran LKF', 'Elastic Ran Feature', 'EUtranCells', 'Eran EUtranCells', ] row = 1 for col in columns: sheet.cell( row=row, column=columns.index(col) + 1, value=col, ) row = 2 for site in sites: for cell_val in site: sheet.cell( row=row, column=site.index(cell_val) + 1, value=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
Based on the updated code, the
fill_sheetfunction takes in asheet(openpyxl sheet object) and asiteslist as arguments. It fills the excel sheet with missed cells using the specified columns.The
fill_sheetfunction first checks if thesiteslist is empty. If it is, the function returns without doing anything.The
columnslist is defined with the desired columns in the specified order.Then, the function iterates over the
columnslist to set the values of the cells in the first row. Therowandcolumnpositions are determined using theindexmethod of thecolumnslist.After setting up the column names, the function sets the
rowvariable to 2 and iterates over thesiteslist. For eachsite, it iterates over the values in thesitedictionary (assumingsiteis a dictionary). It sets the value of each cell in the row based on the corresponding value in thesitedictionary. Therowandcolumnpositions are determined using theindexmethod of thesitelist.The
fill_excelfunction creates a new Workbook object. It then creates a new sheet with the name 'ElasticRan' using thecreate_sheetmethod and assigns it towork_sheet. After that, it calls thefill_sheetfunction to fill thework_sheetwith the specifiedsiteslist.Finally, it removes the default 'Sheet' that is created automatically by
openpyxl, and saves the workbook to the providedpath.Note: Make sure to pass a list of dictionaries as
sitesin thefill_excelfunction, where each dictionary represents a site and contains the necessary keys from thecolumnslist with their corresponding values. - User
line 101, in fill_sheet column=site.index(cell_val) + 1, AttributeError: 'dict' object has no attribute 'index'
- Assistant
Apologies for the confusion. It seems that the
sitevariable is a dictionary instead of a list. To fix this issue, you need to modify thefill_sheetfunction as follows:def fill_sheet(sheet, sites): """ Fill the excel sheet with missed cells. Args: sheet: openpyxl sheet object sites: list of dictionaries """ if len(sites) == 0: return columns = [ 'Region', 'ENM', 'ERBS_Name', 'ID', 'eNBId', 'Vlan', 'VlaRef', 'ExtEnodeB VlanRef', 'EranLink', 'Port Bitrate', 'PTP Time Sync', 'BBU', '10G LKF', '10G Feature', 'Elastic Ran LKF', 'Elastic Ran Feature', 'EUtranCells', 'Eran EUtranCells', ] row = 1 for col in columns: sheet.cell( row=row, column=columns.index(col) + 1, value=col, ) row = 2 for site in sites: for col, cell_val in site.items(): col_index = columns.index(col) + 1 sheet.cell( row=row, column=col_index, value=cell_val, ) row += 1In the modified code, I assume that
sitesis a list of dictionaries where each dictionary represents a site, and the keys correspond to the columns from thecolumnslist.Inside the
fill_sheetfunction, when iterating over thesites, I use the.items()method to iterate over the dictionary items as(col, cell_val). This way,colrepresents the column name, andcell_valrepresents the value for that column.I calculate the
col_indexby finding the index of thecolin thecolumnslist and adding 1 to match the column index in Excel. Then, I set the value of the cell in the row and column index usingsheet.cell()method.This updated code should resolve the 'AttributeError: 'dict' object has no attribute 'index'' issue and correctly fill the excel sheet with missed cells.