Excel Parser

​

Syntax

	$input.parseExcel()

Beschreibung

Liest die eingegebene Excel-Datei und erzeugt daraus eine Datenstruktur. Hierbei wird der Sheetname als Schlüssel für die Map verwendet. Die zugehörigen Listen ergeben sich aus den Inhalten des Sheets. Standardmäßig wird das erste Sheet importiert, aber Sie können auch alle Sheets, das aktive Sheet oder bestimmte Sheets nach Index oder Namen auswählen.

​Parameter

Name Typ Beschreibung Pflicht Default
input Binärwert Excel Datei (.xls bzw. .xlsx). ja -
importAllSheets Boolean Ob alle Sheets aus der Datei importiert werden sollen. Nein false
importSheets List

Liste von Sheets, die importiert werden sollen. Zulässig sind Sheetnamen als Strings und/oder Sheetpositionen in der Datei (beginnend mit 0). Zum Beispiel werden bei der Eingabe importSheets : [0, "1", "Tabelle2"] das erste Sheet und die Sheets mit den Namen "1" bzw. "Tabelle2" importiert.

 Nein null
headers Map / List

Map mit Sheetnamen als Schlüssel und Positionen der Header im Sheet als Wert. Als zulässiger Wert sind einzelne Positionen oder Bereiche (z.B: "B2:F2") bzw. eine Liste von Positionen und/oder Bereichen erlaubt.

Wenn man keinen Sheetnamen angibt, so wird der angegebene Wert (z.B Liste von Bereichen) für alle Sheets als Header verwendet.

 Nein null
importActiveSheet Boolean

Ob das aktive Sheet importiert werden soll.

Das aktive Sheet ist, das zu letzt bearbeitete und gespeicherte Sheet, welche auch (zu mindest in Windows) beim öffnen der Excel-Datei dann direkt angezeigt wird.

 Nein false
raw Boolean

Ob die Funktion die Rohdaten als Liste von Zeilen zurückliefern soll, wobei jede Zeile als Liste von Spaltenwerten dargestellt wird. Wenn raw:false (Standard), liefert die Funktion stattdessen eine Liste von Zeilen, wobei jede Zeile als Map dargestellt wird, die Spaltenwerte auf Header abbildet.

Im Falle headers:null und raw:false wird zuerst eine Liste von Headern entsprechend den Excel-Spaltennamen (A, B, C, ...) erzeugt. Die Anzahl der erzeugten Header entspricht der maximalen Breite der Daten in der Excel-Tabelle. Anschließend werden die Einträge aus den Spalten den entsprechenden Header-Schlüsseln zugeordnet, wobei fehlende Einträge mit leeren Strings aufgefüllt werden.

 Nein false
evaluateFormulas Boolean

Ob Formeln in der Excel-Datei ausgewertet werden sollen. Wenn evaluateFormulas:true (Standard), werden die Formeln ausgewertet und das Ergebnis zurückgegeben. Wenn evaluateFormulas:false, wird die Formel selbst als Text zurückgegeben.

 Nein true

Rückgabewert

Typ: Map<Object, List> / eine Liste

  1. Pro Zeile wird eine Liste (bei raw:true) oder eine Map (bei raw:false) mit Spaltenwerten erzeugt
  2. Diese Zeilen-Objekte werden pro Sheet in einer Liste zusammengefasst
  3. Pro Sheet wird ein Map-Eintrag mit Sheetname/Sheetnummer als Schlüssel und der Liste als Wert erzeugt
  4. Wird nur ein Sheet geparst, dann wird nur die entsprechende Liste zurückgegeben (keine Map mit einem Eintrag)

Hinweise zum Verhalten

Die Priorität der Parameter ist wie folgt:

  1. Wenn importAllSheets:true ist, werden alle verfügbaren Sheets importiert, unabhängig von den anderen Parametern.
  2. Wenn importAllSheets:false und importSheets nicht leer ist, werden nur die in importSheets angegebenen Sheets importiert.
  3. Wenn importAllSheets:false, importSheets leer ist und importActiveSheet:true ist, wird das aktive Sheet importiert.
  4. Wenn importAllSheets:false, importSheets leer ist und importActiveSheet:false ist, wird das erste Sheet importiert.

Der Standardfall (ohne Parameter) entspricht Fall 4: Es wird das erste Sheet importiert.

Header-Spezifikation:

Die Header können auf verschiedene Arten definiert werden:

  • Einzelne Zelle: z.B. "A1", "B2" - Importiert den Wert an dieser Position als Header
  • Zellbereich: z.B. "A1:C1" - Importiert alle Werte in diesem Bereich als Header
  • Übergeordneter Header: z.B. "A1->B2:C2" - Verwendet den Wert von A1 als übergeordneten Header für die Werte in B2 bis C2

Der Schlüssel "all" kann verwendet werden, um die Header-Definitionen auf alle Sheets anzuwenden:

headers: {"all": ["B2:F2"]}

Man kann die Header auch als eine Liste angeben. In diesem Fall wird die Header-Definitionen auf alle Sheets angewendet:

headers: ["B2:F2"]
Der Vorteil bei der Verwendung des Schlüssels "all" besteht darin, dass man für die einzelne Sheets separat eigene Header definieren kann: 
headers: {
  "all": ["A1", "B1:C1"], // für allen anderen Sheets
  0: ["A2", "B2"] // für das Sheet mit Index 0
}
​
Sie können auch verschiedene Header-Definitionen für verschiedene Sheets angeben: 
headers: {
  "Sheet1": ["A1", "B1:C1"],
  0: ["A2", "B2"] // für das Sheet mit Index 0
}

Beispiele

Im Folgenden wird der Aufbau einer Beispiel-Excel-Datei beschrieben. Die Datei hat drei Sheets: "Test", "0" und "Vergleich". Sheet "0" ist das aktive Sheet.

Ferner sei Angenommen, dass diese Datei in einer Variablen importedData bereits gespeichert ist.

Beispiel 1: Standard-Import des ersten Sheets

{
    ...
    $importedData.parseExcel();
}

Ausgabe:

{
  "0": [
    {
      "A": "",
      "B": "",
      "C": "",
      "D": "",
      "E": "",
      "F": "",
      "G": "",
      "H": "",
      "I": "",
      "J": "",
      "K": "",
      "L": ""
    },
    {
      "A": "",
      "B": "Date",
      "C": "Integer",
      "D": "Float",
      "E": "Percent",
      "F": "Währung",
      "G": "",
      "H": "",
      "I": "I2",
      "J": "",
      "K": "",
      "L": ""
    },
    {
      "A": "",
      "B": "Thu Aug 01 00:00:00 CEST 2024",
      "C": 120.0,
      "D": 12.85,
      "E": 0.1070833,
      "F": 5.35,
      "G": "",
      "H": "",
      "I": "",
      "J": "J3",
      "K": "",
      "L": ""
    },
    {
      "A": "",
      "B": "Fri Aug 02 00:00:00 CEST 2024",
      "C": 200.0,
      "D": 20.2,
      "E": "0.101",
      "F": 5.05,
      "G": "",
      "H": "",
      "I": "",
      "J": "",
      "K": "K4",
      "L": ""
    },
    {
      "A": "",
      "B": "Sat Aug 03 00:00:00 CEST 2024",
      "C": 300.0,
      "D": 66.6,
      "E": 0.222,
      "F": 11.1,
      "G": "",
      "H": "",
      "I": "",
      "J": "",
      "K": "",
      "L": "L5"
    },
    {
      "A": "",
      "B": "",
      "C": "",
      "D": "",
      "E": "",
      "F": "",
      "G": "",
      "H": "",
      "I": "",
      "J": "",
      "K": "",
      "L": ""
    },
    {
      "A": "",
      "B": "Summe",
      "C": 620.0,
      "D": 99.65,
      "E": 0.4300833,
      "F": 21.5,
      "G": "",
      "H": "",
      "I": "I7",
      "J": "J7",
      "K": "K7",
      "L": "L7"
    }
  ]
}

Beim Aufruf von $importedData.parseExcel() wird das erste Sheet der Excel-Datei importiert. Hierbei wird zuerst eine Liste von Headern erzeugt. Die Breite der Spalten entsprechen dem längsten Spaltennamen. Anschließend werden die Einträge aus den Spalten entsprechend gemappt, wobei fehlenden Einträge mit leeren Strings aufgefüllt werden. Bei verbundenen Zellen (hier B1 - C1) wird der Wert für den kleinsten Spaltennamen eingetragen. Da

Beispiel 2: Import eines spezifischen Sheets nach Index

{
    ...
    $importedData.parseExcel(importSheets: 1);
}

Ausgabe: 

[
  {
    "A": "",
    "B": "Verbundene Zellen",
    "C": "",
    "D": ""
  },
  {
    "A": "A2",
    "B": "B2",
    "C": "C2",
    "D": "D2"
  },
  {
    "A": "A3",
    "B": "B3",
    "C": "C3",
    "D": "D3"
  },
  {
    "A": "A4",
    "B": "B4",
    "C": "C4",
    "D": "D4"
  }
]

​

Beispiel 3: Import mehrerer Sheets

{
    ...
    $importedData.parseExcel(importSheets: [0, "0"]);
}

Ausgabe: {0=[...], "0"=[...]}

Hier wird beim Aufruf von parseExcel() angegeben, dass das erste Sheet und das Sheet mit den Namen 0 importiert werden sollen.

​​

Beispiel 4: Import mit komplexen Header-Definitionen

{
    ...
    $importedData.parseExcel(importSheets: "Vergleich", 
        headers:{"Vergleich": ["C3", "D3", "E3->E4:F4", "G3->G4:H4"]});
}

Ausgabe:

{
  "Vergleich": [
    {
      "Aktuelles Jahr": {
        "Ausgaben": "W15",
        "Einnahmen": "W14"
      },
      "Vorjahr": {
        "Ausgaben": "W13_2",
        "Einnahmen": "W13_1"
      },
      "H1": "W11",
      "H2": "W12"
    },
    {
      "Aktuelles Jahr": {
        "Ausgaben": "",
        "Einnahmen": "W24"
      },
      "Vorjahr": {
        "Ausgaben": "",
        "Einnahmen": "W23_1"
      },
      "H1": "W21",
      "H2": "W22"
    },
    {
      "Aktuelles Jahr": {
        "Ausgaben": "W35",
        "Einnahmen": ""
      },
      "Vorjahr": {
        "Ausgaben": "W33_2",
        "Einnahmen": ""
      },
      "H1": "W31",
      "H2": "W32"
    },
    {
      "Aktuelles Jahr": {
        "Ausgaben": "",
        "Einnahmen": "W44"
      },
      "Vorjahr": {
        "Ausgaben": "",
        "Einnahmen": ""
      },
      "H1": "",
      "H2": "W42"
    },
    {
      "Aktuelles Jahr": {
        "Ausgaben": "",
        "Einnahmen": ""
      },
      "Vorjahr": {
        "Ausgaben": "W53_2",
        "Einnahmen": "W53_1"
      },
      "H1": "",
      "H2": ""
    }
  ]
}

Hier wird beim Aufruf von parseExcel() angegeben, dass das Sheet mit den Namen "Vergleich" importiert werden sollen und die Header in diesem Sheet auf Positionen C3, D3, E3 (als Hauptheader) mit Unterheader E4 und F4, G3 (als Hauptheader) mit Unterheader G4 und H4 zu finden sind. Nun werden alle Zeilen ab der fünften Zeile und nur die Spalten C bis H importiert und entsprechend gemappt.

​​

Beispiel 5: Import mit Header-Bereich

{
    ...
    $importedData.parseExcel(importSheets: "Test", 
        headers:{"Test": "B2:F2"});
}

Ausgabe:

