16 Replies · Latest reply on May 30, 2017 11:58 AM by davidd.

    Parse Excel (xlsx) file.. with ease!

      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.

        • Re: Parse Excel (xlsx) file.. with ease!

          This is what we need too.  Tried your code but areas don't populate.  does this work in Helsinki?

          • Re: Parse Excel (xlsx) file.. with ease!
            Arnab Dash

            Hi David,

             

            Great work!!

            How did you move your parsed data from ui page to the form, you have displayed in the screenshot?

             

            Thanks,

            Arnab

              • Re: Parse Excel (xlsx) file.. with ease!
                davidd.

                Sure, no problem.  I'll share my code.

                 

                First, let me share what my actual process_wb() function looks like:

                function _closeThisUIPage()
                {
                    GlideDialogWindow.get().destroy();
                }
                
                
                function process_wb(wb)
                {
                    var output = "";
                    output = JSON.stringify(to_json(wb), 2, 2);
                    
                    var userInfoArray = to_json(wb);
                    
                    if (userInfoArray['Sheet 1'])
                    {
                        for (var i = 0; i < userInfoArray['Sheet 1'].length; i++)
                        {
                            add_user_field();  // This function is defined in the Client Script, "ACA onLoad stuff"
                            
                            // The variable, idNum, is defined in the Client Script, "ACA onLoad stuff"
                
                
                            var _n_ = document.getElementById('input_u_name' + idNum);
                            var _r_ = document.getElementById('input_u_role' + idNum);
                            var _e_ = document.getElementById('input_u_email' + idNum);
                            var _i_ = document.getElementById('input_u_amt_id' + idNum);
                            var _d_ = document.getElementById('input_u_days_of_access' + idNum);
                
                
                            if (_n_)
                                _n_.value = userInfoArray['Sheet 1'][i]['Name of user'];
                            if (_r_)
                                _r_.value = userInfoArray['Sheet 1'][i]['Role'];
                            if (_e_)
                                _e_.value = userInfoArray['Sheet 1'][i]['Email'];
                            
                            if (_i_)
                            {
                                if (userInfoArray['Sheet 1'][i]['AMT ID'] == "Has an Existing ID")
                                    _i_.options.selectedIndex = 1;
                                else if (userInfoArray['Sheet 1'][i]['AMT ID'] == "Needs ID (new user)")
                                    _i_.options.selectedIndex = 2;
                                else if (userInfoArray['Sheet 1'][i]['AMT ID'] == "(Unknown)")
                                    _i_.options.selectedIndex = 3;
                                else
                                    _i_.options.selectedIndex = 0;
                            }
                            
                            if (_d_)
                            {
                                var _intVal = parseInt(userInfoArray['Sheet 1'][i]['Days access is needed (Default 90)'], 10);
                                if (_intVal > 1)
                                    _d_.options.selectedIndex = (_intVal - 1);
                                else
                                    _d_.options.selectedIndex = (89);
                            }
                        }
                    }
                    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;
                    
                    if (typeof console !== 'undefined')
                        console.debug("output", new Date());
                    
                    _closeThisUIPage();
                }
                
                
                
                
                
                
                
                
                

                 

                 

                Notice line #18, calling the add_user_field() function.  That's a function I have on the underlying form's onLoad script.  Here's all the code for that to work.

                 

                var idNum = 0;
                var howManyUsersAdded = 0;
                var __maxNewUsers__ = 100;
                
                
                function show_add_additional_users_button()
                {
                    var form = document.getElementsByTagName("form")[0];
                
                
                    if (form)
                    {
                        // Create and add the button
                        var addUserButton = document.createElement('button');
                        addUserButton.onclick = function() { add_user_field(); return false; };
                        addUserButton.textContent = "Add Additional User(s) Manually";
                        addUserButton.id = "__ADD_USER_BUTTON__";
                        form.appendChild(addUserButton);
                
                
                        var addUserButtonByExcel = document.createElement('button');
                        addUserButtonByExcel.onclick = function() { add_users_by_excel(); return false; };
                        addUserButtonByExcel.textContent = "Import Additional Users from Excel Template";
                        addUserButtonByExcel.id = "__ADD_USER_BUTTON_BY_EXCEL__";
                        form.appendChild(addUserButtonByExcel);
                
                
                        // Create and add the table
                        var addUserTable = document.createElement('table');
                        addUserTable.id = "_addUserTable_";
                        if (isIE())
                            addUserTable.style.cssText = "margin-top: 20px;";
                        else
                            addUserTable.style = "margin-top: 20px;";
                
                
                        form.appendChild(addUserTable);
                    }
                }
                
                
                function isIE()
                {
                    var isIE11 = navigator.userAgent.indexOf(".NET CLR") > -1;
                    var isIE11orLess = isIE11 || navigator.appVersion.indexOf("MSIE") != -1;
                    return isIE11orLess;
                }
                
                
                function rename_users_count()
                {
                    var divs = document.getElementsByTagName('DIV');
                    var count = 0;
                    
                    for (var i = 0; i < divs.length; i++)
                    {
                        if (divs[i].id && divs[i].id.indexOf("gDiv_") == 0)
                        {
                            count++;
                            divs[i].innerHTML = count + " of " + __maxNewUsers__;
                        }
                    }
                }
                
                
                function add_user_field()
                {   
                    if (howManyUsersAdded >= __maxNewUsers__)
                    {
                        alert("You can only add " + __maxNewUsers__ + " users at a time.");
                        return false;
                    }
                
                
                    idNum++;
                
                
                    var moreUsersTable = document.getElementById('_addUserTable_');
                    
                    if (!moreUsersTable)
                        return false;
                    
                    var genericDiv = document.createElement('div');
                    genericDiv.id = "gDiv_" + idNum;
                
                
                    var e_header_tr = document.createElement('tr');
                    var e_header_td_x = document.createElement('td');
                    var e_header_td_contact_name = document.createElement('td');
                    var e_header_td_role = document.createElement('td');
                    var e_header_td_email = document.createElement('td');
                    var e_header_td_amtid = document.createElement('td');
                    var e_header_td_days = document.createElement('td');
                
                
                    if (idNum == 0 || howManyUsersAdded <= 0)
                    {
                        e_header_td_x.textContent = "";
                
                
                        if (isIE())
                        {
                            e_header_td_contact_name.style.cssText = "text-align: center;";
                            e_header_td_role.style.cssText = "text-align: center; padding-left:15px;";
                            e_header_td_email.style.cssText = "text-align: center; padding-left:15px;";
                            e_header_td_amtid.style.cssText = "text-align: center; padding-left:15px;";
                            e_header_td_days.style.cssText = "text-align: center; padding-left:15px;";
                        }
                        else
                        {
                            e_header_td_contact_name.style = "text-align: center;";
                            e_header_td_role.style = "text-align: center; padding-left:15px;";
                            e_header_td_email.style = "text-align: center; padding-left:15px;";
                            e_header_td_amtid.style = "text-align: center; padding-left:15px;";
                            e_header_td_days.style = "text-align: center; padding-left:15px;";
                        }
                
                
                        e_header_td_contact_name.textContent = "User's Name";
                        e_header_td_role.textContent = "Role";
                        e_header_td_email.textContent = "Email";
                        e_header_td_amtid.textContent = "AMT ID";
                        e_header_td_days.textContent = "Days access is needed";
                        
                        e_header_tr.appendChild(e_header_td_x);
                        e_header_tr.appendChild(e_header_td_contact_name);
                        e_header_tr.appendChild(e_header_td_role);
                        e_header_tr.appendChild(e_header_td_email);
                        e_header_tr.appendChild(e_header_td_amtid);
                        e_header_tr.appendChild(e_header_td_days);
                
                
                        e_header_tr.id = "addUsers_headerTableRow";
                
                
                        moreUsersTable.appendChild(e_header_tr);
                    }
                
                
                    var remove_user;
                    var contact_name_field;
                    var role_field;
                    var email_field;
                    var amtid_field;
                    var days_field;
                    
                    remove_user = document.createElement('IMG');
                
                
                    if (isIE())
                        remove_user.setAttribute("type", "image");
                    else
                        remove_user.type = "image";
                
                
                    remove_user.id = idNum;
                    remove_user.src = "20x45_redX.pngx";
                    remove_user.style.cssText = "cursor:pointer;";
                
                
                    remove_user.onclick = function remove_file_field()
                    {
                        var z = this.getAttribute("id");
                        var x_button_tr = document.getElementById('addUsers_tableRow_' + z);
                
                
                        x_button_tr.parentNode.removeChild(x_button_tr);
                        rename_users_count();
                
                
                        howManyUsersAdded--;
                
                
                        if (howManyUsersAdded <= 0)
                        {
                            var x__tr = document.getElementById('addUsers_headerTableRow');
                            try
                            {
                                x__tr.parentNode.removeChild(x__tr);
                            } catch (e)
                            {
                                alert("Sorry, there was a web page error: " + e);
                            }
                        }
                        return false;
                    };
                
                
                    var plus_tr = document.createElement('tr');
                    plus_tr.id = "addUsers_tableRow_" + idNum;
                    
                    var x_td = document.createElement('td');
                    
                    var contact_name_td = document.createElement('td');
                    var role_td = document.createElement('td');
                    var email_td = document.createElement('td');
                    var amtid_td = document.createElement('td');
                    var days_td = document.createElement('td');
                    
                    if (isIE())
                    {
                        contact_name_td.style.cssText = "vertical-align:top;";
                        role_td.style.cssText = "padding-left:15px;vertical-align:top;";
                        email_td.style.cssText = "padding-left:15px;vertical-align:top;";
                        amtid_td.style.cssText = "padding-left:15px;vertical-align:top;";
                        days_td.style.cssText = "padding-left:15px;vertical-align:top;";
                        x_td.style.cssText = "height: 45px";
                    }
                    else
                    {
                        contact_name_td.style = "vertical-align:top;";
                        role_td.style = "padding-left:15px;vertical-align:top;";
                        email_td.style = "padding-left:15px;vertical-align:top;";
                        amtid_td.style = "padding-left:15px;vertical-align:top;";
                        days_td.style = "padding-left:15px;vertical-align:top;";
                        x_td.style = "height: 45px";
                    }
                
                
                    contact_name_field = document.createElement('input');
                    contact_name_field.type = 'text';
                    contact_name_field.id = "input_u_name" + idNum;
                    contact_name_field.placeholder = "[Name]";
                    contact_name_field.size = "20";
                    contact_name_field.maxLength = "80";
                    contact_name_field.className = "form-control";
                
                
                    amtid_field = addUser_build_amtid_select_fields(idNum);
                    days_field = addUser_build_days_select_fields(idNum);
                
                
                    role_field = document.createElement('input');
                    role_field.type = 'text';
                    role_field.id = "input_u_role" + idNum;
                    role_field.placeholder = "[Role]";
                    role_field.size = "15";
                    role_field.maxLength = "80";
                    role_field.className = "form-control";
                
                
                    email_field = document.createElement('input');
                    email_field.type = 'text';
                    email_field.id = "input_u_email" + idNum;
                    email_field.placeholder = "[Email]";
                    email_field.size = "25";
                    email_field.maxLength = "80";
                    email_field.className = "form-control";
                
                
                    contact_name_td.appendChild(contact_name_field);
                    role_td.appendChild(role_field);
                    email_td.appendChild(email_field);
                    amtid_td.appendChild(amtid_field);
                    days_td.appendChild(days_field);
                    
                    x_td.appendChild(remove_user);
                    x_td.appendChild(genericDiv);
                    
                    plus_tr.appendChild(x_td);
                    plus_tr.appendChild(contact_name_td);
                    plus_tr.appendChild(role_td);
                    plus_tr.appendChild(email_td);
                    plus_tr.appendChild(amtid_td);
                    plus_tr.appendChild(days_td);
                
                
                    moreUsersTable.appendChild(plus_tr);
                    rename_users_count();
                    howManyUsersAdded++;
                }
                
                
                function addUser_build_days_select_fields(idNumber)
                {
                    var daysOption = null;
                    var days_field = document.createElement('select');
                    days_field.id = "input_u_days_of_access" + idNumber;
                    days_field.className = "form-control";
                
                
                    var i = 1;
                    while (i <= 90)
                    {
                        // For safety
                        if (i > 90)
                            break;
                        
                        daysOption = document.createElement("option");
                        daysOption.value = i;
                        daysOption.text = i;
                        
                        days_field.appendChild(daysOption);
                        i++;
                    }
                    
                    days_field.options.selectedIndex = (i-2);
                
                
                    return days_field;
                }
                
                
                function addUser_build_amtid_select_fields(idNumber)
                {
                    var amtidOption = null;
                    var amtid_field = document.createElement('select');
                    amtid_field.id = "input_u_amt_id" + idNumber;
                    amtid_field.className = "form-control";
                
                
                    amtidOption = document.createElement("option");
                    amtidOption.value = '';
                    amtidOption.text = "-- None --";
                    amtid_field.appendChild(amtidOption);
                    
                    amtidOption = document.createElement("option");
                    amtidOption.value = 'existing';
                    amtidOption.text = "Has an Existing ID";
                    amtid_field.appendChild(amtidOption);
                    
                    amtidOption = document.createElement("option");
                    amtidOption.value = 'new';
                    amtidOption.text = "Needs ID (new user)";
                    amtid_field.appendChild(amtidOption);
                    
                    amtidOption = document.createElement("option");
                    amtidOption.value = 'unknown';
                    amtidOption.text = "(Unknown)";
                    amtid_field.appendChild(amtidOption);
                
                
                    return amtid_field;
                }
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                
                1 of 1 people found this helpful
              • Re: Parse Excel (xlsx) file.. with ease!
                Derek Lenberg

                David,

                 

                Nice work!  I'm looking to do something very similar, but would like to push the data retrieved to a data source record and automatically kick the an import set job.  The ability to parse the Excel sheet up front is a huge bonus, to both validate the sheet prior to attempt to load, and give the user feedback on the sampling of data before hitting the submit button.

                 

                Out of curiosity, I noticed that some of the UI Scripts may be on the server side already.  (e.g. shim.js)  Furthermore, I noticed that some of the AngularJS counterparts are also included available on the server side (Developing on a Helsinki instance).  I'm no AngularJS expert by a long shot, mainly an expert on the server side and been doing basic client JS scripts along with Jelly because I know server side processing is better for speed, especially considering people on IE or low bandwidth connections.)  Would it be easy to translate all this stuff over to an AngularJS based UI Page?  (I did one at Knowledge16, albeit, it seemed like extra work and may be overkill for the client I'm on.)

                 

                Thanks!

                -Derek

                  • Re: Parse Excel (xlsx) file.. with ease!
                    davidd.

                    Yes, it would be easy to translate.  Since this post, I've almost exclusively been coding widgets in the Service Portal, and have converted the parsing excel functionality into it's own widget which works quite well.  I've also updated the code I pasted above to work much more efficiently and dynamically, cutting out nearly 75% of the lines!

                     

                    Thanks for the reply and happy coding

                    1 of 1 people found this helpful
                      • Re: Parse Excel (xlsx) file.. with ease!
                        Derek Lenberg

                        David, That sounds really cool!  Yeah, that was one thing I started to notice as I drilled into the code was that there is a ton of javascript client side code in those JS libraries, that I was looking to cut out what I deemed unnecessary.  (I'm not trying to write an Excel file, just read it! )  In any case, I got it working in a single UI page record, including uploading the attachment into the data source record, and I used a processing script to kick the job off.  Works beautifully!  Kiss the MID server goodbye from the old solution!  So, did you end up just wrapping the Excel JS libraries as directives in your AngularJS code?  Just trying to push myself to keep learning AngularJS.  => Python (check), AngularJS => (getting there!)  Thanks for your insight and inspiration!

                    • Re: Parse Excel (xlsx) file.. with ease!
                      Julien Remy

                      Hello David,

                      Your post pretty much sums up everything I have been trying to do for the past 3 days. Yay to that!

                      So as you might imagine, I've been thrilled when reading through this.
                      I tried downloading the 4 scripts to start playing around and the ODS one is missing: the raw gives a 404 and the gh gives a page not found.

                      Would you still have a copy, or another link, or something I could use?

                      Thank you very much in advance

                      • Re: Parse Excel (xlsx) file.. with ease!
                        Pawan Kumar

                        Hi David,

                         

                        I have pretty much same requirement but I am not able to download the UI scripts from the links in the post. Has it been moved to some other location ?

                         

                         

                        Thanks,

                        Pawan

                        • Re: Parse Excel (xlsx) file.. with ease!
                          Astha Chaubey

                          Hi David,

                           

                          First of all need to thank you as your post helped me a lot with one of the implementations. My customer was quite curious to know about the 4 scripts that are provided, they have a licence or copyright associated?

                           

                          Thanks & Regards,

                          Astha Chaubey