how to get excel sheet data in mrvs

Vinod S Patil
Tera Contributor

5 hours ago

Hello Everyone

I have requirement to add excel sheets data in catalog item's mrvs
I have two sheets in one excel file add and delete.

add sheet has following details in screenshot add


ADDADD

 

 

 
 

delete sheet has following details in screenshot delete
delete.png

 

 




I created script inlcude and onchange client script for it. 
I am getting only first sheet data in mrvs and in second (delete) sheet values are not getting mrvs.

BElow is my script include Please suggest me

getExcelData: function() {
    var arrObj = [];
    var file_id = this.getParameter('sysparam_attSysId');

    var gsa = new GlideSysAttachment();
    var sheetParser = new sn_impex.GlideExcelParser();

    var stream = gsa.getContentStream(file_id);
    sheetParser.parse(stream);

    var sheetNames = sheetParser.getSheetNames();

    for (var i = 0; i < sheetNames.length; i++) {
        var sheetNameRaw = sheetNames[i];
        var sheetName = sheetNameRaw.trim().toLowerCase();
        var action = '';

        if (sheetName === 'add') {
            action = 'add';
        } else if (sheetName === 'delete') {
            action = 'delete';
        } else if (sheetName === 'rename') {
            action = 'rename';
        }

        if (!action) continue;

        // New parser for each sheet
        var streamNew = gsa.getContentStream(file_id);
        var parser = new sn_impex.GlideExcelParser();
        parser.parse(streamNew);
        parser.setSheetName(sheetNameRaw);
        parser.seek(0);

        while (parser.next()) {
            var row = parser.getRow();
            if (!row) continue;

            // Normalize keys
            var normalizedRow = {};
            for (var key in row) {
                if (row.hasOwnProperty(key)) {
                    normalizedRow[key.toLowerCase().trim()] = row[key];
                }
            }

            // Skip empty rows
            var allEmpty = true;
            for (var k in normalizedRow) {
                if (normalizedRow[k]) {
                    allEmpty = false;
                    break;
                }
            }
            if (allEmpty) continue;

            var obj = {};
            obj.action_request = action;

            if (action === 'add' || action === 'delete') {
                obj.device_type = normalizedRow['device type'] || '';
                obj.device_name = normalizedRow['device name'] || '';
                obj.serial_number = normalizedRow['serial number'] || '';
                obj.fqdn_hostname = normalizedRow['fqdn'] || '';
                obj.vlan_network_switch_and_port_info = normalizedRow['address'] || '';
                obj.altas_app = normalizedRow['atlas'] || '';
                obj.ip_type = normalizedRow['ip type'] || '';
            }

            arrObj.push(obj);
        }
    }

    return JSON.stringify(arrObj);
},





@Ankur Bawiskar 

 
0 Helpfuls
  • 66 Views
1 REPLY 1

NagaChandaE
Mega Sage

3 hours ago - last edited 3 hours ago

Hi @Vinod S Patil ,

Try this script 

getExcelData: function() {
    var arrObj = [];
    var file_id = this.getParameter('sysparam_attSysId');
    var gsa = new GlideSysAttachment();

    var sheetNames = this._getSheetNames(file_id, gsa);  // Extract once

    for (var i = 0; i < sheetNames.length; i++) {
        var sheetNameRaw = sheetNames[i];
        var sheetName = sheetNameRaw.trim().toLowerCase();
        var action = '';

        if (sheetName === 'add') action = 'add';
        else if (sheetName === 'delete') action = 'delete';
        else if (sheetName === 'rename') action = 'rename';

        if (!action) continue;

        // Fresh parse for THIS sheet only
        var arrSheet = this._parseSheet(file_id, gsa, sheetNameRaw, i);
        arrObj = arrObj.concat(arrSheet);
    }

    return JSON.stringify(arrObj);
},

_getSheetNames: function(file_id, gsa) {
    var stream = gsa.getContentStream(file_id);
    if (!stream) return [];
    
    var parser = new sn_impex.GlideExcelParser();
    parser.parse(stream);
    return parser.getSheetNames() || [];
},

_parseSheet: function(file_id, gsa, sheetNameRaw, sheetIndex) {
    var arrSheet = [];
    var stream = gsa.getContentStream(file_id);  // Fresh stream every time
    if (!stream) return arrSheet;

    var parser = new sn_impex.GlideExcelParser();
    
    // Method 1: setSheetName BEFORE parse (preferred)
    parser.setSheetName(sheetNameRaw);
    // Backup: setSheetNumber
    parser.setSheetNumber(sheetIndex);
    
    parser.setNullToEmpty(true);  // Handle empties better
    
    if (!parser.parse(stream)) {
        gs.error('Parse failed for sheet: ' + sheetNameRaw);
        return arrSheet;
    }

   

    while (parser.next()) {
        var row = parser.getRow();
        if (!row) continue;

        // Your normalize + skip empty logic (unchanged)
        var normalizedRow = {};
        for (var key in row) {
            if (row.hasOwnProperty(key)) {
                normalizedRow[key.toLowerCase().trim()] = row[key];
            }
        }

        var allEmpty = true;
        for (var k in normalizedRow) {
            if (normalizedRow[k]) {
                allEmpty = false;
                break;
            }
        }
        if (allEmpty) continue;

        var obj = { action_request: action };  // 'action' from outer scope

        if (action === 'add' || action === 'delete') {
            obj.device_type = normalizedRow['device type'] || '';
            obj.device_name = normalizedRow['device name'] || '';
            obj.serial_number = normalizedRow['serial number'] || '';
            obj.fqdn_hostname = normalizedRow['fqdn'] || '';
            obj.vlan_network_switch_and_port_info = normalizedRow['address'] || '';
            obj.altas_app = normalizedRow['atlas'] || '';
            obj.ip_type = normalizedRow['ip type'] || '';
        }

        arrSheet.push(obj);
    }

    
    return arrSheet;
}
0 Helpfuls