{
  "Test": [
    {
      "Integer": 120.0,
      "Float": 12.85,
      "Percent": 0.1070833,
      "Währung": 5.35,
      "Date": "Thu Aug 01 00:00:00 CEST 2024"
    },
    {
      "Integer": 200.0,
      "Float": 20.2,
      "Percent": 0.101,
      "Währung": 5.05,
      "Date": "Fri Aug 02 00:00:00 CEST 2024"
    },
    {
      "Integer": 300.0,
      "Float": 66.6,
      "Percent": 0.222,
      "Währung": 11.1,
      "Date": "Sat Aug 03 00:00:00 CEST 2024"
    },
    {
      "Integer": "",
      "Float": "",
      "Percent": "",
      "Währung": "",
      "Date": ""
    },
    {
      "Integer": 620.0,
      "Float": 99.65,
      "Percent": 0.4300833,
      "Währung": 21.5,
      "Date": "Summe"
    }
  ]
}

Hier wird beim Aufruf von parseExcel() angegeben, dass das Sheet mit den Namen "Test" importiert werden sollen und die Header in diesem Sheet auf den Positionen B2 bis F2 zu finden sind. Nun werden alle Zeilen ab der dritten Zeile und nur die Spalten B bis F importiert und entsprechend gemappt.

​

Beispiel 6: Import mit Rohdaten

{
    ...
    $importedData.parseExcel(importSheets: "Test", raw:true);
}

Ausgabe:

{
  "Test": [
    [],
    [
      null,
      "Date",
      "Integer",
      "Float",
      "Percent",
      "Währung",
      null,
      null,
      "I2"
    ],
    [
      null,
      "Thu Aug 01 00:00:00 CEST 2024",
      120.0,
      12.85,
      0.1070833,
      5.35,
      null,
      null,
      null,
      "J3"
    ],
    [
      null,
      "Fri Aug 02 00:00:00 CEST 2024",
      200.0,
      20.2,
      0.101,
      5.05,
      null,
      null,
      null,
      null,
      "K4"
    ],
    [
      null,
      "Sat Aug 03 00:00:00 CEST 2024",
      300.0,
      66.6,
      0.222,
      11.1,
      null,
      null,
      null,
      null,
      null,
      "L5"
    ],
    [],
    [
      null,
      "Summe",
      620.0,
      99.65,
      0.4300833,
      21.5,
      null,
      null,
      "I7",
      "J7",
      "K7",
      "L7"
    ]
  ]
}

Der Aufruf von parseExcel(importSheers: "Test", raw:true) liefert eine Map von Listen von Listen für das Sheet "Test".

Beispiel 7: Import mit Formelauswertung

In diesem Beispiel zeigt die Excel-Datei eine einfache Tabelle mit drei Spalten:

  • Spalte A enthält feste Werte (10, 15, 20)
  • Spalte B enthält Formeln, die den Wert aus Spalte A verdoppeln (=A2*2, =A3*2, =A4*2)
  • Spalte C enthält Formeln, die die Werte aus Spalte A und B addieren (=A2+B2, =A3+B3, =A4+B4)
{
	// Mit Formelauswertung (Standard)
	$importedData.parseExcel(importSheets: "Formeln",
	headers:{"Formeln": ["A1", "B1", "C1"]});
}

Ausgabe:

{
	"Formeln": [
	{
	"Wert": 10,
	"Formel": 20,
	"Summe": 30
	},
	{
	"Wert": 15,
	"Formel": 30,
	"Summe": 45
	},
	{
	"Wert": 20,
	"Formel": 40,
	"Summe": 60
	}
	]
}

Mit evaluateFormulas:true werden die Formeln ausgewertet und die berechneten Ergebnisse zurückgegeben.

{
	// Ohne Formelauswertung
	$importedData.parseExcel(importSheets: "Formeln",
	headers:{"Formeln": ["A1", "B1", "C1"]},
	evaluateFormulas: false);
}

Ausgabe:

{
	"Formeln": [
	{
	"Wert": 10,
	"Formel": "=A2*2",
	"Summe": "=A2+B2"
	},
	{
	"Wert": 15,
	"Formel": "=A3*2",
	"Summe": "=A3+B3"
	},
	{
	"Wert": 20,
	"Formel": "=A4*2",
	"Summe": "=A4+B4"
	}
	]
}

Mit evaluateFormulas:false werden die Formeln selbst als Text zurückgegeben.