Export Read Only grid with variable source to Excel

Question

Hi everyone, 
 
 I need to export the data contained in a Read Only grid with variable Data Source, therefore I cannot use the default Export functionality available for Record and entity based grids. 
 My grid can have two sources of data, one being the default columns I have on the report on which it is based and/or one or more columns that users can select with a UI we made. 
 The grid then can have a mix of Report and Database data source. 
 I tried the exportProcessReportToExcel function, but it only works with the data and columns set on the report. 
 
 I'm looking for a way to export to Excel the whole content of the grid, whether it is an OTB function or a plugin. 
 
 Thank you very much in advance.

Mike Schmitt · Answer

You might have some luck using the Export CDT to Excel smart service available in the Excel Tools plug-in , which while marked as Deprecated, still works in all recent versions that I'm aware of. You would call it from a process model (which you can call from your form), cast your data set (passed into the process however you like, like in a Map array) into a custom CDT you create (i suggest just creating a generic all-text CDT with columns named like "col1", "col2", etc), then passing that into the smart service. If you do these setup steps correctly then it shouldn't be too much of a lift overall.

Mathieu Drouin · Answer

I built one using https://docs.appian.com/suite/help/23.1/Export_Process_Report_Excel_Smart_Service.html . I used a dummy (empty) process report and used the Custom Cell Positions and Custom Cell Values to manually push to some values to it.

Mike Schmitt · Answer

The only suggestion I can offer is to try assembling the grid data before rendering the grid, instead of *while* rendering the grid - in other words, take whatever logic you're using to create the data row-by-row, and move it to a local variable (which you can then reference by your grid as well as passing into a process to handle document generation).

Mike Schmitt · Answer

Positions: {"A1", "B2", (etc) }, 
 Values: {"Value for cell A1", "Value for cell B2", (etc) }, 
 They can be literally anything you want, as long as the positions array is a valid list of cell identifiers like above, and as long as the values array is a valid list of string that matches the length of the "positions" array.

Mike Schmitt · Answer

I'm not sure what you mean by "fixed for all the rows" - are you referring to the Custom Cell Positions? 
 These are intended to be used such that the designer can insert arbitrary additional values, apart from the data set being used to generate the excel file - such as (for example) the name of the person who generated it, the date it was generated, or filter values. They are also totally optional and completely separate from the main data set that the generated file will reflect.

Custom Cell Positions	Text	Array of cell positions where custom cell values should be added.
Custom Cell Values	Text	Array of values to be added on the specified custom cell position