NetSuite SuiteScript 2.0 export data to Excel file(xls)-CarlZeng

Steps of implement export to excel file in SuiteScript 2.0

In NetSuite SuiteScript, We usually do/implement export data to CSV, that’s straight forward:

1. Collect ‘encoded’ string to Array for column, join them with comma ‘,’ to be a string.
2. Collect each line’s data same as column to push to the Array.
3. Join all the Array data(include column row and all data rows) with ‘\n\t’ to a big CSV string.
4. 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:

---

1. Prepared XML header string.  Put in styles as desire, and workbook -> worksheet -> table
2. Concat to put in dynamic cell data.  So we got whole well formed xml string.
3. nlapiCreateFile(SuiteScript 1.0) or file.create(SuiteScript 2.0) put in encoded xml string to create a Excel file.
4. 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 = ‘‘;
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 += ‘‘
54 + ‘‘ + ‘‘;
57
58 xmlStr += ‘‘;
59 xmlStr += ‘

‘
60 + ‘‘
61 + ‘ ID ‘
62 + ‘ Products Feature ‘
63 + ‘‘;
64
65 xmlStr += ‘‘
66 + ‘1‘
67 + ‘NetSuite Export CSV‘
68 + ‘‘;
69
70 xmlStr += ‘‘
71 + ‘2‘
72 + ‘NetSuite Export Excel‘
73 + ‘‘;
74
75 xmlStr += ‘‘;
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 });