Arbeiten mit Arbeitsmappen mithilfe der Excel-JavaScript-API

In diesem Artikel wird anhand einiger Codebeispiele erklärt, wie Sie allgemeine Aufgaben mit Arbeitsmappen mithilfe der Excel-JavaScript-API ausführen. Eine vollständige Liste der Eigenschaften und Methoden, die das Workbook Objekt unterstützt, finden Sie unter Workbook-Objekt (JavaScript-API für Excel). Dieser Artikel deckt auch auf Arbeitsmappenebene durch das Anwendungsobjekt ausgeführte Aktionen ab.

Das Arbeitsmappenobjekt ist der Einstiegspunkt für die Interaktion des Add-Ins mit Excel. Es enthält Sammlungen von Arbeitsblättern, Tabellen, PivotTables und mehr über die Excel-Daten aufgerufen und geändert werden. Das WorksheetCollection-Objekt gewährt Ihrem Add-In über einzelne Arbeitsblätter Zugriff auf alle Daten der Arbeitsmappe. Insbesondere können Sie mit Ihrem Add-In Arbeitsblätter hinzufügen, zwischen diesen navigieren und Arbeitsblattereignisse Handlern zuweisen. Im Artikel Arbeiten mit Arbeitsblättern mithilfe der Excel-JavaScript-API wird beschrieben, wie Sie auf Arbeitsblätter zugreifen und diese bearbeiten.

Aufrufen der aktiven Zelle oder des markierten Bereichs

Das Arbeitsmappenobjekt enthält zwei Methoden, mit denen ein Zellbereich aufgerufen werden kann, der durch einen Benutzer oder ein Add-In ausgewählt wurde: getActiveCell() und getSelectedRange(). getActiveCell() ruft die aktive Zelle aus der Arbeitsmappe als Bereichsobjekt auf. Das folgende Beispiel zeigt den Aufruf von getActiveCell(). Anschließend wird die Adresse der Zelle in der Konsole gedruckt.

await Excel.run(async (context) => {
    let activeCell = context.workbook.getActiveCell();
    activeCell.load("address");
    await context.sync();

    console.log("The active cell is " + activeCell.address);
});

Die Methode getSelectedRange() gibt den aktuell ausgewählten einzelnen Bereich zurück. Wenn mehrere Bereiche ausgewählt sind, wird ein InvalidSelection-Fehler ausgelöst. Das folgende Beispiel zeigt den Aufruf von getSelectedRange(). Anschließend wird die Füllfarbe des Bereichs auf Gelb festgelegt.

await Excel.run(async (context) => {
    let range = context.workbook.getSelectedRange();
    range.format.fill.color = "yellow";
    await context.sync();
});

Erstellen einer Arbeitsmappe

Ihr Add-In kann unabhängig von der Excel-Instanz, in dem das Add-In derzeit ausgeführt wird, eine neue Arbeitsmappe erstellen. Für diesen Zweck verfügt das Excel-Objekt über die Methode createWorkbook. Wenn diese Methode aufgerufen wird, wird die neue Arbeitsmappe sofort geöffnet und in einer neuer Instanz von Excel angezeigt. Das Add-In bleibt geöffnet und wird mit der vorherigen Arbeitsmappe ausgeführt.

Excel.createWorkbook();

Die Methode createWorkbook kann auch eine Kopie einer vorhandenen Arbeitsmappe erstellen. Die Methode akzeptiert eine base64-codierte Zeichenfolgendarstellung der XLSX-Datei als optionalen Parameter. Die daraus resultierende Arbeitsmappe ist eine Kopie dieser Datei, vorausgesetzt, dass das Zeichenfolgenargument eine gültige XLSX-Datei ist.

Sie können die aktuelle Arbeitsmappe Ihres Add-Ins mithilfe von Dateislicing als base64-codierte Zeichenfolge abrufen. Die FileReader-Klasse kann verwendet werden, um eine Datei in die erforderliche base64-codierte Zeichenfolge zu konvertieren, wie im folgenden Beispiel gezeigt wird.

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (function (event) {
    Excel.run(function (context) {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);

        Excel.createWorkbook(externalWorkbook);
        return context.sync();
    });
});

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

Einfügen einer Kopie der vorhandenen Arbeitsmappe in die aktuelle

Das vorherige Beispiel zeigt eine neue Arbeitsmappe, die aus einer vorhandenen Arbeitsmappe erstellt wurde. Sie können auch einen Teil oder die gesamte Arbeitsmappe in die mit Ihrem Add-In verknüpfte Arbeitsmappe kopieren. Eine Arbeitsmappe verfügt über die insertWorksheetsFromBase64 -Methode zum Einfügen von Kopien der Arbeitsblätter der Zielarbeitsmappe in sich selbst. Die Datei der anderen Arbeitsmappe wird wie beim Excel.createWorkbook Aufruf als base64-codierte Zeichenfolge übergeben.

