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.
Export DSE to Excel notoriously does not support base templates. I have no idea why, as this was a critical feature to me in the predecessor, the "Export CDT to Excel" or "SQL to Excel" nodes, found in the Excel Tools plug-in (which, btw, still works even though it's "officially" deprecated for quite some time now). I have been trying to get them to fix this for some years now, but since they're moving away from the DSE architecture in general, I'm not expecting much.
There is now a relatively new plug-in called "JSON to Excel" which does provide the ability to use a Base Template again - I suggest you try this out and see if it works for you.
Mike Schmitt - 'Export DSE to Excel' is what we using and it is doing the job quite well to fill in the templates other than the few issues what I mentioned above - Like loosing formatting, integer -string conflict and ability to delete sheets.We are trying to work around them as they are not deal breakers at this point of time.
We are planning to promote this to PROD in a month.
Technically we are not using the data in the DSE to export to excel.
We are using a dummy DSE with no records and A800 as the starting row. But we provide an excel template and send array of values for 'Custom Cell Positions' and an array of cell values to 'Custom Cell Values' parameters to match our templates. This does work and we are testing this for the past 4 months.
Let me know if see any sooner you will see a risk of them stop working. (Hope it doesn't)
padmanabant852500 said:. But we provide an excel template and send array of values for 'Custom Cell Positions' and an array of cell values to 'Custom Cell Values' parameters to match our templates.
Then in that case you might try experimenting with the SQL to Excel or the CDT to Excel nodes found in the Excel Tools plug-in, as they (iirc) have the same "custom cell values" functionality but WON'T overwrite base template files (plus the bonus that you don't need to make a spare copy of your template first, because they actually make their own new copy when fed a base template, which is how it should work anyway).
Thanks Harsha - We did tried the options you mentioned in option '2'. That doesn't look helping.