Combiner des feuilles de calcul dans un seul classeur

Article
12/06/2023

Cet exemple montre comment extraire des données de plusieurs classeurs dans un classeur centralisé unique. Il utilise deux scripts : un pour récupérer des informations à partir d’un classeur et un autre pour créer des feuilles de calcul avec ces informations. Il combine les scripts dans un flux Power Automate qui agit sur un dossier OneDrive entier.

Importante

Cet exemple copie uniquement les valeurs des autres classeurs. Il ne conserve pas la mise en forme, les graphiques, les tableaux ou d’autres objets.

Solution

Créez un fichier Excel dans votre OneDrive. Le nom de fichier « Combination.xlsx » est utilisé dans cet exemple.
Créez et enregistrez les deux scripts de cet exemple.
Créez un dossier dans votre OneDrive et ajoutez-y un ou plusieurs classeurs contenant des données. Le nom de dossier « output » est utilisé dans cet exemple.
Créez un flux (comme décrit dans la section Flux Power Automate de cet article) pour effectuer les étapes suivantes :
1. Répertoriez tous les fichiers du dossier « output ».
2. Utilisez le script De retour des données de feuille de calcul pour obtenir les données de chaque feuille de calcul de chacun des classeurs.
3. Utilisez le script Ajouter des feuilles de calcul pour créer une feuille de calcul dans le classeur « Combination.xlsx » pour chaque feuille de calcul de tous les autres fichiers.

Exemple de code : retourner des données de feuille de calcul

/**
 * This script returns the values from the used ranges on each worksheet.
 */
function main(workbook: ExcelScript.Workbook): WorksheetData[] {
  // Create an object to return the data from each worksheet.
  let worksheetInformation: WorksheetData[] = [];

  // Get the data from every worksheet, one at a time.
  workbook.getWorksheets().forEach((sheet) => {
    let values = sheet.getUsedRange()?.getValues();
    worksheetInformation.push({
       name: sheet.getName(),
       data: values as string[][]
    });
  });

  return worksheetInformation;
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Exemple de code : Ajouter des feuilles de calcul

/**
 * This script creates a new worksheet in the current workbook for each WorksheetData object provided.
 */
function main(workbook: ExcelScript.Workbook, workbookName: string, worksheetInformation: WorksheetData[]) {
  // Add each new worksheet.
  worksheetInformation.forEach((value) => {
    let sheet = workbook.addWorksheet(`${workbookName}.${value.name}`);

    // If there was any data in the worksheet, add it to a new range.
    if (value.data) {
      let range = sheet.getRangeByIndexes(0, 0, value.data.length, value.data[0].length);
      range.setValues(value.data);
    }
  });
}

// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
  name: string;
  data: string[][];
}

Flux Power Automate : combiner des feuilles de calcul en un seul classeur

Connectez-vous à Power Automate et créez un flux cloud instantané.
Choisissez Déclencher manuellement un flux , puis sélectionnez Créer.
Obtenez tous les classeurs que vous souhaitez combiner à partir de leur dossier. Ajoutez une action et choisissez l’action Lister les fichiers dans le dossier du connecteur OneDrive Entreprise. Pour le champ Dossier , utilisez le sélecteur de fichiers pour sélectionner le dossier « sortie ».
Ajoutez une action pour exécuter le script De retour des données de feuille de calcul afin d’obtenir toutes les données de chacun des classeurs. Choisissez l’action Exécuter le script du connecteur Excel Online (Business). Utilisez les valeurs suivantes pour l’action. Notez que lorsque vous ajoutez l’ID du fichier, Power Automate encapsule l’action dans un contrôle Pour chaque , de sorte que l’action est effectuée sur chaque fichier.
- Emplacement : OneDrive Entreprise
- Bibliothèque de documents : OneDrive
- Fichier : Id (contenu dynamique de la liste des fichiers dans le dossier)
- Script : retourner des données de feuille de calcul
Ajoutez une action pour exécuter le script Ajouter des feuilles de calcul sur le nouveau fichier Excel que vous avez créé. Cela ajoute les données de tous les autres classeurs. Après l’action Exécuter le script précédente et à l’intérieur du contrôle Pour chaque, ajoutez une action qui utilise l’action Exécuter le script du connecteur Excel Online (Business). Utilisez les valeurs suivantes pour l’action.
- Emplacement : OneDrive Entreprise
- Bibliothèque de documents : OneDrive
- Fichier : « Combination.xlsx » (votre fichier, tel que sélectionné par le sélecteur de fichiers)
- Script : Ajouter des feuilles de calcul
- workbookName : Nom (contenu dynamique de la liste des fichiers dans le dossier)
- worksheetInformation (consultez la note qui suit l’image suivante) : résultat (contenu dynamique de l’exécution du script)
Remarque

Sélectionnez le bouton Basculer pour entrer l’intégralité du tableau pour ajouter directement l’objet tableau, au lieu d’éléments individuels pour le tableau. Effectuez cette opération avant d’entrer le résultat.
Enregistrez le flux. Le concepteur de flux doit ressembler à l’image suivante.
Utilisez le bouton Test de la page de l’éditeur de flux ou exécutez le flux via votre onglet Mes flux . Veillez à autoriser l’accès lorsque vous y êtes invité.
Le fichier « Combination.xlsx » doit maintenant contenir de nouvelles feuilles de calcul.

Résolution des problèmes

Il existe déjà une ressource portant le même nom ou le même identificateur : cette erreur indique probablement que le classeur « Combination.xlsx » possède déjà une feuille de calcul portant le même nom. Cela se produit si vous exécutez le flux plusieurs fois avec les mêmes classeurs. Créez un classeur à chaque fois pour stocker les données combinées ou utiliser des noms de fichiers différents dans le dossier « output ».
L’argument n’est pas valide ou est manquant ou a un format incorrect : cette erreur peut signifier que le nom de la feuille de calcul générée ne répond pas aux exigences d’Excel. Cela est probablement dû au fait que le nom est trop long. Si les noms de feuille de calcul doivent comporter plus de 30 caractères, remplacez le code dans « Ajouter des feuilles de calcul » qui appelle addWorksheet par quelque chose qui raccourcit la chaîne. Étant donné que le nom du classeur lui-même peut être trop long, ajoutez un numéro incrémentiel à la fin du nom de la feuille de calcul. Déclarez ce nombre en dehors de la forEach boucle.
```
let worksheetNumber = 1;
// Add each new worksheet.
worksheetInformation.forEach((value) => {
    let worksheetName = `${workbookName}.${value.name}`;
    let sheet = workbook.addWorksheet(`${worksheetName.substr(0,30)}${worksheetNumber++}`);
```
En outre, si les noms des classeurs ont plus de 30 caractères, vous devez les raccourcir dans le flux. Tout d’abord, vous devez créer une variable dans le flux pour suivre le nombre de classeurs. Cela permet d’éviter que des noms raccourcis identiques ne soient passés au script. Ajoutez une action Initialiser la variable avant le flux (de type « Integer ») et une action Incrémenter une variable entre les deux actions Exécuter le script . Ensuite, au lieu d’utiliser Name comme workbookName dans « Exécuter le script 1 », utilisez l’expression substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?['Name']),20)) et le contenu dynamique de votre variable. Cela raccourcit les noms des classeurs à 20 caractères et ajoute le numéro de classeur actuel à la chaîne passée au script.

Remarque

Plutôt que de compliquer le flux et le script, il peut être plus facile de garantir que les noms des fichiers et des feuilles de calcul sont suffisamment courts.

Partager via