ANS-0652 · SUITESCRIPT DEVELOPMENT
How to Generate Custom Excel Reports in NetSuite?
Leverage NetSuite's scripting capabilities and XML-based Excel format to create dynamic, custom spreadsheet reports from saved search data.
Short answer
Custom Excel reports can be generated in NetSuite by creating an XML-based Excel file. This involves using the N/file module to create a base64 encoded file, potentially leveraging FreeMarker with saved search results for dynamic data, and saving it to the File Cabinet.
Scenario
Organizations often require custom Excel reports that go beyond standard NetSuite exports, needing specific formatting, dynamic data from saved searches, or embedded Excel formulas. The challenge lies in programmatically generating these complex .xls files within NetSuite and storing them for access.
Solution
Excel workbook (.xls) is a format that allows for the creation of an Excel file based on the XML language.
To create an Excel file programmatically within NetSuite, the N/file module is utilized. NetSuite supports the creation of Excel .xls files by script, allowing them to be saved in the File Cabinet under the "EXCEL" (application/vnd.ms-excel) file type.
The content of the file must be base64 encoded and must adhere to the Excel workbook format. While the workbook format documentation may not be extensive, it generally involves a structure akin to a large table, blending XML with HTML/CSS elements.
To generate the spreadsheet file, it is possible to use results from a saved search and FreeMarker to add dynamic data to the report. While NetSuite's native template renderer is primarily designed for generating PDF and HTML output, the core concept of generating Excel XML and using FreeMarker with saved search data remains plausible for custom solutions.
Furthermore, since the output is an Excel spreadsheet, it is possible to embed Excel formulas. It is important to note that formulas use relative positioning based on the current cell. For instance, if a user is in cell B12 and intends to sum cells A2 to A10, the formula would be:
SUM(R[-10]C[-1]:R[-2]C[-1])Expert NetSuite Support
Need help with this NetSuite issue?
SuiteScript Development consulting and configuration support