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:
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:
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
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.
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. ##############################")