Download Excel file – knowledgebase JJ

Download Excel file using suitelet script

/**

* @NApiVersion 2.1

* @NScriptType Suitelet

/***********************************************************************************

* Description : Suitelet script for download the Excel file from the Quote record.

***************************************************************************************************/

define([‘N/search’, ‘N/file’, ‘N/log’, ‘N/encode’], (search, file, log, encode) => {

let currencySymbol = ”;

/**

* Utility function to safely retrieve either the text or value of a field from a search result.

* @param {search.Result} result – The NetSuite search result object.

* @param {string} field – The field ID to retrieve.

* @param {boolean} [getText=false] – If true, fetches the field text instead of the value.

* @returns {string} – The sanitized field value or text. Returns an empty string on error.

function getField(result, field, getText = false) {

try {

return sanitizeData(getText ? result.getText({ name: field }) : result.getValue({ name: field }));

} catch (e) {

log.error(“Error @ getField”, e);

return ”;

}

/**

* Utility function to safely retrieve the value or text of a joined field from a search result.

* @param {search.Result} result – The NetSuite search result object.

* @param {string} field – The field ID to retrieve.

* @param {string} join – The join ID (e.g., ‘customer’, ‘item’, ‘currency’).

* @param {boolean} [getText=false] – If true, retrieves the field text instead of value.

* @returns {string} – The sanitized joined field value or text. Returns an empty string on error.

function getFieldJoined(result, field, join, getText = false) {

try {

return sanitizeData(getText ? result.getText({ name: field, join }) : result.getValue({ name: field, join }));

} catch (e) {

log.error(“Error @ getFieldJoined”, e);

return ”;

}

/**

* Utility function to sanitize input data by removing newline characters and trimming whitespace.

* @param {*} data – The input data to sanitize. Can be a string or any value convertible to string.

* @returns {string} – A sanitized string with newlines replaced by spaces and trimmed of leading/trailing whitespace.

function sanitizeData(data) {

try {

return (data || ”).toString().replace(/[rn]+/g, ‘ ‘).trim();

} catch (e) {

log.error(“Error @ sanitizeData”, e);

return ”;

}

/**

* Utility function to generate the current timestamp in `YYYYMMDD-HHMMSS` format.

* @returns {string} – The formatted timestamp string, or an empty string if an error occurs.

function getTimestamp() {

try {

let d = new Date();

return `${String(d.getHours()).padStart(2, ‘0’)}${String(d.getMinutes()).padStart(2, ‘0’)}${String(d.getSeconds()).padStart(2, ‘0’)}`;

} catch (e) {

log.error(“Error @ getTimestamp”, e);

return ”;

}

/**

* Main function to generate Excel data from Estimate records.

* @param {Array<string|number>} internalIds – Array of internal IDs of Estimate records to process.

* @returns {{excelContent: string, fileName: string}} – Excel XML string and generated file name.

function getEstimateExcelContent(internalIds) {

try {

let fallbackSymbols = { GBP: ‘£’, USD: ‘$’, CAD: ‘$’, EUR: ‘€’, AUD: ‘$’ };

let documentNumber = ”, rows = [], lineData = [], missingDimensionItems = [], grandTotal = 0;

let estimateSearchObj = search.create({

type: “estimate”,

filters: [

[“type”, “anyof”, “Estimate”],

“AND”, [“shipping”, “is”, “F”],

“AND”, [“cogs”, “is”, “F”],

“AND”, [“taxline”, “is”, “F”],

“AND”, [“mainline”, “is”, “F”],

“AND”, [“internalid”, “anyof”, internalIds],

“AND”, [“transactiondiscount”, “is”, “F”]

columns: [

“tranid”, “billaddress”, “shipaddress”, “entity”, “trandate”, “duedate”, “salesrep”, “quantity”,

“rate”, “amount”, “custbody_contactname”, “terms”, “custbody_ezconceptleadtime”, “custbody_originalprojectid”,

“custcol_labeldoornumber”, “custcol_ezwallthickness”, “custcol_ezdoorheight”, “custcol_doorwidth”,

“custcol_ezstriketype”, “custcol_ezhingehand”, “custcol_ezfirerated”, “custcol_ezhingeprep”, “memo”, “item”,

search.createColumn({ name: “symbol”, join: “Currency” }),

search.createColumn({ name: “title” }),

]

});

estimateSearchObj.run().each(result => {

let wall = getField(result, “custcol_ezwallthickness”);

let width = getField(result, “custcol_doorwidth”);

let height = getField(result, “custcol_ezdoorheight”);

let itemName = getField(result, “item”, true) || getField(result, “item”);

let rate = getField(result, “rate”);

if (!wall && !width && !height) {

missingDimensionItems.push({ itemName, rate });

} else {

lineData.push({ result });

}

return true;

});

let extraItemHeaders = missingDimensionItems.map((entry, i) => `${entry.itemName || ‘Item’}`);

if (lineData.length > 0) {

let first = lineData[0].result;

documentNumber = getField(first, “tranid”);

quoteName = sanitizeData(first.getValue({ name: “title” })) || ”;

let rawDateStr = first.getValue({ name: “trandate” });

let [day, month, year] = rawDateStr.split(‘/’);

let quoteDate = `${year}${month.padStart(2, ‘0’)}${day.padStart(2, ‘0’)}`;

let symbol = getFieldJoined(first, “symbol”, “currency”);

currencySymbol = fallbackSymbols[symbol] || ”;

fileName = `${documentNumber} – Project ${quoteName} ${quoteDate} – ${getTimestamp()}`;

rows.push({ header: true, cells: [‘Document Number’, ‘Bill To’, ‘Deliver To’, ‘Account #’, ‘Quote Date’, ‘Quote Expiration’, ‘Entered By’, ‘Contact’, ‘Terms’, ‘Production Lead Time’, ‘Project’] });

rows.push({

header: false,

cells: [

getField(first, “tranid”),

getField(first, “billaddress”),

getField(first, “shipaddress”),

getField(first, “entity”, true),

getField(first, “trandate”),

getField(first, “duedate”),

getField(first, “salesrep”, true),

getField(first, “custbody_contactname”),

getField(first, “terms”, true),

getField(first, “custbody_ezconceptleadtime”),

getField(first, “custbody_originalprojectid”)

]

});

rows.push({

header: true,

cells: [‘Door’, ‘Description’, ‘Wall’, ‘Height’, ‘Width’, ‘Strike’, ‘Hand’, ‘FR’, ‘Prep’, ‘Frame Rate’]

.concat(extraItemHeaders)

.concat([‘Per Frame Amount’, ‘Quantity’, ‘Total’])

});

}

lineData.forEach(({ result }) => {

let rate = parseFloat(result.getValue({ name: “rate” }) || 0);

let quantity = parseFloat(result.getValue({ name: “quantity” }) || 0);

let baseCells = [

getField(result, “custcol_labeldoornumber”, true) || getField(result, “custcol_labeldoornumber”),

getField(result, “memo”),

getField(result, “custcol_ezwallthickness”),

getField(result, “custcol_ezdoorheight”),

getField(result, “custcol_doorwidth”),

getField(result, “custcol_ezstriketype”),

getField(result, “custcol_ezhingehand”, true),

getField(result, “custcol_ezfirerated”, true),

getField(result, “custcol_ezhingeprep”, true),

rate === 0 ? ” : `${currencySymbol} ${rate.toFixed(2)}`

];

let extraRates = missingDimensionItems.map(entry => parseFloat(entry.rate || 0));

let extraCells = extraRates.map(r => r === 0 ? ” : `${currencySymbol} ${r.toFixed(2)}`);

let perFrameAmount = rate + extraRates.reduce((sum, val) => sum + val, 0);

let total = perFrameAmount * quantity;

grandTotal += total;

rows.push({

header: false,

cells: baseCells.concat(extraCells).concat([

perFrameAmount === 0 ? ” : `${currencySymbol} ${perFrameAmount.toFixed(2)}`,

quantity.toString(),

total === 0 ? ” : `${currencySymbol} ${total.toFixed(2)}`

])

});

let totalRowLength = rows[rows.length – 1].cells.length;

rows.push({

header: “grandTotal”,

cells: Array(totalRowLength – 1).fill(”).concat(`${currencySymbol} ${grandTotal.toFixed(2)}`)

});

return {

excelContent: buildExcelXML(rows),

fileName: fileName

};

} catch (e) {

log.error(“error @ getEstimateExcelContent”, e);

return {};

}

/**

* Builds an Excel-compatible XML string from structured row data.

* @param {Array<Object>} rows – An array of row objects with the following structure:

* {

* header: true | false | “grandTotal”,

* cells: Array<string>

* }

* @returns {string} – Excel XML string formatted for Excel import/download.

function buildExcelXML(rows) {

try {

let xml = ‘<?xml version=”1.0″?><?mso-application progid=”Excel.Sheet”?>’;

xml += ‘<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:o=”urn:schemas-microsoft-com:office:office” xmlns:x=”urn:schemas-microsoft-com:office:excel” xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet” xmlns:html=”http://www.w3.org/TR/REC-html40″>’;

xml += ‘<Styles>’;

xml += ‘<Style ss:ID=”boldHeader”><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”titleStyle”><Font ss:Bold=”1″ ss:Size=”16″/><Alignment ss:Horizontal=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”centerBoldHeader”><Font ss:Bold=”1″/><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”lightGreenFill”><Font ss:Bold=”1″/><Interior ss:Color=”#90EE90″ ss:Pattern=”Solid”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”yellowBoldFill”><Font ss:Bold=”1″/><Interior ss:Color=”#FFFF00″ ss:Pattern=”Solid”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”centeredData”><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosHeading”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″ ss:Bold=”1″/><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”noStyle”></Style>’;

xml += ‘<Style ss:ID=”aptosDate”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Alignment ss:Horizontal=”Center”/></Style>’;

xml += ‘<Style ss:ID=”aptosData”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Alignment ss:Horizontal=”Left” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosGreenHeader”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″ ss:Bold=”1″/><Interior ss:Color=”#90EE90″ ss:Pattern=”Solid”/><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosGreenData”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Interior ss:Color=”#90EE90″ ss:Pattern=”Solid”/><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosYellowTotal”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Interior ss:Color=”#FFFF00″ ss:Pattern=”Solid”/><Alignment ss:Horizontal=”Right” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosDataCentered”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosLightGreenHeader”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″ ss:Bold=”1″/><Interior ss:Color=”#E6FFE6″ ss:Pattern=”Solid”/> <!– lighter green –><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosLightGreenData”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Interior ss:Color=”#E6FFE6″ ss:Pattern=”Solid”/> <!– lighter green –><Alignment ss:Horizontal=”Center” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosRightAlignedMoney”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Alignment ss:Horizontal=”Right” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosRightAlignedLightGreen”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Interior ss:Color=”#E6FFE6″ ss:Pattern=”Solid”/><Alignment ss:Horizontal=”Right” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”aptosRightAlignedGreen”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Interior ss:Color=”#90EE90″ ss:Pattern=”Solid”/><Alignment ss:Horizontal=”Right” ss:Vertical=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”blackFill”><Font ss:FontName=”Aptos Narrow” ss:Size=”11″ ss:Color=”#FFFFFF”/><Interior ss:Color=”#000000″ ss:Pattern=”Solid”/><Alignment ss:Horizontal=”Center”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘<Style ss:ID=”underlinedURL”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″ ss:Color=”#8A2BE2″ ss:Underline=”Single”/><Alignment ss:Horizontal=”Left”/><Borders><Border ss:Position=”Bottom” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Top” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Left” ss:LineStyle=”Continuous” ss:Weight=”1″/><Border ss:Position=”Right” ss:LineStyle=”Continuous” ss:Weight=”1″/></Borders></Style>’;

xml += ‘</Styles>’;

xml += ‘<Worksheet ss:Name=”Estimate Report”><Table>’;

xml += ‘<Row>’;

xml += ‘<Cell ss:StyleID=”aptosHeading” ss:MergeAcross=”1″><Data ss:Type=”String”></Data></Cell>’;

for (let i = 0; i < 9; i++) {

xml += ‘<Cell ss:StyleID=”blackFill”><Data ss:Type=”String”> </Data></Cell>’;

}

xml += ‘</Row>’;

let perFrameAmountColIndex = –1;

let totalColIndex = –1;

let frameRateColIndex = 9;

let extraRateStartColIndex = 10;

let quantityColIndex = –1;

if (rows.length > 2 && rows[2].header === true) {

perFrameAmountColIndex = rows[2].cells.indexOf(‘Per Frame Amount’);

totalColIndex = rows[2].cells.indexOf(‘Total’);

quantityColIndex = rows[2].cells.indexOf(‘Quantity’);

}

let rowCounter = 0;

rows.forEach(row => {

rowCounter++;

if (rowCounter === 3) {

xml += ‘<Row>’;

for (let i = 0; i < row.cells.length; i++) {

xml += ‘<Cell ss:StyleID=”blackFill”><Data ss:Type=”String”> </Data></Cell>’;

}

xml += ‘</Row>’;

}

xml += ‘<Row>’;

row.cells.forEach((cell, index) => {

let value = sanitizeData(cell);

let style = “boldHeader”;

if (row.header === true) {

if (cell === ‘Per Frame Amount’) {

style = “aptosLightGreenHeader”;

} else if (cell === ‘Total’) {

style = “aptosGreenHeader”;

} else {

style = “aptosHeading”;

}

else if (row.header === “grandTotal”) {

style = (index === totalColIndex) ? “aptosYellowTotal” : “noStyle”;

}

else if (row.header === false) {

if (index === perFrameAmountColIndex) {

style = “aptosRightAlignedLightGreen”;

} else if (index === totalColIndex) {

style = “aptosRightAlignedGreen”;

} else if (index === frameRateColIndex) {

style = “aptosRightAlignedMoney”;

} else if (index >= extraRateStartColIndex && index < perFrameAmountColIndex) {

style = “aptosRightAlignedMoney”;

}

else if (index === quantityColIndex) {

style = “aptosDataCentered”;

}

else if (index >= 0 && index <= 8) {

style = “aptosDataCentered”;

} else {

style = “aptosData”;

}

xml += `<Cell ss:StyleID=”${style}“><Data ss:Type=”String”>${value}</Data></Cell>`;

});

xml += ‘</Row>’;

});

xml += ‘</Table></Worksheet></Workbook>’;

return xml;

} catch (e) {

log.error(“Error in buildExcelXML”, e);

return ”;

}

/**

* Defines the Suitelet script trigger point.

* @param {Object} scriptContext

* @param {ServerRequest} scriptContext.request – Incoming request

* @param {ServerResponse} scriptContext.response – Suitelet response

* @since 2015.2

function onRequest(context) {

try {

let recIds = context.request.parameters.internalIds;

let { excelContent, fileName } = getEstimateExcelContent(recIds);

let strXmlEncoded = encode.convert({

string: excelContent,

inputEncoding: encode.Encoding.UTF_8,

outputEncoding: encode.Encoding.BASE_64

});

let excelFile = file.create({

name: fileName + ‘.xls’,

fileType: file.Type.EXCEL,

contents: strXmlEncoded

});

context.response.setHeader({ name: ‘Content-Type’, value: ‘application/vnd.ms-excel’ });

context.response.setHeader({ name: ‘Content-Disposition’, value: ‘attachment; filename=”‘ + fileName + ‘.xls”‘ });

context.response.writeFile(excelFile, false);

} catch (e) {

log.error(‘Error in onRequest’, e);

}

return { onRequest };

});

* STBUK-163 : Export Excel file from Quote

****************************************************************************************

* Author: Jobin and Jismi IT Services LLP

* Date created : 09-June-2025

* Unauthorized reproduction or distribution of this script, or any portion of it, may result in severe civil and criminal penalties and will be prosecuted to the maximum extent possible under law.

* Description : Suitelet script for download the Excel file from the Quote record.

* REVISION HISTORY

* @version 1.0 STBUK-163 : 09-June-2025 : created the initial build by JJ0204

***************************************************************************************************/

define([‘N/search’, ‘N/file’, ‘N/log’, ‘N/encode’], (search, file, log, encode) => {

let currencySymbol = ”;

/**

* Utility function to safely retrieve either the text or value of a field from a search result.

* @param {search.Result} result – The NetSuite search result object.

* @param {string} field – The field ID to retrieve.

* @param {boolean} [getText=false] – If true, fetches the field text instead of the value.

* @returns {string} – The sanitized field value or text. Returns an empty string on error.

function getField(result, field, getText = false) {

try {

return sanitizeData(getText ? result.getText({ name: field }) : result.getValue({ name: field }));

} catch (e) {

log.error(“Error @ getField”, e);

return ”;

}

/**

* Utility function to safely retrieve the value or text of a joined field from a search result.

* @param {search.Result} result – The NetSuite search result object.

* @param {string} field – The field ID to retrieve.

* @param {string} join – The join ID (e.g., ‘customer’, ‘item’, ‘currency’).

* @param {boolean} [getText=false] – If true, retrieves the field text instead of value.

* @returns {string} – The sanitized joined field value or text. Returns an empty string on error.

function getFieldJoined(result, field, join, getText = false) {

try {

return sanitizeData(getText ? result.getText({ name: field, join }) : result.getValue({ name: field, join }));

} catch (e) {

log.error(“Error @ getFieldJoined”, e);

return ”;

}

/**

* Utility function to sanitize input data by removing newline characters and trimming whitespace.

* @param {*} data – The input data to sanitize. Can be a string or any value convertible to string.

* @returns {string} – A sanitized string with newlines replaced by spaces and trimmed of leading/trailing whitespace.

function sanitizeData(data) {

try {

return (data || ”).toString().replace(/[rn]+/g, ‘ ‘).trim();

} catch (e) {

log.error(“Error @ sanitizeData”, e);

return ”;

}

/**

* Utility function to generate the current timestamp in `YYYYMMDD-HHMMSS` format.

* @returns {string} – The formatted timestamp string, or an empty string if an error occurs.

function getTimestamp() {

try {

let d = new Date();

return `${String(d.getHours()).padStart(2, ‘0’)}${String(d.getMinutes()).padStart(2, ‘0’)}${String(d.getSeconds()).padStart(2, ‘0’)}`;

} catch (e) {

log.error(“Error @ getTimestamp”, e);

return ”;

}

/**

* Main function to generate Excel data from Estimate records.

* @param {Array<string|number>} internalIds – Array of internal IDs of Estimate records to process.

* @returns {{excelContent: string, fileName: string}} – Excel XML string and generated file name.

function getEstimateExcelContent(internalIds) {

try {

let fallbackSymbols = { GBP: ‘£’, USD: ‘$’, CAD: ‘$’, EUR: ‘€’, AUD: ‘$’ };

let documentNumber = ”, rows = [], lineData = [], missingDimensionItems = [], grandTotal = 0;

let estimateSearchObj = search.create({

type: “estimate”,

filters: [

[“type”, “anyof”, “Estimate”],

“AND”, [“shipping”, “is”, “F”],

“AND”, [“cogs”, “is”, “F”],

“AND”, [“taxline”, “is”, “F”],

“AND”, [“mainline”, “is”, “F”],

“AND”, [“internalid”, “anyof”, internalIds],

“AND”, [“transactiondiscount”, “is”, “F”]

columns: [

“tranid”, “billaddress”, “shipaddress”, “entity”, “trandate”, “duedate”, “salesrep”, “quantity”,

“rate”, “amount”, “custbody_contactname”, “terms”, “custbody_ezconceptleadtime”, “custbody_originalprojectid”,

“custcol_labeldoornumber”, “custcol_ezwallthickness”, “custcol_ezdoorheight”, “custcol_doorwidth”,

“custcol_ezstriketype”, “custcol_ezhingehand”, “custcol_ezfirerated”, “custcol_ezhingeprep”, “memo”, “item”,

search.createColumn({ name: “symbol”, join: “Currency” }),

search.createColumn({ name: “title” }),

]

});

estimateSearchObj.run().each(result => {

let wall = getField(result, “custcol_ezwallthickness”);

let width = getField(result, “custcol_doorwidth”);

let height = getField(result, “custcol_ezdoorheight”);

let itemName = getField(result, “item”, true) || getField(result, “item”);

let rate = getField(result, “rate”);

if (!wall && !width && !height) {

missingDimensionItems.push({ itemName, rate });

} else {

lineData.push({ result });

}

return true;

});

let extraItemHeaders = missingDimensionItems.map((entry, i) => `${entry.itemName || ‘Item’}`);

if (lineData.length > 0) {

let first = lineData[0].result;

documentNumber = getField(first, “tranid”);

quoteName = sanitizeData(first.getValue({ name: “title” })) || ”;

let rawDateStr = first.getValue({ name: “trandate” });

let [day, month, year] = rawDateStr.split(‘/’);

let quoteDate = `${year}${month.padStart(2, ‘0’)}${day.padStart(2, ‘0’)}`;

let symbol = getFieldJoined(first, “symbol”, “currency”);

currencySymbol = fallbackSymbols[symbol] || ”;

fileName = `${documentNumber} – Project ${quoteName} ${quoteDate} – ${getTimestamp()}`;

rows.push({

header: false,

cells: [

getField(first, “tranid”),

getField(first, “billaddress”),

getField(first, “shipaddress”),

getField(first, “entity”, true),

getField(first, “trandate”),

getField(first, “duedate”),

getField(first, “salesrep”, true),

getField(first, “custbody_contactname”),

getField(first, “terms”, true),

getField(first, “custbody_ezconceptleadtime”),

getField(first, “custbody_originalprojectid”)

]

});

rows.push({

header: true,

cells: [‘Door’, ‘Description’, ‘Wall’, ‘Height’, ‘Width’, ‘Strike’, ‘Hand’, ‘FR’, ‘Prep’, ‘Frame Rate’]

.concat(extraItemHeaders)

.concat([‘Per Frame Amount’, ‘Quantity’, ‘Total’])

});

}

lineData.forEach(({ result }) => {

let rate = parseFloat(result.getValue({ name: “rate” }) || 0);

let quantity = parseFloat(result.getValue({ name: “quantity” }) || 0);

let baseCells = [

getField(result, “custcol_labeldoornumber”, true) || getField(result, “custcol_labeldoornumber”),

getField(result, “memo”),

getField(result, “custcol_ezwallthickness”),

getField(result, “custcol_ezdoorheight”),

getField(result, “custcol_doorwidth”),

getField(result, “custcol_ezstriketype”),

getField(result, “custcol_ezhingehand”, true),

getField(result, “custcol_ezfirerated”, true),

getField(result, “custcol_ezhingeprep”, true),

rate === 0 ? ” : `${currencySymbol} ${rate.toFixed(2)}`

];

let extraRates = missingDimensionItems.map(entry => parseFloat(entry.rate || 0));

let extraCells = extraRates.map(r => r === 0 ? ” : `${currencySymbol} ${r.toFixed(2)}`);

let perFrameAmount = rate + extraRates.reduce((sum, val) => sum + val, 0);

let total = perFrameAmount * quantity;

grandTotal += total;

rows.push({

header: false,

cells: baseCells.concat(extraCells).concat([

perFrameAmount === 0 ? ” : `${currencySymbol} ${perFrameAmount.toFixed(2)}`,

quantity.toString(),

total === 0 ? ” : `${currencySymbol} ${total.toFixed(2)}`

])

});

let totalRowLength = rows[rows.length – 1].cells.length;

rows.push({

header: “grandTotal”,

cells: Array(totalRowLength – 1).fill(”).concat(`${currencySymbol} ${grandTotal.toFixed(2)}`)

});

return {

excelContent: buildExcelXML(rows),

fileName: fileName

};

} catch (e) {

log.error(“error @ getEstimateExcelContent”, e);

return {};

}

/**

* Builds an Excel-compatible XML string from structured row data.

* @param {Array<Object>} rows – An array of row objects with the following structure:

* {

* header: true | false | “grandTotal”,

* cells: Array<string>

* }

* @returns {string} – Excel XML string formatted for Excel import/download.

function buildExcelXML(rows) {

try {

let xml = ‘<?xml version=”1.0″?><?mso-application progid=”Excel.Sheet”?>’;

xml += ‘<Styles>’;

xml += ‘<Style ss:ID=”noStyle”></Style>’;

xml += ‘<Style ss:ID=”aptosDate”><Font ss:FontName=”Aptos Narrow” ss:Size=”9″/><Alignment ss:Horizontal=”Center”/></Style>’;

xml += ‘</Styles>’;

xml += ‘<Worksheet ss:Name=”Estimate Report”><Table>’;

xml += ‘<Row>’;

xml += ‘<Cell ss:StyleID=”aptosHeading” ss:MergeAcross=”1″><Data ss:Type=”String”></Data></Cell>’;

for (let i = 0; i < 9; i++) {

xml += ‘<Cell ss:StyleID=”blackFill”><Data ss:Type=”String”> </Data></Cell>’;

}

xml += ‘</Row>’;

let perFrameAmountColIndex = –1;

let totalColIndex = –1;

let frameRateColIndex = 9;

let extraRateStartColIndex = 10;

let quantityColIndex = –1;

if (rows.length > 2 && rows[2].header === true) {

perFrameAmountColIndex = rows[2].cells.indexOf(‘Per Frame Amount’);

totalColIndex = rows[2].cells.indexOf(‘Total’);

quantityColIndex = rows[2].cells.indexOf(‘Quantity’);

}

let rowCounter = 0;

rows.forEach(row => {

rowCounter++;

if (rowCounter === 3) {

xml += ‘<Row>’;

for (let i = 0; i < row.cells.length; i++) {

xml += ‘<Cell ss:StyleID=”blackFill”><Data ss:Type=”String”> </Data></Cell>’;

}

xml += ‘</Row>’;

}

xml += ‘<Row>’;

row.cells.forEach((cell, index) => {

let value = sanitizeData(cell);

let style = “boldHeader”;

if (row.header === true) {

if (cell === ‘Per Frame Amount’) {

style = “aptosLightGreenHeader”;

} else if (cell === ‘Total’) {

style = “aptosGreenHeader”;

} else {

style = “aptosHeading”;

}

else if (row.header === “grandTotal”) {

style = (index === totalColIndex) ? “aptosYellowTotal” : “noStyle”;

}

else if (row.header === false) {

if (index === perFrameAmountColIndex) {

style = “aptosRightAlignedLightGreen”;

} else if (index === totalColIndex) {

style = “aptosRightAlignedGreen”;

} else if (index === frameRateColIndex) {

style = “aptosRightAlignedMoney”;

} else if (index >= extraRateStartColIndex && index < perFrameAmountColIndex) {

style = “aptosRightAlignedMoney”;

}

else if (index === quantityColIndex) {

style = “aptosDataCentered”;

}

else if (index >= 0 && index <= 8) {

style = “aptosDataCentered”;

} else {

style = “aptosData”;

}

xml += `<Cell ss:StyleID=”${style}“><Data ss:Type=”String”>${value}</Data></Cell>`;

});

xml += ‘</Row>’;

});

xml += ‘</Table></Worksheet></Workbook>’;

return xml;

} catch (e) {

log.error(“Error in buildExcelXML”, e);

return ”;

}

/**

* Defines the Suitelet script trigger point.

* @param {Object} scriptContext

* @param {ServerRequest} scriptContext.request – Incoming request

* @param {ServerResponse} scriptContext.response – Suitelet response

* @since 2015.2

function onRequest(context) {

try {

let recIds = context.request.parameters.internalIds;

let { excelContent, fileName } = getEstimateExcelContent(recIds);

let strXmlEncoded = encode.convert({

string: excelContent,

inputEncoding: encode.Encoding.UTF_8,

outputEncoding: encode.Encoding.BASE_64

});

let excelFile = file.create({

name: fileName + ‘.xls’,

fileType: file.Type.EXCEL,

contents: strXmlEncoded

});

context.response.setHeader({ name: ‘Content-Type’, value: ‘application/vnd.ms-excel’ });

context.response.setHeader({ name: ‘Content-Disposition’, value: ‘attachment; filename=”‘ + fileName + ‘.xls”‘ });

context.response.writeFile(excelFile, false);

} catch (e) {

log.error(‘Error in onRequest’, e);

}

return { onRequest };

});

Leave a comment Cancel reply