This article is relevant if you are looking for a way to dynamically generate complex Excel spreadsheets from the NetSuite platform.

Background

One of the nice things NetSuite offers is a way to have both saved search and reports export documents to Excel via the standard NetSuite user interface. However, there are times that we want to produce Excel documents that are not shaped in the same manner as the saved search or report.

I have been working with the NetSuite platform since 2008 when I decided to focus my firm on NetSuite Integration Services. Before NetSuite, my firm specialized in business system solutions centered on Microsoft technologies. In that previous era, we built a number of server-based web systems that generated Excel documents. We called it “Excel on the Server” because we could manipulate Excel via an API without a user interface to deliver Excel document.

NetSuite has no built-in server-based mechanism to utilize the Excel Server API and thus create spreadsheets under our old approach. For the longest time, to generate server-side Excel documents, I thought it would require an “off-platform” approach. Yet, I am very pleased to say I was wrong!

The NetSuite Excel Spreadsheet Generation Breakthrough

For many of us, when we open up an Excel XLS document generated automatically by NetSuite, we receive a warning message. Not surprisingly, we may believe that warning is about using a newer version of Excel that natively uses the XLSX format that was introduced in Office 2007.

However, there is more going on here that is not obvious.  NetSuite natively produced Excel spreadsheets are actually Excel 2003 XML documents with an XLS file extension. When Excel loads the XLS spreadsheet, it reads the contents of the XML document and presents the workbook. You may notice, when you try to save the document, Excel may present the XML type filename extension. While I noticed this in day-to-day use of NetSuite, it wasn’t until Boban D., a senior member of our team, pointed out to me that the Excel document is actually XML giving us a chance to generate this content server-side.

How to Create Complex Excel Spreadsheets in NetSuite

Once Boban D. shared that to me, the connection between my day-to-day experience and the possibility for generating Excel spreadsheets “on platform” was at hand.  I was quite electrified!

Right around the same time, a prospective client came to us enthusiastically about the ways we could connect and link up multiple saved searches together (a different challenge that many suffer from). Existing readers know about our free tool, Content Renderer Engine, which we give to our clients to help solve their most challenging saved search and other NetSuite output requirements.

The prospective client wanted the ability to connect multiple business tables together but they needed to also have their results outputted into a multi-sheet Excel workbook that required no-hands post-delivered manipulation and which could be distributed via email to their customers on a scheduled basis.

Because XML is a well-defined formatted string format, we knew we could solve their challenge. And we did!  Thus, the key to this challenge is to use NetSuite to read saved searches and then output XML. The normal approach then is to use NetSuite Advanced PDF/HTML templates.

Challenges with NetSuite Advanced PDF/HTML Templates

Without SuiteScript programming, NetSuite gives us a way to call an Advanced PDF/HTML template when we execute a saved search and then indicate we want to Print.  So why not supply an Excel XML template instead of a PDF template to the engine?  Unfortunately, at the time of this writing, NetSuite throws an error when you supply an Excel XML template definition because it assumes that the output is going to the PDF generator. Remember, NetSuite has two stages when it works with the Advanced PDF technology:

1. Stage 1 Freemaker: Take the saved search and hand it to the Freemarker template engine allowing you to transform data into string output.  Thus, this stage allows us to create the Excel XML.
2. Stage 2 (B)ig (F)aceless (O)utput: take the results of Stage 1 and hand it to BFO which expects well-formatted XML.  This stage appears to cause NetSuite to throw an error.  Bummer.

Yet, in theory, it may be possible to use the HTML output style for other non-saved search documents so that you can get your Excel 2003 XML.   However, this is child’s play for us using our Content Renderer Engine that effectively opens up NetSuite’s FreeMarker engine (or Trimpath) and allows us to specify the file name and extension we wish to use. Thus, it is indeed entirely possible to generate an Excel Spreadsheet on the platform — with some assistance.

Video Example (5:14) of NetSuite Excel Spreadsheet Generation

The following video (5:14) is an interview between me and Mike I., one of our consultants, showing how we used the Content Rendering Engine to create the Excel documents.

Get Help Generating Microsoft Office Documents from NetSuite

Given that there is an XML document version for the Office products, we have the means to create Office documents in NetSuite, whether they are Excel, Word or PowerPoint. We will though be subject to the limitations of the Office XML format. In our experience, we were not able to automatically generate an Excel macro or include a picture (image) data. But we were able to create complex spreadsheets with formulas, references, and formatting that typically would solve most users’ requirements.

My instinct tells me that if we push hard enough on the built-in tools provided by NetSuite, we probably could get an Excel spreadsheet out. But in the meantime, since we offer our Content Renderer Engine to clients without a license charge, we can unlock the potential today. The tool is easy to learn if you are experienced with NetSuite Saved Search and Advanced PDF/HTML templates.

If you found this article valuable, feel free to sign up to get notifications of new articles. If you have a situation that demands an Enterprise solution, let’s have a conversation.

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube