GENERAR HOJAS EN LIBRO DE EXCEL ONLINE SEGÚN CONDICIÓN CON TYPESCRIPT

Hola a todos,

Hace unos días un lector en LinkedIn me pedía ayuda para crear un proceso en Typescript que hiciese lo siguiente: en una hoja de Excel tenemos una columna, (voy a poner un ejemplo porque no quiero comentar el dato real) el listado de los municipio de España.

Los datos están desordenados y lo que necesita esta persona es crear tantas hojas en ese mismo archivo agrupando en cada hoja según la letra con la que empieza cada provincia. Por ejemplo, en la hoja con la pestaña A irían todos los municipios que empiezan por A, y así con todas las letras.

Este es el listado inicial:

Llega hasta los 8M registros aprox, os dejo el listado:

Pues bien, he creado un script que hace lo que necesita:

function main(libro: ExcelScript.Workbook) {
  // Eliminamos todas las hojas excepto la principal
  borrarHojas(libro);
  // obtenemos el rango utilizado
  var hoja = libro.getActiveWorksheet();
  var  rango = hoja.getUsedRange();
  if (!rango) {
    console.log("El rango está vacío");
    return;
  }
  // Leer las ciudades del rango
  var  ciudades = rango.getValues();
  // Inicializar un objeto donde las ciudades se ordenarán por su letra inicial
  var  ordenado: { [clave: string]: string[] } = {};
  // Iterar sobre las ciudades empezando en la segunda fila, ya que tenemos encabezados
  for (let i = 1; i < ciudades.length; i++) {
    var  ciudad = ciudades[i][0];
    // Si la celda no contiene una cadena, se omite
    if (typeof ciudad !== 'string') {
      console.log(`Valor no string: ${ciudad}. Omitiendo.`);
      continue;
    }
    // Obtenemos la primera letra de la ciudad, sin acentos y en mayúsculas
    var  letra = limpiarLetra(ciudad);
    // Si la letra no existe como clave en el objeto ordenado, la inicializamos con un array vacío
    if (!ordenado[letra]) {
      ordenado[letra] = [];
    }
    // Agregar la ciudad al array correspondiente a su letra inicial
    ordenado[letra].push(ciudad);
  }
  // Crear una nueva hoja para cada letra, agregar las ciudades y colorear la pestaña
  for (var  letra in ordenado) {
    var  nuevaHoja = libro.addWorksheet(letra);
    nuevaHoja.getRange(`A1:A${ordenado[letra].length}`).setValues(ordenado[letra].map(c => [c]));
    nuevaHoja.setTabColor(generarColorAleatorio());
  }
}
// Normalizamos datos en la nueva función, todas las primeras letras son mayusc y sin tildes
function limpiarLetra(palabra: string): string {
  // Quitar acentos y convertir a mayúsculas
  const limpio = palabra.normalize("NFD").replace(/[\u0300-\u036f]/g, "");
  return limpio.charAt(0).toUpperCase();
}
// Función para eliminar todas las hojas excepto la primera
function borrarHojas(libro: ExcelScript.Workbook): void {
  // Obtener una lista de todas las hojas
  var  hojas = libro.getWorksheets();
  // No tenemos en cuenta la primera hoja
  var  aBorrar = hojas.slice(1);
  // Iterar sobre las hojas y eliminamos
  for (const h of aBorrar) {
    h.delete();
  }
}
// Generamos color aleatorio 
function generarColorAleatorio(): string {
  var  letras = '0123456789ABCDEF';
  var  color = '#';
  for (var  i = 0; i < 6; i++) {
    color += letras[Math.floor(Math.random() * 16)];
  }
  return color;
}

Para todo esto funcione correctamente, he creado dentro del script tres funciones específicas, una función para normalizar los datos, es decir, quiero que la primera letra siempre sea mayúscula y sin tildes:

// Normalizamos datos en la nueva función, todas las primeras letras son mayusc y sin tildes
function limpiarLetra(palabra: string): string {
  // Quitar acentos y convertir a mayúsculas
  const limpio = palabra.normalize("NFD").replace(/[\u0300-\u036f]/g, "");
  return limpio.charAt(0).toUpperCase();
}

Otra función para eliminar todas las hojas excepto la primera cada vez que se ejecute el script:

// Función para eliminar todas las hojas excepto la primera
function borrarHojas(libro: ExcelScript.Workbook): void {
  // Obtener una lista de todas las hojas
  const hojas = libro.getWorksheets();
  // No tenemos en cuenta la primera hoja
  const aBorrar = hojas.slice(1);
  // Iterar sobre las hojas y eliminamos
  for (const h of aBorrar) {
    h.delete();
  }
}

Y por último una función que coloree las hojas de forma aleatoria:

// Generamos color aleatorio 
function generarColorAleatorio(): string {
  const letras = '0123456789ABCDEF';
  let color = '#';
  for (let i = 0; i < 6; i++) {
    color += letras[Math.floor(Math.random() * 16)];
  }
  return color;
}

Sobre el resto del código os lo he comentado más arriba y creo que se comprende bastante bien.

Y este es el resultado:

El ejercicio le ha servido perfectamente, aunque es parte de un todo mucho más grande y complejo que me irá comentado.

Y esto es todo, espero que sea de utilidad para todos vosotros, a mi me he resultado muy interesante el haberlo creado.

¿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