Export Data Store Entity to Excel Smart Service

Overview

The Export Data Store Entity to Excel Smart Service allows designers to safely export large datasets.The exported data can then be imported into other third-party applications.

The Smart Service returns a document in an Excel format.

Properties

• Category: Document Generation

• Icon:

• Assignment Options: This smart service can be either attended or unattended (default).

Node inputs

Node outputs

a!exportDataStoreEntityToExcel()

Exports the selected data store entity to Excel. This function will only execute inside a saveInto or a Web API.

Syntax

a!exportDataStoreEntityToExcel( entity, selection, aggregation, filters, documentName, documentDescription, saveInFolder, documentToUpdate, includeHeader, sheetName, sheetNumber, startingCell, customCellPositions, customCellValues, onSuccess, onError )

Parameters

Returns

Document

Example

Copy and paste an example into an Appian Expression Editor to experiment with it.

Replace the ENTITY and FOLDER with the appropriate constants for your application.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
a!localVariables(
  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!ENTITY,
              documentName: "Excel Export " & now(),
              saveInFolder: cons!FOLDER,
              onSuccess: a!save(
                local!exportDocId,
                fv!newDocument
              ),
              /*This displays an error if there is an issue executing the save*/
              onError: a!save(
                local!errorMessage,
                "Error Exporting File to Excel"
              )
            )
          }
        )
      ),
      {}
    ),
    if(
      /*This only displays the download link if a valid document was created*/
      not(isnull(local!exportDocId)),
      /*This changes the link to a download link for the newly created document */
      a!linkField(
        links: a!documentDownloadLink(
          label: "Download Excel File",
          document: local!exportDocId
        )
      ),
      a!textField(
        value: local!errorMessage,
        readOnly: true
      )
    )
  }
)

Usage considerations

The following configurations and expected behavior apply when using the Export Data Store Entity to Excel Smart Service from the Process Modeler:

• To export fields in a specific order, make sure to specify the Selection parameter. Otherwise, the order of the exported fields will not be guaranteed.
• You may specify either the Selection parameter or the Aggregation parameter but not both. If you do not specify either parameter, all fields are returned.
• You may specify either a LogicalExpression or a list of Filters in the Filters parameter but not both. If you wish to specify only one condition, specify a Filter. If you wish to specify more than one condition, use LogicalExpression or use a list of Filters.
• When using fields of type Date or Date and Time in a filter and the filter's value uses either the todate(), now() or today() functions, make sure to cast their value using the appropriate type. For example: cast('type!{http://www.appian.com/ae/types/2009}Date', today()), or cast('type!{http://www.appian.com/ae/types/2009}Datetime', now()).
• To create multiple tabs in the same Excel spreadsheet with data from different data store entities, use multiple nodes of this smart service. You can use the Document to Update and Sheet Number parameters when exporting a second data store entity.
• To export your data to a preformatted spreadsheet, use the Document to Update and Starting Cell parameters. Learn how to export data to a formatted Excel sheet.

Exporting data to a formatted Excel sheet

It’s common for businesses to use standard, preformatted spreadsheets to display and share data. For instance, a preformatted spreadsheet may have a cover page with different font size and colors; instructions above a table where data will be added; formatted table headers; or a specific column width.

When you use this smart service, you can determine the cell in an existing spreadsheet where the output should begin. This is called the Starting Cell parameters. Once you specify the Starting Cell, any cells above this point will keep their original formatting, such as alignment, font, border, fill options, column width, and frozen rows and columns.

To export your data to a preformatted spreadsheet:

1. Create a spreadsheet with any necessary information and formatting. This will be the original spreadsheet where the data will be exported to.
2. Configure the smart service, and set the following parameters:
   • Set the Document to Update parameter to the spreadsheet you just created.
   • Set the Starting Cell parameter to the appropriate cell. Any cells that start at or below the Starting Cell will not maintain any formatting.
3. Execute the smart service. This will create a new version of your spreadsheet with your exported data and formatting.

Supported Excel formatting

The following table shows the formatting options supported by this smart service.

Generic formatting options apply to all data in a given sheet; whereas cell formatting options only apply to any rows that are not updated by these smart services (i.e. custom cell positions/values, starting cell).

Unsupported Excel formatting and elements

The following list displays Excel options that are not supported by this smart service:

• Charts
• Excel formulas
• Filters on columns
• Formulas
• Images
• Links
• Macros
• Any formatting options that are not listed above

Avoiding Query Timeouts when Exporting Data

Exporting data store entities from either record lists or smart services relies on sorting the entity by its primary key. This sorting ensures that data is exported consistently—that is, all rows are exported and no row is exported more than once.

Appian export operations use small batches to control the amount of memory consumed. The last row of a batch is tracked and used to determine where the next batch starts. By managing load this way, we avoid high memory consumption when exporting entities with a very large number of rows.

Designers should adhere to Database Performance Best Practices when creating their schema relationships. Because database views do not have a primary key, or the ability to have indexes, Appian recommends exporting data only from tables or materialized views in order to avoid query timeouts with large data sets.

Related objects

Data Types:

• Selection
• Aggregation
• Query Filter
• Logical Expression

Feature compatibility

Feedback