1 2 3 Previous Next 38 Replies Latest reply on Aug 15, 2017 5:30 AM by beverly

    Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)

    Doug Staubach

      Hi Community:

       

      I've had a lot of good feedback from people who downloaded earlier versions of this tool. (Thank you!)

       

      NEW UPDATE (April 9, 2016): Version 1.03 includes better fixes for "FileMaker's XML Timestamp Bug" plus a lot of improvements for validating international date and number formats (including currency formats). - This should be the last update from me for a while.

       

      FMP2XLS is a totally free (open source) template that uses FileMaker's built-in capabilities to create nice-looking Excel spreadsheets for your end users. There are no third-party plug-ins or software to purchase - your users can simply click a button, and get a preformatted worksheet with tons of extra features that are simply not possible with the standard / built-in 'Export to Excel' menu option.

       

      For a detailed description of features (and a lively discussion about what this solution does and doesn't do), please refer to my original post.

       

      Version 1.03 should be downloaded to REPLACE all previous versions. (The date functions and the number conversion functions have been completely redesigned to better support non-US locations and to provide the best possible work-around solution for the timestamp bug.)

       

      Please send me screen shots showing how you have used my solution to make "pretty spreadsheets" in your own database!

       

      Thanks,

      Doug Staubach

       

      (1) You can make your exported spreadsheets look pretty (rename headers, add totals, real Excel formulas, etc.) !

      Screen Shot (Results).png

       

      (2) I have included a sample database to show how easy it is for your end-users (they just click a button!)

      (3) No software to install, no fees, no plug-ins. (Can be used as-is, or copied and customized by you.)

      Screen Shot (DemoDB).png

      Best wishes,

      Doug

        • 1. Re: Updated Version FMP2XLS v1.02 (export custom Excel spreadsheets)
          CamelCase_data

          Works great at a first go - I'll try to stress it some more and see if there's some particular case that breaks it, but it looks solid. This is certainly a very substantial contribution to the community!

          • 2. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
            Doug Staubach

            Hi David:

             

            I've made additional improvements (including better support for international number formats).

             

            If you are still interested in doing some testing, please download and test v1.03 (above).

             

            Thanks,

            Doug

            • 3. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
              CamelCase_data

              Awesome Doug! Sounds like this should be pretty complete now, within the framework of what it does.

              • 4. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                Doug Staubach

                Ok we just tested, and it works with WebDirect!!

                 

                ... and it was easy to do: no plug-ins, no mapping network drives to client computers, no special permissions,  no loading files into your web server configuration. It just works, right out of the box (*if* you put your stylesheet in the correct folder on their server!)

                 

                So we now have the ability to display a button on the WebDirect layout that creates a fully customized Excel spreadsheet on the user's client device (woo hoo!)

                • 5. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                  Doug Staubach

                  Hi Everyone:

                   

                  User bhagara created a Youtube video showing this solution in action.

                  I've copied his post (which as listed on the thread for v1.01)

                  Re: [ANN] Export custom spreadsheets with FMP2XLS v1.01

                  bhagaraRequirements Gatherer

                   

                  Here's a demo of this...

                  FileMaker Pro Tutorials | Exporting Records to an Excel Spreadsheet - YouTube

                  Best wishes,

                  Doug

                  • 6. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                    electon

                    MS Office Excel 2016 on Windows 10.

                    The file is created from a database on OSX 10.11.3

                    It complains about the format, here's the screenshot when opening the file.

                     

                    Screen Shot 2016-04-27 at 09.52.06.png

                    I can convert the file via Compatibility Mode to .xlsx but that writes a new file.

                    Also when opening the file in OSX Numbers, it shows weird results.

                    Here's a part of the screen.

                    Screen Shot 2016-04-27 at 09.58.13.png

                    Have others not experienced this?

                     

                    Thomas.

                    • 7. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                      beverly

                      are you using the latest version of this demo?

                      beverly

                      • 8. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                        electon

                        Most definitely. 1.03 Downloaded from this post.

                        I've been following this thread and it seems to be the one to use.

                        • 9. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                          Doug Staubach

                          Hi @electon (Thomas):

                           

                          Yes, I have seen that MS-Excel will sometimes complain / display a message that the file format (which is actually Excel 2004 XML) doesn't match the file extension (Excel 97-2004 XLS), but if you click "Yes", to open the file anyway, it will open in Excel without any issues. -- Have you tried clicking Yes?

                           

                          Note: OS version doesn't really matter - I've used this template on OSX 10.10 (Yosemite) and OSX 10.11 (El Capitan), as well as Windows 7 workstation and WebDirect (running on Windows 2008 server). The Excel version should not be an issue either - you say that you are using Excel 2016 for Windows; I am using Excel 2016 for Mac, and my guess is any Excel version after 2004 should work fine.

                           

                          Regarding the second screen shot (strange results when try to open the file in Apple's Numbers / OpenOffice Calc) - what you are seeing is completely normal. In fact, you would get this same result if you created your own spreadsheet in Excel, then saved it as Excel 2004 XML, and tried to open it using Apple's Numbers or OpenOffice Calc. This happens because those apps don't read *every* possible Excel format, they only read common formats (Excel's 2004 XML format, called 'SpreadsheetML' isn't very common; Excel can read it, but other apps probably won't. - Perhaps one day a clever developer will release a demo showing how to save the file as a true 'Office Open XML' format, using the XLSX file extension).

                           

                          If you want your users to be able to click a button and open a custom-looking spreadsheet in another app (not Excel), then you'd have to translate the XSLT script language to work with that other spreadsheet app (this would not be an easy thing to do, but if the other app allows saving in XML format, it is potentially do-able).

                           

                          The solution that I've offered here is totally dependent on MS Excel to open and read the file, but after the file is already opened in Excel, you can convert and save it in a different / more common / more portable format. To do this, just open the XML+XSLT file in Excel, then in the Excel menu, choose File / Save-As, and change the save format to Excel Workbook (XLSX).

                           

                          Since you have MS Excel, just try opening the XLS file using Excel, click 'Yes" when Excel tells you that the file format doesn't match its extension, and see if the file opens correctly.

                           

                          Thanks,

                          Doug

                          • 10. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                            beverly

                            right! there were two threads & I've been following both, so wanted to verify you had the latest.

                             

                            ok to answer your questions

                            1. yes, the "error" is a security measure that may or may not be prevented.

                             

                            2. Numbers may not allow use of the same "open office xml". remember that the extension may say ".xsl", but the content is ".xml".

                             

                            <https://support.apple.com/en-us/HT205391>

                            <https://en.wikipedia.org/wiki/List_of_software_that_supports_Office_Open_XML>

                            you can do more research with 'apple Numbers xml format'

                            • 11. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                              CamelCase_data

                              In my experience, issues like these are pretty much inherent in any attempts at creation Excel (or in general, Office files) using formats that aren't "really" Excel (etc) files. What makes this even more of a pain is that Mac and Windows (and maybe even different Office versions) don't accept the same file extensions for a given content.

                              "My" Excel 2013 on Windows 8.1 will e.g. happily open the files generated using DougStaubach's technique if you give them the .xml file extension. Depending on each case, you can modify the scripting to add an option to set the file extension to xml when generating a file on/for Windows, perhaps using Send Event to automatically open the file created in Excel.

                              I'm not sure if there is a good workaround for Numbers.

                               

                              As mentioned a few times by different people in this discussion, DougStaubach's technique is really great, but there are still cases where you need to go for a plugin-based solution (at least until someone makes a "real" xlsx-generator public).

                              • 12. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                                electon

                                Doug and Beverly,

                                Thanks for the explanation.

                                Yes, I can click on "Yes" and it does open and display ok.

                                I can convert it to anything Excel allows me to.

                                 

                                For me it's not a big deal but because this behavior was not mentioned, apart from "with a click of a button" :-)

                                I've been wondering if this might be an issue with the code and wanted to give some feedback.

                                 

                                Doug, thanks for sharing the good work!

                                • 13. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                                  Doug Staubach

                                  Hi everyone:

                                   

                                  Yes, it's true - if Excel's security features are enabled on the client machine, then the pop-up message might be seen as a nuisance by the end-user (it isn't really one-click: it's two clicks in that case).

                                   

                                  The explanations provided by beverly and David (CamelCase_data) are solid - the contents of the file are actually "Excel SpreadsheetML", and if you save the file with the correct extension (xml instead of xls), then Excel will be much happier - it will not display a warning message, because the contents and the file extension will match each other.

                                   

                                  However, if you do use the correct file extension, then you will have a new problem, because most workstations are not configured to associate XML files with spreadsheet programs, so if you set an XML file to "automatically open", the file will either be opened in in the user's browser, or in an HTML editing program, or perhaps even 'notepad', and the results would look a lot like the ones from your second screen shot.

                                   

                                  So naming the file with an XLS extension is a little "trick" to try to force Excel to open the file.

                                   

                                  Another potential way to make this happen (without causing security warning messages on the client's workstation) would be an external script (something outside of FileMaker that tells the OS to open file "X" with application "Y"), but that can introduce problem of a different nature, because scripts like that are usually linked to a specific OS version or platform, so portability becomes an issue.

                                   

                                  Perhaps another method would be through an enhancement to FileMaker itself -- if we could provide an Export script with the filename of the output file, and also provide the "type" of application that we want to use to auto-open the file (spreadsheet, word processing program, text editor, web browser, picture viewer, etc)., then we would have a lot more control over how the file is opened (for example, we could request that the file be opened in a 'reader' app like PDF viewer instead of an 'editor' app like Acrobat Professional). -- Note here, that I say we should be able to select the "type" of application, because it would be difficult to list the actual names of all of the different software apps that a user might potentially have installed on their workstation.

                                   

                                  Of course, the best solution would require a time machine ... Way back when XML was first being designed, it would have been "nice" if the designers would have decided to build some intelligence into the header format -- to say "Hey OS, when you open a file with the XML extension, read through the header information and see if there are any instructions that require the file to be opened with a particular type of application, like a spreadsheet program or a word processing app" -- if the XML design committee had added that capability, then the user could simply double click any file with an XML extension, and it would open in the correct software app -- but because they didn't do that, every software program on the market ends up using a different file extension: they save the format as XML, which is application-independent, but then they give the file a non-XML extension, a different extension for every app, because that is one of the few ways that developers can associate a file with a specific application (or type of application).

                                   

                                  Anyway - thanks for using the demo, and thanks for pointing out a problem that other people are likely to encounter, and thanks also for the kind words. -- I'm glad this technique works for you, and I'm glad you find it useful.

                                   

                                  Regards,

                                  Doug

                                  • 14. Re: Updated Version FMP2XLS v1.03 (export custom Excel spreadsheets)
                                    beverly

                                    Of course, the best solution would require a time machine ... Way back when XML was first being designed, it would have been "nice" if the designers would have decided to build some intelligence into the header format -- to say "Hey OS, when you open a file with the XML extension, read through the header information and see if there are any instructions that require the file to be opened with a particular type of application, like a spreadsheet program or a word processing app" -- if the XML design committee had added that capability, then the user could simply double click any file with an XML extension, and it would open in the correct software app -- but because they didn't do that, every software program on the market ends up using a different file extension: they save the format as XML, which is application-independent, but then they give the file a non-XML extension, a different extension for every app, because that is one of the few ways that developers can associate a file with a specific application (or type of application).

                                     

                                    WAY BACK MACHINE...

                                    ok the design committee did include ways.

                                     

                                    You have this:

                                    • all the appropriate namespaces (good)

                                    • <?mso-application progid="Excel.Sheet"?> (a processing instruction)

                                    and these are *supposed* to help the application understand what to do with the XML (regardless of or in addition to extension)

                                     

                                    I use this:

                                    <!-- code to get Excel to recognize the xml document as an excel doc -->

                                    <xsl:output method="xml" media-type="application/vnd.ms-excel" indent="yes"/>

                                    <!-- end - code to get Excel to recognize the xml document as an excel doc -->

                                    I leave it up to y'all to test which works best.

                                    beverly

                                    1 2 3 Previous Next