Solved

Download the data from SQL to excel using CF 9.0

Posted on 2011-09-29
29
637 Views
Last Modified: 2012-07-11
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/--->

Open in new window

0
Comment
Question by:Tpaul_10
  • 17
  • 12
29 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 36816792
Unless you're exporting a huge amount of data, I'd suggest using cfspreadsheet instead.  Like COM, it's got some limitations. But give it a whirl.

If you're running MS SQL you can also use it's export features instead.  But try cfspreadsheet first.
0
 

Author Comment

by:Tpaul_10
ID: 36816912
Thanks for the quick reply and any example will be very helpful.

Thanks again.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36817176
Using the sample db's included with CF9

<cfset colListOne = "Foo,Bar,Foo,Bar">
<cfquery name="qOne" datasource="cfdocexamples">
	SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME 
    FROM COURSELIST 
</cfquery> 
 
<cfset colListTwo = "Foo,Bar,Foo,Bar">
<cfquery name="qTwo" datasource="cfdocexamples">
	SELECT 	Name, Address1, City, Country
    FROM 	CENTERS 
</cfquery> 
    
<!--- create a new worksheet --->
<cfset workbook = SpreadSheetNew("First Sheet")>

<!--- populate first sheet with data --->
<cfset SpreadSheetAddRows(workbook, qOne, 1 ) />
<!--- overwrite column names in first row --->
<cfset SpreadSheetAddRow(workbook, colListOne, 1, 1, false) />

<!--- create second sheet --->
<cfset SpreadsheetCreateSheet(workbook, "Second Sheet")>
<cfset SpreadsheetSetActiveSheet(workbook, "Second Sheet")>

<!--- populate second sheet with data --->
<cfset SpreadSheetAddRows(workbook, qTwo, 1 ) />
<!--- overwrite column names in first row --->
<cfset SpreadSheetAddRow(workbook, colListTwo, 1, 1, false) />

<!--- return to browser --->
<cfheader name="Content-Disposition" value="inline; filenameC:\myFile.xls"> 
<cfcontent type="application/vnd.ms-excel" variable="#SpreadSheetReadBinary(workbook)#">

Open in new window

0
 

Author Comment

by:Tpaul_10
ID: 36817396
I am going to try this right now, but a quick question how can I write to an existing xls file format.
That is what I have been looking for and please see my old code from the code section to give an idea what I was doing.

in myExcel.xls file I have defined or created the sheets with column names and just need to insert the data.

THANKS again for all your help
<CFSET DEBUG_STEP_STR = "0">
	<CFSET ERR_FLG = "0">
	<CFSET FILE_TEMPLATE = "#ExpandPath(".")#\myExcel.xls">
	<CFSET FILE_NM = "ABC_#PrdNumber#_#dateformat(now(),"mmddyyyyhhmmss")#.xls">
	<CFSET FILE_SAVEASPATH = "#ExpandPath(".")#\Files\#FILE_NM#">    

<cftry>
	<cfobject action="CREATE" class="Excel.Application" name="objExcel" type="COM">
	<CFSET DEBUG_STEP_STR = "1">
				
	<cfcatch type="Any">
		  <cfabort showerror="Cannot create Excel Object. Make sure that Excel is installed and that ColdFusion has permissions to use Excel COM objects. Error Details: #CFCATCH.MESSAGE#">                    
	</cfcatch>						
