Hi There
I have the following code which exports the search results into a table in Excel.
That part works fine.
However, I am trying to add an image from the filing cabinet which is resulting in the file becoming corrupted.
Why is that? Is it not possible to add an image file ?
/**
*@NApiVersion 2.x
*@NScriptType Suitelet
*/
define(["N/file", "N/encode", "N/search"], function (file, encode, search) {
function onRequest(context) {
if (context.request.method == "GET") {
// Load the logo image file
var logoFile = file.load({
id: 8143 // Replace with the internal ID of the logo file
});
// Get the Base64-encoded image data
var logoData = logoFile.getContents({
encoding: file.Encoding.BASE_64
});
// Get the width and height of the image
var width = logoFile.width;
var height = logoFile.height;
// Create the XML string for the Excel file
var xmlStr =
'<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>';
xmlStr +=
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" ';
xmlStr += 'xmlns:o="urn:schemas-microsoft-com:office:office" ';
xmlStr += 'xmlns:x="urn:schemas-microsoft-com:office:excel" ';
xmlStr += 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" ';
xmlStr += 'xmlns:html="http://www.w3.org/TR/REC-html40">';
// Add the logo to the XML string
xmlStr += '<Drawing ss:Dpi="96" ss:GraphicFrameLocks="0
xmlStr += '<Drawing ss:Dpi="96" ss:GraphicFrameLocks="0">';
xmlStr += '<x:Anchor>';
xmlStr += '<x:Ext>';
xmlStr += '<x:Parent>';
xmlStr += '<x:Row>0</x:Row>';
xmlStr += '<x:Column>0</x:Column>';
xmlStr += '</x:Parent>';
xmlStr += '</x:Ext>';
xmlStr += '<x:Client>';
xmlStr += '<x:MoveWithCells/>';
xmlStr += '<x:SizeWithCells/>';
xmlStr += '</x:Client>';
xmlStr += '</x:Anchor>';
xmlStr += '<x:Graphic>';
xmlStr += '<x:GraphicData uri="http://schemas.openxmlformats.org/drawingml/2006/picture">';
xmlStr += '<x:Pic>';
xmlStr += '<x:BlipFill>';
xmlStr += '<a:Blip r:embed="' + logoData + '"/>';
xmlStr += '<a:Stretch>';
xmlStr += '<a:FillRect/>';
xmlStr += '</a:Stretch>';
xmlStr += '</x:BlipFill>';
xmlStr += '<x:SpPr>';
xmlStr += '<a:Xfrm>';
xmlStr += '<a:Off x="0" y="0"/>';
xmlStr += '<a:Ext cx="' + width + '" cy="' + height + '"/>';
xmlStr += '</a:Xfrm>';
xmlStr += '<a:PrstGeom prst="rect">';
xmlStr += '<a:AvLst/>';
xmlStr += '</a:PrstGeom>';
xmlStr += '</x:SpPr>';
xmlStr += '</x:Pic>';
xmlStr += '<x:Size cx="' + width + '" cy="' + height + '"/>';
xmlStr += '</x:GraphicData>';
xmlStr += '</x:Graphic>';
xmlStr += '</Drawing>';
// Add a row to hold the logo above the table
xmlStr += '<Row ss:AutoFitHeight="0" ss:Height="' + height + '">';
xmlStr += '<Cell>';
xmlStr += '<Data ss:Type="String">' + logoData + '</Data>';
xmlStr += '</Cell>';
xmlStr += '</Row>';
// Create a saved search to retrieve the data that you want to include in the Excel file
var mySearch = search.create({
type: "purchaseorder",
filters: [
[
"status",
"anyof",
"PurchOrd:D",
"PurchOrd:F",
"PurchOrd:B",
"PurchOrd:E",
],
"AND",
["mainline", "is", "T"],
"AND",
["type", "anyof", "PurchOrd"],
],
columns: [
search.createColumn({ name: "internalid", label: "Internal ID" }),
search.createColumn({
name: "trandate",
sort: search.Sort.ASC,
label: "Date",
}),
search.createColumn({ name: "location", label: "Location" }),
search.createColumn({ name: "tranid", label: "Document Number" }),
search.createColumn({ name: "entity", label: "Name" }),
search.createColumn({ name: "memo", label: "Memo" }),
search.createColumn({ name: "amount", label: "Amount" }),
],
});
// Run the search and process the results
mySearch.run().each(function (result) {
var internalId = result.getValue({ name: "internalid" });
var productFeature = result.getValue({
name: "location",
});
// Add a row to the XML string for each search result
xmlStr +=
"<Row>" +
'<Cell><Data ss:Type="String">' +
internalId +
"</Data></Cell>" +
'<Cell><Data ss:Type="String">' +
productFeature +
"</Data></Cell>" +
"</Row>";
return true; // return true to continue processing the search results
});
xmlStr += "</Table></Worksheet></Workbook>";
// Encode the XML string and create the Excel file
var strXmlEncoded = encode.convert({
string: xmlStr,
inputEncoding: encode.Encoding.UTF_8,
outputEncoding: encode.Encoding.BASE_64,
});
var objXlsFile = file.create({
name: "sampleExport.xls",
fileType: file.Type.EXCEL,
contents: strXmlEncoded,
});
// Optional: you can choose to save it to file cabinet
// objXlsFile.folder = -14;
// var intFileId = objXlsFile.save();
context.response.writeFile({
file: objXlsFile,
});
}
}
return {
onRequest: onRequest,
};
});