Tutorial práctico de Python Openpyxl con ejemplos

hands python openpyxl tutorial with examples

La guía completa de la biblioteca de Python Openpyxl incluye la instalación, cómo crear una nueva hoja de trabajo, leer / escribir / eliminar datos del libro de trabajo:

Excel es la aplicación de hoja de cálculo más popular que se utiliza para organizar mejor la información, realizar cálculos, producir gráficos de salida y mucho más. En el mundo de las pruebas, verificar la aplicación con varios conjuntos de datos es crucial para obtener un producto de alta calidad y una buena cobertura de prueba.

Openpyxl es una de esas bibliotecas que nos permite usar Excel para realizar pruebas basadas en datos e incluso para preparar informes de resultados de pruebas, etc.

En este tutorial aprenderemos qué es Openpyxl, su instalación, creando un nuevo libro de trabajo, leyendo datos de la hoja de cálculo y también escribiendo datos en él. También aprenderemos a cargar y descargar el mismo desde una página web.

Lo que vas a aprender:

• Qué es Openpyxl
  • Cómo instalar Openpyxl
  • Configuración de PyCharm IDE
  • Crear un nuevo libro de Excel
  • Escribir datos en el archivo de Excel
  • Cambiar el nombre de la hoja en un archivo de Excel
  • Agregar hojas al archivo de Excel
  • Agregar varios valores a la hoja
  • Eliminar una hoja del libro de Excel
  • Leer datos del archivo de Excel
  • Leer todos los valores en el archivo
  • Obtener letra de columna
• Pruebas basadas en datos
  • Crear un archivo de Excel con datos de entrada
  • Escritura de guión de prueba
  • Subir archivo
  • Descargar archivo
  • Preguntas frecuentes
• Conclusión
  • Lectura recomendada

Qué es Openpyxl

Openpyxl es una biblioteca de Python que se utiliza para leer y escribir archivos de Excel (archivos xlsx / xlsm / xltx / xltm). Este módulo permite a los programas de Python leer y modificar la hoja de cálculo.

XLSX archivo es el formato de archivo predeterminado para Microsoft Excel. Se basa en el estándar Office Open XML.

XLSM El archivo es un archivo de hoja de cálculo habilitado para macros.

XLTX El archivo es un archivo de plantilla de Excel que conserva la configuración definida por el usuario.

XLTM El archivo es un archivo de plantilla habilitado para macros que se crea con Microsoft Excel.

Cómo instalar Openpyxl

Cuando instala Python, las bibliotecas Openpyxl no se instalan de forma predeterminada. Tenemos que ejecutar un comando para obtener las bibliotecas en nuestro Python. Para esto, debe abrir el símbolo del sistema y cambiar el directorio a la carpeta donde se coloca su Python y ejecutar el siguiente comando.

Una vez que ejecute el comando, las bibliotecas se descargarán e instalarán. Esto se puede verificar usando lista de pips comando como se muestra en la imagen a continuación.

Configuración de PyCharm IDE

Hay dos formas de configurar las bibliotecas Openpyxl para un proyecto en PyCharm.

# 1) Uso de la opción de paquetes disponibles en PyCharm

Haga clic en Archivo-> Nuevo proyecto. Haga clic en Crear.

Su proyecto se creará con éxito. Para verificar si las bibliotecas están configuradas, vaya a Archivo -> Configuración . En la página de configuración, vaya a Proyecto -> Intérprete de proyectos .

En Paquetes, verá el paquete Openpyxl. Si eso falta, presione el botón ' + ”En la esquina derecha. En Paquetes disponibles, busque Openpyxl y Hit Install Package, como se muestra en la imagen a continuación. Una vez completada la instalación, obtendrá el 'paquete 'openpyxl' instalado correctamente'

Verifique si el paquete se instaló correctamente.

# 2) Uso de la opción Heredar de paquetes de sitios globales