insertWorksheetsFromBase64(base64File: string, options?: Excel.InsertWorksheetOptions): OfficeExtension.ClientResult<string[]>;

Im folgenden Codebeispiel wird gezeigt, wie Arbeitsblätter aus einer anderen Arbeitsmappe in die aktuelle Arbeitsmappe eingefügt werden. In diesem Codebeispiel wird zunächst eine Arbeitsmappendatei mit einem FileReader -Objekt verarbeitet und eine base64-codierte Zeichenfolge extrahiert. Anschließend wird diese base64-codierte Zeichenfolge in die aktuelle Arbeitsmappe eingefügt. Die neuen Arbeitsblätter werden nach dem Arbeitsblatt mit dem Namen Sheet1 eingefügt. Beachten Sie, dass [] als Parameter für die InsertWorksheetOptions.sheetNamesToInsert-Eigenschaft übergeben wird. Dies bedeutet, dass alle Arbeitsblätter aus der Zielarbeitsmappe in die aktuelle Arbeitsmappe eingefügt werden.

// Retrieve the external workbook file and set up a `FileReader` object. 
let myFile = document.getElementById("file");
let reader = new FileReader();

reader.onload = (event) => {
    Excel.run((context) => {
        // Remove the metadata before the base64-encoded string.
        let startIndex = reader.result.toString().indexOf("base64,");
        let externalWorkbook = reader.result.toString().substr(startIndex + 7);
            
        // Retrieve the current workbook.
        let workbook = context.workbook;
            
        // Set up the insert options. 
        let options = { 
            sheetNamesToInsert: [], // Insert all the worksheets from the source workbook.
            positionType: Excel.WorksheetPositionType.after, // Insert after the `relativeTo` sheet.
            relativeTo: "Sheet1" // The sheet relative to which the other worksheets will be inserted. Used with `positionType`.
        }; 
            
         // Insert the new worksheets into the current workbook.
         workbook.insertWorksheetsFromBase64(externalWorkbook, options);
         return context.sync();
    });
};

// Read the file as a data URL so we can parse the base64-encoded string.
reader.readAsDataURL(myFile.files[0]);

Schützen der Struktur der Arbeitsmappe

Ihr Add-In kann die Möglichkeit eines Benutzers, die Struktur der Arbeitsmappe zu bearbeiten, steuern. Die Eigenschaft protection des Arbeitsmappenobjekts ist ein WorkbookProtection-Objekt mit einer protect()-Methode. Das folgende Beispiel zeigt ein einfaches Szenario, in dem der Schutz der Struktur der Arbeitsmappe umgeschaltet wird.

await Excel.run(async (context) => {
    let workbook = context.workbook;
    workbook.load("protection/protected");
    await context.sync();

    if (!workbook.protection.protected) {
        workbook.protection.protect();
    }
});

Die protect-Methode akzeptiert einen optionalen Zeichenfolgeparameter. Diese Zeichenfolge stellt das Kennwort dar, das der Benutzer benötigt, um den Schutz zu umgehen und die Struktur der Arbeitsmappe zu ändern.

Der Schutz kann auch auf Arbeitsblattebene festgelegt werden, um eine unerwünschte Bearbeitung von Daten zu verhindern. Weitere Informationen finden Sie im Abschnitt Datenschutz des Artikels Arbeiten mit Arbeitsblättern mithilfe der Excel-JavaScript-API.

Zugriff auf Dokumenteigenschaften

Arbeitsmappenobjekte verfügen über Zugriff auf die Metadaten der Office-Datei, die so genannten Dokumenteigenschaften. Die Eigenschaft des properties Workbook-Objekts ist ein DocumentProperties-Objekt , das einige dieser Metadatenwerte enthält. Im folgenden Beispiel wird gezeigt, wie die -Eigenschaft festgelegt wird author .

await Excel.run(async (context) => {
    let docProperties = context.workbook.properties;
    docProperties.author = "Alex";
    await context.sync();
});

Sie können auch benutzerdefinierte Eigenschaften definieren. Das DocumentProperties-Objekt enthält eine custom-Eigenschaft, die eine Sammlung von Schlüssel-Wert-Paaren für benutzerdefinierte Eigenschaften darstellt. Ein Beispiel für das Festlegen benutzerdefinierter Eigenschaften finden Sie im Abschnitt Benutzerdefinierte XML-Daten in Excel und Word des Artikels Beibehalten des Add-In-Zustands und der Einstellungen.

