Kombinieren von Arbeitsblättern in einer einzelnen Arbeitsmappe

Artikel
12/07/2023

In diesem Beispiel wird gezeigt, wie Sie Daten aus mehreren Arbeitsmappen in eine einzelne, zentralisierte Arbeitsmappe pullen. Es werden zwei Skripts verwendet: eines zum Abrufen von Informationen aus einer Arbeitsmappe und ein weiteres zum Erstellen neuer Arbeitsblätter mit diesen Informationen. Es kombiniert die Skripts in einem Power Automate-Flow, der für einen gesamten OneDrive-Ordner fungiert.

Wichtig

In diesem Beispiel werden nur die Werte aus den anderen Arbeitsmappen kopiert. Formatierungen, Diagramme, Tabellen oder andere Objekte werden nicht beibehalten.

Lösung

Erstellen Sie eine neue Excel-Datei auf Ihrem OneDrive. In diesem Beispiel wird der Dateiname "Combination.xlsx" verwendet.
Erstellen und speichern Sie die beiden Skripts aus diesem Beispiel.
Erstellen Sie einen Ordner auf Ihrem OneDrive, und fügen Sie eine oder mehrere Arbeitsmappen mit Daten hinzu. In diesem Beispiel wird der Ordnername "output" verwendet.
Erstellen Sie einen Flow (wie im Abschnitt Power Automate-Fluss in diesem Artikel beschrieben), um die folgenden Schritte auszuführen:
1. Listet alle Dateien im Ordner "output" auf.
2. Verwenden Sie das Skript "Arbeitsblattdaten zurückgeben ", um die Daten aus jedem Arbeitsblatt in den einzelnen Arbeitsmappen abzurufen.
3. Verwenden Sie das Skript Arbeitsblätter hinzufügen , um ein neues Arbeitsblatt in der Arbeitsmappe "Combination.xlsx" für jedes Arbeitsblatt in allen anderen Dateien zu erstellen.

Beispielcode: Zurückgeben von Arbeitsblattdaten

