Writing Images to Microsoft Excel Spreadsheets

Introduction

In this short example you'll learn how to insert images into an Excel spreadsheet. We'll read a GML dataset of City of Vancouver File Hall locations and use the ImageFetcher to grab an image of a map location and save that image in the Excel spreadsheet.

Video

Step-by-Step Instructions

1. Create a blank workspace

2. Add the GML reader and select the Fire Halls GML dataset

3. Add a Reprojector transformer to convert from UTM83 Zone 10 to LL-WGS84 - this is so the image fetcher can use the correct coordinates in the map API

4. AttributeManager. We're going to add some Excel writer format attributes to control the size and placement of the images. Add the AttributeManager transformer to your workspace and set the following attributes:

Attribute Manager Transformer Attributes

When writing an image to Excel using FME you must first specify the row id. The @Count() feature function (used for the xlsx_row_id) will set the counter name to be: row_id, and set the Count Start. We start at row 2 as the field headers will be written in the first row.

The xlsx_col_id is used to define which column the image will be written to. Since our column count starts at '0', we will be placing the images in the 5th column that appears in our output Excel file.

Use the AttributeManager to construct the URL for the map API. The image size values are based on the xlsx_raster_width/height attributes you created. the image location and the marker are derived form the FireHall X & Y values using the @YValue() and @XValue() FME Feature Functions which extract the values from the feature geometry. Here's the full URL:

http://maps.googleapis.com/maps/api/staticmap?size=@Value(xlsx_raster_width)x@Value(xlsx_raster_height)&&sensor=false&markers=color:red|size:mid|label:F|@YValue(),@XValue()

(5) Add the ImageFetcher transformer. ImageFetcher fetches an image by performing an HTTP GET operation on the specified URL. Set the Image URL parameter to be "_imageURL" attribute that you created in the AttributeManager.

(6) Add the Excel Writer: In the Writer Parameter dialog set Overwrite Existing File = Yes. Set the template file to be FireHallsTemplate.xlsx. We use a template file that has the row heights preset. Otherwise the images will overlap.

Connect the writer feature type to the ImageFetcher. Edit the attributes leaving only:

• HallNumber
• Name
• Address
• PhoneNumber
• Location Map - new column that will hold the images

Edit the Excel writer Feature Type Format Parameters tab and set Row ID Attribute = xlsx_row_id

The final workspace will look something like this:

The resulting spreadsheet will look like:

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.