Reading, Writing, Updating, and Deleting Excel Files using Python
In this tutorial, you will learn how to manipulate Excel files using Python. We will use the openpyxl library, which is a powerful and easy-to-use package for working with Excel files.
Step 1: Installing the Required Libraries
First, make sure you have Python installed on your system. You can download it from the official website (https://www.python.org/downloads/). After installing Python, open your command prompt or terminal and install the openpyxl library using pip:
pip install openpyxl
Step 2: Reading Data from an Excel File
Let's start by reading data from an existing Excel file. Create a sample Excel file called data.xlsx with some data (e.g., in columns A and B) before running this code.
import openpyxl
def read_excel_file(file_path):
workbook = openpyxl.load_workbook(file_path)
sheet = workbook.active
data = []
for row in sheet.iter_rows(min_row=2, values_only=True):
data.append(row)
workbook.close()
return data
file_path = "data.xlsx"
data = read_excel_file(file_path)
print(data)
Step 3: Writing Data to an Excel File
Now let's learn how to create a new Excel file and write data to it.
import openpyxl
def write_to_excel(file_path, data):
workbook = openpyxl.Workbook()
sheet = workbook.active
for row in data:
sheet.append(row)
workbook.save(file_path)
workbook.close()
data_to_write = [
["Name", "Age"],
["John", 30],
["Jane", 25],
]
file_path = "new_data.xlsx"
write_to_excel(file_path, data_to_write)
Step 4: Updating Data in an Excel File
To update existing data in an Excel file, you need to first read the file, modify the data, and then save it again.
import openpyxl
def update_excel_file(file_path, update_row, new_data):
workbook = openpyxl.load_workbook(file_path)
sheet = workbook.active
for row_num, row in enumerate(sheet.iter_rows(min_row=2, values_only=True), start=2):
if row == update_row:
for col_num, value in enumerate(new_data, start=1):
sheet.cell(row=row_num, column=col_num, value=value)
break
workbook.save(file_path)
workbook.close()
file_path = "data.xlsx"
update_row = ("John", 30) # Replace with the row you want to update
new_data = ["Johnny", 31] # Replace with the new data you want to use
update_excel_file(file_path, update_row, new_data)
Step 5: Deleting Data from an Excel File
To delete data from an Excel file, you'll read the file, identify the rows you want to delete, and then save the modified file.
import openpyxl
def delete_rows_from_excel(file_path, rows_to_delete):
workbook = openpyxl.load_workbook(file_path)
sheet = workbook.active
for row_num, row in enumerate(sheet.iter_rows(min_row=2, values_only=True), start=2):
if row in rows_to_delete:
sheet.delete_rows(row_num)
workbook.save(file_path)
workbook.close()
file_path = "data.xlsx"
rows_to_delete = [
("Jane", 25), # Replace with the rows you want to delete
]
delete_rows_from_excel(file_path, rows_to_delete)
Congratulations! You've learned how to read, write, update, and delete data in Excel files using Python and the openpyxl library. With this knowledge, you can automate various tasks involving Excel files and work with data more efficiently. Remember to explore the documentation of the openpyxl library to discover more features and functionalities. Happy coding!
