Formatos XLSX
Formatos en WXTABLE
En WXTABLE el formato modifica la presentación del valor de la celda. Por defecto el formato aplicado es el general que por si mismo no modifica la presentación, como si lo hacen los formatos de número y fecha. En los tres casos el formato puede hacerse condicional, modificándose cuando el valor reúne ciertas condiciones.
XLSX no diferencia entre los términos formato y estilo. Habla, por ejemplo, de formato de color. Esto en HTML+CSS se le llama estilo, no existiendo el concepto de formato. Para estos temas el formato es tener un valor original numérico como -1234.567 y presentarlo en formato de moneda como -1.234,57€ aplicándole color rojo al ser un número negativo. Obviamente en el formato puede entrar en juego el estilo, pero también tiene la capacidad de modificar el valor a presentar redondeando el número y agregando el símbolo de moneda, preservándose en cualquier caso el valor original.
Para formatear números se usa el método toLocaleString() de un número en JavaScript. El objeto Date de JavaScript también tiene el método similar toLocaleDateString() para formatear fechas. Ambos métodos tienen dos argumentos, locales y options. En el primero se incluye una cadena para identificar la región geográfica como "en" o "es".
Para configurar un formato en WXTABLE hemos de agregar un atributo data-format que debe tener al menos la propiedad format y otras entre las siguientes según el caso. Si no se pasa una propiedad se tomaran estos valores por defecto. Para número y fecha cuando se pasa locales se formatea según esa región. Las opciones para el formato numérico son las mismas que las del método toLocaleString():
style:decimal; minimumFractionDigits:2; maximumFractionDigits:2; useGrouping:true; forceMinimumGroupingDigits:false; formatEmpty:false; unicode:latn; currency:EUR; currencyDisplay:symbol;
Las del formato de fecha son las propias del método toLocaleDateString().
hour12:false; weekday:none; year:numeric; month:numeric; day:numeric; hour:none; minute:none; second:none; timeZoneName:none;
El formato condicional se resuelve directamente en WXTABLE. Hay que pasar una condición que se comprobará contra los valores value y value2. Si se cumple se aplicarán estilos de color, itálica, negrita, subrayado. Y también puede sustituirse todo el texto o generarse antes y después del valor.
condition:none; negated:false; value: ""; value2: ""; color:black; backcolor:transparent; italic:false; bold:false; line:none; text: ""; textBefore: ""; padBefore:0; textAfter: ""; padAfter:0
Son muchas propiedades que permiten múltiples combinaciones, por lo que no sería posible presentar ejemplos para todas ellas. Y no siempre he podido exportar los mismos resultados a XLSX en todos los casos. En el enlace format-samples.txt se incluye una amplia muestra de todo estos formatos, con una traducción a XLSX en el archivo format-samples.xlsx. En este tema analizaremos sólo algunos ejemplos.
El archivo de datos format-samples.txt es un JSON que contiene los datos exportados del ejemplo desde WXTABLE. Se declaran los atributos de las celdas <td> de esta forma, donde por ejemplo la referencia 1,1 es la de la celda en la fila 1 y columna 1:
"attributes": {
"1,1": {
"data-format": "format:number",
"data-original": "123.4567"
},
"2,1": {
"data-format": "format:number;style:currency",
"data-original": "123.4567"
},
"3,1": {
"data-format": "format:number;style:percent",
"data-original": "0.15"
},
"4,1": {
"data-format": "format:number;style:exponential",
"data-original": "123.4567"
}
}Formatos de números en XLSX
Los formatos de número en WXTABLE se especifican en la propiedad style del objeto options que se pasará internamente como segundo argumento de num.toLocaleString( locales, options ). Ese style puede ser decimal, currency, percent y exponential. Se corresponden con el ejemplo de la Figura que reproduce los formatos decimal, moneda, porcentaje y exponencial.
Puede ver este ejemplo en formato WXTABLE en format-number.txt y en XLSX en format-number.xlsx.
No hay que olvidar que aplicar un formato a una tabla hace que se cambie el aspecto visual de su valor original. Pero ese valor original se sigue preservando en un atributo data-original.
En la Figura se observa que cuando entramos en una celda con formato se repone ese valor original. Al salir de la celda volverá a aplicarse el formato con el nuevo valor si hubiese sido modificado. De forma similar se comportan GoogleSheet o EXCEL.
Los valores en las celdas anteriores son 123.4567 para la primera, segunda y cuarta celda. Y 0.15 para la tercera. En WXTABLE los números siempre se introducen con un punto decimal, a diferencia de GoogleSheet o EXCEL, que si usa idioma español serán las comas las que separen la parte decimal de un número. Pero aún así internamente esas aplicaciones manejan los números con punto decimal.
En la Figura se presenta el XLSX resultado en GoogleSheet. Los formatos se guardan en el archivo xl/styles.xml que almacena todos los formatos y estilos del libro.
Veamos ahora el resultado en el archivo XML xl/styles.xml. XLSX considera formatos y estilos una sóla cosa, por lo que los incluye en ese archivo en el elemento <styleSheet>. En WXTABLE los estilos son los que pueden configurarse con CSS y los formatos son otra cosa.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmts count="4">
<numFmt formatCode="#,##0.00;-#,##0.00" numFmtId="164"/>
<numFmt formatCode="#,##0.00\ "€";-#,##0.00\ "€"" numFmtId="165"/>
<numFmt formatCode="#,##0.00%;-#,##0.00%" numFmtId="166"/>
<numFmt formatCode="0.00E+0;-0.00E+0" numFmtId="167"/>
</numFmts>
...
<cellXfs count="5">
...
<xf numFmtId="164" applyFormat="1" ...><alignment .../></xf>
<xf numFmtId="165" applyFormat="1" ...><alignment .../></xf>
<xf numFmtId="166" applyFormat="1" ...><alignment .../></xf>
<xf numFmtId="167" applyFormat="1" ...><alignment .../></xf>
</cellXfs>
...
</styleSheet>Los formatos en XLSX se declaran en el elemento <numFmts>. Cada elemento <numFmt> contiene el atributo formatCode con el formato. Por ejemplo, la primera celda tiene un valor original 123.4567 y un valor formateado 123,46. En WXTABLE la celda <td> se configura con los atributos data-format="format:number" y data-original="123.4567". Vease que el atributo data-format no incluye los valores por defecto para el formato de número , que son los que vimos en el primer apartado:
style:decimal; minimumFractionDigits:2; maximumFractionDigits:2; useGrouping:true; forceMinimumGroupingDigits:false;
Así que en resumen en WXTABLE se aplica por defecto el formato decimal con 2 dígitos decimales, agrupando miles. La traducción a XLSX es #,##0.00;-#,##0.00, separando por punto y coma el formato de números positivos y negativos. No voy a entrar en como escribir formatos en XLSX, pues es algo complejo y se sale del objetivo de estos temas.
Siguiendo con el primer elemento <numFmt> observamos que tiene numFmtId="164", identificador que vemos que se relaciona en el elemento <xf numFmtId="164"> que declara el estilo (y formato) para las celdas que lo usen. Veamos por que se indexan desde el valor 164 cuando en otros casos se indexaban desde cero.
El apartado 18.8.30 numFmt (Number Format), página 1786 de OOX expone los formatos de número. Por lo visto se reservan los primeros identificadores para formatos específicos. En las pruebas que hice no pude usar identificadores iniciados en cero, pues por lo visto tanto GoogleSheet como EXCEL web los ignoraban, aplicando los formatos específicos. Estos formatos que estamos pasando en el XLSX son formatos personalizados. Y así debe verlos GoogleSheet y EXCEL. La cuestión era saber a partir de que identificador se consideran formatos personalizados, porque OOX no aclaraba mucho sobre esto.
EXCEL web no permite declarar formatos personalizados, aunque si es capaz de leerlos al abrir un XLSX. GoogleSheet si los permite. La posible solución era crear un formato personalizado y exportar el libro a XLSX para ver que identificador usaba.
En la Figura se observa como se crea un formato personalizado en una nuevo libro de GoogleSheet. Descargué esta hoja en XLSX, que también puede ver en este enlace format-custom.xlsx. Ya hemos dicho que un XLSX es en el fondo un ZIP. Si le cambiamos la extensión podemos abrirlo en Windows. Con un visor XML podemos consultar el archivo xl/styles.xml y observar esto:
<numFmts count="1">
<numFmt formatCode=""INICIO "#.##" FIN""
numFmtId="164"/>
</numFmts>Entonces observé que GoogleSheet empezaba a identificar los formatos personalizados con el número 164. Eso, supuestamente, quiere decir que los 163 anteriores son formatos específicos. Y de ahí es de dónde sale ese número de identificador 164.
Formato condicional en XLSX
El formato condicional en WXTABLE puede aplicarse a los tres formatos posibles: general, número y fecha. En estos dos últimos se aplicaría primero el formato de número o fecha y luego el condicional. Para el general se aplica directamente el condicional. En la Figura puede ver un ejemplo con dos celdas que tienen el mismo formato condicional. Se trata de poner color rojo y fondo amarillo cuando el texto empieza con la letra "a".
Las condiciones posibles son variadas. En caso de números se condiciona si es igual, distinto, mayor, menor, mayor o igual y menor igual. En caso de texto se puede condicionar con el campo Valor1 a que el texto empiece o finalice por ese valor. También cabe aplicar las condiciones de número, pues se aplicarán usando un ordenamiento alfábetico. Cuando se indica Valor2 cabe condicionar a que esté entre esos dos valores o que empiece por uno y finalice por el otro. También caben otras condiciones como si contiene el valor, si está vacío y si es un número. La condición siempre hará que se aplique el formato en cualquier caso.
Hay un campo que permite negar la condición que se aplique. También es posible incluir una expresión en Valor1 de tal forma que cuando se evalúe a verdadero se aplicaría el formato. En esa expresión cabe incluir referencias a celdas usando una fórmula, por ejemplo, =[R3C3]="abc" sería una condición que se aplicaría si esa celda contiene el valor "abc".
No voy a extenderme con todas estas posibilidades. Ni a explicar como se configuran los formatos en XLSX pues se sale del objetivo de estos temas que es explicar como exportar a XLSX. Recordamos que en el enlace format-samples.txt se incluye una amplia muestra de todo estos formatos, con una traducción a XLSX en el archivo format-samples.xlsx.
En lo que sigue sólo explicaremos el ejemplo de la Figura, cuyos atributos de las dos celdas en WXTABLE son iguales:
"attributes":{
"1,1":{
"data-format":"condition:starts;value:a;color:red",
"data-original":"abc"
},
"2,1":{
"data-format":"condition:starts;value:a;color:red",
"data-original":"def"
}
}
En la Figura puede ver el resultado en GoogleSheet, con la utilidad para editar el formato condicional de la primera celda. Las reglas de formato condicional en XLSX son parecidas a las de WXTABLE. En este ejemplo se usa la condición de expresión, que en GoogleSheet denomina "La fórmula personalizada es".
Se observa la expresión =LEFT(A1;LEN("a"))=("a"), fórmula que no es otra cosa que comprobar si el contenido de A1 empieza por la letra "a".
En caso de evaluarse a verdadero se aplicará el estilo color rojo y fondo amarillo. Como se observa, es posible también aplicar negrita, itálica, subrayado y tachado. Esto también es posible en WXTABLE.
Hemos de aclarar que he encontrado un problema con el color del texto en este ejemplo en EXCEL web, pues aunque el fondo amarillo si es aplicado cuando la condición se cumple, en cambio el color rojo se aplica en cualquier caso. Esto sucede incluso con un formato condicional creado en una hoja nueva. Además EXCEL web tiene algunas limitaciones para configurar estilo de formato condicional, limitándose sólo a algunas opciones de colores y fondos.
Los formatos se guardan en la hoja de estilos xl/styles.xml para todo el libro. Se ubican dentro del elemento <dxfs>. En este código observará un único formato, pues aplicamos el mismo formato a las dos celdas que contienen los valores "abc" y "def" para observar que se aplica formato a la primera que empieza con la letra "a". En el elemento <dxf>, que tiene índice 0, se declara el estilo que se aplicaría a ese formato condicional:
<dxfs count="1">
<dxf>
<font>
<color rgb="FFFF0000"/>
</font>
<fill>
<patternFill patternType="solid">
<fgColor rgb="FFFFFF00"/>
<bgColor rgb="FFFFFF00"/>
</patternFill>
</fill>
<border/>
</dxf>
</dxfs>El verdadero formato se declara en el archivo xl/worksheets/sheet1.xml, al final del mismo, en tantos elementos <conditionalFormatting> como formatos condicionales hayan, aunque contengan el mismo formato, pues se diferencian porque se aplican a celdas diferentes (atributo sqref):
<conditionalFormatting sqref="A1">
<cfRule priority="1" dxfId="0" type="expression">
<formula>LEFT(A1,LEN("a"))=("a")</formula>
</cfRule>
</conditionalFormatting>
<conditionalFormatting sqref="A2">
<cfRule priority="1" dxfId="0" type="expression">
<formula>LEFT(A2,LEN("a"))=("a")</formula>
</cfRule>
</conditionalFormatting>Vease que ambos apuntan al estilo condicional dxfId="0". Y que ambos tiene la misma regla <cfRule> de tipo expresión, en cuyo caso se incluye el elemento <formula> con la fórmula.
El tipo de regla (atributo type) declara la condición. En el exportador a XLSX usamos este objeto para declarar las condiciones:
const conditions = {
none: null,
always: {str: false, oper: ">=",
direct: {type: "expression", formula: "TRUE"},
negated: {type: "expression", formula: "FALSE"}
},
eq: {str: false, oper: "=",
direct: {type: "cellIs", operator: "equal"},
negated: {type: "cellIs", operator: "notEqual"}
},
ne: {str: false, oper: "<>",
direct: {type: "cellIs", operator: "notEqual"},
negated: {type: "cellIs", operator: "equal"}
},
gt: {str: false, oper: ">",
direct: {type: "cellIs", operator: "greaterThan"},
negated: {type: "cellIs", negatedOperator: "lessThanOrEqual"}
},
lt: {str: false, oper: "<",
direct: {type: "cellIs", operator: "lessThan"},
negated: {type: "cellIs", negatedOperator: "greaterThanOrEqual"}
},
ge: {str: false, oper: ">=",
direct: {type: "cellIs", operator: "greaterThanOrEqual"},
negated: {type: "cellIs", negatedOperator: "lessThan"}
},
le: {str: false, oper: "<=",
direct: {type: "cellIs", operator: "lessThanOrEqual"},
negated: {type: "cellIs", negatedOperator: "greaterThan"}
},
between: {str: false,
direct: {type: "cellIs", operator: "between"},
negated: {type: "cellIs", negatedOperator: "notBetween"}
},
contains: {str: true,
direct: {type: "expression", formula: "NOT(ISERROR(SEARCH([val1],[ref])))"},
negated: {type: "expression", formula: "ISERROR(SEARCH([val1],[ref]))"}
},
starts: {str: true,
direct: {type: "expression", formula: "LEFT([ref],LEN([val1]))=([val1])"},
negated: {type: "expression", formula: `LEFT([ref],LEN([val1]))<>([val1])`}
},
ends: {str: true,
direct: {type: "expression", formula: `RIGHT([ref],LEN([val1]))=([val1])`},
negated: {type: "expression", formula: `RIGHT([ref],LEN([val1]))<>([val1])`}
},
startsEnds: {str: true,
direct: {type: "expression", formula: `AND(LEFT([ref],LEN([val1]))=([val1]),RIGHT([ref],LEN([val2]))=([val2]))`},
negated: {type: "expression", formula: `AND(LEFT([ref],LEN([val1]))<>([val1]),RIGHT([ref],LEN([val2]))<>([val2]))`}
},
empty: {str: true,
direct: {type: "expression", formula: `LEN(TRIM([ref]))=0`},
negated: {type: "expression", formula: `NOT(LEN(TRIM([ref]))=0)`}
},
number: {str: false,
direct: {type: "expression", formula: `ISNUMBER([ref])`},
negated: {type: "expression", formula: `NOT(ISNUMBER([ref]))`}
},
expression: {
direct: {type: "expression", formula: `[val1]`},
negated: {type: "expression", formula: `NOT([val1])`}
}
};Vemos que algunas condiciones tienen el tipo cellIs y un atributo operator que le confiere una condición específica como equal, greaterThan, lessThan y otros comparativos. El resto son del tipo expression con un atributo formula. Vease el ejemplo con la condición starts con el esquema de fórmula LEFT([ref], LEN([val1])) = ([val1]), donde las expresiones entre corchetes serán sustituidas: [ref] por la referencia de la celda donde se aplica el formato y val1 por el valor del campo Valor1 que define el formato. En el ejemplo se obtiene LEFT(A2,LEN("a"))=("a").
Observe que la condición always aplica siempre el formato, pues en la regla se incluirá una fórmula cuyo valor es TRUE. En el siguiente apartado veremos un ejemplo.
Formato condicional de texto generado
El formato condicional permite aplicar estilos y también generar texto, como se observa en los campos del configurador de formatos condicionales en WXTABLE de la Figura. El campo Texto permite presentar ese valor en la celda cuando se cumpla la condición. Con Texto antes y con el número Rellenar antes se procede a incluir el texto que se repite ese número de veces. Lo mismo sucede con Texto después y Rellenar después.
En el ejemplo de la Figura usamos un formato con la condición de que se aplique siempre. Combina la presentación del número entero 456 y de la fecha 20/08/2021 para presentar el número como 0456/21. Por un lado se condiciona el formato con texto antes "0" repetido cuatro veces, con lo que 456 se rellena con "0" por la izquierda hasta que tenga una longitud de cuatro caracteres, quedando 0456. Y por otro se genera texto después usando la fórmula ="/" # right(year([R2C2]), 2), que tomará los dos últimos dígitos de la celda que contiene la fecha y se concatenerá con una barra, quedando 0456/21. En WXTABLE el signo "#" es el operador de concatenación de cadenas.
Estos formatos también son posibles en XLSX, hasta cierto punto, pues no se permiten fórmulas en los campos de texto. Así que se aplicará el texto antes pero no el texto después. En la Figura puede ver el resultado en EXCEL web. Ya hemos dicho que EXCEL web no permite editar formatos personalizados, pero si los leerá cuando abra un archivo XLSX.
El formato que hemos exportado consigue presentar el número 456 completando cuatro ceros por la izquierda, por lo que quedará como 0456. Aunque la fórmula que agrega el texto por la derecha no será exportada, dado que XLSX no soporta fórmulas para esto. El formato vemos que es [>=-1E+307]0000;0000 donde la expresión entre corchetes es una condición. Vimos en el apartado anterior que la condición se incluía en un elemento <cfRule>, pero también es posible incluirla en el elemento <numFmt> con la condición entre corchetes.
En XLSX los corchetes [>=-1E+307] indican que se si cumple la expresión de su interior >=-1E+307 de tal forma que el valor de la celda sea mayor que el menor número posible -10307, lo cual sucederá siempre, se aplicará el formato 0000. Aunque no es necesario pues siempre se cumplirá, la segunda parte después del punto y coma sería el formato para cuando no se cumpla.
Esto se incluirá en un elemento <numFmt> del archivo xl/styles.xml. Observará que en un atributo XML no deben aparacer los caracteres reservados "<", ">", "&", por lo que el caracter ">" aparece escapado ">". En un elemento <dxf> se inserta el estilo condicional, que en este caso no tiene ni debería tener ningún estilo particular, puesto que la condición ya se está incluyendo en <numFmt>.
<numFmts count="1">
<numFmt formatCode="[>=-1E307]0000;0000" numFmtId="164"/>
</numFmts>
...
<dxfs count="1">
<dxf>
<font>
<color rgb="FF000000"/>
</font>
<fill/>
<border/>
</dxf>
</dxfs>En <dxf> se inserta el estilo condicional cuaya condición siempre se inserta en el archivo xl/worksheets/sheet1.xml, en un elemento <cfRule>. En este caso la fórmula es TRUE y es por lo que no tiene sentido que se aplique el mismo estilo siempre.
<conditionalFormatting sqref="B1">
<cfRule priority="1" dxfId="0" type="expression">
<formula>TRUE</formula>
</cfRule>
</conditionalFormatting>En el exportador XLSX con una condición siempre (always) y cuando exista texto generado antes o después con un "0" o con un "#", se usa el condicional en el elemento <numFmt>. Y en el elemento <cfRule> se incluirá una condición TRUE. Sólo en este caso se incluirá ambos condicionales.
Por otro lado el texto generado después con la fórmula ="/"#right(year([R2C2]), 2) será completamente ignorado pues no hay manera de incluir fórmulas en los formatos XLSX. Estos ajustes en el exportador son necesarios para intentar exportar los formatos con éxito, o al menos con el mejor resultado posible.