</cftry>  
<!---- My SQL restuls query here--->
<CFSCRIPT>
				objWorkbooks = objExcel.Workbooks;
				objWorkbook = objWorkbooks.Open(#FILE_TEMPLATE#);
				objWorkbook.Activate();
				DEBUG_STEP_STR = "2";
				
				objWorksheets=objWorkbook.WorkSheets;
				objWorksheet = objWorksheets.Item("Sheet One");
				objWorksheet = objWorksheets.Item("Sheet two");				
				objWorksheet = objWorksheets.Item("Sheet three");
				objWorksheet = objWorksheets.Item("Sheet Four");
				objWorksheet = objWorksheets.Item("Sheet Five");
				objWorksheet = objWorksheets.Item("Sheet Six");								
								
			</CFSCRIPT>
			
<cfset sheetonedata1 = sheetonedata__Results1>

<cfdump var="#sheetonedata1 #">
			<CFLOOP QUERY="sheetonedata1">
				<CFOUTPUT>
					 <CfQuery name= "FirstQryXL" datasource="varExcel"  >
						insert into [Sheet One$] IN '#FILE_SAVEASPATH#' 'EXCEL 5.0;'
						values(#Column1#,#Column2#,'#Description#',#Value#)
					</cfquery>
				</CFOUTPUT>
			</CFLOOP>
			
<!--- other sheets code--->
		<cfheader name="Content-Disposition" value="inline; filename=#FILE_NM#"> 
		<cfcontent type="application/vnd.ms-excel" file="#FILE_SAVEASPATH#">

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36817463
> how can I write to an existing xls file format.

cfspreadsheet works differently. you defined the format when you create the worksheet. The default is ".xls" format.  But you can create the newer .xlsx format using the 2nd parameter:

        SpreadsheetNew([sheetName, xmlformat])

> in myExcel.xls file I have defined or created the sheets with column names and just
> need to insert the data.

Again it works differently. You create a new sheet using SpreadsheetCreateSheet and activate it with SpreadsheetSetActiveSheet.  See my notes and it should make sense what's going on. But let me know if it doesn't :)

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36817516
> how can I write to an existing xls file format.

Oh wait... my example doesn't save it to disk - because it's not necessary if you're just displaying it in the browser.  But if you really do want to save it, use <cfspreadsheet action="write" ...> or SpreadSheetWrite().  
0
 

Author Comment

by:Tpaul_10
ID: 36817759
Your code make sense and it was creating a new file and writing into it.
So, What I understood is, if I need to write to an existing file, I need to use
<cfspreadsheet action="write" ...> .. is that right?

Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36817944
> if I need to write to an existing file,

I think so .. but depends on what you mean by "existing file". cfspreadsheet action="write" and SpreadsheetWrite() are used to create a brand new file (or overwrite one).  I think that's what you're asking.  To read in an existing file and update it with more data - that's possible, but more complicated.  
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36818021
> <CFSET FILE_TEMPLATE = "#ExpandPath(".")#\myExcel.xls">

Hm... I may have misunderstood.  If you're using an existing file as a template, just read in the file first:

      <cfset workbook = SpreadSheetRead(pathToExistingXLSFile)>

Then activate each sheet and add data to it.

      <!--- activate first sheet ... -->
      <cfset SpreadsheetSetActiveSheet(workbook, "First Sheet Name")>
      ... populate current sheet with data ...

Activate the next sheet and repeat.

      <cfset SpreadsheetSetActiveSheet(workbook, "Second Sheet Name")>
      ... populate current sheet with data ...

When finished, return it to the browser like in the earlier example.
0
 

Author Comment

by:Tpaul_10
ID: 36818225
Yes, I am using an existing file as a template and followed the steps you have specified.
Please see the code and not getting any error and not populating the data in to my excel file.
Not sure how to debug this.

Thanks for all your help, appreciate it.
<CFSET DEBUG_STEP_STR = "0">
	<CFSET ERR_FLG = "0">
	<CFSET FILE_TEMPLATE = "#ExpandPath(".")#\Template_New.xls">
	<CFSET FILE_NM = "MEW_#dateformat(now(),"mmddyyyyhhmmss")#.xls">
	<CFSET FILE_SAVEASPATH = "#ExpandPath(".")#\Files\#FILE_NM#">  
	
<cfquery name="qOne" datasource="icat_web">
	select Col1,Col2,Col3,Col4 from myTable
</cfquery> 

<cfset workbook = SpreadSheetRead("#ExpandPath(".")#\Template_New.xls")>

 <!--- activate first sheet ... --->
      <cfset SpreadsheetSetActiveSheet(workbook, "Prouct Info")>
  <!--- populate first sheet with data --->
<cfset SpreadSheetAddRows(workbook, qOne, 1 ) />

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36818291
Sorry, my bad.  SpreadSheetAddRow and SpreadSheetAddRows won't overwrite existing rows unless you tell it to.   So change this

   <cfset SpreadSheetAddRows(workbook, qOne, 1 ) />

to
      <!--- SpreadSheetAddRows(workbook, query, row, column, isInsertOnly) --->
      <cfset SpreadSheetAddRows(workbook, qOne, 1, 1, false ) />

0
 

Author Comment

by:Tpaul_10
ID: 36818416
Still no luck and same problem. I am going to try activating sheet by sheet and see if it works.
Not sure how to dedug though.

Thanks
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 36818440
No, I can confirm it works.  If you're displaying it to the browser, it's probably cached. Either clear your cache or try writing it to a file instead.

<cfquery name="qOne" datasource="cfdocexamples">
    SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME
    FROM COURSELIST
</cfquery>

<CFSET FILE_TEMPLATE = "c:\testTemplate.xls">
<cfset workbook = SpreadSheetRead(FILE_TEMPLATE)>

<cfset SpreadsheetSetActiveSheet(workbook, "First Sheet")>
<cfset SpreadSheetAddRows(workbook, qOne, 1, 1, false ) />      
<cfspreadsheet action="write" filename="c:\output.xls" name="workbook" overwrite="true" />


0
 
LVL 52

Expert Comment

by:_agx_
ID: 36818482
> I am going to try activating sheet by sheet and see if it works.

The spreadsheet functions will only write to the currently activeSheet. So make sure you've activated the proper sheet before writing.

> Not sure how to debug though.

Best way to debug is to use cfdump on the workbook object and/or write the results to a file. Displaying it the browser will make you crazy because IE always caches everything. So the results are often 2 steps behind.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Tpaul_10
ID: 36892918
Here is the update.

1. I have tried writing it to a file and it does work, but I still need to verify the data.
I had to change the column and row number in
<cfset SpreadSheetAddRows(workbook, GetData2, 4, 1, false ) />

2.When I try to open the file it says "office File validation detected a problem while opening this file and opening may be dangerous", if I still open it says "File error: data may have been lost"

3. Also Getting an error "The file specified in contentTag does not exist." from
 <cfcontent type="application/vnd.ms-excel" file="#FILE_SAVEASPATH#">

Based on the error what I understood is I am trying to open a file which is not written to disc..
Hope I am making sense.

Appreciate your response to get through this.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36892975
  > I had to change the column and row number in
That's fine. I used row=1,col=1 but it can start on whatever row/column you need.

    > When I try to open the file it says "office File validation detected a problem while
    > opening this file and opening may be dangerous", if I still open it says "File error:
    > data may have been lost"

Sounds like there's something special about your "template".  What does it contain (macros, formulas,charts...)?  cfspreadsheet can usually handle most stuff. But for really complex stuff, nothing is going to be as good as COM.

Any chance you post a small non-proprietary version of the template that reproduces the issue?

    > what I understood is I am trying to open a file which is not written to disc..

Most likely. IS your code saving the sheet to disk? ;-) We've used both methods, so I not sure what your current code is doing.

