SCript to change csv to xlsx format

[Hafsa1]

I have below script configured in Action of flow designer. This will create attachment from the import table and attach it to case number. Issue is this script in creating csv file. we need xlsx file. please help with code.

(function execute(inputs, outputs) {

var caseID = inputs.case;
var caseObj = inputs.caseObj;
var table = inputs.table;
var file_name = inputs.file_name;
var query = inputs.query;
var headers = inputs.headers;
var fields = inputs.fields;
var delimiter = inputs.delimiter;
var hasAttachment=false;
var gDate = new GlideDate();
var count = 0;

//var Headers = ["Candidate ID","Department Full Name","Business Unit", "Event", "Event Reason"];
var header = headers.split(delimiter);
var fileName = file_name + '_' + gDate.getByFormat('yyyy-MM-dd') + '.csv';
var csvData = ''; //The variable csvData will contain a string which is used to build the CSV file contents
for (var i = 0; i < header.length; i++) { //Build the Headers
csvData = csvData + '"' + header[i] + '"' + delimiter;
}
csvData = csvData+"\r\n";

var obj = new GlideRecord(table);
obj.addEncodedQuery(query);
obj.query();
while(obj.next()) {

hasAttachment=true;
count = obj.getRowCount();

var fieldObj = fields.split(delimiter);
var valObj = '';
for (var p = 0; p < fieldObj.length; p++) {
if(gs.nil(valObj))
valObj = '"' + obj[fieldObj[p].toString()] + '"';
else
valObj += delimiter + '"' + obj[fieldObj[p].toString()] + '"';
}
obj.u_business_unit + '",' + '"' + obj.u_event + '",' + '"' + obj.u_event_reason + '"';
csvData = csvData + valObj;
csvData = csvData+"\r\n";
obj.sys_import_state = 'processed';
obj.sys_import_state_comment = 'Complete';
obj.setWorkflow(false);
obj.update();
}

if(hasAttachment==true){
var grAttachment = new GlideSysAttachment();
grAttachment.write(caseObj, fileName, 'application/csv',csvData);
}
outputs.has_attachment=hasAttachment;
outputs.count = count;

})(inputs, outputs);

[Astik Thombare]

Hi @Hafsa1 ,

To modify the script to generate an XLSX file instead of a CSV file, you'll need to use a different approach because creating an XLSX file involves more complexity than creating a CSV file due to its binary format.

Here's an approach using the Excel API in ServiceNow to create an XLSX file:

(function execute(inputs, outputs) {

    var caseID = inputs.case;
    var caseObj = inputs.caseObj;
    var table = inputs.table;
    var file_name = inputs.file_name;
    var query = inputs.query;
    var headers = inputs.headers;
    var fields = inputs.fields;
    var delimiter = inputs.delimiter;
    var hasAttachment = false;
    var gDate = new GlideDate();
    var count = 0;

    var header = headers.split(delimiter);
    var fileName = file_name + '_' + gDate.getByFormat('yyyy-MM-dd') + '.xlsx';

    // Create a new Excel workbook
    var workbook = new global.ExcelWorkbook();

    // Add a worksheet to the workbook
    var worksheet = workbook.addSheet('Sheet1');

    // Set up headers
    for (var i = 0; i < header.length; i++) {
        worksheet.setValue(1, i + 1, header[i]);
    }

    var obj = new GlideRecord(table);
    obj.addEncodedQuery(query);
    obj.query();
    var row = 2; // Starting row for data

    while (obj.next()) {
        hasAttachment = true;
        count++;

        var fieldObj = fields.split(delimiter);
        for (var p = 0; p < fieldObj.length; p++) {
            worksheet.setValue(row, p + 1, obj[fieldObj[p].toString()]);
        }
        obj.sys_import_state = 'processed';
        obj.sys_import_state_comment = 'Complete';
        obj.setWorkflow(false);
        obj.update();
        row++;
    }

    if (hasAttachment) {
        // Convert the workbook to byte array
        var excelByteArray = workbook.getBytes();

        // Create a new attachment and attach the XLSX file
        var grAttachment = new GlideSysAttachment();
        grAttachment.write(caseObj, fileName, 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', excelByteArray);
    }

    outputs.has_attachment = hasAttachment;
    outputs.count = count;

})(inputs, outputs);

In this script:

• We use the global.ExcelWorkbook() API to create an Excel workbook.
• We add a worksheet to the workbook using addSheet().
• We set up headers using setValue() method.
• We iterate through the GlideRecord, populating the worksheet with data.
• Finally, we convert the workbook to a byte array using getBytes() and attach it to the case object.

        If my reply helped with your issue please mark helpful 👍 and correct ✔️ if your issue is resolved.

                         By doing so you help other community members find resolved questions which may relate to an issue they're having

Thanks,

Astik

[Ankur Bawiskar]

@Astik Thombare 

Did you try running this and verify if it really creates an excel file?

[Ankur Bawiskar]

@Hafsa1 

I doubt you can create xlsx file from script.

simply using csv should be fine as it can be opened in Excel application as well

[Hafsa1]

We have some fields which have korea language values. when creating in csv it is converting those values in garbage values. But when exporting in XLS, it is showing correct values. Is there any way in csv format to overcome this issue. Is there any ways we can edit the script to include UTF-8 or something like that?