Export and import excel data to and from a dictionary or CDT in the UI

Question

I have two requirements that I am not sure how to tackle: 1. I need to to export a datasubset, dictionary, cdt, or map to excel (basically data that is in appian, not in the db) in an interface. 
 2. I need to upload an excel spreadsheet, and extract the data to display in a grid in the UI. So I need to extract it to a datasubset, dictionary, cdt, or map. 
 Is there any OTB functionality or plugin that could achieve one or more of these requirements?

Chris · Accepted Answer

Included in the plugin is the function: fn!readexcelsheet() 
 This returns a dictionary that you can do what you want with - I'll post one of my rules that reads an excel doc (ri!doc) and casts to a CDT, which allows users to upload data from a SAIL interface: 
 a!localVariables(
 local!data: readexcelsheet(
 ri!doc,
 0,
 2
 ),
 
 if(
 not(local!data.success),
 {},
 reject(
 rule!isEmpty,
 a!flatten(
 a!forEach(
 items: local!data.result,
 expression: {
 if(rule!isEmpty(index(fv!item.values,1,"")),
 null,
 'type!{urn:com:gdit:types}SP_ExcelUpload'(
 EmployeeID: index(fv!item.values,1,""),
 Purpose: index(fv!item.values,2,""),
 ProjectCode: index(fv!item.values,3,""),
 Task: index(fv!item.values,4,""),
 ExpenseDate: rule!SP_readExcel_convertDate(
 dateVal: index(split(index(fv!item.values,5,"")," "),1,null)
 ),
 PayCode: index(fv!item.values,6,""),
 Amount: index(fv!item.values,7,""),
 DeptOverride: index(fv!item.values,8,"")
 )
 )
 }
 )
 )
 )
 )
)

Chris · Answer

You will want to review the Excel Tools plugin. Note for requirement #1, the only functionality available for this is the deprecated Export CDT to Excel service, which many of us still have in use as there has not really been a suitable replacement for it's functionality yet.

Export and import excel data to and from a dictionary or CDT in the UI

Top Replies