0
 

Author Comment

by:Tpaul_10
ID: 36893017
Sorry forgot to attach the code..Please find it.

We have macros and formulas in my sheet..will try to get the non-proprietary version.

THANKS again for all your help
<cfset prdNumber = "123444A6">

	
<cfset myDsn = "myDsn">
<cfinclude template="myquery.cfm">

<cfset GetData1 = Prd_GetData1__Results>
<cfset GetData2 = Prd_GetData1__Results>
<cfset GetData3 = Prd_GetData1__Results>



<CFSET DEBUG_STEP_STR = "0">
<CFSET ERR_FLG = "0">
<CFSET FILE_TEMPLATE = "#ExpandPath(".")#\Template.xls">
<CFSET FILE_NM = "NEW_#dateformat(now(),"mmddyyyyhhmmss")#.xls">
<CFSET FILE_SAVEASPATH = "#ExpandPath(".")#\Files\#FILE_NM#">    
			


<cfset workbook = SpreadSheetRead("#ExpandPath(".")#\Template.xls")>

 <!--- activate first sheet ... --->
      <cfset SpreadsheetSetActiveSheet(workbook, "Product Info")>
  <!--- populate second sheet with data --->
<!---<cfset SpreadSheetAddRows(workbook, GetAftershockData1, 1 ) />--->
 <cfset SpreadSheetAddRows(workbook, GetData1, 4, 1, false ) />


 <!--- activate first sheet ... --->
      <cfset SpreadsheetSetActiveSheet(workbook, "Product Pricing")>
  <!--- populate second sheet with data --->
