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.
Thanks!
Discussion posts and replies are publicly visible
Hi Mike,
We are trying to evaluate adding support for basic Excel templates which are likely to address the requirements you mention above. However, support for macros and formulas, which require cell based logic would not be supported.
As for your second request, I recommend using the Export Record List functionality as that allows you to export the same format you see on the record list itself. I don't expect Appian to work on this functionality for out Export to Excel smart service in at least one year.
It'd be great if you could open a use case and attach the Excel template you are planning to use so we have a better idea of your requirements and consider them when making changes to out Export to Excel smart service.
Best regards,
Jupiter Munoz
Principal Product Manager
Floating this back to the top as we have yet another major version release coming up and no sign of any remediation for this issue, as well as yet another community post confused as to why it's not working for them.
I understand there is a need for this functionality and we have it in our radar.
That being said, this has not been prioritized due to other competing priorities. One of our devs. is looking into it on his own time but we currently don't have a timeline for this enhancement.
Thanks for your feedback and will provide an update when appropriate.
Has there been any update on this? Just, curious to know. As we have also encountered this as a blocker for one our requirement.
No update at this time. I will provide an update when appropriate.
Thanks.
Hi ,
Is there any update on this Plugin ?
In 21.4 they've updated it slightly, it seems - such that now when overwriting an already-existing excel file, formatting will no longer be destroyed in rows above the "starting row". My major qualm here is that there's still no "base template" functionality and thus, presumably, it's impossible to have the node create a new file instead of overwriting the existing one you point it at, which is useless for almost all of my potential use cases.
This is how the 21.4 version looks like. Did not test it yet.
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.)