Zugriff auf Dokumenteinstellungen

Die Einstellungen einer Arbeitsmappe ähneln der Sammlung von benutzerdefinierten Eigenschaften. Der Unterschied besteht darin, dass die Einstellungen für eine einzelne Excel-Datei und Add-In-Kopplung eindeutig sind, während die Eigenschaften ausschließlich mit der Datei verbunden sind. Das folgende Beispiel zeigt, wie Sie eine Einstellung erstellen und darauf zugreifen.

await Excel.run(async (context) => {
    let settings = context.workbook.settings;
    settings.add("NeedsReview", true);
    let needsReview = settings.getItem("NeedsReview");
    needsReview.load("value");

    await context.sync();
    console.log("Workbook needs review : " + needsReview.value);
});

Zugreifen auf Anwendungskultureinstellungen

Eine Arbeitsmappe verfügt über Sprach- und Kultureinstellungen, die sich darauf auswirken, wie bestimmte Daten angezeigt werden. Diese Einstellungen können beim Lokalisieren von Daten helfen, wenn die Benutzer Ihres Add-Ins Arbeitsmappen in verschiedenen Sprachen und Kulturen freigeben. Ihr Add-In kann die Zeichenfolgenanalyse verwenden, um das Format von Zahlen, Datumsangaben und Uhrzeiten basierend auf den Systemkultureinstellungen zu lokalisieren, sodass jeder Benutzer Daten im Format seiner eigenen Kultur sieht.

Application.cultureInfo definiert die Systemkultureinstellungen als CultureInfo-Objekt . Dies enthält Einstellungen wie das numerische Dezimaltrennzeichen oder das Datumsformat.

Einige Kultureinstellungen können über die Excel-Benutzeroberfläche geändert werden. Die Systemeinstellungen werden im CultureInfo -Objekt beibehalten. Alle lokalen Änderungen werden als Eigenschaften auf Anwendungsebene beibehalten, z Application.decimalSeparator. B. .

Im folgenden Beispiel wird das Dezimaltrennzeichen einer numerischen Zeichenfolge von "" in das von den Systemeinstellungen verwendete Zeichen geändert.

// This will convert a number like "14,37" to "14.37"
// (assuming the system decimal separator is ".").
await Excel.run(async (context) => {
    let sheet = context.workbook.worksheets.getItem("Sample");
    let decimalSource = sheet.getRange("B2");

    decimalSource.load("values");
    context.application.cultureInfo.numberFormat.load("numberDecimalSeparator");
    await context.sync();

    let systemDecimalSeparator =
        context.application.cultureInfo.numberFormat.numberDecimalSeparator;
    let oldDecimalString = decimalSource.values[0][0];

    // This assumes the input column is standardized to use "," as the decimal separator.
    let newDecimalString = oldDecimalString.replace(",", systemDecimalSeparator);

    let resultRange = sheet.getRange("C2");
    resultRange.values = [[newDecimalString]];
    resultRange.format.autofitColumns();
    await context.sync();
});

Steuern des Berechnungsverhaltens

Einstellen des Berechnungsmodus

Standardmäßig berechnet Excel Formelergebnisse neu, sobald eine Zelle geändert wird. Die Leistung Ihres Add-Ins kann von der Anpassung dieses Berechnungsverhaltens profitieren. Das Anwendungsobjekt besitzt eine calculationMode-Eigenschaft vom Typ CalculationMode. Sie kann auf die folgenden Werte festgelegt werden.

• automatic: Das Standardverhalten für die neue Berechnung, wobei Excel immer dann neue Formelergebnisse berechnet, wenn die entsprechenden Daten geändert werden.
• automaticExceptTables: Identisch mit automatic, außer dass alle an Werten in Tabellen vorgenommenen Änderungen ignoriert werden.
• manual: Berechnungen werden nur dann durchgeführt, wenn der Benutzer oder das Add-In diese anfordern.

Einstellen des Berechnungstyps

Das Anwendungsobjekt enthält eine Methode, um eine sofortige Neuberechnung zu erzwingen. Application.calculate(calculationType) startet eine manuelle Neuberechnung auf der Grundlage der angegebenen calculationType. Die folgenden Werte können angegeben werden.

