Utilisez les macros ONLYOFFICE pour copier des colonnes dans des feuilles de calcul séparées

Les macros ONLYOFFICE ouvrent des possibilités infinies, transformant des tâches complexes qui prendraient des heures en processus réalisés d’un simple clic. Récemment, nous avons reçu une demande d’un utilisateur d’ONLYOFFICE pour une macro qui pourrait automatiser leur flux de travail en copiant des données de manière efficace.

Cet article se penche sur les spécificités de cette demande, en détaillant comment nous avons développé une macro pour répondre à leurs besoins et en explorant les implications plus larges des macros ONLYOFFICE.

Construction de la macro

  const oWorksheet = Api.GetActiveSheet();
  const oRange = oWorksheet.GetSelection();

Pour commencer, nous obtenons la feuille de calcul active et la sélection dans les variables oWorksheet et oRange.

Nous allons maintenant passer en revue les trois fonctions qui composent cette macro, à savoir : getColHeader(), copyColumns() et copyValues().

Tout d’abord, la fonction getColHeader().

  const getColHeader = (colNumber) => {
    const targetColumn = oRange.GetCols(colNumber);
    const colHeader = targetColumn.GetValue()[0][0];
    return colHeader !== null && colHeader !== "" ? colHeader : -1;
  };

La fonction getColHeader() prend un seul paramètre, colNumber, qui spécifie le numéro de la colonne. Cette fonction recherche une valeur valide dans la première ligne de la colonne spécifiée. Si une valeur valide existe, elle renvoie cette valeur ; sinon, elle renvoie -1.

Nous allons maintenant voir comment fonctionne la fonction copyColumns().

  const copyColumns = () => {
    let end = false;
    let i = 2;

    while (!end) {
      i++;
      const sheetName = getColHeader(i);
      if (sheetName === -1) {
        end = true;
        break;
      } else if (sheetName) {
        const columns = oRange.GetCols(i);
        columns.ForEach((cell) => {
          copyValues(cell, sheetName, 1);
        });

        for (let j = 1; j <= 2; j++) {
          const col = oRange.GetCols(j);
          col.ForEach((cell) => {
            copyValues(cell, sheetName);
          });
        }
      } else {
        console.error("Fatal error in copy horizontal logic.");
        end = true;
        break;
      }
    }
  };

La fonction copyColumns() parcourt les colonnes de la feuille de calcul actuelle, en commençant par la troisième colonne. Pour chaque colonne, elle récupère l’en-tête de la colonne à l’aide de la fonction getColHeader(). Si l’en-tête est valide, elle copie les valeurs et le formatage de cette colonne sur la feuille cible spécifiée par l’en-tête, à l’aide de la fonction copyValues().

En outre, il veille à ce que les deux premières colonnes, qui sont considérées comme des colonnes communes, soient également copiées sur chaque feuille cible. Le processus se poursuit jusqu’à ce qu’un en-tête non valide soit rencontré, auquel cas la boucle se termine. Il s’agit de la fonction principale de la macro, qui appelle d’autres fonctions d’aide.

Remarque : dans ce code, nous supposons qu’il y a deux colonnes communes et que les colonnes spécifiques à copier séparément dans chaque feuille commencent à partir de la troisième colonne. Ces valeurs peuvent être ajustées en fonction de votre cas d’utilisation spécifique.

Maintenant, la fonction copyValues().

const copyValues = (range, sheetName, copyType = 0) => {

La fonction copyValues() copie les valeurs et la mise en forme de la plage source vers la feuille cible. Elle prend trois paramètres : range, qui est l’objet range contenant les données des cellules, sheetName, qui est le nom de la feuille cible et copyType, qui spécifie si la colonne à copier est une colonne par défaut ou une colonne spécifique.

    const oValue = range.GetValue();
    const oCharacters = range.GetCharacters(0, 2);
    const oFont = oCharacters.GetFont();
    const fontName = oFont.GetName();
    const oSize = oFont.GetSize();
    const isBold = oFont.GetBold();
    const isItalic = oFont.GetItalic();
    const rowNo = range.GetRow() - 1;
    let colNo;

Tout d’abord, la fonction récupère la valeur et le formatage (nom de la police, taille, gras, italique) de la cellule source.

const targetSheet = Api.GetSheet(sheetName);

    if (!targetSheet) {
      console.error(`Sheet with name ${sheetName} not found.`);
      return;
    }
    if (copyType === 1) {
      colNo = 2;
    } else {
      colNo = range.GetCol() - 1;
    }

Ensuite, la feuille cible est récupérée à l’aide de la variable sheetName, obtenue à partir de la fonction getColHeader().

Si la feuille cible n’existe pas, une erreur est enregistrée et l’exécution de la macro est interrompue.

    if (oValue === null || oValue === "") {
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
    } else {
      oFont.SetName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);

      if (isBold) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
      }
      if (isItalic) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
      }
    }
  };

