8 Replies Latest reply on Feb 13, 2008 12:30 AM by YogeshM

    export data into Excel


      I made a query and displayed the data with <CFGRID>. What I now want to do is to make a button on the page to open the content/data in excel. [export to excel]. Is this possible in coldfusion? What do I have to do to get this started?

      I tried a example like this:

      <cfquery name="" datasource="">
      SELECT * FROM dev

      <CFREPORT format="Excel" template="test.xls"
      query="#excel#" />

      However it doesn't work. I see a little symbol in the left corner of the opened window [square, triangle, ball on it].

      Could somebody direct me in the good way?


        • 1. Re: export data into Excel
          wmanu Level 1
          Hi Kabbi,

          From my point of view you can create a page with same query in and output the query result in the page in <TABLE> format. And at the end of the page use the following code. Then lik to that file through a button click, that should give you a popup with Open/Save command.

          <CFHEADER NAME="Content-Type" VALUE="application/msexcel">
          <CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=Test.xls">

          Hope this helps.

          Cheers / Manu.
          • 2. Re: export data into Excel
            kabbi~thkek Level 1
            Hi Manu,

            Thank you! I'll try this and look if it works.


            • 3. Re: export data into Excel

              Using the method below works on my development box but causes a file not found error on the live server. I have double and tripple checked that the file is there and that the link is correct. Any ideas? BTW I am a ColdFusion newbie.

              • 4. Re: export data into Excel
                kabbi~thkek Level 1

                What exactly does this code do:

                <CFHEADER NAME="Content-Type" VALUE="application/msexcel">
                <CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=Test.xls">

                After trying a lot I did not manage to export some db query results into excel.

                Does somebody knows a good example/tutorial on the internet?

                Regards, Kabbi
                • 5. Re: export data into Excel
                  Hello Kabbi,

                  Hopefully, you have remedied this issue, but if not, there is a solution. It may seem overwhelming, but once you do it, you'll see how easy it is.

                  The only requirements will be Java and Apache's POI which you can get here: http://www.apache.org/dyn/closer.cgi/jakarta/poi/

                  POI is an API to Microsoft formats.

                  Unzip the file and place the JAR file under {cfusion home}/lib and restart the ColdFusion server. That's pretty much it for the hard part.

                  Now, we'll move on to making the actual Excel file;

                  This is the only call you'll make to Apache's POI. This will create the main workbook object.
                  <cfset workbook = createobject("java","org.apache.poi.hssf.usermodel.HSSFWorkbook").init() />

                  Create a sheet for the workbook:
                  <cfset sheet = workbook.createSheet() />

                  You'll need to give the sheet a name so POI will know which one to work with:
                  <cfset workbook.setSheetName(0,"Name it whatever you want") />

                  Start a new row, cell and set the cell name.
                  <cfset row = sheet.createRow(0) />
                  <cfset cell = row.createCell(0) />
                  <cfset cell.setCellValue("Cell One") />

                  Think of the Cells and Rows as an array starting with 0 and stopping at however many cells/rows you need. We'll do a few just for kicks.
                  <cfset second_row = sheet.createRow(1) />
                  <cfset second_cell = row.createCell(1) />
                  <cfset second_cell.setCellValue("Cell Two") />

                  <cfset third_row = sheet.createRow(2) />
                  <cfset third_cell = row.createCell(2) />
                  <cfset third_cell.setCellValue("Cell Three") />

                  <cfset fourth_row = sheet.createRow(3) />
                  <cfset fourth_cell = row.createCell(3) />
                  <cfset fourth_cell.setCellValue("Cell Four") />

                  <cfset fifth_row = sheet.createRow(4) />
                  <cfset fifth_cell = row.createCell(4) />
                  <cfset fifth_cell.setCellValue("Cell Five") />

                  <cfset sixth_row = sheet.createRow(5) />
                  <cfset sixth_cell = row.createCell(5) />
                  <cfset sixth_cell.setCellValue("Cell Six") />

                  Now, we'll begin adding our data to the new Excel sheet.
                  <cfloop query="YourQuery">
                  <cfset row = sheet.createRow(currentrow) />
                  <cfset cell = row.createCell(0) />
                  <cfset cell.setCellValue(QueryColumnName) /> <!--- The values will be the actual names of your columns --->

                  <cfset second_row = sheet.createRow(currentrow) />
                  <cfset second_cell = row.createCell(1) />
                  <cfset second_cell.setCellValue(QueryColumnName) />


                  <cfset sixth_row = sheet.createRow(currentrow) />
                  <cfset sixth_cell = row.createCell(5) />
                  <cfset sixth_cell.setCellValue(dateformat(DateColumnForFun,"yyyy-mm-dd")) />

                  Now, we'll need to call Java's file output stream class so we can write the Excel file to disk.
                  <cfset fos = createobject("java","java.io.FileOutputStream").init("NameThisFile.xls") />

                  The actual writing of the file to disk:
                  <cfset workbook.write(fos) />

                  And finally, close the Java FOS to free up some memory... or something like that.
                  <cfset fos.close() />

                  I know this may look like a lot, but it's really not. It's pretty simple after the first time you do it.

                  I hope this helps... or is at least somewhat close to your original question.

                  • 6. Re: export data into Excel
                    In this example, how would I modify the style/format of the Excel cells/rows? I've downloaded the poi stuff from that site, but the majority of it is beyond me to implement. Thanks.
                    • 7. Re: export data into Excel
                      insuractive Level 3
                      The CFContent / Excel method goes something like this:

                      1) Place the following tag at the top of your CF code:
                      <cfsetting enablecfoutputonly="Yes">
                      this will prevent CF from displaying anything (text, whitespace,etc) unless it is surrounded by a cfoutput tag - this is important because we don't want too much white space.

                      2) Generate an HTML table using CF that has the data from your query in the format you would like it to appear in Excel. Use the <cfsavecontent> tag to store the generated HTML in a CF variable. Make sure you surround your text with <cfoutput> tags

                      3) Use the <cfheader> tag to inform the browser what you are about to sent it and what it should do with it:
                      <CFHEADER NAME="Content-Disposition" VALUE="attachment; filename=Test.xls">
                      Here we tell the browser that we are sending it a file called "Test.xls" and it should treat it as an attachment (i.e. download it). You can also set the value to "inline" which will attempt to load it inline in the browser.

                      4)Use The <cfcontent> tag to set the MIME type for the file so that the browser knows what type of data it is receiving:
                      <cfcontent type="application/vnd.ms-excel">

                      5) output your HTML table using <Cfoutput>

                      The whole thing looks something like this:

                      <cfsetting enablecfoutputonly="Yes">
                      <cfsavecontent variable="sFileContent">
                      <td>Some Text</td>
                      <td>Other Text</td>
                      <CFHEADER NAME="Content-Disposition" VALUE="attachment;filename=test.xls">
                      <cfcontent type="application/vnd.ms-excel"><cfoutput>#sFileContent#</cfoutput>

                      Hope that helps
                      • 8. Re: export data into Excel
                        YogeshM Level 1
                        Hi Michael,

                        What if you had to create several excel files at once? How would you change the above code to cater for that?

                        I currently need to loop through a query and create 1 excel file for each loop.

                        Pls advise at your earliest convenience.

                        Thanks and regards,
                        Yogesh Mahadnac