Este método es bastante sencillo. Ir Archivo-> Nuevo proyecto . Al crear un nuevo proyecto, seleccione ' Heredar paquetes de sitios globales ”Casilla de verificación. Una vez creado el proyecto, navegue hasta Archivo -> Configuración-> Proyecto -> Intérprete de proyecto , verá que el paquete Openpyxl ya está instalado.

Crear un nuevo libro de Excel

Comenzaremos creando un nuevo libro de trabajo. Un archivo de Excel se llama Libro de trabajo y contiene un mínimo de una hoja. Para crear un libro de trabajo, primero tenemos que importar el libro de trabajo desde la biblioteca Openpyxl usando el siguiente comando:

Creemos un objeto de libro de trabajo. Podemos usar este objeto para manejar todas las acciones que deben realizarse, como leer, escribir, etc.

Establezca la ruta del archivo donde desea crear el libro de trabajo, como se muestra a continuación:

Para guardar el uso del libro

Esto creará un nuevo archivo de Excel vacío en la ruta especificada.

Si desea crear un nuevo archivo de Excel en la misma ubicación que los scripts de Python, puede omitir la ruta del archivo y mencionar directamente el nombre del archivo en wb.save comando como se muestra a continuación:

Escribir datos en el archivo de Excel

Ahora que sabemos cómo crear un archivo nuevo, sigamos adelante y completemos los datos en el archivo. Para lograr esto, tenemos que cargar el libro de trabajo que hemos creado anteriormente. Esto se puede hacer usando el siguiente comando:

Tenemos que especificar en qué Hoja vamos a introducir los datos. Como solo tenemos una hoja, podemos usar la palabra clave 'activa' y esta usará la hoja activa actual para ingresar los datos.

En caso de que tenga varias hojas, debe mencionar el nombre de la hoja de trabajo, como se indica a continuación. Donde SheetName es el nombre de la hoja en el libro.

Hay 2 formas de ingresar los datos en el archivo de Excel. Estos son los siguientes:

• Utilice directamente la combinación de columnas. Ejemplo (A1), donde A es la columna y 1 es la fila.
• Utilice los números de fila y columna. Ejemplo fila = 4, columna = 2

Asegúrese de guardar el archivo después de ingresar los valores.

Código completo:

El código anterior escribirá 'Ayuda de prueba de software', 10 y 'Tutorial de Openpyxl', 13.4 en 1^{S t}y 2^{Dakota del Norte}fila respectivamente como se muestra a continuación.

Cambiar el nombre de la hoja en un archivo de Excel

En caso de que esté realizando un seguimiento de la fecha del informe y desee cambiar el nombre de la hoja a la fecha actual. Se puede hacer mediante el comando 'título'.

Para este escenario, necesitamos obtener la fecha actual. Entonces, primero, necesitamos importar el paquete datetime.

ahora() nos dará la fecha y hora actual .strftime () El método devuelve una cadena que representa la fecha y la hora.

Puede cambiar el nombre de la hoja usando sheet.title método. No olvide guardar el archivo.

Código completo:

Dado que hemos utilizado la hoja activa, se ha cambiado el nombre de la hoja activa actual.

Agregar hojas al archivo de Excel

Teniendo en cuenta el ejemplo anterior para realizar un seguimiento del informe de todos los días, debemos crear una nueva hoja para cada día. Para agregar nuevas hojas a Excel, use el create_sheet mando.

Sintaxis:

Cree una hoja de trabajo (en un índice opcional).

Parámetros:

• título (str): título opcional de la hoja.
• índice (int): posición opcional en la que se insertará la hoja.

Nota: Si no se ingresan los parámetros, entonces crea la hoja después de la última hoja y la nombra como 'hoja'.

Código completo:

Resultado:

Agregar varios valores a la hoja

Podemos agregar un grupo de valores al final de la hoja actual. Esto se puede lograr 'adjuntar' mando.

Sintaxis:

Donde iterable es una lista de valores o un valor único.

preguntas y respuestas de la entrevista de shell scripting para experimentados

