We are unable to process the Excel (.xls) file generated after catalog form submission. Although the file is saved with an “.xls” extension, it is stored in XML format in the backend in ServiceNow where the details of the file is showing as xml format, we need this file to store also as a xls file.
Description:
Upon submission of the catalog form, an Excel file is generated and attached to the corresponding record. However, this file is stored in XML format in the backend, despite appearing as a standard ".xls" file. We have created this excel file though Business Rule.
(function executeRule(current, previous /*null when async*/) {
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'>";
gs.info('WRequest: After State');
// 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='WS Request Data'>";//**
columns_of_excel += "<Table>";
gs.info('WRequest: After Define the Styles for bold headers');
// 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'>User</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'>User Last name</Data></Cell>";
columns_of_excel += "</Row>";
gs.info('WRequest: Define the headers and apply the bold style');
//Retrieve the incidents for the 'software' category
var gr = new GlideRecord('sc_req_item');
var currentDateTime= new GlideDateTime();
gr.addQuery('sys_created_on','>=',currentDateTime);
// 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.description + "</Data></Cell>";//**
// rows_data += "<Cell><Data ss:Type='String'>" + gr.sys_created_on + "</Data></Cell>";
rows_data += "<Cell><Data ss:Type='String'>" + gr.user.getDisplayValue() + "</Data></Cell>";
rows_data += "<Cell><Data ss:Type='String'>" + gr.user.last_name.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
gs.info('WRequest: After Combine the columns (headers) and rows data');
// 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();
gs.info('WRequest: After Get the current user record for attaching the file');
// Create the attachment
var attachment = new GlideSysAttachment();
attachment.write(current, "WS request recordes1.xls", "application/xls", columns_and_rows);
gs.info('WRequest: After Create the attachment');
gs.info('WRequest: After Convert the XML string to a Blob');
})(current, previous);
I have attached the screenshot also in the backend how its stored.
Please help me to generate the file in proper excel format only.
