Python Modify Excel File (Developer Tutorial)

Introduction

The ability to programmatically edit Excel files is a vital necessity for a variety of businesses and applications in today's data-driven world. Effective data management and analysis require the capacity to dynamically alter Excel files, whether it is for new content addition, data update, or cell formatting. Python offers developers strong capabilities for working with Excel files because of its extensive library environment. IronXL for Python is a powerful tool that can be used to easily change Excel files, among other tools. This in-depth tutorial will cover IronXL for Python's features, functionalities, and best practices for programmatically using Python to modify Excel files, enabling developers to work with Excel files efficiently.

How to use Python to modify an Excel file

1. Open Visual Studio Code and create a python file.
2. Install IronXL using the pip package manager.
3. After that, set up the Excel file that needs to be modified.
4. Select the working spreadsheet and update the value by row and column index.
5. Save the modified data into a new Excel file.

IronXL

Developers may read, manipulate, and write Excel files directly from Python code with the help of IronXL for Python, a feature-rich library made specifically for working with Excel files. IronXL is a robust solution for easily altering Excel files for developers. It is built on top of the .NET framework and connects with Python effortlessly. IronXL for Python library provides an adaptable toolset to handle various Excel file manipulation tasks, such as adding formulas, formatting cells, and updating data.

Features of IronXL

• With IronXL, data can be quickly read from existing Excel files and written to newly created or pre-existing spreadsheets, including tasks like formatting, applying formulas, accessing cell values, and condensing data to create charts.
• Cross-Platform Compatibility: IronXL is a flexible option for Python developers regardless of their operating system of choice because it is made to function flawlessly on a variety of platforms, including Windows, Linux, and macOS.
• High efficiency: Even when working with huge datasets, IronXL, which is built on the .NET framework, processes and efficiently manipulates Excel files. It accomplishes this by fusing Dot NET's efficiency with Python's adaptability.
• Support for Excel Formats: IronXL supports the following Excel file formats, which it can open and use. Excel format like CSV file, xls (Excel 97–2003), xlsx file (Excel 2007 and beyond), and xlsm file (Excel with macros enabled).
• Advanced-Data Manipulation: With IronXL, we can manipulate Excel spreadsheets, including aggregating, filtering, and sorting, from which they can derive insightful conclusions.
• Cell formatting: Excel spreadsheets can be made more aesthetically pleasing and readable by using IronXL's cell formatting options, which include font styles, colors, borders, and alignment.

Essentially, IronXL provides a trustworthy and user-friendly way to incorporate Excel functionality into Python programs, assisting Python developers in overcoming the difficulties that come with managing Excel files. Whether you're building data analysis tools that need to read Excel files, automating reporting chores that involve reading Excel files, or developing interactive dashboards, IronXL gives you the flexibility and resources you need to succeed in handling Excel files within the Python ecosystem. To find out more about IronXL, click here.

Setup Environment

Prerequisites

Make sure the following are installed on your computer before beginning the guide:

• Because IronXL was developed with the .NET 6.0 SDK, it needs to be installed on your computer.
• Python 3.0+: To follow this tutorial, you must have Python 3.0 or a later version installed.
• pip: Install the Python package installer pip first, as IronXL will require it.

Create a New file and Install IronXL

Open this file in Visual Studio Code, then create a ModifyExcelFile.py Python file. This file contains our script for using IronXL to edit pre-existing Excel files.

To access the command line in Visual Studio Code, choose Terminal > New Terminal from the menu.

The first step before using IronXL is to install the library. Using Python's package manager pip, quickly install IronXL by executing the following command:

 pip install IronXL

You can now use IronXL to read and modify installed Excel spreadsheet files.

Excel File Modification Using IronXL for Python

You can perform various modifications to an Excel file using IronXL for Python, with operations such as data updates, content additions, cell formatting, and more. Let's look at a few typical situations and how IronXL for Python can be used to resolve them. By the sample data that are going to be processed.

Updating Existing Data

Updating current data is one of the most frequent activities involved in modifying Excel files using the below code. With IronXL for Python, you can easily write data in Excel xlsx files by adding new data, fixing errors, and updating out-of-date information.

from ironxl import *
# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Update data in a specific cell
worksheet["B2"].Value = "29"

# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *
# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Update data in a specific cell
worksheet["B2"].Value = "29"

# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

From the above code, we load an existing Excel file, then open a particular worksheet, and manipulate data changes into the value of the cell ("A1"). The updated workbook is then saved as a new file.

Adding New Content

Adding new rows, columns, or worksheets to Excel files is another feature that IronXL for Python offers. The following code shows adding a sample dataset to the existing file.

from ironxl import *
# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# new data
New_content=["Kent",25,55000]
rowcount=worksheet.RowCount+1
for index,Item in enumerate(New_content):
    worksheet.SetCellValue(rowcount, (index), str(Item));
# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *
# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# new data
New_content=["Kent",25,55000]
rowcount=worksheet.RowCount+1
for index,Item in enumerate(New_content):
    worksheet.SetCellValue(rowcount, (index), str(Item));
# Save the modified workbook
workbook.SaveAs("modified_data.xlsx")

In this example, we update an existing spreadsheet by adding new data as a new row at the specified location (i.e., row 5).

Formatting Cells

Another common chore when working with Excel files is formatting cells. For cell formatting, IronXL for Python offers comprehensive support, allowing you to adjust font styles, colors, borders, and alignment, and convert data into the desired format.

from ironxl import *
# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Get the cell
cell = worksheet["B2"]

# Set background color of the cell with an RGB string
cell.Style.SetBackgroundColor("#428D65")

# Save the workbook
workbook.SaveAs("modified_data.xlsx")

from ironxl import *
# Load an existing Excel file
workbook = WorkBook.Load("output.xlsx")

# Access a specific worksheet
worksheet = workbook.WorkSheets[0]

# Get the cell
cell = worksheet["B2"]

# Set background color of the cell with an RGB string
cell.Style.SetBackgroundColor("#428D65")

# Save the workbook
workbook.SaveAs("modified_data.xlsx")

In the above code, we format cell "B2" by setting a yellow background color and making the font bold.

To learn more about the code, please visit the link here.

Conclusion

In summary, IronXL for Python offers developers a robust toolset for updating content, editing data, and formatting cells in Excel spreadsheets. It also offers a comprehensive solution for altering Excel files programmatically. IronXL's user-friendly API and smooth Python integration enable developers to optimize Excel file modification activities, resulting in more effective workflows for data management and analysis.

The $599 Lite edition of IronXL comes with a permanent license, upgrade possibilities, and a year of software support. Customers can assess the product under real-world conditions throughout the trial time. Please check out the license page for additional information about IronXL's cost, licensing, and free trial. You may also find out more about Iron Software by visiting this website.