Skip to main content
W
walkers0001
January 12, 2021
Solved

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

  • January 12, 2021
  • 3 replies
  • 0 views

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? 

Best answer by csteward

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

3 replies

csteward
csteward
January 13, 2021

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.  

W
walkers0001Author
January 13, 2021

Hi [mention:23a2ab7d11d24e69bfaa8e50e14d1914:e9ed411860ed4f2ba0265705b8793d05] 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?

csteward
cstewardAnswer
January 13, 2021

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

    Terms & ConditionsAccessibility statement