CODEWITHSUNDEEP
pythonreplaceopenpyxl
ThisGuyUsesPowershell
ThisGuyUsesPowershell

Reputation: 71

Find and replace using Openpyxl

I am trying to replace anything in column C that has -1 as a value to 0. Here is what I have tried so far. I am new to python and openpyxl so please be patient with me. 

import openpyxl
    excelFile = openpyxl.load_workbook('C:\Users\user1\Documents\file\file.xlsx')
    sheet1 = excelFile.get_sheet_by_name('sheet1')
    currentRow = 1
    for eachRow in sheet1.iter_rows():
        if sheet1.cell(currentRow, column=3) == '-1':
            sheet1.cell(row=currentRow, column=3).value = "0"
            currentRow += 1
            excelFile.save('C:\Users\user1\Documents\file\file.xlsx')

I have also tried this with the same result, no errors however no changes to the file.

import openpyxl
excelFile = openpyxl.load_workbook(r'C:\Users\user1\Documents\file\file.xlsx')
sheet1 = excelFile.get_sheet_by_name('CFRP_1_21')

if sheet1['C:C'] == '-1':
    sheet1['C:C'] = '0'
    excelFile.save('C:\Users\user1\Documents\file\file.xlsx')

Upvotes: 1

Views: 2248

Answers (1)

APhillips
APhillips

Reputation: 1181

Here's the openpyxl tutorial, it should help clear up some basic usage questions you may have.

Try this for your use-case:

import openpyxl
excelFile = openpyxl.load_workbook(r'C:\Users\user1\Documents\file\file.xlsx')
#sheet1 = excelFile.get_sheet_by_name('CFRP_1_21')
sheet1 = excelFile.active #This selects the first sheet in the Excel file, only use specific names if required

for col in sheet1.iter_cols(min_col=3, min_row=1, max_col=3):
    for cell in col:
        if cell.value == '-1':
            cell.value = '0'
excelFile.save(r'C:\Users\user1\Documents\file\file.xlsx') #Don't forget to use 'r' for string here

Upvotes: 1

Related Questions