Tools for working with Excel in the JADE environment

excel_tools.js

class excel_tools{
  static async array_to_table(sheet_name, address, range_data){
    const start_row = excel_tools.row_from_address(address)
    const start_col = excel_tools.column_from_address(address)
    await Excel.run(async function(excel){
      var sheet = excel.workbook.worksheets.getItemOrNullObject(sheet_name); 
      await excel.sync()
      
      if (sheet.isNullObject) { 
        sheet = excel.workbook.worksheets.add(sheet_name)
      }else{
        sheet.getUsedRange().clear()
      }
      await excel.sync()
      
      sheet.activate()
      const range=sheet.getRangeByIndexes(start_row-1,start_col-1,range_data.length, range_data[0].length)
      range.values=range_data
      range.format.autofitColumns()
      sheet.tables.add(range, true /*hasHeaders*/);
      await excel.sync()
    })
    
  }


  static column_from_address(cell_address){// returns the column nubmer from a cell address
    const str = cell_address.match(/[a-zA-Z]+/)[0].toUpperCase()
    let out = 0, len = str.length;
    for (let pos = 0; pos < len; pos++) {
      out += (str.charCodeAt(pos) - 64) * Math.pow(26, len - pos - 1);
    }
    return out;
  }

  static row_from_address(cell_address){// returns the row nubmer from a cell address
    return cell_address.match(/[0-9]+/)[0]
  }




}