Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

Download the data from SQL to excel using CF 9.0

Avatar of Tpaul_10
Tpaul_10Flag for United States of America asked on
ColdFusion Language
29 Comments1 Solution763 ViewsLast Modified:
Experts,

We are upgrading CF 7.0 to CF 9.0 and came to know that CF 9.0 doesn't support the COM. But we have a functionality to download the data from SQL to excel and using the following code where it is failing.

<cfobject action="CREATE" class="Excel.Application" name="objExcel" type="COM">

Since COM is not supported any more by CF, I looked for other options to download the data from SQL to excel and found this
http://aspalliance.com/1832_Import_and_Export_Data_From_Excel_Using_ColdFusion.4

I am using the method 1 from the above link and here are my questions where help is really needed.

1. Is there any way I can use this for multiple sheets?
2. Also, I don't want the column names to be listed in my sheet since I have already have the column names defined and need to place the data in the existing columns.
3. Or do you guys suggest any other way of achieveing or make the download functionality work?

Thanks in advance.


<cffunction name="writeExcelSheet" access="public" output="false" returntype="void">
      <cfargument name="tableName" required="true" type="string" />
      <cfargument name="dsnName" required="true" type="string" />
      <cfargument name="fileName" required="true" type="string" />
      <cfargument name="sheetName" required="true" type="string" />
      <cfscript>
            var getRecords = '';
            var workBook = '';
            var newSheet = '';
            var listOfColumns = '';
            var count = 0;
            var index = '';
            var row = 0;
            var cell = 0;
            var fileOutStream = '';
      </cfscript>
      <cfquery name="getRecords" datasource="#arguments.dsnName#">
            select * from #arguments.tableName#
      </cfquery>
      <cfif getRecords.RecordCount>
            <cfscript>
                  workBook = createObject("java", 
                    "org.apache.poi.hssf.usermodel.HSSFWorkbook").init();
                  newSheet = workBook.createSheet();
                  workBook.setSheetName(0, "#arguments.sheetName#");
                  listOfColumns = "#getRecords.columnList#";
                  count = 0;
            </cfscript>
            <cfloop list="#listOfColumns#" index="index">
                  <cfscript>  
                        row = newSheet.createRow(0);
                        cell = row.createCell(count);
                        cell.setCellValue(index);
                        count = count + 1;
                  </cfscript>
            </cfloop>
            <cfloop query="getRecords">
                  <cfscript>
                        row = newSheet.createRow(#getRecords.currentRow#);
                        count = 0;
                  </cfscript>
                  <cfloop list="#listOfColumns#" index="index">
                        <cfscript>
                              cell = row.createCell(count);
                              cell.setCellValue(getRecords[index][currentRow]);
                              count = count + 1;
                        </cfscript>
                  </cfloop>
            </cfloop>
            <cfscript>
                  fileOutStream = createObject("java", 
                    "java.io.FileOutputStream").init("#arguments.fileName#");
                  workBook.write(fileOutStream);
                  fileOutStream.close();
            </cfscript>
      </cfif>
</cffunction>
<!--- Start Calling the Function--->
	<cfoutput> 
	 #writeExcelSheet(tableName="myTable", dsnName="myDsn",    fileName="C:\myFile.xls",sheetName="MySheet")#
	</cfoutput>
<!--- End Calling the Function--->
	
<!--- Start opening the Dialog box to open/close/--->	
		<cfheader name="Content-Disposition" value="inline; filenameC:\myFile.xls"> 
		<cfcontent type="application/vnd.ms-excel" file="C:\myFile.xls">
<!--- End opening the Dialog box to open/close/--->