Easily View Microsoft Office Files in FileMaker

Introduction

When it comes to viewing Microsoft Office files within FileMaker, this has always been quite a sore point for developers. Unlike images, video files, audio files, and PDF files, we cannot directly view these file formats within container fields.  Developers tend to resort to exporting the files to disk and auto-opening them locally on the users machine. This however requires that the user have a copy of Microsoft Word, Office, or PowerPoint installed in order to view the file they wish to see. This is not always the case.

In this article we're going to show how you can add Excel, Word or PowerPoint document viewing to any existing FileMaker solution in about 10 minutes, the setup couldn't be simpler!

Viewing Files Online - Using the Office App Viewer

Microsoft has a range of apps within its office 365 offering. Examples like Word, Excel, Powerpoint are so commonplace in industry today that almost everyone at some stage needs to view a doc file, or open a spreadsheet in Excel. Microsoft also makes these apps available online in the cloud via their 365 service. Users can keep their documents online in their MS OneDrive, and use online tools to view documents entirely within the browser.

But did you know that they also have an online file viewer, which is freely available for anyone to use, no account required? This will render any Word, Excel or PowerPoint presentation providing that file is hosted online and the URL is accessible to the file viewer tool.

Using this fact we can utilise the viewer in order to view documents stored directly in our own solutions. We just need to get the file online so that the viewer can render it for us. No more viewing of a document in FileMaker and just seeing this:

Getting your files online

The first obstacle to overcome is getting your container data online so that the office viewer can render it.  Now there are obviously numerous ways you can do this, such as:

• Host via third party service (which we will do)
• Use your own web hosting server (good as you can secure it and control access to just the viewer)
• Hack around with Apache/IIS to directly host externally stored container data - yes it can be done though not recommended!

For this example we're going to use a really simple online file hosting service called file.io. The goal here is to upload container fields to this service, and use the resulting hosted URL to pass to the office viewer for display.

File.io - Pros and Cons

File.io is a great little service for hosting files. One of its key benefits is that it is a single-view only service. When a file is hosted there, the URL is only valid to be viewed once only. After that single viewing occurs the file is deleted and the URL is removed. This is perfect for our use because we don't want that file to hang around on a server somewhere, nor do we require the URL after we view it.

While that all sounds good, you have to be very careful when dealing with any third party service especially when they will be hosting your files. You may have sensitive information in your files you do not wish to put in the public domain. While secure, the files will still reside temporarily on someone else's server, and while the URL is a one-time view, you can never fully trust that.

We are using this service for the demo but you should consider a more secure method of hosting your container data online if you intend to use this service. A good method would be to host them on your own server, and only allow the office viewer URL access.

Uploading files to File.io using Insert from URL

The file.io api is really simple, no keys are required, it's a simple CURL post of your file to their server, and in return you are given some simple JSON. Included in that result is the URL to your file.

You can find this script in the example file included at the bottom of this article. It is recommended you download it and have a look through as you read.

Here's the code above. What we do is first put the container data into a variable $file. This makes the subsequent steps a bit easier and clearer.

Next, We calculate a local variable name, which we store in $VariableName. Why do we do this? Well this comes down to how posting of a file works using CURL, in conjunction with what the file.io api is expecting us to give them. This will be more apparent in the next step:

Evaluate ( "Let ( " & $VariableName & " = $file ; 1 ) " )

We are evaluating a calculation string here where we are setting our variable to the file contents that is stored in $file.  As an example if our file name was "test_file.doc" , then the name of the variable we are setting would be $test_file.doc , and the value of this would be that of the file itself.  We use a LET statement to do this as it allows us the ability to soft-code the variable name without having to explicitly define it.

Why must we do this? Because in the following POST to file.io, we send the file using the "-F" command:

When we use a FileMaker variable in a CURL request, the name of the variable is important. File.io is using the variable name as the name of the file itself, and FileMaker in turn is passing the actual content of that variable to file.io - the file itself.  If we were to use some other variable name e.g. "test" then the resulting file uploaded would be called "test".  The Microsoft office viewer would not recognise this as a valid file format and so fail to display it.  This is why variable name is important to be given the same name as the actual file we want to view.

Once our request is posted off to file.io, we simply parse out the "link" key value from the JSON response.

Rendering in a web viewer

Once we have the URL to our file, the rest is easy. The office viewer simply follows a simple url format which accepts the files URL as a parameter: 

http://view.officeapps.live.com/op/view.aspx?src=[OFFICE_FILE_URL]

We just replace [OFFICE_FILE_URL] with the one returned from file.io. We then display this in a web viewer.

How does it look - Word

This is what the viewer looks like for our 3 main file formats. Firstly a Word document:

Not too shabby! The document is strictly read-only so no editing is allowed. Some of the options listed in the toolbar and menus are not available but others are, things that work include:

• Accessibility Mode - a nicer full screen experience
• Save to Onedrive (provides a sign in option) not fully tested!
• Print
• Search within document
• View document comments

There are buttons to "Download" and "Download as PDF" but these do not seem to work in the web viewer. However, the Print option does. The print option should open a new tab in your browser (so outside of FileMaker) and give you a PDF version that is printable. From here you can print or save as PDF.

How does it look - Excel

Here is a simple Excel spreadsheet:

Again, pretty good all things considered. Once again read only, but you can clearly see cells/columns and it looks very much like excel. Again options you can make use of include:

• Viewing multiple sheets within the file
• Summing/counting columns - totals are shown in bottom toolbar
• Save to oneDrive
• Print - opens in browser as PDF
• Find within sheet
• View comments

Same again as the document, download buttons don't work.

How does it look - PowerPoint

Lastly we have a PowerPoint presentation:

Features include:

• Navigate through slides
• Toggle reading / slideshow view
• View associated slide notes
• Start slide show (opens in browser, slide show includes animations)
• Save to oneDrive
• Print to PDF (via browser)
• View slideshow comments
• Accessibility mode

What about Google?

Google has its own similar offering based on its Google Docs service. Again it works in the same way office viewer does - it's just a URL that accepts another file URL to render.

Google actually has quite a bit of benefit over Microsoft when it comes to viewing other file types. Google offers a far wider range of file types to preview. This is really handy for some industries such as graphic design, because Google allows viewing of things like Adobe Illustrator, CAD, and other graphical formats.  Google also allows preview of CSS, JavaScript and HTML files which would come in handy for some.

There is however a big drawback to Google, and why we haven't sung its praises in this article to this point.  Unfortunately it doesn't work very well with our file.io service we are using.

When Google Docs is rendering a file URL, it must be accessing that URL more than once. File.io only allows access one time, and so Google Docs fails to render most file formats for this reason. Now, this is purely a limitation of file.io so if you host files using any other method you are likely to be able to use Google perfectly fine, in which case this may be the better option for you.

Google renders its previews as a PDF in almost all instances (exception being audio/video)..

As of writing this article it would appear only the Word Document format reliably works with file.io and so that has been included in the Demo.

File formats that Google accepts includes the following:

• Image files (.JPEG, .PNG, .GIF, .TIFF, .BMP)
• Video files (WebM, .MPEG4, .3GPP, .MOV, .AVI, .MPEGPS, .WMV, .FLV)
• Text files (.TXT)
• Markup/Code (.CSS, .HTML, .PHP, .C, .CPP, .H, .HPP, .JS)
• Microsoft Word (.DOC and .DOCX)
• Microsoft Excel (.XLS and .XLSX)
• Microsoft PowerPoint (.PPT and .PPTX)
• Adobe Portable Document Format (.PDF)
• Apple Pages (.PAGES)
• Adobe Illustrator (.AI)
• Adobe Photoshop (.PSD)
• Tagged Image File Format (.TIFF)
• Autodesk AutoCad (.DXF)
• Scalable Vector Graphics (.SVG)
• PostScript (.EPS, .PS)
• TrueType (.TTF)
• XML Paper Specification (.XPS)
• Archive file types (.ZIP and .RAR)

So in Summary..

We've shown a really quick and easy way to view office based files from FileMaker. We have however shown you must think carefully about how you use this because both google and Microsoft require a publicly available URL that it can access in order to render the file, so think long and hard about how you handle this requirement.

Good luck!

Inspiration for this Article

While searching for a way to view spreadsheets in FileMaker for a client, it was this following site I came across which gave the answer:

https://gist.github.com/tzmartin/1cf85dc3d975f94cfddc04bc0dd399be

Many thanks to that sites publisher tzmartin for providing this invaluable information.  You will also find on his site information about how you can do this within an iframe, and how you can use both Google and Microsoft to view files stored directly on their respective cloud offerings - Google Docs, and OneDrive.

Example File

As with all of our articles we produce we like to provide a detailed example file to go along with it. It’s not enough to just read how something is done, you should be able to see it in action and explore how it works yourself. Please find attached the example file below. This article is based on the example file. As always, this is a free download, and we won't make you sign up to download :)

Click here to download the example file!