1 2 3 4 Previous Next 52 Replies Latest reply on Jul 21, 2016 1:50 PM by beverly

    [ANN] Export custom spreadsheets with FMP2XLS v1.01

    Doug Staubach

      Hi Community:

       

      I am releasing FMP2XLS - a fully-tested XSLT stylesheet template that will convert the current "found set" of records on any layout into a nicely formatted Excel spreadsheet. (It uses the Export Records function, which can be easily scripted, as my demo DB file shows).

       

      Included in the sample stylesheet, I provide working examples of ALL of the following spreadsheet features:

      • Choose different font sizes, font colors, background colors, cell borders, cell alignment (left, right, center, top, middle, bottom).
      • Wrap text inside cells - automatically set the height of cells to display full text, and set column widths for specific columns (by name).
      • Print the current layout name (or database name) and number of records found/exported (and set the worksheet tab name also).
      • Display formatted 'field names' at the top of each column, so they are easier to spot, and don't get confused with the data rows.
      • Split the spreadsheet into scrolling window panes, and freeze panes to allow the user to scroll through data while still seeing header rows.
      • Rename any field (if you know the FileMaker field name, you can provide a 'prettier' field name in Excel for display purposes).
      • All number, date, and time fields in FileMaker will be preserved as numbers, dates and times in Excel (versus text, which is the default).
      • You can create your own custom number and date formats for each spreadsheet column (like $1,234.50 or Feb 3, 2016)
      • Create cells that contain Excel formulas (using Excel formula syntax and R1C1-style references to values in other fields).
      • Optionally add database 'summary' fields (like COUNT, SUM, AVERAGE, MIN) at the bottom of your spreadsheet.
      • Set default print options like paper size, landscape orientation, fit to page, margin widths, and footer text.

       

      Note: This solution only does one thing: it allows you to EXPORT data from FileMaker, into a nice-looking Excel spreadsheet (it is not useful for importing data from XML into a FileMaker database).  This file can be used by anyone, but it will be most valuable for FileMaker database developers and power users. (The sample stylesheet contains a LOT of comments that will be useful for people who want to modify and edit the sample file and create their own custom template.)


      Usage: You can use this file as-is, or edit it *source code provided* to create your own custom spreadsheet template for any layout in your own database. Then you add a simple script, so that when your user clicks a button, the data currently being displayed on the screen will be saved to their desktop, in Excel format, using the template that you designed. (FileMaker Pro includes a built-in function that allows records to be exported as an Excel sheet, but the built-in version does not include any of the features that I have listed in the bullet points above.) - This solution does not require any plugins or paid software, and there are no screens for users to choose options from: just one click and the Excel file will be created on the user's PC or Mac desktop.

       

      This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License, which means that you can use, copy, and modify my examples free of charge (but you have to give me credit for my work, and you can't sell commercial products containing this work without contacting me). The Official License and terms are available at the following URL: http://creativecommons.org/licenses/by-nc/4.0/

      (Excel is a registered trademark of Microsoft Corporation, and FileMaker is a registered trademark of FileMaker Inc.)

       

      I look forward to seeing what people can do with this solution. If you can make it better/faster please send me your recommendations!

       

      Thanks,

      Doug Staubach

       

      UPDATE: Sample DB and XSLT file updated to v1.01 (added workaround for FileMaker's timestamp error in non-US locales)

        1 2 3 4 Previous Next