taskpane.full.js

/*
 * Copyright (c) Microsoft Corporation. All rights reserved. Licensed under the MIT license.
 * See LICENSE in the project root for license information.
 */

// images references in the manifest
import "../../assets/icon-16.png";
import "../../assets/icon-32.png";
import "../../assets/icon-80.png";

/* global console, document, Excel, Office */

Office.onReady(info => {
  if (info.host === Office.HostType.Excel) {
    document.getElementById("sideload-msg").style.display = "none";
    document.getElementById("app-body").style.display = "flex";
    //document.getElementById("run").onclick = run;
    document.getElementById("capture").onclick = captureRange;
    document.getElementById("predict").onclick = predictRange;
  }
});

import Classifier from 'ml-classify-text'
let processedData = []
let classifier = {}

export async function captureRange() {
  try {
    await Excel.run(async context => {
      let book = context.workbook
      let selectedRange = book.getSelectedRange()
      let usedRange = book.worksheets
        .getActiveWorksheet()
        .getUsedRange();
      selectedRange.load("address");
      usedRange.load("address");
      await context.sync();

      console.log(selectedRange.address)
      console.log(usedRange.address)

      let range = resolveRange(selectedRange.address, usedRange.address)
      console.log(range)

      range = book.worksheets
        .getActiveWorksheet()
        .getRange(range);

      range.load("values");
      await context.sync();

      processedData = processData(range.values);
      console.log(`Captured data! ${JSON.stringify(processedData)}`)
    })

  } catch (error) {
    console.error(error)
  }
}

export async function predictRange() {
  try {
    await Excel.run(async context => {
      trainModel(processedData)

      let range = context.workbook.getSelectedRange()
      //range.load("address");
      range.load("values");
      await context.sync();

      let data = range.values
      // Predict
      let predicted = []
      for (let [text, prediction] of data) {
        let predictions = classifier.predict(text);
        if (predictions.length) {
          prediction = predictions[0].label
        }
        predicted.push([text, prediction]);
      }

      range.values = predicted
      await context.sync()
    })
  } catch (error) {
    console.error(error)
  }
}

function trainModel(data) {
  classifier = new Classifier({
    nGramMin: 1,
    nGramMax: 3,
  });
  //classifier = new natural.BayesClassifier();

  for (let key in data) {
    classifier.train(data[key], key);
    //classifier.addDocument(data[key], key);
  }

  //classifier.train();
}

function hasNumber(str) {
  return str.match(/\d+/g);
}

function resolveRange(selectedAddress, usedAddress) {

  let [sLeft, sRight] = selectedAddress.split('!')[1].split(':')
  let [uLeft, uRight] = usedAddress.split('!')[1].split(':')
  if (hasNumber(sRight)) {
    return `${sLeft}:${sRight}`
  } else {
    let lNum = hasNumber(uLeft)
    let rNum = hasNumber(uRight)
    return `${sLeft}${lNum}:${sRight}${rNum}`
  }
}

function processData(arrayData) {
  let processed = {};

  for (let [i, [text, label]] of arrayData.entries()) {
    // TODO: Deal with headers
    //if (i == 0 && this.excludeFirstRow) continue;
    // Do not add empty rows
    if (text.trim() == '' || label.trim() == '') continue;

    //console.log(`Processing: ${text}, ${label}`);
    if (label in processed) {
      processed[label].push(text);
    } else {
      processed[label] = [text];
    }
  }

  return processed;
}