HTML Page with Link to Download Excel File - FME Flow App

Live Demo

Introduction

This FME Flow App provides a user interface |to stram and download a custom selection of data. The user can choose which year of job openings and which industries they are interested in to stream and download. Follow along with the provided data or try it with your own Excel data and workspace.

FME Flow App: Labor Market Outlook, BC

How it works

The app allows users to select the year of interest. With the Data Streaming service, the output data is streamed to the user's web browser as an HTML page. Additionally, the data is downloadable via the Data Download service using a webhook URL.

Step-by-Step Instructions

In this demo, the “jobsBC App” is an FME Flow App that lets citizens view the labor market outlook in British Columbia. To build an FME Flow App, a workspace must first be designed and published to FME Flow. 

The jobsBC.fmw workspace powers the “jobsBC App”. In this workspace, a Published User Parameter is used to define the year of interest, which is available as user input in the FME Flow App. When the user submits a year between 2021 and 2029, the workspace runs via FME Flow. In fact, the workspace is run twice by FME Flow. The jobsBC App runs the workspace for the Data Streaming service, which streams the HTML page. The webhook runs the workspace a second time to output the Data Download service zip file.  

FME Workbench: jobsBC.fmw

Part 1: Author Workspace

1. Download Sample File

Download the Samples zip file from Files. Open FME Workbench to create a new workspace. Drag and drop the Excel file (Job-openings-by-industry-and-occupation-for-bc.xlsx) onto the canvas.

2. Data Transformation

Clean up the data to align with the web application's purpose and how users will interact with it. For the data used in this demo, we want to remove the following attributes with the AttributeManager transformer: NOC, Variable, Geographic Area, 1st 5-year sum, 2nd 5-year sum, 10-year sum, and fme_feature_type

FME Workbench: AttributeManager Transformer Parameters

3. Filter Data

The “Description” attribute in the Excel file describes the Total for each industry and the occupation names. To keep it simple for end-users, we will only provide a high-level total count of job openings across different industries. With the Tester transformer, test for “Description” equals “Total”. This will reduce the total number of features from 11,506 to 60, meaning the user will be looking at 60 industries. 

FME Workbench: Tester Transformer Parameters

4. Create User Parameter

Add a second AttributeManager transformer to the canvas. Connect the Tester's Passed port to the input port of AttributeManager_2. In AttributeManager_2, add a new attribute, Job Opening. This attribute will use a User Parameter as its Value. Click on the drop-down arrow for Value, then expand User Parameter and click on Create User Parameter.

FME Workbench: AttributeManager_2, add a new attribute, and set the value to User Parameter JobYear

Set the Parameter Identifier to “JobYear”, the Prompt to “Enter Year of Interest (2021-2029), and leave the Default Value blank, then click OK. Once the JobYear User Parameter is created, click the drop-down arrow again, and select Open Text Editor. Add @Value() around the $(JobYear) parameter so that it looks like this:

@Value($(JobYear))

5. Data Management

Finally, with the AttributeKeeper transformer, click on the drop-down arrow and click Select Attributes to open a dialog box. Select the Industry and Job Opening attributes to keep.

FME Workbench: AttributeKeeper, Attributes to Keep 

Industry attribute values can be sorted alphabetically using the Sorter transformer if desired.

6. Create HTML Report

With the HTMLReportGenerator transformer, style the HTML page. In this demo, the page title is set to Labor Market Outlook BC with three page contents: Header, Table, and Custom HTML. To present the data in a different way, such as a pie chart or add images and maps, they can be added here as well. See the CUSTOM HTML content from the HTMLReportGenerator transformer in the sample jobsBC.fmw workspace as an example. 

For the table column settings, the Column Contents are set to Industry and Job Opening with their respective column names. To specify the year for the Job Openings, add the User Parameter JobYear to the column name: Job Openings for $(JobYear)

FME Workbench: HTMLReportGenerator, Page Contents Table

The webhook for the Excel file data download service will be embedded in the Custom HTML section, which allows greater access to styling HTML pages. Add the following into the Custom HTML by selecting Open Text Editor:

<p><a href="<YourWebhook>">Download Excel Sheet</a></p>

Note: the webhook will be created in Part 2.


FME Workbench: HTMLReportGenerator, Page Contents Custom HTML

7. Write Output Data

