Export Data Store Entity to Excel Smart Service

Question

Hi All, 
 
 I'm using for the first time the Export Data Store Entity to Excel Smart Service to add data to an existing excel template (saved as a doc in appian) (Exporting data to a formatted Excel sheet). 
 Once I add the data to the excel it seems it adds it in the same row and erases anything on the same row that was there before, I'm trying to add the new data only in one column, different rows instead in one whole row. 
 Does someone know if this is possible or how I should be configuring the parameters.

customCellPositions

List of Text String

(Optional) Array of cell positions where custom cell values should be added.

customCellValues

List of Text String

(Optional) Array of values to be added on the specified custom cell position.

Or if you could recommend a different smart service to achieve this? 
 
 Thanks in advance

Mike Schmitt · Accepted Answer

My suggestion is to use a different smart service, one that accepts a Base Template, because even after years of complaining , they have not seen fit to update the Export DSE to Excel smart service with this most basic of functionalities. 
 I used to recommend installing the Excel Tools plug-in and using one of the (deprecated-but-still-working-perfectly-fine) original smart services, usually Export CDT to Excel . However there is now a more recent plug-in, " JSON to Excel" , which also takes a base template, and (unlike "Export CDT...") doesn't even require you to use a specific CDT to handle your export (meaning you're free to do data transforms as you wish prior to feeding the JSON array to the smart service).

Mike Schmitt · Answer

CDT to Excel is designed specifically to accept an array of CDT and output it to a series of rows of Excel data in a contiguous manner. That's the main usage anyway. But it does (also) let you populate some individual cells with arbitrary values, by passing in an array of cell positions and their values (the values can be anything expressionable, the CDT you use to populate the main part is irrelevant to this). 
 If you're *only* trying to write 3 arbitrary cell values to an excel sheet, then I must admit I'm a little confused why you're even bothering with an Excel doc. The use case for the "manual cell value" thing is usually to populate some hardcoded supporting data that gives some additional info about the report shown on the rest of the sheet.

Mike Schmitt · Answer

As far as I know it can handle any number of arbitrary field placements - the cell names and values are each just expressionable inputs and don't have a size limit per se, as long as they both match in length.

Mike Schmitt · Answer

You would need to feed in a CDT to get the node to work - I would suggest trying an arbitrary small (1-column perhaps) CDT and passing an either empty set, or a single row of blank data, to appease the node's requirements.

EREN_YEAGER · Answer

Hi, 
 You can use the parameters by passing customCellPositions (e.g., A1,A2,A3,...) and customCellValues (e.g., 1,2,3,...). This will place the data in the specified cell positions within the Excel file. 
 However, I'm not entirely sure whether this approach will override any existing content in those cells. It should insert the data as defined, but please test it to confirm the behavior in your specific case. customCellPositions: {"B2", "B3", "B4"}
customCellValues: {"Apple", "Banana", "Orange"}

Mike Schmitt · Answer

Then definitely try out the "CDT to Excel" node - it's marked as "deprecated" but as i mentioned, has continued to work anyway. It works perfectly with pre-formatted templates.

`customCellPositions`	List of Text String	(Optional) Array of cell positions where custom cell values should be added.
`customCellValues`	List of Text String	(Optional) Array of values to be added on the specified custom cell position.

Export Data Store Entity to Excel Smart Service

Top Replies