In diesem Artikel erfahren Sie, wie Sie mithilfe eines Excel-Skripts Daten von einer Online-Datenbank abfragen. Im konkreten Fall rufen wir Wasserstandsdaten von einer Datenbank ab und bereiten diese grafisch auf.

Office Skript Übungsaufgabe in Excel: Webdaten Online abrufen.

Beschreibung:

In diesem Beispiel wird ein Office-Skript verwendet, um Daten aus einer externen Quelle (der NOAA-Datenbank für Gezeiten und Strömungen) abzurufen und die resultierenden Informationen grafisch darzustellen.

Beispiel-Aufgabe:

In diesem Beispiel müssen Sie den Wasserstand an der Seattle-Station der National Oceanic and Atmospheric Administration grafisch darstellen. 

Sie entwickeln ein Skript, das den Befehl „fetch“ verwendet, um die NOAA-Datenbank „Gezeiten und Strömungen“ abzufragen. Dadurch wird der Wasserstand über einen bestimmten Zeitraum aufgezeichnet. Die Informationen werden als JSON zurückgegeben, sodass ein Teil des Skripts sie in Bereichswerte übersetzt. Sobald die Daten in der Tabelle vorliegen, werden sie zur Erstellung eines Diagramms verwendet.

Anleitung zum Aufbau:

Step 1.  Excel

Öffnen Sie die Arbeitsmappe in Excel.

Step 2. Erstellen eines neuen Skripts

Wählen Sie auf der Registerkarte „Automatisieren“ die Option „Neues Skript“ und fügen Sie das folgende Skript in den Editor ein.

/**
 * Gets data from the National Oceanic and Atmospheric Administration's Tides and Currents database. 
 * That data is used to make a chart.
 */
async function main(workbook: ExcelScript.Workbook) {
  // Get the current sheet.
  let currentSheet = workbook.getActiveWorksheet();

  // Create selection of parameters for the fetch URL.
  // More information on the NOAA APIs is found here: 
  // https://api.tidesandcurrents.noaa.gov/api/prod/
  const option = "water_level";
  const startDate = "20201225"; /* yyyymmdd date format */
  const endDate = "20201227";
  const station = "9447130"; /* Seattle */

  // Construct the URL for the fetch call.
  const strQuery = `https://api.tidesandcurrents.noaa.gov/api/prod/datagetter?product=${option}&begin_date=${startDate}&end_date=${endDate}&datum=MLLW&station=${station}&units=english&time_zone=gmt&application=NOS.COOPS.TAC.WL&format=json`;

  console.log(strQuery);

  // Resolve the Promises returned by the fetch operation.
  const response = await fetch(strQuery);
  const rawJson: string = await response.json();

  // Translate the raw JSON into a usable state.
  const stringifiedJson = JSON.stringify(rawJson);

  // Note that we're only taking the data part of the JSON and excluding the metadata.
  const noaaData: NOAAData[] = JSON.parse(stringifiedJson).data;

  // Create table headers and format them to stand out.
  let headers = [["Time", "Level"]];
  let headerRange = currentSheet.getRange("A1:B1");
  headerRange.setValues(headers);
  headerRange.getFormat().getFill().setColor("#4472C4");
  headerRange.getFormat().getFont().setColor("white");

  // Insert all the data in rows from JSON.
  let noaaDataCount = noaaData.length;
  let dataToEnter = [[], []]
  for (let i = 0; i < noaaDataCount; i++) {
    let currentDataPiece = noaaData[i];
    dataToEnter[i] = [currentDataPiece.t, currentDataPiece.v];
  }

  let dataRange = currentSheet.getRange("A2:B" + String(noaaDataCount + 1)); /* +1 to account for the title row */
  dataRange.setValues(dataToEnter);

  // Format the "Time" column for timestamps.
  dataRange.getColumn(0).setNumberFormatLocal("[$-en-US]mm/dd/yyyy hh:mm AM/PM;@");

  // Create and format a chart with the level data.
  let chart = currentSheet.addChart(ExcelScript.ChartType.xyscatterSmooth, dataRange);
  chart.getTitle().setText("Water Level - Seattle");
  chart.setTop(0);
  chart.setLeft(300);
  chart.setWidth(500);
  chart.setHeight(300);
  chart.getAxes().getValueAxis().setShowDisplayUnitLabel(false);
  chart.getAxes().getCategoryAxis().setTextOrientation(60);
  chart.getLegend().setVisible(false);

  // Add a comment with the data attribution.
  currentSheet.addComment(
    "A1",
    `This data was taken from the National Oceanic and Atmospheric Administration's Tides and Currents database on ${new Date(Date.now())}.`
  );

  /**
   * An interface to wrap the parts of the JSON we need.
   * These properties must match the names used in the JSON.
   */ 
  interface NOAAData {
    t: string; // Time
    v: number; // Level
  }
}

Step 3. Speichern des Skripts

Speichern Sie das Skript unter dem Namen ‚NOAA Water Level Chart‚.

Ausführen des Skripts

Führen Sie auf einem beliebigen Arbeitsblatt das Skript NOAA Water Level Chart aus. Das Skript ruft die Wasserstandsdaten vom 25. Dezember 2020 bis zum 27. Dezember 2020 ab. Die const-Variablen am Anfang des Skripts können geändert werden, um andere Daten zu verwenden oder andere Stationsinformationen zu erhalten. Die CO-OPS-API für den Datenabruf beschreibt, wie alle diese Daten abgerufen werden.

Nach dem Ausführen des Skripts

Prozess:

Branchen im Zusammenhang mit maritimen Aktivitäten, Küstenmanagement und Umweltüberwachung können von diesem Projekt profitieren. Beispielsweise können Reedereien, Küstenstädte und Forschungseinrichtungen die Daten nutzen, um fundierte Entscheidungen über Navigation, Hochwasservorsorge und Forschungsprojekte zu treffen.

Benötigen Sie einen VBA Programmierer?

Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).
Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular.