1 2 Previous Next 22 Replies Latest reply on May 11, 2015 4:43 AM by beverly

    How to keep conditional formatting when exporting to Excel?

    jamesg

      Hi All

       

      I have been trying to export to Excel a found set of records from a layout which uses conditional formatting on certain fields whilst retaining the conditional formatting in the output spreadsheet.

       

      I think it's not possible; I check the 'Apply current layout's data formatting to exported data' check box in the Export dialog.

       

      If I can achieve this the intention is to import the spreadsheet as formatted into InDesign as formatted.

       

      I don't want to use plug-ins and I have next to no understanding of XML and XSL, nor do my end users who need to be able to simply re-link the spreadsheet into their InDesign template.

       

      Thanks for your input, I've been scratching at this for days!!

        • 1. Re: How to keep conditional formatting when exporting to Excel?
          TorstenBernhard

          Please have a look at this thread:

          Is there a way to import Formatted Text from Excel?

          It provides anwers to your question.

          • 2. Re: How to keep conditional formatting when exporting to Excel?
            mikebeargie

            His questions is about exporting, not importing formatting. But the answer is pretty much the same.

             

            Jamesg, the best method to dump formatted data to excel is to export it as HTML, open it in excel, and resave it as xlsx. Unfortunately styling is not supported at all in the native .xlsx export.

             

            If your goal is to import to InDesign, it might be much better to setup filemaker as an ODBC data source, pull the data directly from filemaker into InDesign, and recreate your conditional rules in InDesign itself.

             

            In InDesign you'll need a plugin to pull ODBC:

            EasyCatalog | InDesign Plug-Ins | 65bit Software

             

            And from there, here's a guide to write conditional formatting in InDesign:

            http://indesignsecrets.com/finally-conditional-text.php

            I WISH we had that level of conditional formatting control in FileMaker.

             

            In your terms, since the end result is in InDesign, and not filemaker, I'd focus on making the robust setup on the InDesign side, and only figure out how to dump the raw data you need from filemaker over to InDesign.

            • 3. Re: How to keep conditional formatting when exporting to Excel?
              jamesg

              Hi Mike mikebeargie

               

              Thanks for the reply - I took a look at both options and although very useful (and taught me stuff about InDesign functions I didn't know about!) neither solutions work quite as I need.  You are right and I need to focus on ensuring a robust set up on the InDesign side as it's the critical user end that I am trying to improve upon with less clicks and techy savvy.

               

              What I am trying to do is export formatted data in a spreadsheet format that is then used in tables in InDesign.

              The conditional formatting in InDesign initially looked like it would do the job, however on close inspection it would require mean that each time the spreadsheet linked file is updated all the items would have to have all the conditions re-applied.

               

              What I have come up with is rather clumsy but works, if anyone can think of a better way to do this I would be very grateful!

               

              1. Spreadsheet exported from FM with fields not formatted.
              2. Open exported spreadsheet in Excel "From-FM.xlsx.".
              3. Copy the first three columns 'position', size', 'description'
              4. Open workbook 'Linked-Excel-Template.xlsx' - contains three sheets.
              5. Sheet 1 - 'raw data'
              6. Sheet 2 - 'conditionally-formatted'
              7. Sheet 3 - 'word-copy'
              8. Paste the columns from "From-FM.xlsx" into Sheet 1 'raw-data'
              9. Go to Sheet 2 'conditionally-formatted' copy the first three columns which have now been formatted based on words in the 'description' column to colour the 'position' column
              10. Open Word.  Paste.  Select All.  Copy.
              11. Go to Sheet 3 'word-copy' and paste the data into the first three columns which now retains the formatting without any formulae for conditional formatting, the data is as it should look.
              12. Save and close all MS files ('Linked-Excel-Template.xlsx', 'From-FM.xlsx' and word document).
              13. Open InDesign document.
              14. Create a text box
              15. Place the spreadsheet selecting the sheet 3 'word-copy', Unformatted.

               

              Now the data is linked to a formatted spreadsheet, with columns, it can be updated if a new file is exported from FM by simply replacing the data in the 'raw-data' sheet.

               

              Additionally the text box with the linked data can be Link Pasted throughout the InDesign document, rows removed in different places as relevant and all the instances of the linked spreadsheet are updated at the same time.

               

              Very clumsy and lots of steps but works everytime, just need to persuade Users that this is workable

               

              I've seen elsewhere suggestions to import XML with a stylesheet (with Javascript to conditionally format data) straight into InDesign but I don't know any of the languages used.

              • 4. Re: How to keep conditional formatting when exporting to Excel?
                mikebeargie

                You could potentially use the excel ODBC driver to dump the data straight from filemaker into the excel file, or vice-versa to pull filemaker data straight to excel.

                 

                It's not supported as ESS in FileMaker, so you'd have to write your own SQL queries and use the Execute SQL script step to write the data to excel, but it would be possible assuming you want to take this to a higher level.

                • 6. Re: How to keep conditional formatting when exporting to Excel?
                  jamesg

                  Learning more and more... thanks.  I'll take a look at ODBC driver and how that works.  Always good to find more elegant ways to achieve a solution.  Thanks.

                  • 7. Re: How to keep conditional formatting when exporting to Excel?
                    jamesg

                    Looks like you have a lot of XML, XSLT and Javascript knowledge, do you think I should pursue XML as a possible solution?

                    • 8. Re: How to keep conditional formatting when exporting to Excel?
                      Benjamin Fehr

                      There are chances that with 1 dedicated XSLT-Stylesheet or even 1 CustomFunction, this can be achieved once and forever.

                      You probably have to find a way to include/read-out(?) conditional formatting formulas in this structure. They might be stored in a different layer than general rendering attributes for a layout.

                      mmh, thoughts on that?

                      • 9. Re: How to keep conditional formatting when exporting to Excel?
                        beverly

                        yep, one (sort of complex) XSLT can do it. No javascript needed, just a text editor and reference to the link I'd posted. Oh, and it helps to make a "mockup" IN Excel. Then export (as XML) to see what it might look like. Half the work is done, put the xsl bits where needed, including the field COL/DATA positions from the export.

                         

                        Mostly it depends on what it needs to look like. Formatting (including conditional) definitely can be a part of the XSLT. Sometimes I create  "calc fields" with these values (colors, bold, etc.) so that it's easier to push to the XSLT and process. Again, it really depends on the export and the desired result.

                         

                        beverly

                        On Apr 29, 2015, at 12:32 PM, efficientbizz <noreply@filemaker.com> wrote

                        • 10. Re: How to keep conditional formatting when exporting to Excel?
                          Benjamin Fehr

                          Beverly Voth

                          I once had a great SW called XML-easy from a German company called SoftProject GmbH to do all the data-mapping to create a XSLT file (PC only). The company still exists, but not their SW.

                          What's your recommendation for a affordable(!) tool?

                          Best known product might be Altova but it's pretty expensive.

                          • 11. Re: How to keep conditional formatting when exporting to Excel?
                            beverly

                            I use BBEdit and just code, but that's me.

                             

                            I also use Xmplify (mac) <http://xmplifyapp.com/> when needed.

                             

                            There will be not 'push-a-button' way. The export FROM Excel (as XML will give you much information that can be used as is or changed.

                             

                            Beverly

                            On Apr 29, 2015, at 2:38 PM, efficientbizz <noreply@filemaker.com> wrote

                            • 12. Re: How to keep conditional formatting when exporting to Excel?
                              jamesg

                              Hi Beverly

                              Thanks very much for your advice, would you mind posting that link again as I can't see it in your first message.  I'm going to jump into the world of XSL and XML and hope I float!!

                              • 13. Re: How to keep conditional formatting when exporting to Excel?
                                jamesg

                                Thanks to everyone for the flurry of responses and suggestions I'm very grateful and hopefully all your help will ensure a slightly more elegant solution to my CPW (Cut Paste Workaround!!).

                                • 14. Re: How to keep conditional formatting when exporting to Excel?
                                  beverly

                                  https://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx

                                  HTH!

                                   

                                  You might research Spreadsheet ML, for possible tutorials and other hints.

                                   

                                  On Apr 29, 2015, at 3:10 PM, jamesg <noreply@filemaker.com> wrote

                                   

                                   

                                  How to keep conditional formatting when exporting to Excel?

                                  reply from jamesg in Discussions - View the full discussion

                                  Hi Beverly

                                  Thanks very much for your advice, would you mind posting that link again as I can't see it in your first message.  I'm going to jump into the world of XSL and XML and hope I float!!

                                  Reply to this message by replying to this email, or go to the message on FileMaker Community

                                  Start a new discussion in Discussions by email or at FileMaker Community

                                  Following How to keep conditional formatting when exporting to Excel? in these streams: Inbox

                                  Manage your email preferences

                                   

                                  FileMaker Developer Conference 2015 • Las Vegas, Nevada • July 20-23 • www.filemaker.com/devcon

                                   

                                  1 2 Previous Next