My client was looking for a way to export a list produced by a reporting engine to a Microsoft Excel® spreadsheet file (.xlsx). Other applications in the company have made use of an Excel Export control, so I thought I’d better figure the thing out.

As far as clues on how to configure the control, all I really had to go on was this blog post by Velocity IT, a technology services company in the UK. If you don’t have the control yet, their post should give you a leg up on how to register it on your K2 servers. Thankfully, it was already installed in each of my environments.

Full Disclosure: Obviously, my client didn’t provide me with any information on how to implement the control. But even after finding Velocity IT’s post, I didn’t think to look on the server for notes or a setup guide or a FAQ. I should have invested more effort. I could have searched the K2 Community for guidance, too, and maybe would have found a post from the original developer. I didn’t; at that time, it just didn’t occur to me to do these things. I combed through Google search results instead — it’s how I found Velocity IT’s blog post.

I was grateful for Velocity IT’s guidance, but I thought the post was a little too brief when describing how to implement the control on a list view. It felt like somebody rushed through that part of the post. Having done it myself now, I submit it’s not straightforward. I feel there is much more to discuss. So my focus is to offer a walkthrough on how to configure it in a SmartForms solution.

High-Level Overview

If your assumption is like mine, in that you expect the control will export the list in the view, you too would be mistaken. It’s more accurate to say that you’ll be using an ADO query to duplicate the T-SQL, PL/SQL, or whatever other technology that produced the list, and sending the results from that ADO query to the Excel Export control.

The Excel Export control will then produce an .xlsx for you as a record inside of a SmartBox in the K2 database. Through SmartForm rules, you’re going to place that output into the File Attachment control, which will present it as an .xlsx file.

Okay. Here it is, step-by-step.

Add the Controls to the View

To use the Excel Export on a K2 SmartForms List View, you must add the following controls to the view:

• A button or toolbar button
• The File Attachment control
• The Excel Export control

Yes, you’ll need all three. Add them to your view canvas using the Controls menu at upper right.

Configure the Button

The button’s “When the control is clicked” rule is where all the magic happens.

Execute a Method on the Excel Export Control

Start by selecting the “Execute a control method” action and selecting the Excel Export control as the target of that action.

The Create Excel File() method should automagically appear once you’ve selected the Excel Export control.

A little advice at this point. Before you continue to configure this action, you are going to want to make sure you have an ADO query standing by to populate one of two inputs.

The Excel Export control expects that the method you used to populate your list is through a List() method on a SmartObject. You may find you’ll need to actually create a SmartObject for your data source and create a List() method for it.

(Here’s what I mean by that: my report engine essentially allows the user to build a T-SQL WHERE clause, which gets passed into a stored procedure, appended to a SELECT query, and executed using SQL Server’s ExecuteSQL function. The SmartObject for that procedure takes two inputs — the identity of the data source and the entire clause, as a string. In contrast, the SmartObject query in the control expects a straightforward, standard query, with properties that can be used as inputs and returned (e.g., “SELECT ID, NAME WHERE ID = 7“). So, if your SmartObject doesn’t point directly to the data source and have these standard properties, you may need to create one that does.)

Another important note is that ADO will make conversions to column names or aliases that T-SQL would otherwise treat as a literal. In other words, while your Microsoft SQL database table or view might have a column you have aliased as [Brandy Snifter], ADO is going to make that column name “Brandy_Snifter”. Since those two strings are not the same, attempts to query “[Brandy Snifter]” via ADO will fail. In reality, you’ll click the button and browser will hang — at least, that’s what happened in my case.

So my recommendation is to fire up the Smart Object Services Tester, and run an ADO query against the data source SmartObject. The tester will show you what a default query looks like — A SELECT * query, I think. Execute it and examine the column names. Take note of any conversions you may have to account for in your query.

Okay. Back to the rule:

The object also has a property called “Name.” I assume this is supposed to be the name of the Excel file it creates…. except it doesn’t appear to work the way I thought it would:

I’ll need more time to figure this out. Moving on…

The Output Properties shows the Create Excel File() method has an output called Execution result, which is a number — it’s actually the ID of the record it just created in the ExcelFile SmartBox, AKA the dbo.ExcelFile table inside of the K2 database (see screenshot above).

You’re going to need to persist this ID someplace on the form, because in the next action, you’re going to use it to load the file into your SmartForm (more specifically, into the File Attachment control). I chose to create a parameter for it, but flavor to taste:

Close out of the dialog to return to the rule. Your first action is configured.

Execute the Load() Method on the ExcelFile SmartObject

Add an “Execute a Smartobject method” action, and identify the ExcelFile SmartObject as the target of that action:

The Load() method has a single input — and it’s the ID that was just returned from the Create Excel File() method:

The Load() method returns the actual Excel file. So map the Excel return property to the File Attachment control:

At this point, you have just returned the Excel file to the list view for the user to click on and view in Microsoft Excel.

Optional: Delete the File from the SmartBox

From the “Be Kind and Rewind” archive… now that the user is looking at their data in an .xlsx, you might consider deleting the file from the SmartBox. Simply configure a call to the ExcelFile SmartObject just like the one you made above, but calling the Delete() method (instead of Load()).

Like the Load() method call, you’ll simply pass in that ID you received from the Create Excel File() method.

Finally, in the output mappings of this method call, you might use the opportunity to blank or set to zero whatever control or parameter you were using to hold that ID.

If the field or parameter is configured as a number, you must set a numeric value to avoid runtime exceptions.

Record Limit?

There’s one last thing about the Excel Export control I wanted to mention. Other developers at my client have warned me that the control is great for creating .xlsx files of under about 5,000 records (an estimate). I’ve been told that once you hit about that many records, the control begins to fail.

I’ve not yet seen this behavior, but then I’m returning tens or maybe hundreds of records at most.

To be fair, I don’t know anything about the data others are attempting to return, or what the cause of such failures is. But I do know that expecting a Web application to put over 5,000 records into a spreadsheet for you seems a little unreasonable to me. I mean, who is going to want to manipulate 5,000 row spreadsheet?

Maybe it’s just me, but I think I’d rather limit the users to a particular albeit generous number of exportable records to try to leave more processing and memory available for other users, and ask them to contact us for queries that are expected to return more than the limit. How would I do this? By making a SmartObject call to return a count before calling the Create Excel File() method.