EXCEL Analyzer

EXCEL table analysis

Process an Excel spreadsheet and output it as a CSV or EXCEL file. Load and normalize data by using specified table names, data start cells, and header fields to present a consistent data structure for the input array. The output is written to the text files in the workspace.

EXCEL

Click the "PICK" button manually enter the Excel file name in the workspace, or use the %FILENAME% variable to select an input Excel file.

SHEET

The Excel input sheet name. Leaving it empty represents the first sheet in the file.

DATA CELL

The starting cell (top-left) of table data, using the A1 notation, and excluding the header.

HEADER

The input for table header field names can be represented in two JSON array notations. The first one is a simple one-to-one string array, where the first string in the array corresponds to the first table header field, the second string corresponds to the second table header field, and so on. You cannot skip in between, like this:

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

The second JSON array notation is more flexible. The notation uses properties like regular expressions (regexp), offsets (offset), A1 notation, and other attributes to locate the table header fields. The name property value within these objects is used as the name for the located table header field:

[
  {
    "regexp": "Product Name$", // Locate the cell above the starting cell of the table that ends with "Product Name" in the value
    "name": "product"  // Use the cell found as the header and name it "product"
  },
  {
    "regexp": "Product Name$", // Locate the cell above the starting cell of the table that ends with "Product Name" in the value
    "offset": 1, // Shift one cell to the right
    "name": "price"  // Use the cell found as the header and name it "price" 
  },
  {
    "A1": "C",  // Locate column C (using A1 notation)
    "name": "stock"  // Locate column C (using A1 notation)
  }
] 

OUTPUT

Output file format options: .csv or .xlsx.

Input Array

Each object element in the input array represents a row of data in the input table. The object's key represents one of the table headers, and the value represents the value of that header in this row of data.

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

Output Object

Each key added to the output object will be output as a .txt file in the workspace. The file name will be the key, and the text content will be the corresponding value.

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

// Lodash.js library can also be used to calculate the average value
output['average'] = _.meanBy(input, 'price')

--

We are dedicated to improving our content. Please let us know if you come across any errors, including spelling, grammar, or other mistakes, as your feedback is valuable to us! 🤖️⚡️