• Si es una lista, todos los valores se suman en orden, comenzando desde la primera columna.
• Si es un valor, luego, los valores se asignan a las columnas indicadas por las teclas (números o letras).

Consulte el código a continuación:

Resultado:

Nota: Puede imprimir los nombres de las hojas usando imprimir ('título de la hoja activa:' + sheet.title)

Eliminar una hoja del libro de Excel

En Openpyxl, si elimina una hoja, tenga un poco de cuidado, ya que no es recuperable. Creemos una hoja de Excel con 3 hojas y llámela como 'Hoja1' 'Hoja2' 'Hoja3' y guárdelo como DeleteSheet.xlsx .

Siempre es mejor conocer las hojas, que ya están presentes antes de borrar alguna. Puedes pasarlo nombres de hoja mando.

Entonces puedes usar wb.remove (nombre de la hoja) o del wb(sheetname)

Donde, wb es el libro de trabajo que ha cargado.

Consulte el código a continuación:

Resultado:

Leer datos del archivo de Excel

De manera similar a escribir datos en el archivo, podemos leer los datos de un archivo de Excel de 2 formas.

Cualquiera, utilizar directamente la combinación de columnas . Ejemplo (A1) o, usar los números de fila y columna . Ejemplo fila = 4, columna = 2.

sheet.cell (). value El comando nos ayudará a recuperar el valor de la celda especificada.

Encuentre el código completo a continuación:

Resultado:

Leer todos los valores en el archivo

Por lo general, en las pruebas de software, necesitamos verificar la funcionalidad utilizando varios conjuntos de datos. Suponga que todos los datos se colocan en un libro de trabajo y necesitamos probar la funcionalidad utilizando todos los datos. Por lo tanto, debemos recorrer cada fila y columna para recuperar los valores. Para un gran conjunto de datos, el método anterior no es factible. Para lograr esto, tenemos que recorrer toda la hoja para obtener los valores.

Primero necesitamos buscar el número de filas y el número de columnas en la hoja.

• max_row- Esto da el índice de fila máximo que contiene datos (basado en 1)
• max_column - El índice de columna máximo que contiene datos (basado en 1)

Nota: max_row y max_column son índices basados ​​en 1. Para ir a la última fila, debemos agregar uno al valor de conteo.

A continuación se muestra el código completo para leer los valores:

Nota: Hemos utilizado end = ’‘ para tener un espacio entre cada columna y la nueva línea ( n) para imprimir los valores en una nueva fila.

Resultado:

Obtener letra de columna

Suponga que tiene un gran conjunto de datos almacenados en un libro de trabajo y desea saber en qué índice de fila y columna está disponible un valor particular. Puedes encontrar eso usando get_column_letter. Esto convierte un índice de columna en una letra de columna. Ejemplo 3 a C.

Consulte el siguiente código:

Resultado:

Pruebas basadas en datos

Ahora que sabemos que Openpyxl nos ayuda a leer y escribir datos desde Excel, usaremos estos conceptos para construir un caso de prueba para probar la aplicación con múltiples valores diferentes recuperados de la hoja de cálculo. Por lo tanto, lograr pruebas basadas en datos.

>> Lectura recomendada: Cómo funcionan las pruebas basadas en datos

Considere un escenario en el que iniciaremos sesión en Facebook.com, leeremos las credenciales de inicio de sesión del libro de trabajo, verificaremos el título de la página después de iniciar sesión y escribiremos el resultado en el mismo libro de trabajo.

Repita el escenario anterior para varias credenciales de inicio de sesión

Crear un archivo de Excel con datos de entrada

Creemos un archivo con las credenciales de inicio de sesión, el título esperado, el título real y el campo de resultados y guárdelo como Inputdata.xlsx.

Nota: Este es un archivo ficticio que se usa para mostrar un ejemplo. Complete el correo electrónico y la contraseña correctos mientras practica el guión.

Escritura de guión de prueba

