Exporting .xlsx files with multiple worksheets

[Matty3]

My current requirement is to create a UI Action that queries a table and attaches the results to a form as an .xlsx attachment. I've seen a few suggestions on the forum for creating a spreadsheet from a query, but I couldn't find any questions about multiple worksheets. So, I guess I'm really asking two separate questions.

Is there a recommended way to create an .xlsx from a query?

Is it possible to create an .xlsx with two or more worksheets?

Thank you!

[Paul Deonarine3]

Yes, there are a few ways to create an .xlsx file from a query in ServiceNow. One approach is to use the Excel Export API, which provides a convenient way to export data from a table to an Excel file. Another approach is to use a third-party library, such as SheetJS or Apache POI, to generate the Excel file.

To create an .xlsx file with multiple worksheets, you can use the Excel Export API and specify multiple sheets in the export parameters. Here's an example UI Action script that queries the Incident table and exports the results to a two-sheet Excel file:

function exportToExcel() {
  var gr = new GlideRecord('incident');
  gr.addQuery('active', true);
  gr.query();

  var export = new sn_export_data.ExcelExport();
  export.addSheet(gr, 'Sheet1');
  export.addSheet(gr, 'Sheet2');

  var data = export.exportToString();
  var filename = 'incidents.xlsx';
  var content_type = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet';

  var attachment = new global.GlideSysAttachment().write('incident', gr.getUniqueValue(), filename, content_type, data);
  var sys_id = attachment.getSysId();
  gs.addInfoMessage('Exported ' + gr.getRowCount() + ' records to ' + filename);
}

In this example, we create a GlideRecord to query the Incident table and retrieve active incidents. We then create an ExcelExport object and add two sheets to it, both containing the incident data. Finally, we export the data to a string and create an attachment record to attach the Excel file to the current record.

[Jonatan Horvath]

Heya!
Where is this Excel Export API you're talking about? 🙂

[Community Alums]

Pretty sure it's a GPT-generated answer.

[kailas_samb]

@Paul Deonarine3  

I have some what similar requirement. Please help me with below question

1. Excel Export API - Is it ServiceNow APIS or Any Excel Library to embed in ServiceNow.

2. Can we read and write a specific cell values from excel and populate ServiceNow Data.

Thank in advance!!!

- Kailas