How to Edit Excel Files in Python

Excel files are universally used in data management and analysis tasks. However, manually editing large Excel files can be time-consuming and prone to errors. Python offers powerful libraries and tools for automating Excel spreadsheet editing tasks, making the process more efficient and reliable.

How to Edit Excel Files in Python

1. Install IronXL library for Excel file manipulation in Python.
2. Load the Excel workbook using IronXL.
3. Specify the desired spreadsheet within the workbook.
4. Write data to specific cell values using IronXL for easy modifications and updates.

In this tutorial, we'll explore how to edit Excel files using Python. We will use the IronXL library for reading, writing, and manipulating Excel files, along with practical examples to illustrate each concept. Excel files can contain structured data organized into rows and columns, along with formatting, formulas, and other features. IronXL provides methods for reading, writing, and modifying Excel files programmatically, enabling users to perform various data manipulation tasks efficiently. Before proceeding further, let's explore what IronXL is.

What is IronXL?

IronXL is a powerful library for working with Excel files in Python applications. It provides developers with convenient methods for reading Excel files, editing, and manipulating Excel files data, and streamlining data management tasks. Developers can easily import data from various file formats such as XLS, XLSX, CSV, and TSV files, and work with spreadsheets.

IronXL offers a wide range of features to facilitate efficient Excel file editing. Developers can export worksheets to multiple formats including XLS files, XLSX, CSV file, TSV files, and JSON, enabling seamless data exchange between different systems. Additionally, IronXL supports encryption and decryption of XLSX, XLSM, and XLTX files with passwords, ensuring data security. IronXL ensures accurate data processing by providing the ability to recalculate Excel formulas every time a sheet is edited. Moreover, developers can manipulate Excel files by using range settings, sorting ranges, columns, and rows, and styling cells with customizable font, size, background pattern, border, alignment, and number formats. IronXL empowers developers to efficiently manage Excel data, enhancing productivity and flexibility in Python applications.

Open or Create New Project

Create a New Project or open an existing one in your IDE as per requirement. We are creating a new Project in MS Visual Studio 2022. You can use any IDE. The code will remain the same for each IDE.

Installing IronXL Library

We need to install the IronXL library for working with Excel files. You can install them using pip, the Python package manager:

pip install IronXL

pip install IronXL

This command will install the IronXL Library, which will later be used in the tutorial for Excel file manipulation in Python.

Load Excel Workbook

The very first step is to load an Excel file workbook to our project. We will load the following sample Excel File. We will use the same Excel for all examples discussed in this tutorial.

The following code will load the Excel file in our project.

from ironxl import *     
License.LicenseKey = "IRONSUITE.TEST.TUTORIAL.COM.12345-ABC-XYZ-XYZ-XYZ-ABC-ABC-QWERTY-XYZ-ABC-DEPLOYMENT.TRIAL-123V.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("test_excel.xlsx")

from ironxl import *     
License.LicenseKey = "IRONSUITE.TEST.TUTORIAL.COM.12345-ABC-XYZ-XYZ-XYZ-ABC-ABC-QWERTY-XYZ-ABC-DEPLOYMENT.TRIAL-123V.TRIAL.EXPIRES.27.MAY.2024";
workbook = WorkBook.Load("test_excel.xlsx")

The above code snippet demonstrates how to use the IronXL Python library to load an Excel file named "test_excel.xlsx" into a workbook object. First, it sets the IronXL license key for authentication. Then, it imports the required modules from IronXL. Finally, it loads the Excel file using the WorkBook.Load() method, allowing developers to access and manipulate the data within the workbook using IronXL's functionality.

Select Excel Spreadsheet

The next step is to select Excel Spreadsheets in Python. There could be multiple Excel sheets in one file, so we need to specify the active sheet.

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

# Select worksheet at index 0
worksheet = workbook.WorkSheets[0]

The above line of code selects the worksheet located at index 0 within the workbook object, enabling developers to manipulate Excel spreadsheets effectively. By specifying the index, it allows access to a specific Excel sheet within the Excel file for further analysis or modification.

Write Excel files using Python

Now, let's write data to our Excel file by adding a new row to our dataset. The following code will accomplish that:

// Python edit Excel file
worksheet["A11"].IntValue = 10
worksheet["B11"].Value = 'Kate'
worksheet["C11"].Value = 'Brown'
worksheet["D11"].Value = 'Female'
worksheet["E11"].Value = 'Indonesia'
worksheet["F11"].IntValue = 26
worksheet["G11"].Value = '16/10/2024'
worksheet["H11"].IntValue = 6548
workbook.Save()

// Python edit Excel file
worksheet["A11"].IntValue = 10
worksheet["B11"].Value = 'Kate'
worksheet["C11"].Value = 'Brown'
worksheet["D11"].Value = 'Female'
worksheet["E11"].Value = 'Indonesia'
worksheet["F11"].IntValue = 26
worksheet["G11"].Value = '16/10/2024'
worksheet["H11"].IntValue = 6548
workbook.Save()

The above code modifies existing Excel files by inserting data into specific cells. It assigns cell values to cells in columns A to H for row 11, appending data in the existing spreadsheet. After making these changes, the workbook is saved, ensuring that the modifications are persisted. This functionality enables automated editing of Excel files, facilitating tasks such as data entry, updates, or appending new records.

Using Formula in Excel file

In this next step, we'll use IronXL to compute the average age of customers directly within the Excel file. By harnessing the power of formulas, we can efficiently calculate aggregate statistics, enhancing data analysis capabilities. We can streamline the process of performing complex calculations, empowering users to derive valuable insights from their Excel data effortlessly. Let's dive into computing the average age of customers in our Excel file.

#Apply average value of all numeric cells within the range
avgAge = worksheet["F2:F11"].Avg();
print(f"Average Age is: {avgAge}")

#Apply average value of all numeric cells within the range
avgAge = worksheet["F2:F11"].Avg();
print(f"Average Age is: {avgAge}")

The above code calculates the average value of numeric cells within the specified range "F2:F11" in the Excel worksheet. IronXL's capabilities enable seamless computation of statistical metrics directly within Excel files, enhancing data analysis workflows. By simply invoking the Avg() method on the range, IronXL efficiently computes the average age of customers, demonstrating its ability to streamline complex calculations within Excel documents.

Conclusion

In conclusion, this tutorial has provided a comprehensive overview of editing Excel files using Python with the IronXL library. By leveraging IronXL's powerful features, developers can seamlessly manipulate data in Excel files to streamline data management tasks. With the ability to read, write, and modify Excel data programmatically, IronXL empowers users to analyze data efficiently and derive valuable insights from XLSX files. Moreover, its support for complex calculations and formulas enables advanced data analysis within Excel, enhancing productivity and accuracy in data manipulation workflows.

Furthermore, IronXL offers a free trial, developers can unlock the full potential of IronXL, enabling them to manipulate data with confidence and efficiency. Whether it's analyzing data, manipulating XLSX files, or performing complex calculations, IronXL provides a versatile solution for Excel file editing tasks, enhancing the capabilities of Python applications in data management endeavors.