• full: Neuberechnung aller Formeln in allen geöffneten Arbeitsmappen, unabhängig davon, ob sie seit der letzten Neuberechnung geändert wurden.
• fullRebuild: Überprüfung abhängiger Formeln und dann Neuberechnung aller Formeln in allen geöffneten Arbeitsmappen, unabhängig davon, ob sie seit der letzten Neuberechnung geändert wurden.
• recalculate: Neuberechnung aller Formeln, die seit der letzten Neuberechnung geändert wurden (oder programmgesteuert für die Neuberechnung gekennzeichnet wurden) und der davon abhängigen Formeln in allen aktiven Arbeitsmappen.

Berechnungen vorübergehend anhalten

Mit der Excel-API können Add-Ins Berechnungen auch bis zum Aufruf von RequestContext.sync() deaktivieren. Dies geschieht mit suspendApiCalculationUntilNextSync(). Verwenden Sie diese Methode, wenn das Add-In große Bereiche bearbeitet, ohne dass zwischen den Bearbeitungen ein Zugriff auf die Daten erforderlich ist.

context.application.suspendApiCalculationUntilNextSync();

Erkennen der Arbeitsmappenaktivierung

Ihr Add-In kann erkennen, wenn eine Arbeitsmappe aktiviert ist. Eine Arbeitsmappe wird inaktiv, wenn der Benutzer den Fokus zu einer anderen Arbeitsmappe, zu einer anderen Anwendung oder (in Excel im Web) zu einer anderen Registerkarte des Webbrowsers wechselt. Eine Arbeitsmappe wird aktiviert , wenn der Benutzer den Fokus auf die Arbeitsmappe zurückgibt. Die Arbeitsmappenaktivierung kann Rückruffunktionen in Ihrem Add-In auslösen, z. B. das Aktualisieren von Arbeitsmappendaten.

Um zu erkennen, wann eine Arbeitsmappe aktiviert ist, registrieren Sie einen Ereignishandler für das onActivated-Ereignis einer Arbeitsmappe. Ereignishandler für das onActivated Ereignis empfangen ein WorkbookActivatedEventArgs-Objekt , wenn das Ereignis ausgelöst wird.

Im folgenden Codebeispiel wird gezeigt, wie Sie den onActivated Ereignishandler registrieren und eine Rückruffunktion einrichten.

async function run() {
    await Excel.run(async (context) => {
        // Retrieve the workbook.
        let workbook = context.workbook;
    
        // Register the workbook activated event handler.
        workbook.onActivated.add(workbookActivated);
        await context.sync();
    });
}

async function workbookActivated(event) {
    await Excel.run(async (context) => {
        // Retrieve the workbook and load the name.
        let workbook = context.workbook;
        workbook.load("name");        
        await context.sync();

        // Callback function for when the workbook is activated.
        console.log(`The workbook ${workbook.name} was activated.`);
    });
}

Speichern der Arbeitsmappe

Workbook.save speichert die Arbeitsmappe in einem persistenten Speicher. Die save -Methode akzeptiert einen einzelnen, optionalen saveBehavior Parameter, der einen der folgenden Werte sein kann.

• Excel.SaveBehavior.save (Standard): Die Datei wird gespeichert, ohne dass der Benutzer aufgefordert wird, den Dateinamen und den Speicherort anzugeben. Wurde die Datei noch nicht gespeichert, wird sie am Standardspeicherort gespeichert. Wurde die Datei zuvor gespeichert, wird sie am gleichen Speicherort gespeichert.
• Excel.SaveBehavior.prompt: Wurde die Datei noch nicht gespeichert, wird der Benutzer aufgefordert, den Dateinamen und den Speicherort anzugeben. Wurde die Datei zuvor gespeichert, wird sie am gleichen Speicherort gespeichert, und es wird keine Eingabeaufforderung für den Benutzer angezeigt.

context.workbook.save(Excel.SaveBehavior.prompt);

Schließen der Arbeitsmappe

Workbook.close schließt die Arbeitsmappe, zusammen mit den der Arbeitsmappe zugeordneten Add-Ins (die Excel-Anwendung bleibt geöffnet). Die close -Methode akzeptiert einen einzelnen, optionalen closeBehavior Parameter, der einen der folgenden Werte sein kann.

• Excel.CloseBehavior.save (Standard): Die Datei wird vor dem Schließen gespeichert. Wurde die Datei noch nicht gespeichert, wird der Benutzer aufgefordert, den Dateinamen und den Speicherort anzugeben.
• Excel.CloseBehavior.skipSave: Die Datei wird sofort geschlossen und nicht gespeichert. Alle nicht gespeicherten Änderungen gehen verloren.

context.workbook.close(Excel.CloseBehavior.save);

Siehe auch

• JavaScript-Objektmodell für Excel in Office-Add-Ins
• Arbeiten mit Arbeitsblättern mithilfe der Excel-JavaScript-API