Hướng dẫn how do you update a value in excel using python? - làm cách nào để bạn cập nhật một giá trị trong excel bằng python?

in this tutorial, I will create a python script that will read excel file data and modified them, and update the excel file. I am using python 3.7 and some colors libs to display logs colorful.

I have created a python script that updates the excel sheet based on some input parameters, I just need to read all rows and column values and update accordingly.

You can also checkout other python excel tutorials:

• Popular Python excel Library
• Reading Excel Using Python Pandas
• How To Read & Update Excel File Using Python
• Inserting & Deleting rows/columns using openpyxl

There are the following functionality will achieve in this tutorial –

• Read excel file using an absolute path.
• File the column index based on excel column heading
• Iterate on all rows
• Get and Update column field value
• Save the excel file

Read and Update Microsoft Excel File In Python

if[typeof ez_ad_units!='undefined']{ez_ad_units.push[[[250,250],'pythonpip_com-medrectangle-4','ezslot_2',108,'0','0']]};__ez_fad_position['div-gpt-ad-pythonpip_com-medrectangle-4-0'];We will create a “sample.xlsx” excel file that will have the following data –
employee.xlsx

Name
age Salary Roji 32 1234 Adam 34 2134

We will update grade column value A or B based on salary filed value, Update the grade column value if the salary column value is greater > 1500.

How To Read Excel File in Python

We will create emp.py file and add the below code into this file, I am using some python packages that will install using pip command.

pip
install colorama pip install openpyxl

The colorama package is optional, that only used to display logs in colorful format. The openpyxl mandatory required package.

Let’s import all packages into emp.py file.

import
sys from colorama import Fore, init, Back, Style import openpyxl import re 

How To Read excel file Using openpyxl

The openpyxl package has load_workbook[] method that will use to open xlsx file. There is a number of helper methods that help to read and write excel file.

path
= "C:\\employee.xlsx" wb_obj = openpyxl.load_workbook[path.strip[]] # from the active attribute sheet_obj = wb_obj.active

We have also set the active sheet to read the data of the excel file.if[typeof ez_ad_units!='undefined']{ez_ad_units.push[[[468,60],'pythonpip_com-box-4','ezslot_8',114,'0','0']]};__ez_fad_position['div-gpt-ad-pythonpip_com-box-4-0'];

Cách đọc chiều dài cột và hàng

Chúng tôi sẽ sử dụng các thuộc tính max_column và max_row của đối tượng tệp Excel.max_column and max_row properties of the excel file object.

# get max column count
max_column=sheet_obj.max_column
max_row=sheet_obj.max_row

Cách lặp lại trên các hàng tệp excel trong Python

Chúng tôi sẽ sử dụng phương thức range[] để lặp lại tệp excel trên các hàng, bỏ qua hàng đầu tiên có thông tin tiêu đề tệp Excel.

for j in range[2, 5]:
        salary_cell=sheet_obj.cell[row=i,column=colum_index]

Cách nhận và đặt dữ liệu tệp Excel

Chúng tôi sẽ nhận được đối tượng ô Row và sau đó nhận giá trị ô bằng thuộc tính .value.

//get col object 
salary_cell=sheet_obj.cell[row=i,column=2] 
//get value 
salary = salary_cell.value: 
//set value 
salary_cell.value = 2000; 

Mã nguồn đầy đủ:

Tôi đã hợp nhất tất cả các phần của mã và thêm chúng vào tệp emp.py.

import sys
from colorama import Fore, init, Back, Style
import openpyxl
import re

init[convert=True]
print["\n"]
path = input["Enter xls file path, ex- C:\\employee.xlsx : "]
input_col_name = input["Enter colname, ex- Endpoint : "]
try:
    print[Fore.RESET]
    #path = "C:\\employee.xlsx"
    wb_obj = openpyxl.load_workbook[path.strip[]]
    # from the active attribute 
    sheet_obj = wb_obj.active

    # get max column count
    max_column=sheet_obj.max_column
    max_row=sheet_obj.max_row
    for j in range[2, 5]:
        salary_cell=sheet_obj.cell[row=i,column=2]
        if salary_cell.value > 1500:
            salary_cell.value =  salary_cell.value+500

    wb_obj.save[]
except Exception as e:
    print[e]
    print [Fore.RED + "Error : The file does not found"]
print[Fore.GREEN + "###################### Successfully! Excel file has been read/write. ##############################"]