the logic flow will be this: if the there is a username: "Administrator" in column B, copy the data, and paste it to another new Excel workbook. However, this code give me the following error:
IndexError: list index out of range
def copyRange(startCol, startRow, endCol, endRow, sheet):
rangeSelected = []
#Loops through selected Rows
for i in range(startRow,endRow + 1,1):
#Appends the row to a RowSelected list
rowSelected = []
for j in range(startCol,endCol+1,1):
if (sheet.cell(row = i, column = j).value) in adminlist:
rowSelected.append(sheet.cell(row = i, column = j).value)
#Adds the RowSelected List and nests inside the rangeSelected
rangeSelected.append(rowSelected)
return rangeSelected
def pasteRange(startCol, startRow, endCol, endRow, sheetReceiving, copiedData):
countRow = 0
for i in range(startRow,endRow+1,1):
countCol = 0
for j in range(startCol,endCol+1,1):
sheetReceiving.cell(row = i, column = j).value = copiedData[countRow][countCol]
countCol += 1
countRow += 1
selectedRange = copyRange(1, 1, 13, sheet.max_row, sheet) #Change the 4 number values
pasteRange(1, 1, 13, sheet.max_row, Users_sheet, selectedRange) #Change the 4 number values
The simpler approach.
If you were just copying the values I would probably just create a list from the values in the source row then use users_sheet.append on the destination workbook to just append the list, meaning the row will be written to the next unused row in the destination sheet.
Since you want to copy the formatting/style of the cell then we copy the cells one by one and copy the formatting/style at the same time. dst_row is used to ensure the values are copied to the next unused row on the destination sheet.
This example doesn't copy the header row (starts writing on destination from row 1). If headers are needed same or other this will need to be added.
The example code is expanded to show the link between variables. Can be condensed where you understand what it's doing.
import openpyxl
from copy import copy
from openpyxl.utils.cell import column_index_from_string as cifs
src_file = 'data.xlsx'
wb1 = openpyxl.load_workbook(src_file)
sheet = wb1['Sheet1']
dst_file = 'foo.xlsx'
wb2 = openpyxl.load_workbook(dst_file)
users_sheet = wb2['Sheet1']
### List to search or just a string if that's all that's being searched
search_list = ['administrator'] # Lower case to make search case-insensitive
### Search column
search_column = 'B'
search_col_int = cifs(search_column)
### Column Range A - M to copy in cell
### This can be set using column letter or use openpyxl sheet max value
start_col = 'A'
end_col = sheet.max_column+1
rng = range(cifs(start_col),end_col)
### Start writing on destination sheet from row 1
dst_row = 1
### Looping col search_column only
for row in sheet.iter_rows(min_col=search_col_int, max_col=search_col_int):
### Check cell value is in the search list.
### Convert to lower case for case-insensitive search
if row[0].value.lower() in search_list:
### Use cell offsets for each cell in the row from Cols A - M
for col in rng: # Range Cols A - M
dst_cell = users_sheet.cell(dst_row, col)
### Use cell offsets to get the cell value of all cells in the current row.
### Therefore row does not change. column cycles from A (-1) to M (11)
src_cell = row[0].offset(row=0, column=col-search_col_int)
### Copy source cell value to destination cell
dst_cell.value = src_cell.value
### If cell has a styles then copy this as well
if src_cell.has_style:
dst_cell.font = copy(src_cell.font)
dst_cell.alignment = copy(src_cell.alignment)
dst_cell.border = copy(src_cell.border)
dst_cell.fill = copy(src_cell.fill)
dst_cell.number_format = src_cell.number_format
dst_row += 1 # Destination rows tracked
wb2.save(dst_file)