<!---<cfset SpreadSheetAddRows(workbook, GetAftershockData1, 1 ) />--->
 <cfset SpreadSheetAddRows(workbook, GetData2, 4, 1, false ) />

   <cfdump var="#FILE_NM#">												
		
  <cfspreadsheet action="write" filename="c:\output1.xls" name="workbook" overwrite="true" />
  
		<cfheader name="Content-Disposition" value="inline; filename=#FILE_NM#"> 
		<cfcontent type="application/vnd.ms-excel" file="#FILE_SAVEASPATH#">--->

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>
<head>
	<title>Testing Exce</title>
</head>
<script language="JavaScript">
 window.status='';
 window.status='Processing your request,Please wait...' ;
 </script>

<body>	

		<cfoutput>Downloaded Succesfully</Cfoutput>
		
</body>
</html>

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 36893131
It's possible the macros are the problem.  Try creating a copy of the template and removing the macros. Then run it against the copy. Any change?

> FILE_SAVEASPATH
If you want to save to disk, you need to replace "c:\output1.xls" with the path to your file here:

    <cfspreadsheet action="write" filename="c:\output1.xls" name="workbook" overwrite="true" />

0
 

Author Comment

by:Tpaul_10
ID: 36893827
Thanks againfor your help and patience.

1. I am able write the file to disc and open the file through the browser, but still getting "Data may have been lost" alert box when I am opening.
2. When I open the file, file name on top of the excel sheet says index.cfm (read only) compatibility mode.
3. I am going to complete all of my sheets code and will see how it is going to respond.

Keep you posted.
THANKS for your help
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36894449
No problem :)  

> still getting "Data may have been lost"

Do you get that message only when using your template OR even with the simple example I posted? If it's the former that suggests some issue with cfspreadsheet and your template. Without seeing it it's hard to say, but it may be special features (macros, images, etc...).  
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36895475
You need to find out if the problem is your code or the template. Quickest way is to run the same code against a "blank" template.  No macros or anything. Just change the sheet names. If it works, the problem's your template.
0
 

Author Comment

by:Tpaul_10
ID: 36905860
I am still working on it and found that there are some hidden columns in my spreadsheet and do you think this is one of the reason I am getting the error?

So, is there any way or how to handle the hidden columns while I am adding the rows.?

Data is good for the spread sheets which don't have the hidden columns.

Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36906164
> do you think this is one of the reason I am getting the error?

It doesn't usually cause a problem.  But I'm having a hard time following what you have/haven't tried so far ... Are you saying it works with your files as long as they don't contain hidden columns? If yes, is the error the same as before or different?
0
 

Author Comment

by:Tpaul_10
ID: 36919458
Here is what I did.

1. I have been using my spreadsheet to test this and have not tried with a blank sheet.
2. As you said, hidden columns are not an issue (THANK YOU)
3. The problem is, There are some comments/notes inserted on the some of the columns.
If I delete those comments/notes and run the code (as per your suggestions) works fine with no errors.
4. If I put back those comments and run the code, I get the error saying "File Error : Data may have been lost". But I can still open the file through the browser with no comments/notes on the columns.
5. That is the only problem I have right now and not sure how I can retain those comments I have on the columns.

THANKS for all your help and direction.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36919730
Good detective work.  So when you get the error "File Error : Data may have been lost" and open the file, are the comments still there?  

Also, anything special about the comments? If that's the cause, there may not be much you can do about it ... other than searching the bug db to see if it's been reported/fixed. Like I said, nothing is going to be as good as COM ... unfortunately.
0
 

Author Comment

by:Tpaul_10
ID: 36919906
>>"File Error : Data may have been lost" and open the file, are the comments still there?  

"No, I don't see any of the comments on the columns after download".
Those are the comments for users to what kind of data they are supposed to enter in a a given column and I have comments for only one sheet out of 8.

Thanks
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36920018
At least now you know what data was lost.  

Is there anything special about them? The only thing I can think of is to try and remove any extra formatting. Or try it with a single comment.  If that doesn't work either (though I can't imagine why it wouldn't ..) I don't know what else to suggest without a test file :/
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36920129
> I don't know what else to suggest

Other than placing the instructions in a cell rather than a comment.
0
 

Author Closing Comment

by:Tpaul_10
ID: 37034173
Thanks Agx and your suggestions were helpful, I sitll have to figure out about the comments on the columns
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now