Cómo crear, leer, actualizar y buscar archivos de Excel usando Python

Este artículo mostrará en detalle cómo trabajar con archivos de Excel y cómo modificar datos específicos con Python.

Primero, aprenderemos cómo trabajar con archivos CSV leyéndolos, escribiéndolos y actualizándolos. A continuación, veremos cómo leer archivos, filtrarlos por hojas, buscar filas / columnas y actualizar celdas de archivos xlsx.

Comencemos con el formato de hoja de cálculo más simple: CSV.

Parte 1: el archivo CSV

Un archivo CSV es un archivo de valores separados por comas, donde los datos de texto sin formato se muestran en formato tabular. Se pueden utilizar con cualquier programa de hoja de cálculo, como Microsoft Office Excel, Google Spreadsheets o LibreOffice Calc.

Sin embargo, los archivos CSV no son como otros archivos de hojas de cálculo porque no le permiten guardar celdas, columnas, filas o fórmulas. Su limitación es que también permiten solo una hoja por archivo. Mi plan para esta primera parte del artículo es mostrarle cómo crear archivos CSV usando Python 3 y el módulo de biblioteca estándar CSV.

Este tutorial terminará con dos repositorios de GitHub y una aplicación web en vivo que realmente usa el código de la segunda parte de este tutorial (aún actualizado y modificado para un propósito específico).

Escribir en archivos CSV

Primero, abra un nuevo archivo de Python e importe el módulo CSV de Python.

import csv

Módulo CSV

El módulo CSV incluye todos los métodos necesarios integrados. Estos incluyen:

  • csv.reader
  • csv.writer
  • csv.DictReader
  • csv.DictWriter
  • y otros

En esta guía nos centraremos en los métodos de escritor, DictWriter y DictReader. Estos le permiten editar, modificar y manipular los datos almacenados en un archivo CSV.

En el primer paso debemos definir el nombre del archivo y guardarlo como variable. Deberíamos hacer lo mismo con el encabezado y la información de los datos.

filename = "imdb_top_4.csv" header = ("Rank", "Rating", "Title") data = [ (1, 9.2, "The Shawshank Redemption(1994)"), (2, 9.2, "The Godfather(1972)"), (3, 9, "The Godfather: Part II(1974)"), (4, 8.9, "Pulp Fiction(1994)") ]

Ahora necesitamos crear una función llamada escritor que tomará tres parámetros: encabezado , datos y nombre de archivo .

def writer(header, data, filename): pass

El siguiente paso es modificar la función del escritor para que cree un archivo que contenga datos del encabezado y variables de datos . Esto se hace escribiendo la primera fila de la variable de encabezado y luego escribiendo cuatro filas de la variable de datos (hay cuatro filas porque hay cuatro tuplas dentro de la lista).

def writer(header, data, filename): with open (filename, "w", newline = "") as csvfile: movies = csv.writer(csvfile) movies.writerow(header) for x in data: movies.writerow(x)
La documentación oficial de Python describe cómo funciona el método csv.writer. Le sugiero encarecidamente que se tome un minuto para leerlo.

¡Y voilá! Creó su primer archivo CSV llamado imdb_top_4.csv. Abra este archivo con su aplicación de hoja de cálculo preferida y debería ver algo como esto:

El resultado podría escribirse así si elige abrir el archivo en alguna otra aplicación:

Actualizar los archivos CSV

Para actualizar este archivo, debe crear una nueva función llamada actualizador que tomará solo un parámetro llamado nombre de archivo .

def updater(filename): with open(filename, newline= "") as file: readData = [row for row in csv.DictReader(file)] # print(readData) readData[0]['Rating'] = '9.4' # print(readData) readHeader = readData[0].keys() writer(readHeader, readData, filename, "update")

Esta función primero abre el archivo definido en la variable de nombre de archivo y luego guarda todos los datos que lee del archivo dentro de una variable llamada readData . El segundo paso es codificar el nuevo valor y colocarlo en lugar del anterior en la posición readData [0] ['Rating'] .

El último paso en la función es llamar al escritor de la función mediante la adición de un nuevo parámetro de actualización que le indicará la función que usted está haciendo una actualización.

csv.DictReader se explica más en la documentación oficial de Python aquí.

Para que el escritor funcione con un nuevo parámetro, debe agregar un nuevo parámetro en todos los lugares donde se define el escritor . Volver al lugar en el que primero llamó al escritor función y agrega “escritura” como un nuevo parámetro:

writer(header, data, filename, "write")

Justo debajo de la función de escritura, llame al actualizador y pase el parámetro de nombre de archivo en él:

writer(header, data, filename, "write") updater(filename)

Ahora tiene que modificar el escritor función para tomar un nuevo parámetro denominado opción :

def writer(header, data, filename, option):

A partir de ahora esperamos recibir dos opciones diferentes para el escritor de función ( de escritura y actualización ). Por eso, debemos agregar dos declaraciones if para admitir esta nueva funcionalidad. La primera parte de la función bajo "if option ==" write: " ya la conoce. Solo necesita agregar la sección " elif option ==" update ": del código y la parte else tal como están escritas a continuación:

def writer(header, data, filename, option): with open (filename, "w", newline = "") as csvfile: if option == "write": movies = csv.writer(csvfile) movies.writerow(header) for x in data: movies.writerow(x) elif option == "update": writer = csv.DictWriter(csvfile, fieldnames = header) writer.writeheader() writer.writerows(data) else: print("Option is not known")

¡Bravo! ¡Ya terminaste!

Ahora su código debería verse así:

También puede encontrar el código aquí:

//github.com/GoranAviani/CSV-Viewer-and-Editor

En la primera parte de este artículo hemos visto cómo trabajar con archivos CSV. Hemos creado y actualizado uno de esos archivos.

Parte 2 - El archivo xlsx

For several weekends I have worked on this project. I have started working on it because there was a need for this kind of solution in my company. My first idea was to build this solution directly in my company’s system, but then I wouldn’t have anything to write about, eh?

I build this solution using Python 3 and openpyxl library. The reason why I have chosen openpyxl is because it represents a complete solution for creating worksheets, loading, updating, renaming and deleting them. It also allows us to read or write to rows and columns, merge or un-merge cells or create Python excel charts etc.

Openpyxl terminology and basic info

  • Workbook is the name for an Excel file in Openpyxl.
  • A workbook consists of sheets (default is 1 sheet). Sheets are referenced by their names.
  • Una hoja consta de filas (líneas horizontales) que comienzan desde el número 1 y columnas (líneas verticales) que comienzan desde la letra A.
  • Las filas y columnas dan como resultado una cuadrícula y forman celdas que pueden contener algunos datos (valores numéricos o de cadena) o fórmulas.
Openpyxl está muy bien documentado y le aconsejo que eche un vistazo aquí.

El primer paso es abrir su entorno Python e instalar openpyxl dentro de su terminal:

pip install openpyxl

A continuación, importe openpyxl a su proyecto y luego cargue un libro de trabajo en la variable Archivo .

import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') print(theFile.sheetnames) currentSheet = theFile['customers 1'] print(currentSheet['B4'].value)

As you can see, this code prints all sheets by their names. It then selects the sheet that is named “customers 1” and saves it to a currentSheet variable. In the last line, the code prints the value that is located in the B4 position of the “customers 1” sheet.

This code works as it should but it is very hard coded. To make this more dynamic we will write code that will:

  • Read the file
  • Get all sheet names
  • Loop through all sheets
  • In the last step, the code will print values that are located in B4 fields of each found sheet inside the workbook.
import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names {} " .format(theFile.sheetnames)) for x in allSheetNames: print("Current sheet name is {}" .format(x)) currentSheet = theFile[x] print(currentSheet['B4'].value)

This is better than before, but it is still a hard coded solution and it still assumes the value you will be looking for is in the B4 cell, which is just silly :)

I expect your project will need to search inside all sheets in the Excel file for a specific value. To do this we will add one more for loop in the “ABCDEF” range and then simply print cell names and their values.

