Excel Writer Parameters | Converting Excel to Excel

Introduction

Reading Excel spreadsheets is relatively straight-forward, but writing to Excel can be more nuanced because of the functionality supported. This exercise will discuss the basics of the Excel writer, and how to set parameters for formatting.
The tutorial will make use of the workspace from the previous article where we read in Excel data and added a calculated column. Download and open the WritingExcel-Begin.fmwt workspace to get started. 
 

Step-by-step Instructions

1. Open Starting Workspace
Download and open the WritingExcel-Begin.fmwt workspace. This workspace reads in two worksheets from a Microsoft Excel workbook, merges them together based on the Month attribute (column), creates a new attribute (column) with values calculated from the two worksheets, then adds a percent % sign. 


We want to add the “Percent of Average” attribute to the other two worksheets in this file (2018 and 2019). Let’s read the rest of the file in the next step. 
 
2. Add More Feature Types
We already have two reader feature types (worksheets) on the canvas (2020 and Average), but we want to add in the remainder of the file. On the top toolbar go to Readers > Import Feature Types


Import Feature Types allows you to import more parts of your dataset, in this case, it would be additional Excel worksheets. The Import Feature Types dialog is similar to the Add Reader dialog where you can choose which file you want to import from. If it isn’t already set up, select Microsoft Excel as the Format and then browse to the Precipitation.xlsx available from the Files section. Then click on Parameters. 


The Import Reader Feature Types Parameters dialog is the same as the reader parameters dialog. For this exercise, we will be adding the 2018 and 2019 worksheets. Uncheck 2020 and Average, then check 2018 and 2019. Click OK twice to add the feature types to the canvas. 

3. Duplicate Percent of Average Attribute
Connect the two new reader feature types (2018 and 2019) to the Left input port on the FeatureJoiner. Once connected, run the workspace with Feature Caching Enabled to the FeatureJoiner, and inspect the cache in Visual Preview. 


Since our input data doesn’t have an attribute indicating what worksheet the data came from, you can’t tell which year the month data belongs to. This can easily be fixed by enabling the fme_feature_type attribute. This attribute reflects the name of the feature type (worksheet). 
 
4. Enable the fme_feature_type Attribute
To enable the fme_feature_type attribute, double-click on the 2020 reader feature type to open the parameters. In the parameters dialog, switch to the Format Attributes tab. Check the fme_feature_type attribute and then click OK. Repeat this step with the 2018 and 2019 reader feature types. 


Rerun the workspace to the FeatureJoiner and inspect the output in Visual Preview. There should now be a new attribute (column) called fme_feature_type with the year, which reflects what the worksheets are titled. 



5. Write out to Microsoft Excel
a. Add Writer
Now with the Percent of Average attribute added,  we can set up the writer. Add a Microsoft Excel writer to the canvas and call it Preceipitation.xlsx. Change the Sheet Definition to Automatic and click OK. We will be writing to the same worksheet that we are reading from, it is good practice to save a duplicate of the original file before running the workspace. 


When the Feature Type dialog appears, click OK to close it, we will modify the parameters in a minute. Connect the writer feature type to the StringConcatenator. Once connected, double-click on it to reopen the parameters. 
 
b. Fanout Data by Year
Currently, all of the years are merged together into one dataset, but we would like to write them back out into individual worksheets separated by year, to do this we will do a feature type fanout. In the writer feature type parameter dialog, click on the drop-down arrow next to Sheet Name, then select fme_feature_type from Attributes. 

c. Stylize Columns
Before we run the workspace let’s add some style to one of the columns. Switch to the User Attributes tab, then switch the Sheet Definition to Manual. Change the Cell Width to 25 for each of the Attributes. Then click on Edit next to Percent of Average. In the Edit dialog, enter the following into the Custom Number Format:

[Black][<=100];[Red][>100]

This formula will color all the values in the Percent of Average column red if the value is higher than 100%. See Custom Number Formats for more information. Click OK twice to finish editing the parameters. 


Editing the styling within the writer modifies only the columns. If you’d like to modify cells or rows, please see the ExcelStyler transformer documentation. If you would like to use a template file to stylize your Excel document, see Using a Template File when Writing Excel Data.
 
d. Add Another Feature Type
We need to add in another writer feature type to copy over the Average worksheet that we are not modifying. From the top menu bar go to Writers > Add Feature Type. 


In the Feature Type parameters, change the Sheet Name to Average and then expand the Advanced section. In the Advanced section, enter 4 for Sheet Order. Since our Average worksheet is only for reference, we want to ensure it appears last in the order of worksheets. Click OK to accept the parameters. 


Connect the Average reader feature type to the Average writer feature type. 


6. Run the Workspace
Finally, run the workspace and view the output file in Microsoft Excel. There should be the four worksheets, with the Percent of Average column on the three year worksheets. 

Data Attribution

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