Creating a pdf file from the excel file added in the attachment by script include

Amol Pawar · ‎08-02-2023

Hi everyone,

I have a requirement where I need to create a pdf file from an attachment added in a record in ServiceNow suppose a request. When someone approves the request, the system will create a pdf file by taking some field values from an Excel sheet added to that request.

By using a script include it is doing as expected. But generating pdf is done. I want to know while generating a pdf from an Excel sheet from an attachment, empty cells populate as "Null" in pdf. How can we import those cells as empty cells only, without adding the "Null" keyword?

Thanks in advance if you could help me with this.

Regards,

Amol

BharathChintala · ‎08-02-2023

@Amol Pawar in same script include that can be done as if value is null pass empty to that.

If you share script I can help. Or you if understood you can do it yourself.

If my inputs have helped with your question, please mark my answer as accepted solution, and give a thumb up.
Bharath Chintala

Amol Pawar · ‎08-02-2023

Hi @BharathChintala ,

Thank you for your reply, below is the script:

var releasenotespdf = Class.create();

releasenotespdf.prototype = Object.extendsObject(global.AbstractAjaxProcessor, {
generatePDF: function(target_table, target_id) {

var gr = new GlideRecord(target_table);
gr.get(target_id);

var pageProperties = {
HeaderImageAttachmentId: '',
HeaderImageAlignment: 'LEFT',
PageSize: 'A4',
GeneratePageNumber: 'false',
TopOrBottomMargin: '72',
LeftOrRightMargin: '36'
};

var header_text = '<div class="pageHeader">' +
'<h3>Free and Open-Source Software Bill Of Materials (SBOM)</h3>' +
'</div>';

var footer_text = '<div class="pageFooter">' +
'<p>Infosys Limited, Bangalore,India</p>' +
'</div>';

var body_text = '<div class="content">' +
'<table width="100%" class="lined-table font1">' +
'<tbody><tr><td>Intellectual Asset Name     : </td><td class="intData">' + gr.select_intellectual_asset.getDisplayValue() + '</td></tr>' +
'<tr><td>Intellectual Asset Version  : </td><td class="intData">' + gr.select_iav.getDisplayValue() + '</td></tr>' +
'<tr><td>Module Name                    : </td><td class="intData">' + gr.module_name.getDisplayValue() + '</td></tr>' +
'<tr><td>Release Date                      : </td><td class="intData">' + GlideDateTime() + '</td></tr>' +
'<tr><td>Deployment Type               : </td><td class="intData">' + gr.module_deployment_type.toString() + '</td></tr></tbody></table>' +
'<h3>COPYRIGHT NOTICE</h3>' +
'<p> believes the information in this document is accurate as of its publication date; such information is subject to change without notice. acknowledges the proprietary rights of other companies to the trademarks, product names and such other intellectual property rights mentioned in this document. Except as expressly permitted, neither this document nor any part of it may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, printing, photocopying, recording or otherwise, without the prior permission of Limited and/or any named intellectual property rights holders under this document.</p>' +
'<ul class="list"><li><b>Objective</b> <p>This BOM report provides a list of the components being employed in the project as per Infosys Policy. For all the FOSS listed in the table below the corresponding license text are stated in the “License Text” section of this report.</p></li><li><b>Asset Bill of Materials</b><table class="tableA" border="1"><tbody>' + this.getAssetBillInfo(target_table, target_id) + '</tbody></table></li><li><b>Conclusion</b><p>This software Bill of Materials (SBOM) is an inventory detailing all the Open-Source components included in this product along with their respective licenses, versions, and source URL.As per Infosys guidelines and Open-source policy it is required to maintain an accurate SBOM in order to ensure that code remains high-quality, compliant, and secure.<br></br> FOSS Liaison: OpenSource.Legal@infosys.com</p></li></ul>';

body_text += '</div>';

var html = css_text + header_text + footer_text + body_text;
//gs.info(html);
//var target_table = 'target_table';
//var target_id = 'target_sys_id';
//dd06dbcc1b7ae8103cd411b1b24bcb56 is sys_id for sys_pdf_generation_font_family
new sn_pdfgeneratorutils.PDFGenerationAPI().convertToPDFWithHeaderFooter(html, target_table, target_id, 'Release Notes', pageProperties);

},

getAssetBillInfo: function(table, sysID) {

var attachmentSysID = "";
var attachGR = new GlideRecord('sys_attachment');
attachGR.addQuery('table_name', table);
attachGR.addQuery('table_sys_id', sysID);
attachGR.addQuery('file_name', 'Final BOM.xlsx');
attachGR.query();
if (attachGR.next()) {
attachmentSysID = attachGR.sys_id;
}
// gs.info("attachmentSysID " + attachmentSysID);

var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
// use attachment sys id of an excel file
var attachmentStream = attachment.getContentStream(attachmentSysID);

parser.parse(attachmentStream);

//retrieve the column headers
var headers = parser.getColumnHeaders();
var header1 = headers[3]; //Component name
var header2 = headers[4]; //Component version name
var header3 = headers[7]; //License names

var header4 = headers[46]; //Component Link

//print headers
// gs.info(header1 + " " + header2);
// gs.info(header3 + " " + header4);
var htmlData = "<tr><th>Component name</th><th>Component version</th><th>License name</th><th>Component URL</th></tr>";

while (parser.next()) {
var row = parser.getRow();
//print row value for both columns
// gs.info(row[header1] + ' ' + row[header2]);
if (global.JSUtil.notNil(row[header1]))
htmlData += '<tr><td>' + row[header1] + '</td><td>' + row[header2] + '</td><td>' + row[header3] + '</td><td class="assetURL">' + row[header4] + '</td></tr>';
}

return htmlData;

},

type: 'releasenotespdf'
});

Please help where I can put null value pass?

Thanks in advance,

Amol

izzeee · ‎06-27-2025

Hi Amol, did you ever figure this out?