Setting up Excel templates

You can upload Microsoft Excel files to use as templates for data opened from grid widgets using the Edit in Excel plug-in. You can configure columns in worksheets with any standard Excel functionality, such as formulas and cell formatting.

Excel templates are uploaded to Supply Chain using either the Grid widget properties or the Data Management page.

Creating an Excel template

You can use any of the standard Excel functionality in your templates. Template elements often include the following:

• A header row
• Rows and columns set to fixed heights and widths
• Cell colors
• Text formatting and alignment, word wrap
• Borders
• Data types
• Formulas

As you develop your templates, test them in App Studio with your data. Use the following tips to ensure their appearance and functionality meets your requirements:

• Adjust the template configuration in the template, not in an instance of the spreadsheet generated using the Edit In Excel button. You will not be able to save spreadsheet configuration updates back into App Studio. To save changes to the spreadsheet configuration, update the template.
• If you apply data types to columns, ensure the data layout in the grid within App Studio matches the design of your template.
• Data from the grid is loaded into the selected Excel sheet starting from cell A1. If you define headers in your template, the data is added directly beneath them, beginning from cell A2.
• Formulas can be referenced across sheets.
• Templates can have more columns than what is included in the grid. However, no data from App Studio is mapped to extra columns.
• Default values for fields display when the data is opened in Edit in Excel, and are saved back to App Studio.

Configure a Grid widget to open data in an Excel template

1. If necessary, install the Edit In Excel plugin.
2. Upload an Excel template to the platform:
   1. On the Navigation toolbar, select Layout Designer .
   2. In the Layout Designer pane, in the Projects section, navigate to and select the app board holding the grid widget.
   3. In the upper right corner of the grid widget, select .

      The Grid Properties dialog box is displayed.

   4. On the Templates tab, do either of the following:
      • Click Upload Excel Template, and specify the Excel file.
      • Drag and drop the file onto the Drop file here area.
      You can also use the Data Management page to upload Excel files for use as templates.
   5. For Selected Sheet To Use, select the worksheet in the Excel file where you want the grid data to appear.
   6. Click OK.
3. Click Save.

   App users can now send data from the Grid widget into an Excel file that's created using the template.

Open data in an Excel template

1. In the Application Viewer, open an app containing a Grid widget configured to use an Excel template.
   The Edit in Excel button is only available in the Application Viewer.
2. On the Grid toolbar, select Edit in Excel.

   

   • Changes and additions to the data in a generated spreadsheet are only allowed if the grid is configured to allow editing.
   • Changes to the configuration of a spreadsheet generated using the Edit In Excel option cannot be saved back into App Studio. For example, if you change a formula in a generated spreadsheet, the change won't be saved back to the template. To save changes to the spreadsheet configuration, update the template.
   • Default values for fields display, and are saved back to App Studio.

Update an Excel template

1. In the upper left corner of the platform landing page, select Data Management.

   The Data Management page is displayed.

2. In the search box, type some search key words to find the template you want to update.

   The asset list updates to show all assets that match your search.

3. To the right of the name of the spreadsheet, select , and choose Replace.
4. In the Open dialog box, choose the replacement file, and select Open.

   The uploaded file replaces the spreadsheet.