how to assign a list value to column in excel file?-CodePudding

I have a list value and want to assign it into a column in a excel file. The values I want to change are in sheet 6.

my poor code looks something like this the best I could do is try to first change the AF6:AF22 to a fixed value 5 with hope that I could change it to list.

But is there a simple way to change AF6:AF22 values to a list? something simple ws['AF6:AF22'] = l?


from openpyxl import Workbook
import pandas as pd
from openpyxl import load_workbook

l = list(range(5))

FilePath = 'excel_file.xlsx'
wb = load_workbook(FilePath)

ws = wb.worksheets

sheet_number = 6 

for sheet_number in ws.iter_cols('AF6:AF22'):
    for cell in sheet_number:
        cell.value = 5

CodePudding user response：

Option 1 Hi - I am adding a faster way here. This is probably better as it avoids the for loop and updating cells one at a time.

from openpyxl import Workbook
import pandas as pd
from openpyxl import load_workbook

l = list(range(17))  #The list - You can replace l with whatever you need

with pd.ExcelWriter('excel_file.xlsx', mode='a', engine = 'openpyxl') as writer: 
    pd.DataFrame(l).to_excel(writer, sheet_name='Sheet6', startrow = 5, startcol= 31, index=False, header=None)

Option 2 You can use the below code to do what you need. Added comments, so you get an understanding of my logic...

from openpyxl import Workbook
import pandas as pd
from openpyxl import load_workbook

l = list(range(17))  #The list - You can replace l with whatever you need

FilePath = 'excel_file.xlsx'
wb = load_workbook(FilePath)

ws = wb.worksheets[5]  #Worksheet 5 is the 6th sheet as numbering starts from zero
for i in range(6,23):  # Column numbers 6 through 22
    ws.cell(row=i, column=32).value = l[i-6]  #Write to cell in AF = column 32
    
wb.save("excel_file.xlsx")