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:
- 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 ‘\n\t’ 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 = ‘‘;
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 + ‘
62 + ‘
63 + ‘
64
65 xmlStr += ‘
66 + ‘
67 + ‘
68 + ‘
69
70 xmlStr += ‘
71 + ‘
72 + ‘
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 });
