Excel Parser

Syntax

	$input.parseExcel()

Description

Reads the Excel file entered and creates a data structure from it. The sheet name is used as the key for the map. The associated lists are derived from the contents of the sheet. The first sheet is imported by default, but you can also select all sheets, the active sheet or specific sheets by index or name.

Parameters

Sheet name Type Type Description Mandatory Default
Input Binary value Excel file (.xls or .xlsx). Yes -
importAllSheets Boolean Whether all sheets should be imported from the file. No false
importSheets List

List of sheets to be imported. Sheet names are permitted as strings and/or sheet positions in the file (starting with 0). For example, if you enter importSheets : [0, "1", "Table2"], the first sheet and the sheets with the names "1" and "Table2" are imported.

 No null
headers Map / List

Map with sheet name as key and positions of the headers in the sheet as value. Individual positions or areas (e.g. "B2:F2") or a list of positions and/or areas are permitted as a valid value.

If you do not specify a sheet name, the specified value (e.g. list of ranges) is used as the header for all sheets.

 No null
importActiveSheet Boolean

Whether the active sheet should be imported.

The active sheet is the sheet that was last edited and saved, which is also displayed directly (at least in Windows) when the Excel file is opened.

 No false
raw Boolean

Whether the function should return the raw data as a list of rows, with each row displayed as a list of column values. If raw:false (default), the function returns a list of rows instead, with each row represented as a map that maps column values to headers.

In case headers:null and raw:false a list of headers corresponding to the Excel column names (A, B, C, ...) is generated first. The number of headers generated corresponds to the maximum width of the data in the Excel table. The entries from the columns are then assigned to the corresponding header keys, with missing entries being filled with empty strings.

 No false
evaluateFormulas Boolean

Whether formulas in the Excel file should be evaluated. If evaluateFormulas:true (default), the formulas are evaluated and the result is returned. If evaluateFormulas:falsethe formula itself is returned as text.

 No true

Return value

Type: Map<Object, List> / a list

  1. One list (for raw:true) or a map (with raw:false) with column values is generated
  2. These row objects are summarized per sheet in a list
  3. A map entry with sheet name/sheet number as the key and the list as the value is created for each sheet
  4. If only one sheet is parsed, then only the corresponding list is returned (no map with an entry)

Notes on the behavior

The priority of the parameters is as follows:

  1. If importAllSheets:true all available sheets are imported, regardless of the other parameters.
  2. If importAllSheets:false and importSheets are not empty , only the sheets specified in importSheets are imported.
  3. If importAllSheets:false, importSheets is empty and importActiveSheet:true is empty, the active sheet is imported.
  4. If importAllSheets:false, importSheets is empty and importActiveSheet:false the first sheet is imported.

The default case (without parameters) corresponds to case 4: The first sheet is imported.

Header specification:

The headers can be defined in various ways:

  • Single cell: e.g. "A1", "B2" - Imports the value at this position as header
  • Cell range: e.g. "A1:C1" - Imports all values in this range as header
  • Parent header: e.g. "A1->B2:C2" - Uses the value of A1 as the parent header for the values in B2 to C2

The "all" key can be used to apply the header definitions to all sheets:

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

You can also specify the headers as a list. In this case, the header definitions are applied to all sheets:

headers: ["B2:F2"]
The advantage of using the "all" key is that you can define separate headers for the individual sheets: 
headers: {
  "all": ["A1", "B1:C1"], // für allen anderen Sheets
  0: ["A2", "B2"] // für das Sheet mit Index 0
}
You can also specify different header definitions for different sheets: 
headers: {
  "Sheet1": ["A1", "B1:C1"],
  0: ["A2", "B2"] // für das Sheet mit Index 0
}

Examples

The structure of an example Excel file is described below. The file has three sheets: "Test", "0" and "Comparison". Sheet "0" is the active sheet.

It is also assumed that this file is already saved in a variable importedData.

Example 1: Standard import of the first sheet

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

Output:

{
  "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"
    }
  ]
}

When $importedData.parseExcel() is called, the first sheet of the Excel file is imported. A list of headers is generated first. The width of the columns corresponds to the longest column name. The entries from the columns are then mapped accordingly, with missing entries being filled with empty strings. For connected cells (here B1 - C1), the value for the smallest column name is entered. Since

Example 2: Importing a specific sheet by index

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

Output: 

[
  {
    "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"
  }
]

Example 3: Importing several sheets

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

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

Here, when parseExcel() is called, it is specified that the first sheet and the sheet with the name 0 are to be imported.

Example 4: Import with complex header definitions

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

Output:

{
  "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": ""
    }
  ]
}

Here, when parseExcel() is called, it is specified that the sheet with the name "Comparison" is to be imported and that the headers in this sheet are to be found in positions C3, D3, E3 (as main header) with subheader E4 and F4, G3 (as main header) with subheaders G4 and H4. Now all rows from the fifth row and only columns C to H are imported and mapped accordingly.

Example 5: Import with header area

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

Output:

{
  "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"
    }
  ]
}

Here, when calling parseExcel(), it is specified that the sheet with the name "Test" is to be imported and that the headers in this sheet are to be found in positions B2 to F2. Now all rows from the third row and only columns B to F are imported and mapped accordingly.

Example 6: Import with raw data

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

Output:

{
  "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"
    ]
  ]
}

Calling parseExcel(importSheers: "Test", raw:true) returns a map of lists of lists for the "Test" sheet.

Example 7: Import with formula evaluation

In this example, the Excel file shows a simple table with three columns:

  • Column A contains fixed values (10, 15, 20)
  • Column B contains formulas that double the value from column A (=A2*2, =A3*2, =A4*2)
  • Column C contains formulas that add the values from columns A and B (=A2+B2, =A3+B3, =A4+B4)
{
	// Mit Formelauswertung (Standard)
	$importedData.parseExcel(importSheets: "Formeln",
	headers:{"Formeln": ["A1", "B1", "C1"]});
}

Output:

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

The formulas are evaluated with evaluateFormulas:true and the calculated results are returned.

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

Output:

{
	"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"
	}
	]
}

With evaluateFormulas:false the formulas themselves are returned as text.