8 Replies Latest reply on Apr 3, 2007 5:05 AM by mikeap

    How to generate multiple excel files at once?

    YogeshM Level 1
      Hi,

      Normally, I use the following to create an excel file

      <cfcontent type="application/msexcel">
      <cfheader name="Content-Disposition" value="filename=myfilename.xls">

      I have a multi-select combo box (e.g. employees)

      Each employee has his own set of details and each employee's details need to be on 1 separate excel file.

      If I choose 1 employee at a time, it works fine.

      But if I include the cfcontent and cfheader tags inside the cfloop tag as follows, it doesn't work.

      <!--- Loop each employee selected --->
      <cfloop index="i" list="#form.employee_cb#" delimiters=",">

      <!--- create excel file --->
      <cfcontent type="application/msexcel">

      <!--- Create 1 file for each employee; each file corresponds to each employee name --->
      <cfheader name="Content-Disposition" value="filename=#i#.xls">

      <!--- Process each employee selected and output in excel --->


      </cfloop>

      How can I generate 1 excel file automatically for each of the employees?

      Thanks and regards,
      Yogesh Mahadnac
        • 1. Re: How to generate multiple excel files at once?
          YogeshM Level 1
          Hello????

          Is there anybody who can pls help with this thread?

          Thanks
          • 2. Re: How to generate multiple excel files at once?
            Level 7
            Such is the nature of HTTP that you can only respond to a request with a
            single file.

            I suggest generating the files first, then zipping them up and respond with
            the zip file.

            --
            Adam
            • 3. Re: How to generate multiple excel files at once?
              efecto747
              Have you tried putting the excel generating code into a function and then calling the function from within the loop?
              • 4. How to generate multiple excel files at once?
                BKBK Adobe Community Professional & MVP
                > How can I generate 1 excel file automatically for each of the employees?

                The combination <cfcontent><cfheader> makes coldfusion display the file, for viewing or download, one at a time in the browser. Very much like a slide-show. What you wish to do with the loop is like a movie. I don't think it would work. At least, not as you expect. Even with the loop, I expect Coldfusion to deliver just one file.

                If the loop did work, it wouldn't have been a good idea after all. Coldfusion would have given each employee the xls sheets of all the other employees. That can't be what you wanted. Here is an idea

                <cfif listContainsNoCase(form.employee_cb, employee_name) GT 0>
                <cfcontent type="application/msexcel">
                <cfheader name="Content-Disposition" value="inline; filename=#employee_name#.xls">
                </cfif>



                • 5. How to generate multiple excel files at once?
                  insuractive Level 3
                  You should be able to use the Jakarta POI interface to build the excel files. Its basically a Java interface that allows you to build/read from excel spreadsheet files using java (the Excel application need not be installed). And the best part is that you already have it installed if you are using CF 7 - Adobe uses it for some of their CF Reports functionatity.

                  The interface is not that complicated. I've even seen some custom tags that have wrapped some of the Java funtionality with an easy to use ColdFusion wrapper.

                  The advantage of using Jakarta POI (as opposed to using <cfcontent> with HTML) is that the files are ACTUAL excel files (file type .xls), not just HTML or XML files that excel can open and interpret as a stylesheet.

                  Once you've created your files you could:

                  Attach them to an email and then delete them after the email ships

                  Use a custom tag to add them all to a zip file and then force the user to download the zip file

                  Provide a set of temporary links to your files and delete them after a short period of time.

                  The possibilities are endless.
                  • 6. Re: How to generate multiple excel files at once?
                    efecto747 Level 1
                    Hey thanks for posting that suggestion insuractive, we generate a lot of excel files using the html method so this will be very useful.

                    YogeshM, for some good examples on how to use the Jakarta POI interface, have a look at this link: www.d-ross.org

                    cheers.
                    • 7. Re: How to generate multiple excel files at once?
                      YogeshM Level 1
                      Thanks a lot for the help.

                      Regards,
                      Yogesh
                      • 8. Re: How to generate multiple excel files at once?
                        mikeap
                        I found a good article on Google that I've been trying to re-find since I didn't bookmark it but it explained exactly what you wanted to do. It was geared towards creating HTML pages using ColdFusion and a database. Basically he looped over every record and created a master and detail page for all 700 records.