Created
February 6, 2021 22:39
-
-
Save ismaelc/ee45b53e4b0ee84bb669339133966da9 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
* 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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment