How to Use Python to Manage Excel Spread Sheets?

Prajwal Jadhav February 01, 2024

How to integrate Python and Excel for effective data manipulation.

How to Use Python to Manage Excel Spread Sheets?

Python and Excel both are useful to work with data.

Python is a powerful and robust programming language that can do lots of operation on data, like data processing ,data visualizing ,and applying machine learning algorithms on data.

Excel is a popular spreadsheet application used for creating ,organizing and presenting data in efficient way, Microsoft Excel provide features such as formulas, charts, and tables to organize data. 

In this Blog post, we will discuss two ways for manipulating Excel spreadsheets: integrating Python within Excel and by using python separately.

Python in Excel is a cool feature that allows you to write Python code directly within an Excel cell and execute it in the cloud. Results are shown in the Excel worksheet in the form of charts and visualizations. You can also create your own Python functions for use in other Excel cells.To access this feature, you'll need a Microsoft 365 subscription. You can enable it in the Formulas tab.

Second method,by using Python libraries in Excel, you can use Python libraries for data analysis alongwith Excel features. You can clean and modify data faster, create graphs and dashboards, and access data from any machine with an internet connection.

Openpyxl is a Python library that helps you work with Excel. It's specifically useful for creating, modifying, and extracting data from Excel sheets in both the older .xls format and the newer .xlsx format.

Now,let's see how this works in a practical approach

Install openpyxl

You can install openpyxl by using the following command in your terminal or command prompt:

pip install openpyxl

Importing in Python

After install, you can start using openpyxl by importing it

import openpyxl

Working with Excel

Opening a Worksheet

You can either open an existing Excel worksheet or create a new one.

import openpyxl

# Open an existing workbook
existing_workbook = openpyxl.load_workbook('existingworksheet.xlsx')

# Create a new workbook
fresh_workbook = openpyxl.Workbook()

Accessing Worksheets

You can access sheets within the workbook

# Access the open/active worksheet
active_sheet = existing_workbook.active
# Access a particular worksheet by name (for example, 'Sheet1')
particular_sheet = existing_workbook['Sheet1']

# Access worksheet Sheet2 by name
sheet2 = existing_workbook['Sheet2']

Reading and Writing Data

Read and write data to particular cells & sheets

# Read data from a cell in the active sheet
cell_value_a1 = active_sheet['A1'].value
print("Value in cell A1:", cell_value_a1)

# Read data from a cell in Sheet2
cell_value_b1 = sheet2['B1'].value
print("Value in Sheet2, cell B1:", cell_value_b1)

# Write data to a cell in the active sheet
active_sheet['A1'] = 'Hello, Excel!'

# Write data to a cell in Sheet2
sheet2['B1'] = 'New Data for B1'

Iterating Through Rows and Columns

# Iterate through rows (max_row = 5, max_col = 5)
for row in active_sheet.iter_rows(min_row=1, max_row=5, min_col=1, max_col=5):
    for cell in row:
        print(cell.value)

# Iterate through columns (max_row = 5, max_col = 5)
for column in sheet2.iter_cols(min_row=1, max_row=5, min_col=1, max_col=5):
    for cell in column:
        print(cell.value)

Saving and Closing

# Save changes to an existing workbook with a new name
existing_workbook.save('modified_existingworksheet.xlsx')

# Save a new workbook with a different name
fresh_workbook.save('new_example.xlsx')

# Close the workbooks
existing_workbook.close()
fresh_workbook.close()

Conclusion

In conclusion, the integration of Python in Excel,through direct usage or Python libraries, provides users with a powerful combination for optimizing data workflows.This approach enhances data analysis and visualization, offering efficient solution within the Excel environment.

Tags: