Code: Select all
Cannot find module '/Users/switchserver/Library/Application Support/Enfocus/SwitchProcessorService/cache/e70949d77e9b490bdf68243cd0705bfe9cfa42ead1d769669b7ce7fa7b7a80ad/1629987785/node_modules/xlsx' Require stack: - /Applications/Enfocus/Enfocus Switch/ScriptExecutor/NodeScriptExecutor.js
Code: Select all
var xlsx = require("xlsx");
// read excel file
var wb = xlsx.readFile("EFI_Salesperson Commissions Report.xlsx");
// read sheet in excel file
var ws = wb.Sheets["EFI_Salesperson Commissions Rep"];
// convert data to json array
var data = xlsx.utils.sheet_to_json(ws);
async function jobArrived( s, flowElement, job ){
//get data from job
let jobName = await job.getName(includeExtension=false);
}
//rearranges columns in excel to the end of the file
// 2nd record is simply to rename to the original column name
//deletes columns no longer necessary in the file
var newData = data.map(function(record){
record.nSalesRep = record.Query_Grouping;
delete record.Query_Grouping
record.SalesRep = record.nSalesRep;
delete record.nSalesRep;
record.nAccount = record["Query_Account Name"];
delete record["Query_Account Name"];
record.Account = record.nAccount;
delete record.nAccount;
record.nQuery_InvoiceNumber = record.Query_InvoiceNumber;
delete record.Query_InvoiceNumber;
record.InvoiceNumber = record.nQuery_InvoiceNumber;
delete record.nQuery_InvoiceNumber;
record.nSubtotal = record.Query_Subtotal;
delete record.Query_Subtotal;
record.Subtotal = record.nSubtotal;
delete record.nSubtotal;
record.nShipping = record.Query_Cat19;
delete record.Query_Cat19;
record.Shipping = record.nShipping;
delete record.nShipping;
record.nPostage = record.Query_Cat17;
delete record.Query_Cat17;
record.Postage = record.nPostage;
delete record.nPostage;
record.nTotalInvoice = record.Subtotal - record.Shipping - record.Postage;
delete record.InvoiceTotalRep;
record.TotalInvoice = record.nTotalInvoice;
delete record.nTotalInvoice;
// deletes unused columns
delete record.Title;
delete record.DateRange;
delete record.PrintDate;
delete record.Query_CatName17;
delete record.Query_PickupDate;
delete record.Query_Cat18;
delete record.SubtotalRep;
delete record.Cat18Rep;
delete record.Cat19Rep;
delete record.Cat17Rep;
delete record["Subtotal-postage"];
return record;
});
//writes data to a new sheet in a new workbook
var newWB = xlsx.utils.book_new();
var newWS = xlsx.utils.json_to_sheet(newData);
xlsx.utils.book_append_sheet(newWB,newWS,"Revised Data");
xlsx.writeFile(newWB,"new data file.xlsx");