Hi everyone ,
I am using Export Data Store Entity to Excel smart service to get the document and stored it in Appian.In this, I have marked one of the column's visibility as false.
Now, when I try to read the excel sheet using readexcelsheet(), It's not giving me the hidden columns.Anyother way to hide the columns in document but retrieve it in readexcelsheet()
Discussion posts and replies are publicly visible
Try readexcelsheetpaging(). It returns hidden columns also. Also, can you confirm if the excel generated has that hidden column? If excel wont have the column no function can get the respective data.
Excel Doxument is generated using Export Data Store Entity to Excel. In this, under selection, the column is mentioned with visibility as false. Does this count as hidden column?
I have not tried this scenario myself. Can you open the generated excel and tell here if you see the column in excel or not
I have worked with excels where a column is hidden in the excel file itself! Like you will see column A, B and then D because C was hidden within excel file. In that case readexcelsheetpaging() still returned the data from column C. Thats why its important to verify the excel has the data before you try to read it.
In my case, the value is not hidden in excel it seems.Any idea on how to hide the column when we export to excel using Export Data Store Entity to Excel smart service?
You can give this idea a try and see if it works.
Have a blank excel template and upload in Appian. Have 2 sheets in it - hide sheet number 2 in the template itself.
In your process, use two Export data store entity to excel nodes. In first one pass the template to 'Document to Update' attribute. And also set up selection query here with all the columns except the one you want to hide.
Out of this first Export DSE to Excel will have all the columns except the 'hidden' one.
Now set up one for Export DSE to Excel node where you pass the output of first Export DSE to Excel node in the 'Document to Update' setting. In this node don't provide any selection. Importantly set the sheet Number to 2 here. So that the hidden sheet in your excel has all the column data.The output of 2nd node you can save in the same variable as first document. As you used 'Document to Update' the 2nd node will not create a new document but a new version of the first one itself.
This way users will see sheet 1 without one column. And you can read data from sheet 2 which will have all the column data. Here is a documentation for Excel formatting considerations.
Will try this out. Thank you
It worked with the Template concept Harsha. Thank you for your guidance.
Great! Glad it helped :)