The analyzeSpreadsheet function reads an Excel file from a specified path (or a default location if not provided) and extracts cell values from a specified sheet and range. It returns an array of these cell values, with optional parameters for specifying the sheet name and range.

Run example

npm run import -- "decode xlsx spreadsheet"

decode xlsx spreadsheet

const fs = require('fs')
const path = require('path')
const xlsx = require('xlsx');

const HOMEPATH = process.env.HOME || process.env.HOMEPATH || process.env.USERPROFILE

async function analyzeSpreadsheet(sheetFile, sheetName, rangeString) {

  if(!sheetFile) {
    sheetFile = path.join(HOMEPATH, 'Downloads', 'stable diffusion prompts (2).xlsx')
  }

  const workbook = xlsx.readFile(sheetFile);
  
  if(!sheetName) {
    sheetName = workbook.SheetNames[0]; // Get the name of the first sheet
  }

  console.log(Object.keys(workbook.Sheets))
  const sheet = workbook.Sheets[sheetName];
  
  //const data = xlsx.utils.sheet_to_json(sheet);

  if(!rangeString) {
    rangeString = 'A1-Z999'
  }

  /*
  const range = {
    s: {
      r: parseInt(rangeString.split('-')[0].split(/[^0-9]/gi).join('')),
      c: rangeString.split('-')[0].toUpperCase().split(/[^A-Z]/gi).map(c => c.charCodeAt(0) - 'A'.charCodeAt(0))
        .reduce(function(a, b) { return a + b; }, 0)
    },
    e: {
      r: parseInt(rangeString.split('-')[1].split(/[^0-9]/gi).join('')),
      c: rangeString.split('-')[1].toUpperCase().split(/[^A-Z]/gi).map(c => c.charCodeAt(0) - 'A'.charCodeAt(0))
        .reduce(function(a, b) { return a + b; }, 0)
    }
  }
  */

  const range = {
    s: xlsx.utils.decode_cell(rangeString.split('-')[0]),
    e: xlsx.utils.decode_cell(rangeString.split('-')[1])
  }
  console.log(range)
  //console.log(sheet)
  
  let results = []

  for(var R = range.s.r; R <= range.e.r; ++R) {
    for(var C = range.s.c; C <= range.e.c; ++C) {
      const cellAddress = xlsx.utils.encode_cell({ r: R, c: C });
      const cell = sheet[cellAddress];
      if(cell)
        results.push(cell.v)
    }
  }

  return results
}

module.exports = analyzeSpreadsheet

const path = require('path');
const fs = require('fs');
const process = require('process');

## Third-Party Modules
### Xlsx Module for Excel File Operations

const xlsx = require('xlsx');

## Environment Variables
### HOME or HOMEPATH or USERPROFILE

const HOMEPATH = process.env.HOME || process.env.HOMEPATH || process.env.USERPROFILE;

Code Breakdown

Requires and Constants

The code starts by requiring three modules:

• fs (File System) for interacting with the file system
• path for working with file paths
• xlsx for reading and writing Excel files

It then defines three constants:

• HOMEPATH: the home directory path, which defaults to process.env.HOME or process.env.HOMEPATH or process.env.USERPROFILE

Analyze Spreadsheet Function

The analyzeSpreadsheet function takes three arguments:

• sheetFile: the path to the Excel file to read (optional)
• sheetName: the name of the sheet to read (optional)
• rangeString: the range of cells to read (optional)

Function Flow

1. If sheetFile is not provided, it defaults to a specific file path.
2. The function reads the Excel file using xlsx.readFile.
3. If sheetName is not provided, it defaults to the first sheet in the workbook.
4. It logs the keys of the workbook.Sheets object.
5. It extracts the sheet object from the workbook.
6. If rangeString is not provided, it defaults to a specific range.
7. It extracts the start and end cells from the rangeString using xlsx.utils.decode_cell.
8. It loops through the cells in the specified range and extracts their values.
9. It returns an array of cell values.

Return Value

The function returns an array of cell values in the specified range.

Notes

• The xlsx module is used for reading and writing Excel files.
• The path module is used for working with file paths.
• The fs module is not used in this code snippet.
• The function assumes that the Excel file is in the default file location if not specified.