Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 657
  • Last Modified:

Download the data from SQL to excel using CF 9.0

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
Tpaul_10
Asked:
Tpaul_10
  • 17
  • 12
1 Solution
 
_agx_Commented:
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
 
Tpaul_10Author Commented:
Thanks for the quick reply and any example will be very helpful.

Thanks again.
0
 
_agx_Commented:
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
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
Tpaul_10Author Commented:
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
 
_agx_Commented:
> 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
 
_agx_Commented:
> 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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
> 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
 
_agx_Commented:
> <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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
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
 
_agx_Commented:
> 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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
  > 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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
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
 
_agx_Commented:
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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
> 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
 
Tpaul_10Author Commented:
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
 
_agx_Commented:
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
 
Tpaul_10Author Commented:
>>"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
 
_agx_Commented:
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
 
_agx_Commented:
> I don't know what else to suggest

Other than placing the instructions in a cell rather than a comment.
0
 
Tpaul_10Author Commented:
Thanks Agx and your suggestions were helpful, I sitll have to figure out about the comments on the columns
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 17
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now