Exporto MRVS to Excel

Martina283L · ‎01-08-2025

I have several MRVS that I should export each to a different sheet, I should attach the file to the ritm

Martina283L · ‎01-08-2025

I was able to create the xls file with different sheets by creating this custom BR:

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

    var gr_sh = new GlideRecord('io_set_item');
    gr_sh.addEncodedQuery('sc_cat_item='+[YOUR CAT ITEM]); //MRVS included in your cat item
    gr_sh.query();

    while (gr_sh.next()) {
        var rows_data = "";
        //gs.info('row_data first'+rows_data);
        columns_of_excel += "<Worksheet ss:Name='" + gr_sh.variable_set.title +
            "'>";
        columns_of_excel += "<Table>";

        // Define the headers and apply the bold style
        columns_of_excel += "<Row>";
        var gr_col = new GlideRecord('item_option_new');
        gr_col.addEncodedQuery('variable_set=' + gr_sh.variable_set + '^ORDERBYorder');
        gr_col.query();

        while (gr_col.next()) {
            columns_of_excel += "<Cell ss:StyleID='BoldHeader'><Data ss:Type='String'>" + gr_col.question_text + "</Data></Cell>";
        }

        columns_of_excel += "</Row>";

        // Retrieve the MRVS
        var rows = '';
        var qa_r = new GlideRecord('sc_multi_row_question_answer'); //Tabella con i valori del multirow variable set
        qa_r.addEncodedQuery('parent_id=' + current.getUniqueValue() + '^variable_set=' + gr_sh.variable_set);
        qa_r.query();
        while (qa_r.next()) {
            if (rows == '') {
                rows = qa_r.row_index;
            } else {
                rows += ',' + qa_r.row_index;
            }

        }
 
        var arrayUtil = new ArrayUtil();
        var rowIndex = arrayUtil.unique(rows.split(','));

        for (var r = 0; r < rowIndex.length; r++) {
            rows_data += "<Row>";
            var gr_col2 = new GlideRecord('item_option_new');
            gr_col2.addEncodedQuery('variable_set=' + gr_sh.variable_set + '^ORDERBYorder');
            gr_col2.query();

            while (gr_col2.next()) {
                var gr = new GlideRecord('sc_multi_row_question_answer'); //Tabella con i valori del multirow variable set
                gr.addEncodedQuery('parent_id=' + current.getUniqueValue() + '^variable_set=' + gr_sh.variable_set + '^row_index=' + rowIndex[r] + "^item_option_new=" + gr_col2.getUniqueValue());
                gr.query();

                if (gr.next()) {
                    rows_data += "<Cell><Data ss:Type='String'>" + gr.getDisplayValue('value') + "</Data></Cell>";
                } else {
                    rows_data += "<Cell><Data ss:Type='String'> </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

    // Create the attachment
    var attachment = new GlideSysAttachment();
    attachment.write(current, current.number + ".xls", "application/vnd.ms-excel", columns_and_rows);
   
    gs.info(current.number+" Excel file exported and attached successfully. Please check.");

View solution in original post

Ilaria2 · ‎01-10-2025

@Martina283L Thanks for posting your solution. It is very useful for my work.