how to get excel sheet data in mrvs

[Vinod S Patil]

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:

Action Request	Device Type	Device Name	Serial Number	FQDN	Address	Atlas	IP Type
add	server	awarepoint-6bd6	00603514752A	test	10.212.105.115	FDVC0659924	static

delete sheet has following details:

Action Request	Device Type	Device Name	Serial Number	FQDN	Address
delete	server	SFSFOAM6993188	MXL2034JB3	sfsfoam6993188.sfo.ca.kp.org	Test

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);

},

t

@Ankur Bawiskar 

[Sanjay191]

Hi @Vinod S Patil  

can you please provide client script SS what you are passing from the client side?
Thank you 

[Vinod S Patil]

@Sanjay191 yes have look at client script please 

function onChange(control, oldValue, newValue, isLoading) {

    if (isLoading)

        return;

    if (!newValue)

        return;

    //g_form.addInfoMessage("Attachment: " + newValue);

        g_form.setLabelOf('excel_file','');

    // Initialize GlideAjax to call the Script Include

    var ga = new GlideAjax('C2CGetApplicationName');

    ga.addParam('sysparm_name', 'getExcelData');

    ga.addParam('sysparam_attSysId', newValue);

    ga.getXMLAnswer(function(response) {

       // g_form.addInfoMessage('second');

        if (!response)

            return;

        // Convert the JSON response string into a JavaScript object

        var data = JSON.parse(response);

        //g_form.addInfoMessage('third');

        g_form.setValue('request_details', JSON.stringify(data));

    });

}

[Vinod S Patil]

Here it is

function onChange(control, oldValue, newValue, isLoading) {

    if (isLoading)

        return;

    if (!newValue)

        return;

    //g_form.addInfoMessage("Attachment: " + newValue);

        g_form.setLabelOf('excel_file','');

    // Initialize GlideAjax to call the Script Include

    var ga = new GlideAjax('C2CGetApplicationName');

    ga.addParam('sysparm_name', 'getExcelData');

    ga.addParam('sysparam_attSysId', newValue);

    ga.getXMLAnswer(function(response) {

       // g_form.addInfoMessage('second');

        if (!response)

            return;

        // Convert the JSON response string into a JavaScript object

        var data = JSON.parse(response);

        //g_form.addInfoMessage('third');

        g_form.setValue('request_details', JSON.stringify(data));

    });

}

[bonjarney]

The stream is a cursor — once parse() reads it, it's at EOF. Your second sheet gets an empty read. Get a fresh stream for each sheet:

var attachment = new GlideSysAttachment();
var sheetNames = ['add', 'delete'];

for (var i = 0; i < sheetNames.length; i++) {
var stream = attachment.getContentStream(attachmentSysId); // fresh every time
var parser = new sn_impex.GlideExcelParser();
parser.setSheetName(sheetNames[i]); // BEFORE parse
parser.parse(stream);

while (parser.next()) {
var row = parser.getRow();
// process row
}
parser.close();
}