Applying a formula to a column in excel python-CodePudding

On Python 3.9.

So here's what I'm trying to do. I'm running reports almost every hour and they aren't formatted when exported to excel to the way I need them to be.

I'm essentially trying to figure out a way to start on the second row (since I have headers) and apply =RIGHT(Ax, 10) to each row in the column A such as =RIGHT(A2, 10), =RIGHT(A3, 10), =RIGHT(A4, 10), and so on until the last row in the workbook. Each workbook will have an arbitrary number of rows since it changes (increases) as the day goes on.

import csv
import openpyxl

wb = openpyxl.Workbook()
ws = wb.active  

with open(r'location\file.csv') as f: 
    reader = csv.reader(f, delimiter=',') 
    for row in reader:
        ws.append(row) 

wb.save(r'location\file.xlsx') 

wb = openpyxl.load_workbook(r'location\file.xlsx')
sheet = wb['Sheet']
maxr = sheet.max_row
tuple(sheet[f'A2:{maxr}'])
sheet[f'A2:A{maxr}'] = f"=RIGHT(A2:A{maxr}, 10)"

wb.save(r'location\file.xlsx')

CodePudding user response：

Replace

tuple(sheet[f'A2:{maxr}'])
sheet[f'A2:A{maxr}'] = f"=RIGHT(A2:A{maxr}, 10)"

with

for i in range(1,maxr 1):
    sheet[f'A{i}'].value = sheet[f'A{i}'].value[-10:]

CodePudding user response：

Try to keep it simple. Give the following a try.

Change this section of your code

maxr = sheet.max_row
tuple(sheet[f'A2:{maxr}'])
sheet[f'A2:A{maxr}'] = f"=RIGHT(A2:A{maxr}, 10)"

to a loop

maxr = len(sheet['A'])
for curr_row in range(2, maxr    1):
    curr_value = sheet.cell(row=curr_row, column=4).value 
    new_value = curr_value[-10:]
    sheet.cell(row=curr_row, column=4) = new_value