TRABAJAR CON FUNCIONES DE EXCEL EN TYPESCRIPT

MPORTANTE: Este post ha sido actualizado al nuevo modelo del API, los cambios son significativos y necesarios para que el código funcione, por favor, después de leer la publicación completa, visita el nuevo post: TRABAJAR CON FUNCIONES DE EXCEL EN TYPESCRIPT: CONVIRTIENDO SCRIPT ASÍNCRONO HEREDADO AL MODELO ACTUAL

Hola a todos!

Qué tal estáis?. Espero que bien : )

En el post de hoy vamos ver cómo podemos programar las funciones de Excel en TypeScript.

Aunque existe un método para llamar a las funciones integradas de Excel: y que muy resumidamente sería este su código:

var MiFuncion = context.workbook.functions."funcion"();
MiFuncion.load('value');
return context.sync()
    .then(function () {
        console.log(MiFuncion.value);
    });

Sobre esta forma de programar o llamar a las funciones de Excel trabajaremos en futuras publicaciones.

En el post de hoy vamos a utilizar otra forma de hacerlo y que me parece muy interesante. Vamos a trabajar con la propiedad «.formulas», que nos va a permitir escribir las funciones o fórmulas de Excel en una cadena y pasarlas a la hoja Excel. La función se ejecutará automáticamente en la hoja como fórmula (se puede pasar a valores, pero ralentizaría el script).

Veamos un ejemplo en el que podamos introducir varias fórmulas y operaciones:

Partiendo de la columna NOMBRE en la que tenemos la descripción de las oficinas vamos a calcular varias columnas:

• Nº OFICINA: Extraemos en número de cada centro de trabajo.
• PERSONAL: Realizamos un Buscarv a la hoja PERSONAL para indicar cuantas personas trabajan en cada centro.
• TIPO DE OFICINA:
  -Si la oficina es <=5 es: OFICINA PEQUEÑA,
  -Si es >5 y <=10 es OFICINA MEDIANA,
  -Si es >10 y <=15 es OFICINA MEDIANA PLUS,
  -Si es > 15 es OFICINA GRANDE.
• OBJETIVOS: Simplemente multiplicamos el número de personas por 3.

Como podéis observar tenemos un ejemplo muy variado de fórmulas y métodos para trabajar.

El código/script que os propongo es el siguiente:

