Ícono de mensaje
Saltar al pie de página
  1. IronXL para Python
  2. Blog de IronXL para Python
  3. Usando IronXL para Python
  4. Python Edita Archivo de Excel
USANDO IRONXL PARA PYTHON

Cómo Editar Archivos de Excel en Python

Curtis Chau
Curtis Chau
Actualizado:

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 the 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 files, 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.

How to Edit Excel Files in Python: Figure 1 - New Project

Installing IronXL Library

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

pip install IronXL
pip install IronXL
SHELL

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

How to Edit Excel Files in Python: Figure 2 - Install IronXL

Load Excel Workbook

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

How to Edit Excel Files in Python: Figure 3 - Excel File Input

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

from ironxl import *     

# Set the IronXL license key
License.LicenseKey = "IRONSUITE.TEST.TUTORIAL.COM.12345-ABC-XYZ-XYZ-XYZ-ABC-ABC-QWERTY-XYZ-ABC-DEPLOYMENT.TRIAL-123V.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook
workbook = WorkBook.Load("test_excel.xlsx")
from ironxl import *     

# Set the IronXL license key
License.LicenseKey = "IRONSUITE.TEST.TUTORIAL.COM.12345-ABC-XYZ-XYZ-XYZ-ABC-ABC-QWERTY-XYZ-ABC-DEPLOYMENT.TRIAL-123V.TRIAL.EXPIRES.27.MAY.2024"

# Load the Excel workbook
workbook = WorkBook.Load("test_excel.xlsx")
PYTHON

The above code snippet demonstrates how to use the IronXL for 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 the worksheet at index 0
worksheet = workbook.WorkSheets[0]
# Select the worksheet at index 0
worksheet = workbook.WorkSheets[0]
PYTHON

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: insert data into specific cells
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

# Save the workbook with modifications
workbook.Save()
# Python edit Excel file: insert data into specific cells
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

# Save the workbook with modifications
workbook.Save()
PYTHON

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.

How to Edit Excel Files in Python: Figure 4 - XLSX File

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 calculation to all numeric cells within the range
avgAge = worksheet["F2:F11"].Avg()

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

# Display the average age
print(f"Average Age is: {avgAge}")
PYTHON

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.

How to Edit Excel Files in Python: Figure 5 - Age Output using Excel Formula

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, allowing developers to 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.

Preguntas Frecuentes

¿Cómo puedo automatizar la manipulación de archivos de Excel en Python?

Puedes automatizar la manipulación de archivos de Excel en Python usando la biblioteca IronXL. Proporciona métodos para leer, escribir y modificar archivos de Excel programáticamente, reduciendo la necesidad de intervención manual.

¿Qué pasos se involucran en editar archivos de Excel con Python?

Para editar archivos de Excel con Python, instala la biblioteca IronXL, carga el libro con WorkBook.Load(), selecciona la hoja de trabajo deseada, realiza tus cambios y guarda el archivo usando Save().

¿Cómo instalo IronXL para la manipulación de archivos Excel en Python?

Instala IronXL usando el gestor de paquetes pip con el comando: pip install IronXL. Esto agregará la biblioteca a tu entorno de Python para la manipulación de archivos de Excel.

¿Puedo usar Python para actualizar múltiples hojas en un archivo de Excel?

Sí, con IronXL, puedes actualizar múltiples hojas dentro de un archivo de Excel. Puedes navegar entre hojas usando su índice o nombre y aplicar cambios según sea necesario.

¿Cómo maneja IronXL las fórmulas complejas en archivos de Excel usando Python?

IronXL te permite incorporar cálculos complejos y fórmulas directamente en tus archivos de Excel. Soporta operaciones como promediar, sumar y recalcular fórmulas.

¿Qué formatos de archivo de Excel son compatibles con IronXL en Python?

IronXL soporta varios formatos de archivo de Excel, incluidos XLS, XLSX, CSV y TSV, proporcionando flexibilidad en el manejo de diferentes tipos de datos y estructuras.

¿Es posible asegurar archivos de Excel manipulados por Python?

Sí, IronXL te permite encriptar y desencriptar archivos de Excel como XLSX, XLSM y XLTX con contraseñas, asegurando la seguridad de los datos durante la manipulación.

¿Puedo probar IronXL gratis para la manipulación de Excel en Python?

Sí, IronXL ofrece una prueba gratuita, permitiendo a los desarrolladores explorar sus amplias funcionalidades para la manipulación de archivos de Excel sin ningún costo inicial.

¿Cómo puedo escribir datos en una celda específica en un archivo de Excel usando Python?

Con IronXL, puedes escribir datos en celdas específicas asignando valores a referencias de celdas en la hoja de trabajo y luego guardando los cambios usando el método Save().

Curtis Chau
Curtis Chau
  Chatea con el equipo de ingeniería ahora
Escritor Técnico

Curtis Chau tiene una licenciatura en Ciencias de la Computación (Carleton University) y se especializa en el desarrollo front-end con experiencia en Node.js, TypeScript, JavaScript y React. Apasionado por crear interfaces de usuario intuitivas y estéticamente agradables, disfruta trabajando con frameworks modernos y creando manuales bien ...

Leer más

Artículos Relacionados

Prueba de 30 días → Producto completo. Sin límites. Sin tarjeta. Comenzar prueba gratuita