Skip to main content

EXCEL Analyzer

EXCEL spreadsheet analysis. By specifying the sheet name, data starting cell, and header columns, the loaded data is normalized into a consistent data structure (input array) as a Low-Code data analysis source, with results written to working folder documents.

Parameters​

EXCEL - EXCEL input file. Click "PICK" to select a file, type the EXCEL filename from the working folder, or use the %FILENAME% variable.

SHEET - EXCEL input sheet name. Leave empty to use the first sheet in the file.

DATA CELL - The starting (upper-left) cell of the table data in A1 notation, not including the header.

HEADER - A naming array for the input table's header columns. There are two JSON array formats. The first is a simple one-to-one string array, where the first string in the array corresponds to the first header column of the table, the second string corresponds to the second header column, with no columns skipped in between, and so on:

["Column A header name", "Column B header name", ...]

The second is a more flexible object array, using the regular expression regexp, offset value, A1 notation, and other properties in the object to locate header columns, with the name property value used as the located header column name:

[
  {
    "regexp": "品名$",  // Find the cell above the data starting point whose value ends with "品名"
    "name": "product"  // Use the found cell as the header column, named "product"
  },
  {
    "regexp": "品名$",  // Find the cell above the data starting point whose value ends with "品名"
    "offset": 1, // Shift one column to the right
    "name": "price"  // Use the found cell as the header column, named "price"
  },
  {
    "A1": "C",  // Find column C (in A1 notation)
    "name": "stock"  // Use the found cell as the header column, named "stock"
  }
]

No-Code Editor​

Provides No-Code commands such as: "Find By", "Max By", and "Min By". For example, find the data row in the specified table where the source column contains google, and write each column of that row to working folder documents.

Low-Code Editor​

input Array​

Each object element in the input array represents a row of data from the input table, where the key is the header column and the value is that column's value in this row.

// Print specified columns for each row of the input table
input.forEach((row, index) => {
  console.log(index + '. product:' + row.name + ', price:' + row.price)
})

output Object​

Each key added to the output object will be exported as a TXT file in the working folder, with the filename being the key and the text content being the corresponding value.

// Write analysis results to total.txt in the working folder
let total = 0
input.forEach((row) => total = total + row.price)
output['total'] = total

// You can also use the lodash library to calculate the average
output['average'] = _.meanBy(input, 'price')