- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-08-2025 02:45 AM - edited ‎01-08-2025 03:55 AM
I have several MRVS that I should export each to a different sheet, I should attach the file to the ritm
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-08-2025 08:06 AM
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.");
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-08-2025 05:42 AM
Among the features there is also WorkSheet Management but I can't figure out how to do it
https://www.servicenow.com/docs/bundle/xanadu-integrate-applications/page/administer/integrationhub-...
If it should not work do you have any suggestions also using heavy scripts to generate this type of excel?
I realize that it would be a heavy customization
Thanks in advance for your availability
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-08-2025 05:52 AM
check this link
Custom Excel Export or Import in Client Side using ExcelJS Library
If my response helped please mark it correct and close the thread so that it benefits future readers.
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-08-2025 08:06 AM
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.");
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-10-2025 06:25 AM
It solved my problem too, creating Excel file directly from ServiceNow is a crucial thing that was missing in other threads.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎01-14-2025 02:25 AM
Hi Martina,
Can you please let me know how to change background color of row.
<Style ss:ID='BoldHeader'>
For this style I want to change background color to green. Can you advise how to do it.