NetSuite SuiteScript 2.0 export data to Excel file(xls)

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 += '<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         });

NetSuite SuiteScript 2.0 export data to Excel file(xls)

Share ScreenShoot:

High level view:

Sample in SuiteScript 2.0:

相关内容

公告