Export Data Store Entity to Excel - Excel file column wrap and header row color

Question

Hi, 
 I am using "Export Data Store Entity to Excel" smart service to to generate an excel file. When I have more content in any of the column, column is not wrapping. 
 Is there a way we can set the column to wrap the content. Also, is there a way to add a color to the header row.

Sanchit Gupta (Xebia) · Answer

Well yes there is a way. 
 For that, you need to create an excel template in which you will add the headers manually (all the fields of entity in the same order as in generated document) and color you want in the header row and format the wrapping of desired columns. Then upload that document in the appian folder. 
 Now when you are using Export DSE to Excel service you will provide this template document in documentToUpdate input and save this node input in a pv say pv!docTemplate and for startingCell provide "A2" and set IncludeHeader to false. Note that you will not be able to create new document now but the new version of this same document will be created and the name will also be same. 
 Now use Copy Document smart service to copy the generated document from above (node output) with a new name which you wanted from your actual generated document and then save the generated document from this service in the same pv! which your provided in input 
 And then use Delete Document Version smart service to delete the new version that was created in Export DSE to Excel SS, this time use the pv!docTemplate to pass value in Document input (keep versionNumber empty, it will delete the latest version). This step is necessary as we need to delete the new version so that we can have our Excel template back which will be used next time. 
 
 Now, call this process model in saveInto of your Generate Document interface component using a!startProcess and onSuccess save the value of pv in which you saved the output of Copy Document SS in a local variable and at last use this localVar in documentDownloadLink to download the document. 
 Example code: 
 a!localVariables(
 local!generatedDocument,
 {
 a!buttonArrayLayout(
 buttons: a!buttonWidget(
 label: "Generate Document",
 saveInto: {
 a!startProcess(
 processModel: cons!PA_Export_DSE_TO_EXCEL,
 onSuccess: a!save(
 local!generatedDocument,
 fv!processInfo.pv.NewDocument
 )
 )
 }
 ),
 align: "START"
 ),
 a!linkField(
 links: a!documentDownloadLink(document: local!generatedDocument, label: "Download Doc" )
 )
 }
) 
 
 edit: Have a look at the comments below and change this approach slightly by copying the document template first and then just use that copied document in Export DSE to Excel SS.

Mike Schmitt · Answer

FYI, in my testing, I've always found that the Export DSE to Excel node overwrites anything (formatting and everything) found in the tab it's called upon in the source Excel file. Meaning this method (while reasonable to try) will not accomplish what OP is after. If you have evidence that this info is out-of-date though, please let me know - I haven't used this node very often thanks to the extensive and frankly painful feature-incompleteness it was shipped with, mostly relating to the inability to pass in a source template (and its subsequent inability to inherently make a direct copy). 
 BTW, I would suggest amending your recommended steps as listed above: it's far easier to create a copy of the original file first , then have the ExportDSE node reference the copied document. This avoids having to mess with creating and then deleting a new version on an original file which may be used a lot and may be important to not accidentally lose, and the method you outline opens up maybe a dozen different ways in which this could go wrong (like what if 2 different process instances try to do it at once?)