4 Replies Latest reply on May 27, 2015 5:43 AM by disabled_menno

    Export to XLSX filetype using XSLT Transformation

    ManjitSandhu

      Title

      Export to XLSX filetype using XSLT Transformation

      Post

      OK, so after some really useful help on getting the XSLT transformation working, I'm now getting the data transformed and it looks as I'd hoped. The only downside is that I'm unable to save the output to the 2007+ filetype.

      <?xml version="1.0" encoding="utf-8"?>
      <xsl:stylesheet version="1.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:fmp="http://www.filemaker.com/fmpxmlresult"
        xmlns="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:o="urn:schemas-microsoft-com:office:office"
        xmlns:x="urn:schemas-microsoft-com:office:excel"
        xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
        xmlns:html="http://www.w3.org/TR/REC-html40">

        <xsl:output indent="yes"/>
        <xsl:strip-space elements="*"/>
        <xsl:template match="/">
          <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>

      This is the stylesheet in my XSLT file, but if I save the reporting output as a XLSX file, it cannot be opened in Office - Excel reports:

      Excel cannot open the file 'my_xls_file.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and the file extension matches the format of the file.

      If saved as .XLS, it will open after clicking OK on the following message:

      The format and extension of 'my_xls_file.xls' don't match. The file could be corrupted or unsafe. Unless you trust it's source, don't open it. Do you want to open it anyway?

      Is there a way to allow the XSLT to output the data correctly for Office 2007 or later? For reference, I don't have Excel installed on the FM server - I did try a batch script to run on the documents folder to open and re-save the files but as Excel is not on the machine hosting FileMaker server, this doesn't work (as it relies on the Excel application object).

      When saving the straight XML output from fileMaker as XLSX, it works fine and the files are opened without any error/warning, but the transformation breaks that, so whilst the end users now get a nicer output, they have to open and re-save as XLSX each time.

      Any ideas on how to get the file format correct?

      Thanks in advance

      Martin

        • 1. Re: Export to XLSX filetype using XSLT Transformation
          philmodjunk

          You have to export as XML to use the XSLT grammar as far as I know.

          Perhaps you could set up a VB macro in the excel file to do the conversion?

          • 2. Re: Export to XLSX filetype using XSLT Transformation

            @Manjit Sandhu

            I had a nice example for you, but it is not possible to add files here or to add large slabs of code. So here a shorter version:

            You probably are exporting to xlsx with xml/xslt to become nice formatted excel and maybe working formula's and maybe more then one worksheet. Ik have made a few of that myself and found it doable, but pretty complex and eassy to do something wrong :( .

            Keep in mind that as Phil stated, you actually export to xml, in fact it is excel-xml-2003 format. 

            Just one little mistake will cause Excel not to open the document, but for now I suppose that your document is correct, then is should open with no trouble. I have this working on Windows and on MacOSX with office 2007 - 2013/365. I use the extension .xls (not .xlsx) So try giving the extension .xls instead of .xlsx to the document, excel should open it then,

            I posted an example (it is a text-file) where you can see a working example. You just need to create an export from any of your own files: http://www.v-beek.eu/ExcelList.txt 

            regards, Menno

            • 3. Re: Export to XLSX filetype using XSLT Transformation
              ManjitSandhu

              You have to export as XML to use the XSLT grammar as far as I know.

              Perhaps you could set up a VB macro in the excel file to do the conversion?

              Thanks, yes but it's possible to open the file (with a dialog) if exported and saved as an XLS file. I had hoped to avoid using VBA, and had found a VB script solution, but that requires Excel on the server, which it isn't. Installing Microsoft's Excel viewer won't work either as it can't open XLS files. There are third party tools to do the conversion, but was looking to avoid those if possible.

              @Manjit Sandhu

              I had a nice example for you, but it is not possible to add files here or to add large slabs of code. So here a shorter version:

              You probably are exporting to xlsx with xml/xslt to become nice formatted excel and maybe working formula's and maybe more then one worksheet. Ik have made a few of that myself and found it doable, but pretty complex and eassy to do something wrong :( .

              Keep in mind that as Phil stated, you actually export to xml, in fact it is excel-xml-2003 format. 

              Just one little mistake will cause Excel not to open the document, but for now I suppose that your document is correct, then is should open with no trouble. I have this working on Windows and on MacOSX with office 2007 - 2013/365. I use the extension .xls (not .xlsx) So try giving the extension .xls instead of .xlsx to the document, excel should open it then,

              I posted an example (it is a text-file) where you can see a working example. You just need to create an export from any of your own files: http://www.v-beek.eu/ExcelList.txt 

              regards, Menno

              i've seen the issues you mention - incorrect data in a specific format (ie text where a number is expected) will prevent the file from opening, but hopefully, with help of the resulting log file, it has helped find and fix the issues in the data. Agreed leaving it as xls is the simple option, but the department aren't happy and want xlsx if at all possible, so was investigating options. I'll look at your example, thanks for taking the time to share.

              Thanks

              Martin

              • 4. Re: Export to XLSX filetype using XSLT Transformation

                If you want real .xlsx, then you need to disect that format ... and .xlsx is a kind of .zip-file (you can open it with winzip) and there you'll find files and folder. The path: <yourdocument>\xl\worksheets\sheet1.xml contains the data. If you's install 7zip, you can use the commandline to store a new "sheet1.xml" in the original location (i have been not able to create a full new .xlsx so far) which you create with the xml-export. I do not have the proper commands at hand now, but surely you can find them on the internet.

                Regards, Menno