Custom Excel Export or Import in Client Side using ExcelJS Library

[VivekSattanatha]

ExcelJS is a powerful JavaScript library for working with Excel spreadsheets. It allows developers to read, write, and manipulate Excel files programmatically, using a simple and intuitive API. ExcelJS can be used in a wide range of applications, including web applications, desktop applications, and server-side applications.

When it comes to ServiceNow platform, ExcelJS can be particularly useful for automating data imports and exports. ServiceNow is a powerful platform for IT service management, but managing large volumes of data can be time-consuming and error-prone. By using ExcelJS, developers can build custom integrations that allow data to be seamlessly transferred between ServiceNow and other applications.

For example, a developer might build a custom integration that allows data to be imported from an external Excel spreadsheet into ServiceNow. This could be particularly useful for importing data such as user accounts, inventory lists, or other configuration data. Alternatively, a developer might build an integration that exports ServiceNow data to an Excel spreadsheet, allowing data to be analyzed or shared with other stakeholders.

ExcelJS can also be used to build custom reports and dashboards within ServiceNow. By using ExcelJS to generate dynamic Excel spreadsheets, developers can create sophisticated reports that can be easily shared with other users. For example, a developer might build a custom dashboard that allows users to view real-time data on service requests, incidents, or other IT service management metrics.

Overall, ExcelJS is a powerful tool for developers working with the ServiceNow platform. By using ExcelJS to automate data imports and exports, build custom reports, and create dynamic dashboards, developers can streamline IT service management processes and improve organizational efficiency.

The attached ExcelJS example demonstrates how to export data in JSON format as a ServiceNow UI page.

#ExcelJS #CustomExcel #CustomExcelGeneration #ClientsideExcel

[HugoFirst]

Awesome!  I've been trying to figure out how to export data in Excel via a UI Page for a while now.  Never did figure it out.   But your example worked great the first time.  Thank you for doing the hard work on this and making such a useful example.

[Tapish Sharma1]

Is ExcelJS available OOB ? how do we add it to servicenow instance ?

Can we password protect the excel using ExcelJS?

[VivekSattanatha]

It is not OOB. Please have a look at the sample UI page which I attached in the original post. The above one is using the exceljs npm project, as per their documentation it can be protected by password but i havent explored

https://www.npmjs.com/package/exceljs#sheet-protection

[Tapish Sharma1]

I tried sheet protection but it doesnt work, so was wondering if it is supported anymore or not

[Zdravko]

Nice work! How can I make the library available on the backend? I need to read and process an xlsx attachment.

[Gautham Shenoy]

Hi Vivek,

is there any option to generate the excel from the script.

we have a use case to data massage from a table and convert to excel and attach to a records, we have a ability to do it in csv format but i was wondering if we can use ExcelJS for this, the alternative solution what we are doing is to hold the data in an temp table and call a rest call to the same instance and convert to excel oob.

Please let me know your thoughts on this.

[VivekSattanatha]

@Gautham Shenoy

The excel.js library can be seamlessly injected into the browser environment, allowing us to dynamically generate Excel files on demand. This means that when a user clicks a designated button to generate an Excel file and attach it to a particular record, we can initiate a UI page to facilitate this process.

1. User Interaction: When a user triggers the Excel generation process by clicking a designated button within our application.

2. UI Page Initiation: We can call a user interface (UI) page designed to handle Excel generation and attachment to records.

3. Excel Generation: Within the UI page, the excel.js library will come into play. It will dynamically generate the Excel file based on the user's input or preferences.

4. Attachment to Records: Once the Excel file is generated, it will be seamlessly attached to the relevant records

[Zdravko]

@Gautham Shenoy 

I actually downloaded the src file from Vivek's example and shoved it into a UI Script. Then I was able to address it as src in a UI page:

Then it is accessible in the UI page Client script:

Also, the same UI Script can be added as a portal widget dependency, which allows you to use the library directly in the widget Client script:

If you then need to process things on the backend, Server scripts and/or GlideAjax are your friends. Hope that helps 🙂

[VivekSattanatha]

@Zdravko Nice enhancement!

[Zdravko]

@VivekSattanatha Thanks, my main reasoning behind this was to keep the ExcelJS functionality available on the instance, in case someday the creators decide to discontinue it.

[kedler]