async function main(context: Excel.RequestContext) {
	//asignamos variables para hojas INICIO y PERSONAL
	let MiHoja = context.workbook.worksheets.getItem("INICIO");
	let Personal = context.workbook.worksheets.getItem("PERSONAL");
	//cargamos valores de filas y columna
	let MiRangoRow = MiHoja.getUsedRange().load("Values, rowCount");
	let MiRangoCol = MiHoja.getUsedRange().load("Values, ColumnCount");
	let MiRangoRow_Personal = Personal.getUsedRange().load("Values, rowCount");
	await context.sync();
	//Pasamos valores a variables
	let FinRow = MiRangoRow.rowCount;
	let FinCol = MiRangoCol.columnCount;
	let FinRow_Personal = MiRangoRow_Personal.rowCount;
	//Antes de proceder limpiamos contenido
	MiHoja.getRangeByIndexes(1, 1, FinRow, FinCol).clear(Excel.ClearApplyTo.all);
	// Escribimos títulos
	MiHoja.getCell(0, 1).values = [
		["Nº OFICINA"]
	];
	MiHoja.getCell(0, 2).values = [
		["PERSONAL"]
	];
	MiHoja.getCell(0, 3).values = [
		["TIPO OFICINA"]
	];
	MiHoja.getCell(0, 4).values = [
		["OBJETIVOS"]
	];
	for (let i = 1; i < FinRow; i++) {
		// Ejecutamos combinación de fórmulas para extraer última posición
		MiHoja.getCell(i, 1).formulas = [
			["=RIGHT(A" + (i + 1) + "," + "LEN(A" + (i + 1) + ")" + "-" + "SEARCH(\" \"" + "," + "A" + (i + 1) + "," + 1 + "))"]
		];
		// Ejecutamos Buscarv para traer número de personas en oficina
		MiHoja.getCell(i, 2).formulas = [
			["=VLOOKUP(A" + (i + 1) + "," + "'PERSONAL'!" + "A2:B" + FinRow_Personal + ",2,0"]
		];
	}
	//Cargamos valores de columna PERSONAL 
	let CPersonal = MiHoja.getRangeByIndexes(1, 2, FinRow - 1, 1).load("values")
	await context.sync();
	//Creamos variables para condicional  
	let x: any = 5
	let j: any = 10
	let h: any = 15
	let Obj: any = 3
	//Iniciamos bucle for
	for (let i = 1; i < FinRow; i++) {
		//Pasamos valores Personal a matriz
		let Of_Personal = CPersonal.values[i - 1];
		//y utilizamos condicional para determinar tipo de oficina
		if (Of_Personal <= x) {
			MiHoja.getCell(i, 3).values = [
				["OFICINA PEQUEÑA"]
			]
		} else if (Of_Personal > x && Of_Personal <= j) {
			MiHoja.getCell(i, 3).values = [
				["OFICINA MEDIANA"]
			]
		} else if (Of_Personal > j && Of_Personal <= h) {
			MiHoja.getCell(i, 3).values = [
				["OFICINA MEDIANA PLUS"]
			]
		} else {
			MiHoja.getCell(i, 3).values = [
				["OFICINA GRANDE"]
			]
		}
		// Multiplicamos PERSONAL por 3      
		let Objetivos: any
		Objetivos = Of_Personal[0] * Obj;
		MiHoja.getCell(i, 4).values = [
			[Objetivos]
		]
	}
}

Muchas cosas ya las hemos visto en post anteriores, pero me gustaría incidir sobre el método a través del cual generamos las funciones y las pasamos a la hoja Excel. En este caso funciones anidadas de Derecha(), Largo() y Hallar(). Importante, las funciones deben estar en inglés y en lugar de «;» es una «,».

MiHoja.getCell(i, 1).formulas = [
			["=RIGHT(A" + (i + 1) + "," + "LEN(A" + (i + 1) + ")" + "-" + "SEARCH(\" \"" + "," + "A" + (i + 1) + "," + 1 + "))"]
		];

Es interesante ver cómo se transcribe Hallar(" "; que pasa a ser así: "SEARCH(\" \"" + "," +

La otra función que vamos a utilizar en Buscarv:

MiHoja.getCell(i, 2).formulas = [
			["=VLOOKUP(A" + (i + 1) + "," + "'PERSONAL'!" + "A2:B" + FinRow_Personal + ",2,0"]
		];

Aquí utilizamos la variable FinRow_Personal que nos indica el rango de la hoja PERSONAL para realizar más eficientemente el cálculo.

Siguiendo con el script creo que es importante también indicar que para realizar el condicional y que se pueda comparar con Of_Personal que tiene almacenados los valores de PERSONAL, es necesario declarar los números en variables:

//Creamos variables para condicional  
	let x: any = 5
	let j: any = 10
	let h: any = 15

Y por último, para poder multiplicar por 3 los valores de la matriz Of_Personal[0], debemos almacenar el cálculo en otra variable let Objetivos: any y luego igualar el valor al ítem correspondiente de la columna 4:

// Multiplicamos PERSONAL por 3      
		let Objetivos: any
		Objetivos = Of_Personal[0] * Obj;
		MiHoja.getCell(i, 4).values = [
			[Objetivos]
		]

Este es el resultado después de aplicar el script:

Y esto es todo, teniendo en cuenta este post y los anteriores, podremos programar rutinas altamente eficientes en Excel para realizar cálculos en nuestros proyectos. Sin duda muy interesante todo.

Espero que sea de utilidad 🙂

Descarga el archivo pulsando en:

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.



¡¡Muchas gracias!!

Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies