Writing Images to Microsoft Excel Spreadsheets

Liz Sanderson
Liz Sanderson
  • Updated

FME Version

  • FME 2016.x

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:

Output Attribute Attribute Value Comments
xlsx_row_id @Count(row_id,2) set start count = 2 - see below
xlsx_col_id 4 Column number (stating at column 0)
xlsx_raster_height 200 height of the image

xlsx_raster_width

200

width of the image

_imageURL 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() see description below

 

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:

writingimagestoexcel.jpg

The resulting spreadsheet will look like:

outputexcelspreadsheet.jpg

 

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.

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.