Enfin, si tout se passe bien et qu’aucune erreur ne se produit, la fonction copyValues() fixe la valeur et la mise en forme dans la cellule correspondante de la feuille cible.

copyColumns();

Enfin, nous terminons la macro en invoquant la fonction copyColumns(), qui est le point d’entrée de la macro.

Code complet de la macro

Voici le code de la macro complète :

// Macro Workflow and Info
// This macro automates the process of copying specific columns to designated sheets
// and 'common columns' to all sheets. It ensures data and formatting consistency
// across multiple sheets. You can specify the common columns, which must start from the left and be continuous.
// For specific columns, as long as you have a column apart from the common ones with a valid header and a sheet with the same name exists, the macro will take that column into consideration/.
//If you have a secific column for which a sheet doesnt exist, the console will give you an error.
// For any other problems, be sure to check the console logs.
// Example Scenario:
// Suppose we have columns 'Name', 'Type', 'Calcium', and 'Magnesium' in a sheet called 'FOOD'.
// We also have sheets named 'Calcium' and 'Magnesium'.
// Selecting all data in the 'FOOD' sheet and running the macro will:
// 1. Copy columns 'Name' and 'Type' to both 'Calcium' and 'Magnesium' sheets.
// 2. Copy the 'Calcium' column to the 'Calcium' sheet.
// 3. Copy the 'Magnesium' column to the 'Magnesium' sheet.

(function () {
  const oWorksheet = Api.GetActiveSheet();
  const oRange = oWorksheet.GetSelection();

  /**
   * Gets the value in the first row of the Column
   *
   * @param {number} colNumber
   * @returns {string}

   */
  const getColHeader = (colNumber) => {
    const targetColumn = oRange.GetCols(colNumber);
    const colHeader = targetColumn.GetValue()[0][0];

    return colHeader !== null && colHeader !== "" ? colHeader : -1;
  };

  /**
   * Iterates through all valid columns and copies them to target sheet.
   */
  const copyColumns = () => {
    let end = false;
    let i = 2;

    while (!end) {
      i++;
      const sheetName = getColHeader(i);
      if (sheetName === -1) {
        end = true;
        break;
      } else if (sheetName) {
        const columns = oRange.GetCols(i);
        columns.ForEach((cell) => {
          copyValues(cell, sheetName, 1);
        });

        // Copy the common rows in every sheet.
        for (let j = 1; j <= 2; j++) { const col = oRange.GetCols(j); col.ForEach((cell) => {
            copyValues(cell, sheetName);
          });
        }
      } else {
        console.error("Fatal error in copy horizontal logic.");
        end = true;
        break;
      }
    }
  };

  /**
   * Copies the values and formatting from the source range to the target sheet.
   * @param {object} range - The range object containing the cell data.
   * @param {string} sheetName - The name of the target sheet to copy the values to.
   * @param {number} copyType - Indicates type of copy operation. 1 for fixed column copy, default or dyanamic.
   */
  const copyValues = (range, sheetName, copyType = 0) => {
    const oValue = range.GetValue();
    const oCharacters = range.GetCharacters(0, 2);
    const oFont = oCharacters.GetFont();
    const fontName = oFont.GetName();
    const oSize = oFont.GetSize();
    const isBold = oFont.GetBold();
    const isItalic = oFont.GetItalic();
    const rowNo = range.GetRow() - 1;
    let colNo;

    const targetSheet = Api.GetSheet(sheetName);

    if (!targetSheet) {
      console.error(`Sheet with name ${sheetName} not found.`);
      return;
    }

    if (copyType === 1) {
      colNo = 2;
    } else {
      colNo = range.GetCol() - 1;
    }

    if (oValue === null || oValue === "") {
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(" ");
    } else {
      oFont.SetName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetValue(oValue);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontName(fontName);
      targetSheet.GetRangeByNumber(rowNo, colNo).SetFontSize(oSize);

      if (isBold) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetBold(true);
      }

      if (isItalic) {
        targetSheet.GetRangeByNumber(rowNo, colNo).SetItalic(true);
      }
    }
  };

  copyColumns();
})();

Voyons maintenant comment fonctionne notre macro !

Voilà, c’est fait ! Nous venons de démontrer comment les fonctionnalités les plus complexes peuvent être automatisées en utilisant les macros ONLYOFFICE. L’API ONLYOFFICE est un outil puissant, capable d’effectuer un large éventail de tâches et offrant un immense potentiel pour développer des macros et des plugins encore plus avancés. Avec cette API, les utilisateurs peuvent exploiter toute la puissance d’ONLYOFFICE pour améliorer leur productivité et rationaliser leurs flux de travail.

Si vous avez des questions ou des concepts innovants, nous vous encourageons à les partager avec nous. Nous apprécions votre contribution et nous nous réjouissons de collaborer avec vous. Nous vous souhaitons bonne chance dans vos projets exploratoires !