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

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? 

  Discussion posts and replies are publicly visible

  • 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.  

  • Hi thank you for your reply.  I did see that Export CDT to excel is now deprecated.  For requirement 2, I don't see any functionality that would allow me to import an excel, did you have a specific function in mind?

  • 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,"")
                  )
                )
              }
            )
          )
        )
      )
    )