The last step to creating the workspace is to add the two writers, HTML and Excel. Connect the HTML writer to the HTMLReportGenerator transformer, then connect the Excel writer to the AttributeKeeper transformer. For this demo, the webhook will run the workspace and create a new Excel sheet for users to download based on the users’ year input. To ensure a new file is created, in the Drop Existing Sheet/Name Range of the Excel writer parameters, select Yes. Set User Attributes to Automatic.

FME Workbench: Excel Writer, Drop Existing Sheet

FME Workbench: Excel Writer, Attribute Definition Automatic 

Part 2: Create Webhook

1. Publish Workspace to FME Flow

Before publishing to FME Flow, save the workspace as jobsBC.fmw. Next, click the “Publish to FME Flow” button or select File > Publish to FME Flow from the menu bar. Connect to your FME Flow and publish the workspace into a new repository named “Demo”.

For details on how to create an FME Flow connection, please see below: 

• To publish a workspace from FME Workbench to FME Flow, follow the generic instructions below using the specific details listed in the article. If the article does not have a specific repository name listed, we recommend creating a Training repository. If a specific service is not listed in the article, please use the default Job Submitter.

  1. Click Publish

  From the File menu, click Publish to FME Flow, or from the toolbar ribbon, click Publish.

  Toolbar Ribbon:
  
  
  File Menu:
  

  Deploy Menu (2025.1+):
  

  2. Add a Web Connection

  In the Publish to FME Flow wizard, a FME Flow connection needs to be selected or created. To create an FME Flow connection, click the drop-down under FME Flow Connection and select Connect to FME Flow.

  

  The following instructions are for a basic connection, but if your FME Flow is set up for Microsoft Azure, SAML, or Windows Credentials, please see the Using Web Connections to Connect to FME Flow documentation.

  An FME Flow Connection dialog will appear; here, we will set up the connection. The connection information and credentials can be obtained from your FME Flow Administrator. If you have not yet logged into FME Flow after installing, please do so prior to continuing, as you will be prompted to change the password from the default:
  Username: admin
  Password: admin

  • • Web Service: FME Flow

    • Server URL: http://localhost

    • Connection Name: Training FME Flow

    • Authentication: Basic

    • Username: your username

    • Password: your password

    Click OK. FME will try to authenticate the connection. Once authenticated, this connection will be available whenever you publish a workspace. The connection will only need to be created once.

  • • Web Service: FME Server

    • Connection Name: Training FME Server

    • Server URL: http://localhost

    • Username: your username

    • Password: your password

    Click Authenticate. FME will try to authenticate the connection. Once authenticated, this connection will be available whenever you publish a workspace. The connection will only need to be created once.

    Click Next to move to the Create Repository page of the dialog

  3. Create Repository

  Repositories are used to store groups of related items that have been published to FME Flow (similar to directories). Items such as workspaces, source data, custom formats, custom transformers, and templates can be published to a repository.

  If you need to create a new repository, click on the New button next to Repository Name. Enter the repository name and an optional description. Click OK to close the Create New Repository dialog.
  

  To select an existing repository, click the drop-down next to Repository Name and select the desired repository.
  

  4. Upload Files

  Some workspaces require additional files to run correctly on FME Flow, so these need to be uploaded along with the workspace. To upload files, enable Upload data files at the bottom of the dialog.

  When files are uploaded through the publishing menu, they are stored within the repository. If you want to upload folder files (such as geodatabases) or to specify where the files stored, the Select Files button allows you to do that. For detailed instructions on how to use the Select Files button, see the documentation.

  5. Register Services

  The final step before publishing a workspace is to register services. Services control how the data will be output after the workspace is run.

  

  Services return results in different forms:

  • Data Download Service returns results as a downloadable zip file

  • Data Streaming Service returns results as a data stream

  • Job Submitter Service accepts and runs workspace job requests

  • KML Network Link returns a KML Network Link that can be used in Google Earth

  • Notification Service allows for event-driven messaging

  Typically, the Job Submitter is always enabled, but multiple services can be enabled. Once services have been enabled, click Publish.

  6. Review Translation Log

  To confirm whether or not the workspace was published successfully, check the Translation Log. The Translation log will show which repository you published to, which files were included, and a quick link to run the workspace.

Ensure “Upload data files” is enabled and click “Select Files…” to include the Job-openings-by-industry-and-occupation-for-bc.xlsx spreadsheet. Select OK and click Next.

In the last dialog box of the Publish to FME Flow wizard, click on the Edit button beside the Data Download service. For the “Include Writers in Download:” parameter, only select the Excel writer [XLSXW]. Now click on the Edit button beside the Data Download service. For the “Include Writers in Stream” parameter, only select the HTML writer. Click OK and Publish.

