Description
The format of the CSV file is as follows
There is a client script which contains a function named getBase64Image.Within this function, we have created the HTML canvas element using document.createElement() and storing it inside the canvas variable. Since we want to draw an image on this canvas, which is a two-dimensional image, we will first get the context of our canvas element using the getContext() function, and as an argument to this function, we will pass 2d, and to store this context, we have created another variable called ctx.
Then we have used the Image() constructor and store it inside a variable img. Then we have linked the image to the img using the src property.
The context provides us with a function called drawImage() for drawing images on the canvas.
Syntax of drawing images to the canvas is as
drawImage(image, sx, sy, sWidth, sHeight, dx, dy, dWidth, dHeight) ;
where
image = The image itself that we want to crop and display
sx (source image x-axis) = This parameter says from where you want to clip or start cropping the image from the x-axis.
sy (source image y-axis) = This parameter says from where you want to clip or start cropping the image from the y-axis.
sWidth = The width of the image starting from sx.
sHeight = The height of the image starting from sy.
dx = The point from which to start drawing the image on the screen from the x-axis.
dy = The point from which to start drawing the image on the screen from the y-axis.
dWidth = The length of the images that should be displayed on the screen.
dHeight = The height of the images that should be displayed on the screen.
/**
* @NApiVersion 2.x
* @NScriptType ClientScript
* @NModuleScope SameAccount
*/
define(['N/currentRecord', 'N/record', 'N/search'], function(currentRecord, record, search) {
/**
* Function to be executed after page is initialized.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.mode - The mode in which the record is being accessed (create, copy, or edit)
*
* @since 2015.2
*/
function pageInit(scriptContext) {
}
/**
* Function to be executed when field is changed.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
* @param {string} scriptContext.fieldId - Field name
* @param {number} scriptContext.lineNum - Line number. Will be undefined if not a sublist or matrix field
* @param {number} scriptContext.columnNum - Line number. Will be undefined if not a matrix field
*
* @since 2015.2
*/
function fieldChanged(scriptContext) {
}
/**
* Function to be executed when field is slaved.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
* @param {string} scriptContext.fieldId - Field name
*
* @since 2015.2
*/
function postSourcing(scriptContext) {
}
/**
* Function to be executed after sublist is inserted, removed, or edited.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
*
* @since 2015.2
*/
function sublistChanged(scriptContext) {
}
/**
* Function to be executed after line is selected.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
*
* @since 2015.2
*/
function lineInit(scriptContext) {
}
/**
* Validation function to be executed when field is changed.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
* @param {string} scriptContext.fieldId - Field name
* @param {number} scriptContext.lineNum - Line number. Will be undefined if not a sublist or matrix field
* @param {number} scriptContext.columnNum - Line number. Will be undefined if not a matrix field
*
* @returns {boolean} Return true if field is valid
*
* @since 2015.2
*/
function validateField(scriptContext) {
}
/**
* Validation function to be executed when sublist line is committed.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
*
* @returns {boolean} Return true if sublist line is valid
*
* @since 2015.2
*/
function validateLine(scriptContext) {
}
/**
* Validation function to be executed when sublist line is inserted.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
*
* @returns {boolean} Return true if sublist line is valid
*
* @since 2015.2
*/
function validateInsert(scriptContext) {
}
/**
* Validation function to be executed when record is deleted.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @param {string} scriptContext.sublistId - Sublist name
*
* @returns {boolean} Return true if sublist line is valid
*
* @since 2015.2
*/
function validateDelete(scriptContext) {
}
/**
* Validation function to be executed when record is saved.
*
* @param {Object} scriptContext
* @param {Record} scriptContext.currentRecord - Current form record
* @returns {boolean} Return true if record is valid
*
* @since 2015.2
*/
function saveRecord(scriptContext) {
}
function getBase64Image(imgUrl, callback) {
var img = new Image();
// onload fires when the image is fully loadded, and has width and height
img.onload = function(){
var canvas = document.createElement("canvas");
canvas.width = img.width;
canvas.height = img.height;
var ctx = canvas.getContext("2d");
ctx.drawImage(img, 0, 0);
var dataURL = canvas.toDataURL("image/png");//, dataURL = dataURL.replace(/^data:image\/(png|jpg);base64,/, "");
callback(dataURL); // the base64 string
};
// set attributes and src
img.setAttribute('crossOrigin', 'anonymous'); //
img.src = imgUrl;
}
function formatValue(r, c) {
var type = c.toJSON().type;
if (type == 'select') return r.getText(c);
else if (type == 'float' || type == 'currency') return Number(r.getValue(c));
else if (type == 'text') return r.getValue(c);
else if (!isNaN(r.getValue(c))) return Number(r.getValue(c));
return r.getValue(c);
}
return {
pageInit: pageInit,
/*fieldChanged: fieldChanged,
postSourcing: postSourcing,
sublistChanged: sublistChanged,
lineInit: lineInit,
validateField: validateField,
validateLine: validateLine,
validateInsert: validateInsert,
validateDelete: validateDelete,
saveRecord: saveRecord,*/
excelExport: function(params, files) {
var a = nlExtOpenDivWindow('Generating Excel file', 300, 100, null);
var e = parent.document.getElementById("Generating Excel file");
jQuery(e).parent().removeClass("x-window-loaded");
parent.jQuery('.x-tool-close').hide();
var filesObj = JSON.parse(nlapiGetFieldValue('custpage_filedata'));//JSON.parse(files);
var settings = JSON.parse(params);
var cr = currentRecord.get();
var tran = record.load({ type: cr.type, id: cr.id });
var tranId = tran.getValue({ fieldId: settings.fileName });
const workbook = new ExcelJS.Workbook();
const ws = workbook.addWorksheet(tranId);
//ws.properties.defaultRowHeight = 50;
//ws.properties.defaultColWidth = 25;
if (settings.headerSearch) {
var headers = search.load({ id: settings.headerSearch });
headers.filters.push(search.createFilter({ name: settings.headerSearchKeyFilterId, operator: 'is', values: cr.id }));
headers.run().each(function(header) {
header.columns.forEach(function(c) {
var value = formatValue(header, c);
/*if (value && lineImgColumns.indexOf(trXIdx) != -1) {
var fileId = line.getValue(c);
const tdImage = workbook.addImage({ base64: fileData[fileId], extension: 'png' });
ws.addImage(tdImage, trXIdx + tdYIdx + ':' + trXIdx + tdYIdx);
}
else */ws.getCell(c.label).value = value;
});
return true;
});
}
//ws.mergeCells('A1:E1');
//var address = 'Price Point Importers Pty Ltd \n' + 'T/A Point Accessories \n'+ 'E: lisa@pointaccessories.com.au'
//ws.getCell('E1').alignment = { vertical: 'top', horizontal: 'right', wrapText: true };
//ws.getCell('E1').value = address;
if (settings.lineSearch) {
var lines = search.load({ id: settings.lineSearch });
lines.filters.push(search.createFilter({ name: settings.lineSearchKeyFilterId, operator: 'is', values: cr.id }));
var lineStartX = settings.lineCellStart.x;//'A';
var lineStartY = settings.lineCellStart.y;//'2';
var lineImgColumns = settings.imageColumns;//['A', 'B'];
//Add line headers
var thXIdx = lineStartX;
lines.columns.forEach(function(c) {
if (settings.excludeColumnLabels.indexOf(c.label) == -1) {
ws.getColumn(thXIdx).width = 25;
ws.getCell(thXIdx + lineStartY).value = c.label;
ws.getCell(thXIdx + lineStartY).alignment = { horizontal: 'center' };
thXIdx = String.fromCharCode(thXIdx.charCodeAt() + 1);
}
});
//header style
var fontStyle = JSON.parse(settings.lineHeaderFontStyle || '{}');
if (Object.keys(fontStyle).length) ws.getRow(lineStartY).font = fontStyle;//{ name: 'Arial Black', color: { argb: '00842184' }, family: 2, size: 12 };
lineStartY = (++lineStartY).toString();
var hasImages = false;
var fileData = {};
Object.keys(filesObj).forEach(function(fileId) {
hasImages = true;
var fileUrl = filesObj[fileId];
//1) load all image data first
getBase64Image(fileUrl, function(base64image) {
fileData[fileId] = base64image;
//2) check if all images are loaded
if (Object.keys(fileData).length == Object.keys(filesObj).length) {
//3) then set cell values
process();
}
});
});
if (!hasImages) process();
function process() {
var trXIdx = lineStartX;
var tdYIdx = lineStartY;
lines.run().each(function(line) {
line.columns.forEach(function(c) {
if (settings.excludeColumnLabels.indexOf(c.label) == -1) {
var value = line.getValue(c);
if (hasImages && value && lineImgColumns.indexOf(trXIdx) != -1 && !!fileData[value]) {
ws.getRow(tdYIdx).height = settings.lineImageCellSize.height || 50;
ws.getColumn(trXIdx).width = settings.lineImageCellSize.width || 50;
var fileId = line.getValue(c);
const tdImage = workbook.addImage({ base64: fileData[fileId], extension: 'png' });
ws.addImage(tdImage, trXIdx + tdYIdx + ':' + trXIdx + tdYIdx);
}
else {
ws.getCell(trXIdx + tdYIdx).value = formatValue(line, c);
ws.getCell(trXIdx + tdYIdx).alignment = { vertical: 'middle', horizontal: 'center' };
if (settings.lineColumnsToWrapText.indexOf(c.label) != -1)
ws.getCell(trXIdx + tdYIdx).alignment.wrapText = true;
}
trXIdx = String.fromCharCode(trXIdx.charCodeAt() + 1);
}
});
trXIdx = lineStartX;
tdYIdx = (++tdYIdx).toString();
return true;
});
//lineStartY = (++tdYIdx).toString();
//write file in buffer
workbook.xlsx.writeBuffer().then(function(buffer){
saveAs(new Blob([buffer]), tranId + '.xlsx');
a.close();
}).catch(function(err){
console.log(err);
a.close();
alert('Error generating excel file. Please contact admin.');
});
}
}
else {
alert('No saved search source set. Please contact administrator.')
}
}
};
});