This has been very helpful and I have tried using it in my PDI. I am interested in using this code but I would like to execute it in a scheduled job. Is that possible to use it in server side code? Ideally I would have a scheduled job that executes code and emails out custom spreadsheet created using ExcelJS. A scheduled report would not work because I want to add spreadsheet columns and values that are not stored in ServiceNow tables. Any ideas on how to approach that? @VivekSattanatha @Zdravko If that isnt possible, what other ways can this be used in ServiceNow besides a UI page? Thanks, Karl

[VivekSattanatha]

What I provided doesnt work on Server side, because we are just loading the library on the browser. If it has to work on the server side then the library needs to created in Script include. But we cannot directly use the exceljs library into script include, because it was written in plain javascript which needs to adapted as per Servicenow standard.

[Zdravko]

@kedler , you can try this :

https://www.servicenow.com/community/developer-blog/mini-lab-adding-underscore-js-into-servicenow/ba...

Just paste the library code in a script include and then call it anywhere in a serverside script with gs.include(). I'll try it in my PDI when I have the time, but I don't see any reason why it wouldn't work.

[VivekSattanatha]

Earlier I tried though but it didnt work because those library written in some new javascript version which Servicenow doesnt work well with it. But in new Servicenow release it supports ES12 version so maybe a worth a try.

[Zdravko]

@VivekSattanatha Maybe try in scoped app, that's where ES6 and beyond should work. Alternatively, running the code through am ES5 transpiler should also do the trick.

[Community Alums]

@VivekSattanatha Thanks for this post, I stumbled across it at the weekend and it's helped me immensely!

I've taken it one step further by utilising a template XLSX file attached to the UI page - the ACLs should be set up so that the users running the report can read the attachment - and the following snippet to load in the template and work with it:

var request = new XMLHttpRequest();
        request.open("GET", templateUrl, true);
        request.responseType = "blob";
        request.onload = function () {
            var reader = new FileReader();
            reader.readAsArrayBuffer(request.response);
            reader.onload = function (event) {
                var arrayBuffer = event.target.result;
                var workbook = new ExcelJS.Workbook();
                workbook.xlsx.load(arrayBuffer).then(function () {
                  // do stuff with the workbook here
                    });
                });
            };
        };
       request.send();

[soundhar R]

@VivekSattanatha  - I have the same requirement. Need to generate an .xlsx file and need to show the file in front end record producer. Can you please suggest how can we achieve.

[VivekSattanatha]

You can achieve the same using Service Portal widget, I havent tried but I am sure it will work. Basically create a variable which should link the widget, that widget can have a button or link to generate the excel sheet you want.

https://www.servicenow.com/docs/bundle/yokohama-platform-user-interface/page/build/service-portal/ta...

[soundhar R]

@VivekSattanatha  - I will try above above method and let you know. Thank you so much!!

[ComradeJon]

Hey @Zdravko  @VivekSattanatha BIG thank you for this post! 

I have tested it and it runs perfectly when calling the library externally. However, due to security I tried to create a UI Script and paste the entire script from the exceljs.min.js file, and I am getting a corrupted excel file.

Do you guys have an idea why this can be? 

many thanks again!

[ComradeJon]

UPDATE ON MY ISSUE

I tried to use this library via UI Script, because my instance is blocking external URLs. However, when copying & pasting the entire exceljs.min.js script from the source and call it in a UI Page or the Portal widget, it generates a corrupted file. 

The issue was that ExcelJS 4.4.0 version had some unsupported things for ServiceNow, and I had to use the ExcelJS 4.3.0 version which solved my problem! I really hope this comment helps someone trying to do the same thing as I did.

Steps to implement this:

1. Download the amazing XML file provided by @VivekSattanatha 
2. Import it in your ServiceNow PDI 
3. Create a new UI Script type Desktop, check the Global checkbox
4. Copy the entire script from ExcelJS 4.3.0 version and paste it in your UI Script
5. Replace the 3rd line in the ExportExcel_exceljs  UI Page that calls the library externally with  <g:require name="YOUR_UI_SCRIPT_NAME.jsdbx" />
6. Try It

If you wish to do the same thing on the portal, you can do that by creating a dependency of  your UI Script, and call it in your Widget Client Script 
spUtil.get("your_ui_action_name").then(function() {

c.generateExcel = function() {

// The script from generateExcel() function

}

});

Hope this helps anyone!

[OmkarC]

Very Informative article. Thanks!