Generate Excel file (xls or xlsx) based on script

[Gustavo Olivei1]

Hello All,

I am looking for some help to generate an Excel file based on Script. I read a lot of content based on CSV file, but the request is to have a XLS or XLXS file. So, I have the script results working fine for csv, so I just want to "improve" the result.

The request is:

I want an Excel file to show in the header the name of the field (ex: Assignment Group) and the number of records assigned to them (ex: 5). There is more complexity to obtain the result (filtering and using Glideaggregate), but the final output into the file should be like this:

Assignment Group	Quantity
Group A	5
Group B	2

The current code that I am using you can find below:

var gr = new GlideRecord('table');

gr.get('sys_id of record');

var grAttachment = new GlideSysAttachment();

grAttachment.write(gr, filename, contenttype, excelData);

[Sonu Parab]

Hi @Gustavo Olivei1 ,
Have a look below thread,

https://www.servicenow.com/community/itsm-forum/how-can-i-export-data-into-excel-xlsx-via-script/m-p...

Thank you,

[Gustavo Olivei1]

Hello Sonu,

Yeah, I saw this reference, but all discussions are evolving sometimes client scripts to load a list or form and others still working with CSV files when the background is requested.

[Anne18]

Hi Sonu,

Did you find a way to produce a .xls file with a script ?

Regards

[AJAY1231]

Hi , I was able to acheive this by bringing to Excel (.xlsx) instead of csv.

Just below copy paste the code, and u will have excel created in the specified record.

Script:

var rows_data = "";

var columns_of_excel = "<?xml version='1.0' encoding='UTF-8'?>";

columns_of_excel += "<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>";

// Define the Styles for bold headers

columns_of_excel += "<Styles>";

columns_of_excel += "<Style ss:ID='BoldHeader'>";

columns_of_excel += "<Font ss:Bold='1'/>";  // Make the font bold

columns_of_excel += "</Style>";

columns_of_excel += "</Styles>";

columns_of_excel += "<Worksheet ss:Name='Incident Data'>";

columns_of_excel += "<Table>";

// Define the headers and apply the bold style

columns_of_excel += "<Row>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Number</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Opened</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Short Description</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Created</Data></Cell>";

columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Caller</Data></Cell>";

columns_of_excel += "</Row>";

// Retrieve the incidents for the 'software' category

var gr = new GlideRecord('incident');

gr.addQuery('category', 'software');

gr.query();

while (gr.next()) {

    rows_data += "<Row>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.number + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.opened_at + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.short_description + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.sys_created_on + "</Data></Cell>";

    rows_data += "<Cell><Data ss:Type='String'>" + gr.caller_id.getDisplayValue() + "</Data></Cell>";

    rows_data += "</Row>";

}

// Close the XML structure

columns_of_excel += rows_data;

columns_of_excel += "</Table>";

columns_of_excel += "</Worksheet>";

columns_of_excel += "</Workbook>";

// Combine the columns (headers) and rows data

var columns_and_rows = columns_of_excel; // complete data including headers and rows

// Get the current user record for attaching the file

var user = new GlideRecord('sys_user');

user.addQuery('sys_id', gs.getUserID());

user.query();

user.next();

// Create the attachment

var attachment = new GlideSysAttachment();

attachment.write(user, "Incident software.xls", "application/vnd.ms-excel", columns_and_rows);

gs.print("Excel file exported and attached successfully. Please check.");