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 = ‘<?xml version=”1.0″?><?mso-application progid=”Excel.Sheet”?>’;
47 xmlStr += ‘