Saltar al contenido

Curso sobre como trabajar con hojas de cálculo (Excel, Calc) usando openpyxl en Python (IV)

Etiquetas:

Índice:

Hemos visto por encima muchas de las cosas que podemos hacer con los libros y las hojas. Pero ahora vamos a una parte más importante de la información, las celdas, que es donde acabamos metiendo nuestros datos con los que queremos trabajar.

Empezamos importando cosas que usaremos en este capítulo:

import random
import datetime as dt

import openpyxl
from openpyxl.comments import Comment
from openpyxl.styles import Font, PatternFill, Color, Alignment, Border, Side
from openpyxl.styles.borders import BORDER_THICK
from openpyxl.styles.fills import FILL_SOLID
from openpyxl.worksheet.hyperlink import Hyperlink

Vamos a empezar creando un documento nuevo con algunos datos:

wb = openpyxl.Workbook()
ws = wb.active

for row in range(1, 101):
    for col in list("ABCDEFGHIJKL"):
        value = random.random()
        idx = f"{col}{row}"
        ws[idx] = value

Hemos insertado 100 filas y 12 columnas donde el valor de cada celda es un valor aleatorio entre 0 y 1.

Si guardamos el notebook y lo abrimos podemos ver cómo queda:

wb.save("new.xlsx")

Si abrís el resultado veréis algo como lo siguiente:

hoja de cálculos

La celda

No lo hemos comentado hasta ahora pero para acceder a la celda podemos usar su localización basada en el nombre de su columna y número de fila así “A1” nos daría la celda colocada en la columna A y en la fila 1. Tenemos más formas de acceder a las celdas. Vamos a ver alguna:

  • desde la hoja usando su método cell podemos obtener la celda o para darle un valor:
celda_a = ws.cell(1, 1)

Lo anterior sería equivalente a:

celda_b = ws["A1"]
print(celda_a is celda_b)

Y el resultado de lo anterior debería ser True.

Podríamos obtener todos los valores (que no objetos Cell) de una fila usando:

primera_fila = next(ws.values)
print(primera_fila)

El resultado sería algo como:

(0.6047202896994622, 0.4631788608638664, 0.5532250811862743, 0.2766069622897511, 0.2503075193258225, 0.14578096988182676, 0.7689579198941188, 0.821634921940374, 0.25763355746880623, 0.19170307327057512, 0.38284790906752786, 0.8751794411671501)

Podemos iterar sobre filas o columnas usando los métodos iter_rows o iter_cols, respectivamente.

# Ejemplo de como acceder a todas las celdas de la primera columna
for col in ws.iter_cols(min_col=1, max_col=1):
    for cell in col:
        print(cell)

Lo anterior mostrará en pantalla lo siguiente:

<Cell 'Sheet'.A1>
<Cell 'Sheet'.A2>
<Cell 'Sheet'.A3>
<Cell 'Sheet'.A4>
<Cell 'Sheet'.A5>
<Cell 'Sheet'.A6>
<Cell 'Sheet'.A7>
<Cell 'Sheet'.A8>
<Cell 'Sheet'.A9>
<Cell 'Sheet'.A10>
<Cell 'Sheet'.A11>
<Cell 'Sheet'.A12>
<Cell 'Sheet'.A13>
<Cell 'Sheet'.A14>
<Cell 'Sheet'.A15>
<Cell 'Sheet'.A16>
<Cell 'Sheet'.A17>
<Cell 'Sheet'.A18>
<Cell 'Sheet'.A19>
<Cell 'Sheet'.A20>
<Cell 'Sheet'.A21>
<Cell 'Sheet'.A22>
<Cell 'Sheet'.A23>
<Cell 'Sheet'.A24>
<Cell 'Sheet'.A25>
<Cell 'Sheet'.A26>
<Cell 'Sheet'.A27>
<Cell 'Sheet'.A28>
<Cell 'Sheet'.A29>
<Cell 'Sheet'.A30>
<Cell 'Sheet'.A31>
<Cell 'Sheet'.A32>
<Cell 'Sheet'.A33>
<Cell 'Sheet'.A34>
<Cell 'Sheet'.A35>
<Cell 'Sheet'.A36>
<Cell 'Sheet'.A37>
<Cell 'Sheet'.A38>
<Cell 'Sheet'.A39>
<Cell 'Sheet'.A40>
<Cell 'Sheet'.A41>
<Cell 'Sheet'.A42>
<Cell 'Sheet'.A43>
<Cell 'Sheet'.A44>
<Cell 'Sheet'.A45>
<Cell 'Sheet'.A46>
<Cell 'Sheet'.A47>
<Cell 'Sheet'.A48>
<Cell 'Sheet'.A49>
<Cell 'Sheet'.A50>
<Cell 'Sheet'.A51>
<Cell 'Sheet'.A52>
<Cell 'Sheet'.A53>
<Cell 'Sheet'.A54>
<Cell 'Sheet'.A55>
<Cell 'Sheet'.A56>
<Cell 'Sheet'.A57>
<Cell 'Sheet'.A58>
<Cell 'Sheet'.A59>
<Cell 'Sheet'.A60>
<Cell 'Sheet'.A61>
<Cell 'Sheet'.A62>
<Cell 'Sheet'.A63>
<Cell 'Sheet'.A64>
<Cell 'Sheet'.A65>
<Cell 'Sheet'.A66>
<Cell 'Sheet'.A67>
<Cell 'Sheet'.A68>
<Cell 'Sheet'.A69>
<Cell 'Sheet'.A70>
<Cell 'Sheet'.A71>
<Cell 'Sheet'.A72>
<Cell 'Sheet'.A73>
<Cell 'Sheet'.A74>
<Cell 'Sheet'.A75>
<Cell 'Sheet'.A76>
<Cell 'Sheet'.A77>
<Cell 'Sheet'.A78>
<Cell 'Sheet'.A79>
<Cell 'Sheet'.A80>
<Cell 'Sheet'.A81>
<Cell 'Sheet'.A82>
<Cell 'Sheet'.A83>
<Cell 'Sheet'.A84>
<Cell 'Sheet'.A85>
<Cell 'Sheet'.A86>
<Cell 'Sheet'.A87>
<Cell 'Sheet'.A88>
<Cell 'Sheet'.A89>
<Cell 'Sheet'.A90>
<Cell 'Sheet'.A91>
<Cell 'Sheet'.A92>
<Cell 'Sheet'.A93>
<Cell 'Sheet'.A94>
<Cell 'Sheet'.A95>
<Cell 'Sheet'.A96>
<Cell 'Sheet'.A97>
<Cell 'Sheet'.A98>
<Cell 'Sheet'.A99>
<Cell 'Sheet'.A100>

Muy bien, tenemos guardado un objeto Cell en la variable cell. Echemos un vistazo a todo lo que tenemos disponible:

print(dir(cell))

Lo anterior mostrará:

['__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__slots__', '__str__', '__subclasshook__', '_bind_value', '_comment', '_hyperlink', '_style', '_value', 'alignment', 'base_date', 'border', 'check_error', 'check_string', 'col_idx', 'column', 'column_letter', 'comment', 'coordinate', 'data_type', 'encoding', 'fill', 'font', 'has_style', 'hyperlink', 'internal_value', 'is_date', 'number_format', 'offset', 'parent', 'pivotButton', 'protection', 'quotePrefix', 'row', 'style', 'style_id', 'value']

Algo evidente es la property value que nos deja coger el valor que contiene la celda o nos permite darle un valor a la celda:

print(cell.value)

Nos mostrará el contenido de la celda.

Podemos saber qué celda es con la que estamos tratando o saber en qué fila está o en qué columna,…, usando:

print(f"fila: {cell.row}")
print(f"columna: {cell.column}")
print(f"columna (letra): {cell.column_letter}")
print(cell.coordinate)

Lo anterior muestra:

fila: 100
columna: 1
columna (letra): A
A100

Podríamos acceder a celdas que están colocadas en posiciones basadas en la posición de esta celda. Por ejemplo, si quiero la celda que está a su derecha (misma fila y siguiente columna) podría hacer:

