Exporting to an excel template

I'm using Excel tools 2.2.6 to create excel documents from a template. I create a document where I have a table on the first sheet and a chart on the second sheet. What I want to do is to be able to dump the data in the first sheet so the graph is updated with the new data.

However, the problem is that when I export the data into the document, the formatting is lost and the charts are removed from the second sheet. 

Is it possible to preserve any existing configurations and charts within the template when exporting the data?

Here is the code I'm working with.

a!localVariables(
  
  local!data: rule!EXT_QE_getSampleData(id: null).data,
  local!exportDocId,
  local!errorMessage,
  
  
  {
    
      if(
        isnull(local!exportDocId),
        /*This link field contains the link that starts the export*/
        a!linkField(
          labelPosition: "COLLAPSED",
          links: a!dynamicLink(
            label: "Export to Excel File",
            saveInto: {
              a!exportDataStoreEntityToExcel(
                entity: cons!EXT_DS_SAMPLE_DATA, 
                
                documentDescription: "Data exported to excel", 
                documentToUpdate: cons!EXT_EXCEL_EXPORT_TEMPLATE,
                saveInFolder: cons!EXT_FOLDER_EXPORT_TO_EXCEL, 
                includeHeader: false, 
                sheetName: "Data", 
                sheetNumber: 1, 
                startingCell: "A2",
                onSuccess:  a!save(local!exportDocId, fv!newDocument), 
                onError: a!save(local!errorMessage, "Error Exporting File to Excel")
              )
            }
          )
        ),
        {}
      ),
      if(
        not(isnull(local!exportDocId)),
        a!linkField(
          links: a!documentDownloadLink(
            label: "Download Excel File",
            document: 
            local!exportDocId
          )
        ),
        a!textField(value: local!errorMessage, readOnly: true)
      )
    
  }
)

  Discussion posts and replies are publicly visible

  • 0
    Certified Lead Developer

    a!exportDataStoreEntityToExcel() is not in Excel Tools (as in the plug-in), but instead it is an out-of-the-box feature added somewhat recently.  For whatever reason, they decided to forego the ability to use an excel base template, like you could have done using the "Export CDT to Excel" smart service from the Excel Tools plug-in.  In your code above I see you're using the "documentToUpdate", but as far as I've been able to tell, all this does is overwrite that existing document with the unformatted output of the Data Store Entity output.  On that note, a word of caution: I expect your above code will probably overwrite the actual template file referenced by cons!EXT_EXCEL_EXPORT_TEMPLATE.  This is the expected behavior of the documentToUpdate parameter, anyway - let me know if this has not been your experience.

    In my opinion this is a big functional gap (see my previous feature request thread) and once again I'm disappointed that they're not addressing it in the next major release (as far as I can tell from the 20.1 webinar which took place yesterday).  The official word from Appian on this has been that if you have a required use case for the Export DSE to Excel functionality to be updated, then you should open a support case with Appian so they can log it with their engineers.

  • , thanks for your response. I didn't realize that was not part of the plugin. Looking at the available options under the plugin it seems they deprecated most functions including the most important one, export CDT to excel which I used in the past and made sense given the higher flexibility. 

    I looked at your feature request post and knowing Appian, this might not be resolved any time soon, if at all. 

  • 0
    Certified Lead Developer
    in reply to Jose H.
    they deprecated most functions including the most important one, export CDT to excel

    For what it's worth, even with the latest version of Appian and the latest version of the plug-in, the CDT to Excel and SQL to Excel nodes are still available and working, just marked "deprecated".  In my opinion these should be used with caution but can probably still be used when the use case requires, given that no functional alternative has been provided yet.

  • Hii could you please tell me how to add filters to excel so that what ever we are selecting that has to be show in the excel data to dwl