Verwenden Sie ONLYOFFICE-Makros zum Kopieren von Spalten in verschiedene Tabellenkalkulationen

ONLYOFFICE-Makros eröffnen unendliche Möglichkeiten und verwandeln komplexe Aufgaben, die sonst Stunden dauern würden, in Prozesse, die mit einem einzigen Klick erledigt sind. Kürzlich erhielten wir die Anfrage eines ONLYOFFICE-Benutzers nach einem Makro, das seinen Workflow durch effizientes Kopieren von Daten automatisieren könnte.

In diesem Artikel gehen wir auf die Einzelheiten dieser Anfrage ein und beschreiben, wie wir ein Makro entwickelt haben, das die Anforderungen des Kunden erfüllt, und untersuchen die allgemeinen Auswirkungen von ONLYOFFICE-Makros.

Erstellung des Makros

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

Zu Beginn erhalten wir das aktive Arbeitsblatt und die Auswahl in den Variablen oWorksheet und oRange.

Nun gehen wir die 3 Funktionen durch, aus denen dieses Makro besteht, nämlich: getColHeader(), copyColumns() und copyValues().

Zuerst die Funktion getColHeader().

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

Die Funktion getColHeader() benötigt einen einzigen Parameter, colNumber, der die Spaltennummer angibt. Diese Funktion prüft, ob in der ersten Zeile der angegebenen Spalte ein gültiger Wert steht. Wenn ein gültiger Wert vorhanden ist, gibt sie diesen zurück, andernfalls -1.

Als nächstes werden wir sehen, wie die Funktion copyColumns() funktioniert.

  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;
      }
    }
  };

Die Funktion copyColumns() durchläuft die Spalten des aktuellen Arbeitsblatts, beginnend mit der dritten Spalte. Für jede Spalte wird die Spaltenüberschrift mit der Funktion getColHeader() ermittelt. Wenn die Überschrift gültig ist, kopiert sie die Werte und die Formatierung dieser Spalte in das durch die Überschrift angegebene Zielblatt, indem sie die Funktion copyValues() verwendet.

Zusätzlich wird sichergestellt, dass die ersten beiden Spalten, die als gemeinsame Spalten gelten, ebenfalls in jedes Zielblatt kopiert werden. Der Prozess wird so lange fortgesetzt, bis eine ungültige Kopfzeile gefunden wird, woraufhin die Schleife beendet wird. Dies ist die Hauptfunktion des Makros und ruft weitere Hilfsfunktionen auf.

Hinweis: In diesem Code wird davon ausgegangen, dass es zwei gemeinsame Spalten gibt, und die einzelnen Spalten, die getrennt in jedes Blatt kopiert werden sollen, beginnen ab der dritten Spalte aufwärts. Diese Werte können je nach Ihrem Anwendungsfall angepasst werden.

Jetzt die Funktion copyValues().

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

Die Funktion copyValues() kopiert die Werte und die Formatierung aus dem Quellbereich in das Zielblatt. Sie benötigt drei Parameter: range, d.h. das Bereichsobjekt, das die Zelldaten enthält, sheetName, d.h. den Namen des Zielblatts, und copyType, d.h. die Angabe, ob es sich bei der zu kopierenden Spalte um eine Standardspalte oder eine bestimmte Spalte handelt.

    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;

Zunächst ruft die Funktion den Wert und die Formatierung (Schriftart, Größe, fett, kursiv) der Quellzelle ab.

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;
    }

Als nächstes wird das Zielblatt mit Hilfe der Variablen sheetName abgerufen, die von der Funktion getColHeader() erhalten wird.

Wenn das Zielblatt nicht existiert, wird ein Fehler protokolliert und die Makroausführung abgebrochen.

    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);
      }
    }
  };

Wenn alles in Ordnung ist und keine Fehler auftreten, setzt die Funktion copyValues() den Wert und die Formatierung in der entsprechenden Zelle des Zielblatts.

copyColumns();

Schließlich beenden wir das Makro, indem wir die Funktion copyColumns() aufrufen, die der Einstiegspunkt in das Makro ist.

Der vollständige Code des Makros

Hier ist der Code für das gesamte Makro:

// 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();
})();

Jetzt wollen wir sehen, wie unser Makro funktioniert!

Das war’s! Wir haben gerade gezeigt, wie selbst die komplexesten Funktionen mit ONLYOFFICE-Makros automatisiert werden können. Die ONLYOFFICE-API ist ein leistungsstarkes Tool, das eine Vielzahl von Aufgaben ausführen kann und ein immenses Potenzial für die Entwicklung noch fortschrittlicherer Makros und Plugins bietet. Mit dieser API können die Benutzer die volle Leistungsfähigkeit von ONLYOFFICE nutzen, um ihre Produktivität zu steigern und ihre Arbeitsabläufe zu optimieren.

Wenn Sie Fragen oder Ideen für Makros haben, freuen wir uns, wenn Sie diese mit uns teilen. Wir schätzen Ihren Beitrag und freuen uns darauf, mit Ihnen zusammenzuarbeiten. Viel Erfolg!