FME Workbench: Publish to FME Flow Wizard, Register Services

FME Workbench: Publish to FME Flow Wizard, Register Services, Edit Data Download

FME Workbench: Publish to FME Flow Wizard, Register Services, Edit Data Streaming

2. Run Workspace

Log in to FME Flow with your credentials, then go to the Run Workspace page. Select the Demo repository and the jobsBC.fmw workspace that was just published. Select Data Download for the service parameter and enter any year between 2021 and 2029. Click on the arrow to expand the Advanced section and select “Create a Webhook”.

Note: In the following steps, the year you selected will be replaced with your User Parameter, $(JobYear), in FME Workbench. In this demo, we chose the year 2023. 

FME Flow: Run a Workspace, Create a Webhook

3. Create Webhook

In the Create Webhook page, disable the “User Can Upload” parameter. Scroll down and click OK.

FME Flow: Create a Webhook, Disable User Can Upload

Download the webhook to save the information. This is the only time you will be able to access the token and instructions to use this webhook, so don’t skip this step! Before leaving this page, copy the Authorization with Query String URL, which includes the token at the end.

FME Flow: Create a Webhook, Copy Authorization Query String

4. Embedded Webhook in Workspace

Go back to the jobsBC.fmw workspace in FME Workbench and open the HTMLReportGenerator transformer to edit Custom HTML.

FME Flow: HTMLReportGenerator, Edit Custom HTML

In the Text Editor, replace <yourWebhook> with the copied Webhook from FME Flow, so that it looks similar to this: 

<p><a href="http://<yourFlowHost>/fmedatadownload/Demo/jobsBC.fmw?SourceDataset_XLSXR=%24(FME_MF_DIR)job-openings-by-industry-and-occupation-for-bc.xlsx&JobYear=2021&DestDataset_HTML_3=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.html&DestDataset_XLSXW=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.xlsx&opt_showresult=false&opt_servicemode=sync&token=cb6a663689dccdd94c6271cda9b1e5c1b273f1d3">Download Excel Sheet</a></p>

Next, find the JobYear parameter in the webhook link. Replace 2023 with the User Parameter, $(JobYear), which will look something like this:

<p><a href="http://<yourFlowHost>/fmedatadownload/Demo/jobsBC.fmw?SourceDataset_XLSXR=%24(FME_MF_DIR)job-openings-by-industry-and-occupation-for-bc.xlsx&JobYear=$(JobYear)&DestDataset_HTML_3=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.html&DestDataset_XLSXW=C%3A%5CarticleDataTest%5CFMEServerHTMLDemo1363%5CjobsBC.xlsx&opt_showresult=false&opt_servicemode=sync&token=cb6a663689dccdd94c6271cda9b1e5c1b273f1d3">Download Excel Sheet</a></p>

5. Save and Republish Workspace

Select OK and save the workspace. Select File > Republish to FME Flow.

Part 3: Create Flow Workspace App

1. Create Workspace App

In FME Flow, select “Create Workspace App” from the “Flow Apps” page. If you are using the data provided in this demo, paste the following into the description section:

Annual labor market outlook for British Columbia. Data presented in a webpage. Select "Download Excel Sheet" to download the zip file.

**Data Source**: Labour Market Outlook, Labour Market Information Office, Ministry of Advanced Education & Skills Training, Government of British Columbia.

**About the Data**: Published by the Ministry of Advanced Education and Skills Training - Labour Market Analytics, Forecasting & Information
Licensed under Open Government Licence - British Columbia
Each year a report forecasting BC's labour market needs over the coming decade is produced by the BC government. The report looks at employment supply and demand by occupation and industry for each of the province's regions.

Fill in the remaining parameters according to the table below, then select OK.

FME Flow: Build a Workspace App, Parameters

After clicking OK, the workspace app URL will be available. 

2. Try the FME Flow App

Copy and paste the URL for the workspace app into a web browser. Test the Flow app by entering a year.

Congratulations, you have successfully created an FME Flow workspace application that both streams your data and downloads an Excel file from one workspace. The URL runs your FME Flow App and grants users access to the HTML page and the data at their convenience.

Additional Resources

Getting Started with Flow Apps
Tips for Automating Excel Data Processing Tasks with FME
5 Ways FME Can Improve Your Excel Data
Tutorial: Getting Started with Excel

Data Attribution

The data used here originates from data made available by the Government of British Columbia. It contains information licensed under the Open Government License - British Columbia.