USING IRONXL FOR PYTHON

Python Modify Excel File (Developer Tutorial)

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.

Python Modify Excel File (Developer Tutorial): Figure 1 - Click New File in Visual Studio Code and name the file ModifyExcelFile.py

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

Python Modify Excel File (Developer Tutorial): Figure 2 - Click on New terminal from the menu to open a terminal within Visual Studio Code

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
pip install ironxl
SHELL

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

Python Modify Excel File (Developer Tutorial): Figure 3 - Type the command above to install ironXL

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.

Python Modify Excel File (Developer Tutorial): Figure 4 - Example Excel Worksheet

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")
PYTHON

In the above code, we load an existing Excel file, then open a particular worksheet, and change the value of the cell ("B2"). The updated workbook is then saved as a new file.

Python Modify Excel File (Developer Tutorial): Figure 5 - Replacing the value in the B2 cell to 29 programmatically using IronXL and python

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 to be added
new_content = ["Kent", 25, 55000]
row_count = worksheet.RowCount + 1

# Add new data as a new row at the end of the worksheet
for index, item in enumerate(new_content):
    worksheet.SetCellValue(row_count, 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 to be added
new_content = ["Kent", 25, 55000]
row_count = worksheet.RowCount + 1

# Add new data as a new row at the end of the worksheet
for index, item in enumerate(new_content):
    worksheet.SetCellValue(row_count, index, str(item))

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

In this example, we update an existing spreadsheet by adding new data as a new row at the specified location, i.e., at the end of the current data.

Python Modify Excel File (Developer Tutorial): Figure 6 - Adding a new row of data to the Excel Worksheet

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 B2
cell = worksheet["B2"]

# Set background color of the cell using 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 B2
cell = worksheet["B2"]

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

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

In the above code, we format cell "B2" by setting a background color and adjusting the style as desired.

Python Modify Excel File (Developer Tutorial): Figure 7 - Formatting the B2 cell programmatically using IronXL and python

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 $749 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.

Frequently Asked Questions

What is a feature-rich library for working with Excel files in Python?

IronXL for Python is a feature-rich library designed to help developers read, manipulate, and write Excel files directly from Python code. It provides powerful tools for tasks like adding formulas, formatting cells, and updating data.

How do I install a library for modifying Excel files in Python?

You can install IronXL for Python using pip, the Python package manager, by running the command: 'pip install ironxl' in your terminal.

What are the prerequisites for using a Python library to manipulate Excel files?

To use IronXL with Python, you need to have the .NET 6.0 SDK and Python 3.0+ installed on your computer, along with the pip package manager.

Can a Python library for Excel files be used on different operating systems?

Yes, IronXL is designed to work seamlessly on various platforms, including Windows, Linux, and macOS, making it a flexible choice for Python developers.

What types of Excel file formats are supported by a Python library for Excel manipulation?

IronXL supports various Excel file formats, including CSV, xls (Excel 97–2003), xlsx (Excel 2007 and beyond), and xlsm (Excel with macros enabled).

How can I modify existing data in an Excel file using a Python library?

You can modify existing data by loading the Excel file into IronXL, accessing the specific worksheet, updating the desired cells, and saving the modified workbook.

How do I add new content to an Excel file using a Python library?

To add new content, load the Excel file with IronXL, access the worksheet, and use the SetCellValue method to insert new data into the desired rows and columns.

What cell formatting options does a Python library for Excel files offer?

IronXL provides options for cell formatting, including adjusting font styles, colors, borders, alignment, and background colors to make spreadsheets more visually appealing and readable.

Is there a free trial available for a Python library for Excel manipulation?

Yes, IronXL offers a free trial period, allowing users to evaluate the product under real-world conditions. More information about licensing and pricing is available on their website.

What are the benefits of using a Python library for Excel file manipulation?

IronXL offers a reliable and user-friendly way to integrate Excel functionality into Python applications, helping developers efficiently manage and analyze data with its versatile toolset.

Chaknith Bin
Software Engineer
Chaknith works on IronXL and IronBarcode. He has deep expertise in C# and .NET, helping improve the software and support customers. His insights from user interactions contribute to better products, documentation, and overall experience.
< PREVIOUS
Python Protect Excel (Developer Tutorial)
NEXT >
How to read an Excel file in Python using Visual Studio Code

Ready to get started? Version: 2025.6 just released

View Licenses >