Feature Request: update the "Export Data Store Entity to Excel" smart service to accept an Excel Base Template

Certified Lead Developer

The Export Data Store Entity to Excel smart service has many advantages including directly handling paging for large data sets, however it is lacking some major important features that prevent it from being the replacement for Export SQL to Excel or Export CDT to Excel (from the Excel Tools plug-in) that it was intended to be.

1) The biggest of these by far is its inability to accept an Excel file as a base template.  So far this lack has made this new feature useless to me, personally, for any of my projects because they all require the features that can only be added via a base template: namely, the ability to format certain things in advance.  That includes pre-setting column widths, and setting column headers with filtering already in-place.  See screenshot for an example of both of these:

It seems to me as if this might be an easy addition to the current smart service since it wouldn't override or break any existing functionality if users are using it *without* the base template already. Of course, I can't speak for what's going on in the Appian internals that might make this harder than I think.

2) Another thing that's missing from, say, the things we were able to do with Export CDT to Excel, is the ability to do some Appian-side transformation to data prior to it being written to Excel.  In many cases, for example, end users don't know or care about usernames, but instead would want to see the user's plaintext name i.e. the result of rule!APN_displayUser( pv!username ), or similar.  That is not a transformation that can always be done via the database, not even necessarily in a view since not everyone keeps user plaintext names in the database.  Another example would be the ability to reformat dates, for instance from a datetime down to an ISO-compliant date string (YYYY-MM-DD), or whatever other format we might like.

So I'm not sure how feasible this might be or how it might be accomplished using the current configurations of the smart service or any seamless additions, but if someone could figure out a way to allow us to pass in certain column names and an expression that should be applied to the value of each row of that column, that would also be amazing.  I would consider EDSEtE feature-complete if both of these were added.

[edited (2020-03-12) to add:]
3) A third use issue has occurred to me recently after seeing a post on Community: the DSE export has no functionality by which the results can be sorted.  No matter what you do, the results are in whatever order they would default to from the database.  To me this would almost never be the desired outcome, and being told to built a custom view for each individual use case (as opposed to just being able to pass in a sortInfo parameter somewhere in the smart service call) seems like a major unnecessary inconvenience.

Everyone please let me know what you think and whether there are any other (feasible, generally-usable) wishlist items I should've included.


  Discussion posts and replies are publicly visible

Parents Reply
  • 0
    Certified Lead Developer
    in reply to Stefan Helzle

    A side-by-side comparison to 21.3 shows no actual change to these inputs (unless i'm really missing something); presumably the thrust of the new update is that it now no longer destroys all prior contents of a sheet when that sheet is updated with data, which it did before.

    I'm sorely disappointed that they missed this chance to stir in a sorting input and "base template".  I know a "data transform by expression" is a real stretch goal (i'm unsure how it would work but I assume there's a way), but as Chris just pointed out, there are just too many use cases where this was easy to do with CDT to Excel and hard-to-impossible here, even with a custom view.

    Edit: I just ran a quick test of the new version and it does essentially what I expect.  It still requires you to overwrite an existing file via "update", but at least now it does in fact preserve formatting at least as far as pre-set column widths, and anything speically formatted in rows above the starting cell.  For my example I used one of my standard report templates which doesn't include anything overly fancy, but does include a Header Row with pre-set widths, bolded column labels, and pre-applied data filter capabilities on the columns.  (Actually, interestingly, while the formatting is kept it looks like the "column filtering enabled" feature is lost in the resulting document.  Screenshot below, comparing before-and-after.)

No Data