Mirando el archivo de entrada anterior, podemos decir que tenemos que repetir el script 4 veces para 4 credenciales diferentes. Entonces, para evitar la redundancia, creemos una clase separada que contendrá funciones para leer y escribir el archivo de Excel. Lo hemos nombrado ExcelUtils.

A continuación se muestra el código ExcelUtils:

cuál es la mejor eliminación de malware

Ahora importe la clase ExcelUtils.py a su clase principal y podremos reutilizar las funciones de lectura y escritura.

Leeremos el correo electrónico y la contraseña del archivo de Excel e iniciaremos sesión en Facebook. Si el inicio de sesión es exitoso, el script actualizará el resultado en el archivo como Pasado. Si el inicio de sesión no se realiza correctamente, el script actualizará el título real de la página y marcará el resultado como Fallido.

Nota: Instale las bibliotecas de Selenium en su IDE. No puede ejecutar el código sin eso. pip instalar selenio El comando se usa para instalar bibliotecas de Selenium.

A continuación se muestra el código completo:

Resultado:

Subir archivo

Una vez que comiences a automatizar los proyectos en tiempo real, tarde o temprano vas a realizar cargas de archivos. Por ejemplo, cargando el archivo de resultado en la unidad compartida o la aplicación probando cargando un archivo, etc. El problema con la carga de archivos es que, una vez que hace clic en el botón de carga, el cuadro de diálogo de selección de archivo que se abre no es propiedad del navegador no puede controlarlo usando selenio.

Para lograr esto, necesitamos pasar directamente la ruta del archivo al elemento de entrada.

Deberíamos seleccionar el elemento de entrada y luego pasarle la ruta del archivo usando el send_keys método.

(imagen fuente )

En este ejemplo, estamos tomando el elemento del campo de entrada de id ('contenido') y pasando la ruta del archivo directamente usando las claves de envío.

A continuación se muestra el código para cargar un archivo de Excel.

Resultado:

Descargar archivo

Descargar un archivo es bastante simple en Selenium. Busque el elemento web del archivo y el botón de descarga. Pero, por lo general, los archivos se descargan en la carpeta de descargas.

Si desea descargar los archivos en una carpeta específica, tendrá que usar las opciones de Chrome y especificar la ruta de la carpeta donde se debe descargar el archivo.

Consulte el código a continuación:

Este código descargará el archivo en la ruta especificada por usted.

Resultado:

Preguntas frecuentes

P # 1) ¿Cómo instalo Openpyxl?

Responder: Tenemos que ejecutar un comando para obtener las bibliotecas en nuestro Python. Abra el símbolo del sistema y cambie el directorio al directorio de trabajo de Python y ejecute el siguiente comando.

P # 2) ¿Puedo usar Python en Excel?

Responder: El módulo Openpyxl permite a Python leer datos de un archivo de Excel y también nos permite escribir / modificar / agregar datos al mismo.

P # 3) ¿Cómo cargo un libro de trabajo en Openpyxl?

Responder:

#Importar load_workbook
desde openpyxl importar load_workbook

#Cargar el libro de trabajo
wb = load_workbook (ruta del libro de trabajo)

Ejemplo: wb = load_workbook ('C: \ Users \ demo.xlsx')

P # 4) ¿Funciona Openpyxl con XLS?

Responder: No, no es compatible con XLS. Si intenta utilizar el formato de archivo .XLS, Python lanzará una excepción.

elevar InvalidFileException (msg)

openpyxl.utils.exceptions.InvalidFileException: openpyxl no es compatible con el antiguo formato de archivo .xls; utilice xlrd para leer este archivo o conviértalo al formato de archivo .xlsx más reciente.

Más aprendizaje = >> Manejo de excepciones en Python

P # 5) ¿Cómo encuentro qué versión de Openpyxl tengo?

Responder: Abra el símbolo del sistema. Cambie el directorio a la carpeta donde está instalado Python, luego ejecute el comando lista de pips . Esto le dará todos los paquetes instalados junto con su versión.

