Create Excel File Via Script And Download

[Dung Hoang]

Hi ServiceNow Community,

I have a task to generate an Excel file via script with a specific format. The file should contain 5 columns, and within those, 3 columns need to be merged.

I've already researched on the Community and consulted ChatGPT, and I found the following script:

Script to generate Excel file:

buildExcelFile: function () {

        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

        // let incidentGr = new GlideRecord("incident");

        // if (incidentGr.get("255ac3fcc37e661007dd78cc050131fa")) {

        //  let gsa = new GlideSysAttachment();

        //  gsa.write(incidentGr, "test_excel.xls", "application/vnd.ms-excel", columns_and_rows);

        // }

        // return GlideStringUtil.base64DecodeAsBytes(GlideStringUtil.base64Encode(columns_and_rows));

        return columns_and_rows;

Client script to download the Excel file from a UI Page:

(function() {

    function stringToBinary(inputString) {

        var binaryResult = "";

        for (var i = 0; i < inputString.length; i++) {

            var charCode = inputString.charCodeAt(i);

            var binaryChar = charCode.toString(2);

            // Pad with leading zeros to ensure 8-bit representation (for ASCII characters)

            while (binaryChar.length < 8) {

                binaryChar = "0" + binaryChar;

            }

            binaryResult += binaryChar;

        }

        return binaryResult;

    }

    let ga = new GlideAjax("global.global_ExcelBuilder");

    ga.addParam("sysparm_name", "buildExcelFile");

    ga.getXMLAnswer(function(response) {

        console.log(response);

        let binaryStr = stringToBinary(response);

        let excelType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        var blob = new Blob([binaryStr], {

            type: excelType

        });

        var objectURL = URL.createObjectURL(blob);

        var link = document.createElement("a");

        link.href = objectURL;

        link.download = "my_generated_file.xlsx"; // Desired file name

        link.click();

        // Revoke the object URL after use to free up memory

        URL.revokeObjectURL(objectURL);

    });

})();

However, the result I got can be downloaded, but when I try to open it, an error dialog appears with the message: "The file format or file extension is not valid".
Could you please help me review the code to identify what might be wrong and how I can fix it?

Thank you very much!

[Rafael Batistot · Accepted solution]

Hi @Dung Hoang 

You’re generating an Excel XML Spreadsheet 2003 (SpreadsheetML), not a .xlsx file. That means:

• Your Content-Type and file extension must match: use .xls with
  "application/vnd.ms-excel".
• In the XML, one of your headers has a missing quotation mark:
• <Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>Number</Data></Cell>
• If you want merged columns, you need to add ss:MergeAcross="N" on the <Cell> tag. For example, to merge 3 columns starting from this cell:
• <Cell ss:MergeAcross="2"><Data ss:Type="String">Merged Header</Data></Cell>

But i have a doubt… Why are you build this script if is more easy export the data to xlsx?

See the reference

https://www.servicenow.com/docs/bundle/zurich-platform-administration/page/administer/exporting-data...