Creating an .xlsx file from script include

[Frank Silveira]

Hello Experts,

I have the following requirement to achieve: In the CSM/FSM Configurable Workspace the agent needs to be able to export the information from a related list to an .xlsx file, this file should be renamed, the content of the file needs to be worked a little bit, and the file needs to be attached to the parent record of the record where this related list is.
I've managed to do all this with a declarative action that invokes a script include by creating an xml with the excel format and then creating the attachment in the sys_attachment list.
My problem is that the file created (when saved as .xls) gives a warning message that the extension and format don't match (but if I open anyway it works) and when saved as .xlsx it doesn't open. This is unacceptable by my customer as this is a file to be sent to a third party.
I've also considered the option of exporting a javascript library like the 'exceljs' into a script include to use the methods to achieve this, but I don't know the implications of doing that and what kind of impact it would have on the instance.

Any suggestions on how to solve this?

Here is the script include I'm currently using:

var ipGr = new GlideRecord("ip_table");
ipGr.get(recordSysId);
var caseSysId = ipGr.parent;

var caseGr = new GlideRecord("case_table");
caseGr.get(caseSysId);

var xmlContent =
  '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
  '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"' +
  ' xmlns:o="urn:schemas-microsoft-com:office:office"' +
  ' xmlns:x="urn:schemas-microsoft-com:office:excel"' +
  ' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"' +
  ' xmlns:html="http://www.w3.org/TR/REC-html40">' +
  '<Worksheet ss:Name="Sheet1"><Table>';

// Add columns
xmlContent +=
  "<Row>" +
  '<Cell><Data ss:Type="String">Last name</Data></Cell>' +
  '<Cell><Data ss:Type="String">First name</Data></Cell>' +
  '<Cell><Data ss:Type="String">Date of Birth</Data></Cell>' +
  '<Cell><Data ss:Type="String">Age at outward journey</Data></Cell>' +
  '<Cell><Data ss:Type="String">Tour Guide</Data></Cell>' +
  "</Row>";

var dataRows = [];

var participantGr = new GlideRecord("participant_table");
participantGr.addQuery("parent", recordSysId);
participantGr.orderByDesc("travel_guide");
participantGr.query();

while (participantGr.next()) {
  var dataArr = [];
  dataArr.push(participantGr.getValue("first_name"));
  dataArr.push(participantGr.getValue("last_name"));
  dataArr.push(participantGr.getDisplayValue("date_of_birth"));
  dataArr.push(
    participantGr.getDisplayValue("age_at_the_beginning_of_outward_journey")
  );
  participantGr.travel_guide == true ? dataArr.push("X") : dataArr.push("");
  dataRows.push(dataArr);
}

for (var i = 0; i < dataRows.length; i++) {
  xmlContent += "<Row>";
  for (var j = 0; j < dataRows[i].length; j++) {
    xmlContent +=
      '<Cell><Data ss:Type="String">' + dataRows[i][j] + "</Data></Cell>";
  }
  xmlContent += "</Row>";
}

xmlContent += "</Table></Worksheet></Workbook>";

var attachment = new GlideSysAttachment();
var attachmentSysId = attachment.write(caseGr, "filename.xls", "application/vnd.ms-excel", xmlContent);

Thanks in advance.

[soundhar R]

Hi Frank,

Am also have same requirement and stuck with same issue.

[bencollyer]

Hi @Frank Silveira ,

Try this solution here:

https://www.servicenow.com/community/now-platform-forum/generate-excel-file-xls-or-xlsx-based-on-scr...

I have not tried this from a workspace but executing this from a background script works without warnings.  The only difference I can see is that there is less declarations in the xml and workbook tags.

Mine looks like:
<Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet'>

Also I dont have standalone="yes" in the xml tag.  Just <?xml version='1.0' encoding='UTF-8'?>

Otherwise your code looks the same as mine, which works.