So, I'll take the cookies out of the cookie jar that sits on the top shelf, and lay them out on the table ready for consumption.
First, you'll need four (4) files:
File: xlsx.js
RAW: https://raw.githubusercontent.com/SheetJS/js-xlsx/master/xlsx.js
GIT: https://github.com/SheetJS/js-xlsx/blob/master/xlsx.js
File: shim.js
RAW: https://raw.githubusercontent.com/SheetJS/js-xlsx/master/shim.js
GIT: https://github.com/SheetJS/js-xlsx/blob/master/shim.js
File: ods.js
RAW: https://raw.githubusercontent.com/SheetJS/js-xlsx/master/ods.js
GIT: https://github.com/SheetJS/js-xlsx/blob/master/ods.js
File: jszip.js
RAW: https://raw.githubusercontent.com/SheetJS/js-xlsx/master/jszip.js
GIT: https://github.com/SheetJS/js-xlsx/blob/master/jszip.js
You'll create 4 separate UI Scripts for each file:
Great. Now, create a UI Page that you'll call from where ever you'd like. The UI Page will look like this:
HTML (xml):
<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="false" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<g:include_script src="shim.jsdbx" />
<g:include_script src="jszip.jsdbx" />
<g:include_script src="xlsx.jsdbx" />
<g:include_script src="ods.jsdbx" />
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
</head>
<body>
<p id="TemplateLink"></p>
<p><input type="file" name="xlfile" id="xlf" /></p>
<br />
<pre id="out"></pre>
<br />
</body>
</j:jelly>
Client Script:
var X = XLSX;
function fixdata(data)
{
var o = "";
var l = 0;
var w = 10240;
for (; l < data.byteLength / w; ++l)
{
o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w, l * w + w)));
}
o += String.fromCharCode.apply(null, new Uint8Array(data.slice(l * w)));
return o;
}
function to_json(workbook)
{
var result = {};
workbook.SheetNames.forEach(function(sheetName) {
var roa = X.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
if(roa.length > 0)
result[sheetName] = roa;
});
return result;
}
function process_wb(wb)
{
var output = "";
output = JSON.stringify(to_json(wb), 2, 2);
var excelData_array = to_json(wb);
if (excelData_array['Sheet 1'])
{
for (var i = 0; i < excelData_array['Sheet 1'].length; i++)
{
var column_1 = excelData_array['Sheet 1'][i]['Name of user'];
var column_2 = excelData_array['Sheet 1'][i]['Role'];
var column_3 = excelData_array['Sheet 1'][i]['Email'];
}
}
else
alert("Cannot determine the contents of the file specified.\rEither there were no users listed in the columns,\ror the file is not valid.\r\nError [07072016]");
if (out.innerText === undefined)
out.textContent = output;
else
out.innerText = output;
}
function handleFile(e)
{
var files = e.target.files;
var f = files[0];
{
var reader = new FileReader();
var name = f.name;
reader.onload = function(e) {
var data = e.target.result;
var arr = fixdata(data);
var wb = X.read(btoa(arr), {type: 'base64'});
process_wb(wb);
};
reader.readAsArrayBuffer(f);
}
}
var xlf = document.getElementById('xlf');
if (xlf && xlf.addEventListener)
xlf.addEventListener('change', handleFile, false);
Please take note of the process_wb(wb) function above! This is where you'll need to edit it for your own purposes.
You can simply output the stringified JSON data, or you can use the data as I have done as pictured above.
For example, my Excel document looks like this:
Now, there's a lot more I'm doing with this data but I'm sure you're not interested. The point of this post is to get you 99% there to parsing Excel documents in ServiceNow and then using the retrieved data however you see fit.
For what it's worth, here's how I'm using mine:
... and the result after I choose my file:
I certainly hope someone finds this useful.
Well, it could be a couple of things. First, how many tabs do you have in your document? This has only been tested with 1 tab, and the name of the tab must match your code.
For example:
In the process_wb() function, notice this: if (excelData_array['Sheet 1'])
Those must match. And this has been tested in Geneva, not Helsinki but I don't believe there would be any issues since it's pure code and not interacting with any of the back-end functions.