import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names {} " .format(theFile.sheetnames)) for sheet in allSheetNames: print("Current sheet name is {}" .format(sheet)) currentSheet = theFile[sheet] # print(currentSheet['B4'].value) #print max numbers of wors and colums for each sheet #print(currentSheet.max_row) #print(currentSheet.max_column) for row in range(1, currentSheet.max_row + 1): #print(row) for column in "ABCDEF": # Here you can add or reduce the columns cell_name = "{}{}".format(column, row) #print(cell_name) print("cell position {} has value {}".format(cell_name, currentSheet[cell_name].value))

We did this by introducing the “for row in range..” loop. The range of the for loop is defined from the cell in row 1 to the sheet’s maximum number or rows. The second for loop searches within predefined column names “ABCDEF”. In the second loop we will display the full position of the cell (column name and row number) and a value.

However, in this article my task is to find a specific column that is named “telephone” and then go through all the rows of that column. To do that we need to modify the code like below.

import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names {} " .format(theFile.sheetnames)) def find_specific_cell(): for row in range(1, currentSheet.max_row + 1): for column in "ABCDEFGHIJKL": # Here you can add or reduce the columns cell_name = "{}{}".format(column, row) if currentSheet[cell_name].value == "telephone": #print("{1} cell is located on {0}" .format(cell_name, currentSheet[cell_name].value)) print("cell position {} has value {}".format(cell_name, currentSheet[cell_name].value)) return cell_name for sheet in allSheetNames: print("Current sheet name is {}" .format(sheet)) currentSheet = theFile[sheet]

This modified code goes through all cells of every sheet, and just like before the row range is dynamic and the column range is specific. The code loops through cells and looks for a cell that holds a text “telephone”. Once the code finds the specific cell it notifies the user in which cell the text is located. The code does this for every cell inside of all sheets that are in the Excel file.

The next step is to go through all rows of that specific column and print values.

import openpyxl theFile = openpyxl.load_workbook('Customers1.xlsx') allSheetNames = theFile.sheetnames print("All sheet names {} " .format(theFile.sheetnames)) def find_specific_cell(): for row in range(1, currentSheet.max_row + 1): for column in "ABCDEFGHIJKL": # Here you can add or reduce the columns cell_name = "{}{}".format(column, row) if currentSheet[cell_name].value == "telephone": #print("{1} cell is located on {0}" .format(cell_name, currentSheet[cell_name].value)) print("cell position {} has value {}".format(cell_name, currentSheet[cell_name].value)) return cell_name def get_column_letter(specificCellLetter): letter = specificCellLetter[0:-1] print(letter) return letter def get_all_values_by_cell_letter(letter): for row in range(1, currentSheet.max_row + 1): for column in letter: cell_name = "{}{}".format(column, row) #print(cell_name) print("cell position {} has value {}".format(cell_name, currentSheet[cell_name].value)) for sheet in allSheetNames: print("Current sheet name is {}" .format(sheet)) currentSheet = theFile[sheet] specificCellLetter = (find_specific_cell()) letter = get_column_letter(specificCellLetter) get_all_values_by_cell_letter(letter) 

This is done by adding a function named get_column_letter that finds a letter of a column. After the letter of the column is found we loop through all rows of that specific column. This is done with the get_all_values_by_cell_letter function which will print all values of those cells.

Wrapping up

Sujetador gjort! Hay muchas cosas que puedes hacer después de esto. Mi plan era crear una aplicación en línea que estandarizara todos los números de teléfono suecos tomados de un cuadro de texto y ofreciera a los usuarios la posibilidad de simplemente copiar los resultados del mismo cuadro de texto. El segundo paso de mi plan fue ampliar la funcionalidad de la aplicación web para admitir la carga de archivos de Excel, el procesamiento de números de teléfono dentro de esos archivos (estandarizándolos a un formato sueco) y ofrecer los archivos procesados ​​a los usuarios.

He realizado ambas tareas y puede verlas en vivo en la página de Herramientas de mi sitio Incodaq.com :

//tools.incodaq.com/

Además, el código de la segunda parte de este artículo está disponible en GitHub:

//github.com/GoranAviani/Manipulate-Excel-spreadsheets

Thank you for reading! Check out more articles like this on my Medium profile: //medium.com/@goranaviani and other fun stuff I build on my GitHub page: //github.com/GoranAviani