P # 6) ¿Funciona Openpyxl con Excel 2016?

Responder: Sí, admite los formatos de archivo xlsx, xlsm, xltx y xltm.

P # 7) ¿Cómo creo una nueva hoja en Openpyxl?

Responder: Para agregar nuevas hojas al uso de Excel

Cree una hoja de trabajo (en un índice opcional).

Parámetros :

• título (str) - título opcional de la hoja.
• índice (int) - posición opcional en la que se insertará la hoja.

Nota: no ingresa los parámetros If, entonces crea una hoja después de la última hoja y la nombra como 'hoja'

Ejemplo - wb.create_sheet (índice = 3, título = 'Día4')

P # 8) ¿Cómo cierro un libro en Openpyxl?

Respuesta: Workbook_Object.close ()

Esto cerrará el archivo del libro de trabajo si está abierto. Solo afecta a los modos de solo lectura y solo escritura. Ejemplo – Wb.close ()

Una vez que cierre el libro de trabajo. No podrá volver a utilizarlo hasta que vuelva a cargar el libro de trabajo.

P # 9) ¿Cómo resolverlo? ¿No hay módulo llamado error 'openpyxl'?

Responder: ModuleNotFoundError: no se produce ningún error de módulo denominado openpyxl cuando ese módulo no está instalado.

Si tiene las versiones Python2.xy Python3.x instaladas en la misma máquina, use pip para instalar en Python2.xy pip3 para instalar en Python3.x.

Ejemplo –

pip install openpyxl - Instale el módulo Openpyxl en Python2.x

pip3 instalar openpyxl - Instale el módulo Openpyxl en Python3.x

P # 10) ¿Cómo abrir un archivo de Excel que ya existe usando Openpyxl?

Responder: Si ya tiene un archivo de Excel, utilice el load_workbook función para abrir.

Si el archivo está ubicado en su directorio de trabajo actual de Python, simplemente escriba el nombre del archivo como un argumento para esta función.

Ejemplo: de openpyxl import load_workbook
wb = load_workbook ('myexcelfile.xlsx')

Si su archivo no está en su directorio de trabajo de Python, entonces mencione la ruta del archivo como parámetro para cargar el libro de trabajo.

Ejemplo: de openpyxl import load_workbook
wb = load_workbook ('C: \ Users \ myexcelfile.xlsx')

Conclusión

En este tutorial, hemos aprendido sobre la biblioteca Openpyxl que nos brinda la flexibilidad de interactuar con Excel y, por lo tanto, facilita las pruebas basadas en datos.

A continuación se menciona la esencia de este tutorial:

1. Es una biblioteca de Python que nos permite usar Excel para realizar acciones como leer datos y escribir datos.
2. Esta biblioteca de Python se puede instalar con un solo comando pip instalar openpyxl .
3. Este módulo se puede instalar fácilmente en PyCharm IDE usando la ventana Paquetes disponibles.
4. Hemos visto cómo crear un libro de trabajo y escribir datos en el mismo.
5. También nos permite renombrar los nombres de las hojas, crear nuevas hojas y también eliminar las hojas.
6. La lectura de un valor de celda individual o la lectura de todo el libro de trabajo se puede hacer usando esta biblioteca.
7. También hemos visto un script de prueba de muestra que muestra cómo se puede realizar una prueba basada en datos.
8. En este tutorial también se describe la carga y descarga del archivo usando selenio.

Lectura recomendada

• Cómo abrir un archivo XML en Excel, Chrome y MS Word
• Tutorial de Python para principiantes (entrenamiento práctico de Python GRATIS)
• Trabajar con objetos de Excel de VBScript
• 12 mejores IDE y editores de código de Python en 2021
• Cómo leer o escribir datos desde una hoja de Excel en el controlador web Selenium
• Tutorial de manejo de archivos de Python: cómo crear, abrir, leer y escribir
• Tutorial de la función principal de Python con ejemplos prácticos
• Tutorial de Python Tuple con ejemplos prácticos