首页 > 代码库 > NetSuite SuiteScript 2.0 export data to Excel file(xls)

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:

  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 = ‘<?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 : onRequest101             };102 103         });

 

NetSuite SuiteScript 2.0 export data to Excel file(xls)