We are using 'Export Data Store Entity to Excel' smart service to write data into Excel files. We built a process model which copies an excel template and writes into the copy of the excel template to generate an excel file using this smart service.This smart service is pretty much doing a good job other than few issues which we are trying to find resolution.
Here are couple of questions if you can help with.
1) We notice that formats maintained in the Excel template is getting lost if we write some values into that specific cell. Is there a known way to avoid this? (Please see the screen shot attached) 2) Also - we notice, in the cells, if we write numeric values, the generated excel is comming up with this 'comment' icon in cell corner. We tried to write the value as numeric to the cell. But looks like still that is not fixing the issue. Is there a known way to avoid this? (Please see the screen shot attached)
3) Is there a way to delete a sheet from the excel in the process model?
Discussion posts and replies are publicly visible
1. You can apply conditional formatting formula to have cell formatting applied if the cell value is not null. I had faced similar issues in the past and configuring cell style conditionally have handled this issue without much hassle.
2. Wrap the data field in tointeger() when passing to the smart service or make sure you are using variable of type integer.
3. You can use Delete Excel sheet smart service from SQL to Excel Smart Service Plugin
Thank for your answers Harsha. Appreciate you taking the time to answer them.
Few clarifications.
1) We are using 'Export Data Store Entity to Excel' smart service. Do you mean doing conditional formatting in 'Excel template' itself?
2) For mapping a specific sheet in an excel template through the above smart service - we send an array or cell numbers to 'Custom Cell Positions' and an array of cell values to 'Custom Cell Values' parameters.
We do send the values as integers (in the list) in that array of values where integer value need to be printed. But still see the comment icon issue. Not sure how 'Export Data Store Entity to Excel' smart service is handling this in backend. So we are sending it as integers and it still showing the comments.
Feel free to let me know if I overseeing anything in your comments.
3) Let me try the SQL to Excel Smart Service Plugin to see if we can delete a sheet from a excel workbook
1. Yes add conditional formatting condition in the template itself to the cells impacted.
2. If you are sending integer then the issue may be in template. Is that cell marked as ‘General’ in excel template. Try saving that cell type to integer in base template if not already done.
Thanks Harsha - We did tried the options you mentioned in option '2'. That doesn't look helping.