celda_dcha = cell.offset(column=1)
print(f"fila: {celda_dcha.row}")
print(f"columna: {celda_dcha.column}")
print(f"columna (letra): {celda_dcha.column_letter}")
print(celda_dcha.coordinate)

El resultado de lo anterior será ahora:

fila: 100
columna: 2
columna (letra): B
B100

Puedo añadirle un comentario a la celda usando el método comment. Por ejemplo, voy a coger la celda “A1” y a añadirle un comentario:

c_a1 = ws["A1"]
comentario = c_a1.comment
comentario = Comment("This is a comment", "Kiko", 100, 100)
c_a1.comment = comentario

Vamos a guardar el libro y ver cómo se ve con el comentario:

wb.save("new.xlsx")

El resultado debería verse parecido a lo siguiente:

Celda con comentario

Podemos identificar el tipo de dato que tiene la celda:

print(c_a1.data_type)

El resultado será n.

Lo anterior indica que el tipo de dato es n lo cual no es muy informativo. Si nos vamos al módulo donde está el código del objeto Cell podemos ver que allí se definen los tipos:

  • `
TYPE_STRING = 's'
TYPE_FORMULA = 'f'
TYPE_NUMERIC = 'n'
TYPE_BOOL = 'b'
TYPE_NULL = 'n'
TYPE_INLINE = 'inlineStr'
TYPE_ERROR = 'e'
TYPE_FORMULA_CACHE_STRING = 'str'

Por tanto, el tipo del valor de la celda es numérico. Podemos formatear un poco el número para que no nos muestre tantos decimales. Veamos cómo hacer esto:

print(c_a1.number_format)
El resultado de lo anterior será General.

Vemos que el formato de número es General. En realidad esto es un estilo. La referencia para esto la podemos encontrar aquí.

Podemos cambiar ese estilo. Por ejemplo:

c_a1.number_format = "0.00"

Si guardamos y visualizamos como se ve ahora la celda “A1”:

wb.save("new.xlsx")

El resultado se vería así:

celda con número formateado

Podemos ver que ahora solo vemos dos decimales.

Podemos alinear lo que se ve en la celda:

al = Alignment(horizontal="center")
c_a1.alignment = al
print(c_a1.alignment)

El resultado de lo anterior será algo parecido a lo siguiente:

<openpyxl.styles.alignment.Alignment object>
Parameters:
horizontal='center', vertical=None, textRotation=0, wrapText=None, shrinkToFit=None, indent=0.0, relativeIndent=0.0, justifyLastLine=None, readingOrder=0.0

Ahora el contenido se vería centrado horizontalmente.

Podemos añadir bordes:

print(c_a1.border)

Se mostrará:

<openpyxl.styles.borders.Border object>
Parameters:
outline=True, diagonalUp=False, diagonalDown=False, start=None, end=None, left=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, right=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, top=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, bottom=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, diagonal=<openpyxl.styles.borders.Side object>
Parameters:
style=None, color=None, vertical=None, horizontal=None

Vamos a darle un borde nuevo:

border = Border(
    left=Side(border_style=BORDER_THICK, color='FF000000'),
    right=Side(border_style=BORDER_THICK, color='00FF0000'),
    top=Side(border_style=BORDER_THICK, color='0000FF00'),
    bottom=Side(border_style=BORDER_THICK, color='FFFF0000'),
)
c_a1.border = border

Guardamos y vemos el número alineado con dos decimales y los bordes de colores:

wb.save("new.xlsx")

Podemos cambiar el color del fondo y la fuente:

print(c_a1.fill)

Se mostrará:

<openpyxl.styles.fills.PatternFill object>
Parameters:
patternType=None, fgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb', bgColor=<openpyxl.styles.colors.Color object>
Parameters:
rgb='00000000', indexed=None, auto=None, theme=None, tint=0.0, type='rgb'

Métemos un poco de código:

fill = PatternFill(patternType=FILL_SOLID,
                   fgColor=Color(rgb="00000000"))
c_a1.fill = fill

Lo anterior dejará la celda en negro (y no veremos el número que había en la celda). Vamos a ver qué podemos hacer con ayuda de la clase Font:

print(c_a1.font)

Y ahora veremos:

<openpyxl.styles.fonts.Font object>
Parameters:
name='Calibri', charset=None, family=2.0, b=False, i=False, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object>
Parameters:
rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type='theme', extend=None, sz=11.0, u=None, vertAlign=None, scheme='minor'

Cambiamos la fuente y guardamos el documento:

font = Font(color="00FFFFFF")
c_a1.font = font

wb.save("new.xlsx")

El resultado debería verse así:

Celda formateada

Podríamos, también, proteger la celda.

Contenido de una celda

Como hemos visto una celda puede contener texto (como vimos en el capítulo anterior) y números (como estamos viendo en este capítulo). Pero una celda puede contener más cosas o podemos hacer que lo que contiene se vea de determinada forma.

Por ejemplo, una celda se puede ver como fecha:

c_a1.value = dt.datetime.utcnow()
print(c_a1.number_format)

Lo anterior mostrará yyyy-mm-dd h:mm:ss.

print(c_a1.data_type)

Ahora el tipo de dato será d.

print(c_a1.base_date)

Se mostrará 2415018.5.

print(c_a1.is_date)

Mostrará True.

Pero también podemos tener fórmulas, como ya sabemos:

c_form = ws["N1"]
c_form.value = "=SUM(B:B)"

print(c_form.number_format)
print(c_form.data_type)
print(c_form.internal_value)
print(c_form.value)

Lo anterior mostrará:

General
f
=SUM(B:B)
=SUM(B:B)

Notas sobre las fórmulas:

  • openpyxl nunca evalúa las fórmulas.
  • Para la fórmulas hemos de usar su nombre en inglés.
  • Los argumentos en las fórmulas se separan mediante comas, no punto y coma u otras cosas.
  • Las fórmulas más típicas se pueden encontrar en openpyxl.utils.FORMULAE. Si alguna fórmula sabemos que existe pero no está incluida en la especificación se puede usar con el prefijo _xlfn. para poder usarse.
print(openpyxl.utils.FORMULAE)

Lo anterior mostrará:

frozenset({'VALUE', 'WEIBULL', 'ABS', 'GESTEP', 'INDIRECT', 'TRUNC', 'CONCATENATE', 'INTERCEPT', 'IMEXP', 'NORMINV', 'COLUMN', 'FIXED', 'CUMIPMT', 'IMPOWER', 'CUBESET', 'GAMMALN', 'IMLN', 'KURT', 'HEX2OCT', 'ROWS', 'IMPRODUCT', 'MONTH', 'VLOOKUP', 'SIGN', 'PV', 'RECEIVED', 'ASC', 'LCM', 'PHONETIC', 'DSUM', 'WEEKNUM', 'BESSELY', 'CUBEMEMBER', 'DGET', 'AVEDEV', 'XIRR', 'SINH', 'ACOS', 'AMORDEGRC', 'SERIESSUM', 'SLOPE', 'STDEVPA STEYX', 'SUM', 'OFFSET', 'EXPONDIST', 'MINVERSE', 'DEC2BIN', 'MID', 'TAN', 'COUPPCD', 'IMSUM', 'ATAN2', 'LOG10', 'CEILING', 'LEFTB', 'DSTDEVP', 'IMAGINARY', 'CHAR', 'XNPV', 'DB', 'ZTEST', 'YEARFRAC', 'RIGHT', 'PROB', 'WEEKDAY', 'NA', 'EXACT', 'MDETERM', 'NPER', 'EVEN', 'HYPERLINK', 'COUNT', 'SEARCHB', 'PERCENTRANK', 'SKEW', 'LARGE', 'IRR', 'REPT', 'TBILLYIELD', 'CUBESETCOUNT', 'LOGNORMDIST', 'DEVSQ', 'TODAY', 'ODDFPRICE', 'DATEDIF', 'SUMPRODUCT', 'UPPER', 'IMABS', 'FLOOR', 'FORECAST', 'VARP', 'ISNUMBER', 'AVERAGEIF', 'PRICEMAT', 'SIN', 'MINUTE', 'IMCONJUGATE', 'LOGEST', 'FV', 'AVERAGEIFS', 'DVAR', 'OR', 'FACT', 'TRIMMEAN', 'EDATE', 'DMAX', 'RATE', 'NORMSDIST', 'PRODUCT', 'N', 'IMDIV', 'COUNTIFS', 'IMLOG2', 'BIN2HEX', 'AND', 'GAMMADIST', 'SUMIF', 'SMALL', 'ROMAN', 'DAVERAGE', 'COUPDAYBS', 'NORMSINV', 'CORREL', 'PEARSON', 'MIRR', 'BETAINV', 'NETWORKDAYS', 'SUBSTITUTE', 'RAND', 'DPRODUCT', 'CUBERANKEDMEMBER', 'ATANH', 'TREND', 'COUPNUM', 'RANK', 'ODDLYIELD', 'MINA', 'RANDBETWEEN', 'GCD', 'CUBEKPIMEMBER', 'TBILLEQ', 'COUNTBLANK', 'MODE', 'ISERR', 'NORMDIST', 'DISC', 'EXP', 'TTEST', 'HOUR', 'ISERROR', 'ERROR.TYPE', 'HYPGEOMDIST', 'TRANSPOSE', 'ROUNDDOWN', 'IMARGUMENT', 'PRICEDISC', 'ISO.CEILING', 'MAX', 'CHITEST', 'DATEVALUE', 'PROPER', 'IMSQRT', 'POWER', 'COVAR', 'FINV', 'ERF', 'COS', 'YIELDDISC', 'FREQUENCY', 'LINEST', 'LENB', 'HLOOKUP', 'ASINH', 'RSQ', 'ROW', 'FVSCHEDULE', 'ATAN', 'DEC2OCT', 'BESSELJ', 'AVERAGE', 'CONFIDENCE', 'FIND', 'VARA', 'MEDIAN', 'SUMX2MY2', 'COSH', 'FACTDOUBLE', 'COUPNCD', 'FDIST', 'NPV', 'TANH', 'CELL', 'ISNONTEXT', 'FTEST', 'QUOTIENT', 'TDIST', 'ODD', 'LOWER', 'ASIN', 'ACOSH', 'MMULT', 'BAHTTEXT', 'PMT', 'ISREF', 'DAYS360', 'ISTEXT', 'PI', 'DAY', 'LOOKUP', 'AMORLINC', 'TBILLPRICE', 'SQRT', 'IPMT', 'TEXT', 'VAR', 'JIS', 'YEAR', 'ROUND', 'ISPMT', 'LEFT', 'SLN', 'SUMSQ', 'ODDLPRICE', 'TRIM', 'DCOUNTA', 'DURATION', 'CUMPRINC', 'ECMA.CEILING', 'DEGREES', 'BESSELK', 'MIN', 'ERFC', 'MATCH', 'BESSELI', 'MULTINOMIAL', 'DOLLARFR', 'MDURATION', 'DVARP', 'ISEVEN', 'AVERAGEA', 'COUNTA', 'BIN2OCT', 'MIDB', 'ODDFYIELD', 'TIMEVALUE', 'BETADIST', 'TYPE', 'VDB', 'REPLACE', 'HEX2BIN', 'REPLACEB', 'COMPLEX', 'CRITBINOM', 'VARPA', 'HEX2DEC', 'INDEX', 'IMLOG10', 'HARMEAN', 'CHIINV', 'FALSE', 'COLUMNS', 'LOG', 'CODE', 'OCT2DEC', 'GROWTH', 'RTD', 'DELTA', 'CLEAN', 'INTRATE', 'BIN2DEC', 'RADIANS', 'COUPDAYS', 'TINV', 'SQRTPI', 'POISSON', 'SUMX2PY2', 'PERCENTILE', 'DEC2HEX', 'FISHER', 'TRUE ADDRESS', 'ISODD', 'PRICE', 'OCT2HEX', 'ROUNDUP', 'GEOMEAN', 'COMBIN', 'IMREAL', 'WORKDAY.INTL', 'MROUND', 'YIELDMAT', 'OCT2BIN', 'CUBEVALUE', 'EOMONTH', 'IMSUB', 'IMSIN', 'CHOOSE', 'QUARTILE', 'INFO', 'CHIDIST', 'NETWORKDAYS.INTL', 'CONVERT', 'COUPDAYSNC', 'PPMT', 'FINDB', 'YIELD', 'SUMXMY2', 'SECOND', 'ISLOGICAL', 'GAMMAINV', 'ACCRINTM', 'ISNA', 'NOW', 'NEGBINOMDIST', 'LN', 'BINOMDIST', 'DCOUNT', 'AREAS', 'MOD', 'IMCOS', 'ACCRINT', 'WORKDAY ', 'MAXA', 'DOLLAR', 'COUNTIF', 'STDEV STDEVA', 'DSTDEV', 'STANDARDIZE', 'SUBTOTAL', 'CUBEMEMBERPROPERTY', 'GETPIVOTDATA', 'SYD', 'STDEVP', 'LEN', 'NOT', 'T', 'SUMIFS', 'DDB', 'PERMUT', 'DOLLARDE', 'SEARCH', 'NOMINAL', 'DMIN', 'RIGHTB', 'FISHERINV', 'IFERROR', 'IF', 'INT', 'EFFECT', 'DATE', 'ISBLANK', 'TIME', 'LOGINV'})

Veamos el resultado de la fórmula que hemos metido (la suma de los valores de la columna B:

wb.save('new.xlsx')
Hoja con fórmula

Hipervínculos

Las celdas pueden tener un enlace a otras partes de un documento, un enlace a internet o, incluso, enlaces a otras celdas en hojas de otros libros. Vamos a ver un ejemplo sencillo para ver cómo funciona esto:

c_n2 = ws["N2"]
c_n3 = ws["N3"]

print(c_n2.hyperlink)
print(c_n3.hyperlink)

Los resultados serán None.

Vamos a añadir un hipervínculo a una celda de la misma hoja, la celda “A30”:

#hv = '=HYPERLINK("#Sheet!A30","Esto contiene un link a la celda A30")'
#c_n2.value = hv
c_n2.value = "Esto contiene un link a la celda A30"
c_n2.hyperlink = "#Sheet!A30"

En el anterior código hemos usado la forma en que se usa en Excel u otros programas de hojas de cálculo. La referencia sería de la siguiente forma:

  • “Libro#Hoja!Celda”:
    • “Libro” sería la referencia del libro. Por ejemplo, si tenemos un fichero xlsx que se llama “MiLibro.xlsx” ese sería el valor de “Libro”. Si estamos referenciando al propio libro donde estamos usando el hipervínculo entonces no es necesario añadir el libro en la ruta.
    • “Hoja” sería el nombre de la hoja del libro.
    • “Celda” sería la referencia a la celda a la que queremos enlazar.

Ahora vamos a meter un hipervínculo a una celda de otra hoja, para ello creamos una hoja de trabajo nueva:

wb.create_sheet("NewSheet")

hv = '=HYPERLINK("#NewSheet!A1", "Enlace a la celda A1 de la hoja NewSheet")'
c_n3.value = hv

print(c_n2.hyperlink)
print(c_n3.hyperlink)

Ahora el resultado será:

<openpyxl.worksheet.hyperlink.Hyperlink object>
Parameters:
ref='N2', location=None, tooltip=None, display=None, id=None
None

Como veis, seguimos sin tener nada en la property hyperlink de la segunda celda. No estamos usando la clase HyperLink que proporciona openpyxl y lo estamos haciendo de la forma que se indica aquí.

wb.save('new.xlsx')

Resumiendo

Hemos visto como tratar las celdas, valores en celdas, añadir hipervínculos, formatear la celda,… En el próximo capítulo vamos a profundizar un poco más en los estilos.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

− 1 = four

Pybonacci