Hoy un artículo cortito, en el que quiero compartir contigo cómo usar Azure Functions y Python para recuperar los datos de una tabla de Excel en formato JSON.
Blob trigger
Este ejemplo sería si queremos utilizar un blob trigger para recuperar el archivo Excel de una cuenta de almacenamiento:
import logging
import azure.functions as func
from io import BytesIO
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import json
def main(myblob: func.InputStream):
logging.info(f"Python blob trigger function processed blob \n"
f"Name: {myblob.name}\n"
f"Blob Size: {myblob.length} bytes")
# Load the workbook
workbook = load_workbook(filename=BytesIO(myblob.read()))
logging.info(f"Loaded workbook: {workbook}")
# Get the active sheet
worksheet = workbook.active
excel_data = []
# Iterate through the rows and columns of the worksheet
last_column = len(list(worksheet.columns))
last_row = len(list(worksheet.rows))
# Iterate row
for row in range(1, last_row + 1):
row_data = {}
# Iterate column
for column in range(1, last_column + 1):
# Get the value of the cell
column_letter = get_column_letter(column)
if row > 1:
row_data[worksheet[column_letter +
str(1)].value] = worksheet[column_letter + str(row)].value
if row_data:
excel_data.append(row_data)
# Convert to JSON
data = json.dumps(excel_data, sort_keys=True, indent=4)
logging.info(f"Converted to JSON: {data}")
Http trigger
En este otro sería si queremos recuperar el archivo directamente de la llamada HTTP:
import logging
import azure.functions as func
from io import BytesIO
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import json
def main(req: func.HttpRequest) -> func.HttpResponse:
logging.info('Python HTTP trigger function processed a request.')
for input_file in req.files.values():
filename = input_file.filename
logging.info('Processing: %s' % filename)
# Load the workbook
workbook = load_workbook(filename=BytesIO(input_file.stream.read()))
logging.info(f"Loaded workbook: {workbook}")
# Get the active sheet
worksheet = workbook.active
excel_data = []
# Iterate through the rows and columns of the worksheet
last_column = len(list(worksheet.columns))
logging.info(f"Last column: {last_column}")
last_row = len(list(worksheet.rows))
logging.info(f"Last row: {last_row}")
# Iterate row
for row in range(1, last_row + 1):
row_data = {}
# Iterate column
for column in range(1, last_column + 1):
# Get the value of the cell
column_letter = get_column_letter(column)
logging.info(f"Column letter: {column_letter}")
if row > 1:
logging.info(f"{worksheet[column_letter + str(1)].value}")
row_data[worksheet[column_letter + str(1)].value] = worksheet[column_letter + str(row)].value
if row_data:
excel_data.append(row_data)
# Convert to JSON
data = json.dumps(excel_data, sort_keys=True, indent=4)
return func.HttpResponse(f'{data}')
El código de ambas funciones lo tienes en mi GitHub.
¡Saludos!