6 Replies Latest reply on Aug 2, 2017 11:10 PM by user19752

    Export to excel in different spreadsheets

    josh84

      Hi

      Im bit new in FM world and Ive been looking few posts but not sure if its possible.

       

      I would liket to export diferent data to diferent spreadhseets in excel. Is that possible?

       

      thanks a lot

        • 1. Re: Export to excel in different spreadsheets
          fmpdude

          Do you just mean using a different file variable for the output file to write to or something beyond that?

          • 2. Re: Export to excel in different spreadsheets
            beverly

            Shall we clarify?

            Say you have a workbook (Excel file/spreadsheet), and it has several "tabs", aka worksheets.

             

            I'd like to know if you meant different worksheets in one workbook.

             

            IF so, this is much more difficult, unless you make multiple Exports (as CSV, for example) and import them into the various worksheets.

            Another alternative, is if you have precise formatting on sheets 2..n and can import into sheet 1, where the data "flows" (by Excel calcs) into the other sheets. This takes careful pre-setup.

             

            These are merely ideas for triggering what you really want to see in Excel (with I'm presuming export - or more - from FileMaker). Can you explain just a bit more, please?

             

            Beverly

            1 of 1 people found this helpful
            • 3. Re: Export to excel in different spreadsheets
              josh84

              Hi all

              Tahnks for your quick respone and sorry for not given enough details.

               

              what I mean is one excel file with several tabs.

               

              I want to export some data on one tub and another data into another one

               

              thanks

              • 4. Re: Export to excel in different spreadsheets
                user19752

                For creating new excel file, you can use XML exporting with XSL.

                • 5. Re: Export to excel in different spreadsheets
                  beverly

                  Yes! That's how I do this. But since JSON started stepping on XML's toes, we need to find a way for Excel to work with it (or so I've been told).

                   

                  Spreadsheet Markup Language is pretty old (sigh...)

                  Beverly

                   

                  Sent from miPhone

                  • 6. Re: Export to excel in different spreadsheets
                    user19752

                    Example.XSL

                    This take first field as worksheet name, so it shouldn't be empty.

                    Save the result with filename.xml, then you can open it with Excel.

                     

                    <?xml version="1.0"?>

                    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:fmp="http://www.filemaker.com/fmpxmlresult" exclude-result-prefixes="fmp">

                    <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

                    <xsl:template match="fmp:FMPXMLRESULT">

                    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>

                    <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">

                    <xsl:variable name="header">

                      <ss:Row>

                        <xsl:for-each select="//fmp:FIELD[position()>1]">

                          <ss:Cell ss:StyleID="s1">

                          <ss:Data ss:Type="String">

                          <xsl:value-of select="@NAME"/>

                          </ss:Data>

                          </ss:Cell>

                        </xsl:for-each>

                      </ss:Row>

                    </xsl:variable>

                    <Styles>

                    <Style ss:ID="s1">

                    <Borders>

                    <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>

                    <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>

                    <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>

                    <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>

                    </Borders>

                    </Style>

                    </Styles>

                    <xsl:for-each select="//fmp:ROW">

                      <xsl:if test="not(preceding-sibling::fmp:ROW/fmp:COL[1]/fmp:DATA[1] = current()/fmp:COL[1]/fmp:DATA[1])">

                        <xsl:variable name="sheetName" select="fmp:COL[1]/fmp:DATA[1]"></xsl:variable>

                        <Worksheet ss:Name="{$sheetName}">

                        <ss:Table>

                        <xsl:copy-of select="$header"></xsl:copy-of>

                        <xsl:for-each select="//fmp:ROW[fmp:COL[1]/fmp:DATA[1] = $sheetName]">

                          <ss:Row>

                          <xsl:for-each select="fmp:COL[position()>1]">

                            <ss:Cell ss:StyleID="s1">

                            <ss:Data ss:Type="String">

                            <xsl:value-of select="fmp:DATA"/>

                            </ss:Data>

                            </ss:Cell>

                          </xsl:for-each>

                          </ss:Row>

                        </xsl:for-each>

                        </ss:Table>

                        </Worksheet>

                      </xsl:if>

                    </xsl:for-each>

                    </Workbook>

                    </xsl:template>

                    </xsl:stylesheet>

                    1 of 1 people found this helpful