/**
 * 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[][];
}

Beispielcode: Hinzufügen von Arbeitsblättern

/**
 * 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[][];
}

Power Automate-Flow: Kombinieren von Arbeitsblättern in einer einzelnen Arbeitsmappe

Melden Sie sich bei Power Automate an, und erstellen Sie einen neuen Instant Cloud Flow.
Wählen Sie Manuell einen Flow auslösen und dann Erstellen aus.
Rufen Sie alle Arbeitsmappen, die Sie kombinieren möchten, aus ihrem Ordner ab. Fügen Sie eine Aktion hinzu, und wählen Sie die Aktion Dateienim Ordner auflisten des OneDrive for Business-Connectors aus. Verwenden Sie für das Feld Ordner die Dateiauswahl, um den Ordner "output" auszuwählen.
Fügen Sie eine Aktion hinzu, um das Skript "Arbeitsblattdaten zurückgeben" auszuführen, um alle Daten aus den einzelnen Arbeitsmappen abzurufen. Wählen Sie die Aktion Skript ausführen des Excel Online (Business)-Connectors aus. Verwenden Sie die folgenden Werte für die Aktion. Beachten Sie, dass Power Automate, wenn Sie die ID für die Datei hinzufügen, die Aktion in ein For each-Steuerelement umschließt, sodass die Aktion für jede Datei ausgeführt wird.
- Location: OneDrive for Business
- Document Library: OneDrive
- Datei: ID (dynamischer Inhalt aus Listendateien im Ordner)
- Skript: Zurückgeben von Arbeitsblattdaten
Fügen Sie eine Aktion hinzu, um das Skript Arbeitsblätter hinzufügen für die neue Excel-Datei auszuführen, die Sie erstellt haben. Dadurch werden die Daten aus allen anderen Arbeitsmappen hinzugefügt. Fügen Sie nach der vorherigen Aktion Skript ausführen und innerhalb des For each-Steuerelements eine Aktion hinzu, die die Aktion Skript ausführen des Excel Online (Business)-Connectors verwendet. Verwenden Sie die folgenden Werte für die Aktion.
- Location: OneDrive for Business
- Document Library: OneDrive
- Datei: "Combination.xlsx" (Ihre Datei, wie von der Dateiauswahl ausgewählt)
- Skript: Hinzufügen von Arbeitsblättern
- workbookName: Name (dynamischer Inhalt aus Listendateien im Ordner)
- worksheetInformation (siehe hinweis auf die nächste Abbildung): result (dynamischer Inhalt aus Skript ausführen)
Hinweis

Wählen Sie die Schaltfläche Zur Eingabe des gesamten Arrays wechseln aus, um das Arrayobjekt direkt anstelle einzelner Elemente für das Array hinzuzufügen. Führen Sie dies vor der Eingabe des Ergebnisses aus.
Speichern Sie den Flow. Der Flow-Designer sollte wie in der folgenden Abbildung aussehen.
Verwenden Sie die Schaltfläche Test auf der Flow-Editor-Seite, oder führen Sie den Flow über die Registerkarte Meine Flows aus. Achten Sie darauf, den Zugriff zuzulassen, wenn Sie dazu aufgefordert werden.
Die Datei "Combination.xlsx" sollte jetzt über neue Arbeitsblätter verfügen.

Problembehandlung

Eine Ressource mit demselben Namen oder Bezeichner ist bereits vorhanden: Dieser Fehler weist wahrscheinlich darauf hin, dass die Arbeitsmappe "Combination.xlsx" bereits über ein Arbeitsblatt mit demselben Namen verfügt. Dies geschieht, wenn Sie den Flow mehrmals mit den gleichen Arbeitsmappen ausführen. Erstellen Sie jedes Mal eine neue Arbeitsmappe, um die kombinierten Daten zu speichern oder unterschiedliche Dateinamen im Ordner "output" zu verwenden.
Das Argument ist ungültig oder fehlt oder weist ein falsches Format auf: Dieser Fehler kann bedeuten, dass der generierte Arbeitsblattname die Anforderungen von Excel nicht erfüllt. Dies liegt wahrscheinlich daran, dass der Name zu lang ist. Wenn die Arbeitsblattnamen mehr als 30 Zeichen umfassen, ersetzen Sie den Code in "Arbeitsblätter hinzufügen", der aufruft addWorksheet , durch etwas, das die Zeichenfolge verkürzt. Da der Arbeitsmappenname selbst möglicherweise zu lang ist, fügen Sie am Ende des Arbeitsblattnamens eine inkrementierende Zahl hinzu. Deklarieren Sie diese Zahl außerhalb der forEach Schleife.
```
let worksheetNumber = 1;
// Add each new worksheet.
worksheetInformation.forEach((value) => {
    let worksheetName = `${workbookName}.${value.name}`;
    let sheet = workbook.addWorksheet(`${worksheetName.substr(0,30)}${worksheetNumber++}`);
```
Wenn die Arbeitsmappennamen länger als 30 Zeichen sind, müssen Sie sie außerdem im Flow kürzen. Zunächst müssen Sie eine Variable im Flow erstellen, um die Arbeitsmappenanzahl nachzuverfolgen. Dadurch wird vermieden, dass identische verkürzte Namen an das Skript übergeben werden. Fügen Sie eine Aktion Variable initialisieren vor dem Flow ( vom Typ "Integer") und eine Variable erhöhen-Aktion zwischen den beiden Skriptausführungsaktionen hinzu . Verwenden Sie dann anstelle von Name als workbookName in "Skript ausführen 1" den Ausdruck substring(items('Apply_to_each')?['Name'],0,min(length(items('Apply_to_each')?['Name']),20)) und den dynamischen Inhalt ihrer Variablen. Dadurch werden die Arbeitsmappennamen auf 20 Zeichen gekürzt und die aktuelle Arbeitsmappennummer an die Zeichenfolge angefügt, die an das Skript übergeben wird.

Hinweis

Anstatt den Flow und das Skript komplizierter zu machen, ist es möglicherweise einfacher, sicherzustellen, dass die Datei- und Arbeitsblattnamen kurz genug sind.