Excel Spreadsheets in Python
We offer you a brighter future with placement-ready courses - Start Now!!
In today’s era, where data is everything, Excel Spreadsheets are used extensively to manage and analyse data. Python, being one of the most popular programming languages, provides us with the ability to work with Excel Spreadsheets using various libraries. In this blog, we are going to learn how to edit Excel Spreadsheets in Python using the openpyxl library.
Openpyxl is a Python library used for working with Excel spreadsheets, which allows us to read, modify, and write Excel files. It is simple to use, flexible, and compatible with various file formats, including XLSX, XLSM, XLSB, and XLTX.
Some of the key features of openpyxl library are:
- Allows reading and writing of data, formulas, formatting, and charts
- Can read and write files in a range of Excel file formats
- Support for Unicode characters
- Supports the use of Python generators to stream large data sets without the need for excessive memory
Now let’s dive into how to edit Excel Spreadsheets in Python with openpyxl.
Steps to Edit Excel Spreadsheets in Python with openpyxl:
1. Install openpyxl Library
To start with openpyxl, we first need to install it. We can install it using pip by running the following command in the terminal:
pip install openpyxl
2. Import openpyxl
Once we have installed the openpyxl library, we can import it into our Python code using the following command:
import openpyxl
3. Creating a new spreadsheet
Creating a new spreadsheet using Openpyxl is very simple. Here’s an example code snippet that creates a new Excel workbook:
from openpyxl import Workbook
# Create a new workbook
wb = Workbook()
# Select the active worksheet
ws = wb.active
# Rename the active worksheet
ws.title = "New Sheet"
# Save the workbook
wb.save("new_workbook.xlsx")
This code creates a new workbook, selects the active worksheet, renames it to “New Sheet”, and saves the workbook with the given filename.
4. Writing data to a spreadsheet
Once we have created a new spreadsheet, we can write data to it. Here’s an example code snippet that writes some data to a new Excel workbook:
from openpyxl import Workbook
# Create a new workbook
wb = Workbook()
# Select the active worksheet
ws = wb.active
# Write some data to the worksheet
ws["A1"] = "Name"
ws["B1"] = "Age"
ws["A2"] = "John"
ws["B2"] = 25
# Save the workbook
wb.save("new_workbook.xlsx")
In this code, we have written some data for the worksheet. We have written “Name” and “Age” to the first row and “John” and 25 in the second row.
5. Formatting cells
We can format cells in an Excel spreadsheet using Openpyxl. Here’s an example code snippet that sets the font size and bold style of a cell:
from openpyxl import Workbook
from openpyxl.styles import Font
# Create a new workbook
wb = Workbook()
# Select the active worksheet
ws = wb.active
# Write some data to the worksheet
ws["A1"] = "Name"
# Format the cell
ws["A1"].font = Font(size=14, bold=True)
# Save the workbook
wb.save("new_workbook.xlsx")
In this code, we have set the font size and bold style of the cell that contains the “Name” text.
6. Reading data from a spreadsheet
We can also read data from an Excel spreadsheet using Openpyxl. Here’s an example code snippet that reads data from an existing Excel workbook:
from openpyxl import load_workbook # Load the workbook wb = load_workbook(filename="existing_workbook.xlsx") # Select the active worksheet ws = wb.active # Read some data from the worksheet name = ws["A1"].value age = ws["B1"].value # Print the data print(name) print(age)
In this code, we have loaded an existing workbook, selected the active worksheet, and read data from the cells “A1” and “B1”. We have printed the data to the console.
7. Load Excel Spreadsheet
To start editing an existing Excel spreadsheet, we need to load it into our Python program using the ‘load_workbook()’ function from openpyxl. We can do this by specifying the path to the Excel file:
wb = openpyxl.load_workbook('file_name.xlsx')
8. Access Worksheet
Once we have loaded the Excel file, we need to access the worksheet we want to edit. We can do this by using the ‘active’ method to get the active worksheet or using the ‘get_sheet_by_name()’ method to get the worksheet by name:
sheet = wb.active
9. Edit Spreadsheet
Now that we have loaded the worksheet, we can edit it by accessing individual cells and updating their values:
sheet['A1'] = 'DataFlair
We can also update multiple cells at once using a loop:
for row in sheet.iter_rows(min_row=2, max_row=5, min_col=2, max_col=5):
for cell in row:
cell.value = cell.value * 2
10. Working with Multiple Worksheets:
OpenPyXL allows us to work with multiple worksheets within a single Excel workbook. The first step is to create a new worksheet by calling the create_sheet() method of the Workbook object. Here’s an example:
from openpyxl import Workbook
workbook = Workbook()
worksheet1 = workbook.active # By default, the first sheet is named "Sheet"
worksheet1.title = 'Data Sheet 1' # Rename the default sheet
worksheet2 = workbook.create_sheet('Data Sheet 2') # Create a new sheet
# Write data to the first sheet
worksheet1['A1'] = 'Name'
worksheet1['B1'] = 'Age'
worksheet1['A2'] = 'John'
worksheet1['B2'] = 25
# Write data to the second sheet
worksheet2['A1'] = 'City'
worksheet2['B1'] = 'Population'
worksheet2['A2'] = 'New York'
worksheet2['B2'] = 8623000
workbook.save('my_data.xlsx')
In the above example, we first create a new Workbook object and then create two new worksheets using the create_sheet() method. We rename the first sheet to “Data Sheet 1” and write some data to it. Similarly, we write some data to the second sheet, which we name “Data Sheet 2”. Finally, we save the workbook to a file named “my_data.xlsx”.
11. Working with Excel Charts:
Excel charts are a powerful tool for visualizing data in a spreadsheet. OpenPyXL allows us to create and modify Excel charts programmatically using the charts module. Here’s an example:
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference, Series
workbook = Workbook()
worksheet = workbook.active
# Write some data to the worksheet
worksheet['A1'] = 'Year'
worksheet['B1'] = 'Sales'
for i in range(1, 11):
worksheet.cell(row=i+1, column=1, value=2000+i)
worksheet.cell(row=i+1, column=2, value=i*100)
# Create a line chart
chart = LineChart()
chart.title = 'Sales Data'
chart.x_axis.title = 'Year'
chart.y_axis.title = 'Sales'
data = Reference(worksheet, min_col=2, min_row=2, max_col=2, max_row=11)
categories = Reference(worksheet, min_col=1, min_row=3, max_row=11)
series = Series(data, categories)
chart.append(series)
# Add the chart to the worksheet
worksheet.add_chart(chart, 'D2')
workbook.save('my_data.xlsx')
In the above example, we create a new Workbook object and a new worksheet. We write some data to the worksheet and then create a LineChart object using the data from the worksheet. We set the chart title and axis titles and add the data series to the chart. Finally, we add the chart to the worksheet at cell D2 and save the workbook to a file named “my_data.xlsx”.
12. Save Excel Spreadsheet
After editing the spreadsheet, we need to save the changes back to the Excel file using the ‘save()’ method:
wb.save('file_name.xlsx')
Real-time Example:
Let’s consider a scenario where we have an Excel spreadsheet that contains the sales data for a company for a particular year. We want to update the data for the last quarter of the year with the current quarter’s data.
We can use openpyxl to load the Excel file, access the relevant cells, and update their values with the current data. Finally, we can save the changes back to the Excel file.
Conclusion
openpyxl is a powerful Python library that allows us to manipulate Excel spreadsheets. It provides us with a range of functionalities that make editing, formatting, and extracting data from spreadsheets a breeze. With its easy-to-use interface and extensive documentation, openpyxl can save us time and effort in our data management tasks. We hope this article has provided you with a comprehensive understanding of the library, its usage, and some practical examples. So, go ahead and explore the library to unleash the full potential of your data management tasks.
