In NetSuite SuiteScript, We usually do/implement export data to CSV, that's straight forward:
- Collect 'encoded' string to Array for column, join them with comma ',' to be a string.
- Collect each line's data same as column to push to the Array.
- Join all the Array data(include column row and all data rows) with ' ' to a big CSV string.
- Save the CSV string as file content then store it to file-cabinet, or write them directly in SuiteLet as a output.
Today I am going to talk about export custom NetSuite data to EXCEL file(file suffix is .xls)
Share ScreenShoot:
High level view:
- Prepared XML header string. Put in styles as desire, and workbook -> worksheet -> table
- Concat to put in dynamic cell data. So we got whole well formed xml string.
- nlapiCreateFile(SuiteScript 1.0) or file.create(SuiteScript 2.0) put in encoded xml string to create a Excel file.
- Store the file to filecabinet or set it as output of a SuiteLet(so directly download it)
Sample in SuiteScript 2.0:
1 /** 2 * @NApiVersion 2.x 3 * @NScriptType Suitelet 4 * @NModuleScope SameAccount 5 * @author Carl, Zeng 6 * @description This's a sample SuiteLet script(SuiteScript 2.0) to export data 7 * to Excel file and directly download it in browser 8 */ 9 define( 10 [ 'N/file', 'N/encode' ], 11 /** 12 * @param {file} 13 * file 14 * @param {format} 15 * format 16 * @param {record} 17 * record 18 * @param {redirect} 19 * redirect 20 * @param {runtime} 21 * runtime 22 * @param {search} 23 * search 24 * @param {serverWidget} 25 * serverWidget 26 */ 27 function(file, encode) { 28 29 /** 30 * Definition of the Suitelet script trigger point. 31 * 32 * @param {Object} 33 * context 34 * @param {ServerRequest} 35 * context.request - Encapsulation of the incoming 36 * request 37 * @param {ServerResponse} 38 * context.response - Encapsulation of the Suitelet 39 * response 40 * @Since 2015.2 41 */ 42 function onRequest(context) { 43 44 if (context.request.method == 'GET') { 45 46 var xmlStr = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?>'; 47 xmlStr += '<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" '; 48 xmlStr += 'xmlns:o="urn:schemas-microsoft-com:office:office" '; 49 xmlStr += 'xmlns:x="urn:schemas-microsoft-com:office:excel" '; 50 xmlStr += 'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" '; 51 xmlStr += 'xmlns:html="http://www.w3.org/TR/REC-html40">'; 52 53 xmlStr += '<Styles>' 54 + '<Style ss:ID="s63">' 55 + '<Font x:CharSet="204" ss:Size="12" ss:Color="#000000" ss:Bold="1" ss:Underline="Single"/>' 56 + '</Style>' + '</Styles>'; 57 58 xmlStr += '<Worksheet ss:Name="Sheet1">'; 59 xmlStr += '<Table>' 60 + '<Row>' 61 + '<Cell ss:StyleID="s63"><Data ss:Type="String"> ID </Data></Cell>' 62 + '<Cell><Data ss:Type="String"> Products Feature </Data></Cell>' 63 + '</Row>'; 64 65 xmlStr += '<Row>' 66 + '<Cell><Data ss:Type="String">1</Data></Cell>' 67 + '<Cell><Data ss:Type="String">NetSuite Export CSV</Data></Cell>' 68 + '</Row>'; 69 70 xmlStr += '<Row>' 71 + '<Cell><Data ss:Type="String">2</Data></Cell>' 72 + '<Cell><Data ss:Type="String">NetSuite Export Excel</Data></Cell>' 73 + '</Row>'; 74 75 xmlStr += '</Table></Worksheet></Workbook>'; 76 77 var strXmlEncoded = encode.convert({ 78 string : xmlStr, 79 inputEncoding : encode.Encoding.UTF_8, 80 outputEncoding : encode.Encoding.BASE_64 81 }); 82 83 var objXlsFile = file.create({ 84 name : 'sampleExport.xls', 85 fileType : file.Type.EXCEL, 86 contents : strXmlEncoded 87 }); 88 // Optional: you can choose to save it to file cabinet 89 // objXlsFile.folder = -14; 90 // var intFileId = objXlsFile.save(); 91 92 context.response.writeFile({ 93 file : objXlsFile 94 }); 95 } 96 97 } 98 99 return { 100 onRequest : onRequest 101 }; 102 103 });