5 Replies Latest reply on Jul 8, 2007 4:29 PM by efecto747

    Query to Excel

      I have a page that displays results of a search query. I'd like to give users an option to export the results that they see into Excel format by clicking on an icon.
      How can I do that?

      I do have an UDF that convert query to Excel and takes query as an argument. Anyway I can call it after the page's been rendered?
      Any suggestions welcome.

      Thank you
        • 1. Re: Query to Excel
          Level 7
          I can think of a couple of possibilities.

          1)If you generate the output in a CSV format and deliver that with the
          appropiate content-headers using the <cfcontent...> tag, it would open
          or download into excell or any other spreadsheet application configured
          to handle CSV files on the client machine.

          2)A long ago ColdFusion Developer Journal article talked about how Excel
          can fairly easily be convinced to read a HTML table as data and showed
          how to format data to be download into a client's worksheet.
          • 2. Query to Excel
            c_wigginton Level 1
            Use cfcontent. Here's a livedoc article on it.

            http://livedocs.adobe.com/coldfusion/6/Developing_ColdFusion_MX_Applications_with_CFML/man ageFiles5.htm

            Jump to the section "To create an Excel spreadsheet with cfcontent"
            • 3. Re: Query to Excel
              efecto747 Level 1
              I used to use the html method which is very simple - generate your output in an html table, save it to a file with an .xls extension and then open it using cfcontent. Excel automatically converts the html table into excel rows and columns and some basic formatting is also retained.

              More recently however I've been using Jakarta POI which is simple to use and much more powerful. I won't try explaining how to use it as there's a page that already does a nice job of it >>HERE<<

              • 4. Re: Query to Excel
                mama_karlo Level 1
                Thank you all,
                But my problem is not converting query to excel, i do have a function that handles it: generateExcel(aQuery). This function takes any query as an argument and generates .xls file on the fly.
                I need to call this function by clicking an icon and pass the result query whithin the same page that displays the result in HTML format
                • 5. Re: Query to Excel
                  efecto747 Level 1
                  What you're after in that case, is some form of Ajax functionality. There's a stack of resources out there on Ajax that you'll find with a bit of googling.

                  You could also achieve this type of funtionality by using frames and making 1 (or more) frames invisible by setting their height to 0. You can then direct action to the hidden frame - this keeps the current page visible while the hidden frame generates your excel.